JDBC source account creation using role/access/application request

Identity Security Cloud (ISC) ISC Discussion and Questions
,
1

Hi Team,

We are trying to create new accounts in JDBC source using role/access/application request and execute below multiple SQL statements and generate password and pass in one of the SQL statements as below.

multiple SQL statements during account creation at JDBC source:

– Create the Oracle user
CREATE USER $plan.nativeIdentity$ IDENTIFIED BY ‘password’ TEMPORARY TABLESPACE TEMP2 PROFILE test;

– Grant basic role or privilege
GRANT test TO “$plan.nativeIdentity$”;

– Additional grants (if needed)
GRANT test TO “$plan.nativeIdentity$”;
GRANT test TO “$plan.nativeIdentity$”;

Thanks

Kalyan

2

You will need to use Oracle Database type source in this case instead of JDBC as you are trying to create a DB user, not insert a record to a table

3

Hi @iamnithesh and everyone,

Thank you for your reply and this JDBC source is not a new source and already we have onboarded this source long time ago.

How to configure the above requirement in JDBC source of tyoe of Oracle DB?

Can we use below in JDBC?

image
image2530×1855 149 KB

We are using below JDBC provisioning rule for all different operations.

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Types;
import java.util.List;
import sailpoint.api.SailPointContext;
import sailpoint.connector.JDBCConnector;
import sailpoint.object.Application;
import sailpoint.object.ProvisioningPlan;
import sailpoint.object.ProvisioningPlan.AccountRequest;
import sailpoint.object.ProvisioningPlan.AttributeRequest;
import sailpoint.object.ProvisioningPlan.PermissionRequest;
import sailpoint.object.ProvisioningResult;
import sailpoint.object.Schema;
import java.util.Random;

log.debug(“entering testJDBCProvision Rule”);
ProvisioningResult result = new ProvisioningResult();

if ( plan != null ) {
List accounts = plan.getAccountRequests();
if ( ( accounts != null ) && ( accounts.size() > 0 ) ) {
for ( AccountRequest account : accounts ) {
try {
String nativeIdentity = (String) account.getNativeIdentity();
if ( AccountRequest.Operation.Modify.equals( account.getOperation() ) ) {

				// String nativeIdentity = (String) account.getNativeIdentity();
				log.debug("nativeIdentity is " + nativeIdentity);
				AttributeRequest attrReq = account.getAttributeRequest("PRIV");
				log.debug("attrReq is " + attrReq);
													 
				if ( account != null ) {
					if ( attrReq != null && ProvisioningPlan.Operation.Remove.equals(attrReq.getOperation()) ) {

						List entitlemetsToBeRemoved = new ArrayList();				
						if (attrReq.getValue()!=null && attrReq.getValue() instanceof String) {

							String attrValue = (String) attrReq.getValue();
							entitlemetsToBeRemoved .add(attrValue);

						} else if (attrReq.getValue()!=null && attrReq.getValue() instanceof List) {

							List attrValueList = (List) attrReq.getValue();
							entitlemetsToBeRemoved .addAll(attrValueList);

						}
					  	for (String attval:entitlemetsToBeRemoved){

							String queryStatement = "revoke "+attval+" from "+nativeIdentity;
							log.debug("queryStatement is " + queryStatement);
							PreparedStatement statement = connection.prepareStatement(queryStatement);
							statement.executeUpdate();
							statement.close();

					  	}

					}
				}
				result.setStatus( ProvisioningResult.STATUS_COMMITTED );
				 
			}  else if ( AccountRequest.Operation.Disable.equals( account.getOperation() ) ) {

	 			String accountlock =" alter user " + nativeIdentity  + " account lock password expire";
				PreparedStatement DisableStatement = connection.prepareStatement( accountlock );  
				DisableStatement.executeUpdate();	
				DisableStatement.close();		
				result.setStatus(ProvisioningResult.STATUS_COMMITTED);	

			} else if ( AccountRequest.Operation.Create.equals( account.getOperation() ) ) {

				String charset = "ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz1234567890!@#$%^&*()_+-={}[]:;<>?,./";
                int len = charset.length();
                Random rnd = new Random();
                StringBuilder newPassword = new StringBuilder();

                // Generate one capital letter
                char capitalLetter = charset.charAt(rnd.nextInt(26));
                newPassword.append(capitalLetter);

                // Generate one small letter
                char smallLetter = charset.charAt(26 + rnd.nextInt(26));
                newPassword.append(smallLetter);

                // Generate one symbol
                char symbol = charset.charAt(52 + rnd.nextInt(14));
                newPassword.append(symbol);

                // Generate one number
                char number = charset.charAt(66 + rnd.nextInt(10));
                newPassword.append(number);

                // Generate the remaining characters
                for (int count = 0; count < 8; count++) {
                    char randomChar = charset.charAt(rnd.nextInt(len));
                    newPassword.append(randomChar);
                }

				String createUser = "CREATE USER \"" + nativeIdentity + "\" " +
                "IDENTIFIED BY \"newPassword\" " +
                "TEMPORARY TABLESPACE TEMP2 " +
                "PROFILE TESTING";

        PreparedStatement createUserPst = connection.prepareStatement( createUser );

createUserPst.executeUpdate();

       String createSession = "GRANT CREATE SESSION TO \"" + nativeIdentity + "\"";
       String selectAnyTable = "GRANT SELECT ANY TABLE TO \"" + nativeIdentity + "\"";
       String selectAnyDictionary = "GRANT SELECT ANY DICTIONARY TO \"" + nativeIdentity + "\"";

       PreparedStatement createSessionPst = connection.prepareStatement( createSession );

createSessionPst.executeUpdate();

PreparedStatement selectAnyTablePst = connection.prepareStatement( selectAnyTable );
selectAnyTablePst.executeUpdate();

PreparedStatement selectAnyDictionaryPst = connection.prepareStatement( selectAnyDictionary );
selectAnyDictionaryPst.executeUpdate();

        createUserPst.close();

createSessionPst.close();
selectAnyTablePst.close();
selectAnyDictionaryPst.close();
result.setStatus( ProvisioningResult.STATUS_COMMITTED );

			}
		} catch( SQLException e ) {
			result.setStatus( ProvisioningResult.STATUS_FAILED );
			result.addError( e );
		}
	}
}

}
log.debug(“leaving testJDBCProvision Rule”);

return result;

Thanks

4

Do you mean to use newPassword variable in this string? Here it is being passed as a String “newPassword“ instead of the value from the variable

5

Hi @iamnithesh thank you for your feedback. I have updated the code and could you please provide your feedback on the rule code for account creation block?

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.PreparedStatement;

import java.sql.SQLException;

import java.sql.Types;

import java.util.List;

import sailpoint.api.SailPointContext;

import sailpoint.connector.JDBCConnector;

import sailpoint.object.Application;

import sailpoint.object.ProvisioningPlan;

import sailpoint.object.ProvisioningPlan.AccountRequest;

import sailpoint.object.ProvisioningPlan.AttributeRequest;

import sailpoint.object.ProvisioningPlan.PermissionRequest;

import sailpoint.object.ProvisioningResult;

import sailpoint.object.Schema;

import java.util.Random;

log.debug(“entering EBSDBJDBCProvision Rule”);

ProvisioningResult result = new ProvisioningResult();

if ( plan != null ) {

List accounts = plan.getAccountRequests();

if ( ( accounts != null ) && ( accounts.size() > 0 ) ) {

    for ( AccountRequest account : accounts ) {

        try {

            String nativeIdentity = (String) account.getNativeIdentity();

            if ( AccountRequest.Operation.Modify.equals( account.getOperation() ) ) { 



                // String nativeIdentity = (String) account.getNativeIdentity();

                log.debug("nativeIdentity is " + nativeIdentity);

                AttributeRequest attrReq = account.getAttributeRequest("PRIV");

                log.debug("attrReq is " + attrReq);

                                                     

                if ( account != null ) {

                    if ( attrReq != null && ProvisioningPlan.Operation.Remove.equals(attrReq.getOperation()) ) {



                        List entitlemetsToBeRemoved = new ArrayList();              

                        if (attrReq.getValue()!=null && attrReq.getValue() instanceof String) {



                            String attrValue = (String) attrReq.getValue();

                            entitlemetsToBeRemoved .add(attrValue);



                        } else if (attrReq.getValue()!=null && attrReq.getValue() instanceof List) {



                            List attrValueList = (List) attrReq.getValue();

                            entitlemetsToBeRemoved .addAll(attrValueList);



                        }

                        for (String attval:entitlemetsToBeRemoved){



                            String queryStatement = "revoke "+attval+" from "+nativeIdentity;

                            log.debug("queryStatement is " + queryStatement);

                            PreparedStatement statement = connection.prepareStatement(queryStatement);

                            statement.executeUpdate();

                            statement.close();



                        }



                    }

                }

                result.setStatus( ProvisioningResult.STATUS_COMMITTED );

                 

            }  else if ( AccountRequest.Operation.Disable.equals( account.getOperation() ) ) {



                String accountlock =" alter user " + nativeIdentity  + " account lock password expire";

                PreparedStatement DisableStatement = connection.prepareStatement( accountlock );  

                DisableStatement.executeUpdate();   

                DisableStatement.close();       

                result.setStatus(ProvisioningResult.STATUS_COMMITTED);  



            } else if ( AccountRequest.Operation.Create.equals( account.getOperation() ) ) {



                String charset = "ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz1234567890!@#$%^&\*()\_+-={}\[\]:;<>?,./";

                int len = charset.length();

                Random rnd = new Random();

                StringBuilder newPassword = new StringBuilder();



                // Generate one capital letter

                char capitalLetter = charset.charAt(rnd.nextInt(26));

                newPassword.append(capitalLetter);



                // Generate one small letter

                char smallLetter = charset.charAt(26 + rnd.nextInt(26));

                newPassword.append(smallLetter);



                // Generate one symbol

                char symbol = charset.charAt(52 + rnd.nextInt(14));

                newPassword.append(symbol);



                // Generate one number

                char number = charset.charAt(66 + rnd.nextInt(10));

                newPassword.append(number);



                // Generate the remaining characters

                for (int count = 0; count < 8; count++) {

                    char randomChar = charset.charAt(rnd.nextInt(len));

                    newPassword.append(randomChar);

                }



                

                String createUser = "CREATE USER \\"" + nativeIdentity + "\\" "

       + "IDENTIFIED BY \\"" + newPassword + "\\" "

       + "TEMPORARY TABLESPACE TEMP2 "

       + "PROFILE testing";




        PreparedStatement createUserPst = connection.prepareStatement( createUser );

createUserPst.executeUpdate();

       String createSession = "GRANT CREATE SESSION TO \\"" + nativeIdentity + "\\"";

       String selectAnyTable = "GRANT SELECT ANY TABLE TO \\"" + nativeIdentity + "\\"";

       String selectAnyDictionary = "GRANT SELECT ANY DICTIONARY TO \\"" + nativeIdentity + "\\"";



       PreparedStatement createSessionPst = connection.prepareStatement( createSession );

createSessionPst.executeUpdate();

PreparedStatement selectAnyTablePst = connection.prepareStatement( selectAnyTable );

selectAnyTablePst.executeUpdate();

PreparedStatement selectAnyDictionaryPst = connection.prepareStatement( selectAnyDictionary );

selectAnyDictionaryPst.executeUpdate();

        createUserPst.close();

createSessionPst.close();

selectAnyTablePst.close();

selectAnyDictionaryPst.close();

result.setStatus( ProvisioningResult.STATUS_COMMITTED );

            }

        } catch( SQLException e ) {

            result.setStatus( ProvisioningResult.STATUS_FAILED );

            result.addError( e );

        }

    }

}

}

log.debug(“leaving EBSDBJDBCProvision Rule”);

return result;

6

I have updated the Markdown for your code to make it more readable. It is a good idea to make sure that your code blocks show up correctly to help others view it. Also consider removing extra spaces so it shows up on the screen better.

I was going to suggest the Provisioning Rule for adding your queries, as you should be able to make multiple calls in the Create section.

What is the current issue you are having with this code? What errors are you getting, if any?

1 Like
7

Thanks to @gmilunich for cleaning up the code :slight_smile:

I do not see any issues with your code, but would run the SQL queries directly to confirm the intended process runs

8

Hi @iamnithesh and @gmilunich thank you.

Getting below error while requesting through Request Center with the above code.

Request failed

An unexpected error occurred: java.lang.StringIndexOutOfBoundsException: begin 0, end 8, length 5

  1. Do we need to create a provisioning policy for create account through Postman or from UI do we need to define create account policy and how it look like?
  2. What kind of permission does service account need to have to execute these SQL statements?
  3. The above SQL statements are provided by DB team.

Thanks

9

SQL Statements you are executing seem to need ADMIN privileges in the DB Server. However, this is the question you need to ask your DB team.

Please read Connector documentation for these details.

10

I added the formatting to the error code returned to make it more obvious.

The error code seems to show you have an index error in your code somewhere. Given it shows the bounds to be 0 to 8, with a length of 5, that would lead me to the For Loop, however there are no issues visible in the code you provided. It could be in other areas if you are doing additional work.

I agree with everything stated here by @iamnithesh in that you should be working with the DB team for permissions. For Oracle, creating table space accounts is different than standard inputs, and @iamnithesh mentioned the Oracle DB connector, which I believe is specifically set up for handling those types of accounts. Review the documentations for that connector and review the requirements you have. If you are managing just these accounts in oracle, you may need to switch to the Oracle Connector. If you need to do additional work, then you’ll need to work out the Permissions with your DB Team.

1 Like
11

Hi @gmilunich and @iamnithesh thank you for your feedback and will post for any other issues/details on this.

Thanks

12

Hi @iamnithesh and @gmilunich and everyone,

How to send an email to the user with temporary password created/set in the JDBC provisioning rule in the above code during an account creation in the jdbc provisioning rule if the temporary password created/set is not part an account schema attribute?

Thanks

13

If you are okay with making this password visible to admins in ISC, then you can create an identity attribute for this password and then use a workflow that triggers on account creation and sends the email

14

Hi @Kalyana,

It is not a recommended practise to store password in the Account Schema attribute and share it. What you can do is generate a Know Dynamic Password by making the use of First Name, Last Name, Email and adding some ASCII characters to it and then using the same format in the Provisioning Completed Workflow to share it with the end user. This way you will not be storing it in any attribute and it wont cause any compliance issue.

thanks

15

Hi @RAKRHEEM thank you and could you please provide the code for the same?

Thanks

16

As another option if you are creating the password in the JDBC Provisioning rule, then you can set up a workflow with external trigger and call the same from inside your JDBC Provisioning rule passing the recipient info as well as the password created as input parameters to the workflow. Then you can send the same via an email from within the workflow