OIM Role Model - SQL queries

At a glance, OIM role model consists of User Groups (Business Roles), Access Policies (which in fact are IT Roles – collections of IT Privileges) and Entitlements (atomic IT privileges — for example, Active Directory user groups).

One Business Role (BR) may be linked to several Access Policies (IT Roles). If a BR has some membership rules attached it is called dynamic. Otherwise, if users are assigned to this role manually, is it called static.

Here are some useful SQL queries to OIM DB which can help in building Role Model reports.


1. List all groups with info about administrative groups and membership rules (if any)
General info about user groups is stored in UGP table. Information about Administrative groups (and which admin group has which rights to a particular managed group) is stored in GPP table.

select group_admins_info.ugp_key as "Business Role OIM ID",
group_info.ugp_name as "Business Role Name",
group_admins_info.gpp_ugp_key as "Admin Group OIM ID",
c.ugp_name as "Admin Group Name"
from gpp group_admins_info
left join ugp group_info on group_info.ugp_key = group_admins_info.ugp_key
join ugp c on group_admins_info.gpp_ugp_key=c.ugp_key
where group_admins_info.ugp_key>4

Note: We need a condition where ugp.ugp_key>4 in our query, because we do not want to get info about OIM Built-in groups (SYSTEM ADMINISTRATORS, ALL USERS, SELF OPERATORS и OPERATORS).

2. List all group membership rules (detailed info)
Information about all (not only group membership) rules is stored in RUL table. GM rules always have type ‘General’ and are not linked to any Resource Object or Process Definition.
Information about rule elements (if form “attribute=condition”) is stored in RUE table.
And, finally, we can find out which groups have which membership rules by quering RGP table.

Select rue.rul_key as "Rule OIM ID", rue.rue_sequence as "Predicate No", rue.rue_attribute as "Rule Attribute", rue.rue_operation as "Is Equal", rue.rue_value as "Attribute Value", rgp.ugp_key as "Business Role OIM ID", rul.rul_operator as "Rule Elements join Operator"
from rue
inner join rgp on rue.rul_key = rgp.rul_key
join rul on rul.rul_key = rgp.rul_key

3. List all access policies
General information about access policies is stored in POL table
select
pol.pol_key as "IT Role OIM ID", pol.pol_name as "IT Role Name", pol.pol_description as "IT Role Description"
from pol

4. List all entitlements
All information about OIM entitlements is stored in ENT_LIST table (including the relationship of each entitlement to the real IT privilege in some Resource Object)
Note: This table appeared since release 9.1.0.2.
select ent_list.ent_list_key as "Entitlement OIM ID",
ent_list.obj_key as "Resource Object Key",
obj.obj_name as "Resource Object Name",
ent_list.ent_code as "Entitlement Unique Name",
/* this value corresponds to the 'Code' field of corresponding
lookup containing IT privilege data*/
ent_list.ent_value as "Entitlement Description" from ent_list
inner join obj on obj.obj_key=ent_list.obj_key

5. List all dependencies between Access Policies and Entitlements (child data of resource object forms)
We can find out which data was added to a res object’s process child form by quering POC table.

select poc.poc_key as "Ent 2 Priv Link OIM ID", poc.pol_key as "Entitlement OIM ID", poc.obj_key as "Resource Object OIM ID", obj.obj_name as "Resource Object Name", poc.poc_field_value as "Entitlement Name" from poc
inner join obj on obj.obj_key=poc.obj_key

6. List all connections between User Groups and Access Policies
Connections between User Groups and Access Policies is stored in POG table.
select ugp.ugp_key as "Business Role OIM ID", ugp.ugp_name as "Business Role Name",
pol.pol_key as "IT Role OIM ID", pol.pol_name as "IT Role Name", pol.pol_description as "IT Role Description"
from pol
left join pog on pog.pol_key=pol.pol_key
join ugp on ugp.ugp_key = pog.ugp_key