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 SESSIONprivilege 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_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.
Start the discussion at forums.toadworld.com