Oracle Multi-tenant Application Containers – Part XIII: Identify common application users for an application

    Jul 12, 2018 9:42:00 AM by Anju Garg

    An Oracle multitenant application container can house multiple applications. These applications may be shared by various application PDBs within the container. Each of these applications can have common application user(s) associated with it, which are created as part of the application install/upgrade action. However, it is not possible to identify common application users associated with a specific application by looking at DBA_USERS, as it does not have any column indicating the application. Regarding this, I contacted Connor McDonald, Developer Advocate at Oracle Corporation, who subsequently reached out to the Multitenant PM and got this back:

    DBA_USERS has these columns: COMMON, INHERITED, IMPLICIT, 

    For the schema created within an Application Begin/End block, you should see 'YES', 'NO', 'NO' for these columns. 

    DBA_USERS does not indicate which Application created the user, but USER$.SPARE10 column has the Application ID of the Application that created the user. 

    In this article, I will demonstrate how we can identify common application users associated with an application using the USER$.SPARE10 column.

    Current scenario

    Here, we have an Oracle Database 12.2.0.1 CDB called orclcdb, having

    • CDB seed PDB pdb$seed 
    • A regular PDB orcl 
    • An application container app_roothaving
      • Application PDBs app_pdb1 and app_pdb2
      • Applications
        • sales_app with application users sales_app_user1 and sales_app_user2 created within the Application Begin/End block
        • hr_app with application user hr_app_user created within the Application Begin/End block

    5383.anju july.png-1100x19998

    We will learn how to identify the mapping between

    • Common application users sales_app_user1, sales_app_user2 and hr_app_user

    and

    • Applications sales_app and hr_app.

    Demonstration

    Let us first connect to CDB orclcdb and verify that there is a regular PDB orcl, and an application container app_root in this CDB.

    SQL> conn sys/oracle@orclcdb as sysdba

         set sqlprompt CDB$ROOT>

         sho con_name

     

    CON_NAME

    ------------------------------

    CDB$ROOT

     

    CDB$ROOT>SELECT con_id, name, open_mode, application_root app_root,

               application_pdb app_pdb, application_seed app_seed

             from v$containers

            where application_root = 'NO' and application_PDb = 'NO'

              and application_seed = 'NO';

     

       CON_ID NAME       OPEN_MODE APP_ROOT APP_PDB APP_SEED

    ---------- ---------- ---------- -------- -------- --------

          1 CDB$ROOT   READ WRITE NO   NO     NO

          2 PDB$SEED   READ ONLY  NO   NO     NO

          3 ORCL       READ WRITE NO   NO     NO

     

    CDB$ROOT>SELECT con_id, name, open_mode, application_root app_root,

                   application_pdb app_pdb, application_seed app_seed

             from v$containers

            where application_root = 'YES' and application_pdb = 'NO';  

     

       CON_ID NAME       OPEN_MODE   APP_ROOT APP_PDB APP_SEED

    ---------- ---------- ---------- -------- -------- --------

          6    APP_ROOT   READ WRITE YES      NO     NO

     

    Connect to application root app_root and note that there are two application PDBs app_pdb1 and app_pdb2 associated with it.

    APP_ROOT>@get_app_containers

     

       CON_ID NAME       OPEN_MODE   APP_ROOT APP_PDB  APP_SEED APP_ROOT_CLONE

    ---------- ---------- ---------- -------- -------- -------- -----------------

          4   APP_PDB1   READ WRITE   NO      YES      NO         NO

          6   APP_ROOT   READ WRITE   YES     NO       NO         NO

          10  APP_PDB2   READ WRITE   NO      YES      NO         NO

     

    Besides an implicit application, two other applications sales_app and hr_app are currently installed in this container.

    APP_ROOT>@get_app_status              

     

    APP_NAME                       APP_VERSION APP_ID APP_STATUS  IMPLICIT

    -------------------------------------- ------------ ------ ------------ ------

    APP$62CB609B7509C23AE05364C909C0AE0E   1.0            2 NORMAL      Y

    SALES_APP                              1.4           21 NORMAL      N

    HR_APP                                 1.0           41 NORMAL      N

     

    Let us find out the common application users created in application root app_root.

    APP_ROOT>select username, common from dba_users

             where common = 'YES' and inherited = 'NO';

     

    USERNAME                 COMMON

    ------------------------------ ----------

    SALES_APP_USER2          YES

    SALES_APP_USER1          YES

    HR_APP_USER              YES

     

    From the output, we learn that there are three common application users in application root app_root but it is not possible to identify which application user is associated with which application, as there is no application-related column in data dictionary view DBA_USERS. However, in the base table user$, the SPARE10 column has the application ID of the application that created the user. 

    Let us join dba_applications with USER$ to find out the mapping between common application users and applications.

    APP_ROOT>Select s.name username, a.app_name from user$ s, dba_applications a

    where s.spare10 = a.app_id;

      

    USERNAME                 APP_NAME

    ------------------------------ --------------------------------------

    SALES_APP_USER1          SALES_APP

    SALES_APP_USER2          SALES_APP

    HR_APP_USER              HR_APP

     

    Now, we learn that the users sales_app_user1 and sales_app_user2 are associated with the sales_app application and the user hr_app_user is associated with the hr_app application.

    Thus, the undocumented column SPARE10 of data dictionary base table USER$ can be used to identify the application which created the application user(s).

    Summary

    • An Oracle multitenant application container can house multiple applications.
    • Each of these applications can have common application user(s) associated with it that are created as part of the application install/upgrade action.
    • It is not possible to identify common application users associated with a specific application by looking at DBA_USERS, as it does not have any column indicating the application.
    • We can identify common application users associated with an application using the USER$.SPARE10 column which stores the application ID of the application that created the user.

    Scripts used in this article

    get_app_containers.sql

    -- Find out the containers in an application root when executed from application root

    SELECT con_id, name, open_mode, application_root app_root,

                application_pdb app_pdb, application_seed app_seed

    from v$containers 

    order by con_id; 

     

    get_app_status.sql

    -- Find out the status of various applications in an application container when executed from an application root

    select app_name, app_version, app_id, app_status, app_implicit implicit from dba_applications;

    Tags: Oracle

    Anju Garg

    Written by Anju Garg

    Anju Garg is an Oracle Ace with over 14 years of experience in IT Industry in various roles. Since 2010, she has been involved in teaching and has trained more than a hundred DBAs from across the world in various core DBA technologies like RAC, Data guard, Performance Tuning, SQL statement tuning, Database Administration etc. She is a regular speaker at Sangam and OTNYathra. She writes articles about Oracle and is one of the reviewers of the following book published by Pearson Oracle Problem-Solving and Troubleshooting Handbook She is certified for : Oracle 9i Database Administration OCP Oracle 11g Database Administration OCP Oracle 11g Performance Tuning OCE Oracle 11g R2 RAC OCE Oracle 11g SQL Tuning OCE Oracle 12c Database Administration OCP Oracle Real Application Clusters 12c Certified Implementation Specialist She is passionate about learning and has keen interest in RAC and Performance Tuning. She shares her knowledge via her technical blog at http://oracleinaction.com/