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

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)

Connect2DB with VB

Define connection:
Dim strCon
strCon = "Driver={Microsoft ODBC for Oracle}; " & _
"CONNECTSTRING=(DESCRIPTION=" & _
"(ADDRESS=(PROTOCOL=TCP)" & _
"(HOST=mysrv)(PORT=7001))" & _
"(CONNECT_DATA=(SERVICE_NAME=MYDB))); uid=read;pwd=read;"
Dim oCon: Set oCon = WScript.CreateObject("ADODB.Connection")
Dim oRs: Set oRs = WScript.CreateObject("ADODB.Recordset")
oCon.Open strCon
As an option, we can use tnsnames file, where connect string oracle_server exists:
Dim connection, connectionString, theCommand, commandString
connectionString = "DRIVER={Microsoft ODBC for Oracle};SERVER=oracle_server;User Id=user;Password=password;"
Set connection = CreateObject("ADODB.Connection")
Set theCommand = CreateObject("ADODB.Command")
connection.Open connectionString

Example:
1. SELECT
Set rows = connection.Execute("select * from people where email = 'john@doe.com'")

While Not rows.EOF
'Get data fields
name = rows.Fields("name").Value
email = rows.Fields("email").Value
rows.MoveNext
Wend

2. INSERT
const cnstCommand = 1 'Query type - 1 (standard)
commandString = "INSERT INTO people (name, email) VALUES ('John Doe', 'john@doe.com');"
thecommand.CommandText = commandString
thecommand.CommandType = cnstCommand
thecommand.ActiveConnection = connection
thecommand.Execute

3. Stored procedure call
const cnstStoredProcedure = 4 'Command type - 4 (Stored procedure)
commandString = "STORED_PROCEDURE_NAME"
thecommand.CommandText = commandString
thecommand.CommandType = cnstStoredProcedure
thecommand.ActiveConnection = connection
thecommand.Execute

Connect2DB with BASH

#!/bin/bash
########################
export ORACLE_BASE=/u01/app/oracle;
export ORACLE_HOME=/u01/app/oracle/product/10.2.0;
export ORACLE_SID=orcl;
export PATH=$PATH:$ORACLE_HOME/bin:/bin:/sbin;

TEST=`sqlplus -s $USER/$PASSWORD@$NAMES << EOF
-- connect $USER/$PASSWORD@$NAMES
set heading off
set feedback off
whenever sqlerror exit;
-- session count
select count(*) from v\\$session;
exit;
EOF`
echo $TEST

sort by status
select status,count(*) from v\\$session group by status
and format it!
echo $TEST | sed -e 's/"//g' | sed -e 's/,/:/g'

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>

What's this blog about (dummy first post)

Hi!
In this blog I am going to post my notes on Identity and Access Management deployment tips