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';

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

Getting the list of unmatched users

This query returns all recon events for users, who exist in OIM, and should match, but for some reasons are in 'No Match Found' status

select * from usr where usr_udf_emp_num in (
select distinct rcd_value from rcd where rce_key in (
select DISTINCT rce_key from rce where obj_key=OBJECT_KEY and rce_status='No Match Found' and rce_key BETWEEN FLIMIT and LLIMIT
)
and orf_key=RECON_KEY_FILED and rcd_value is not null
)

Provisioning 2 Revoked/Provisioned

If resource status for user got stuck in 'Provisioning' status (for example becuase of adapter error), that problem can be solved by running the following sql statement:
UPDATE OIU SET OST_key=(Select ost_key from ost where obj_key=(select obj_key from obj where obj_name='RO') and OST_status='Status2') WHERE OST_key=(Select ost_key from ost where obj_key=(select obj_key from obj where obj_name='RO') and OST_status='Status1');

commit;

Where RO - resource name, Status2 = Provisioned/Revoked, Status1 = Provisioning (or any other 'bad' status).

For example, for AD User RO we must run the following query:

UPDATE OIU SET OST_key=(Select ost_key from ost where obj_key=(select obj_key from obj where obj_name='AD User') and OST_status='Revoked') WHERE OST_key=(Select ost_key from ost where obj_key=(select obj_key from obj where obj_name='AD User') and OST_status='Provisioning');

commit;

see also metalink (KB DocID 464074.1)

Useful OIM links@blogspot

Extremely useful OIM-IDM blog!
OIM 9.1 Metadata
http://rajnishbhatia19.blogspot.com/2008/09/oim-91-metadata.html

Executing VBScripts within OIM
http://rajnishbhatia19.blogspot.com/2008/07/executing-vbscripts-within-oim.html

Calling OIM User Operations from a JSP Web Page
http://rajnishbhatia19.blogspot.com/2007/08/calling-oim-user-operations-from-jsp.html

Рassing user defined fields in OIM to ORM

1. First use this command after login into SQL PLUS as appuser of ORM:

update FINALIZATION_LEASE set EXPIRATION = 0;
COMMIT;

2. Add this to OIM_INTEGRATION.xml

<parameter>
<source-name>USR_UDF_MYFIELD</source-name>
<dest-name>ormMyField</dest-name>
<dest-type>java.lang.String</dest-type>
<default>No value provided</default>
</parameter>
3. Modify the ...bizlogic.def/bizlogic.oim_integration.xml with something like:
<argument id="MyFieldName">
<title>MyFieldName</title>
<related-object-type>person</related-object-type>
<related-object-attribute>ormMyField</related-object-attribute>
</argument>
4. Deploy the modified car files

5. Add this entry to IMCONFIG.xml

<exported-fields>
<exported-field-group name="Users">
<fields>
<field name="USR_UDF_MYFILED">
</fields>
</exported-field-group>
</exported-fields>