Version 8.1
we have one table in DB, we need provisioning rule for this, kindly help on this to have JDBC provisioning rule. we have one table in DB, we need provisioning rule for this, kindly help on this to have JDBC provisioning rule.
Can you be more specific about your requirements
Anyhow there are lot of articles in community that can help you with JDBC provisioning rules
Below is a good one to check
IdentityNow Rule Guide - JDBC Provision Rule - Compass (sailpoint.com)
Though its IDNOW article the rule is same in IIQ as well
Hi,
You can refer the rule doc for JDBC Provisioning rule. If you want to add a custom logic, please Share the requirement, account schema and native identity of JDBC app.
Regards,
Arun
Hi @shaffusailpoint,
If you are looking for some specific help on custom logic, then please share the requirement and if it is just about sample rule then please find the sample example rule which will be available in your local Sailpoint installation.
<!--
Example JDBC Provisioning rule. This rule is called when the
connector needs to provision the Account(s) data in the database.
-->
<Rule name="Example JDBC Provisioning Rule" type="JDBCProvision">
<Description>
An example Provisioning rule for the JDBC Connector that
deletes the Account(s) from the database.
</Description>
<Signature>
<Inputs>
<Argument name='context'>
<Description>
A sailpoint.api.SailPointContext object that can be used to
query the database to aid in provisioning.
</Description>
</Argument>
<Argument name='application'>
<Description>
The Application the account is being provisioned for.
</Description>
</Argument>
<Argument name='schema'>
<Description>
Schema representing the data being provisioned.
</Description>
</Argument>
<Argument name='connection'>
<Description>
Connection to the application database.
</Description>
</Argument>
<Argument name='plan'>
<Description>
The ProvisioningPlan created against the application.
</Description>
</Argument>
<Argument name='log'>
<Description>
log for debugging
</Description>
</Argument>
</Inputs>
</Signature>
<Source>
<![CDATA[
import java.util.*;
import java.sql.SQLException;
import sailpoint.object.ProvisioningResult;
import sailpoint.object.ProvisioningPlan.AccountRequest;
List accounts = plan.getAccountRequests();
System.out.println("string is " + plan.toXml());
System.out.println("before for loop");
ProvisioningResult provResult = new ProvisioningResult();
if(accounts.size() > 0) {
for(int i = 0; i < accounts.size(); i++) {
AccountRequest temp = accounts.get(i);
System.out.println(temp.getApplication());
AccountRequest.Operation op = temp.getOperation();
String accountId = temp.getNativeIdentity();
System.out.println("identity " + accountId);
System.out.println("operation " + op);
try {
stmt = connection.createStatement();
// creating Query String
String query = "DELETE FROM USER WHERE FIRSTNAME='" + accountId + "'";
System.out.println("query is " + query);
stmt.executeUpdate(query);
}
catch(SQLException e) {
provResult.setStatus(ProvisioningResult.STATUS_FAILED);
provResult.addError(e);
}
}
}
System.out.println("after the for loop");
return provResult ;
]]>
</Source>
</Rule>
<!--
Example JDBC Enable Provisioning rule. This rule is called when the
connector needs to provision the Account(s) data in the database.
-->
<Rule name="JDBC Enable Provision Rule" type="JDBCOperationProvisioning">
<Description>
An example Provisioning rule for the JDBC Connector that
enables the Account(s) in the database.
</Description>
<Signature>
<Inputs>
<Argument name='context'>
<Description>
A sailpoint.api.SailPointContext object that can be used to
query the database to aid in provisioning.
</Description>
</Argument>
<Argument name='application'>
<Description>
The Application the account is being provisioned for.
</Description>
</Argument>
<Argument name='schema'>
<Description>
Schema representing the data being provisioned.
</Description>
</Argument>
<Argument name='connection'>
<Description>
Connection to the application database.
</Description>
</Argument>
<Argument name='plan'>
<Description>
The ProvisioningPlan created against the application.
</Description>
</Argument>
<Argument name='log'>
<Description>
log for debugging
</Description>
</Argument>
<Argument name='request'>
<Description>
request for specific operation
</Description>
</Argument>
</Inputs>
</Signature>
<Source>
<![CDATA[
import java.sql.*;
import java.sql.Statement;
import sailpoint.object.ProvisioningResult;
ProvisioningResult result = new ProvisioningResult();
result.setStatus(ProvisioningResult.STATUS_COMMITTED);
Statement stmt=null;
String user = request.getNativeIdentity();
try{
String query = "UPDATE openconntest.accounts SET isrevoked='N' where userid='" + user + "'";
stmt = connection.createStatement();
stmt.execute(query);
}catch(Exception e){
result.addError(e.getMessage());
result.setStatus(ProvisioningResult.STATUS_FAILED);
}finally{
if(stmt!=null)
stmt.close();
}
return result;
]]>
</Source>
</Rule>
<!--
Example JDBC Disable Provisioning rule. This rule is called when the
connector needs to provision the Account(s) data in the database.
-->
<Rule name="JDBC Disable Provision Rule" type="JDBCOperationProvisioning">
<Description>
An example Provisioning rule for the JDBC Connector that
disables the Account(s) in the database.
</Description>
<Signature>
<Inputs>
<Argument name='context'>
<Description>
A sailpoint.api.SailPointContext object that can be used to
query the database to aid in provisioning.
</Description>
</Argument>
<Argument name='application'>
<Description>
The Application the account is being provisioned for.
</Description>
</Argument>
<Argument name='schema'>
<Description>
Schema representing the data being provisioned.
</Description>
</Argument>
<Argument name='connection'>
<Description>
Connection to the application database.
</Description>
</Argument>
<Argument name='plan'>
<Description>
The ProvisioningPlan created against the application.
</Description>
</Argument>
<Argument name='log'>
<Description>
log for debugging
</Description>
</Argument>
<Argument name='request'>
<Description>
request for specific operation
</Description>
</Argument>
</Inputs>
</Signature>
<Source>
<![CDATA[
import java.sql.*;
import java.sql.Statement;
import sailpoint.object.ProvisioningResult;
ProvisioningResult result = new ProvisioningResult();
result.setStatus(ProvisioningResult.STATUS_COMMITTED);
Statement stmt=null;
String user = request.getNativeIdentity();
try{
String query = "UPDATE openconntest.accounts SET isrevoked='Y' where userid='" + user + "'";
stmt = connection.createStatement();
stmt.execute(query);
}catch(Exception e){
result.addError(e.getMessage());
result.setStatus(ProvisioningResult.STATUS_FAILED);
}finally{
if(stmt!=null)
stmt.close();
}
return result;
]]>
</Source>
</Rule>
<!--
Example JDBC Delete Provisioning rule. This rule is called when the
connector needs to provision the Account(s) data in the database.
-->
<Rule name="JDBC Delete Provision Rule" type="JDBCOperationProvisioning">
<Description>
An example Provisioning rule for the JDBC Connector that
deletes the Account(s) from the database.
</Description>
<Signature>
<Inputs>
<Argument name='context'>
<Description>
A sailpoint.api.SailPointContext object that can be used to
query the database to aid in provisioning.
</Description>
</Argument>
<Argument name='application'>
<Description>
The Application the account is being provisioned for.
</Description>
</Argument>
<Argument name='schema'>
<Description>
Schema representing the data being provisioned.
</Description>
</Argument>
<Argument name='connection'>
<Description>
Connection to the application database.
</Description>
</Argument>
<Argument name='plan'>
<Description>
The ProvisioningPlan created against the application.
</Description>
</Argument>
<Argument name='log'>
<Description>
log for debugging
</Description>
</Argument>
<Argument name='request'>
<Description>
request for specific operation
</Description>
</Argument>
</Inputs>
</Signature>
<Source>
<![CDATA[
import java.sql.*;
import java.sql.Statement;
import sailpoint.object.ProvisioningResult;
ProvisioningResult result = new ProvisioningResult();
result.setStatus(ProvisioningResult.STATUS_COMMITTED);
Statement stmt=null;
String user = request.getNativeIdentity();
try{
String query = "delete from openconntest.accounts where userid='" + user + "'";
stmt = connection.createStatement();
stmt.execute(query);
}catch(Exception e){
result.addError(e.getMessage());
result.setStatus(ProvisioningResult.STATUS_FAILED);
}finally{
if(stmt != null)
stmt.close();
}
return result;
]]>
</Source>
</Rule>
<!--
Example JDBC Unlock Provisioning rule. This rule is called when the
connector needs to provision the Account(s) data in the database.
-->
<Rule name="JDBC Unlock Provision Rule" type="JDBCOperationProvisioning">
<Description>
An example Provisioning rule for the JDBC Connector that
unlocks the Account(s) in the database.
</Description>
<Signature>
<Inputs>
<Argument name='context'>
<Description>
A sailpoint.api.SailPointContext object that can be used to
query the database to aid in provisioning.
</Description>
</Argument>
<Argument name='application'>
<Description>
The Application the account is being provisioned for.
</Description>
</Argument>
<Argument name='schema'>
<Description>
Schema representing the data being provisioned.
</Description>
</Argument>
<Argument name='connection'>
<Description>
Connection to the application database.
</Description>
</Argument>
<Argument name='plan'>
<Description>
The ProvisioningPlan created against the application.
</Description>
</Argument>
<Argument name='log'>
<Description>
log for debugging
</Description>
</Argument>
<Argument name='request'>
<Description>
request for specific operation
</Description>
</Argument>
</Inputs>
</Signature>
<Source>
<![CDATA[
import java.sql.*;
import java.sql.Statement;
import sailpoint.object.ProvisioningResult;
ProvisioningResult result = new ProvisioningResult();
result.setStatus(ProvisioningResult.STATUS_COMMITTED);
Statement stmt=null;
String user = request.getNativeIdentity();
try{
String query = "UPDATE openconntest.accounts SET islock='N' where userid='" + user + "'";
stmt = connection.createStatement();
stmt.execute(query);
}catch(Exception e){
result.addError(e.getMessage());
result.setStatus(ProvisioningResult.STATUS_FAILED);
}finally{
if(stmt!=null)
stmt.close();
}
return result;
]]>
</Source>
</Rule>
<!--
Example JDBC Create Provisioning rule. This rule is called when the
connector needs to provision the Account(s) data in the database.
-->
<Rule name="JDBC Create Provision Rule" type="JDBCOperationProvisioning">
<Description>
An example Provisioning rule for the JDBC Connector that
creates the Account(s) in the database.
</Description>
<Signature>
<Inputs>
<Argument name='context'>
<Description>
A sailpoint.api.SailPointContext object that can be used to
query the database to aid in provisioning.
</Description>
</Argument>
<Argument name='application'>
<Description>
The Application the account is being provisioned for.
</Description>
</Argument>
<Argument name='schema'>
<Description>
Schema representing the data being provisioned.
</Description>
</Argument>
<Argument name='connection'>
<Description>
Connection to the application database.
</Description>
</Argument>
<Argument name='plan'>
<Description>
The ProvisioningPlan created against the application.
</Description>
</Argument>
<Argument name='log'>
<Description>
log for debugging
</Description>
</Argument>
<Argument name='request'>
<Description>
request for specific operation
</Description>
</Argument>
</Inputs>
</Signature>
<Source>
<![CDATA[
import java.sql.*;
import java.sql.Statement;
import sailpoint.object.ProvisioningResult;
ProvisioningResult result = new ProvisioningResult();
result.setStatus(ProvisioningResult.STATUS_COMMITTED);
Statement stmt=null;
String user = request.getNativeIdentity();
try{
/*
From the request get all the attribute request
Populate the hashmap and extract data from hashmap and form the query accordingly.
*/
String query = "INSERT INTO openconntest.accounts(userid,firstname,lastname,email,password,islock,isrevoked) values ('"+user+"','Mango','Mood','[email protected]','Sailpoint123','N','N')";
stmt = connection.createStatement();
stmt.execute(query);
}catch(Exception e){
result.addError(e.getMessage());
result.setStatus(ProvisioningResult.STATUS_FAILED);
}finally{
if(stmt != null)
stmt.close();
}
return result;
]]>
</Source>
</Rule>
<!--
Example JDBC Modify Provisioning rule. This rule is called when the
connector needs to provision the Account(s) data in the database.
-->
<Rule name="JDBC Modify Provision Rule" type="JDBCOperationProvisioning">
<Description>
An example Provisioning rule for the JDBC Connector that
modify the Account(s) in the database.
</Description>
<Signature>
<Inputs>
<Argument name='context'>
<Description>
A sailpoint.api.SailPointContext object that can be used to
query the database to aid in provisioning.
</Description>
</Argument>
<Argument name='application'>
<Description>
The Application the account is being provisioned for.
</Description>
</Argument>
<Argument name='schema'>
<Description>
Schema representing the data being provisioned.
</Description>
</Argument>
<Argument name='connection'>
<Description>
Connection to the application database.
</Description>
</Argument>
<Argument name='plan'>
<Description>
The ProvisioningPlan created against the application.
</Description>
</Argument>
<Argument name='log'>
<Description>
log for debugging
</Description>
</Argument>
<Argument name='request'>
<Description>
request for specific operation
</Description>
</Argument>
</Inputs>
</Signature>
<Source>
<![CDATA[
import java.sql.*;
import java.sql.Statement;
import sailpoint.object.ProvisioningResult;
ProvisioningResult result = new ProvisioningResult();
result.setStatus(ProvisioningResult.STATUS_COMMITTED);
Statement stmt=null;
String user = request.getNativeIdentity();
try{
String query = "UPDATE openconntest.accounts SET firstname='AMOL',lastname='CHAUDHARI',email='[email protected]' where userid='"+user+"'";
stmt = connection.createStatement();
stmt.execute(query);
}catch(Exception e){
result.addError(e.getMessage());
result.setStatus(ProvisioningResult.STATUS_FAILED);
}finally{
stmt.close();
}
return result;
]]>
</Source>
</Rule>
Thanks
Hi,
i would suggest before JDBC provision topic, if you don’t have knowledge on the Java JDBC functions please go through once it will help you to make your requirements easy and solve it very effectively.