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
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. |
Argument | Type |
Purpose |
result | sailpoint.object.ProvisioningResult |
ProvisioningResult object containing the status (success, failure, retry, etc.) of the provisioning request |
<?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>
<?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>
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 |
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.
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
I was able to call stored procedure like this (Sql Server implementation)
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
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.
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?
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:
I tried:
statement.setString ( 6, getAttributeRequestValue(account,"role") );
and
statement.setString ( 6, getAttributeRequestValue(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:
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