Introduction
This guide is for implementing a custom solution for one of the use cases, like provisioning of accounts from a single JDBC application to more than one database at a time. This solution has some workarounds at the application as well, and we will discuss them in the document.
Business use case
The requirement was to consider a customer who has two data centers for their IT infrastructure. One is in India, and the second one is in Australia. The India data center is the main data center, and the Australia data center is a secondary or critical usage or backup center. The database application is there in both the India and Australia data centers. Now, SailPoint IIQ has to provision accounts to both data centers’ databases using a single JDBC application because the customer wants to make a single request only, and that should be updated/provisioned in both databases at a time by a single transaction request.
Solution overview
There could be other solutions as well; I chose it as one of the solutions that we can achieve. Since the customer wants to provision in both databases using a single application. First onboard JDBC application with one data center’s database (India) as usual. As we all know that JDBC applications won’t support provisioning by default, we have to write logic in the provisioning rule of connector rules. Once we onboard the application, we will be having a connection object reference that contains all the connection details of the main database (India), which is being used in writing JDBC provisioning rules. So, now we will have to create one more Connection (connection2) object using the DriverManager API and use that to execute queries along with the connection object. This way the SQL queries will be executed in both databases at the same time. But ultimately SailPoint IIQ will communicate and aggregate with the main database only, not the secondary database. But we will use the custom logic to perform provisioning using another connection object so that it will be done in both DBs simultaneously.
Prerequisites
- Handy are the second database connection details, like JDBC URL, JDBC username, and JDBC password.
- Decrypt the password, which will be used in the rule.
- Make sure the second DB is also up and running at all times.
- Get the SQL queries from the DB for what SQL statements we have to execute while provisioning so that we can change them dynamically later in the rule.
Implementation Details
- Onboard the application as it is with primary database details.
- Write down the logic to provision accounts (Connector Rules-> Global Provisioning Rule). This logic should contain the code to perform provisioning on both databases.
Rule: BPK-Database-Global-Provisioin
<?xml version='1.0' encoding='UTF-8'?>
<!DOCTYPE Rule PUBLIC "sailpoint.dtd" "sailpoint.dtd">
<Rule language="beanshell" name="BPK-Database-Global-Provisioin" type="JDBCProvision">
<Description>This rule is used by the JDBC connector to do provisioning of the data .</Description>
<Signature returnType="ProvisioningResult">
<Inputs>
<Argument name="log" type="org.apache.commons.logging.Log">
<Description>
The log object associated with the SailPointContext.
</Description>
</Argument>
<Argument name="context" type="sailpoint.api.SailPointContext">
<Description>
A sailpoint.api.SailPointContext object that can be used to query the database if necessary.
</Description>
</Argument>
<Argument name="application">
<Description>
The application whose data file is being processed.
</Description>
</Argument>
<Argument name="schema">
<Description>
The Schema currently in use.
</Description>
</Argument>
<Argument name="connection">
<Description>
A connection object to connect to database.
</Description>
</Argument>
<Argument name="plan">
<Description>
The ProvisioningPlan created against the JDBC application.
</Description>
</Argument>
</Inputs>
<Returns>
<Argument name="result">
<Description>
A Provisioning Result object is desirable to return the status.IT can be a new object or part of Provisioning Plan
</Description>
</Argument>
</Returns>
</Signature>
<Source>
import java.util.List;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.Statement;
import java.sql.SQLException;
import java.sql.ResultSet;
import java.sql.Types;
import sailpoint.api.SailPointContext;
import sailpoint.object.ProvisioningPlan;
import sailpoint.object.ProvisioningPlan.AccountRequest;
import sailpoint.object.ProvisioningPlan.AttributeRequest;
import sailpoint.object.ProvisioningResult;
import sailpoint.tools.Util;
log.error("\n\n\n");
log.error("****************************************");
log.error("Entering into BPK-Database-Global-Provisioin Rule");
log.error("****************************************");
log.error("\n\n\n");
//JDBC credentials for Dallas data center
String jdbcUrl = "jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCPS)(HOST=xxxxx.bpk.com)(PORT=1630))(CONNECT_DATA=(SERVICE_NAME=xxxx)))";
String jdbcUser = "UserName";
String jdbcPassword = context.decrypt("2:BPK:xxxxhNgMs9e6cxcxPDaRb1QJ/49cxcxcxvcIqQ="); //decrypt the pwd before using it
Connection connection2 = DriverManager.getConnection(jdbcUrl, jdbcUser, jdbcPassword);
//Internal method for grabbing an Attribute Request Value.
public Object getAttributeRequestValue(AccountRequest acctReq, String attribute) {
if ( acctReq != null ) {
AttributeRequest attrReq = acctReq.getAttributeRequest(attribute);
if ( attrReq != null ) {
return attrReq.getValue();
}
}
return null;
}
//Main
// The ProvisioningResult is the return object for this type of rule. We'll create it here and then populate it later
ProvisioningResult result = new ProvisioningResult();
// Check if the plan is null or not, if not null, process it...
if ( plan != null ) {
log.error("The plan is not null and passed: \n"+plan.toXml());
// Get all Account Requests out of the plan
List accounts = plan.getAccountRequests();
if ( ( accounts != null ) && ( accounts.size() > 0 ) ) {
// If the plan contains one or more account requests, we'll iterate through them
for ( AccountRequest account : accounts ) {
try {
if (AccountRequest.Operation.Create.equals(account.getOperation())) {
// CREATE Operation
log.error("Account Request Operation = Create");
Statement createStmt=null;
Statement grantBasicStmt=null;
Statement createStmt2=null;
Statement grantBasicStmt2=null;
String userName = (String) account.getNativeIdentity();
String password = getAttributeRequestValue(account, "password");
String defaultTableSpace = getAttributeRequestValue(account, "DEFAULT_TABLESPACE");
String temporaryTableSpace = getAttributeRequestValue(account, "TEMPORARY_TABLESPACE");
String profile = getAttributeRequestValue(account, "PROFILE");
String systemPrivileges = getAttributeRequestValue(account, "SYSTEM_PRIVILEGES");
/*log.error("userName: " +userName);
log.error("password: " +password);
log.error("defaultTableSpace: " +defaultTableSpace);
log.error("temporaryTableSpace: " +temporaryTableSpace);
log.error("profile: " +profile);
log.error("systemPrivileges: " +systemPrivileges);*/
String createQuery = "CREATE USER "+userName+" IDENTIFIED BY "+"\""+password+"\""+" DEFAULT TABLESPACE "+defaultTableSpace+" TEMPORARY TABLESPACE "+temporaryTableSpace+" PROFILE "+profile;
log.error("createQuery: " +createQuery);
createStmt = connection.createStatement();
createStmt2 = connection2.createStatement();
createStmt.execute(createQuery);
createStmt2.execute(createQuery);
log.error("Created user and ready for grant basic privilege");
String grantBasicQuery = "GRANT "+systemPrivileges+" TO "+userName;
log.error("grantBasicQuery: " +grantBasicQuery);
grantBasicStmt = connection.createStatement();
grantBasicStmt.execute(grantBasicQuery);
grantBasicStmt2 = connection2.createStatement();
grantBasicStmt2.execute(grantBasicQuery);
log.error("Granted basic privilege");
List listOfAttrReqRoles = account.getAttributeRequests("ROLES");
if (listOfAttrReqRoles != null && listOfAttrReqRoles.size()>=1)
{
for(AttributeRequest attrReqRoles : listOfAttrReqRoles)
{
String roleName = (String) attrReqRoles.getValue();
String grantRoleQuery = "GRANT "+roleName+" TO "+userName;
Statement grantRoleStmt = connection.createStatement();
Statement grantRoleStmt2 = connection2.createStatement();
grantRoleStmt.execute(grantRoleQuery);
grantRoleStmt2.execute(grantRoleQuery);
log.error("grantRoleQuery in Create: "+grantRoleQuery);
}
}
// Sucessful Create, so mark result as COMMITTED
result.setStatus(ProvisioningResult.STATUS_COMMITTED);
}
else if (AccountRequest.Operation.Modify.equals(account.getOperation())) {
// MODIFY Operation
log.error("Account Request Operation = Modify");
String userName = (String) account.getNativeIdentity();
List listOfAttrReqRoles = account.getAttributeRequests("ROLES");
if (listOfAttrReqRoles != null && listOfAttrReqRoles.size()>=1)
{
for(AttributeRequest attrReqRoles : listOfAttrReqRoles)
{
if(ProvisioningPlan.Operation.Add.equals(attrReqRoles.getOperation()))
{
String roleName = (String) attrReqRoles.getValue();
String grantRoleQuery = "GRANT "+roleName+" TO "+userName;
Statement grantRoleStmt = connection.createStatement();
Statement grantRoleStmt2 = connection2.createStatement();
grantRoleStmt.execute(grantRoleQuery);
grantRoleStmt2.execute(grantRoleQuery);
log.error("grantRoleQuery in Modify: "+grantRoleQuery);
}
else if(ProvisioningPlan.Operation.Remove.equals(attrReqRoles.getOperation()))
{
String roleName = (String) attrReqRoles.getValue();
String revokeRoleQuery = "REVOKE "+roleName+" FROM "+userName;
Statement revokeRoleStmt = connection.createStatement();
Statement revokeRoleStmt2 = connection2.createStatement();
revokeRoleStmt.execute(revokeRoleQuery);
revokeRoleStmt2.execute(revokeRoleQuery);
log.error("revokeRoleQuery in Modify: "+revokeRoleQuery);
}
}
}
// Sucessful Modify, so mark result as COMMITTED
result.setStatus(ProvisioningResult.STATUS_COMMITTED);
}
else if (AccountRequest.Operation.Delete.equals(account.getOperation())) {
// DELETE Operation
log.error("Account Request Operation = Delete");
String userName = (String) account.getNativeIdentity();
String deleteUserQuery = "DROP USER "+userName;
Statement deletUserStmt = connection.createStatement();
Statement deletUserStmt2 = connection2.createStatement();
deletUserStmt.execute(deleteUserQuery);
deletUserStmt2.execute(deleteUserQuery);
log.error("Delete User Query: "+deleteUserQuery);
// Sucessful Delete, so mark result as COMMITTED
result.setStatus(ProvisioningResult.STATUS_COMMITTED);
}
else if (AccountRequest.Operation.Disable.equals(account.getOperation()) || AccountRequest.Operation.Lock.equals(account.getOperation())) {
// DISABLE Operation
log.error("Account Request Operation = Disable");
//at the time of leaver, there are few roles are to be revoked. So for this below one extra code to revoke them. Under that it is for disable
String userName = (String) account.getNativeIdentity();
List listOfAttrReqRoles = account.getAttributeRequests("ROLES");
if (listOfAttrReqRoles != null && listOfAttrReqRoles.size()>=1)
{
for(AttributeRequest attrReqRoles : listOfAttrReqRoles)
{
if(ProvisioningPlan.Operation.Remove.equals(attrReqRoles.getOperation()))
{
String roleName = (String) attrReqRoles.getValue();
log.error("Account Request Operation = Disable and Roole Remove: "+roleName);
String revokeRoleQuery = "REVOKE "+roleName+" FROM "+userName;
Statement revokeRoleStmt = connection.createStatement();
Statement revokeRoleStmt2 = connection2.createStatement();
revokeRoleStmt.execute(revokeRoleQuery);
revokeRoleStmt2.execute(revokeRoleQuery);
log.error("revokeRoleQuery in Disable: "+revokeRoleQuery);
}
}
}
String disableUserQuery = "ALTER USER "+userName+" ACCOUNT LOCK";
Statement disableUserStmt = connection.createStatement();
Statement disableUserStmt2 = connection2.createStatement();
disableUserStmt.execute(disableUserQuery);
disableUserStmt2.execute(disableUserQuery);
log.error("Disable User Query: "+disableUserQuery);
// Sucessful Disable, so mark result as COMMITTED
result.setStatus(ProvisioningResult.STATUS_COMMITTED);
}
else if (AccountRequest.Operation.Enable.equals(account.getOperation()) || AccountRequest.Operation.Unlock.equals(account.getOperation())) {
// ENABLE Operation
log.error("Account Request Operation = Enable");
String userName = (String) account.getNativeIdentity();
String enableUserQuery = "ALTER USER "+userName+" ACCOUNT UNLOCK";
Statement enableUserStmt = connection.createStatement();
Statement enableUserStmt2 = connection2.createStatement();
enableUserStmt.execute(enableUserQuery);
enableUserStmt2.execute(enableUserQuery);
log.error("Enable User Query: "+enableUserQuery);
// Sucessful Enable, so mark result as COMMITTED
result.setStatus(ProvisioningResult.STATUS_COMMITTED);
}
else {
// Unknown operation
log.error("Unknown operation ["+ account.getOperation() + "]!");
}
} catch (SQLException e) {
System.out.println("Error occured at: " + e);
result.setStatus(ProvisioningResult.STATUS_FAILED);
result.addError(e);
}
}
}
}
log.error("\n\n\n");
log.error("****************************************");
log.error("Exit from BPK-Database-Global-Provisioin Rule");
log.error("****************************************");
log.error("\n\n\n");
return result;
</Source>
</Rule>
What does this rule do?
- Create a connection2 object for secondary database provisioning using credentials gotten from the DB team.
- Perform CREATE, MODIFY, DELETE, DISABLE, ENABLE, LOCK, and UNLOCK operations. For each individual operation we have to execute the query for both connections (connection, which is the default one used for the primary database, and connection2, which is used for the secondary database).
- Get the operation’s queries from the DB team and change or construct the string according to your requirements. dynamically.
- After successful execution of SQL queries in each operation, mark the provisioning result as COMMITTED and return the ProvisioningResult (result) from the rule.
- While executing queries, if any one of the connections’ queries fails, then the task itself fails and the access request will fail.
Challenges and workarounds
- While executing queries there will be some network lag (not too much, very negligible based on network speed), meaning SailPoint should connect the DB from the main data center to the secondary data center because SailPoint and the primary DB are in the India data center, and IIQ should communicate with the Australia data center.
- If there will be any data center maintenance scheduled, they have to let the SailPoint IIQ team know; otherwise, the tasks will fail because the data center is down. Once the SailPoint IIQ team knows, they will put the application in maintenance mode until the time. and later the IIQ will pick up the queued transaction if any came.
- By any chance IIQ failed to do provisioning, the DB team should take the risk to update things from DB manually, though SailPoint throws an error.
Optional work
The below steps are optional; you can use them if you want. But those are very common as part of the JDBC application.
- By default, SailPoint IIQ won’t make or decide whether the user is an active user or not while aggregating. For which we have to write explicit logic to check all the accounts while aggregating to check if the account is disabled or not? If yes, then disable it. We can achieve it with JDBC account customization rule.
Rule: BPK-Database-Customization
<?xml version='1.0' encoding='UTF-8'?>
<!DOCTYPE Rule PUBLIC "sailpoint.dtd" "sailpoint.dtd">
<Rule language="beanshell" name="BPK-Database-Customization" type="ResourceObjectCustomization">
<Description>This rule is configured on the application and is called after the connector has build a ResourceObject from the native application data.
Initially designed for non-rule based connectors to add SPPrivileged flag to an object, but could be used to do any transformations.</Description>
<Signature returnType="ResourceObject">
<Inputs>
<Argument name="log" type="org.apache.commons.logging.Log">
<Description>
The log object associated with the SailPointContext.
</Description>
</Argument>
<Argument name="context" type="sailpoint.api.SailPointContext">
<Description>
A sailpoint.api.SailPointContext object that can be used to query the database if necessary.
</Description>
</Argument>
<Argument name="object">
<Description>
The ResourceObject built by the connector.
</Description>
</Argument>
<Argument name="application">
<Description>
Application that references the connector.
</Description>
</Argument>
<Argument name="connector">
<Description>
The connector object.
</Description>
</Argument>
<Argument name="state">
<Description>
A Map containing state information.
</Description>
</Argument>
</Inputs>
<Returns>
<Argument name="resourceObject">
<Description>
The updated resource object.
</Description>
</Argument>
</Returns>
</Signature>
<Source>if (object != null )
{
String accountStatus = object.getAttribute("ACCOUNT_STATUS");
if("EXPIRED".equals(accountStatus) || "LOCKED".equals(accountStatus) || "EXPIRED & LOCKED".equals(accountStatus))
{
object.put("IIQDisabled", true);
}
}
return object;</Source>
</Rule>
- By default, SailPoint IIQ won’t enable an account if the user had an account and it was disabled when the user requested entitlements of the database. For which we have to write explicit logic to check if it is the Modify request and the add request of entitlements. If yes, then we have to add Enable AccountRequest to the plan. So that the provisioning engine will do the enabling as well. We can achieve it with the JDBC Before Provisioning rule.
Rule: BPK-Database-Before-Provisioning
<?xml version='1.0' encoding='UTF-8'?>
<!DOCTYPE Rule PUBLIC "sailpoint.dtd" "sailpoint.dtd">
<Rule name="BPK-Database-Before-Provisioning" type="BeforeProvisioning">
<Description>An IdentityIQ server-side rule that is executed before the connector's provisioning method is called. This gives the customer the ability to customize or react to anything in the ProvisioningPlan BEFORE it is sent to the underlying connectors used in provisioning.
This rule will be called for any application found in a plan that also has a configured 'beforeProvisioningRule' configured.
The plan can be updated directly in the rule by reference and does not need to return the plan.</Description>
<Signature>
<Inputs>
<Argument name="log" type="org.apache.commons.logging.Log">
<Description>
The log object associated with the SailPointContext.
</Description>
</Argument>
<Argument name="context" type="sailpoint.api.SailPointContext">
<Description>
A sailpoint.api.SailPointContext object that can be used to query the database if necessary.
</Description>
</Argument>
<Argument name="plan">
<Description>
The ProvisioningPlan object on its way to the Connector.
</Description>
</Argument>
<Argument name="application">
<Description>
The application object that references this before/after script.
</Description>
</Argument>
</Inputs>
</Signature>
<Source>import sailpoint.object.Identity;
import sailpoint.object.ProvisioningPlan.AccountRequest;
import sailpoint.object.ProvisioningPlan.AttributeRequest;
import sailpoint.object.ProvisioningPlan.Operation;
import java.util.ArrayList;
import java.util.List;
log.error("\n\n\n");
log.error("****************************************");
log.error("Entering into BPK-Database-Before-Provisioning Rule");
log.error("****************************************");
log.error("\n\n\n");
log.error("The plan before process: \n"+plan.toXml());
try {
AccountRequest acctReq = plan.getAccountRequest("Siebel Database");
String opString = null;
String attReqOpString = null;
if(acctReq != null)
{
opString = acctReq.getOperation().toString();
AttributeRequest attributeRequest = acctReq.getAttributeRequest("ROLES");
if(attributeRequest != null)
{
attReqOpString = attributeRequest.getOperation().toString();
}
}
if (opString != null && attReqOpString != null)
{
if("Modify".equals(opString) && (!"Remove".equals(attReqOpString)))
{
List accreqs = new ArrayList();
String appName = acctReq.getApplication();
String identityName = plan.getNativeIdentity();
Identity identityObject = context.getObjectByName(Identity.class, identityName);
AccountRequest enableAcctReq = new AccountRequest(AccountRequest.Operation.Enable, appName, null, identityName);
enableAcctReq.setNativeIdentity(acctReq.getNativeIdentity());
enableAcctReq.setApplication(appName);
accreqs.add(acctReq);
accreqs.add(enableAcctReq);
plan.setAccountRequests(accreqs);
plan.setIdentity(identityObject);
}
}
} catch (Exception e) {
System.err.println("Exception occured at: "+e);
}
log.error("The plan after process: \n"+plan.toXml());
log.error("\n\n\n");
log.error("****************************************");
log.error("Exit from BPK-Database-Before-Provisioning Rule");
log.error("****************************************");
log.error("\n\n\n");</Source>
</Rule>
Conclusion
By implementing this solution, organizations can significantly improve their identity governance processes for provisioning in JDBC applications. It not only saves time and reduces manual work in the same DBs. There could be other solutions for the same thing. This is just one example of how we can leverage IIQ for it. I would recommend you all please use the above snippet code and change it if required, follow the procedure, and do testing; you will get more understanding about it.
You can go through the connector document given by SailPoint for reference. https://documentation.sailpoint.com/connectors/identityiq8_4/jdbc/help/integrating_jdbc/introduction.html
You can reach out if you need any assistance. I’m happy to help you. If you feel this is helpful, please like and share it with others who might benefit 
.