Installing and Configuring DBAT Connector for MySQL in OIM

Version: Oracle Identity Manager 11g R1
Description: This section shows how to install and configure the Oracle "Database Applications Table" (DBAT) connector in OIM. The DBAT connector uses Generic Technology Connector (GTC) framework. MySQL database is used to demonstrate the use of the DBAT connector.

Prerequisites
1. Obtain the DBAT connector here.
Name: Database Applications Table
Version: 9.1.0.5.0
File: DBAT_91050.zip

2. MySQL 5.1.30 or later. You can find the guide here.

3. Download the MySQL driver here.
Connector/J 5.1.22
File: mysql-connector-java-5.1.22.zip
Unzip "mysql-connector-java-5.1.22.zip" and place the "mysql-connector-java-5.1.22-bin.jar" into the "<JAVA_HOME>/jre/lib/ext" directory.

Unzip mysql-connector-java-5.1.22.zip
cp mysql-connector-java-5.1.22/mysql-connector-java-5.1.22-bin.jar /home/oracle/java/jrockit64/jre/lib/ext/

Installing the Connector in OIM
1. Unzip "DBAT_91050.zip" and place content into "<IDM_HOME>/server/ConnectorDefaultDirectory"

unzip DBAT_91050.zip -d /home/oracle/Oracle/Middleware/Oracle_IDM1/server/ConnectorDefaultDirectory

2. Login to the OIM console and navigate to Advanced -> Manage Connector under System Management.

3. Click Install.
Connector List: DatabaseApplicationTables 9.1.0.5.0

Configuring DBAT for MySQL
The database and the table given below will be used for this demonstration. Here are the sql statements.

CREATE DATABASE dbat_test;
USE dbat_test;
CREATE TABLE users (
userid VARCHAR(100) PRIMARY KEY, 
firstname VARCHAR(100), 
lastname VARCHAR(100), 
middlename VARCHAR(100), 
email VARCHAR(200), 
status int);

1. To setup account status for the MySQL resouce for provisioning and reconciliation, create the following lookup definitions in Design Console:

Code: Lookup.DBAT_TEST_status 
Group: Account Status for Provisioning
Code Key            Decode
disable             0
enable              1

The code keys "disable" and "enable" are OIM values for account statuses on provisioning.
The decode value "0" indicates that the account in the target system is disabled.
The decode value "1" indicates that the account in the target system is enabled.
The decode values for account statuses can be defined however you like it to be, just make sure your status column in MySQL is the right type.

Code: Lookup.DBAT_TEST_recon_status
Group: Account Status for Reconciliation

Code Key            Decode
0                   Disabled
1                   Enabled

The decode keys "Disabled" and "Enabled" are OIM values for account statuses on reconciliation.
The code key value "0" indicates that the account in the target system is disabled.
The code key value "1" indicates that the account in the target system is enabled.

2. Create a generic connector for your MySQL tables.
In the OIM console, navigate to Advanced -> Create Generic Connector under Configuration. Given below  are the information I provided for each step process. Adjust according to your environment.

Step 1: Provide Basic Information

Name: dbat_test
Reconciliation: check 
Transport Provider: Database Application Tables Reconciliation 
Format Provider: Database Application Tables Reconciliation
Provisioning: check 
Transport Provider: Database Application Tables Provisioning 
Format Provider: Database Application Tables Provisioning

Step 2: Specify Paramter Values
==For Reconciliation and Provisioning==

Database Driver: com.mysql.jdbc.Driver 
Database URL: jdbc:mysql://localhost/dbat_test 
Database User ID: root 
Database Password: ******* 
Connection Properties: databaseName=dbat_test,port=3306

==Design Parameters==
Database Application Tables Reconciliation

Parent Table/View Name: dbat_test.users
Unique Attribute: userid

Database Application Tables Provisioning

Parent Table/View Name: dbat_test.users
Unique Attribute: userid
Status Attribute: status
Status Lookup Code: Lookup.DBAT_TEST_status
Target Date Format: yyyy-MM-dd hh:mm:ss.fffffffff
Batch Size: All
Stop Reconciliation Threshold: None
Stop Threshold Minimum Records: None
Source Date Format: yyyy/MM/dd HH:mm:ss z
Reconcile Deletion of Multivalued Attribute Data: check
Reconciliation Type: Full

Step 3: Modify Connector Configutration
==Configuring Account Status Reconciliation ==
1. On the Reconciliation Staging section, edit the status field.

Mapping Action: Create Mapping With Translation
Data Type: String
Input: Dataset = Source, Field Name = status
Lookup Code Name: Literal = Lookup.DBAT_TEST_recon_status

2. Remove the status field from "OIM-Account".
3. Create a mapping between status field in "Reconciliation" and "OIM Object Status" field in "OIM-Account".

==Configuring Account Status Provisioning==
1. Remove status field from "Provisioining Staging".

==Rule Matching==
1 . For the userid field in "OIM-Account", check "Matching Only" and "Case-Insensitive"
2. Create a mapping without transformation between  userid field in "Reconcilation Staging" and User Login in OIM.

Step 4: Verify Connector Form Name
OIM-Account: DBATTEST

The screenshots given below demonstrates the steps given above.

You should now be able to provision mysql accounts to OIM users, disable or enable a user's mysql account, and link mysql accounts to OIM users through reconcilation.