Below are some tables where different OIM components are stored.
svr— IT Resource information. (svr_key is primary key)
spd— IT resource parameter Definition information
svd – IT Resource Type Definition.
svp - IT Resource parameter values
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');
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;
Different process tasks and mapped adapters in any process definition of a resource:
select mil.mil_name,evt.evt_name,obj.obj_name from obj join pkg on pkg.obj_key=obj.obj_key join tos on tos.pkg_key=pkg.pkg_key join mil on mil.tos_key=tos.tos_key left outer join evt on mil.evt_key=evt.evt_key where obj.obj_name='<Provide your Resource Object Name';
Process forms attached to Resource Objects :
select obj_name,sdk_name from tos,pkg,sdk,obj where tos.pkg_key=pkg.pkg_key and tos.sdk_key=sdk.sdk_key and PKG.OBJ_KEY=obj.obj_key and obj.obj_name in '<Provide your Resource Object Name';
This tables and queries I have given are as per my work Knowledge on OIM 11gr2ps2,ps3 version, please test the same on your OIM DB if they are working same with your version.
svr— IT Resource information. (svr_key is primary key)
spd— IT resource parameter Definition information
svd – IT Resource Type Definition.
svp - IT Resource parameter values
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');
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;
Different process tasks and mapped adapters in any process definition of a resource:
select mil.mil_name,evt.evt_name,obj.obj_name from obj join pkg on pkg.obj_key=obj.obj_key join tos on tos.pkg_key=pkg.pkg_key join mil on mil.tos_key=tos.tos_key left outer join evt on mil.evt_key=evt.evt_key where obj.obj_name='<Provide your Resource Object Name';
Process forms attached to Resource Objects :
select obj_name,sdk_name from tos,pkg,sdk,obj where tos.pkg_key=pkg.pkg_key and tos.sdk_key=sdk.sdk_key and PKG.OBJ_KEY=obj.obj_key and obj.obj_name in '<Provide your Resource Object Name';
This tables and queries I have given are as per my work Knowledge on OIM 11gr2ps2,ps3 version, please test the same on your OIM DB if they are working same with your version.
No comments:
Post a Comment