//require ojdbc6.jar in classpath
String driverName = "oracle.jdbc.driver.OracleDriver"; 
String connectionURL = "jdbc:oracle:thin:@oracledb.mstrblog.co.uk"; 
String login = "user1"; 
String password = "password1"; 
String tableName = "DELIVERY"; 
String userColumn = "DELIVERY_ID"; 
try {
//load driver here
Class.forName(driverName);
//use jdbc url, database login, and database password to create connection
java.sql.Connection conn = java.sql.DriverManager.getConnection(connectionURL, login, password);
java.sql.Statement stmt = conn.createStatement();
String dbQuery = null;
dbQuery = "TRUNCATE TABLE " + tableName; //delete all data from table
stmt.executeUpdate (dbQuery); 
oBufReader = new BufferedReader(new FileReader("C:/mstrblog/AdGroups.txt"));
while ((sPropFileLine = oBufReader.readLine()) != null) {
GroupArr = sPropFileLine.split(";"); 
//Read AD users by group name
StringBuffer sADAllUsers = new StringBuffer();
sADAllUsers.append("cmd /c dsget group \"CN="+GroupArr[0]+",OU=Resource Access,OU=Groups,OU=Enterprise,DC=mstrblog,DC=co,DC=uk\" -members | dsget user -dn"); 
Process procReadADUsers=Runtime.getRuntime().exec(sADAllUsers.toString());
BufferedReader brInputReadADUsers = new BufferedReader(new InputStreamReader(procReadADUsers.getInputStream()));
BufferedReader brErrorReadADUsers = new BufferedReader(new InputStreamReader(procReadADUsers.getErrorStream()));
String sReadADUserErrors = null; 
String sReadADUser = brInputReadADUsers.readLine();
String unprocessedUserDN = brInputReadADUsers.readLine();
while (unprocessedUserDN!= null) { 
if((! unprocessedUserDN.equals("dsget succeeded"))) {
sReadADUser =(unprocessedUserDN.substring((int)unprocessedUserDN.indexOf('=')+1,(int)unprocessedUserDN.indexOf(','))).trim().toLowerCase();
//execute sql query
dbQuery = "INSERT INTO " + tableName + " VALUES ('"+ sReadADUser + "');";
printOut(dbQuery);
stmt.executeUpdate (dbQuery);
}
unprocessedUserDN = brInputReadADUsers.readLine();
}
while ((sReadADUserErrors = brErrorReadADUsers.readLine()) != null) {
printOut("Error reading members of AD group "+GroupArr[0]+": "+sReadADUserErrors); 
}
int exitValue = procReadADUsers.waitFor(); 
printOut("Exit Status: "+exitValue);
sADAllUsers.delete(0,sADAllUsers.length()); 
stmt.close();
conn.close();
} 
} catch (Exception e) { 
printErr(e.getLocalizedMessage()); 
}
Load AD users to Oracle table (based on AD SAM account)
/*This procedure will perform the following tasks:
1. Retrives all AD users created in AD group.
2. Checks if AD users are created in MSTR.
3. Inserts MSTR user "login" and "full name" in oracle table USER_TABLE.
4. Provides access to new mstrblog users in oracle table USER_TABLE.
*/

String driverName = "oracle.jdbc.driver.OracleDriver";
String connectionURL = "jdbc:oracle:thin:@oracledb.mstrblog.co.uk";
String orclLogin = "user1";
String orclPassword = "password1";
String tableName = "USER_TABLE";
String sProjectName = "Project1";
String sGroupName = "AD_Group1";
String mstrFullName;
String mstrLogin;
int intUserExistsMstr;
String dbQuery;

try {
//oracle connection
Class.forName(driverName).newInstance();
java.sql.Connection conn = java.sql.DriverManager.getConnection(connectionURL, orclLogin, orclPassword);
java.sql.Statement stmt = conn.createStatement(); 
dbQuery = "TRUNCATE TABLE USER_TABLE."+tableName; //delete all data from table
stmt.executeQuery (dbQuery);
printOut(dbQuery);

//Read AD users by group name
StringBuffer sADAllUsers = new StringBuffer();
//sADAllUsers.append("cmd /c dsget group \"CN="+sGroupName+",OU=Resource Access,OU=Groups,OU=Enterprise,DC=mstrblog,DC=co,DC=uk\" -members | dsget user -dn"); 
sADAllUsers.append("cmd /c dsget group \"CN="+sGroupName+",OU=Resource Access,OU=Groups,OU=Enterprise,DC=mstrblog,DC=co,DC=uk\" -members | dsget user -samid"); 
Process procReadADUsers=Runtime.getRuntime().exec(sADAllUsers.toString());
BufferedReader brInputReadADUsers = new BufferedReader(new InputStreamReader(procReadADUsers.getInputStream()));
BufferedReader brErrorReadADUsers = new BufferedReader(new InputStreamReader(procReadADUsers.getErrorStream()));
String sReadADUserErrors = null; 
String sReadADUser = brInputReadADUsers.readLine();
String unprocessedUserDN = brInputReadADUsers.readLine();
while (unprocessedUserDN!= null) { 
if((! unprocessedUserDN.equals("dsget succeeded"))) {
//sReadADUser =(unprocessedUserDN.substring((int)unprocessedUserDN.indexOf('=')+1,(int)unprocessedUserDN.indexOf(','))).trim().toLowerCase();
sReadADUser =unprocessedUserDN.trim().toLowerCase();
ResultSet upResultSet=executeCapture("LIST ALL PROPERTIES FOR USER \""+sReadADUser+"\";");
intUserExistsMstr = upResultSet.getRowCount();
if(intUserExistsMstr != 0) {
upResultSet.moveFirst();
boolean enabled=(boolean)upResultSet.getFieldValue(ENABLED);
mstrFullName = upResultSet.getFieldValueString(FULL_NAME);
mstrLogin = upResultSet.getFieldValueString(LOGIN);
if (mstrFullName.contains("@")) {
dbQuery = "INSERT INTO Schema1."+tableName+" VALUES('"+sProjectName+"','"+sGroupName+"','"+mstrFullName+"','"+mstrLogin+"')";
printOut(dbQuery);
stmt.executeUpdate (dbQuery);
} else {
dbQuery = "INSERT INTO Schema1."+tableName+" VALUES('"+sProjectName+"','ALL','"+mstrFullName+"','"+mstrLogin+"')";
printOut(dbQuery);
stmt.executeUpdate (dbQuery);
}
}
}
unprocessedUserDN = brInputReadADUsers.readLine();
} 
while ((sReadADUserErrors = brErrorReadADUsers.readLine()) != null) {
printOut("Error reading members of AD group "+sGroupName+": "+sReadADUserErrors); 
}
int exitValue = procReadADUsers.waitFor(); 
printOut("Exit Status: "+exitValue);
sADAllUsers.delete(0,sADAllUsers.length());
stmt.close();
conn.close();
} catch (Exception e) { 
printErr(e.getLocalizedMessage());
}

Leave a Reply

Your email address will not be published. Required fields are marked *