OIM Database Tables
JANUARY 11, 2017 RAVI LEAVE A COMMENT
Tables where different OIM components are stored.
SVR — IT Resource information. (svr_key is primary key)
SPD — IT Resource parameter DeCnition information
SVD – IT Resource Type DeCnition.
APP_INSTANCE — Application Instance Information (app_instance_key is the primary key). It
will have itresource_key to link with svr_key in SVR table and object_key to link with obj_key in
OBJ table.
OBJ – Resource Object information (obj_key)
OBI — Object Instance information (obi_key)
CATALOG — Request Catalog Information (applications instances, roles, entitlements)
(Primary key is Catalog_id)
ORC — It stores any users account instance information (orc_key)
OST — It stores the object status information (ost_key)
Note : Never join (link) OST and OBJ tables directly.
1. Join OST and OIU tables with ost_key
2. Then Join OBI and OIU tables with obi_key
3. Then Join OBI and OBJ using obj_key
ENT_LIST — Stores the entitlements information (ent_list_key)
LKV — Stores Lookup Values information (lkv_key)
LKU – Stores Lookup DeCnition information (lku_key)
ENT_ASSIGN — Stores the information of entitlements assigned to users. (ent_assign_key)
Note : This table contains a column providing information of provisioning mechanism i.e. How
the entitlement was added. (via Reconcillation, Direct Provisioning, Access Policy, Manual
Provisioning etc).
1. This table will be joined with different tables, using ent_list_key.
2. Join can be performed with ENT_LIST table.
3. Also, with CATALOG table using entity_key.
ENT_ASSIGN_HIST – Stores the information of history of entitlements assigned to user.
OIU — It is the major table that will be linked to other tables like OST, ORC, OBI, USR,
APP_INSTANCE etc. It stores user account information
1. Primary key is oiu_key
2. It has ost_key to join with OST table
3. It has orc_key to join with ORC table
4. It has obi_key to join with OBJ table
5. It has app_instance_key to join with APP_INSTANCE table
\. It has usr_key to join with USR table
UGP — Roles information
AUD_JMS – Audit data
UPA – Audit purpose
POL – Policies information
MIL – Table stores task information
OSI – Key table that can be joined with
1. orc_key of ORC table
2. sch_key of SCH table
3. mil_key of MIL table
SCH – Status and other information of the task triggered
SDK – Form information.
Queries-
Get all accounts along with status present under user’s proCle:
SELECT usr.usr_login, obj.obj_name, app_instance.app_instance_display_name, orc.orc_tos_instance_key, ost.ost_status
FROM usr, oiu, ost,orc, obi, obj, app_instance
WHERE oiu.usr_key=usr.usr_key AND ost.ost_key=oiu.ost_key AND oiu.obi_key=obi.obi_key AND obi.obj_key=obj.obj_key AND app_in
Get list of entitlements user is having associated with Enabled/Provisioned accounts:
SELECT *
FROM ent_assign, usr, catalog, oiu, ost, orc
WHERE ent_assign.ent_list_key=catalog.entity_key AND Ent_assign.usr_key=usr.usr_key AND ent_assign.oiu_key=oiu.oiu_key AND o
Rejected Tasks:
SELECT *
FROM osi, sch, mil , orc,usr, oiu
WHERE orc.orc_key=osi.orc_key AND sch.sch_key=osi.sch_key AND oiu.orc_key=orc.orc_key AND oiu.usr_key=usr.usr_key AND osi.mi
