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

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'