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

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

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;