Installing and Configuring Database Application Tables Connector

Description: This post will show you how to install and configure the Database Application Tables Connector. Oracle Database is used as an example for the target system.
Version: Database Application Tables 11.1.1.5.0
References: Oracle Identity Manager Identity Connectors Documentation Release 11.1.1
Oracle® Identity Manager Connector Guide for Database Application Tables Release 11.1.1


1. Download the Database Application Tables and the Oracle Database driver.
Download Link: Oracle Identity Manager Connector Downloads
File Name: dbat-11.1.1.5.0.zip

Download Link: Oracle JDBC Drivers
File Name: ojdbc6.jar

2. Create the target database tables. Given below is a sample SQL script to create the target system.

-------------
-- Sequences
-------------
/*CREATE SEQUENCE DBAT_USER_ID_SEQ  MINVALUE 1 MAXVALUE 999999999999999999999999999 INCREMENT BY 1 START WITH 801 CACHE 20 NOORDER  NOCYCLE;
CREATE SEQUENCE DBAT_ROLE_ID_SEQ  MINVALUE 1 MAXVALUE 999999999999999999999999999 INCREMENT BY 1 START WITH 801 CACHE 20 NOORDER  NOCYCLE;
CREATE SEQUENCE DBAT_GROUP_ID_SEQ  MINVALUE 1 MAXVALUE 999999999999999999999999999 INCREMENT BY 1 START WITH 801 CACHE 20 NOORDER  NOCYCLE;*/

-----------------
--Table Creation
-----------------
CREATE TABLE DBAT_USER 
(
    USERNAME VARCHAR(100) PRIMARY KEY,
    PASSWORD VARCHAR(100),
    STATUS VARCHAR(100),
    LAST_UPDATE TIMESTAMP (8) DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE DBAT_ROLE
(
    USERNAME VARCHAR(100),
    ROLE VARCHAR(100),
    CONSTRAINT PK_DBAT_ROLE PRIMARY KEY (USERNAME, ROLE) 
);

CREATE TABLE DBAT_GROUP
(
    USERNAME VARCHAR(100),
    GROUP_NAME VARCHAR(100),
    CONSTRAINT PK_DBAT_GROUP PRIMARY KEY (USERNAME, GROUP_NAME)
);

-----------
-- Index
-----------
CREATE INDEX DBAT_ROLE_ID ON DBAT_ROLE (USERNAME);
CREATE INDEX DBAT_GROUP_ID ON DBAT_GROUP (USERNAME);

----------------
-- Foreign Key
----------------
ALTER TABLE DBAT_ROLE ADD CONSTRAINT FK_DBAT_ROLE FOREIGN KEY (USERNAME) REFERENCES DBAT_USER (USERNAME) ENABLE;
ALTER TABLE DBAT_GROUP ADD CONSTRAINT FK_DBAT_GROUP FOREIGN KEY (USERNAME) REFERENCES DBAT_USER (USERNAME) ENABLE;

--------------------
-- Sequence Trigger
--------------------
/*CREATE OR REPLACE TRIGGER DBAT_ROLE_SEQ_TRG BEFORE
INSERT ON DBAT_ROLE  FOR EACH row BEGIN
SELECT DBAT_ROLE_ID_SEQ.nextval INTO :new.ROLE_KEY FROM dual;
END;
/
ALTER TRIGGER DBAT_ROLE_SEQ_TRG ENABLE;

CREATE OR REPLACE TRIGGER DBAT_GROUP_SEQ_TRG BEFORE
INSERT ON DBAT_GROUP  FOR EACH row BEGIN
SELECT DBAT_GROUP_ID_SEQ.nextval INTO :new.GROUP_KEY FROM dual;
END;
/
ALTER TRIGGER DBAT_GROUP_SEQ_TRG ENABLE;*/

---------------------
-- Timestamp Trigger
---------------------
CREATE OR REPLACE TRIGGER DBAT_USER_TS_TRG BEFORE
INSERT OR
UPDATE ON DBAT_USER REFERENCING NEW AS NEW FOR EACH ROW BEGIN
SELECT CURRENT_TIMESTAMP INTO :NEW.LAST_UPDATE FROM dual;
END;
/
ALTER TRIGGER DBAT_USER_TS_TRG ENABLE;

CREATE OR REPLACE TRIGGER DBAT_ROLE_PT_TS_TRG AFTER
INSERT OR
UPDATE ON DBAT_ROLE FOR EACH ROW DECLARE BEGIN
UPDATE DBAT_USER
SET DBAT_USER.LAST_UPDATE = CURRENT_TIMESTAMP
WHERE DBAT_USER.USERNAME = :new.USERNAME;

END;
/
ALTER TRIGGER DBAT_ROLE_PT_TS_TRG ENABLE;

/*CREATE OR REPLACE TRIGGER DBAT_ROLE_TS_TRG BEFORE
INSERT OR
UPDATE ON DBAT_ROLE REFERENCING NEW AS NEW FOR EACH ROW BEGIN
SELECT CURRENT_TIMESTAMP INTO :NEW.LAST_UPDATE FROM dual;
END;
/
ALTER TRIGGER DBAT_ROLE_TS_TRG ENABLE;*/

CREATE OR REPLACE TRIGGER DBAT_ROLE_DELETE_ROW 
BEFORE DELETE ON   DBAT_ROLE
For Each Row Declare Begin
  UPDATE DBAT_USER
  Set DBAT_USER.LAST_UPDATE = Current_Timestamp
  WHERE DBAT_USER.USERNAME    = :old.USERNAME;
END;
/
ALTER TRIGGER DBAT_ROLE_DELETE_ROW ENABLE;

CREATE OR REPLACE TRIGGER DBAT_GROUP_PT_TS_TRG AFTER
INSERT OR
UPDATE ON DBAT_GROUP FOR EACH ROW DECLARE BEGIN
UPDATE DBAT_USER
SET DBAT_USER.LAST_UPDATE = CURRENT_TIMESTAMP
WHERE DBAT_USER.USERNAME    = :new.USERNAME;
END;
/
ALTER TRIGGER DBAT_GROUP_PT_TS_TRG ENABLE;

/*CREATE OR REPLACE TRIGGER DBAT_GROUP_TS_TRG BEFORE
INSERT OR
UPDATE ON DBAT_GROUP REFERENCING NEW AS NEW FOR EACH ROW BEGIN
SELECT CURRENT_TIMESTAMP INTO :NEW.LAST_UPDATE FROM dual;
END;
/
ALTER TRIGGER DBAT_GROUP_TS_TRG ENABLE;*/

CREATE OR REPLACE TRIGGER DBAT_GROUP_DELETE_ROW 
BEFORE DELETE ON   DBAT_GROUP
For Each Row Declare Begin
  UPDATE DBAT_USER
  Set DBAT_USER.LAST_UPDATE = Current_Timestamp
  WHERE DBAT_USER.USERNAME    = :old.USERNAME;
END;
/
ALTER TRIGGER DBAT_GROUP_DELETE_ROW ENABLE;

3. Extract the zip file. Then extract "dbat-11.1.1.5.0/generator/dbat-generator-11.1.1.5.0.zip". Modify the "dbat-generator-11.1.1.5.0/resources/DBATConfiguration.groovy". Refer to section "2.2.2.1 Entries in the Predefined Sections" in the DBAT connector guide for a description of each parameter. Given below is an example configuration for the sample target resource given in step 2.

// Run like:  DBATGenerator.cmd ..\resources\DBATConfiguration.groovy db1
target {

    //Resource Name
    itResourceDefName='DBAT' // DBAT is a name of the package
    itResourceName="$itResourceDefName" //the same as itResourceDefName

    //Output files
    connectorDir="../$itResourceDefName" //out dir is the same as it resource name
    xmlFile='DBAT-ConnectorConfig.xml'

    //  configFile='DBAT-CI.xml'
    //  propertiesFile='dbat-generator.properties'
    //  version='11.1.1.5.0'

    //  provisionDatasetFile='ProvisionResource_DBATUser.xml'
    //  modifyResourceDatasetFile='ModifyProvisionedResource_DBATUser.xml'
    //  requestDMDatasetsFile='Datasets.xml'

    //Configuration
    bundleJar='../lib/org.identityconnectors.databasetable-1.2.2.jar'
    config = [
        'table' : 'DBAT_USER',
        'keyColumn' : 'USERNAME',
        'passwordColumn' : 'PASSWORD',
        'user' : 'DEV_OIM',
   //   'password' : '', never provide passwords to generator
        'jdbcUrlTemplate' : 'jdbc:oracle:thin:@localhost:1521:orcl',
        'jdbcDriver': 'oracle.jdbc.driver.OracleDriver',
        'relationTables': 'DBAT_ROLE, DBAT_GROUP',
        'statusColumn':'STATUS',
        'enableValue':'Enabled',
        'disableValue':'Disabled',
   //   'database':'',
   //   'host':'',
   //   'port':'',
   //   'datasource':'',
   //   'jndiProperties':'',
   //   'customizedQuery':'',
   //   'rethrowAllSQLExceptions', true,
        'allNative':true,  // We prefer to have native dates, timestamps in OIM
   //   'validConnectionQuery':'SELECT 1 FROM DUMMY',
   //   'dateFormat':'dd/MM/yyyy',
        'timestampFormat':'dd/MM/yyyy HH:mm:ss:SSS', // Force the full timestamp formating
   //   'enableEmptyString':false,
   //   'quoting':'NONE',
        'changeLogColumn':'LAST_UPDATE',
        'nativeTimestamps': true,
   //   'createScript': '',   // provide the groovy script, or file:///url of the script
   //   'updateScript': '',  // provide the groovy script, or file:///url of the script
   //   'deleteScript': '',  // provide the groovy script, or file:///url of the script
   // 'executeQueryScript': '',  // provide the groovy script, or file:///url of the script
    ]


    //Database table name to OIM resource form name alliasing.
    //Make sure that itResourceName lenght plus child table alias lenght is within 8 characters limit. 
    alias = ['DBAT_ROLE':'RO', 'DBAT_GROUP':'GRP']


    //Attribute to form field names alliasing
    alias += ['__NAME__':'User ID']

    //Generate prepopulate adapters.PrePopluate Adapters Usage is ['CONNECTOR_ATTRIBUTE':'OIM USER ATTRIBUTE']. Use connector attribute name based on prepopulate name selection in design console (On the process form when mapping a prepopulate adapter to a form field, you will be able to see a list of attribute names to use). Eg., ['DISPLAY_NAME':'Display Name']
    prepopulate = ['__NAME__':'User Login', 'FIRST_NAME':'First Name', 'LAST_NAME':'Last Name', '__PASSWORD__':'Password']

}

4. Place the database driver in "dbat-generator-11.1.1.5.0/lib" directory.

# For Oracle Database Target System
cp /home/oracle/bits/connectors/ojdbc6.jar /home/oracle/bits/connectors/dbat-11.1.1.5.0/generator/dbat-generator-11.1.1.5.0/lib

5. Run the DBAT Generator script located in "dbat-generator-11.1.1.5.0/bin" to generate the connector package. The script makes a connection to the target database table and builds the connector metadata based on the table structure. You will be prompt to enter the password for the database user account. Execute the following command:

# Replace CONFIG_FILE with the path to the DBATConfiguration.groovy file
# Replace CONFIG_NAME with the name of the configuration being used for the target system. The predefined configurations within the DBATConfiguration.groovy file are trusted and target.
sh DBATGenerator.sh CONFIG_FILE CONFIG_NAME

# Example command
sh DBATGenerator.sh ../resources/DBATConfiguration.groovy target

6. After successfully executing the DBAT Generator script, the connector bundle package should be produced. The output location is determined by "connectorDir" parameter defined in the "DBATConfiguration.groovy" file. Extract the connector bundle package and place into"$MW_HOME/Oracle_IDM1/server/ConnectorDefaultDirectory/" directory.


7. Skip this step if you are using Oracle Database as the target system. Create a directory in "$MW_HOME/Oracle_IDM1/server/ConnectorDefaultDirectory/targetsystems-lib/" with same name as the installer package. For example, "$MW_HOME/Oracle_IDM1/server/ConnectorDefaultDirectory/targetsystems-lib/dbat-11.1.1.5.0". Then copy the JDBC driver to this directory.

8. Install the connector in OIM.

Log in to Identity System Administration

On left pane, click Manage Connector under System Management.

Click Install

Select DBAT Connector and load.

Successful install

Fill out database user account password in the DBAT IT Resource.

Create sandbox.

On top right menu, click Sandboxes.

Create Sandbox

Specify Sandbox information

Create a new DBAT UI form.

On left pane, click Form Designer under Configuration

Click Create

Specify form information and then create

Create an application instance.

On left pane, select Application Instances under Configuration

Click Create

Specify application instance information and then create

Publish sandbox.

Publish sandbox

Run "Catalog Synchronization Job" scheduled job.

On left pane, select Scheduler under System Management

Run Catalog Synchronization Job

9. Validate connector by provisioning and reconciling.