OIM 11g : How to find User and Manager details : USR table
Oracle Identity Manager (OIM) maintains user details in table USR where
- USR_LOGIN is the login ID of user that is used to login to OIM
- USR_FIRST_NAME is first name of user
- USR_LAST_NAME is last name of user
- USR_MANAGER_KEY is USR_KEY of User’s Manager
- USR_LDAP_DN is DN (Distinguished Name) of User in LDAP Server (If OIM is configured with LDAP SYNC) to know more about LDAP SYNC with OIM 11g click here, here, and here
If you wish to list OIM user and their manager’s name then run below SQL query (connect using OIM schema XXXX_OIM)
SQL> select USR_FIRST_NAME || ‘|’ || USR_LAST_NAME || ‘|’ || USR_LOGIN || ‘|’ || (select u2.USR_LOGIN from usr u2 where u2.usr_key=u1.usr_manager_key and rownum < 2) as MANAGER_NAME from USR u1 ;
You should see output like
MANAGER_NAME
——————————————————————————–
System|Administrator|XELSYSADM|
Operator|Group|XELOPERATOR|
Registration|Self|XELSELFREG|
WEBLOGIC|WEBLOGIC|WEBLOGIC|
OIMINTERNAL|OIMINTERNAL|OIMINTERNAL|
|user10|USER10|
|test15|TEST15|
|user3|USER3|
|user30|USER30|
Atul|Kumar|ATUL30|XELSYSADM
Here user ATUL30 (First Name Atul and LastName Kumar) has manager XELSYSADM .
SQL> desc usr
Name Null? Type
—————————————– ——– —————————-
USR_KEY NOT NULL NUMBER(19)
ACT_KEY NOT NULL NUMBER(19)
USR_LAST_NAME NOT NULL VARCHAR2(150 CHAR)
USR_FIRST_NAME VARCHAR2(150 CHAR)
USR_MIDDLE_NAME VARCHAR2(80 CHAR)
USR_DISPLAY_NAME VARCHAR2(382 CHAR)
USR_MANAGER VARCHAR2(12 CHAR)
USR_TYPE NOT NULL VARCHAR2(30 CHAR)
USR_LOCATION VARCHAR2(34 CHAR)
USR_FSS VARCHAR2(255 CHAR)
USR_TODO VARCHAR2(16 CHAR)
USR_PASSWORD NOT NULL VARCHAR2(200 CHAR)
USR_DISABLED VARCHAR2(1 CHAR)
USR_PWD_CANT_CHANGE VARCHAR2(1 CHAR)
USR_PWD_MUST_CHANGE VARCHAR2(1 CHAR)
USR_PWD_NEVER_EXPIRES VARCHAR2(1 CHAR)
USR_UPDATE_AD VARCHAR2(20 CHAR)
USR_CREATED DATE
USR_STATUS VARCHAR2(25 CHAR)
USR_EMP_TYPE NOT NULL VARCHAR2(255 CHAR)
USR_LOGIN VARCHAR2(256 CHAR)
USR_DISABLED_BY_PARENT VARCHAR2(1 CHAR)
USR_PWD_EXPIRE_DATE DATE
USR_PWD_WARN_DATE DATE
USR_MANAGER_KEY NUMBER(19)
USR_POLICY_UPDATE VARCHAR2(1 CHAR)
USR_PWD_WARNED VARCHAR2(1 CHAR)
USR_PWD_EXPIRED VARCHAR2(1 CHAR)
USR_START_DATE DATE
USR_END_DATE DATE
USR_PROVISIONING_DATE DATE
USR_DEPROVISIONING_DATE DATE
USR_PROVISIONED_DATE DATE
USR_DEPROVISIONED_DATE DATE
USR_EMAIL VARCHAR2(256 CHAR)
USR_LOCKED VARCHAR2(1 CHAR)
USR_LOGIN_ATTEMPTS_CTR NUMBER(19)
USR_PWD_RESET_ATTEMPTS_CTR NUMBER(19)
USR_CHANGE_PWD_AT_NEXT_LOGON VARCHAR2(1 CHAR)
USR_PWD_MIN_AGE_DATE DATE
USR_DATA_LEVEL NUMBER(1)
USR_CREATE DATE
USR_CREATEBY NUMBER(19)
USR_UPDATE DATE
USR_UPDATEBY NUMBER(19)
USR_NOTE CLOB
USR_TIMEZONE VARCHAR2(100 CHAR)
USR_LOCALE VARCHAR2(100 CHAR)
USR_LOCKED_ON DATE
USR_MANUALLY_LOCKED VARCHAR2(1 CHAR)
USR_AUTOMATICALLY_DELETE_ON DATE
USR_FULL_NAME VARCHAR2(240 CHAR)
USR_COUNTRY VARCHAR2(100 CHAR)
USR_DEPT_NO VARCHAR2(80 CHAR)
USR_DESCRIPTION VARCHAR2(2000 CHAR)
USR_COMMON_NAME VARCHAR2(240 CHAR)
USR_EMP_NO VARCHAR2(80 CHAR)
USR_FAX VARCHAR2(4000 CHAR)
USR_GEN_QUALIFIER VARCHAR2(80 CHAR)
USR_HIRE_DATE DATE
USR_HOME_PHONE VARCHAR2(20 CHAR)
USR_LOCALITY_NAME VARCHAR2(80 CHAR)
USR_MOBILE VARCHAR2(20 CHAR)
USR_PAGER VARCHAR2(20 CHAR)
USR_HOME_POSTAL_ADDRESS VARCHAR2(2800 CHAR)
USR_POSTAL_ADDRESS VARCHAR2(2800 CHAR)
USR_POSTAL_CODE VARCHAR2(30 CHAR)
USR_PO_BOX VARCHAR2(20 CHAR)
USR_STATE VARCHAR2(120 CHAR)
USR_STREET VARCHAR2(120 CHAR)
USR_TELEPHONE_NUMBER VARCHAR2(4000 CHAR)
USR_TITLE VARCHAR2(120 CHAR)
USR_INITIALS VARCHAR2(10 CHAR)
USR_PWD_GENERATED VARCHAR2(1 CHAR)
USR_LDAP_ORGANIZATION VARCHAR2(240 CHAR)
USR_LDAP_ORGANIZATION_UNIT VARCHAR2(240 CHAR)
USR_LDAP_GUID VARCHAR2(256 CHAR)
USR_LDAP_DN VARCHAR2(2048 CHAR)
USR_ROWVER RAW(8)
USR_ACCESSIBILITY_MODE VARCHAR2(20 CHAR)
USR_COLOR_CONTRAST VARCHAR2(10 CHAR)
USR_FONT_SIZE VARCHAR2(10 CHAR)
USR_NUMBER_FORMAT VARCHAR2(30 CHAR)
USR_CURRENCY VARCHAR2(20 CHAR)
USR_DATE_FORMAT VARCHAR2(20 CHAR)
USR_TIME_FORMAT VARCHAR2(20 CHAR)
USR_EMBEDDED_HELP VARCHAR2(10 CHAR)
USR_LANGUAGE VARCHAR2(100 CHAR)
USR_TERRITORY VARCHAR2(100 CHAR)
USR_NAME_PREFERRED_LANG VARCHAR2(20 CHAR)
USR_UDF_OBGUID VARCHAR2(300 CHAR)