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.


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