To reflect the changes of your use-case in this case a field that mentions it is locked you need to have the following things in place:
i. Schema should have the attribute which depicts its a locked account (which I assume you have since post aggregation you are able to reflect the same onto the ISC UI).
ii. Single account aggregation query is populated with the query which will return the said attribute and other attributes of the a user.
The single account aggregation query although not a required option it helps in basically verifying whether the operation performed by ISC has completed its course or not.
Now coming back to your other queries regarding the FeatureString yes you can manipulate it if required below is the default set of operations present:
Now coming back to your other query how you can propagate the LCS status to JDBC Provisioning straight answer is unfortunately you cannot as JDBC provisioning rule is a connector rule that being said it has a different set of arguments.
How do I use it? I make a placeholder attribute and map it with cloudLifecycleState and voila you got LCS in your rule but it will just be a string.
Hi @amansingh thank you so much for your detailed information.
Getting below error for below SQL statement:
PreparedStatement roleRevokeStatement = connection.prepareStatement(“Update A SET A.RoleID=(select RoleID from SecurityRole Where Name=‘No_Role’),A.Disabled=1 FROM Admin AS A
INNER JOIN SecurityRole AS B ON A.RoleID=B.RoleID WHERE A.AdminID = ? and B.Name = ?”);
[“BeanShell script error: bsh.EvalError: Sourced file: inline evaluation of: ``import java.sql.Connection; import java.sql.DriverManager; import java.sql.P . . . \u0027\u0027 Token Parsing Error: Lexical error at line 60, column 232. Encountered: "\n" (10), after : "\"Update A SET A.RoleID\u003d(select RoleID from SecurityRole Where Name\u003d\\u0027No_Role\\u0027),A.Disabled\u003d1 FROM Admin AS A " : at Line: 20 : in file: inline evaluation of: ``import java.sql.Connection; import java.sql.DriverManager; import java.sql.P . . . \u0027\u0027 : PreparedStatement statement \n BSF info: VaricentClientStage at line: 0 column: columnNo”,“BeanShell script error: bsh.EvalError: Sourced file: inline evaluation of: ``import java.sql.Connection; import java.sql.DriverManager; import java.sql.P . . . \u0027\u0027 Token Parsing Error: Lexical error at line 60, column 232. Encountered: "\n" (10), after : "\"Update A SET A.RoleID\u003d(select RoleID from SecurityRole Wh… 0 column: columnNo”]
The query has few syntaxes errors, find below modified query:
PreparedStatement roleRevokeStatement = connection.prepareStatement(
"UPDATE Admin AS A " +
"SET A.RoleID = (SELECT RoleID FROM SecurityRole WHERE Name = 'No_Role'), " +
"A.Disabled = 1 " +
"FROM Admin AS A " +
"INNER JOIN SecurityRole AS B ON A.RoleID = B.RoleID " +
"WHERE A.AdminID = ? AND B.Name = ?"
);
Hi @gogubapu thanks for the update and getting error as [“com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near the keyword \u0027AS\u0027.”,“com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near the keyword \u0027AS\u0027.”]
Rule code:
<?xml version='1.0' encoding='UTF-8'?>
This JDBCProvision JDBC rule can process account modification requests that pertain to the “role” attribute during user access review. It logs debug messages if other account request types are submitted.
<![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;
log.debug("entering VaricentClientStage Rule");
ProvisioningResult result = new ProvisioningResult();
PreparedStatement statement;
if ( plan != null ) {
List accounts = plan.getAccountRequests();
if ( ( accounts != null ) && ( accounts.size() > 0 ) ) {
for ( AccountRequest account : accounts ) {
try {
String nativeIdentity = (String) account.getNativeIdentity();
if ( AccountRequest.Operation.Modify.equals( account.getOperation() ) ) {
// String nativeIdentity = (String) account.getNativeIdentity();
log.debug("nativeIdentity is " + nativeIdentity);
AttributeRequest attrReq = account.getAttributeRequest("Role");
log.debug("attrReq is " + attrReq);
if ( account != null ) {
if ( attrReq != null && ProvisioningPlan.Operation.Remove.equals(attrReq.getOperation()) ) {
//List entitlemetsToBeRemoved = new ArrayList();
if (attrReq.getValue()!=null && attrReq.getValue() instanceof String)
{
String attrValue = (String) attrReq.getValue();
//entitlemetsToBeRemoved .add(attrValue);
}
//PreparedStatement roleUpdateStatement = connection.prepareStatement( "Update A SET A.RoleID=0,A.Disabled=1 FROM Admin AS A INNER JOIN SecurityRole AS B ON A.RoleID=B.RoleID WHERE A.AdminID = ? and B.Name = ?" );
//roleUpdateStatement.setString ( 1, (String) account.getNativeIdentity() );
// roleUpdateStatement.setString ( 2, (String) attrReq.getValue() );
log.debug("queryStatement is " + queryStatement);
//PreparedStatement roleRevokeStatement = connection.prepareStatement("Update A SET A.Disabled = 1 FROM Admin AS A Where A.AdminID = ?");
PreparedStatement roleRevokeStatement = connection.prepareStatement(
"UPDATE Admin AS A " +
"SET A.RoleID = (SELECT RoleID FROM SecurityRole WHERE Name = ‘No_Role’), " +
"A.Disabled = 1 " +
"FROM Admin AS A " +
"INNER JOIN SecurityRole AS B ON A.RoleID = B.RoleID " +
“WHERE A.AdminID = ? AND B.Name = ?”
);
roleRevokeStatement.setString ( 1, (String) account.getNativeIdentity() );
roleRevokeStatement.setString ( 2, (String) attrReq.getValue() );
// roleUpdateStatement.executeUpdate();
roleRevokeStatement.executeUpdate();
}
}
result.setStatus( ProvisioningResult.STATUS_COMMITTED );
} else if ( attrReq != null && AccountRequest.Operation.Disable.equals( account.getOperation() ) )
{
AttributeRequest attrReq = account.getAttributeRequest("Role");
PreparedStatement roleRevokeStatement = connection.prepareStatement("UPDATE Admin AS A " + " SET A.RoleID = (SELECT RoleID FROM SecurityRole WHERE Name = 'No_Role'), " + " A.Disabled = 1 " + " FROM Admin AS A " + " INNER JOIN SecurityRole AS B ON A.RoleID = B.RoleID " + " WHERE A.AdminID = ? AND B.Name = ?");
roleRevokeStatement.setString ( 1, (String) account.getNativeIdentity() );
roleRevokeStatement.setString ( 2, (String) attrReq.getValue() );
roleRevokeStatement.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();
}
}
}
}
}
log.debug("leaving VaricentClientStage Rule");
return result;
<?xml version='1.0' encoding='UTF-8'?>
This JDBCProvision JDBC rule can process account modification requests that pertain to the “role” attribute during user access review. It logs debug messages if other account request types are submitted.
<![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;
log.debug("entering VaricentClientStage Rule");
ProvisioningResult result = new ProvisioningResult();
PreparedStatement statement;
if ( plan != null ) {
List accounts = plan.getAccountRequests();
if ( ( accounts != null ) && ( accounts.size() > 0 ) ) {
for ( AccountRequest account : accounts ) {
try {
String nativeIdentity = (String) account.getNativeIdentity();
AttributeRequest attrReq = account.getAttributeRequest("Role");
String attrValue;
if ( attrReq != null {
attrValue = (String) attrReq.getValue();
}
if ( AccountRequest.Operation.Modify.equals( account.getOperation() ) ) {
// String nativeIdentity = (String) account.getNativeIdentity();
log.debug("nativeIdentity is " + nativeIdentity);
AttributeRequest attrReq = account.getAttributeRequest("Role");
log.debug("attrReq is " + attrReq);
if ( account != null ) {
//PreparedStatement roleRevokeStatement = connection.prepareStatement("Update A SET A.Disabled = 1 FROM Admin AS A Where A.AdminID = ?");
PreparedStatement roleRevokeStatement = connection.prepareStatement(
"UPDATE Admin AS A " +
"SET A.RoleID = (SELECT RoleID FROM SecurityRole WHERE Name = ‘No_Role’), " +
"A.Disabled = 1 " +
"FROM Admin AS A " +
"INNER JOIN SecurityRole AS B ON A.RoleID = B.RoleID " +
“WHERE A.AdminID = ? AND B.Name = ?”
);
roleRevokeStatement.setString ( 1, (String) account.getNativeIdentity() );
roleRevokeStatement.setString ( 2, (String) attrReq.getValue() );
// roleUpdateStatement.executeUpdate();
roleRevokeStatement.executeUpdate();
}
}
result.setStatus( ProvisioningResult.STATUS_COMMITTED );
} else if ( AccountRequest.Operation.Disable.equals( account.getOperation() ) )
{
// AttributeRequest attrReq = account.getAttributeRequest("Role");
PreparedStatement roleRevokeStatement = connection.prepareStatement("UPDATE Admin AS A " + " SET A.RoleID = (SELECT RoleID FROM SecurityRole WHERE Name = 'No_Role'), " + " A.Disabled = 1 " + " FROM Admin AS A " + " INNER JOIN SecurityRole AS B ON A.RoleID = B.RoleID " + " WHERE A.AdminID = ? AND B.Name = ?");
if ( account != null ) {
roleRevokeStatement.setString ( 1, (String) account.getNativeIdentity() );
roleRevokeStatement.setString ( 2, (String) attrReq.getValue() );
roleRevokeStatement.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();
}
}
}
}
}
log.debug("leaving VaricentClientStage Rule");
return result;
When I look at the BuildMap Rule you are trying to get IIQDisabled attribute which would return an error if IIQDisabled in not an attribute that is being provided by the your end target system in this case your JDBC source.
The code 1 and 0 are these coming in from a different attribute? Ex: empStatus?
If yes then you would be performing account.get(“empStatus”) and then with the string comparison you will be performing map.put(“IIQDisabled”,true/false).
As for the provisioning rule I dont see any issues on it were you able to perform a remove operation in eclipse IDE or any other other IDE? If no then would suggest you to try your code snipped of actual invocation of remove operation the “disableroleRevokeStatement” part.
remove all the roles/permissions during user access revoke
remove all the roles/permissions and disable the user during user leaver where user status changes from active to terminated.
We are trying to remove the same set entitlements attribute “Priv” in both cases 1 and 2.
As SailPoint IDN may not revoke and trigger same SQL statment in both cases 1 and 2 so defined a “Disable” provisioning policy for case 2 and defined the entitlement attribute as “disablePriv” in “Disable” provisioning policy for case 2 . But we are trying to remove the same set entitlements attribute “Priv” in both cases 1 and 2.
We have implemented the aboveJDBC provisioning rule where it is working for case 1 remove all the roles/permissions during user access revoke and not working for case 2 where all the roles/permissions are not revoked but disable of the user is happening during user leaver.
Please update the code if you think it may not work and provide your input.
<?xml version='1.0' encoding='UTF-8'?>
This 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.
<![CDATA[
Your use-case would be achieved by Workflow wherein you can capture the leaver event vai Identity Attribute change and add filters to the User Accounts and trigger Manage Access step and loop in the JDBC specific accesses that the user holds.
Your rule will work but if an access was assigned by request center SailPoint will not remove it unless you force it out to.
Hi @amansingh thank you for the update and currently we are not using workflows in IDN.
I have written the above rule code and it is trying to remove only one entitlement and rest entitlements are still showing up and user is getting disabled for use case during the user leaver process use case 2.
<?xml version='1.0' encoding='UTF-8'?>
This 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.
<![CDATA[
I have written below before provisioning rule for JDBC source and getting Constructor error: Can\u0027t create instance of an interface: interface java.sql.Connection error and any input please.
<?xml version='1.0' encoding='UTF-8'?>
EBSDBBeforeProvisoning Before Provisioning Rule which removes all the roles/permissions from EBSDB and set lock and expire upon user termination.
<![CDATA[
import java.util.ArrayList;
import java.util.List;
import sailpoint.object.Identity;
import sailpoint.object.ProvisioningPlan.AccountRequest;
import sailpoint.object.ProvisioningPlan.AttributeRequest;
import sailpoint.object.ProvisioningPlan;
import sailpoint.tools.Util;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import sailpoint.api.SailPointContext;
import sailpoint.connector.JDBCConnector;
import sailpoint.object.Application;
import sailpoint.object.ProvisioningPlan.PermissionRequest;
import sailpoint.object.ProvisioningResult;
import sailpoint.object.Schema;
import java.sql.Connection;
import java.sql.SQLException;
import sailpoint.object.Schema;
log.debug("Inside Rule - BeforeProvisioning - EBSDBBeforeProvisoning");
ProvisioningResult result = new ProvisioningResult();
Identity nativeIdentity = plan.getIdentity();
List accountRequests = plan.getAccountRequests();
//List entitlemetsToBeRemoved = new ArrayList();
String currentLCS = identity.getAttribute("cloudLifecycleState");
Connection connection = new Connection();
log.debug("currentLCS:: "+currentLCS);
if (accountRequests != null) {
for (AccountRequest accountRequest : accountRequests) {
try {
if ( AccountRequest.Operation.Modify.equals( accountRequest.getOperation() ) ) {
log.debug("nativeIdentity is " + nativeIdentity);
AttributeRequest attrReq = accountRequest.getAttributeRequest("PRIV");
log.debug("attrReq is " + attrReq);
if ( accountRequest != null ) {
if ( attrReq != null && ProvisioningPlan.Operation.Remove.equals(attrReq.getOperation()) ) {
List entitlemetsToBeRemoved = new ArrayList();
if (attrReq.getValue()!=null && attrReq.getValue() instanceof String) {
String attrValue = (String) attrReq.getValue();
entitlemetsToBeRemoved .add(attrValue);
} else if (attrReq.getValue()!=null && attrReq.getValue() instanceof List) {
List attrValueList = (List) attrReq.getValue();
entitlemetsToBeRemoved .addAll(attrValueList);
}
for (String attval:entitlemetsToBeRemoved){
String queryStatement = "revoke "+attval+" from "+nativeIdentity;
log.debug("queryStatement is " + queryStatement);
PreparedStatement statement = connection.prepareStatement(queryStatement);
statement.executeUpdate();
//statement.close();
}
}
}
result.setStatus( ProvisioningResult.STATUS_COMMITTED );
} else if (AccountRequest.Operation.Disable.equals(accountRequest.getOperation()) && Util.nullSafeCaseInsensitiveEq("terminated",currentLCS)) {
log.debug("operation disable loop:: ");
// String nativeIdentity = accountRequest.getNativeIdentity();
log.debug("ApplicationName"+application.getName());
log.debug("nativeIdentity"+nativeIdentity);
AttributeRequest attrReq = accountRequest.getAttributeRequest("disablePRIV");
List entitlemetsToBeRemoved = new ArrayList();
log.debug("attrReq is " + attrReq);
if (attrReq.getValue()!=null && attrReq.getValue() instanceof String) {
String attrValue = (String) attrReq.getValue();
entitlemetsToBeRemoved .add(attrValue);
} else if (attrReq.getValue()!=null && attrReq.getValue() instanceof List) {
List attrValueList = (List) attrReq.getValue();
entitlemetsToBeRemoved .addAll(attrValueList);
}
for (String attval:entitlemetsToBeRemoved){
String queryStatement = "revoke "+attval+" from "+nativeIdentity;
log.debug("queryStatement is " + queryStatement);
PreparedStatement statement = connection.prepareStatement(queryStatement);
statement.executeUpdate();
//statement.close();
result.setStatus(ProvisioningResult.STATUS_COMMITTED);
}
String accountlock =" alter user " + nativeIdentity + " account lock password expire";
PreparedStatement DisableStatement = connection.prepareStatement( accountlock );
DisableStatement.executeUpdate();
//DisableStatement.close();
result.setStatus(ProvisioningResult.STATUS_COMMITTED);
}
else {
// Unknown operation!
}
}
catch( SQLException e ) {
result.setStatus( ProvisioningResult.STATUS_FAILED );
result.addError( e );
}
}
}
]]>
</Source>
