Steps Developing Schedule Task with OIM 11g R2

OIM Common Tables and queries

OIM 11gR2 PS2/PS3 tables and queries, OIM useful Queries, OIM sql queries, OIM Database queries, OIM tables, OIM queries

svr— IT Resource information. (svr_key is primary key)

spd— IT resource parameter Definition information

svd – IT Resource Type Definition.

app_instance — Application Instance Information (app_instance_key is the primary key)

It will have itresoucre_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) (Catalog_id)

orc — It stores any users account instance information (orc_key)

ost — It stores the object status information (ost_key)

Note : never link ost and obj table directly, we have to link ost and oiu table with ost_key and then obi and oiu with obi_key and then link obi, obj using obj_key

ent_list — Stores the entitlements information (ent_list_key)

lkv,lku — Lookup tables lkv stores Lookup Values information, lku stores Lookup Definition information (lkv_key, lku_key)

ent_assign — Stores the information of entitlements assigned to users. (ent_assign_key)

Note : This table will be having a column to say the provisioning mechanism as well, how the entitlement was added (via Recon, Direct Prov, access policy, Manual Provisioning etc..), This table will be linked with different tables, using ent_list_key we can link with ent_list table as well as entity_key of catalog table , ud_child_key we can link with child table of any recourse.

ud_disc, ud_disc_ent – This may vary but my intention is to tell how parent table, child table are linked with other tables, using orc_key in this tables we can link with orc_key in orc table

Note : These are forms associated with different resources and are stored in UD tables and will vary based on the Target Resource, you can get this tables names from form designer.

ent_assign_hist – Stores the information of history of entitlements assigned to user.

oiu — It is the key table which will be linked to all other tables like ost, orc, obi, usr, app_instance etc. to get the users account information (oiu_key, It will have ost_key to link with ost table, orc_key to link with orc table, obi_key to link with obi table, app_instance_key to link with app_instance table, usr_key to link with usr table )

ugp — Stores the roles information

aud_jms, upa tables— Used for audit purpose

pol— Stores access Policies information

poc --Stores the child table/Entitlements attached to a access policy

pof -- Stores Resource tied in policy (pol_field_value will have IT resource key but as char so when we link with svr table, should be like to_char(svr.svr_key))

mil – Table stores task information, process task that triggered

osi – key table that can link with orc_key of orc table, sch_key of sch table, mil_key of mil table

sch – status and other information of the task triggered

sdk – This table stores the Form information.

sdp – Stores the properties of the form.

arm_aud – Admin role membership audit table.

Some queries using this tables:

To get all the accounts along with status present under a user’s profiles:

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_instance.app_instance_key=oiu.app_instance_key and orc.orc_key=oiu.orc_key and usr.usr_login=<PROVIDE THE USR_LOGIN OF USER YOU ARE LOOKING>;

To get the 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 ost.ost_key=oiu.ost_key and oiu.orc_key=orc.orc_key and usr.usr_login=<PROVIDE THE USR_LOGIN OF USER YOU ARE LOOKING>;

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.mil_key=mil.mil_key and sch.sch_status='R'

To get List of roles and associated applications (through access policies):

select ugp.ugp_name, app_instance.app_instance_display_name from pol,ugp, pog , pof,obj,svr,app_instance

where pol.pol_key=pog.pol_key and

pog.ugp_key=ugp.ugp_key and pof.pol_key=pol.pol_key and obj.obj_key=pof.obj_key

and app_instance.itresource_key=svr.svr_key and

to_char(svr.svr_key)=pof.POF_FIELD_VALUE ;

Above queries can be modified accordingly  by adding different conditions on obj, app_instance, catalog etc.. to get user-application reports etc..

Applications and published Organizations:

select app_instance.app_instance_name,act.act_name from APP_INST_PUBLICATION_VW,act,app_instance where act.act_name=APP_INST_PUBLICATION_VW.act_name and app_instance.app_instance_key=APP_INST_PUBLICATION_VW.entity_id and act.act_name in ('Top');

You can add conditions to above query based on requirement.

Roles and published Organizations:

select  ugp_name ,ROLE_PUBLICATION_VW.act_name from ROLE_PUBLICATION_VW,ugp,act where

ROLE_PUBLICATION_VW.entity_id=ugp.ugp_key and ROLE_PUBLICATION_VW.act_name=act.act_name and act.act_name in ('Top');

You can add conditions to above query based on requirement.

Organization Membership:

select usr.usr_login,act.act_name from org_user_memberships,act,usr where org_user_memberships.usr_key=usr.usr_key and act.act_key=org_user_memberships.act_key and usr.usr_status='Active' and act.act_name=<Organziation name>;

Admin Role Membership:

select usr.usr_login,admin_role.role_name from admin_role_membership,admin_role,usr where admin_role_membership.user_id=usr.usr_key and admin_role_membership.role_id=admin_role.role_id and usr.usr_status='Active' andadmin_role.role_name= <Admin role Name>;;

Business Role Membership:

select  usr.usr_login,ugp.ugp_name  from usg,usr,ugp where ugp.ugp_key=usg.ugp_key and usr.usr_key=usg.usr_key and usr.usr_status='Active' and ugp.ugp_name= <Busniess role name>;;

User's Roles associated with application (tied through policies ) along with the child data tied in policies :

select  usr.usr_login, ugp.ugp_name,ent_list.ent_display_name,pol.pol_name from poc,ent_list,ent_assign,pol,pog,ugp,usr where poc.poc_field_value=ent_list.ent_code and poc.pol_key=pol.pol_key

and ent_assign.ent_list_key=ent_list.ent_list_key and pog.ugp_key=ugp.ugp_key and pog.pol_key=pol.pol_key

and ent_assign.usr_key=usr.usr_key

and  ugp.ugp_key in (select ugp.ugp_key from pol,ugp, pog , pof,obj,svr,app_instance

where pol.pol_key=pog.pol_key and

pog.ugp_key=ugp.ugp_key and pof.pol_key=pol.pol_key and obj.obj_key=pof.obj_key

and app_instance.itresource_key=svr.svr_key and

to_char(svr.svr_key)=pof.POF_FIELD_VALUE and app_instance_display_name=<Application instance display name>)

and  usr.usr_status='Active';

To get all entitlements attached to policies linked to a role :

select * from pol, ugp,pog, poc where pol.pol_key=pog.pol_key

and poc.pol_key=pol.pol_key and pog.ugp_key=ugp.ugp_key and

ugp.ugp_name like '<Role Name>';

Child and Parent roles :

select ugp1.ugp_name as ParentRole, ugp2.ugp_name as childrole from gpg,ugp ugp1,ugp ugp2 where gpg.ugp_key=ugp1.ugp_key

and gpg.gpg_ugp_key=ugp2.ugp_key;

SQL query to get all the users having account on particular resource in OIM

Query to get users list having active/enabled account:

SELECT USR.USR_LOGIN FROM OBJ,OBI,OIU,OST,USR WHERE OBJ.OBJ_KEY=OBI.OBJ_KEY AND OBI.OBI_KEY=OIU.OBI_KEY AND OIU.USR_KEY=USR.USR_KEY AND OIU.OST_KEY=OST.OST_KEY AND OST.OBJ_KEY=OBJ.OBJ_KEY AND OBJ.OBJ_NAME=' [Insert Resource Name here]' AND OST.OST_STATUS IN ('Enabled','Provisioned'); Example: To get the users having active/enabled account on EBS User resource: SELECT USR.USR_LOGIN FROM OBJ,OBI,OIU,OST,USR WHERE OBJ.OBJ_KEY=OBI.OBJ_KEY AND OBI.OBI_KEY=OIU.OBI_KEY AND OIU.USR_KEY=USR.USR_KEY AND OIU.OST_KEY=OST.OST_KEY AND OST.OBJ_KEY=OBJ.OBJ_KEY AND OBJ.OBJ_NAME='EBS User' AND OST.OST_STATUS IN ('Enabled','Provisioned');

Query to get users list having disabled account:

SELECT USR.USR_LOGIN FROM OBJ,OBI,OIU,OST,USR WHERE OBJ.OBJ_KEY=OBI.OBJ_KEY AND OBI.OBI_KEY=OIU.OBI_KEY AND OIU.USR_KEY=USR.USR_KEY AND OIU.OST_KEY=OST.OST_KEY AND OST.OBJ_KEY=OBJ.OBJ_KEY AND OBJ.OBJ_NAME=' [Insert Resource Name here]' AND OST.OST_STATUS IN ('Disabled'); Example: To get the users having disabled account on EBS  User resource: SELECT USR.USR_LOGIN FROM OBJ,OBI,OIU,OST,USR WHERE OBJ.OBJ_KEY=OBI.OBJ_KEY AND OBI.OBI_KEY=OIU.OBI_KEY AND OIU.USR_KEY=USR.USR_KEY AND OIU.OST_KEY=OST.OST_KEY AND OST.OBJ_KEY=OBJ.OBJ_KEY AND OBJ.OBJ_NAME='AD User' AND OST.OST_STATUS IN ('Disabled');

Query to get users list having revoked account:

SELECT USR.USR_LOGIN FROM OBJ,OBI,OIU,OST,USR WHERE OBJ.OBJ_KEY=OBI.OBJ_KEY AND OBI.OBI_KEY=OIU.OBI_KEY AND OIU.USR_KEY=USR.USR_KEY AND OIU.OST_KEY=OST.OST_KEY AND OST.OBJ_KEY=OBJ.OBJ_KEY AND OBJ.OBJ_NAME=' [Insert Resource Name here]' AND OST.OST_STATUS IN ('Revoked'); Example: To get the users having revoked account on EBS   User resource: SELECT USR.USR_LOGIN FROM OBJ,OBI,OIU,OST,USR WHERE OBJ.OBJ_KEY=OBI.OBJ_KEY AND OBI.OBI_KEY=OIU.OBI_KEY AND OIU.USR_KEY=USR.USR_KEY AND OIU.OST_KEY=OST.OST_KEY AND OST.OBJ_KEY=OBJ.OBJ_KEY AND OBJ.OBJ_NAME='EBS User' AND OST.OST_STATUS IN ('Revoked');