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)

 

About the Author Atul Kumar

Oracle ACE, Author, Speaker and Founder of K21 Technologies & K21 Academy : Oracle Gold Partner specialising in Design, Implement, and Trainings.

follow me on:

Leave a Comment:

7 comments
Amit says September 6, 2013

Dear Atul,
I have created more than 6000 user for system test in OIM and I can see all the user entries in USR table, after finishing my test I want to delete or drop all these users. Please could you help me to delete or drop these user, pls provide me the command to delete/drop.
my database is oracle 11gr2.

Thanks in advance.

Amit

Reply
    Atul Kumar says September 7, 2013

    @ Amit, How did you create users in OIM ?

    You should not update or drop users by updating USR table .

    Reply
Amit says September 8, 2013

Atul,
These users are created by portal-OIM-oracleDB,
and LDAP, As I told you performance team had created more than 6000 user to perform test scenario. now after performance test I have to delete the created users from oracle DB.

Thanks,
Amit

Reply
    Atul Kumar says September 9, 2013

    @ Amit,
    You can either user OIM bulkload or OIM API or Trusted GTC to set end date for these users in BULK. If OIM is integrated with LDAP like OID using LDAPSync then you can use LDIF to bulk update or bulk delete users in OID that will end date then in OIM USR table.

    Reply
Amit says September 9, 2013

Atul,
Actually very sorry to say, I’m not a expert in OIM and ldap part. and our concerned member is also not very much expert. the user is created from web application using OIM and store user information in oracle database with ldap sync.
I would be very thanks to you if you guide me to delete those user. how to delete from OIM and LDAP?
Please provide me the script and way to process.

Thanks,
Amit

Reply
Kalpani says August 4, 2014

Hi Atul,

Does it mean, if we mistakenly create a user in OIM, we cannot delete the user and create another one with the same login? If there a way to fix it back, could you please share.

Thanks,
Kalpani

Reply
kss says December 23, 2014

How do we locate these tables.
I also want to look for USG table attributes.
Please suugest

Reply
Add Your Reply