Hi Team,
We would like to implement JDBC 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 sources?
Thanks
Kalyan
Hi Kalyana,
You can using JDBC provisioning rule for this,Refer the below document.
When the access it revoked it will disable the account in SailPoint which the call the provisioning rule, there you have to write your code inside disable operation which has to remove the user from database.
Thanks!!
Hi @kalyannambi2010 ,
If your target applications provisioning rule are configured, then there is no other configuration you need to do to propagate the revoke decision.
It is OOTB configuration it will revoke any action automatically if the target is connected system.
Please take a look on the document and let me know if you still face issue.
Thanks
Hi @naveenkarthikkrk thank you for your response.
If the user is having multiple accounts on the source with different set of entitlements but the campaign owner tries to revoke only one entitlement by maintainng other set of entitlements for the same user. How to handle this type of things when there is no need to disable the user? Coudl you share the sample SQL query for removal of the access from the database?
Thanks
Kalyan
Hi @ashutosh08 thank you for your reply and do you have sample provisioning rule to revoke the access during campaign revoke.
Thanks
Kalyan
Hi @kalyannambi2010 ,
You may use this sample rule itself and can modify it as per your need.
<?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 {
// Unknown operation!
}
}
catch( SQLException e ) {
result.setStatus( ProvisioningResult.STATUS_FAILED );
result.addError( e );
}
finally {
if(statement != null) {
statement.close();
}
}
}
}
}
return result;
]]></Source>
</Rule>
You just need to attach the rule with the source.
Thanks
Hi @kalyannambi2010 ,
In this case you will need to get the native identity which is usually the account id, since you are having many accounts for the source each account will have different account id similarly in your database you will be having different data for each account ,the query will be DELETE FROM group_users
WHERE userid = kalyanana;
Thanks!!
Hi @naveenkarthikkrk and @ashutosh08 do we need to get complete table details from database team for removal of user access for which SQL query will be written?
Thanks
Kalyan
Hi @ashutosh08 what AccountRequest operation like delete or Modify we need to consider during campiagn access revoke?
Do we need to have specific service account permission required for JDBC source campiagn access revoke?
Thanks
Kalyan
Hi @kalyannambi2010,
There is no specific configuration you have to do for certification. You just need to configure your provisioning rule and other things will fall in place.
About operation, generally for access revocation your plan will be having modify operation for account request and your access will be present as attribute request with remove operation.
Thanks
Hi @ashutosh08 thank you for your message.
In our case, table is not fixed for removal of the access during UAR and how to write jdbc provisioning rule for removal of access/entitlement during UAR revocation?
Thanks
Kalyan
Hi,
Could anyone help me in the below senario?
In our case, table is not fixed for removal of the access during UAR and how to write jdbc provisioning rule for removal of access/entitlement during UAR revocation?
Thanks
Kalyan
In our case, table is not fixed for removal of the access during UAR and how to write jdbc provisioning rule for removal of access/entitlement during UAR revocation?
Thanks
Kalyan
@kalyannambi2010 you only need to ask your question once, and if someone is available, they will help you. The forum community is here to help when they are available and have something to contribute to the topic. If you need more immediate help, consider reaching out to Expert Services or Professional services offered by SailPoint, or one of the many Partners.
For your issue, i don’t understand what you mean by “your table is not fixed for removal of access”. Could you explain what you mean by this?
If the issue is that you don’t have the removal logic configured in the JDBC Provisioning Rule, then I would start by reviewing the documentation for that rule and the API for loading it. I would then work with your DB team to get the proper Stored Procedures or query for the removal of the entitlements. Once you have that, you can start constructing you JDBC Provisioning Rule’s modify and/or disable operations with beanshell to handle the removal of the entitlements. What some of the other community members have mentioned is that you may need to handle the removal with the modify operation.
This topic was automatically closed 60 days after the last reply. New replies are no longer allowed.