MSTR Users & Groups to CSV & Oracle
//EXECUTE PROCEDURE MSTR_User_Extraction("resultfilename");
String driverName = "oracle.jdbc.driver.OracleDriver";
String connectionURL = "jdbc:oracle:thin:@exadata.google.co.uk";
String login = "user1";
String password = "password";
ResultSet oResultSet = executeCapture("LIST ALL PROJECTS;");
try {
PrintWriter outputfile= new PrintWriter("C:\\temp\\aaa.csv","UTF-8");//create output file
outputfile.println("Project,Group,User,Login");
String outputline=null;
//oracle connection
Class.forName(driverName).newInstance();
java.sql.Connection conn = java.sql.DriverManager.getConnection(connectionURL, sLogin, password);
java.sql.Statement stmt = conn.createStatement();
String dbQuery = null;
dbQuery = "TRUNCATE TABLE USER_ACCESS";//delete all data from table
stmt.executeQuery (dbQuery);
if(oResultSet.getRowCount() > 0) {
oResultSet.moveFirst();
String sProject = null;
// Go through each security role and list all users and groups
while (!oResultSet.isEof()) {
sProject = oResultSet.getFieldValueString(NAME);
String active = oResultSet.getFieldValueString(PROJECT_STATUS_2);
//list security roles for those loaded projects
//if(active != null && active.equalsIgnoreCase("loaded")){
if(sProject.equals("Project1")) {
ResultSet pResultSet=executeCapture("LIST PROJECT, PROJECT ACCESS FOR PROJECT CONFIGURATION IN PROJECT \"" + sProject + "\";");
pResultSet.moveFirst();
while (!pResultSet.isEof()) {
ResultSet aResultSet= (ResultSet) pResultSet.getFieldValue(PROJECT_ACCESS_RESULT_SET);
aResultSet.moveFirst();
//iterate over security roles to look for groups
while (!aResultSet.isEof()) {
String securityRole=(String)aResultSet.getFieldValue(SECURITY_ROLE);
ResultSet srResultSet=executeCapture("LIST ALL MEMBERS FOR SECURITY ROLE \""+securityRole+"\" IN PROJECT \""+sProject+ "\";");
srResultSet.moveFirst();
ResultSet mResultSet=(ResultSet)srResultSet.getFieldValue(MEMBER_RESULTSET);
Stack allGroups=new Stack();
mResultSet.moveFirst();
//iterate over members and check if it's a group
while (!mResultSet.isEof()) {
String sGroup =mResultSet.getFieldValueString(USER_OR_GROUP_NAME);
String isGroup=mResultSet.getFieldValueString(IS_GROUP);
if (isGroup.equalsIgnoreCase("true")) {
//List members of the group
allGroups.push(sGroup);
}
else {
if (isGroup.equalsIgnoreCase("false")) {
String fullName=mResultSet.getFieldValueString(FULL_NAME);
ResultSet upResultSet=executeCapture("LIST ALL PROPERTIES FOR USER \""+sGroup+"\";");
upResultSet.moveFirst();
boolean enabled=(boolean)upResultSet.getFieldValue(ENABLED);
outputline=sProject + ",DIRECT ASSIGMENT,"+fullName+","+sGroup;//create the row
//sql insert statement
dbQuery = "INSERT INTO USER_ACCESS VALUES('"+sProject+"','DIRECT ASSIGMENT','"+fullName+"','"+sGroup+"')";
if (enabled) {
outputfile.println(outputline);//write row to file
//execute sql
stmt.executeQuery (dbQuery);
printOut(dbQuery);
}
else {
printOut("user "+sGroup+" disabled");
}
}
}
mResultSet.moveNext();
}
while (!allGroups.empty()) {
String sGroupName=(String)allGroups.pop();
ResultSet uResultSet=executeCapture("LIST MEMBERS FOR USER GROUP\"" + sGroupName+ "\";");
uResultSet.moveFirst();
while (!uResultSet.isEof()) {
ResultSet umResultSet= (ResultSet) uResultSet.getFieldValue(MEMBER_RESULTSET);
umResultSet.moveFirst();
while (!umResultSet.isEof()) {
String isGroupIn=umResultSet.getFieldValueString(IS_GROUP);
String fullName=umResultSet.getFieldValueString(FULL_NAME);
String login=umResultSet.getFieldValueString(LOGIN);
if (isGroupIn.equalsIgnoreCase("false")) {
ResultSet upResultSet=executeCapture("LIST ALL PROPERTIES FOR USER \""+login+"\";");
upResultSet.moveFirst();
boolean enabled=(boolean)upResultSet.getFieldValue(ENABLED);
outputline=sProject + ","+sGroupName+","+fullName+","+login;//create the row
//sql insert statement
dbQuery = "INSERT INTO USER_ACCESS VALUES('"+sProject+"','"+sGroupName+"','"+fullName+"','"+login+"')";
if (enabled) {
outputfile.println(outputline);//write row to file
//execute sql
stmt.executeQuery (dbQuery);
printOut(dbQuery);
} else {
printOut("user "+login+" disabled");
}
}
else {
allGroups.push(fullName);
}
umResultSet.moveNext();
}
uResultSet.moveNext();
}
}
aResultSet.moveNext();
}
pResultSet.moveNext();
}
}
else {
printOut(sProject + " is unloaded");
}
oResultSet.moveNext();
}
} else {
printOut("No Project exists.");
}
outputfile.close();
stmt.close();
conn.close();
}
catch (Exception e) {
printErr("Error reading properties file: " + e.getLocalizedMessage());
}