The Linux open source operating system, or Linux OS, is a freely distributable, cross-platform operating system based on Unix

Saturday, March 9, 2019

Oracle Identity Manager Tables and SQL Queries


Oracle Identity Manager Tables and Description/SQL Queries


Table Name
Table Description
OIU
Object Instance Request Target User Information.

Associate user information to the resource object instance when provisioning take places.
OST
Object Status Information.
OBI
Object Instance Information.

Once resource provisioned to user, OIM created resource instance for each resource provisioning.
OBJ
Resource Object definition information

This contains detail about resource such as resource name, auto-save enable or not and auto-prepopulate is enable or not, and whether or not the resource object allows multiple instances.
USR
It contains user information like login id, password, etc.,
ORCHPROCESS
Stores the process instances that are being executed.
ORCHEVENTS
Stores event handler names, status and result for all orchestration processes.

Event status like COMPLETED, FAILED, PENDING, etc.
ORCHFAILEDEVENTS
Stores event handler information that are executed because of failures in main flow.
UPA
User profile audit information
USG
Role assigned to user

Query to list the resource that are in different status for given user:-
 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 = 'TSTUSR01'); 
  
Changing Resource Status in OIM in Account Tab:-
Through below query we can change the resource (AD, LDAP, Exchange etc..) Status.
UPDATE oiu
   SET ost_key =
           (SELECT ost_key
              FROM ost
             WHERE     obj_key = (SELECT obj_key
                                    FROM obj
                                   WHERE obj.obj_name = 'LDAP User')
                   AND ost_status = 'Revoked')
 WHERE oiu.orc_key IN (SELECT orc_key
                         FROM ud_ldap_usr
                        WHERE ud_ldap_usr_userid IN ('TSTUSR01')); 

Force users to change password on next login:-
In case user’s password reset by either OIM Admin or API, user will be prompt to reset on next login. to avoid the force user password on next login by update column 'USR_CHANGE_PWD_AT_NEXT_LOGON' in table ‘usr’. Column takes values 0 or 1. 
The column value 0 means User not forced to reset password on next login.
 UPDATE usr
   SET USR_CHANGE_PWD_AT_NEXT_LOGON = '0'
 WHERE usr_login = 'TSTUSR01';
 The column value 1 means User forced to reset password on next login.
 UPDATE usr
   SET USR_CHANGE_PWD_AT_NEXT_LOGON = '1'
 WHERE usr_login = ‘TSTUSR01’;

Orchestration Query:-
Orchestration is main Component in OIM, Operations, such as create user, modify user, Delete, Enable etc., were closely integrate with OIM Orchestration.
Known Issue: OIM Orchestration will retry failed event handlers ONLY 2 times and will ignore after that. Because, the retry limit was hard coded in OIM.
SQL Query:
Below sql query is to get list of event handlers, which are executed for a particular users during enable process:
This query used to get user key from usr table-
SELECT usr_key
  FROM USR
 WHERE usr_login = 'TSTUSR01'; 
This query get process instance of enabled user ‘TSTUSR01’
SELECT id
  FROM orchprocess
WHERE entityid = '1045' AND entitytype = 'User' AND operation = 'ENABLE';

This query gets all the event handler for enabled user ‘TSTUSR01’:-
SELECT *
    FROM orchevents
   WHERE processid = ’237254’
ORDER BY orchorder; 

In the same way we can use for Create, Modify, Delete, Disable...etc Operation.

Query to find who modified user attributes:-
We can identify when and who made change for user profile attributes for example, email address.
Below query fetch the email address value for user ‘TSTUSR01’ from audit table: 
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 'TSTUSR01'))
and field_name = 'Users.Email'
order by upa_usr_key, field_name;

Query to find who assigned role to users:-
OIM provides strong auditing features that will capture all user profile modification. It will be stored on UPA table.
Below query gets list of roles when was assigned to user ‘TSTUSR01’:
SELECT *
  FROM upa
 WHERE     usr_key = (SELECT usr_key
                        FROM usr
                       WHERE LOWER (usr_login) = 'TSTUSR01')
       AND src LIKE '%RoleManager%CREATE%'; 

Similarly, we can check for user role revoked by using src with ‘%RoleManager%DELETE%'

Oracle Schema Version Registry:-
Most of the Oracle Fusion Middleware components require existence of schemas in database prior to install. These schemas created and loaded using RCU. 
You can run query to get list of schema created though RCU:
SELECT * FROM schema_version_registry;

Query to get users whose specific role:-
We often may need to find user who has specific role in OIM.
I have used query to get users who have role called ‘System Administrator’.
SELECT usr.usr_display_name,
       usr.usr_login,
       usr.usr_email,
       ugp.ugp_name
  FROM usg  usg
       LEFT OUTER JOIN usr usr ON (usg.usr_key = usr.usr_key)
       LEFT OUTER JOIN ugp ugp ON (ugp.ugp_key = usg.ugp_key)
 WHERE UPPER (ugp_name) IN (UPPER ('System Administrator')); 

Query to update the ldap common name on process form:-
UPDATE ud_ldap_usr
   SET UD_LDAP_USR_COMMON_NAME = 'Tst01 User01'
 WHERE UD_LDAP_USR_USERID = 'TSTUSR01'

Query to find all the Schedule tasks:-
SELECT * FROM QRTZ92_JOB_DETAILS          --contains list of all schedule task
SELECT * FROM JOB_HISTORY   --Contains the details about the execution history

Query to list the resource that are in different status for given user:-
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 = 'MUQTHIYAR.PASHA');

Changing Resource Status in OIM in Account Tab:-
Through below query we can change the resource (AD, LDAP, Exchange etc..) Status.
UPDATE oiu
   SET ost_key =
           (SELECT ost_key
              FROM ost
             WHERE     obj_key = (SELECT obj_key
                                    FROM obj
                                   WHERE obj.obj_name = 'LDAP User')
                   AND ost_status = 'Revoked')
 WHERE oiu.orc_key IN (SELECT orc_key
                         FROM ud_ldap_usr
                        WHERE ud_ldap_usr_userid IN ('TESTUSR01'));

In the same way we can use for Create, Modify, Delete, Disable...etc Operation.

Query to find who modified user attributes:-
We can identify when and who made change for user profile attributes for example, email address.
Below query fetch the email address value for user ‘TSTUSR01’ from audit table:
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 'MUQTHIYAR.PASHA'))
         AND field_name = 'Users.Email'
ORDER BY upa_usr_key, field_name;

Query to Generate Audit Report of Users:-
 SELECT b.usr_login     LoginID,
         a.field_name    attribute_Name,
         a.field_old_value Old_Value,
         a.field_new_value New_Value,
         a.create_date   CreateDate,
         a.update_date   UpdateDate
    FROM PRDOIM_OIM.usr b, PRDOIM_OIM.upa_usr c, PRDOIM_OIM.upa_fields a
   WHERE     1 = 1
         AND c.usr_key = b.usr_key
         AND a.upa_usr_key = c.upa_usr_key
         AND a.field_name != 'Users.Role'
ORDER BY a.UPA_USR_KEY DESC;

Query to get info on all the 'Active' users:-
  SELECT U.USR_FIRST_NAME || ' ' || U.USR_LAST_NAME
             Name,
         U.USR_LOGIN
             LoginName,
         R.UGP_DISPLAY_NAME
             RoleName,
         A.STATUS
             RoleStatus,
         A.USG_UPDATEBY_LOGIN
             Assigned_RevokedBy_LoginName,
         USR.USR_DISPLAY_NAME
             Assigned_RevokedBy_Name,
         A.CREATE_DATE
             Assigned_RevokedDate
    FROM PRDOIM_OIM.UPA_USR           U,
         PRDOIM_OIM.UGP               R,
         PRDOIM_OIM.UPA_GRP_MEMBERSHIP A,
         PRDOIM_OIM.USR               USR
   WHERE     U.USR_STATUS = 'Active'
         AND U.USR_LOGIN NOT IN ('oim_write',
                                 'XELSYSADM',
                                 'FAAdmin',
                                 'OCLOUD9_OSN_APPID',
                                 'FUSION_APPS_HCM_SOA_SPML_APPID',
                                 'oamAdminUser',
                                 'weblogic_idm')
         AND A.UPA_USR_KEY = U.UPA_USR_KEY
         AND A.UGP_KEY = R.UGP_KEY
         AND A.USG_UPDATEBY_LOGIN = USR.USR_LOGIN
ORDER BY A.CREATE_DATE, A.STATUS, R.UGP_DISPLAY_NAME;

Query to get info for a single user:-
SELECT U.USR_FIRST_NAME || ' ' || U.USR_LAST_NAME
             Name,
         U.USR_LOGIN
             LoginName,
         R.UGP_DISPLAY_NAME
             RoleName,
         A.STATUS
             RoleStatus,
         A.USG_UPDATEBY_LOGIN
             Assigned_RevokedBy_LoginName,
         USR.USR_DISPLAY_NAME
             Assigned_RevokedBy_Name,
         A.CREATE_DATE
             Assigned_RevokedDate
    FROM PRDOIM_OIM.UPA_USR           U,
         PRDOIM_OIM.UGP               R,
         PRDOIM_OIM.UPA_GRP_MEMBERSHIP A,
         PRDOIM_OIM.USR               USR
   WHERE     U.USR_STATUS = 'Active'
         AND U.USR_LOGIN = 'MUQTHIYAR.PASHA'
         AND A.UPA_USR_KEY = U.UPA_USR_KEY
         AND A.UGP_KEY = R.UGP_KEY
         AND A.USG_UPDATEBY_LOGIN = USR.USR_LOGIN


ORDER BY A.CREATE_DATE, A.STATUS, R.UGP_DISPLAY_NAME;

0 comments :

Post a Comment

Note: Only a member of this blog may post a comment.