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.
.png)
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>
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.
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;
}
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?
Revision #:
9 of 9
Last update:
Jul 29, 2024
05:06 PM
Updated by:
