I have written below JDBC provisioning rule to revoke role/entitlement during certification revoke but it is throwing error as “[“java.sql.SQLSyntaxErrorException: ORA-00990: missing or invalid privilege\n”,“java.sql.SQLSyntaxErrorException: ORA-00990: missing or invalid privilege\n”]”.
<?xml version='1.0' encoding='UTF-8'?>
<!DOCTYPE Rule PUBLIC "sailpoint.dtd" "sailpoint.dtd">
<Rule name="SampleDBStage" 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.Modify.equals( account.getOperation() ) ) {
String nativeIdentity = (String) account.getNativeIdentity();
String PRIVValue = (String) getAttributeRequestValue(account,"PRIV");
AttributeRequest attrReq = account.getAttributeRequest("PRIV");
PreparedStatement statement = connection.prepareStatement("revoke ? from ?");
statement.setString(2, (String) account.getNativeIdentity());
statement.setString (1, attrReq.getValue());
if ( account != null ) {
// AttributeRequest attrReq = account.getAttributeRequest("PRIV");
if ( attrReq != null && ProvisioningPlan.Operation.Remove.equals(attrReq.getOperation()) ) {
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>
@kalyannambi2010 Does your service account has all the rights? it seems its a permission issue. Are you able to add / remove the entitlements using access requests?
Yes, service account has all the rights and we do not have access requests enabled yet in IDN.
But we are able to execute below SQL revoke statement PreparedStatement statement = connection.prepareStatement(“revoke Test from XYZ”); wuth some hard coded values for testing but when we try to execute SQL revoke statement it is throwing error. PreparedStatement statement = connection.prepareStatement(“revoke ? from ?”);
Can you please have your logger enabled and print statement before executing it? just to check if the query is fine. Otherwise your code is looking fine.
I have enabled the logs and it is pulling the values properly but when we try to execute SQL revoke statement it is throwing error. PreparedStatement statement = connection.prepareStatement(“revoke ? from ?”);
Yes, you are correct but here in my case table is not fixed so we are trying to use revoke statement as below.
We are able to execute below SQL revoke statement PreparedStatement statement = connection.prepareStatement(“revoke rolename from testuser”); wuth some hard coded values for testing but when we try to execute SQL revoke statement it is throwing error. PreparedStatement statement = connection.prepareStatement(“revoke ? from ?”);
Hi @Sriindugula and @uday_kilambiCTS thank you for the inputs and now throwing SQL [“java.sql.SQLSyntaxErrorException: ORA-00900: invalid SQL statement\n”,“java.sql.SQLSyntaxErrorException: ORA-00900: invalid SQL statement\n”].
Please find the below updated rule and suggest where I am missing the syntax.
<?xml version='1.0' encoding='UTF-8'?>
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.
<![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) {
It could be mostly because of the format of attributeRequest.getValue, it could be a list type.
Hence, add a condition to verify if the attReq.getValue is a list or string, if it is a string store the value in a variable, if it is a list, assign the first value from the list to the variable.
Use this variable to construct your query rather than using attreq,getValue() directly. It should resolve your issue.
it should be like below:
String queryStatement = "revoke “+attrReqValue+” from "+nativeIdentity;
and
PreparedStatement statement = connection.prepareStatement(queryStatement);
This rule code works when I try to revoke one entitlement/role from user access review. But the rule code is not working when I try to revoke more than one entitlement/role from user access review and getting error “BSF info: EBSDBStage at line: 0 column: columnNo","The application script threw an exception: java.lang.ClassCastException: Cannot cast java.util.ArrayList to java.lang.String BSF "]”