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_ROOTAPP_PDBAPP_SEED APP_ROOT_CLONE
------- ---------- ---------- -------- -------- -------- ------------------
      1 CDB$ROOT   READ WRITE      NO    NO     NO    NO
      2 PDB$SEED   READ ONLY       NO    NO     NO     NO
      3ORCL       READ WRITE      NO    NO     NO     NO
      4 APP_PDB1   READ WRITE      NO    YES   NO     NO
      6APP_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_userhas been replicated in application root APP_ROOT, regular PDB orcl, and application PDB app_pdb1and 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_USERin 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 applicationhr_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 inapp_root as well asapp_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_usercan connect to app_pdb1as 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

About the Author

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 passionate about learning and has keen interest in RAC and Performance Tuning. You can learn all about Anju’s credentials and read more from her via her technical blog site at http://oracleinaction.com/

Start the discussion at forums.toadworld.com