Scheduled Task Plugin: Reconciliation Event Generator Database Feed

Tested On: Oracle Identity Manager 11.1.2.3.0
Description: A custom scheduled task used to create reconciliation events for a specific resource object using data from a database table. Trusted or target resource object can be used as long as the required fields are provided.


Scheduled Task Plug-in
Given below are the components that make the plug-in: Scheduled Task Java Class, Plug-in XML, and scheduled task XML. 

	<?xml version="1.0" encoding="UTF-8"?>
	<oimplugins xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
	<plugins pluginpoint="oracle.iam.scheduler.vo.TaskSupport">
	<plugin pluginclass="com.blogspot.oraclestack.scheduledtasks.ReconEventsGeneratorDatabaseSource" version="1.0" name="ReconEventsGeneratorDatabaseSource"/>
	</plugins>
	</oimplugins>

view raw plugin.xml hosted with ❤ by GitHub

	package com.blogspot.oraclestack.scheduledtasks;
	import Thor.API.Exceptions.tcAPIException;
	import Thor.API.Exceptions.tcColumnNotFoundException;
	import Thor.API.Exceptions.tcInvalidLookupException;
	import Thor.API.Operations.tcLookupOperationsIntf;
	import Thor.API.tcResultSet;
	import java.io.Serializable;
	import java.sql.Connection;
	import java.sql.PreparedStatement;
	import java.sql.ResultSet;
	import java.sql.ResultSetMetaData;
	import java.sql.SQLException;
	import java.util.ArrayList;
	import java.util.Date;
	import java.util.HashMap;
	import java.util.Iterator;
	import java.util.List;
	import java.util.Map;
	import java.util.Map.Entry;
	import javax.naming.Context;
	import javax.naming.InitialContext;
	import javax.naming.NamingException;
	import javax.sql.DataSource;
	import oracle.core.ojdl.logging.ODLLevel;
	import oracle.core.ojdl.logging.ODLLogger;
	import oracle.iam.platform.Platform;
	import oracle.iam.reconciliation.api.BatchAttributes;
	import oracle.iam.reconciliation.api.ChangeType;
	import oracle.iam.reconciliation.api.InputData;
	import oracle.iam.reconciliation.api.ReconOperationsService;
	import oracle.iam.reconciliation.api.ReconciliationResult;
	import oracle.iam.scheduler.vo.TaskSupport;
	/**
	* A scheduled task to create reconciliation events of a specified resource object
	* using data from a database table. Trusted or target resource object can be
	* used as long as the required fields are provided.
	* @author rayedchan
	*
	* Additional features:
	* - Dynamic Attribute Mapping via Lookup
	* - Child Data
	*/
	public class ReconEventsGeneratorDatabaseSource extends TaskSupport
	{
	// Logger
	private static final ODLLogger LOGGER = ODLLogger.getODLLogger(ReconEventsGeneratorDatabaseSource.class.getName());
	// OIM API Services
	private ReconOperationsService reconOps = Platform.getService(ReconOperationsService.class);
	// Default Date Format
	private static final String DEFAULT_DATE_FORMAT = "yyyy-MM-dd";
	@Override
	public void execute(HashMap params) throws NamingException, SQLException, tcColumnNotFoundException, tcInvalidLookupException, tcAPIException
	{
	LOGGER.log(ODLLevel.NOTIFICATION, "Scheduled Job Parameters: {0}", new Object[]{params});
	Connection conn = null;
	tcLookupOperationsIntf lookupOps = null;
	try
	{
	// Get the parameters from the scheduled job
	String dataSource = (String) params.get("Data Source"); // JNDI Name for the Data Source
	String resourceObjectName = (String) params.get("Resource Object Name"); // Reconciliation Profile Name
	String tableName = (String) params.get("Table Name"); // Database table name
	String filter = (String) params.get("Filter") == null ? "" : (String) params.get("Filter"); // WHERE clause filter
	String dateFormat = (String) params.get("Date Format") == null ? DEFAULT_DATE_FORMAT : (String) params.get("Date Format"); // Date Format for reconciliation event E.g. "yyyy-MM-dd"
	Boolean ignoreDuplicateEvent = (Boolean) params.get("Ignore Duplicate Event"); // Identical to using IgnoreEvent API; if true, reconciliation event won't be created if there is nothing to update
	String attrMappings = (String) params.get("Mapping Lookup"); // Correlates target field to recon field
	String itResName = (String) params.get("IT Resource Name"); // IT Resource Name required for target sources. Empty for trusted sources.
	// Parameters for Child Data
	String linkColumnName = (String) params.get("Link Column Name"); // Field to relate parent and child table
	// Reconciliation events details
	Boolean eventFinished = true; // No child data provided; mark event to Data Received
	Date actionDate = null; // Event to be processed immediately for null. If a date is specified, defer reconciliation event.
	BatchAttributes batchAttrs = new BatchAttributes(resourceObjectName, dateFormat, ignoreDuplicateEvent);
	// Get database connection from data source
	conn = getDatabaseConnection(dataSource);
	LOGGER.log(ODLLevel.NOTIFICATION, "Retrieved connection for datasource: {0}" , new Object[]{dataSource});
	// Fetch Recon Attr Map Lookup if any
	lookupOps = Platform.getService(tcLookupOperationsIntf.class);
	HashMap<String,String> reconAttrMap = convertLookupToMap(lookupOps, attrMappings);
	LOGGER.log(ODLLevel.NOTIFICATION, "Lookup {0} : {1}" , new Object[]{attrMappings, reconAttrMap});
	// Derive child table mappings
	HashMap<String,String> childTableMappings = new HashMap<String,String>(); // Key = Multivalued Field Name, Code = Target Child Table Name
	HashMap<String,HashMap<String,String>> childColumnMappings = new HashMap<String,HashMap<String,String>>(); // Key = Multivalued Field Name, {Key = Field Name, Value = Target Column Name}
	deriveChildTableMappings(reconAttrMap, childTableMappings, childColumnMappings);
	// Construct list of reconciliation event to be created reading from source database table
	List<InputData> allReconEvents = constructReconciliationEventList(conn, tableName, filter, eventFinished, actionDate, reconAttrMap, itResName, childTableMappings, childColumnMappings, linkColumnName);
	LOGGER.log(ODLLevel.NOTIFICATION, "Recon Events {0}: {1}", new Object[]{allReconEvents.size(), allReconEvents});
	InputData[] events = new InputData[allReconEvents.size()];
	allReconEvents.toArray(events);
	// Create reconciliation events in OIM and process them
	ReconciliationResult result = reconOps.createReconciliationEvents(batchAttrs, events);
	LOGGER.log(ODLLevel.NOTIFICATION, "Success result: {0}", new Object[]{result.getSuccessResult()});
	LOGGER.log(ODLLevel.NOTIFICATION, "Failed result: {0}", new Object[]{result.getFailedResult()});
	}
	catch (tcAPIException e)
	{
	LOGGER.log(ODLLevel.SEVERE, "Could not get lookup: ", e);
	throw e;
	}
	catch (tcInvalidLookupException e)
	{
	LOGGER.log(ODLLevel.SEVERE, "Could not get lookup: ", e);
	throw e;
	}
	catch (tcColumnNotFoundException e)
	{
	LOGGER.log(ODLLevel.SEVERE, "Could not get lookup: ", e);
	throw e;
	}
	catch (SQLException e)
	{
	LOGGER.log(ODLLevel.SEVERE, "Could not get database connection: ", e);
	throw e;
	}
	catch (NamingException e)
	{
	LOGGER.log(ODLLevel.SEVERE, "Could not get database connection: ", e);
	throw e;
	}
	finally
	{
	if(conn != null)
	{
	conn.close();
	}
	if(lookupOps != null)
	{
	lookupOps.close();
	}
	}
	}
	@Override
	public HashMap getAttributes()
	{
	return null;
	}
	@Override
	public void setAttributes()
	{
	}
	/**
	* Derive Child Table Mappings
	* @param reconAttrMap Reconciliation Filed Mappings; Remove child info once processed
	* @param childTableMappings Populate reconciliation map name with corresponding child table name
	* @param childColumnMappings Populate child reconciliation field name with corresponding child column name
	*/
	private void deriveChildTableMappings(HashMap<String,String> reconAttrMap, HashMap<String,String> childTableMappings, HashMap<String,HashMap<String,String>> childColumnMappings)
	{
	// Iterator for Recon Attr Lookup
	Iterator<Entry<String,String>> it = reconAttrMap.entrySet().iterator();
	// Iterate each entry and only getting child info
	while(it.hasNext())
	{
	Map.Entry<String,String> pair = (Map.Entry) it.next();
	// Child mapping detected
	if(pair.getKey().contains("~"))
	{
	String key = pair.getKey(); // Reconcilition Multivauled Name and Field E.g. BadgeUD_ACCESS Child~Name
	String value = pair.getValue(); // Target table and column E.g. MY_BADGE_ENT_RE_FEED~NAME
	String[] reconMapAndField = key.split("~");
	String[] targetTableAndColumn = value.split("~");
	String rfMultiValueName = reconMapAndField[0];
	String targetChildTable = targetTableAndColumn[0];
	String childFieldName = reconMapAndField[1];
	String childColumnName = targetTableAndColumn[1];
	// Multivalued Field Map exist already; adding additional column
	if(childTableMappings.containsKey(rfMultiValueName))
	{
	childColumnMappings.get(rfMultiValueName).put(childFieldName, childColumnName);
	}
	// First time inspecting child table
	else
	{
	childTableMappings.put(rfMultiValueName, targetChildTable); // Put Multivalued Field and target table name
	HashMap<String,String> fieldToColumnMap = new HashMap<String,String>();
	fieldToColumnMap.put(childFieldName, childColumnName);
	childColumnMappings.put(rfMultiValueName, fieldToColumnMap); // Put Multivalued Map Name and field to column correspondence
	}
	// Remove child info from Recon Attr Map
	it.remove();
	}
	}
	}
	/**
	* Get database connection from a data source
	* using the JNDI Name
	* @param jndiName JNDI Name
	* @return Database Connection Object
	* @throws NamingException
	* @throws SQLException
	**/
	private Connection getDatabaseConnection(String jndiName) throws NamingException, SQLException
	{
	Context initContext = new InitialContext();
	DataSource ds = (DataSource)initContext.lookup(jndiName);
	Connection connection = ds.getConnection();
	return connection;
	}
	/**
	* Construct a list of reconciliation events staging to be created
	* @param conn Database connection
	* @param tableName Source table name
	* @param filter WHERE clause to be appended to SQL query
	* @param eventFinished Determine if child data needs to be added
	* @param actionDate For deferring events
	* @param reconAttrMap Reconciliation Attribute Mappings
	* @param itResName IT Resource Name; Used for target resources; Empty for trusted
	* @param childTableMappings Map of Reconciliation Field Map Name to Target Table Name
	* @param childColumnMappings Map of Reconciliation Field Map Name and corresponding field name to target column name
	* @param linkColumnName Column to link parent to child table
	* @return List of events to be created
	* @throws SQLException
	*/
	private List<InputData> constructReconciliationEventList(Connection conn, String tableName, String filter, Boolean eventFinished, Date actionDate, HashMap<String,String> reconAttrMap, String itResName, HashMap<String,String> childTableMappings, HashMap<String,HashMap<String,String>> childColumnMappings, String linkColumnName) throws SQLException
	{
	List<InputData> allReconEvents = new ArrayList<InputData>();
	String linkColumnValue = null;
	// SELECT SQL Query on source table
	String usersQuery = "SELECT * FROM " + tableName + (filter == null || "".equals(filter) ? "" : " " + filter);
	PreparedStatement ps = conn.prepareStatement(usersQuery);
	ResultSet rs = ps.executeQuery();
	// Get the result set metadata
	ResultSetMetaData rsmd = rs.getMetaData();
	int columnCount = rsmd.getColumnCount();
	LOGGER.log(ODLLevel.NOTIFICATION, "Column count: {0}", new Object[]{columnCount});
	// Correlate target column with recon field name
	boolean useTranslateMap = !reconAttrMap.isEmpty(); // Use lookup to get mappings if not empty; otherwise assume target column names are identical to the recon field names
	// Iterate each record
	while(rs.next())
	{
	// Store recon event data
	HashMap<String, Serializable> reconEventData = new HashMap<String, Serializable>();
	// Use Lookup to translate mappings
	if(useTranslateMap)
	{
	// Iterate Attr Mappings Lookup
	for(Map.Entry<String,String> entry : reconAttrMap.entrySet())
	{
	String reconFieldName = entry.getKey(); // Code Key
	String targetColumnName = entry.getValue(); // Decode
	// IT Resource Name Field; Only for target
	if("__SERVER__".equals(targetColumnName))
	{
	reconEventData.put(reconFieldName, itResName);
	}
	// All other attributes
	else
	{
	String value = rs.getString(targetColumnName); // Get column value
	reconEventData.put(reconFieldName, value);
	// Get key value for relating parent and child tables
	if(linkColumnName != null && linkColumnName.equals(targetColumnName))
	{
	linkColumnValue = value;
	LOGGER.log(ODLLevel.INFO, "Key attribute {0} : {1}", new Object[]{linkColumnName, linkColumnValue});
	}
	}
	}
	}
	// Target columns are identical to reconciliation field names
	// No Child data supported for this optional
	// TODO: Remove this
	else
	{
	// Iterate each column and populate map accordingly
	for(int i = 1; i <= columnCount; i++)
	{
	String reconFieldName = rsmd.getColumnName(i); // Get column name
	String value = rs.getString(reconFieldName); // Get column value
	reconEventData.put(reconFieldName, value);
	}
	}
	Map<String,List<Map<String,Serializable>>> userChildEventData = fetchUserEntitlements(conn, childTableMappings, childColumnMappings, linkColumnName, linkColumnValue);
	LOGGER.log(ODLLevel.NOTIFICATION, "Recon Event Data: {0}", new Object[]{reconEventData});
	LOGGER.log(ODLLevel.NOTIFICATION, "Child Recon Event Data: {0}", new Object[]{userChildEventData});
	InputData event = new InputData(reconEventData, userChildEventData, eventFinished, ChangeType.CHANGELOG, actionDate);
	// Add recon event to list
	allReconEvents.add(event);
	}
	return allReconEvents;
	}
	/**
	* Fetch all entitlements for a single user
	* @param conn Database connection
	* @param childTableMappings Contains all child table to inspect
	* @param childColumnMappings Contains all child columns to inspect
	* @param linkColumnName Name of key attribute
	* @param linkColumnValue Key value to get user's child records
	* @return Object containing user's entitlements and use to feed to OIM API
	* @throws SQLException
	*/
	private Map<String,List<Map<String,Serializable>>> fetchUserEntitlements(Connection conn, HashMap<String,String> childTableMappings, HashMap<String,HashMap<String,String>> childColumnMappings, String linkColumnName, String linkColumnValue) throws SQLException
	{
	Map<String,List<Map<String,Serializable>>> childReconData = new HashMap<String,List<Map<String,Serializable>>>(); // {Key = Child Recon Field Map Name, Value = {Key = Child Recon Field Name, Value = data}}
	// Only inspect child data if necessary information are provided
	if(linkColumnName != null && !"".equals(linkColumnName) && linkColumnValue != null && !"".equals(linkColumnValue))
	{
	// Iterate each child table
	for(Map.Entry<String,String> entry : childTableMappings.entrySet())
	{
	String rfMapName = entry.getKey();
	String childTableName = entry.getValue();
	// Fetch user entitlement records from child table
	String userEntQuery = "SELECT * FROM " + childTableName + " WHERE " + linkColumnName + " = ?";
	PreparedStatement ps = conn.prepareStatement(userEntQuery);
	ps.setString(1, linkColumnValue);
	ResultSet rs = ps.executeQuery();
	// List containing all user's entitlements on one child table
	List<Map<String,Serializable>> childEntries = new ArrayList<Map<String,Serializable>>();
	// Iterate result set (Number of entitlements a user has)
	while(rs.next())
	{
	HashMap<String,String> columnMap = childColumnMappings.get(rfMapName);
	Map<String,Serializable> childRecordData = new HashMap<String, Serializable>();
	// Get child record data
	for(Map.Entry<String,String> cEntry : columnMap.entrySet())
	{
	String rfName = cEntry.getKey(); // recon field name
	String columnName = cEntry.getValue(); // column name
	String columnValue = rs.getString(columnName); // get column value
	childRecordData.put(rfName, columnValue); // Populate child recon field with corresponding target column
	}
	childEntries.add(childRecordData); // add child record to list
	}
	childReconData.put(rfMapName, childEntries); // add child table name and user's entitlements
	}
	}
	return childReconData;
	}
	/**
	* Converts a lookup definition into a Map. The Code Key column is used as
	* the key and the Decode column is used as the value.
	* @param lookupDefinitionName Name of the lookup definition
	* @return Map of lookup values {Key = Code Key, Value = Decode}.
	* @throws tcAPIException
	* @throws tcInvalidLookupException
	* @throws tcColumnNotFoundException
	*/
	private HashMap<String, String> convertLookupToMap(tcLookupOperationsIntf lookupOps, String lookupDefinitionName) throws tcAPIException, tcInvalidLookupException, tcColumnNotFoundException
	{
	HashMap<String, String> lookupValues = new HashMap<String, String>();
	if(lookupDefinitionName != null && !lookupDefinitionName.equalsIgnoreCase(""))
	{
	tcResultSet lookupValuesRs = lookupOps.getLookupValues(lookupDefinitionName); // Get lookup values
	int numRows = lookupValuesRs.getTotalRowCount();
	// Iterate lookup resultset and construct map
	for (int i = 0; i < numRows; i++)
	{
	lookupValuesRs.goToRow(i);
	String codeKey = lookupValuesRs.getStringValue("Lookup Definition.Lookup Code Information.Code Key"); // Fetch Code Key
	String decode = lookupValuesRs.getStringValue("Lookup Definition.Lookup Code Information.Decode"); // Fetch Decode
	lookupValues.put(codeKey, decode);
	}
	}
	return lookupValues;
	}
	}

view raw ReconEventsGeneratorDatabaseSource.java hosted with ❤ by GitHub

	<?xml version="1.0" encoding="UTF-8"?>
	<scheduledTasks xmlns="http://xmlns.oracle.com/oim/scheduler">
	<task>
	<name>Recon Event Generator Database Source</name>
	<class>com.blogspot.oraclestack.scheduledtasks.ReconEventsGeneratorDatabaseSource</class>
	<description>Creates reconciliation events using data from a database</description>
	<retry>5</retry>
	<parameters>
	<string-param required="true" encrypted="false" helpText="JNDI of WebLogic Datasource">Data Source</string-param>
	<string-param required="true" encrypted="false" helpText="Name of Reconciliation Profile">Resource Object Name</string-param>
	<string-param required="true" encrypted="false" helpText="Target Table Name">Table Name</string-param>
	<string-param required="true" encrypted="false" helpText="Date Format">Date Format</string-param>
	<string-param required="false" encrypted="false" helpText="Filter">Filter</string-param>
	<boolean-param required="true" encrypted="false" helpText="True to only create recon event when a delta is detected">Ignore Duplicate Event</boolean-param>
	<string-param required="false" encrypted="false" helpText="Correlates target field to recon field">Mapping Lookup</string-param>
	<string-param required="false" encrypted="false" helpText="Required for target; Empty for trusted">IT Resource Name</string-param>
	<string-param required="false" encrypted="false" helpText="Column used to relate parent and child table">Link Column Name</string-param>
	</parameters>
	</task>
	</scheduledTasks>

view raw ReGenDBSrcMetadata.xml hosted with ❤ by GitHub

Usage
Given below demonstrates using this plug-in to create reconciliation events for a disconnected resource. Linking of accounts to users are handled by the reconciliation engine.

1. Create the database tables. Given below are SQL scripts for creating tables with sample records.

	/*Parent Table*/
	CREATE TABLE MY_BADGE_RE_FEED
	(
	LOGIN VARCHAR2(256 CHAR),
	EMPLID VARCHAR2(256 CHAR)
	);
	INSERT INTO MY_BADGE_RE_FEED (LOGIN, EMPLID) VALUES ('JPROUDMOORE','100000');
	INSERT INTO MY_BADGE_RE_FEED (LOGIN, EMPLID) VALUES ('AWINDRUNNER','100001');
	INSERT INTO MY_BADGE_RE_FEED (LOGIN, EMPLID) VALUES ('MSTORMRAGE','100002');
	/* Child Table One */
	CREATE TABLE MY_BADGE_ENT_RE_FEED
	(
	EMPLID VARCHAR2(256 CHAR),
	NAME VARCHAR2(100 CHAR),
	DESCRIPTION VARCHAR2(64 CHAR)
	);
	INSERT INTO MY_BADGE_ENT_RE_FEED (EMPLID, NAME, DESCRIPTION) VALUES ('100000','Lab', 'Access to laboratory');
	INSERT INTO MY_BADGE_ENT_RE_FEED (EMPLID, NAME, DESCRIPTION) VALUES ('100000','Secret Room', 'Mysterious Room');
	INSERT INTO MY_BADGE_ENT_RE_FEED (EMPLID, NAME, DESCRIPTION) VALUES ('100000','Core', 'Main workstation');
	INSERT INTO MY_BADGE_ENT_RE_FEED (EMPLID, NAME, DESCRIPTION) VALUES ('100001','Core', 'Main workstation');
	/* Child Table Two*/
	CREATE TABLE MY_BADGE_ENT_ROLE_RE_FEED
	(
	EMPLID VARCHAR2(256 CHAR),
	NAME VARCHAR2(100 CHAR)
	);
	INSERT INTO MY_BADGE_ENT_ROLE_RE_FEED (EMPLID, NAME) VALUES ('100000','ENG');
	INSERT INTO MY_BADGE_ENT_ROLE_RE_FEED (EMPLID, NAME) VALUES ('100000','ADMIN');

view raw BadgeReDbFeed.sql hosted with ❤ by GitHub

2. Define reconciliation action rules on the resource object in order to dictate when linking should occur.

Resource Object: Reconciliation Action Rules

3. Create reconciliation rule for linking the target account to OIM user using key attributes.

Reconciliation Rule:
Link by "User Login" attribute to "Account Login" reconciliation field.

4. Ensure process rule matching are set appropriately.

Reconciliation Mappings: Key flag made up the process rule matching

5. Create a lookup to define mappings between table columns and reconciliation fields. For Code Key, use the name of the reconciliation field. For Decode, use the actual target column name. Entries with "~" indicate child table (Code Key = <Reconciliation Field Map>~<Reconciliation Field Name>, Decode = <Child Table Name>~<Column Name>). "__SERVER__" is used to indicate IT Resource field and is required for target resource.  

Code Key = Reconciliation Field Name
Decode = Target Column Name

6. Adjust the parameters on the scheduled job accordingly.

Parameters for Scheduled Job

Data Source = JNDI Name of WebLogic data source

Date Format = Date format for reconciliation event

Filter = WHERE clause for filtering E.g. where emplid='100001'
Ignore Duplicate Event = If true, a reconciliation event is created only when there is a change between the incoming data and the data on the process form. If false, reconciliation event is created regardless of any deltas.

IT Resource Name = Required for target resource. Not needed for trusted.

Link Column Name = Name of column to relate parent table to child tables.

Mapping Lookup = Mapping between columns and reconciliation fields.

Resource Object Name = Name of resource object / reconciliation profile

Table Name = Parent table name

7. Run scheduled job and ensure reconciliation events are created and linked accordingly.