How to Setup Database Table Resource in SIM

Version: Sun Identity Manager 8.1
Description: This post will show you how to setup a database table resource in Sun Identity Manager. An Oracle database table is used as an example.
Reference: https://docs.oracle.com/cd/E19225-01/820-6551/gijbf/index.html


Prerequisites
JDBC Driver
1. Download the appropriate JDBC driver and place the jar file in "$WSHOME/WEB-INF/lib" directory.
E.g. Oracle Database JDBC Driver

2. Shutdown application server. Repackage $WSHOME in to a WAR file and redeploy on application server.

3. Start application server.

Target Table Setup
1. Below is a sample SQL script to setup the target tables for Oracle database:

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

---------------------
-- 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;

Configure Database Table Resource
1. Log in to the Sun Identity Manager console as configurator.

2. Navigate to "Resources". On the "Resource List" section, select "Configure Managed Resource" under "--- Resource Type Actions ---" drop down menu.

3. On the "Configured Managed Resource" page, check mark "Database Table" resource adapters. Then scroll down to the bottom of the page and save.

4. On the "Resources => Resource List" section, select "New Resource" under "--- Resource Type Actions ---" drop down menu.

5. Select "Database Table" as the resource type. Then click New.

6. Specify database environment information. Then click Next.

Database Type: Oracle
JDBC Driver: oracle.jdbc.driver.OracleDriver
JDBC URL Template: jdbc:oracle:thin:@%h:%p/%d
Host: localhost
TCP Port: 1521
Database: orcl
User: DEV_OIM

 7. Select target table.

8. Select columns to be managed by SIM.

9. Define account attribute mappings (Core identity account attributes to target resource attribute mappings).

10. Specify Identity template.

11. Specify Identity System Parameters.

Enable Logging
1. Navigate to the "localhost/idm/debug" page.

2. Select "Show Trace".

3. Check mark "Trace Enabled" and add "com.waveset.adapter.DatabaseTableResourceAdapter" class.

Validate Provisioning
1. Navigate to "Accounts" section and then select a user.

2. On the "Edit User" page, select the "Resources" tab.

3. Move "Database Table" to the "Current Resources" side. Then save.

4. Verify resource account and then save.

5. Verify the user's account on the target system.