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.

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