Can someone share sample logic for provisioning rule that can handle multiple entitlements assignments to a user. I’m able to insert one record for a user with entitlement ‘A’ into DB using the provisioning rule, but wanting to know how to insert entitlement ‘B’ for the same user in that table.
In your rule once you have created the record using entitlement ‘A’ to add a new entitlement ‘B’ you need to do a update query it would not be an insert query because the user record is already created.
You can do a select query and get the record PK. Use this PK in the update query and add the next entitlement.
These flow you can control using plan operations.
Thank you @udayputta ! So, I have a scenario where user needs access to multiple organizations/roles. For this reason I will need to insert multiple user records with different entitlements. How can I achieve this?
It depends on your table structure. Does your table allow to insert multiple records with same user details. Usually table will have single entry for users and a seperate column to store roles. Sometimes same table will hold single entry for users and a column to store multiple roles. So, validate this once and based on that you need to design your code to achieve various use cases.
Hello @udayputta , yes our table allows multiple records with same user details.So, I’m wondering, if there is a sample code for provisioning rule to achieve this kind of use case.
You can refer this rule. I believe you will get most of it.
You also need to add this additional check in Modify request
requestedItemRoles = account.getAttributeRequests("Roles");
if(requestedItemRoles != null && !requestedItemRoles.isEmpty()) {
for(AttributeRequest itemRole : requestedItemRoles)
{
if(((itemRole.getOp()).equals(ProvisioningPlan.Operation.Add)))
{
<Perform the insert operation and update the results>
} else if(((itemRole.getOp()).equals(ProvisioningPlan.Operation.Remove))) {
<Add the logic to remove or delete the entry from your table>
}
}
}
Hello @udayputta,
I already implemented using this reference and able to insert single user details. When I insert another record for same user details, it is going to modify operation instead of Add operation and not inserting into the table.
okay I understand the problem. When the new account is created ISC might be correlating the account to identity and the next request would be a modify request. What are your correlation attributes. When you create new records for same user how are you going to tie all of the records to same identity. I think you need to have a unique attribute for each of the record that you insert. if possible can you share some sample examples from both your table and from ISC
requestedItemRoles = account.getAttributeRequests(“Roles”);
if(requestedItemRoles != null && !requestedItemRoles.isEmpty()) {
for(AttributeRequest itemRole : requestedItemRoles)
{
if(((itemRole.getOp()).equals(ProvisioningPlan.Operation.Add)))
{
} else if(((itemRole.getOp()).equals(ProvisioningPlan.Operation.Remove))) {
}
}
}
Sorry, I think this piece of code is not in my provisioning rule, I will add this and test it. Will let you know once I do this. Thanks again!
Hello @udayputta , Here is the sample code I have tried, but not able to insert multiple records
hi @keerthiP7,
In the modify - Add block you are creating a new PreparedStatement object but you are using previous prepared statement object (statement) instead of statement_2 to set the values. Can you update that and try it once. Also your getAttributeRequestValue method is returning null. I hope you are passing the correct value instead of null.
Add the loggers and validate if the data is going as expected and correct block is getting called.
Hello @keerthiP7
Good day!
Please check the giving attribute name in bellow the Screenshot, you are using wrong variable to set the values.
Please use the statement_2.setString(1, (String) account.getNativeIdentity().toUpperCase());
Thank you
Hi @udayputta , @vijayasaikoppineni , Sorry for the confusion, that’s a copy paste mistake while trying to remove original table and column names. But I have been using statement_2 to setString.
And I’m getting this error when I try to insert second record for the same user with different entitlement.
“com.ibm.db2.jcc.am.SqlIntegrityConstraintViolationException: DB2 SQL Error: SQLCODE=-407”
@udayputta , this is the full method for getAttributeRequestValue
Keerthi, this error means you cannot have either foreign key, primary key or any other unique key if any column is using cannot be reused. Are you sure you can insert multiple records. If not you have use update statement and update the entitlements. Try this out if you have access to the table insert multiple records with same user details but different entitlements.
Hi @uday, yes I’m able to insert multiple records into table manually.
1 Like
Hi @keerthiP7,
Good Day!
Could you please try to use the below code for assigning the role:-
//Modify Operation
else if ( AccountRequest.Operation.Modify.equals( account.getOperation() ) )
{
// Modify account request – Update the user information
PreparedStatement updateStatement = connection.prepareStatement(==========query====== );
updateStatement.setString ( 1, (String) account.getNativeIdentity() );
updateStatement.setString ( 2, getAttributeRequestValue(account,"First Name") );
//**For removal of ROLE**
PreparedStatement RemoveRoleStatement = connection.prepareStatement(========query======);
RemoveRoleStatement.setString ( 1, (String) account.getNativeIdentity() );
//**For assigning the role**
PreparedStatement AddRoleStatement = connection.prepareStatement(=========query to add role=======);
AddRoleStatement.setString ( 1, (String) account.getNativeIdentity() );
//for removing the role
if ( account != null ) {
AttributeRequest attrReq = account.getAttributeRequest("ROLE");
if ( attrReq != null && ProvisioningPlan.Operation.Remove.equals(attrReq.getOperation()) ) {
RemoveRoleStatement.setString ( 2, attrReq.getValue() );
RemoveRoleStatement.executeQuery();
result.setStatus( ProvisioningResult.STATUS_COMMITTED );
}
// **Adding the role**
else if ( attrReq != null && ProvisioningPlan.Operation.Add.equals(attrReq.getOperation()) ) {
//Here 2 is in which Paramenter you want to add the role.
AddRoleStatement.setString(2,attrReq.getValue());
AddRoleStatement.executeUpdate();
result.setStatus( ProvisioningResult.STATUS_COMMITTED );
}
else
{
updateStatement.executeUpdate();
updateStatement.close();
result.setStatus( ProvisioningResult.STATUS_COMMITTED );
}
}
}
2 Likes
Thank you all @Abhishek_1995 , @udayputta , @vijayasaikoppineni for the replies. Unfortunately, none of the above solutions worked for me. However, my requirements have changed a little bit now. I need to insert multiple rows for same user , with same entitlement but different value in ‘Rcolumn’ which describes security level. Something similar to this.
user entitlement Rcolumn
testuser1 ABC TESTER
testuser1 ABC DEVELOPER
So, I tried with below changes, but the second row is inserting multiple times when I run aggregation. Not sure what options I can try.
-Made ‘Rcolumn’ as multi-valued in account schema in IDN
-Updated rule to this
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;
public String getAttributeRequestValue(AccountRequest acctReq, String attribute) {
if ( acctReq != null ) {
AttributeRequest attrReq = acctReq.getAttributeRequest(attribute);
if ( attrReq != null ) {
return attrReq.getValue();
}
}
return null;
}
ProvisioningResult result = new ProvisioningResult();
PreparedStatement statement;
if ( plan != null ) {
List accounts = plan.getAccountRequests();
if ( ( accounts != null ) && ( accounts.size() > 0 ) ) {
for ( AccountRequest account : accounts ) {
try {
if ( AccountRequest.Operation.Create.equals( account.getOperation() ) ) {
// Ideally we should first check to see if the account already exists.
// As written, this just assumes it does not.
statement = connection.prepareStatement("INSERT INTO TEST_TABLE(ID,COL_2,COL_3,COL_4,COL_5,ROLE) values (?,?,?,?,?,?)");
statement.setString(1, (String) account.getNativeIdentity().toUpperCase());
statement.setString(2, getAttributeRequestValue(account, "COL_2"));
statement.setString(3, getAttributeRequestValue(account, "COl_3"));
statement.setString(4, getAttributeRequestValue(account, "COL_4"));
statement.setString(5, getAttributeRequestValue(account, "COL_5"));
statement.setString(6, getAttributeRequestValue(account, "ROLE"));
statement.executeUpdate();
result.setStatus(ProvisioningResult.STATUS_COMMITTED);
} else if ( AccountRequest.Operation.Modify.equals( account.getOperation() ) ) {
// Modify account request -- change role
if ( account != null ) {
AttributeRequest attrReq = account.getAttributeRequest("ROLE");
if ( attrReq != null && ProvisioningPlan.Operation.Remove.equals(attrReq.getOperation()) ) {
PreparedStatement statementNew = connection.prepareStatement("DELETE FROM TEST_TABLE WHERE UPPER(RTRIM(ID)) = ?");
statementNew.setString(1, (String) account.getNativeIdentity().toUpperCase());
statementNew.executeUpdate();
}
else if ( attrReq != null && ProvisioningPlan.Operation.Add.equals(attrReq.getOperation()) ) {
statement = connection.prepareStatement("UPDATE TEST_TABLE SET COL_2 = ?, COl_3 = ?, COL_4 =?, COL_5=?, ROLE =? WHERE ID = ?");
statement.setString(1, getAttributeRequestValue(account, "COL_2"));
statement.setString(2, getAttributeRequestValue(account, "COl_3"));
statement.setString(3, getAttributeRequestValue(account, "COL_4"));
statement.setString(4, getAttributeRequestValue(account, "COL_5"));
statement.setString(5, getAttributeRequestValue(account, "ROLE"));
statement.setString(6, (String) account.getNativeIdentity().toUpperCase());
statement.executeUpdate();
}
}
result.setStatus( ProvisioningResult.STATUS_COMMITTED );
} else if ( AccountRequest.Operation.Delete.equals( account.getOperation() ) ) {
} else if ( AccountRequest.Operation.Disable.equals( account.getOperation() ) ) {
// Disable, not supported.
} else if ( AccountRequest.Operation.Enable.equals( account.getOperation() ) ) {
// Enable, not supported.
} else if ( AccountRequest.Operation.Lock.equals( account.getOperation() ) ) {
// Lock, not supported.
} else if ( AccountRequest.Operation.Unlock.equals( account.getOperation() ) ) {
// Unlock, not supported.
} else {
// Unknown operation!
}
}
catch( SQLException e ) {
result.setStatus( ProvisioningResult.STATUS_FAILED );
result.addError( e );
}
finally {
if(statement != null) {
statement.close();
}
}
}
}
}
return result;
If anyone has any suggestions, please let me know. Thank you!
@keerthiP7 -
The problem you’re experiencing—where the second row is inserting multiple times during aggregation—is likely due to how multi-valued attributes are being handled in your code and the database. When you set Rcolumn as multi-valued in the account schema and run aggregation, IdentityNow may process each value separately, leading to multiple insertions. Without proper checks in place, this can result in duplicate entries.
Possible Causes:
- Lack of Uniqueness Constraints: Your database table may not have a primary key or unique constraint that prevents duplicate rows with the same
ID,COL_2,COL_3,COL_4,COL_5, andROLE. - Improper Handling of Multi-Valued Attributes: The code might not correctly handle multi-valued attributes, causing it to insert a new row for each value without checking for existing entries.
To resolve this issue, you need to:
- Ensure Database Constraints Prevent Duplicates.
- Modify the Code to Handle Multi-Valued Attributes Properly.
- Implement Checks Before Inserting Data.
2. Modify the Code to Handle Multi-Valued Attributes Properly
Your current code doesn’t handle multi-valued attributes for ROLE. You need to modify it to iterate over each value in the multi-valued attribute.
Update getAttributeRequestValue Function
First, adjust the getAttributeRequestValue function to return a list of values when the attribute is multi-valued:
public List getAttributeRequestValues(AccountRequest acctReq, String attribute) {
List values = new ArrayList();
if (acctReq != null) {
List attrReqs = acctReq.getAttributeRequests(attribute);
if (attrReqs != null) {
for (AttributeRequest attrReq : attrReqs) {
Object value = attrReq.getValue();
if (value instanceof List) {
values.addAll((List) value);
} else {
values.add((String) value);
}
}
}
}
return values;
}
Modify the Insert Logic for Multi-Valued Attributes
In the create operation, iterate over each value of the multi-valued ROLE attribute and insert a row for each one:
if (AccountRequest.Operation.Create.equals(account.getOperation())) {
List roles = getAttributeRequestValues(account, "ROLE");
for (String role : roles) {
statement = connection.prepareStatement(
"INSERT INTO TEST_TABLE(ID, COL_2, COL_3, COL_4, COL_5, ROLE) VALUES (?, ?, ?, ?, ?, ?)");
statement.setString(1, account.getNativeIdentity().toUpperCase());
statement.setString(2, getAttributeRequestValue(account, "COL_2"));
statement.setString(3, getAttributeRequestValue(account, "COL_3"));
statement.setString(4, getAttributeRequestValue(account, "COL_4"));
statement.setString(5, getAttributeRequestValue(account, "COL_5"));
statement.setString(6, role);
statement.executeUpdate();
}
result.setStatus(ProvisioningResult.STATUS_COMMITTED);
}
Modify the Update Logic for Multi-Valued Attributes
For modify operations, handle add and remove separately:
else if (AccountRequest.Operation.Modify.equals(account.getOperation())) {
List attrReqs = account.getAttributeRequests("ROLE");
if (attrReqs != null) {
for (AttributeRequest attrReq : attrReqs) {
List roles = new ArrayList();
Object value = attrReq.getValue();
if (value instanceof List) {
roles.addAll((List) value);
} else {
roles.add((String) value);
}
if (ProvisioningPlan.Operation.Remove.equals(attrReq.getOperation())) {
// Delete roles from the table
for (String role : roles) {
PreparedStatement statementNew = connection.prepareStatement(
"DELETE FROM TEST_TABLE WHERE UPPER(RTRIM(ID)) = ? AND ROLE = ?");
statementNew.setString(1, account.getNativeIdentity().toUpperCase());
statementNew.setString(2, role);
statementNew.executeUpdate();
statementNew.close();
}
} else if (ProvisioningPlan.Operation.Add.equals(attrReq.getOperation())) {
// Insert new roles into the table
for (String role : roles) {
statement = connection.prepareStatement(
"INSERT INTO TEST_TABLE(ID, COL_2, COL_3, COL_4, COL_5, ROLE) VALUES (?, ?, ?, ?, ?, ?)");
statement.setString(1, account.getNativeIdentity().toUpperCase());
statement.setString(2, getAttributeRequestValue(account, "COL_2"));
statement.setString(3, getAttributeRequestValue(account, "COL_3"));
statement.setString(4, getAttributeRequestValue(account, "COL_4"));
statement.setString(5, getAttributeRequestValue(account, "COL_5"));
statement.setString(6, role);
statement.executeUpdate();
}
}
}
}
result.setStatus(ProvisioningResult.STATUS_COMMITTED);
}
3. Implement Checks Before Inserting Data
Before inserting a new row, check if the record already exists to prevent duplicates. You can do this by attempting the insert and handling a SQL exception due to a primary key violation, or by performing a select query first.
Option 1: Handle SQL Exception
try {
statement.executeUpdate();
} catch (SQLException e) {
if (e.getErrorCode() == /* Duplicate entry error code */) {
// Record already exists, you might want to update it or ignore
} else {
throw e; // Rethrow exception if it's not a duplicate entry
}
}
Option 2: Check Before Insert
PreparedStatement checkStmt = connection.prepareStatement(
"SELECT COUNT(*) FROM TEST_TABLE WHERE ID = ? AND ROLE = ?");
checkStmt.setString(1, account.getNativeIdentity().toUpperCase());
checkStmt.setString(2, role);
ResultSet rs = checkStmt.executeQuery();
rs.next();
int count = rs.getInt(1);
rs.close();
checkStmt.close();
if (count == 0) {
// Proceed with insert
statement.executeUpdate();
}
Here’s how your adjusted code look:
// ... [imports and getAttributeRequestValues function as above] ...
ProvisioningResult result = new ProvisioningResult();
if (plan != null) {
List accounts = plan.getAccountRequests();
if (accounts != null && !accounts.isEmpty()) {
for (AccountRequest account : accounts) {
try {
String nativeIdentity = account.getNativeIdentity().toUpperCase();
if (AccountRequest.Operation.Create.equals(account.getOperation())) {
List roles = getAttributeRequestValues(account, "ROLE");
for (String role : roles) {
// Check if record exists
PreparedStatement checkStmt = connection.prepareStatement(
"SELECT COUNT(*) FROM TEST_TABLE WHERE ID = ? AND ROLE = ?");
checkStmt.setString(1, nativeIdentity);
checkStmt.setString(2, role);
ResultSet rs = checkStmt.executeQuery();
rs.next();
int count = rs.getInt(1);
rs.close();
checkStmt.close();
if (count == 0) {
// Insert new record
statement = connection.prepareStatement(
"INSERT INTO TEST_TABLE(ID, COL_2, COL_3, COL_4, COL_5, ROLE) VALUES (?, ?, ?, ?, ?, ?)");
statement.setString(1, nativeIdentity);
statement.setString(2, getAttributeRequestValue(account, "COL_2"));
statement.setString(3, getAttributeRequestValue(account, "COL_3"));
statement.setString(4, getAttributeRequestValue(account, "COL_4"));
statement.setString(5, getAttributeRequestValue(account, "COL_5"));
statement.setString(6, role);
statement.executeUpdate();
statement.close();
}
}
result.setStatus(ProvisioningResult.STATUS_COMMITTED);
}
else if (AccountRequest.Operation.Modify.equals(account.getOperation())) {
List attrReqs = account.getAttributeRequests("ROLE");
if (attrReqs != null) {
for (AttributeRequest attrReq : attrReqs) {
List<String> roles = new ArrayList<>();
Object value = attrReq.getValue();
if (value instanceof List) {
roles.addAll((List) value);
} else {
roles.add((String) value);
}
if (ProvisioningPlan.Operation.Remove.equals(attrReq.getOperation())) {
// Delete roles
for (String role : roles) {
PreparedStatement delStmt = connection.prepareStatement(
"DELETE FROM TEST_TABLE WHERE UPPER(RTRIM(ID)) = ? AND ROLE = ?");
delStmt.setString(1, nativeIdentity);
delStmt.setString(2, role);
delStmt.executeUpdate();
delStmt.close();
}
} else if (ProvisioningPlan.Operation.Add.equals(attrReq.getOperation())) {
// Insert new roles
for (String role : roles) {
// Check if record exists
PreparedStatement checkStmt = connection.prepareStatement(
"SELECT COUNT(*) FROM TEST_TABLE WHERE ID = ? AND ROLE = ?");
checkStmt.setString(1, nativeIdentity);
checkStmt.setString(2, role);
ResultSet rs = checkStmt.executeQuery();
rs.next();
int count = rs.getInt(1);
rs.close();
checkStmt.close();
if (count == 0) {
// Insert new record
statement = connection.prepareStatement(
"INSERT INTO TEST_TABLE(ID, COL_2, COL_3, COL_4, COL_5, ROLE) VALUES (?, ?, ?, ?, ?, ?)");
statement.setString(1, nativeIdentity);
statement.setString(2, getAttributeRequestValue(account, "COL_2"));
statement.setString(3, getAttributeRequestValue(account, "COL_3"));
statement.setString(4, getAttributeRequestValue(account, "COL_4"));
statement.setString(5, getAttributeRequestValue(account, "COL_5"));
statement.setString(6, role);
statement.executeUpdate();
statement.close();
}
}
}
}
}
result.setStatus(ProvisioningResult.STATUS_COMMITTED);
}
// Handle other operations...
} catch (SQLException e) {
result.setStatus(ProvisioningResult.STATUS_FAILED);
result.addError(e);
} finally {
// Close any open resources if not already closed
}
}
}
}
return result;
Hope this helps!
Thank you!
1 Like
Thank you @officialamitguptaa , I will try this out and let you know.
Hi @officialamitguptaa , I’m not able to insert second row with the above code. I don’t have access to server logs to see what exactly happened. However I want to clarify this, I want to insert rows into DB like below:
ID | ROLE(Entitlement) | Rcolumn
testID ABC TESTER
testID ABC DEVELOPER
And every row is a separate request. That means user should be able to raise request for same ID, with same ROLE but different Rcolumn. We are passing only single value for each field in every request. I tried different ways but didn’t work, so I made Rcolumn as multi-valued but had issue with multiple rows getting inserted. The DB should be able to have rows like I mentioned above and every request is sent with single string value not list of values. How can I achieve this? Is making Rcolumn multi-valued a right approach?
Please let me know if anyone has any suggestions.
Thanks in advance!!