Provisioning Completed Workflow sample code for JDBC

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$”;

Generate a Password with 20+ characters complex one with Combination of Alpha Numaric + Special Character ( # ) to it and then using the same format in the Provisioning Completed Workflow to share it with the end user.

Thanks

2

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

3

Hi @iamology and everyone,configured below workflow for this and I assume I am missing some configuration and could you please check the workflow?

{
“name”: “Notify User - test DB Account Created”,
“description”: “Send email notification when test DB account is created”,
“definition”: {
“start”: “Get Identity”,
“steps”: {
“Get Identity”: {
“actionId”: “sp:get-identity”,
“attributes”: {
“id.$”: “$.trigger.identity.id”
},
“nextStep”: “Send Email”,
“type”: “action”,
“versionNumber”: 2
},
“Send Email”: {
“actionId”: “sp:send-email”,
“attributes”: {
“body”: “Hello {{$.getIdentity.displayName}},Your Oracle test4567 DB account has been successfully created.Source: Oracle test4567 - JDBC DV3You can now log in to corporate systems using your test4567 DB credentials.Regards,
IAM Team”,
“from”: “no-reply-stage@identity.test.com”,
“recipientEmailList.$”: “$.getIdentity.emailAddress”,
“subject”: “Your Oracle Oracle test - JDBC Account is Ready”
},
“nextStep”: “Success”,
“type”: “action”,
“versionNumber”: 2
},
“Success”: {
“actionId”: “sp:operator-success”,
“type”: “success”
}
}
},
“trigger”: {
“type”: “EVENT”,
“attributes”: {
“filter.$”: “$.accountRequests[?(@.accountOperation == ‘Create’ && @.source.name == ‘Oracle test - JDBC’ && @.provisioningResult == ‘committed’)]”,
“id”: “idn:post-provisioning”
}
}
}

Thanks

4

sp:send-email expects a list, but the workflow is passing a single string. shouldn’t it be this “recipientEmailList”: [“{{$.getIdentity.emailAddress}}”]

5

Hi @iamology and @BBR1 and everyone,

Here we would like to use external trigger worfklow to send the temporary password created in JDBC provisioning rule to the end user.

Please refer to the below rule code and workflow code and provide your feedback.

Please refer the below code for JDBC create account operation:

final int PASSWORD_LENGTH = 24;

String upperChars = “ABCDEFGHIJKLMNOPQRSTUVWXYZ”;
String lowerChars = “abcdefghijklmnopqrstuvwxyz”;
String digits = “0123456789”;
String specialChars = “!@#$%&*()-_=+{};:,.<>?”;

String allChars = upperChars + lowerChars + digits + specialChars;
Random rnd = new Random();

// Build password with guaranteed complexity
StringBuilder password = new StringBuilder(PASSWORD_LENGTH);

// 1. Uppercase
password.append(upperChars.charAt(rnd.nextInt(upperChars.length())));

// 2. Lowercase
password.append(lowerChars.charAt(rnd.nextInt(lowerChars.length())));

// 3. Digit
password.append(digits.charAt(rnd.nextInt(digits.length())));

// 4. Special
password.append(specialChars.charAt(rnd.nextInt(specialChars.length())));

// 5. Fill the rest — ensuring total length >= PASSWORD_LENGTH
for (int i = 4; i < PASSWORD_LENGTH; i++) {
password.append(allChars.charAt(rnd.nextInt(allChars.length())));
}

// Shuffle characters for randomness
char pwdArray = password.toString().toCharArray();
for (int i = 0; i < pwdArray.length; i++) {
int randomPos = rnd.nextInt(pwdArray.length);
char temp = pwdArray[i];
pwdArray[i] = pwdArray[randomPos];
pwdArray[randomPos] = temp;
}

String newPassword = new String(pwdArray);

// BUILD SQL
String createUser =
“CREATE USER “” + nativeIdentity + “” IDENTIFIED BY “” + newPassword +
“” TEMPORARY TABLESPACE TEMP2 PROFILE test”;

PreparedStatement ps = null;
try {
ps = connection.prepareStatement(createUser);
ps.executeUpdate();
} finally {
if (ps != null) ps.close();
}

// Grant CREATE SESSION
ps = null;
try {
ps = connection.prepareStatement(
“GRANT CREATE SESSION TO “” + nativeIdentity + “””
);
ps.executeUpdate();
} finally {
if (ps != null) ps.close();
}

// Grant SELECT ANY TABLE
ps = null;
try {
ps = connection.prepareStatement(
“GRANT SELECT ANY TABLE TO “” + nativeIdentity + “””
);
ps.executeUpdate();
} finally {
if (ps != null) ps.close();
}

// Grant SELECT ANY DICTIONARY
ps = null;
try {
ps = connection.prepareStatement(
“GRANT SELECT ANY DICTIONARY TO “” + nativeIdentity + “””
);
ps.executeUpdate();
} finally {
if (ps != null) ps.close();
}

result.setStatus(ProvisioningResult.STATUS_COMMITTED);

String workflowId = “1234”;

String email = getAttributeRequestValue(account, “email”);

String workflowUrl = “https://sailpoint.api.identitynow.com/v2024/workflows/”

  • workflowId + “/test”;

URL url = new URL(workflowUrl);
HttpURLConnection conn = (HttpURLConnection) url.openConnection();

conn.setRequestMethod(“POST”);
conn.setDoOutput(true);

conn.setRequestProperty(“Content-Type”, “application/json”);

String jsonInput = “{”

  • ““input”:{”
  • ““email”:“” + email + “”,”
  • ““newPassword”:“” + newPassword + “”,”
  • ““nativeIdentity”:“” + nativeIdentity + “””
  • “}”
  • “}”;

OutputStream os = conn.getOutputStream();
os.write(jsonInput.getBytes(“UTF-8”));
os.flush();
os.close();

int responseCode = conn.getResponseCode();
log.info("Workflow Response Code: " + responseCode);

Workflow code:

{
“name”: “EBS DB Email Notify”,
“description”: “EBS DB Email Notify”,
“definition”: {
“start”: “Send Email”,
“steps”: {
“Send Email”: {
“actionId”: “sp:send-email”,
“attributes”: {
“body”: “Hello {{$.trigger.nativeIdentity}},\nYour Oracle test - JDBC DV3 DB account has been successfully created.\nSource: Oracle test - JDBC DV3 - JDBC DV3\nYou can now log in to corporate systems using your test - JDBC DV3 DB credentials $.trigger.newPassword and change password.\nRegards,
IAM Team”,
“context”: {},
“from”: “no-reply-stage@identity.test4567.com”,
“recipientEmailList.$”: “$.trigger.email”,
“subject”: “Your Oracle Oracle test - JDBC DV3 Account is Ready”
},
“displayName”: “”,
“type”: “action”,
“versionNumber”: 2
}
}
},
“trigger”: {
“type”: “EXTERNAL”,
“attributes”: {
“id”: “idn:external-http”
}
}
}

Thanks