cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 

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 

Hi @adminea ,

 

I was checking your above post. Just creating the provisioning plan does not help here. How to create the rule and deploy in Identity Now.

Thanks !

I want to update the database with the entitlement details when the entitlement is created/updated through Entitlement catalogue for a JDBC application. How can we do that?

is this really worked? I am newer for this part. I had some java experiences, based on my understanding, this variable "plan" is not defined? could anyone give some advices on this part?

<?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>

@Michael_Tai  in the case there is an attribute sync and the plan does not contain the attribute "rule" the VA will send the sql statement "update users set role =  where login =<native identity>" which is not correct. 
I would test first if the attribute "rule" from the provisioning plan is not empty and operation is "Modify" then create and execute SLQ statement to update the column role.

Thanks again @OlivierJacques  for your replied, Appreciate your time on this topic.

I was starting to understanding the source in the template, it is Beanshell which is different from ...

Beanshell in SailPoint | SailPoint Tutorial (bytearray.in)

I will try based on your provided suggestions from tomorrow, will keep your posted in case of any good news. since I am in China and will go to sleep now

Hello @OlivierJacques just let you know that it worked for this source after we deployed in the SailPoint, however i got the same issue that @prajna_shetty metioned, could not print the log statement in the rule.

@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

Hi @Michael_Tai ,

 

I have not used the rule shown above. I was asking question on how to deploy the rule.

 

So i am not sure if the code works or not.

@sabthami_52  currently we try to print log in the print.error. it worked.

However we met another issue which i would like to get some here from @OlivierJacques

Recently we got one strange thing, we had tried to modify the jar followed below guideline by adding some logs in that java file, however there is no related logs printed, even we changed the jar name we deployed and we also modify some decriptions in the source and restart the ccg, it still failed.

It seems that SailPoint is always picking the orignial jar we deployed. It is some bugs related.

Is there any advices on this? @OlivierJacques 

Provisioning to a JDBC Source via an external jar - Compass (sailpoint.com)

 

Hello @sabthami_52  @OlivierJacques , I would like to provide some new update and good news.

After further explore and your valuable inputs, all issue had been fixed now. It worked as what we expected now.

Regarding create/modify/disabled/enabled it worked well. however i met one below which also raised support case to SailPoint as well.

could you give some ideas from your side? have you met this before?

CS0222356.PNG

I am  getting the null values  for some attributes in my database after this provisioning rule. 

Can anyone help me?

I'm trying to get the attribute "entitlement" of my access request,

but not able to locate, insert null value in my DB.

I tried:
statement.setString ( 6, getAttributeRequestValue(account,"role") );

and
statement.setString ( 6, getAttributeRequestValue(account,"entitlement") );

bellow my code:

  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 table (value1,value2,value3,value4,value5,value6) values (?,?,?,?,?,?)" );
            statement.setString ( 1, getAttributeRequestValue(account,"value1") );
            statement.setString ( 2, getAttributeRequestValue(account,"value2") );
            statement.setString ( 3, getAttributeRequestValue(account,"value3") );
            statement.setString ( 4, getAttributeRequestValue(account,"value4"));
            statement.setString ( 5, getAttributeRequestValue(account,"value5"));
            statement.setString ( 6, getAttributeRequestValue(account,"entitlement") );
            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 );
          }
        }
        catch( SQLException e ) {
          result.setStatus( ProvisioningResult.STATUS_FAILED );
          result.addError( e );
        }
        finally {
          if(statement != null) {
            statement.close();
          }
        }
      }
    }
  }

  return result;

I tried:
statement.setString ( 6getAttributeRequestValue(account,"role") );

and
statement.setString ( 6getAttributeRequestValue(account,"entitlement") );


How to define your role and entitlement in your account provisioning polcy and account schema?

Hello all,

I have few questions regarding JDBC connector:

  • I'm using the alternative method Provisioning to a JDBC Source via an external jar (by the way, this link no more work right?)
  • How inside the java class you can get Identity values? Not only provisioning plan values, but all values from identity

  • In the above example there is this code. Where account.deleteSQL is defined?
    application.getAttributeValue( "account.deleteSQL" )

 

Thanks

@Nicolas_NAPOLEONI this is 3 months late, but you would either have to add attributes to your plan with a before provisioning rule or make an API call to /search and retrieve an identity object.  I too want to look at that old reference doc that got deleted.  Provisioning to a JDBC Source via an external jar

 

 

create a new rule and save it from the Ui

hi

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