Updating role information in database using global provisioning rule

Which IIQ version are you inquiring about?

Version 8.4

Please share any other relevant files that may be required (for example, logs).

I am using the below mentioned global provisioning rule for all operations, and I want the add role operation also to provision the role to the target database and update the values in tables.

can you help me with the modifications in the rule that I need to do achieve this requirement

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
  //
  System.out.println("\n\n\n\n\n");
  System.out.println("*********************************************");
  System.out.println(" Entering Provisioning Rule Application ");
    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 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, account.getNativeIdentity().toUpperCase());
            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"));
            //Execute the stored procedure
            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 removeRoles = new ArrayList();
            List addRoles = new ArrayList();
            // Get current roles
            Statement currStmt = connection.createStatement();
            ResultSet rs = currStmt.executeQuery("SELECT * FROM IPGIRBUSERHASBUSINESSROLEVIEW WHERE id = " + account.getNativeIdentity().toUpperCase());
			
			// This is actual query where all roles are present here "SELECT * FROM GENEVA_ADMIN.BUSINESSROLE;"
			// This is exact query where we are storing all username with assigned roles "SELECT * FROM IPGIRBUSERHASBUSINESSROLEVIEW;"
            List currentRoles = new ArrayList();
            while (rs.next()) {
              currentRoles.add(rs.getString("BUSINESS_ROLE_NAME"));
            }
            // Get all Attribute Requests and filter for roles
            List 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 = 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 = req.getValue();
                    }
                  }
                }
              }
            }
            // Process role removals
            if (!removeRoles.isEmpty()) {
              for (String role : removeRoles) {
                CallableStatement removeRoleStmt = connection.prepareCall("{call IPGRBUSERMANAGEMENT.REMOVEBUSINESSROLETOUSER(?,?)}");
                removeRoleStmt.setString(1, account.getNativeIdentity().toUpperCase());
                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, account.getNativeIdentity().toUpperCase());
                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, account.getNativeIdentity().toUpperCase());
           // callableStmt.setString(1, "status"); // Provide appropriate status
           // callableStmt.setString(2, "error_message"); // Provide appropriate error message if needed
	   //     callableStmt.registerOutParameter(1,Types.INTEGER);
	   //     callableStmt.registerOutParameter(2,Types.VARCHAR);
            callableStmt.executeUpdate();
                 //      String status= callableStmt.getString(2);
		//	int error_message=callableStmt.getInt(3);		
		//	System.out.println("Status Values:"+status);
		//	System.out.println("error_message:"+error_message);
            // 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, account.getNativeIdentity().toUpperCase());
            callableStmt.executeUpdate();

            // Successful Disable, so mark result as COMMITTED
            result.setStatus(ProvisioningResult.STATUS_COMMITTED);
          }  

else if (AccountRequest.Operation.Enable.equals(account.getOperation())) {
            System.out.println("Account Request Operation = Enable");
            CallableStatement callableStmt = connection.prepareCall("{call IPGRBUSERMANAGEMENT.ENABLERBUSER(?)}");
            callableStmt.setString(1, account.getNativeIdentity().toUpperCase());
            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 Application. \n  Result=  \n" + result.toXml(false));
  System.out.println("****************************************");
  System.out.println("****************************************");
  System.out.println("\n\n\n\n\n");        
  return result;

Hi @SecurityConsultant123,

for manage the creation and updating groups, you need to managed the group.getOperation() and own relative provisiosing policy.

image

@SecurityConsultant123,

Can you please check if the Business role which you are looking to update in DB is available in global rule provisioning plan variable.

We are trying to update role name from Sailpoint roles tab via manage user access quicklink.

Dear Team,

Please find the detail information for the above code.

we are stuck at updating the role name in the both given queries Add Role operation (Modify) and Remove Role operation (Modify) to these queries (call IPGRBUSERMANAGEMENT.ADDBUSINESSROLETOUSER(?,?) & call IPGRBUSERMANAGEMENT.REMOVEBUSINESSROLETOUSER(?,?) ) via java code.

We are trying in Application XML we are fetching all the user-attached roles within object-type account. Select * from ipgirbuserhasbusinessroleview; when we are trying to execute the above query we are unable to fetch the user with role name.

Thanks

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