Another screenshot of my configuration and provisioning rule below.
<?xml version='1.0' encoding='UTF-8'?>
<!DOCTYPE Rule PUBLIC "sailpoint.dtd" "sailpoint.dtd">
<Rule name="mySQLProvisioningRule" 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.ResultSet;
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;
import org.apache.logging.log4j.Logger;
import org.apache.logging.log4j.LogManager;
public Object 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 selectStatement;
PreparedStatement updateStatement;
PreparedStatement insertStatement;
// Initialize the log for this rule
Logger log = LogManager.getLogger("rule.JDBC");
log.info("rule.JDBC: Start");
if ( plan != null ) {
List accounts = plan.getAccountRequests();
if ( ( accounts != null ) && ( accounts.size() > 0 ) ) {
for ( AccountRequest account : accounts ) {
try {
if ( AccountRequest.Operation.Create.equals( account.getOperation() ) ) {
// Check if the user exist on the sql database
selectStatement = connection.prepareStatement("SELECT * FROM users WHERE userID = ?");
selectStatement.setString(1, getAttributeRequestValue(account, "userID"));
log.info("Searched user account's employeeId value on the database: " + getAttributeRequestValue(account, "userID"));
ResultSet resultSet = selectStatement.executeQuery();
log.debug("Result from the SQL search: " + resultSet);
if (resultSet.next()) {
// User already exists, update the user
log.info("The user already exists");
updateStatement = connection.prepareStatement("UPDATE users SET username=?, capability=?, firstname=?, lastname=?, status=?, locked=?, test=? WHERE userID = ?");
updateStatement.setString(1, getAttributeRequestValue(account, "username"));
updateStatement.setString(2, getAttributeRequestValue(account, "capability"));
updateStatement.setString(3, getAttributeRequestValue(account, "firstname"));
updateStatement.setString(4, getAttributeRequestValue(account, "lastname"));
updateStatement.setString(5, getAttributeRequestValue(account, "status"));
updateStatement.setString(6, getAttributeRequestValue(account, "locked"));
updateStatement.setString(7, getAttributeRequestValue(account, "test"));
updateStatement.setString(8, getAttributeRequestValue(account, "userID"));
updateStatement.executeUpdate();
log.info("Updated user on the database");
} else {
// User does not exist, create the user
log.info("The user does not exist");
// Creation of the user account with only the userID value
insertStatement = connection.prepareStatement("INSERT INTO users (userID) VALUES (?)");
insertStatement.setString(1, getAttributeRequestValue(account, "userID"));
insertStatement.executeUpdate();
log.info("Creation of user's userID in the database:" + getAttributeRequestValue(account, "userID"));
log.debug("Value of the retreived user's userID from IIQ: " + getAttributeRequestValue(account, "userID"));
// Check if the user was correctly added to the database
selectStatement = connection.prepareStatement("SELECT * FROM users WHERE userID = ?");
selectStatement.setString(1, getAttributeRequestValue(account, "userID"));
log.info("Searching the newly added user to the database: " + getAttributeRequestValue(account, "userID"));
ResultSet resultSetAdded = selectStatement.executeQuery();
log.debug("Result from the SQL search: " + resultSetAdded);
if (resultSetAdded.next()) {
// The new user was correctly added to the database
log.info("Retreived created user on the database");
updateStatement = connection.prepareStatement("UPDATE users SET username=?, capability=?, firstname=?, lastname=?, status=?, locked=?, test=? WHERE userID = ?");
updateStatement.setString(1, getAttributeRequestValue(account, "username"));
updateStatement.setString(2, getAttributeRequestValue(account, "capability"));
updateStatement.setString(3, getAttributeRequestValue(account, "firstname"));
updateStatement.setString(4, getAttributeRequestValue(account, "lastname"));
updateStatement.setString(5, getAttributeRequestValue(account, "status"));
updateStatement.setString(6, getAttributeRequestValue(account, "locked"));
updateStatement.setString(7, getAttributeRequestValue(account, "test"));
updateStatement.setString(8, getAttributeRequestValue(account, "userID"));
updateStatement.executeUpdate();
log.info("Provisioned user on the database");
} else {
// The new user was not correctly added to the database
log.info("Not retreived user, closing now");
}
}
} else if ( AccountRequest.Operation.Modify.equals( account.getOperation() ) ) {
// Modify account request -- change role
PreparedStatement statement = connection.prepareStatement( "update users set role = ? where login = ?" );
statement.setString ( 2, (String) account.getNativeIdentity() );
if ( account != null ) {
AttributeRequest attrReq = account.getAttributeRequest("role");
if ( attrReq != null && ProvisioningPlan.Operation.Remove.equals(attrReq.getOperation()) ) {
statement.setNull ( 1, Types.NULL );
statement.executeUpdate();
} else {
statement.setString(1,attrReq.getValue());
statement.executeUpdate();
}
}
result.setStatus( ProvisioningResult.STATUS_COMMITTED );
} else if ( AccountRequest.Operation.Delete.equals( account.getOperation() ) ) {
PreparedStatement statement = connection.prepareStatement( (String) application.getAttributeValue( "account.deleteSQL" ) );
statement.setString ( 1, (String) account.getNativeIdentity() );
statement.executeUpdate();
result.setStatus( ProvisioningResult.STATUS_COMMITTED );
} else if ( AccountRequest.Operation.Disable.equals( account.getOperation() ) ) {
// Disable, not supported.
} else if ( AccountRequest.Operation.Enable.equals( account.getOperation() ) ) {
// Enable, not supported.
} else if ( AccountRequest.Operation.Lock.equals( account.getOperation() ) ) {
// Lock, not supported.
} else if ( AccountRequest.Operation.Unlock.equals( account.getOperation() ) ) {
// Unlock, not supported.
} else {
// Unknown operation!
}
}
catch( SQLException e ) {
result.setStatus( ProvisioningResult.STATUS_FAILED );
result.addError( e );
}
finally {
if(statement != null) {
statement.close();
}
}
}
}
}
return result;
]]></Source>
</Rule>
