Hi Team,
I am working on jdbc provisioning rule.
This is my first time working on it need some direction below is the creation of account store proc can someone tell me how can this be integrated in the rule so that i can draft rest all the operations accordingly.
Please note the sugvtte i have is native identity value
EXEC AppSource.dbo.genericsCreateAccount @loginID = ‘NAMCK\sugvtte’, @acctName = ‘sugvtte’, @email = ‘[email protected]’, @firstName = ‘test1’, @lastName = ‘test2’
Since this is your first time working on a JDBC Provisioning Rule, I’ll guide you through integrating your stored procedure for account creation, deploying the rule to ISC, and attaching it to your JDBC source.
Here’s how to integrate your stored procedure’s:
Example:
import java.sql.Connection;
import java.sql.CallableStatement;
import java.sql.SQLException;
import java.util.List;
import sailpoint.object.ProvisioningPlan;
import sailpoint.object.ProvisioningPlan.AccountRequest;
import sailpoint.object.ProvisioningPlan.AttributeRequest;
import sailpoint.object.ProvisioningResult;
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();
CallableStatement statement = null;
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())) {
// Prepare the stored procedure call
String procCall = "{call AppSource.dbo.genericsCreateAccount(?,?,?,?,?)}";
statement = connection.prepareCall(procCall);
// Set parameters based on your stored procedure
String nativeIdentity = (String) account.getNativeIdentity();
statement.setString(1, "NAMCK\\" + nativeIdentity); // @loginID
statement.setString(2, nativeIdentity); // @acctName
statement.setString(3, getAttributeRequestValue(account, "email"));
statement.setString(4, getAttributeRequestValue(account, "firstName"));
statement.setString(5, getAttributeRequestValue(account, "lastName"));
// Execute the stored procedure
statement.execute();
// Mark the operation as successful
result.setStatus(ProvisioningResult.STATUS_COMMITTED);
} else if (AccountRequest.Operation.Modify.equals(account.getOperation())) {
// for Modify logic
log.debug("Modify operation not yet implemented for: " + account.getNativeIdentity());
} else if (AccountRequest.Operation.Delete.equals(account.getOperation())) {
// for Delete logic
log.debug("Delete operation not yet implemented for: " + account.getNativeIdentity());
} else {
log.debug("Unsupported operation: " + account.getOperation());
}
} catch (SQLException e) {
result.setStatus(ProvisioningResult.STATUS_FAILED);
result.addError(e);
log.error("Provisioning failed: " + e.getMessage());
} finally {
if(statement != null) {
statement.close();
}
}
}
}
}
return result;
2: Deploying the Rule to ISC
refer Deploying Connector Rules.
3: Attaching the Rule to Your JDBC Source
refer Attaching the JDBCProvision Rule for more on patching sources.
Hope this helps!
How did you get these attribute names?
email
firstName etc how can i know what all are available
also for next stored proc grant access i need rolename and roleid how to get that in the code?
The attribute names like email, firstName, etc., typically come from the ProvisioiningPolicy defined for your JDBC source. for create operation use can use Create Profile.
If you’re still unsure, this video JDBC Connectors in ISC - Watch This Before Deploying walks through onboarding a JDBC source and setting up the schema—definitely worth a watch for clarity!
Getting rolename and roleid for Stored Procedure Grant Access
For your next use case with the stored procedure to grant access, I assume roleid is an entitlement type (like a role or group in your system), and rolename is its readable name. To fetch these in your code in a Provisioning Rule, you can extract them from the provisioning plan’s account requests.
Sample code snippet based on the Before Provisioning Rule guide IdentityNow Rule Guide - Before Provisioning Rule
Thanks @kalyan_dev32 ,
The video was helpful.
this is the add access stored proc
EXEC AppSource.dbo.genericsGrantAccess @acctName = ‘sugvtte’, @entID = ‘268’, @entName = ‘Admin (Security)’;
here i need in @entName i need name and @entid i need id
this is how the role is defined
both the create account and add access should run in the create operation.
How can this be done
refer sample code
List accounts = plan.getAccountRequests();
if (accounts != null && accounts.size() > 0) {
for (AccountRequest account : accounts) {
try {
if (AccountRequest.Operation.Create.equals(account.getOperation())) {
// Prepare and execute the account creation stored procedure
String procCall = "{call AppSource.dbo.genericsCreateAccount(?,?,?,?,?)}";
statement = connection.prepareCall(procCall);
String nativeIdentity = (String) account.getNativeIdentity();
statement.setString(1, "NAMCK\\" + nativeIdentity); // @loginID
statement.setString(2, nativeIdentity); // @acctName
statement.setString(3, getAttributeRequestValue(account, "email"));
statement.setString(4, getAttributeRequestValue(account, "firstName"));
statement.setString(5, getAttributeRequestValue(account, "lastName"));
statement.execute();
// Process entitlement additions
List attributeRequests = account.getAttributeRequests();
if (attributeRequests != null && !attributeRequests.isEmpty()) {
String procCallAccess = "{call AppSource.dbo.genericsGrantAccess(?,?,?)}";
for (AttributeRequest attributeRequest : attributeRequests) {
if ("Add".equals(attributeRequest.getOp())) {
// Close previous statement and create new one for access grant
if (statement != null) {
statement.close();
}
statement = connection.prepareCall(procCallAccess);
statement.setString(1, nativeIdentity); // @acctName
Object entIdValue = attributeRequest.getValue();
statement.setString(2, (String) entIdValue); // @entID
statement.setString(3, attributeRequest.getDisplayValue()); // @entName
statement.execute();
}
}
}
result.setStatus(ProvisioningResult.STATUS_COMMITTED);
} else if (AccountRequest.Operation.Modify.equals(account.getOperation())) {
// for Modify logic
log.debug("Modify operation not yet implemented for: " + account.getNativeIdentity());
} else if (AccountRequest.Operation.Delete.equals(account.getOperation())) {
// for Delete logic
log.debug("Delete operation not yet implemented for: " + account.getNativeIdentity());
} else {
log.debug("Unsupported operation: " + account.getOperation());
}
} catch (SQLException e) {
result.setStatus(ProvisioningResult.STATUS_FAILED);
result.addError(e);
log.error("Provisioning failed: " + e.getMessage());
} finally {
if (statement != null) {
statement.close();
}
}
}
}
}
Thanks,
another question if during deletion i need an account attribute (userid) - this is an account level attribute not present at identity level how can i put that in the code
EXEC AppSource.dbo.genericsDeleteAccount @loginID = ‘NAMCK\sugvtte’, @requestID = ‘0’, @acctName = ‘sugvtte’, @userID = ‘3714’;
You can creating a disable provisioning policy using REST API onto your JDBC source.
By doing this, whenever there is a disable operation on your accounts, the attributes from this disable provisioning policy can be fetched in your JDBC Provisioning Rule with the plan keyword.
API reference to create a provisioning policy: create-provisioning-policy | SailPoint Developer Community
Ensure to use usageType as DISABLE and add the attributes that you would want to invoke during account disablement. And make changes in your JDBC Provisioning rule accordingly.
Thanks,
Arshad.
Have you found where where in the rule to set the argument nofiltering=true?
I think this is feasible within IIQ workflows but I haven’t see in being configured on ISC (no references on ISC documentation too).
In another post someone suggested writing the non-null values from the provisioning plan then read the attributes that are null from the application table to write a complete record. It seems to be working.