Which IIQ version are you inquiring about?
8.4 version
Share all details about your problem, including any error messages you may have received.
I am trying to build JDBC provisioning Rule for Creating a user in table and Update the table for User Roles and deleting the user with stored procedure queries.
I am using the below rule for JDBC create, Update, Disable, Delete operations.
Rule:
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.Date;
import java.sql.DriverManager;
import java.sql.SQLException;
import sailpoint.object.TaskResult;
import sailpoint.tools.Util;
import java.sql.PreparedStatement;
import java.sql.Statement;
import java.sql.ResultSet;
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;
import sailpoint.tools.Util;
//
// Internal method for grabbing an Attribute Request Value.
//
public object getAttributeRequestValue(AccountRequest acctReq, String attribute) {
if(acctReq!= null){
AttributeRequest attrReq = acctReq.getAttributeRequest(attribute);
if(attrReq!= null){
return attrReq.getValue();
}
}
return null;
}
//
// JDBC Provisioning Rule Body
//
// We will handle these cases right now:
//
// Account Request Create
// Account Request Modify
// Account Request Delete
// Account Request Lock/Unlock
// Account Request Enable/Disable
//
Date now = new Date();
System.out.println("\n\n\n\n\n");
System.out.println("*********************************************");
System.out.println(" Entering Provisioning Rule ");
System.out.println("Current Time = "+ now.toString());
System.out.println("*********************************************");
//
// The ProvisioningResult is the return object for this type of rule. we'll create it her and then populate it later.
//
ProvisioningResult result = new ProvisioningResult();
//
// Check if the plan is null or not, if not null, Process it..........
//
if(plan != null){
System.out.println("*** \n The Provisioning Plan being passed in = \n***\n" + plan.toXml() +"\n****************************************");
List<AccountRequest> accounts = plan.getAccountRequests();
//
// Get all Account Requests out of the plan
//
if ((accounts != null) && (accounts.size() > 0)) {
//
// If the plan contains one or more account requests, we'll iterate through them
//
for (AccountRequest account : accounts) {
try {
//
// All of the account operations will reside in a try block in case we have any errors, we can mark the provisioningresult as "Failed" if we have an issue.
//
if (AccountRequest.Operation.Create.equals(account.getOperation())) {
//
// CREATE Operation
//
System.out.println("Account Request Operation = Create");
CallableStatement callableStmt = connection.prepareCall("{call IPGRBUSERMANAGEMENT.CREATERBUSER(?,?,?,?,?,?)}");
callableStmt.setString(1, getAttributeRequestValue(account, "P_ORACLEUSERNAME"));
callableStmt.setString(2, getAttributeRequestValue(account, "P_ORACLEPASSWORD")); // Assuming this is the password
callableStmt.setString(3, getAttributeRequestValue(account, "P_PROFILE"));
callableStmt.setString(4, getAttributeRequestValue(account, "p_default_tablespace"));
callableStmt.setString(5, getAttributeRequestValue(account, "p_default_temp_tablespace"));
callableStmt.setString(6, getAttributeRequestValue(account, "p_language_id"));
callableStmt.executeUpdate();
// Successful Create, so mark result as COMMITTED
result.setStatus(ProvisioningResult.STATUS_COMMITTED);
} else if (AccountRequest.Operation.Modify.equals(account.getOperation())) {
//
// MODIFY Operation
//
System.out.println("Account Request Operation = Modify");
List<String> removeRoles = new ArrayList<>();
List<String> addRoles = new ArrayList<>();
// Get current roles
Statement currStmt = connection.createStatement();
ResultSet rs = currStmt.executeQuery("SELECT * FROM IPGIRBUSERHASBUSINESSROLEVIEW WHERE id = " + getAttributeRequestValue(account, "userId"));
List<String> currentRoles = new ArrayList<>();
while (rs.next()) {
currentRoles.add(rs.getString("BUSINESS_ROLE_NAME"));
}
// Get all Attribute Requests and filter for roles
List<AttributeRequest> modAttrRequests = account.getAttributeRequests();
if (modAttrRequests != null) {
for (AttributeRequest req : modAttrRequests) {
if (req.getName().equals("BUSINESS_ROLE_NAME")) {
if (ProvisioningPlan.Operation.Remove.equals(req.getOperation())) {
if (req.getValue() instanceof String) {
removeRoles = Util.csvToList((String) req.getValue(), true);
} else if (req.getValue() instanceof List) {
removeRoles = (List<String>) req.getValue();
}
} else if (ProvisioningPlan.Operation.Add.equals(req.getOperation())) {
if (req.getValue() instanceof String) {
addRoles = Util.csvToList((String) req.getValue(), true);
} else if (req.getValue() instanceof List) {
addRoles = (List<String>) req.getValue();
}
}
}
}
}
// Process role removals
if (!removeRoles.isEmpty()) {
for (String role : removeRoles) {
CallableStatement removeRoleStmt = connection.prepareCall("{call IPGRBUSERMANAGEMENT.REMOVEBUSINESSROLETOUSER(?,?)}");
removeRoleStmt.setString(1, getAttributeRequestValue(account, "userId"));
removeRoleStmt.setString(2, role);
removeRoleStmt.executeUpdate();
}
}
// Process role additions
if (!addRoles.isEmpty()) {
for (String role : addRoles) {
CallableStatement addRoleStmt = connection.prepareCall("{call IPGRBUSERMANAGEMENT.ADDBUSINESSROLETOUSER(?,?)}");
addRoleStmt.setString(1, getAttributeRequestValue(account, "userId"));
addRoleStmt.setString(2, role);
addRoleStmt.executeUpdate();
}
}
// Successful Modify, so mark result as COMMITTED
result.setStatus(ProvisioningResult.STATUS_COMMITTED);
} else if (AccountRequest.Operation.Delete.equals(account.getOperation())) {
//
// DELETE Operation
//
System.out.println("Account Request Operation = Delete");
CallableStatement callableStmt = connection.prepareCall("{call IPGRBUSERMANAGEMENT.removeRBUser(?,?,?)}");
callableStmt.setString(1, getAttributeRequestValue(account, "userId"));
callableStmt.setString(2, "status"); // Provide appropriate status
callableStmt.setString(3, "error_message"); // Provide appropriate error message if needed
callableStmt.executeUpdate();
// Successful Delete, so mark result as COMMITTED
result.setStatus(ProvisioningResult.STATUS_COMMITTED);
} else if (AccountRequest.Operation.Disable.equals(account.getOperation())) {
System.out.println("Account Request Operation = Disable");
CallableStatement callableStmt = connection.prepareCall("{call IPGRBUSERMANAGEMENT.DISABLERBUSER(?)}");
callableStmt.setString(1, getAttributeRequestValue(account, "userId"));
callableStmt.executeUpdate();
// Successful Disable, so mark result as COMMITTED
result.setStatus(ProvisioningResult.STATUS_COMMITTED);
} else {
// Unknown operation!
System.out.println("Unknown operation [" + account.getOperation() + "]!");
}
} catch (SQLException e) {
System.out.println("Error: " + e);
result.setStatus(ProvisioningResult.STATUS_FAILED);
result.addError(e);
}
} // account request loop
} // if account requests exist
} // if plan not null
System.out.println("****************************************");
System.out.println("****************************************");
System.out.println("Exiting Provisioning Rule \n Result= \n" + result.toXml(false));
System.out.println("****************************************");
System.out.println("****************************************");
System.out.println("\n\n\n\n\n");
return result;
I am facing the error: java.sql.SQLException: missing in or out parameter at index:: 7
Team any idea about the error and i appreciate if you have done before please share your inputs.
Thanks
Security consultant