Hello,
Our company has a SQL database table called ActiveDirectory.ADP_Update that currently pulls specified identity data from our HR system “ADP WorkforceNow”. We are wanting to use SailPoint IDN to take over this provisioning process so that it is automated. A JDBC connector has been set up for this database table in our SailPoint tenant and it has successfully aggregated data from the ActiveDirectory.ADP_Update table. We are now working on setting up the provisioning policy for this source so that SailPoint will modify the nativeIdentity in the database or create the nativeIdentity if necessary. The data provisioning from SailPoint to this database table will only be employee information (no entitlements) from our ADP authoritative source. Currently the JDBC connector has 33 specified attributes in the account schema that will need to be provisioned for each identity. Would the below provisioning rule allow the JDBC connector source to either CREATE or MODIFY data to the specified ActiveDirectory.ADP_Update database table?
<?xml version='1.0' encoding='UTF-8'?>
<!DOCTYPE Rule PUBLIC "sailpoint.dtd" "sailpoint.dtd">
<Rule name="Provisioning Rule with Work E-mail as NativeIdentity" type="JDBCProvision">
<Description>
This JDBC rule checks if the account exists, provisions only the listed attributes if the account does not exist, and updates only changed attributes if the account exists.
The rule uses Work E-mail as the unique identifier in the ActiveDirectory.ADP_Update table and no longer performs delete operations.
</Description>
<Source><![CDATA[
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.ResultSet;
import java.util.List;
import sailpoint.object.ProvisioningPlan;
import sailpoint.object.ProvisioningPlan.AccountRequest;
import sailpoint.object.ProvisioningPlan.AttributeRequest;
import sailpoint.object.ProvisioningResult;
// Helper method to get the attribute value from AccountRequest
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;
ResultSet resultSet;
if (plan != null) {
List<AccountRequest> accounts = plan.getAccountRequests();
if ((accounts != null) && (accounts.size() > 0)) {
for (AccountRequest account : accounts) {
try {
// The nativeIdentity in SailPoint corresponds to Work E-mail (used as the unique identifier)
String workEmail = (String) account.getNativeIdentity(); // This corresponds to Work E-mail in AD table
String selectQuery = "SELECT work_email FROM ActiveDirectory.ADP_Update WHERE work_email = ?";
statement = connection.prepareStatement(selectQuery);
statement.setString(1, workEmail);
resultSet = statement.executeQuery();
// Check if the account already exists (based on Work E-mail)
if (resultSet.next()) {
// Account exists, we will update only changed attributes
if (AccountRequest.Operation.Modify.equals(account.getOperation())) {
// Check each attribute to see if it needs to be updated
// Here, we provision/update only the listed attributes
// List of attributes to be provisioned/modified
String[] attributes = {
"Payroll Name", "Position ID", "Windows User Name", "Home Department Description",
"Location Description", "Job Title Description", "Reports To Position ID", "Reports To Name",
"EmpADAccount", "SpvADAccount", "WFNPhone_Ext", "WFNPhone_Ext", "WFNWorkPhone",
"WFNExtension", "ADWorkPhone", "ADExtension", "Last Name", "First Name", "Middle Name",
"Department Number", "Work E-mail", "Preferred Name", "Approval Limit", "Location Code",
"Market", "Work Contact Work Mobile", "Personal Contact Home Phone", "Personal Contact Personal Mobile",
"Personal Contact Personal Email", "Work Address Works From Home", "Pronouns", "NMLS_ID",
"LoanOfficerLink", "Certifications"
};
for (String attribute : attributes) {
String value = getAttributeRequestValue(account, attribute);
if (value != null) {
String updateQuery = "UPDATE ActiveDirectory.ADP_Update SET " + attribute + " = ? WHERE work_email = ?";
statement = connection.prepareStatement(updateQuery);
statement.setString(1, value);
statement.setString(2, workEmail);
statement.executeUpdate();
}
}
}
result.setStatus(ProvisioningResult.STATUS_COMMITTED);
} else {
// Account does not exist, so create a new account with all attributes
if (AccountRequest.Operation.Create.equals(account.getOperation())) {
String insertQuery = "INSERT INTO ActiveDirectory.ADP_Update (work_email, ";
// Prepare the list of attributes and values
StringBuilder attributeNames = new StringBuilder();
StringBuilder valuePlaceholders = new StringBuilder();
String[] attributes = {
"Payroll Name", "Position ID", "Windows User Name", "Home Department Description",
"Location Description", "Job Title Description", "Reports To Position ID", "Reports To Name",
"EmpADAccount", "SpvADAccount", "WFNPhone_Ext", "WFNPhone_Ext", "WFNWorkPhone",
"WFNExtension", "ADWorkPhone", "ADExtension", "Last Name", "First Name", "Middle Name",
"Department Number", "Work E-mail", "Preferred Name", "Approval Limit", "Location Code",
"Market", "Work Contact Work Mobile", "Personal Contact Home Phone", "Personal Contact Personal Mobile",
"Personal Contact Personal Email", "Work Address Works From Home", "Pronouns", "NMLS_ID",
"LoanOfficerLink", "Certifications"
};
for (String attribute : attributes) {
if (getAttributeRequestValue(account, attribute) != null) {
attributeNames.append(attribute).append(", ");
valuePlaceholders.append("?, ");
}
}
attributeNames.delete(attributeNames.length() - 2, attributeNames.length()); // Remove last comma
valuePlaceholders.delete(valuePlaceholders.length() - 2, valuePlaceholders.length()); // Remove last comma
insertQuery += attributeNames.toString() + ") VALUES (?" + ", ?".repeat(attributeNames.toString().split(", ").length - 1) + ")";
statement = connection.prepareStatement(insertQuery);
int index = 1;
for (String attribute : attributes) {
String value = getAttributeRequestValue(account, attribute);
if (value != null) {
statement.setString(index++, value);
}
}
statement.setString(index, workEmail); // Add the Work E-mail as the last value
statement.executeUpdate();
result.setStatus(ProvisioningResult.STATUS_COMMITTED);
}
}
} catch (SQLException e) {
result.setStatus(ProvisioningResult.STATUS_FAILED);
result.addError(e);
} finally {
if (statement != null) {
statement.close();
}
if (resultSet != null) {
resultSet.close();
}
}
}
}
}
return result;
]]></Source>
</Rule>