IdentityNow Rule Guide - JDBC Provision Rule

IdentityNow Rule Guide - JDBC Provision Rule

Purpose

 

This rule is designed to carry out provisioning actions from a provisioning plan, via a supplied JDBC connection. These typically issue SQL commands, such as insert, update, select, and deletes

 

Execution

 

  • Connector Execution - This rule executes within the Virtual Appliance and may offer special abilities to perform connector-related functions, and may offer managed connections to sources.
  • Logging - Logging statements are viewable within the ccg.log on the Virtual Appliance and by SailPoint personnel.

 

 

Input

 

Argument Type

Purpose

connection java.sql.Connection

A reference to the current SQL connection.

plan sailpoint.object.ProvisioningPlan

Provisioning plan containing the provisioning request(s).

application sailpoint.object.Application Attribute value of the identity attribute before the rule runs.
schema sailpoint.object.Schema

A reference to the Schema object for the Delimited File source being read.

 

 

Output

 

Argument Type

Purpose

result sailpoint.object.ProvisioningResult

ProvisioningResult object containing the status (success, failure, retry, etc.) of the provisioning request

 

Template

 

 

 

 

 

<?xml version='1.0' encoding='UTF-8'?>
<!DOCTYPE Rule PUBLIC "sailpoint.dtd" "sailpoint.dtd">
<Rule name="Example Rule" type="JDBCProvision">
  <Description>Describe your rule here.</Description>
  <Source><![CDATA[

  // Add your logic here.

  ]]></Source>
</Rule>

 

 

 

 

 

 

 

 

Example

 

 

 

 

 

<?xml version='1.0' encoding='UTF-8'?>
<!DOCTYPE Rule PUBLIC "sailpoint.dtd" "sailpoint.dtd">
<Rule name="Example Rule" type="JDBCProvision">
  <Description>This example JDBC rule can process account creation requests, deletion requests, and modification requests that pertain to the “role” attribute. It logs debug messages if other account request types are submitted.</Description>
  <Source><![CDATA[
  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 users (login,first,last,role,status) values (?,?,?,?,?)" );
            statement.setString ( 1, (String) account.getNativeIdentity() );
            statement.setString ( 2, getAttributeRequestValue(account,"first") );
            statement.setString ( 3, getAttributeRequestValue(account,"last") );
            statement.setString ( 4, getAttributeRequestValue(account,"role") );
            statement.setString ( 5, getAttributeRequestValue(account,"status") );
            statement.executeUpdate();

            result.setStatus( ProvisioningResult.STATUS_COMMITTED );

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

            // Modify account request -- change role

            PreparedStatement statement = connection.prepareStatement( "update users set role = ? where login = ?" );
            statement.setString ( 2, (String) account.getNativeIdentity() );
            if ( account != null ) {
              AttributeRequest attrReq = account.getAttributeRequest("role");
              if ( attrReq != null && ProvisioningPlan.Operation.Remove.equals(attrReq.getOperation()) ) {
                statement.setNull ( 1, Types.NULL );
                statement.executeUpdate();
              } else {
                statement.setString(1,attrReq.getValue());
                statement.executeUpdate();
              }
            }
            result.setStatus( ProvisioningResult.STATUS_COMMITTED );

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

            PreparedStatement statement = connection.prepareStatement( (String) application.getAttributeValue( "account.deleteSQL" ) );

            statement.setString ( 1, (String) account.getNativeIdentity() );
            statement.executeUpdate();

            result.setStatus( ProvisioningResult.STATUS_COMMITTED );

          } 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;

  ]]></Source>
</Rule>

 

 

 

Alternative Method

As an alternative method an externally packaged jar file can be used for all provisioning actions with the rule simple calling the methods within the jar. Please reference the documentation on Provisioning to a JDBC Source via an external jar for more information.

Comments

Something that we found, and would be cool to get your view @neil_mcglennon 

If your JDBC Provisioning Rule may contain something similar that was written for us, for a password reset event:

 private static void modifyAccount ( AccountRequest accountRequest ) throws SQLException {

if  ( ( accountRequest != null ) && ( accountRequest.getAttributeRequest( \"password\" ) != null ) ) {
changePassword ((String) accountRequest.getAttributeRequest( \"USER_NAME\" ).getValue(),(String) accountRequest.getAttributeRequest( \"password\" ).getValue() );
}
}

 

 

and your account schema of the relating JDBC source contained an attribute with the same name, in our instance USER_NAME:

we got the following error when the rule executed:

"Attempt to invoke method getValue on null value

Replicating in a test instance on IIQ, I found that to be as a result of: "Use of noFiltering argument in workflow" with reference to the below:

Re: Use of noFiltering argument in workflow

IIQ by default does not send  any attribute provisioning to connector if value being sent and value already present in the link object is equal. This is called filtering. If you would like to override the option i.e. do not want filtering, then noFiltering flag needs to be set to true.

Strictly speaking, this is for IIQ, but I am assuming components of the design and implementation are shared

https://community.sailpoint.com/t5/IdentityIQ-Forum/Use-of-noFiltering-argument-in-workflow/m-p/1327...

The USER_NAME attribute was no longer being added to the AttributeRequest as it (USER_NAME) existed on the Link object. and I guess the design assumption (without the noFiltering argument) is that your script will query the link for the value, whereas our JDBC Rule is querying the AccountRequest for the value: “accountRequest.getAttributeRequest( \"USER_NAME\" ).getValue()

As a workaround I had deleted the schema attribute USER_NAME, and added an alias to my aggregation SQL query to call it something else.

How do we retrieve the NativeIdentity if the Id in the SQL table is generated automatically after provisioning ?

Because waiting for the aggregation is not really satisfying.

Once you fire the SQL and get the nativeidentity ,Try setting it at the AccountRequest level accountRequest.setNativeIdentity()

I was able to do it : account.setNativeIdentity(id); And it's working. Thanks

@hari_patel I am not able to print the log statement in the rule. Neither OOTB logging "log" nor custom log statement print any statement in the ccg.log file. Can you please help with logging for this rule

@superjb could you expand on getting the id? im having the same issue.

@germanduci you parse the resultset, read the id you've configured to be the answer of your sql query or you run another statement with a select with criterias to get 1 result only and extract the id. ANd after you set it with account.setNativeIdentity(id);

@superjb Thank you, i'm trying to figure a way to make it work in the code, not exactly sure where tu place it, will look into it.

@superjb would it be to much to ask if you could share or give me some example of the code? Thank you.

@germanduci an example in my case

statement = connection.prepareStatement("Select UserId from User where [Login] = ?;");
statement.setString(1, login);

ResultSet rs = statement.executeQuery();
while (rs.next()) {
    retrievedId = rs.getString("UserId");
}
account.setNativeIdentity(retrievedId);

How can we call a stored procedure. Can we add a stored procedure for Create/disable/Modify in Application xml objects

 

@Shiva_Chaitanya 

I was able to call stored procedure like this (Sql Server implementation)

} else if ( AccountRequest.Operation.Disable.equals( account.getOperation() ) ) {
 
          // Call stored procedure to disable account
          CallableStatement statement = connection.prepareCall("{call MyDbName.dbo.MyStoredProc(?)}");
          statement.setString ( 1, (String) account.getNativeIdentity() );
          statement.execute();
          result.setStatus( ProvisioningResult.STATUS_COMMITTED );
 
Note that I updated the import statements as below
 
import java.sql.*;
//import java.sql.Connection;
//import java.sql.DriverManager;
//import java.sql.PreparedStatement;
//import java.sql.SQLException;
//import java.sql.Types;

Also, might not be too obvious from the above, but this section seems to be used for attribute sync. I include an example below using stored procedure

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

          if ( account != null ) {
            // Modify account request
            CallableStatement statement = connection.prepareCall("{call MyDbName.dbo.MyStoredProc(?,?,?,?)}");
            statement.setString ( 1, (String) account.getNativeIdentity() );
            statement.setString ( 2, getAttributeRequestValue(account,"FirstName") );
            statement.setString ( 3, getAttributeRequestValue(account,"lastname") );
            statement.setString ( 4, getAttributeRequestValue(account,"EmployeeId") );
            statement.execute();      
          }
          result.setStatus( ProvisioningResult.STATUS_COMMITTED );

We only have our JDBC connection set to read. DB owner doesn't want Sailpoint to write. 

Is there a way to stop Sailpoint from trying to provision? 

@MikeHamilton Can you please provide a template of how you got this rule working at your org using stored procs ? I can dm you directly if needed. Thanks a bunch. 

@adminea This is what I am currently using and it works for me. You will see below stored procedures used for modify (sync), enable and disable

 

import java.sql.*;
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 users (login,first,last,role,status) values (?,?,?,?,?)" );
statement.setString ( 1, (String) account.getNativeIdentity() );
statement.setString ( 2, getAttributeRequestValue(account,"first") );
statement.setString ( 3, getAttributeRequestValue(account,"last") );
statement.setString ( 4, getAttributeRequestValue(account,"role") );
statement.setString ( 5, getAttributeRequestValue(account,"status") );
statement.executeUpdate();

result.setStatus( ProvisioningResult.STATUS_COMMITTED );

*/

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

if ( account != null ) {
// Modify account request
// Call stored procedure to sync account
CallableStatement statement = connection.prepareCall("{call MYDB.dbo.UpdateUser(?,?,?,?)}");
statement.setString ( 1, (String) account.getNativeIdentity() );
statement.setString ( 2, getAttributeRequestValue(account,"FirstName") );
statement.setString ( 3, getAttributeRequestValue(account,"lastname") );
statement.setString ( 4, getAttributeRequestValue(account,"EmployeeId") );
statement.execute();
}
result.setStatus( ProvisioningResult.STATUS_COMMITTED );

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

/*
PreparedStatement statement = connection.prepareStatement( (String) application.getAttributeValue( "account.deleteSQL" ) );

statement.setString ( 1, (String) account.getNativeIdentity() );
statement.executeUpdate();

result.setStatus( ProvisioningResult.STATUS_COMMITTED );
*/

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

// Call stored procedure to disable account
CallableStatement statement = connection.prepareCall("{call MYDB.dbo.DisableUser(?)}");
statement.setString ( 1, (String) account.getNativeIdentity() );
statement.execute();
result.setStatus( ProvisioningResult.STATUS_COMMITTED );

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

// Call stored procedure to enable account
CallableStatement statement = connection.prepareCall("{call MYDB.dbo.EnableUser(?)}");
statement.setString ( 1, (String) account.getNativeIdentity() );
statement.execute();
result.setStatus( ProvisioningResult.STATUS_COMMITTED );

} 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;

 

This is awesome stuff @MikeHamilton , I'll plug in and test as needed. I'll provide updates if successful or otherwise. Thanks again.

Finally got my provisioning rule working after a few iterations. Thanks again @MikeHamilton   .I am still facing some issues regarding account creations, hoping someone can assist. Our DB requires the ADUsername and SecurityRoleId,officeId(where needed),regionId(where needed) and securityroleuserId (after account creation) for the users to be populated in the security role table. I am able to add new roles for existing users but creates for new users only brings back the account ID section and no other attribute is populated. I set up a create profile page(not sure if this is needed) just so sailpoint can pull the account attributes from the provisioning rule but not sure how to have sailpoint pull these . Any one ever experienced this during account creates ? thanks as you respond.Screen Shot 2023-01-23 at 1.23.31 PM.png

import java.sql.*;
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 class JDBCProvisioningRule {
    private Connection connection;
    private String opsappUsername;
    private int SecurityRoleUserId;
    private int SecurityRoleId;
    private int OfficeId;
    private int RegionId;
    private CallableStatement statement;
    public JDBCProvisioningRule(String opsappUsername, Connection connection) {
        this.opsappUsername = opsappUsername;
        this.connection = connection;
    }
    public String getattributeRequestValue(AccountRequest acctReq, String attribute) {
        if ( acctReq != null ) {
            attributeRequest attrReq = acctReq.getattributeRequest(attribute);
            if ( attrReq != null ) {
                return attrReq.getValue();
            }
        }
        return null;
    }
    public ProvisioningResult execute(ProvisioningPlan plan) {
        ProvisioningResult result = new ProvisioningResult();
        if ( plan != null ) {
            List<AccountRequest> 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.prepareCall("{call mystoredproc(?,?,?,?,?)}");
                            statement.setString ( 1, (String) account.getNativeIdentity() );
                            statement.setInt(2, (Integer) account.getattribute("SecurityRoleId"));
                            statement.SetInt(3, (Integer) account.getattribute("OfficeId"));
                            statement.setInt(4, (Integer) account.getattribute("RegionId"));
                            sstatement.registerOutParameter(5, Types.INTEGER);
                            statement.execute();
                            SecurityRoleUserId = statement.getInt(5);
                            result.setStatus(ProvisioningResult.STATUS_COMMITTED);
                        } else if ( AccountRequest.Operation.Modify.equals( account.getOperation() ) ) {
                            // Code to handle modify operation here
                            return result;
                        } else if ( AccountRequest.Operation.Disable.equals( account.getOperation() ) ) {
                            // Call stored procedure to disable/delete account
                            statement = connection.prepareCall("{call mystoredproc(?)}");
                            statement.setInt(1, (Integer) account.getattribute("SecurityRoleUserId"));
                        statement.execute();
                        result.setStatus( ProvisioningResult.STATUS_COMMITTED );
                    } else if ( AccountRequest.Operation.Enable.equals( account.getOperation() ) ) {
                        // Code to handle enable operation here
                    } 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;
}

 

 

 

  

Update on this; finally got my provisioning rule working by using stored procedures and non stored sql commands as needed. Thanks 

Version history
Revision #:
8 of 8
Last update:
‎Oct 19, 2021 09:22 AM
Updated by: