• Find us:
    +1-669-900-5138   |   +44-203-372-5553
  • Free Newsletter

    Get Latest Updates

  • Make Training Enquiry


    Company

  • Categories

  • Archive

  • OIM 11g : How to find User and Manager details : USR table

    Posted by "" in "OIM" on 2012-12-30

    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”
    42. OIM EBS User Management : eBusiness UM Lookup Definition Reconciliation failed with Invalid Schedule Task Parameter

    5 Responses to “OIM 11g : How to find User and Manager details : USR table”

    1. Amit says:

      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

    2. Amit says:

      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

      • Atul Kumar says:

        @ 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.

    3. Amit says:

      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

    Leave a Reply



  • K21 Technologies is among the most experienced Oracle Gold Partner for Identity Access Management service providers. We work with application development companies and in-house technology division to help achieve significant returns on their IT security investment. Our clientele includes some of the globally renowned corporate, which speaks of our expertise in our field.

    We have the most talented and experienced team that can swiftly deploy security solutions even in complex IT ecosystem. Our clients highly appreciate our timely implementation, interactive training, on-demand support and community resources.
  • CONTACTS

    K21 Technologies
    8 Magnolia Place, Harrow,
    London, HA2 6DS

    UK: +44(0)7476444481
    USA: +1-888-414-1821

  • 2014, K21 Technologies. All rights reserved DMCA.com
  • TOP
    TOP