SQL query

OIM - SQL query to get all proxy assigned for users.

 

SELECT DISTINCT USR.USR_LOGIN "User_Login", 
PU.USR_LOGIN "Proxy_User_Login",
TO_CHAR(P.PXD_START_DATE,'DD-MON-YYYY') "Proxy_Start_Date",
TO_CHAR(P.PXD_END_DATE,'DD-MON-YYYY') "Proxy_End_Date"
FROM PXD P, USR, USR PU
WHERE P.PXD_ORIG_USR_KEY = USR.USR_KEY
AND P.PXD_PROXY_KEY = PU.USR_KEY
AND P.PXD_END_DATE > SYSDATE;

To find SID in Oracle database.

sql> select sys_context('userenv','instance_name') from dual;

 

To find 'Service Name' in Oracle database.

sql> select value from v$parameter where name='service_names';

 

To get all the entitlements provisioned to the user.

SELECT EL.ENT_CODE "Entitlement Name"
FROM ENT_LIST EL, ENT_ASSIGN EA, USR
WHERE EL.ENT_LIST_KEY = EA.ENT_LIST_KEY
AND EA.USR_KEY = USR.USR_KEY
AND EA.ENT_STATUS = 'Provisioned'
AND UPPER(USR.USR_LOGIN) = UPPER('<Replace_User_Login>');

To get all the User's provisioned/enabled accounts.

SELECT OBJ.OBJ_NAME, OST_STATUS
FROM OBJ,OBI,OIU,OST,USR
WHERE OBJ.OBJ_KEY = OBI.OBJ_KEY
AND OBI.OBI_KEY = OIU.OBI_KEY
AND OIU.USR_KEY = USR.USR_KEY
AND OIU.OST_KEY = OST.OST_KEY
AND OST.OBJ_KEY = OBJ.OBJ_KEY
AND OST.OST_STATUS IN ('Enabled','Provisioned')
AND UPPER(USR.USR_LOGIN) = UPPER('<Replace_User_Login>');

To get all the members of specific Role.

SELECT UGP.UGP_NAME,USR.USR_LOGIN
FROM UGP, USG, USR
WHERE USG.USR_KEY = USR.USR_KEY
AND UGP.UGP_KEY = USG.UGP_KEY
AND UPPER(UGP_NAME) = UPPER('<Replace_Role_Name>');

To get all the members of specific Admin Role.

SELECT USR.USR_LOGIN, AR.ROLE_NAME
FROM ADMIN_ROLE_MEMBERSHIP ARM, ADMIN_ROLE AR, USR
WHERE ARM.USER_ID = USR.USR_KEY
AND ARM.ROLE_ID = AR.ROLE_ID
AND USR.USR_STATUS = 'Active'
AND AR.ROLE_NAME=  '<Replace_Admin_Role_Name>';

To get all the Users having specific entitlement provisioned.

SELECT USR.USR_LOGIN, USR.USR_FIRST_NAME, USR.USR_LAST_NAME
FROM ENT_LIST EL, ENT_ASSIGN EA, USR
WHERE EL.ENT_LIST_KEY = EA.ENT_LIST_KEY
AND EA.USR_KEY = USR.USR_KEY
AND EA.ENT_STATUS = 'Provisioned'
AND EL.ENT_CODE = '<Replace_Entitlement_Name>';

To get all the Users having specific account provisioned.

SELECT USR.USR_LOGIN, USR.USR_FIRST_NAME, USR.USR_LAST_NAME
FROM OBJ,OBI,OIU,OST,USR
WHERE OBJ.OBJ_KEY = OBI.OBJ_KEY
AND OBI.OBI_KEY = OIU.OBI_KEY
AND OIU.USR_KEY = USR.USR_KEY
AND OIU.OST_KEY = OST.OST_KEY
AND OST.OBJ_KEY = OBJ.OBJ_KEY
AND OST.OST_STATUS IN ('Enabled','Provisioned')
AND OBJ.OBJ_NAME = '<Replace_Resource_Object_Name>';

To get all proxy assigned for users.

SELECT DISTINCT USR.USR_LOGIN "User_Login", 
PU.USR_LOGIN "Proxy_User_Login",
TO_CHAR(P.PXD_START_DATE,'DD-MON-YYYY') "Proxy_Start_Date",
TO_CHAR(P.PXD_END_DATE,'DD-MON-YYYY') "Proxy_End_Date"
FROM PXD P, USR, USR PU
WHERE P.PXD_ORIG_USR_KEY = USR.USR_KEY
AND P.PXD_PROXY_KEY = PU.USR_KEY
AND P.PXD_END_DATE > SYSDATE;

How to Remove Account from User's Account Tab in OIM.

Below SQL queries will remove specified account from user account tab.

1. Delete entry from child table:

Note: Execute this query if application has entitlements.

DELETE FROM <Replace_Child_Table_Name>
WHERE ORC_KEY IN
  (SELECT OIU.ORC_KEY FROM OIU, <Replace_Child_Table_Name> CT
     WHERE OIU.ORC_KEY = CT.ORC_KEY
     AND OIU.APP_INSTANCE_KEY = (SELECT APP_INSTANCE_KEY FROM  APP_INSTANCE WHERE APP_INSTANCE_NAME = '<Replace_Application_Instance_Name>')
  );

2. Delete entry from parent table:

DELETE FROM <Replace_Parent_Table_Name>
WHERE ORC_KEY IN
  (SELECT OIU.ORC_KEY FROM OIU, <Replace_Parent_Table_Name> PT
WHERE OIU.ORC_KEY= PT.ORC_KEY
AND OIU.APP_INSTANCE_KEY = (SELECT APP_INSTANCE_KEY FROM APP_INSTANCE WHERE APP_INSTANCE_NAME = '<Replace_Application_Instance_Name>')
  );

3. Delete entry from OIU table:

DELETE FROM OIU

WHERE ORC_KEY IN

  (SELECT OIU.ORC_KEY FROM OIU, ORC

WHERE OIU.ORC_KEY = ORC.ORC_KEY

AND OBI_KEY IN (SELECT OBI_KEY FROM OBI WHERE OBJ_KEY = (SELECT OBJ_KEY FROM OBJ WHERE OBJ_NAME = '<Replace_Resource_Object_Name>'))

);