JDBC and AD deprovisioning during ceritification user access remediation

kalyannambi2010 · December 4, 2023, 10:20am

Hi Team,

We would like to implement JDBC and AD deprovisioning during ceritification user access remediation like if the source owner revokes any access item automatically that access shoule be revoked from the end system/application.

How to implement the above both in JDBC and AD sources?

Thanks
Kalyan

MVKR7T · December 4, 2023, 1:38pm

For AD you don’t need to do anything.

For JDBC, you need to handle that in Provisioning Rules.

kalyannambi2010 · December 5, 2023, 4:46am

Hi Krishna Mummadi,

Thank you for your reply.

For AD only IQ Service details are needed or any other configuration details needed?

For JDBC, do we have any sample provisioning rule code and it shoud be after/before provisioning rule?

Thanks
Kalyan

kjakubiak · December 5, 2023, 6:59am

Hi Kalyana,
For AD you have to also provide credentials and few details about your domain like search bases, domain controlers etc.

For JDBC you have to create provisioning rule and here you have 2 options

JDBCProvisioning rule - this is one rule executed whenever any provisioning happens to the JDBC connector, here is the example

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;
import sailpoint.tools.xml.XMLObjectFactory;
import org.apache.commons.logging.LogFactory;
import org.apache.commons.logging.Log;
Log _log = LogFactory.getLog("RuleProvisionSampleDB");
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();
if ( plan != null ) {
_log.debug( "plan [" + plan.toXml() + "]" );
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.
_log.debug( "Operation [" + account.getOperation() + "] detected." );
PreparedStatement 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
_log.debug( "Operation [" + account.getOperation() + "] detected." );
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 );
 _log.debug( "Preparing to execute:"+statement.toString() );
 statement.executeUpdate();
 } else {
statement.setString(1,attrReq.getValue());
_log.debug( "Preparing to execute:"+statement.toString() );
statement.executeUpdate();
}
}
result.setStatus( ProvisioningResult.STATUS_COMMITTED );
} else if ( AccountRequest.Operation.Delete.equals( account.getOperation() ) 
) {
_log.debug( "Operation [" + account.getOperation() + "] detected." );
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() 
) ) {
// Not supported.
_log.debug( "Operation [" + account.getOperation() + "] is not 
supported!" );
} else if ( AccountRequest.Operation.Enable.equals( account.getOperation() ) 
) {
// Not supported.
_log.debug( "Operation [" + account.getOperation() + "] is not 
supported!" );
} else if ( AccountRequest.Operation.Lock.equals( account.getOperation() ) ) 
{
// Not supported.
_log.debug( "Operation [" + account.getOperation() + "] is not 
supported!" );
} else if ( AccountRequest.Operation.Unlock.equals( account.getOperation() ) 
) {
// Not supported.
_log.debug( "Operation [" + account.getOperation() + "] is not 
supported!" );
} else {
// Unknown operation!
_log.debug( "Unknown operation [" + account.getOperation() + "]!" );
}
}
catch( SQLException e ) { 
 _log.error( e );
result.setStatus( ProvisioningResult.STATUS_FAILED );
result.addError( e );
 }
 }
 }
}
_log.debug( "result [" + result.toXml(false)+ "]");
return result;

JDBCOperationProvisioning rule - it works similar way to the previous one but you have to create 1 rule for each operation. Here is example:

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.ProvisioningResult;
import sailpoint.object.Schema;
import sailpoint.tools.xml.XMLObjectFactory;
import org.apache.commons.logging.LogFactory;
import org.apache.commons.logging.Log;
public String getAttributeRequestValue(AccountRequest acctReq, String attribute) {
if ( acctReq != null ) {
AttributeRequest attrReq = acctReq.getAttributeRequest(attribute);
if ( attrReq != null ) {
 return attrReq.getValue();
 }
 }
 return null;
}
AccountRequest acctRequest = (AccountRequest) request;
ProvisioningResult result = new ProvisioningResult();
try {
//Ideally we should first check to see if the account already exists.
//As written, this just assumes it does not.
log.debug( "Operation [" + acctRequest.getOperation() + "] detected." );
PreparedStatement statement = connection.prepareStatement( "insert into 
users (login,first,last,role,status) values (?,?,?,?,?)" );
statement.setString (1, (String) acctRequest.getNativeIdentity() );
statement.setString (2, getAttributeRequestValue(acctRequest,"first") );
statement.setString (3, getAttributeRequestValue(acctRequest,"last") );
statement.setString (4, getAttributeRequestValue(acctRequest,"role") );
statement.setString (5, getAttributeRequestValue(acctRequest,"status") );
statement.executeUpdate();
result.setStatus( ProvisioningResult.STATUS_COMMITTED );
}
catch( SQLException e ) { 
 log.error( e );
result.setStatus( ProvisioningResult.STATUS_FAILED );
result.addError( e );
}
log.debug( "result [" + result.toXml(false)+ "]");
return result;

Both types of rules you can select in the application definition in the rules section
If you select Global Provisioning Rule you can provide rule no.1

and if you select By Operation Rules you will be able to provide rule for each operation

kalyannambi2010 · December 6, 2023, 4:30am

Hi Kamil Jakubiak,

Thank you so much for your reply.

With respect to JDBC what type of rule we need to configure like before provisioning or after provisioning and is there a way that the same rule can be configured for more than one JDBC application in IDN and how to apply this rule to a JDBC source in IDN?

Thanks
Kalyan

RAKGDS · December 6, 2023, 11:33am

Hi Kalyan,
You need to have a Provisioning Rule which is a Connector rule to be configured in IdentityNow. You can attach same rule to as many JDBC application you like. The only thing is that the rule should be coded in such a way that it never fails.

Please find below documnetation for rule. Please go through the same and let us know if you are facing any isuse regarding the same.

kalyannambi2010 · December 14, 2023, 6:02am

Hi Kamil and Rakesh,

Thank you for update and for Microsoft SQL Server deprovisioning during certification remediation along with Microsoft SQL Server
provisioning rule service account which is being used to aggregate the users need to have capability to remove the access from source.

Please provide your inputs on this.

Thanks
Kalyan

system · February 12, 2024, 6:03am

This topic was automatically closed 60 days after the last reply. New replies are no longer allowed.

Topic		Replies	Views
JDBC deprovisioning during ceritification user access remediation ISC Discussion and Questions identity-security-cloud , provisioning	15	205	September 5, 2024
JDBC provisioning rule to remove the roles and disable the user ISC Discussion and Questions provisioning	8	117	November 25, 2024
JDBC provisioning Error in Global Provisioning Rule IIQ Discussion and Questions identityiq , provisioning	7	63	December 2, 2024
JDBC Provisioning rule error for revoke during certification revoke ISC Discussion and Questions identity-security-cloud , provisioning	26	144	September 27, 2024
JDBC Provisioning Disable ISC Discussion and Questions rules , identity-security-cloud , provisioning	16	209	October 1, 2024

JDBC and AD deprovisioning during ceritification user access remediation

Related topics