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)

 

Related Posts for Identity Manager


  1. Oracle Identity Manager (User Provisioning – Thor)
  2. Installing Oracle Identity Manager (Thor Xellerate)
  3. Oracle Identity Manager 9.1 released
  4. Oracle Identity Manager (Thor Xellerate) Architecture
  5. Resource, Reconciliation, Provisioning and Connector in Oracle Identity Manager #OIM
  6. Oracle Identity Manager (OIM) Connector for Oracle Internet Directory (OID) : Architecture and Overview
  7. Step by Step Installation of OIM Design Console 9.1.0
  8. Error while running PurgeCache in OIM 11g : LoginException unable to find LoginModule class : WebLogic Full Clinet
  9. Integrate OIM 11g with OID using connector for Provisioning / Reconcilliation – Installation
  10. PurgeCache in OIM 11g : CategoryName
  11. OIM LDAP Sync : Overview and Key Points
  12. OIM 11g : How to export/import/delete Files from MDS
  13. Where are OAM details stored in OIM (account unlock, password reset)
  14. libOVD adapters in OIM LDAP Integration : LDAPsync – view and modify Adapter settings (bindDN and bindPassword)
  15. Error Starting OIM Design Console (xlclient.sh) on Linux java.lang. NoClassDefFoundError
  16. OIM 11g Challenge Questions (PCQ) for forgot password
  17. Oracle EBS Integration with OIM (Identity Manager) : Things you should know
  18. Users not synced from OID to OIM : Debug Scheduled Job
  19. OIM Connector for Microsoft : AD, Exchange, Windows, Password Management
  20. Connector Server for OIM connectors : .NET or JAVA
  21. OIM 11g Challenge Questions – Everything you must know
  22. OIM 11g How to add Challenge Questions
  23. OIM : Assign AD resource : An error occurred because the Adapters are not compiled : How to compile adapters in OIM
  24. OIM User Creation : An Error occurred while performing create user operation. Unable to get LDAP connection
  25. OIM – AD integration : Active Directory Group Lookup Recon failed with error Remote Framework Key is invalid
  26. Microsoft Active Directory (AD) to Oracle Identity Manager (OIM) Password Synchronization: Things you must know : Part I
  27. Provision resource “Microsoft Exchange” to user in OIM : Status remains in Provisioning : Part I
  28. Target Resource (or Managed Resource) vs Trusted Source (or Authoritative Source) Mode : OIM integration with applications (AD, OID, OVD, EBS, SAP, HR, LDAP)
  29. 500 Internal server accessing OIM application : com.bea. security.MicroSM. getInstance oracle.iam. platform. authz.impl
  30. Your account is locked. You can unlock your account by going to Forgot Password
  31. OIM 11g : How to find User and Manager details : USR table
  32. OIM 11g : User Detail/Attribute (Description) not visible in OIM User screen : EBS / OID / OIM integration
  33. OIM 11g: The add proxy operation for user XXXXX failed with following error oracle. bpel. services. workflow. client. workflowservieclientException javax.xml.ws.WebServiceException could not determine wsdl ports
  34. Oracle Identity Manager BP07 for 11gR1 PS1 11.1.1.5.7 (16097399) is now available – (Part of Identity Management SUite BP03 16209876)
  35. OIM 11g : SQL to List User’s Manager
  36. OIM integrated with OAM (SSO) showing OIM login screen : User Soft Locked
  37. OIM 11g: Beware if you are applying WebLogic patch !
  38. Help Me : Microsoft Active Directory Password Sync version and latest patch for Oracle Identity Manager 9.1.1.5
  39. Upgrade OIM connector for Microsoft Exchange to 11.1.1.6 Part I
  40. OIM Administrators : Is your OIM database Growing ? Do you purge enough ?
  41. EBS Integration with OIM : Employee Reconciliation : NumberFormatException: “BUSINESS_GROUP_ID”