Working with User Group Membership Rules in OIM

As was discussed in previous post, OIM users’ group membership can be automatically recalculated on each profile update by means of user group membership rules (MR).
In OIM MR is a collection of predicates in form “attribute=value” or “attribure!=value” joined by a logical operator (either AND or OR), where attribure is the name of field from USR table (USR_ or USR_UDF_).
Unfortunately, there is no OIM API (documented API) which allows creating MR from java code (see, for example, this discussion at oracle IDM forums).
There are at least 2 ways how we can create MR programmatically.

1. Create one MR via OIM Design console, export it to xml file. Create as much rules as you need based on this template (you can use java xml libraries) and the import the new rules using OIM import API.

2. You can use the following SQL queries to manage MR and their predicates:
2.1 Create rule
insert into RUL (RUL_KEY, OBJ_KEY, PKG_KEY, RUL_NAME, RUL_OPERATOR, RUL_TYPE, RUL_SUBTYPE, RUL_ALL_OBJECTS, RUL_ALL_PROCESSES, RUL_DATA_LEVEL, RUL_CREATE, RUL_CREATEBY, RUL_UPDATE, RUL_UPDATEBY, RUL_NOTE, RUL_ROWVER) values (rul_seq.nextval, null, null, 'RULE_NAME', 'AND_OR_OPERATOR', 'General', null, '0', '0', null, SYSDATE, 1, SYSDATE, 1, 'RULE_DESCRIPTION', HEXTORAW('0000000000000000'));

2.2 Delete rule with given RUL_KEY
DELETE FROM RUL
WHERE RUL_KEY= PARENT_RUL_KEY;

2.2 Add predicate to rule with given RUL_KEY
Insert into RUE (RUE_KEY, RUL_KEY, RUE_CHILD_RUL_KEY, RUE_ATTRIBUTE, RUE_VALUE, RUE_OPERATION, RUE_SEQUENCE, RUE_ATTRIBUTE_SOURCE, RUE_ATTRIBUTE_SOURCE_SDK_KEY, RUE_TYPE, RUE_DATA_LEVEL, RUE_CREATE, RUE_CREATEBY, RUE_UPDATE, RUE_UPDATEBY, RUE_NOTE, RUE_ROWVER) values (rue_seq.nextval, PARENT_RUL_KEY, null, 'ATTRIBUTE_NAME', 'ATTRIBUTE_VALUE', 'PREDICATE_OPERATOR', PREDICATE_ORDER , 'User Profile Data', null, null, null, SYSDATE, 1, SYSDATE, 1, null, HEXTORAW('0000000000000000'));

Where PARENT_RUL_KEY is a key of rule to which you want to add the predicate, PREDICATE_OPERATOR can be either 0 (==) or 1 (!=) and PREDICATE_ORDER is an order in which predicate will be evaluated during rule evaluation (initial orader is 1).

2.3. Update rule predicate with given RUL_KEY
UPDATE RUE
SET RUE_VALUE='NEW_ATTRIBUTE_VALUE'
WHERE RUL_KEY= PARENT_RUL_KEY and RUE_ATTRIBUTE='ATTRIBUTE_NAME';

2.4 Delete rule predicate with given RUL_KEY
DELETE FROM RUE
WHERE RUL_KEY= PARENT_RUL_KEY and RUE_ATTRIBUTE='ATTRIBUTE_NAME';

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

Create DB link

Run this script on source DB

CREATE DATABASE LINK "ORASUPD_WORLD.REGRESS.RDBMS.DEV.US.ORACLE.COM"
CONNECT TO OIM_USER
IDENTIFIED BY pwd
USING 'ORASUPD.WORLD';

create database link "my1"
connect to oim_user
identified by "manager1"
using '(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = hostname)(PORT = 1521)))(CONNECT_DATA =(SERVICE_NAME= ORASUPD.WORLD) ) )'

insert into rep@ORASUPD_WORLD
select * from rep
where rep_key >18
and rep_key not in (select rep_key from rep@ORASUPD_WORLD)

Get users with more than one instance of given RO

select u.usr_login, count(u.usr_login) as total from ud_parususr p, orc o, usr u where p.orc_key= o.orc_key and o.usr_key= u.usr_key
group by u.usr_login HAVING count(u.usr_login) >1

Get recon event data for AD

select obj.obj_name "Object Name",rcb.rcb_fieldname
"field Name",rcb.rcb_value "User Login",rce.rce_status "Status" , rce.rce_create "Event Date"
from rce rce LEFT OUTER JOIN usr usr onrce.usr_key=usr.usr_key LEFT OUTER JOIN act act on rce.act_key=act.act_key LEFT OUTER
JOIN obj obj on rce.obj_key=obj.obj_key LEFT OUTER JOIN orc orc on rce.orc_key=orc.orc_key LEFT OUTER JOIN usr admusr on rce.rce_admin_usr_key=admusr.usr_key
LEFT OUTER JOIN ugp ugp on rce.rce_admin_ugp_key=ugp.ugp_key ,rcb rcb left outer join orf orf on rcb.orf_key=orf.orf_key where rcb.rce_key=rce.rce_key
and (rcb.rcb_fieldname='givenName' or rcb.rcb_fieldname='cn' or rcb.rcb_fieldname='sn' or rcb.rcb_fieldname='saMMaccountName')
order by rce.rce_create;
--Sql query for "No Match found" condition:--
select obj.obj_name "Object Name",rcb.rcb_fieldname "field Name",rcb.rcb_value "User Login",rce.rce_status "Status" , rce.rce_create "Event Date" from rce rce LEFT OUTER JOIN usr usr onrce.usr_key=usr.usr_key LEFT OUTER JOIN act act on rce.act_key=act.act_key LEFT OUTER JOIN obj obj on rce.obj_key=obj.obj_key LEFT OUTER JOIN orc orc on rce.orc_key=orc.orc_key LEFT OUTER JOIN usr admusr on rce.rce_admin_usr_key=admusr.usr_key LEFT OUTER JOIN ugp ugp on rce.rce_admin_ugp_key=ugp.ugp_key ,rcb rcb left outer join orf orf on rcb.orf_key=orf.orf_key
where rce.rce_status='No Match Found' and rcb.rce_key=rce.rce_key and (rcb.rcb_fieldname='givenName' or rcb.rcb_fieldname='cn' or rcb.rcb_fieldname='sn' or rcb.rcb_fieldname='saMMaccountName')
order by rce.rce_create;

See Metalink Note:434216.1

Get number of RO in each status

select distinct ost_key from oio where ost_key in (Select ost_key from ost where obj_key=RO_KEY)

Get number of recon events for each RO

select (select obj_name from obj where obj_key= a.obj_key) as "IT Resource",count (*) from RCE a where rce_status='Event Received'
group by obj_key