Oracle Multitenant Application Containers – Part XI Common Application Users

    May 11, 2018 11:54:16 AM by Anju Garg

    Prior to Oracle database 12.2.0.1, there could be following types of users in an Oracle multitenant container database (CDB)

    –  Common User – A common user is created in the CDB root and has the same username and authentication credentials across all the existing and future PDBs. A common user can always connect to and perform administrative operations in CDB$ROOT. Moreover, a common user can also perform administrative operations within any PDB in which it has sufficient privileges. A common user can be :

    • Oracle supplied – For example, SYS and SYSTEM
    • User created

    –  Local user – A local user is a database user that exists only in a single PDB and has administrative privileges only in that PDB.

     

    Users in a Pre 12.2.0.1 Oracle multitenant CDB

    With the introduction of Oracle multitenant application containers in Oracle Database 12.2.0.1, a user can create a common user in the CDB root or in an application root. As a result, now there can be two subtypes of user-created common users; i.e.,

    –  Application Common User - An application common user is created in the application root. It can only connect to the application root in which it was created, or to a PDB that is plugged in to this application root, depending on its privileges. It is responsible for activities such as creating, plugging, unplugging, opening, closing, and dropping application PDBs. It can create application common objects in the application root. As it does not have the CREATE SESSION privilege in any container outside its own application container, it cannot access the entire CDB environment and is restricted to its own application container.

    –  CDB Common User – A CDB common user is defined in the CDB root. It may be able to access all PDBs within the CDB, including application roots and their application PDBs. It can connect to any container in the CDB to which it has sufficient privileges. With appropriate privileges, it can perform all tasks that an application common user can perform.

    Users in an Oracle multitenant CDB 12.2.0.1 onwards

    It is worth mentioning here that local users in either the regular PDBs or the application PDBs have access only to the PDBs in which the local user resides.

     

    CDB Common User vs Application Common User

    • Whereas a CDB common user account is created while connected to CDB$ROOT,an application common user account is created in an application root.
    • A CDB common user account is common to all PDBs and application roots in the CDB. An application common user account is common only within this application container.
    • CDB common users may be able to access all PDBs within the CDB, including application roots and their application PDBs. Application common users have access only to the PDBs that belong to the application container and hence cannot access the entire CDB environment like CDB common users. 
    • An application common user is restricted to its own application container as it does not have the CREATE SESSION privilege in any container outside its own application container.
    • Only a CDB common user can run an ALTER DATABASE statement that specifies the recovery clauses that apply to the entire CDB.

    In this article, I will demonstrate access hierarchy and visibility of CDB common users, application common users, and local users in a CDB.

     

    Current scenario

    Here, we have an Oracle database 12.2.0.1 CDB called orclcdb, as shown below. Within this CDB, besides the CDB Seed PDB pdb$seed, we have a regular PDB orcl and an application container app_root  with an application sales_app installed in it. The application container app_root houses one application PDB app_pdb1.

    8508.a3.png-1100x19998

     

    Demonstration

    - Let us first connect to CDB orclcdb and verify that it has

    -      one regular PDB, orclpdb

    -      one application container app_root having

    • An application sales_app installed in it
    • An application PDB app_pdb1
    SQL> conn sys/oracle@orclcdb as sysdba
         set sqlprompt ORCLCDB$ROOT>
          sho con_name
    CON_NAME
    ------------------------------
    CDB$ROOT
    ORCLCDB$ROOT>@get_app_containers
     CON_ID NAME       OPEN_MODE APP_ROOT APP_PDB APP_SEED APP_ROOT_CLONE
    ------- ---------- ---------- -------- -------- -------- ------------------
          1 CDB$ROOT   READ WRITE      NO    NO     NO    NO
          2 PDB$SEED   READ ONLY       NO    NO     NO     NO
          3 ORCL       READ WRITE      NO    NO     NO     NO
          4 APP_PDB1   READ WRITE      NO    YES    NO     NO
          6 APP_ROOT   READ WRITE      YES   NO     NO    NO
    APP_ROOT>@get_app_status

    APP_NAME                        APP_VERSION APP_ID APP_STATUS  IMPLICIT
    -------------------------------------- ------------ ------ ------------ ------
    APP$62CB609B7509C23AE05364C909C0AE0E   1.0          2 NORMAL   Y

    SALES_APP                            1.3      21 NORMAL      N

    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

    - Note that the Oracle supplied common user system exists in CDB$ROOT and is replicated in application root, regular PDB, and application PDB

    ORCLCDB$ROOT>SELECT u.username, u.common, u.con_id, c.name con_name
                 FROM cdb_users u, v$containers c
                 WHERE username = 'SYSTEM' and u.con_id = c.con_id
                ORDER BY con_id;
    USERNAME                 COMMON          CON_ID CON_NAME
    ------------------------ ---------- ---------- --------------------
    SYSTEM                         YES           1 CDB$ROOT
    SYSTEM                         YES           3 ORCL
    SYSTEM                         YES           4 APP_PDB1
    SYSTEM                         YES           6 APP_ROOT

    - Create a CDB common user c##_cdb_root_user in the CDB root and grant privileges to it.

    ORCLCDB$ROOT>create user c##_cdb_root_user identified by oracle;
                 GRANT CREATE SESSION, create procedure, CREATE TABLE,
                       unlimited tablespace
                 TO c##_cdb_root_user container = all;

    - Verify that the new CDB common user c##_cdb_root_user has been replicated in application root APP_ROOT, regular PDB orcl, and application PDB app_pdb1 and can connect to each of these since it has create session privilege in all the containers.

    ORCLCDB$ROOT>SELECT u.username, u.common, c.name con_name, u.con_id con_id
                FROM cdb_users u, v$containers c
                WHERE username = 'C##_CDB_ROOT_USER' AND u.con_id = c.con_id
                ORDER BY 4;
     
    USERNAME              COMMON  CON_NAME           CON_ID
    ------------------- -------- ------------------- ----------
    C##_CDB_ROOT_USER        YES  CDB$ROOT              1
    C##_CDB_ROOT_USER        YES  ORCL                  3
    C##_CDB_ROOT_USER        YES  APP_PDB1              4
    C##_CDB_ROOT_USER        YES  APP_ROOT              6
     
    ORCLCDB$ROOT>conn C##_CDB_ROOT_USER/oracle@host01:1522/orcl
    Connected.
    ORCLCDB$ROOT>conn C##_CDB_ROOT_USER/oracle@host01:1522/app_root
    Connected.
    ORCLCDB$ROOT>conn C##_CDB_ROOT_USER/oracle@host01:1522/app_pdb1
    Connected.

    - Check that presently application root APP_ROOT has only one user created common user C##_CDB_ROOT_USER.

    • A value of 'YES' in the COMMON column tells us that user C##_CDB_ROOT_USER is a common object.
    • A value of 'YES' in the INHERITED column tells us that user C##_CDB_ROOT_USER has been created by replication from the parent container CDB root.
    APP_ROOT>SELECT username, common, inherited
             FROM dba_users
             where common = 'YES' and oracle_maintained = 'N';
    USERNAME                      COMMON   INHERITED
    ---------------------------- -------- ------------
    C##_CDB_ROOT_USER             YES      YES

    - If we check the INHERITED column for user C##_CDB_ROOT_USER in CDB root, it shows a value of 'NO', indicating that the user has been created in CDB root itself.

    ORCLCDB$ROOT>SELECT username, common, inherited
             FROM dba_users
             where common = 'YES' and oracle_maintained = 'N';  

    USERNAME                      COMMON    INHERITED
    --------------------------- ---------- ------------
    C##_CDB_ROOT_USER             YES        NO

    - Let us create a common user (outside application action) non_app_user in the application root app_root and grant create session privilege to it in all the containers.

    APP_ROOT>create user non_app_user identified by oracle;
         GRANT CREATE SESSION TO non_app_user container = all;
    - Let us now create a common user sales_app_user1 as part of upgrading application sales_app
    APP_ROOT>ALTER PLUGGABLE DATABASE APPLICATION sales_app
             BEGIN UPGRADE '1.3' TO '1.4';
     
          CREATE USER sales_app_user1 IDENTIFIED BY oracle CONTAINER=ALL;
     
          GRANT CREATE SESSION, create procedure, CREATE TABLE, unlimited tablespace TO sales_app_user1;

    - We will now create another common user sales_app_user2 as part of upgrading application sales_app to verify that more than one common application user can be associated with an application.

    APP_ROOT>CREATE USER sales_app_user2 IDENTIFIED BY oracle CONTAINER=ALL;
     
          GRANT CREATE SESSION, create procedure, CREATE TABLE, unlimited tablespace TO sales_app_user2;
     
          ALTER PLUGGABLE DATABASE APPLICATION sales_app
          END UPGRADE TO '1.4';

    - Now, if we create another application hr_app in app_root and try to associate user sales_app_user1 with hr_app, it fails, thereby telling us that one common application user can be associated with one application only.

    APP_ROOT>ALTER PLUGGABLE DATABASE APPLICATION hr_app BEGIN INSTALL '1.0';
     
    APP_ROOT>CREATE USER sales_app_user1 IDENTIFIED BY oracle CONTAINER=ALL;
    CREATE USER sales_app_user1 IDENTIFIED BY oracle CONTAINER=ALL
               *
    ERROR at line 1:
    ORA-01920: user name 'SALES_APP_USER1' conflicts with another user or role name
     
    APP_ROOT>exec dbms_pdb.set_user_explicit ('SALES_APP_USER1');
    BEGIN dbms_pdb.set_user_explicit ('SALES_APP_USER1'); END;
     
    *
    ERROR at line 1:
    ORA-65317: cannot modify the user created by another application
    ORA-06512: at "SYS.DBMS_PDB", line 310
    ORA-06512: at line 1
    • Let us now create a common application user hr_app_user associated with application hr_app and end installation of application hr_app.
    APP_ROOT>CREATE USER hr_app_user IDENTIFIED BY oracle CONTAINER=ALL;
     
    APP_ROOT>ALTER PLUGGABLE DATABASE APPLICATION hr_app END INSTALL '1.0';
     
    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

    - Note that Common users (non_app_user, sales_app_user1, sales_app_user2 and hr_app_user) created in the application root app_root are visible in the application root app_root and a value of 'NO' in the INHERITED column indicates that these users have been created in app_root itself.

    APP_ROOT>SELECT username, common, inherited
             FROM dba_users
             where common = 'YES' and oracle_maintained = 'N';  

    USERNAME                     COMMON   INHERITED
    --------------------------- -------- ------------
    SALES_APP_USER2              YES      NO
    NON_APP_USER                 YES      NO
    SALES_APP_USER1              YES      NO
    HR_APP_USER                  YES      NO
    C##_CDB_ROOT_USER            YES      YES

    - Verify that Common users (non_app_user, sales_app _user1, sales_app_user2 and hr_app_user) created in application root app_root are not visible in CDB root or the regular PDB orcl. Consequently, they cannot access the entire CDB environment and are restricted to their own application container app_root

    ORCLCDB$ROOT>SELECT username, common, inherited
                 FROM dba_users
                 where common = 'YES' and oracle_maintained = 'N';
      
    USERNAME                     COMMON   INHERITED
    ---------------------------- ------- ------------
    C##_CDB_ROOT_USER             YES      NO
     
    ORCLCDB$ROOT>conn non_app_user/oracle@host01:1522/orclcdb
    ERROR:
    ORA-01017: invalid username/password; logon denied
     
    ORCLCDB$ROOT>conn sales_app_user1/oracle@host01:1522/orclcdb
    ERROR:
    ORA-01017: invalid username/password; logon denied
     
    ORCLCDB$ROOT>conn sales_app_user2/oracle@host01:1522/orclcdb
    ERROR:
    ORA-01017: invalid username/password; logon denied
    ORCL>SELECT username, common, inherited
                 FROM dba_users
                 where common = 'YES' and oracle_maintained = 'N';  
     
    USERNAME                      COMMON     INHERITED
    ---------------------------- ---------- ----------
    C##_CDB_ROOT_USER             YES       YES
     
    ORCL>conn non_app_user/oracle@host01:1522/orcl
    ERROR:
    ORA-01017: invalid username/password; logon denied
    ORCL>conn sales_app_user1/oracle@host01:1522/orcl
    ERROR:
    ORA-01017: invalid username/password; logon denied
     
    ORCL>conn sales_app_user2/oracle@host01:1522/orcl
    ERROR:
    ORA-01017: invalid username/password; logon denied

    - Verify that Common users (non_app_user, sales_app_user1, sales_app_user2 and hr_app_user) created in application root app_root are visible in application PDB app_pdb1 after it is synchronized with the applications sales_app and hr_app. A value of 'YES' in the INHERITED column indicates that these users have not been created in app_pdb1 and have been inherited from another container higher up in the hierarchy.

    APP_PDB1>alter pluggable database application all sync;
    Pluggable database altered.
     
    APP_PDB1>SELECT username, common, inherited
             FROM dba_users
             where common = 'YES' and oracle_maintained = 'N';  
     
    USERNAME            COMMON     INHERITED
    ------------------ ---------- ------------
    SALES_APP_USER2     YES       YES
    NON_APP_USER        YES       YES
    SALES_APP_USER1     YES       YES
    HR_APP_USER         YES       YES
    C##_CDB_ROOT_USER   YES       YES
     
    5 rows selected.

    - Note that we issued a SQL statement to grant CREATE SESSION privilege to C##_CDB_ROOT_USER, non_app_user, sales_app_user1 and sales_app_user2 in all the containers. However, the CREATE SESSION privilege has been granted to non_app_user in app_root only and not in app_pdb1 because it has been created outside application action. On the other hand, CREATE SESSION privilege has been granted to C##_CDB_ROOT_USER, sales_app_user1 and sales_app_user2 in app_root as well as app_pdb1 . Consequently, whereas C##_CDB_ROOT_USER, sales_app_user1 and sales_app_user2 can connect to app_pdb1, non_app_user cannot connect to app_pdb1.

    APP_ROOT>select GRANTEE, PRIVILEGE, con.name
          from cdb_sys_privs priv, v$containers con
          where priv.con_id = con.con_id
             and priv.privilege = 'CREATE SESSION'
             and priv.grantee in ('C##_CDB_ROOT_USER', 'NON_APP_USER', 'SALES_APP_USER1', 'SALES_APP_USER2' );  
     
    GRANTEE             PRIVILEGE                     NAME
    ------------------ ------------------------------ ----------
    SALES_APP_USER1     CREATE SESSION                APP_PDB1
    SALES_APP_USER2     CREATE SESSION                APP_PDB1
    C##_CDB_ROOT_USER   CREATE SESSION                APP_PDB1
    SALES_APP_USER1     CREATE SESSION                APP_ROOT
    SALES_APP_USER2     CREATE SESSION                APP_ROOT
    C##_CDB_ROOT_USER   CREATE SESSION                APP_ROOT
    NON_APP_USER      CREATE SESSION              APP_ROOT
     
    7 rows selected.
     
    APP_PDB1>select GRANTEE, PRIVILEGE
          from dba_sys_privs
          where privilege = 'CREATE SESSION'
         and  grantee in ('C##_CDB_ROOT_USER', 'NON_APP_USER', 'SALES_APP_USER1', 'SALES_APP_USER2' );  
     
    GRANTEE             PRIVILEGE
    -------------------- --------------------
    SALES_APP_USER2     CREATE SESSION
    SALES_APP_USER1     CREATE SESSION
    C##_CDB_ROOT_USER   CREATE SESSION
     
    APP_PDB1>conn C##_CDB_ROOT_USER/oracle@host01:1522/app_pdb1
    Connected.
     
    APP_PDB1>conn sales_app_user1/oracle@host01:1522/app_pdb1
    Connected.
     
    APP_PDB1>conn sales_app_user2/oracle@host01:1522/app_pdb1
    Connected.
     
    APP_PDB1>conn non_app_user/oracle@host01:1522/app_pdb1
    ERROR:
    ORA-01045: user NON_APP_USER lacks CREATE SESSION privilege; logon denied

    Warning: You are no longer connected to ORACLE.

    Hence, it can be concluded that:

    -      An application common user created in the application root outside application action cannot connect to any application PDB in that container.

    -      CDB common users and application common users created inside application action(s) can access application PDBs.

    • Let us create local user app_pdb1_user in application PDB app_pdb1 and grant CREATE SESSION privilege to it.
    APP_PDB1>CREATE USER app_pdb1_user IDENTIFIED BY oracle;
             Grant create session to app_pdb1_user;

    - Note that COMMON = NO and INHERITED = NO for app_pdb1_user because it has been created locally in an application PDB.

    APP_PDB1>SELECT username, common, inherited
             FROM dba_users
             where oracle_maintained = 'N' order by 1,2,3;  
    USERNAME             COMMON     INHERITED
    -------------------- -------- ------------
    APP_PDB1_USER       NO         NO
    C##_CDB_ROOT_USER   YES       YES
    HR_APP_USER         YES        YES
    NON_APP_USER        YES        YES
    PDB_ADMIN           NO         NO
    SALES_APP_USER1     YES        YES
    SALES_APP_USER2     YES        YES
     
    7 rows selected.

    - Verify that app_pdb1_user can connect to app_pdb1 as he has been granted CREATE SESSION privilege.

    APP_PDB1>select GRANTEE, PRIVILEGE
          from dba_sys_privs
          where privilege = 'CREATE SESSION'
             and grantee = 'APP_PDB1_USER';
     
    GRANTEE             PRIVILEGE
    ------------------ --------------------
    APP_PDB1_USER       CREATE SESSION
    APP_PDB1>conn app_pdb1_user/oracle@host01:1522/app_pdb1
    Connected.

    - Verify that app_pdb1_user is not listed in app_root / CDB root and cannot connect to app_root / CDB root.

    ORCLCDB$ROOT>SELECT username, common, inherited
             FROM dba_users where username = 'APP_PDB1_USER';
     
    no rows selected
     
    ORCLCDB$ROOT>conn app_pdb1_user/oracle@host01:1522/orclcdb
    ERROR:
    ORA-01017: invalid username/password; logon denied
     
    APP_ROOT>SELECT username, common, inherited
             FROM dba_users where username = 'APP_PDB1_USER';
    no rows selected
     
    APP_ROOT>conn app_pdb1_user/oracle@host01:1522/app_root
    ERROR:
    ORA-01017: invalid username/password; logon denied

    - Let us create another application PDB app_pdb2 in app_root.

    APP_ROOT>ho mkdir -p /u02/app/oracle/oradata/orclcdb/app_root/app_pdb2
             alter session set db_create_file_dest = '/u02/app/oracle/oradata/orclcdb/app_root/app_pdb2/';
             CREATE PLUGGABLE DATABASE app_pdb2
             ADMIN USER pdb_admin IDENTIFIED BY oracle;
            alter pluggable database app_pdb2 open;
              
    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

    - Verify that app_pdb1_user is not listed in app_pdb2 and cannot connect to app_pdb2.

    APP_PDB2>alter pluggable database application all sync;
     
    Pluggable database altered.
     
    APP_PDB2>SELECT username, common, inherited
             FROM dba_users where oracle_maintained = 'N';
     
    USERNAME                      COMMON   INHERITED
    ---------------------------- ---------- ----------
    SALES_APP_USER2               YES       YES
    NON_APP_USER                  YES       YES
    PDB_ADMIN                     NO        NO
    SALES_APP_USER1               YES       YES
    HR_APP_USER                   YES       YES
    C##_CDB_ROOT_USER             YES       YES
    6 rows selected.
     
    APP_PDB2>select GRANTEE, PRIVILEGE
             from dba_sys_privs
             where privilege = 'CREATE SESSION'
             and grantee in ( 'SALES_APP_USER1', 'SALES_APP_USER2', 'C##_CDB_ROOT_USER', 'APP_PDB1_USER');
     
    GRANTEE             PRIVILEGE
    -------------------- --------------
    SALES_APP_USER2      CREATE SESSION
    SALES_APP_USER1      CREATE SESSION
    C##_CDB_ROOT_USER    CREATE SESSION
    3 rows selected.
     
    APP_PDB2>conn app_pdb1_user/oracle@host01:1522/app_pdb2
    ERROR:
    ORA-01017: invalid username/password; logon denied
    APP_PDB2>conn sales_app_user1/oracle@host01:1522/app_pdb2
    Connected.
     
    APP_PDB2>conn sales_app_user2/oracle@host01:1522/app_pdb2
    Connected.

    Hence, a local user in an application PDB exists only in that PDB and has administrative privileges only in that PDB. It cannot access any other application PDB within the same container.

     

    Summary

    • Multiple common application users can be associated with one application.
    • One common application user can be associated with one application only.
    • Common users created in application root cannot access the entire CDB environment and are restricted to their own application container.
    • An application common user created in the application root outside application action cannot connect to any application PDB in that container.
    • A local user in an application PDB exists only in that PDB and has administrative privileges only in that PDB. It cannot access any other application PDB within the same container.

    References:

    https://docs.oracle.com/en/database/oracle/oracle-database/12.2/cncpt/overview-of-the-multitenant-architecture.html#GUID-19337B94-645B-45E8-842C-762BBC544B13

    https://docs.oracle.com/en/database/oracle/oracle-database/12.2/dbseg/managing-security-for-oracle-database-users.html#GUID-BBBD9904-F2F3-442B-9AFC-8ACDD9A588D8

    https://docs.oracle.com/en/database/oracle/oracle-database/12.2/refrn/COMMON_USER_PREFIX.html#GUID-516ADCCF-3661-4B54-908A-7041854EA14F

    https://docs.oracle.com/en/database/oracle/oracle-database/12.2/cncpt/overview-of-the-multitenant-architecture.html#GUID-3BB161DA-9CC0-4D61-A2C1-5D3662E0DECF

    https://docs.oracle.com/en/database/oracle/oracle-database/12.2/dbseg/managing-security-for-oracle-database-users.html#GUID-F78AE647-184C-4790-9E95-493787CACDE1

    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/