OTS IAM Architecture

====================================================================

                                                          OIM Useful SQL Queries

====================================================================



*****************************************************************************************************************

User Details:

select * from USR where UPPER(usr_login)='username';

   
*****************************************************************************************************************
   
AUDIT:

select * from dev_oim.UPA_FIELDS UPA_FIELDS LEFT outer JOIN dev_oim.upa_usr  upa_USR ON upa_fields.upa_usr_key=upa_USR.UPA_USR_KEY where UPPER(upa_usr.usr_login) like 'userlogin';

   
*****************************************************************************************************************
   
Resource Audit:

select * from dev_oim.UPA_RESOURCE;

*****************************************************************************************************************
   
Plugins Info:

    select * from dev_oim.plugins;

*****************************************************************************************************************

Audit Table:

      select * from dev_oim.UPA where usr_key='userkey' order by upa_key desc;
     
     
****************************************************************************************************************

OIM Audit Table: Sample Queries to Find Who did What and When ?

     
      SELECT usr_key,   usr_login as changed_by_user,  upa_usr.upa_usr_eff_from_date AS changed_time,   field_name,   field_old_value,   field_new_value
 FROM upa_usr, upa_fields,   (SELECT field_new_value AS changed_by_user    FROM upa_fields
   WHERE upa_fields_key =  (SELECT MAX(upa_fields_key)      FROM upa_fields, upa_usr
     WHERE upa_usr.upa_usr_key = upa_fields.upa_usr_key
     AND upa_usr.usr_key = <>
     AND upa_fields.field_name ='Users.Updated By Login'
     )
   )
 WHERE upa_usr.upa_usr_key = upa_fields.upa_usr_key
 AND upa_usr.usr_key = <>;

*****************************************************************************************************************
Jar file:

    select * from dev_oim.oimhome_jars;

*****************************************************************************************************************

Provisioned/provisioning :

    SELECT OIU_KEY,    OIU.ORC_KEY, USR.USR_KEY,
            USR_LOGIN,
            OBJ_NAME,
            OST_STATUS
        FROM OIU,
              OST,
              OBI,
              OBJ,
              USR
            WHERE OIU.USR_KEY=USR.USR_KEY
            AND OIU.OST_KEY  =OST.OST_KEY
            AND OIU.OBI_KEY  =OBI.OBI_KEY
            AND OBJ.OBJ_KEY  =OBI.OBJ_KEY
            AND USR.USR_LOGIN=UPPER('TUSER13')
            ORDER BY OIU_KEY;



*****************************************************************************************************************

Display all users accounts:

        SELECT *
        FROM USR,
          OBI,
          OIU,
          OBJ,
          OST OST
        WHERE USR.USR_KEY =OIU.USR_KEY
        AND OIU.OBI_KEY   =OBI.OBI_KEY
        AND OIU.OST_KEY   = OST.OST_KEY
        AND OBI.OBJ_KEY   =OBJ.OBJ_KEY;


*****************************************************************************************************************

list of all the users and their resource Status :       
       
SELECT usr.usr_login, usr.usr_first_name, usr.usr_last_name,
obj.obj_key, obj.obj_name, oiu.oiu_create, ost.ost_status, orc.orc_tos_instance_key
FROM orc, usr, obj, oiu, ost, obi
WHERE orc.orc_key = oiu.orc_key AND
oiu.usr_key = usr.usr_key AND
oiu.ost_key = ost.ost_key AND
oiu.obi_key = obi.obi_key AND
obi.obj_key = obj.obj_key



*****************************************************************************************************************

Query to get account details based on specif connectors: 

Ex: OID Connector accounts

            SELECT USR.USR_KEY,
              USR.USR_LOGIN,
              UD_OID_USR.UD_OID_USR_KEY,
              OST.OST_STATUS,
              OBJ.OBJ_NAME
            FROM USR,
              UD_OID_USR,
              OIU,
              OBI,
              OBJ,
              OST,
              ORC
            WHERE USR.USR_LOGIN =UD_OID_USR.UD_OID_USR_USERID -- matching condition needs to be extended based on recon profile
            AND USR.USR_KEY     =OIU.USR_KEY
            AND OIU.OBI_KEY     =OBI.OBI_KEY
            AND OBI.OBJ_KEY     =OBJ.OBJ_KEY
            AND OIU.OST_KEY     =OST.OST_KEY
            AND OST.OST_STATUS IN ('Provisioned', 'Enabled', 'Disabled')
              --and obj.obj_name='OID User'
            AND OIU.ORC_KEY=ORC.ORC_KEY
              --AND ORC.ORC_KEY=UD_OID_USR.ORC_KEY;
             
             
             
*****************************************************************************************************************

OIM User Accounts Provisioned Yet Doesn’t Exist :

select * from oiu
left outer join pol on (oiu.pol_key = pol.pol_key)
left outer join ost on (oiu.ost_key = ost.ost_key)
left outer join obj on (ost.obj_key = obj.obj_key)
left outer join orc on (oiu.orc_key = orc.orc_key)
left outer join usr on (oiu.usr_key = usr.usr_key)
left outer join obi on (oiu.obi_key = obi.obi_key)
where usr.usr_login = ‘JACK’ and usr.usr_status = ‘Active';


select * from OST where OBJ_KEY = '9';


update oiu
set ost_key = 66
where oiu_key = 35341



*****************************************************************************************************************

OIM Update User Accounts After Adding Role:


select USR.USR_LOGIN, USR.USR_KEY, USR.USR_STATUS, USER_PROVISIONING_ATTRS.POLICY_EVAL_IN_PROGRESS, USER_PROVISIONING_ATTRS.POLICY_EVAL_NEEDED from user_provisioning_attrs
left outer join usr on USER_PROVISIONING_ATTRS.USR_KEY = USR.USR_KEY
where POLICY_EVAL_IN_PROGRESS = 1 or POLICY_EVAL_NEEDED = 1
order by usr_login desc




update user_provisioning_attrs
set policy_eval_needed=1,
POLICY_EVAL_IN_PROGRESS=0,
update_date=sysdate
where usr_key in (
select usr_key from usr where usr_login in (‘JACK’) and usr_status = ‘Active’
)


*****************************************************************************************************************
Query OIM Database for OIM User Policy ::


select * from oiu
left outer join pol on (oiu.pol_key = pol.pol_key)
left outer join ost on (oiu.ost_key = ost.ost_key)
left outer join obj on (ost.obj_key = obj.obj_key)
left outer join orc on (oiu.orc_key = orc.orc_key)
left outer join usr on (oiu.usr_key = usr.usr_key)
left outer join obi on (oiu.obi_key = obi.obi_key)
where usr.usr_login = ‘JACK’ and usr.usr_status = ‘Active';

and obj.OBJ_NAME = ‘Oracle DB User';


*****************************************************************************************************************

Below Query gives me all the records of users that are disabled/terminated prior to 50 days (from today) but their AD directory resource is not revoked yet:

SELECT usr.usr_login,
usr.usr_first_name,
usr.usr_last_name,
usr.usr_end_Date,
usr.usr_status "User Status",
ost.ost_status "AD Status",
sysdate - 50 "date comparison"
FROM orc,
usr,
obj,
oiu,
ost,
obi
WHERE orc.orc_key = oiu.orc_key AND
oiu.usr_key = usr.usr_key AND
oiu.ost_key = ost.ost_key AND
oiu.obi_key = obi.obi_key AND
obi.obj_key = obj.obj_key AND
obj.obj_name = 'AD User' AND
ost.ost_status != 'Revoked' AND
usr.usr_disabled = 1 AND
usr.usr_end_date < sysdate - 50;


*****************************************************************************************************************


1. Getting all information about Email Definition:
======================================================
select emd.emd_subject, emd.emd_name,emd.usr_key, emd.emd_body, emd.emd_from_type, emd.emd_type from emd emd where emd.emd_name='Email Definition Name';


*****************************************************************************************************************

2. Updating Resource Status to Revoked for a given resource:
==========================================================

update oiu set ost_key = (select ost_key from ost where obj_key in ( select obj_key from obj where obj_name like 'RESOURCENAME' ) and ost_status like 'Revoked') where ORC_KEY = 'Process Instance Key'

update orc set orc_status='X' where orc_key = 'Process Instance Key'

*****************************************************************************************************************

 OIM – SQL Script to fetch who Revoked a Resource :


select usr.usr_login, obj.obj_name, ost.ost_status, rev.usr_login “Revoker”
from oiu, usr, obi, obj, ost, usr rev
where oiu.usr_key=usr.usr_key
and oiu.obi_key=obi.obi_key
and obi.obj_key=obj.obj_key
and oiu.ost_key=ost.ost_key
and oiu.oiu_updateby=rev.usr_key
and ost.ost_status = ‘Revoked’



*****************************************************************************************************************

OIM DB Script – Fetch status of any task for a specified resource with a specified status:


SELECT USR.USR_LOGIN, USR2.USR_LOGIN , USR.USR_START_DATE, MIL.MIL_NAME,OSI.SCH_KEY,SCH.SCH_STATUS,STA.STA_BUCKET, SCH_ACTUAL_START, SCH_ACTUAL_END FROM
OSI,SCH,STA,MIL,TOS,PKG,OIU,USR,OBJ,OST, ORC,USR USR2,UD_ADUSER
WHERE OSI.MIL_KEY=MIL.MIL_KEY
AND SCH.SCH_KEY=OSI.SCH_KEY
AND STA.STA_STATUS=SCH.SCH_STATUS
AND TOS.PKG_KEY=PKG.PKG_KEY
AND MIL.TOS_KEY=TOS.TOS_KEY
AND OIU.USR_KEY=USR.USR_KEY
AND OIU.OST_KEY=OST.OST_KEY
AND OST.OBJ_KEY=OBJ.OBJ_KEY
AND OSI.ORC_KEY=OIU.ORC_KEY
–AND OST.OST_STATUS = ‘Disbaled’
AND STA.STA_BUCKET = ‘Completed’
AND PKG.PKG_NAME= ‘AD User’
AND MIL.MIL_NAME= ‘Disable User’
AND ORC.ORC_KEY=UD_ADUSER.ORC_KEY
AND ORC.ORC_KEY=OIU.ORC_KEY
AND OSI.OSI_UPDATEBY = USR2.USR_KEY

*****************************************************************************************************************



Update USR set USR_POLICY_UPDATE=1 where USR_KEY =$USER_KEY ;
COMMIT;

Update OIU set OIU_POLICY_REVOKE=1, POL_KEY=$POLICY_KEY, OIU_POLICY_BASED=1 where ORC_KEY=$ORC_KEY;
COMMIT;


**********************************************************************************************************************************************************************************************************************************

OIM SQL query to find who assigned role to users :

select * from upa
where usr_key = (select usr_key from usr
                 where lower(usr_login)= 'lakshmi.nandyala')
and src like '%RoleManager%CREATE%';

Ref : http://www.identityaccessmgmt.com/2015/05/oim-11g-sql-query-to-find-who-assigned.html

**********************************************************************************************************************************************************************************************************************************

List of resource provisioned to user in OIM:


select oiu.oiu_key, oiu.obi_key, oiu.orc_key, ost.ost_status, obj.obj_name, obj.obj_key,oiu.req_key
from oiu
inner join ost on oiu.ost_key = ost.ost_key
inner join obi on oiu.obi_key = obi.obi_key
inner join obj on obi.obj_key = obj.obj_key
where oiu.usr_key =(select usr_key from usr where usr_login='lakshmi.nandyala');



**********************************************************************************************************************************************************************************************************************************


 This query used to get user key from usr table:

   
select usr_key from USR
where usr_login = ‘lakshmi.nandyala’;



This query get process instance of enabled user ‘lakshmi.nandyala’

   
select id from orchprocess
where entityid=’1024’ and entitytype='User' and operation='ENABLE';

This query gets all the event handler for enabled user ‘lakshmi.nandyala’

   
select * from orchevents
where processid=’561092’ order by orchorder;


**********************************************************************************************************************************************************************************************************************************

OIM SQL query to find who modified user attributes:

    select field_name, field_old_value, field_new_value
    from upa_fields fields
    where upa_usr_key in ( select upa_key from upa
                           where upa_key in (select usr_key
                                             from usr
                                             where lower(usr_login) like 'lakshmi.nandyala')))
    and field_name = 'Users.Email'
    order by upa_usr_key, field_name;



**********************************************************************************************************************************************************************************************************************************
OIM SQL query to force users to change password on next login:

he column value 0 means User not forced to reset password on next login.
?
1
2
   
update usr set USR_CHANGE_PWD_AT_NEXT_LOGON='0'
where usr_login = 'lakshmi.nandyala';

The column value 1 means User forced to reset password on next login.
?
1
2
   
update usr set USR_CHANGE_PWD_AT_NEXT_LOGON='1'
where usr_login = 'lakshmi.nandyala';




**********************************************************************************************************************************************************************************************************************************

Oracle Schema Version Registry:

select * from schema_version_registry;



**********************************************************************************************************************************************************************************************************************************

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



**********************************************************************************************************************************************************************************************************************************


 Query to Pull Lookup table form OIM Database

select LKV_ENCODEd, LKV_DECODED from LKV, LKU WHERE lku.lku_type_string_key='Lookup Code Name' and lkv.lku_key = lku.lku_key;


**********************************************************************************************************************************************************************************************************************************

How DB find's out the Version Details :

select * from v$version;

select * from schema_version_registry;


**********************************************************************************************************************************************************************************************************************************

How OIM find's out the Version Details :

select XSD_VALUE from XSD where XSD_CODE='XL_BUILD_NUMBER';



**********************************************************************************************************************************************************************************************************************************

SELECT table_name FROM dba_tables;

SELECT table_name FROM all_tables;

SELECT table_name FROM user_tables;


**********************************************************************************************************************************************************************************************************************************
scheduler Jobs:


SELECT * FROM QRTZ92_JOB_DETAILS



**********************************************************************************************************************************************************************************************************************************