Issue with Attribute Provisioning from SailPoint IIQ to JDBC Database

IdentityIQ (IIQ) IIQ Discussion and Questions
, ,
1

Hi Team,

I am using a Delimited File as the authoritative source and have successfully aggregated the user attributes into SailPoint IIQ. However, I am unable to push attribute changes (such as First Name and Last Name) from SailPoint IIQ to the database table.

I have already configured the required Target Mappings under Identity Mappings, but the updates are not being reflected in the database.

Could you please help me understand if there are any additional configurations required for JDBC provisioning or attribute synchronization?

Thanks

capture
capture1861×880 34.7 KB

2

Hi @suresh11 - Have you configured the provisioning rules? Natively IIQ does not know “how” to provision to JDBC, so you have to setup rules in the connectors. You can use a global rule that covers all operation types or independently setup rules for the operations. When the connector is called, the ProvisioningPlan is parsed for the operation and will use the appropriate rule. Attribute sync for example will use a Modify provision rule.

image
image614×363 24.5 KB

You can try something like this example:



import java.sql.PreparedStatement;
import sailpoint.object.ProvisioningPlan.AccountRequest;
import sailpoint.object.ProvisioningPlan.AttributeRequest;
import sailpoint.object.ProvisioningResult;
import org.apache.commons.logging.LogFactory;

ProvisioningResult result = new ProvisioningResult();
org.apache.commons.logging.Log log = LogFactory.getLog("rule.JDBC.Provisioning");

if (plan != null) {
for (AccountRequest req : plan.getAccountRequests()) {
if (AccountRequest.Operation.Create.equals(req.getOperation())) {
PreparedStatement stmt = null;
try {
String nativeId = req.getNativeIdentity();
String firstName = "";
String lastName = "";

AttributeRequest fnReq = req.getAttributeRequest("firstname");
if (fnReq != null && fnReq.getValue() != null) firstName = (String) fnReq.getValue();

AttributeRequest lnReq = req.getAttributeRequest("lastname");
if (lnReq != null && lnReq.getValue() != null) lastName = (String) lnReq.getValue();

String sql = "INSERT INTO your_user_table (emp_id, first_name, last_name) VALUES (?, ?, ?)";
stmt = connection.prepareStatement(sql);
stmt.setString(1, nativeId);
stmt.setString(2, firstName);
stmt.setString(3, lastName);

stmt.executeUpdate();
result.setStatus(ProvisioningResult.STATUS_COMMITTED);

} catch (Exception e) {
log.error(e.getMessage());
result.setStatus(ProvisioningResult.STATUS_FAILED);
result.addError(e);
} finally {
if (stmt != null) {
try { stmt.close(); } catch (Exception se) {}
}
}
}
}
}
return result;
3

Hi,

Am using this global provisioning rule

import java.sql.PreparedStatement;
import java.sql.Types;
import java.util.List;

import sailpoint.object.ProvisioningPlan;
import sailpoint.object.ProvisioningPlan.AccountRequest;
import sailpoint.object.ProvisioningPlan.AttributeRequest;
import sailpoint.object.ProvisioningPlan.Operation;
import sailpoint.object.ProvisioningResult;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

Log log = LogFactory.getLog(“rule.JDBC.GlobalProvisioning”);
ProvisioningResult result = new ProvisioningResult();
PreparedStatement stmt = null;

try {

if (plan == null || connection == null) {

  throw new Exception("Provisioning plan or JDBC connection is null");

}
List requests = plan.getAccountRequests();

if (requests != null) { 

  for (Object obj : requests) {

    AccountRequest req = (AccountRequest) obj; 
    if (AccountRequest.Operation.Modify.equals( req.getOperation())) { 
      String employeeId = req.getNativeIdentity(); 
      if (employeeId == null || employeeId.trim().isEmpty()) { 
        throw new Exception("EMPLOYEE_ID is missing"); 
      }
      AttributeRequest firstReq = req.getAttributeRequest("FIRST_NAME");
      AttributeRequest lastReq = req.getAttributeRequest("LAST_NAME"); 
      boolean updateFirst = firstReq != null; 
      boolean updateLast = lastReq != null; 
      if (!updateFirst && !updateLast) { 
        log.info("No name attributes to update for " + employeeId);
        continue;
      } 
      StringBuilder sql = new StringBuilder("UPDATE USERS SET "); 
      if (updateFirst) { 
        sql.append("FIRST_NAME = ?");
      } if (updateFirst && updateLast) { 
        sql.append(", ");
      } if (updateLast) {
        sql.append("LAST_NAME = ?");
      } 
      sql.append(" WHERE EMPLOYEE_ID = ?"); 
      stmt = connection.prepareStatement(sql.toString());
      int index = 1; 
      if (updateFirst) { 
        if (firstReq.getValue() == null || Operation.Remove.equals(firstReq.getOperation())) { 
          stmt.setNull(index++, Types.VARCHAR); 
        } else { 
          stmt.setString(index++, String.valueOf(firstReq.getValue())); 
        } 
      } if (updateLast) { 
        if (lastReq.getValue() == null || Operation.Remove.equals(lastReq.getOperation())) { 
          stmt.setNull(index++, Types.VARCHAR);
        } else { 
          stmt.setString(index++, String.valueOf(lastReq.getValue())); 
        }
      } 
      stmt.setString(index, employeeId); 
      int rows = stmt.executeUpdate(); 
      if (rows == 0) { 
        throw new Exception( "No USERS row found for EMPLOYEE_ID: " + employeeId); 
      } 
      log.info("Updated USERS for EMPLOYEE_ID: " + employeeId); 
      stmt.close();
      stmt = null;
    } 
  }
}
result.setStatus(ProvisioningResult.STATUS_COMMITTED);

} catch (Exception e) {
log.error(“JDBC provisioning failed”, e);result.setStatus(ProvisioningResult.STATUS_FAILED);result.addError(e);
} finally {if (stmt != null) {
try {stmt.close();}
catch (Exception e) {log.warn(“Unable to close JDBC statement”, e);}
}
}
return result;

4

Hi @suresh11 did you configure “Create Provisioning Policy”?.

5

Hi @santhirajumunganda,

Yes I configured create provisioning policy.

Thanks

6

can you pull the provision transactions and logs as well

7

Hello Suresh. Your firstname → Employee_DB.FIRST_NAME mapping looks fine. Since you also mentioned Last Name, could you check whether lastname has its own target mapping to Employee_DB.LAST_NAME? Target mappings are set per attribute, and the screenshot only shows First Name.

Before changing the JDBC rule, I would suggest checking whether IIQ is generating a Modify request at all. Your firstname mapping checks AuthEmployee → First Name first, then Employee → givenName. If AuthEmployee already has a value, changes from Employee will not update the identity’s first name. Could you confirm which one is your Delimited File source? For one test identity, try the following:

  1. Change the name in the source that takes priority and aggregate it.
  2. Run Identity Refresh with Refresh identity attributes and Synchronize Attributes enabled, with the identity in scope.
  3. Confirm the identity cube value changed, then check whether a Modify request was generated for Employee_DB.

The Provisioning Transactions table logs failures only by default, so set Maximum Log Level to Success under Global Settings → IdentityIQ Configuration → Miscellaneous for the test, otherwise a Modify that succeeds (or commits without updating) will not show up there.

If the cube changes but no Modify request appears, I would check the synchronization settings and whether the identity has a correlated Employee_DB account. If the Modify request reaches your rule, check whether req.getNativeIdentity() matches the EMPLOYEE_ID in your USERS table. Your rule also appears able to return committed when no name attribute was updated, so logging the attributes received and the executeUpdate() row count would help confirm whether the database was actually updated.

Could you share whether the Modify request is generated and what the logs show?

8

@suresh11 - can you add a log at the beginning of your provisioning rule? Something with “Starting JDBC Provisioning rule” along with the operation?’