As we learnt in my previous article in this series, 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:
– 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. 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 for 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
Common user names
A user created common user can be distinguished from a local user from within a PDB by querying the data dictionary :
SQL>Select username, common from dba_users where common='YES';
Another convenient method offered by Oracle is just looking at the name of the user. Whereas names for user-created common users must begin with a common user prefix specified by the initialization parameter COMMON_USER_PREFIX, local user names cannot start with common user prefix. The COMMON_USER_PREFIX parameter in CDB$ROOT defines the common user prefix for CDB common users and has a default value of C##. This prefix is reserved for CDB common users and cannot be used to start the name of a user created in any container other than CDB$root. With the introduction of Oracle multitenant application containers in Oracle Database 12.2.0.1, the COMMON_USER_PREFIX parameter in an Application Root defines the common user prefix for application common users in that container and is, by default, an empty string. As a result, by default, there are no restrictions on the name that can be assigned to an application common user, other than that it cannot start with the prefix reserved for CDB common users. So if you are using the CDB default then any user marked COMMON in the DBA_USERS view that does NOT begin with C## would be an application common user.
However, COMMON_USER_PREFIX can be set in an Application Root to a non-null value, in which case within that application container
– The name of an application common user should start with that prefix
– A local user created in an application PDB cannot start with that prefix.
Moreover, local user names in an application PDB cannot start with the common user prefix for CDB common users as well.
Now, I will demonstrate the use of COMMON_USER_PREFIX with multitenant application containers.
Current scenario
Here, we have an Oracle Database 12.2.0.1 CDB called orclcdb, having CDB Seed PDB pdb$seed and a regular PDB orcl .
Demonstration
- Let us first connect to CDB orclcdb and verify that it has
– CDB Seed PDB pdb$seed
– one regular PDB, orclpdb
ORCLCDB$ROOT>@get_app_containers
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
- Let us create an application container app_root and open it.
ORCLCDB$ROOT>ho mkdir -p /u02/app/oracle/oradata/orclcdb/app_root/
alter session set db_create_file_dest = '/u02/app/oracle/oradata/orclcdb/app_root/';
CREATE PLUGGABLE DATABASE app_root AS APPLICATION CONTAINER
ADMIN USER AppAdmin IDENTIFIED BY oracle;
alter pluggable database app_root open;
ORCLCDB$ROOT>@get_app_containers
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 MOUNTED NO NO NO
6 APP_ROOT READ WRITE YES NO NO
- Verify that parameter COMMON_USER_PREFIX is at its default value of C## in CDB$ROOT . Create a CDB common user c##cdb_root_user and grant privileges to it.
ORCLCDB$ROOT>sho parameter common_user_prefix
NAME TYPE VALUE
------------------------------------ ----------- -----------------------------
common_user_prefix string C##
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;
ORCLCDB$ROOT> select username, common from dba_users
where username = 'C##_CDB_ROOT_USER';
USERNAME COM
------------------------------ ---
C##_CDB_ROOT_USER YES
- Verify that, if we try to create a common user c##_app_root_user in application container app_root, it fails with ORA-65096, as the name of a common application user cannot start with the prefix reserved for CDB common users.
APP_ROOT>create user c##_app_root_user identified by oracle;
create user c##_app_root_user identified by oracle
*
ERROR at line 1:
ORA-65096: invalid common user or role name
APP_ROOT>ho oerr ora 65096
65096, 00000, "invalid common user or role name"
// *Cause: An attempt was made to create a common user or role with a name
// that was not valid for common users or roles. In addition to the
// usual rules for user and role names, common user and role names
// must consist only of ASCII characters, and must contain the prefix
// specified in common_user_prefix parameter.
// *Action: Specify a valid common user or role name.
//
- In application container app_root, verify that parameter COMMON_USER_PREFIX is at its default value of NULL. Create a common application user app_root_user and grant privileges to it.
APP_ROOT>sho parameter common_user_prefix
NAME TYPE VALUE
------------------------------------ ----------- -----------------------------
common_user_prefix string
APP_ROOT>create user app_root_user identified by oracle;
User created.
APP_ROOT>GRANT CREATE SESSION, create procedure, CREATE TABLE,
unlimited tablespace
TO app_root_user container = all;
Grant succeeded.
- While connected to application container app_root, note that, in the data dictionary,
- CDB common User C##_CDB_ROOT_USER has been replicated in the application root
- INHERITED = YES for user C##_CDB_ROOT_USER, which indicates that this user definition was inherited from another container (CDB$ROOT)
- INHERITED = NO for user APP_ROOT_USER, meaning that this user has been created in the current container only
APP_ROOT> select username, common, inherited from dba_users where username like '%ROOT_USER%';
USERNAME COMMON INHERITED
—————————— ———- ————
C##_CDB_ROOT_USER YES YES
APP_ROOT_USER YES NO
- Create an application Sales_app in application container app_root
APP_ROOT>ALTER PLUGGABLE DATABASE APPLICATION sales_app BEGIN INSTALL ‘1.0’;
— Create common application user sales_app_user for application sales_app and grant privileges to it.
APP_ROOT> CREATE USER sales_app_user IDENTIFIED BY oracle CONTAINER=ALL;
GRANT CREATE SESSION, create procedure, CREATE TABLE,
unlimited tablespace
TO sales_app_user;
— Create application table customers with sharing = metadata
APP_ROOT> drop table sales_app_user.customers purge;
CREATE TABLE sales_app_user.customers SHARING=METADATA
( cust_id NUMBER not null,
cust_name varchar2(30),
cust_add varchar2(30),
cust_zip NUMBER
);
APP_ROOT> ALTER PLUGGABLE DATABASE APPLICATION sales_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.0 21 NORMAL N
- Note that sales_app_user is a COMMON user, as it was created in the application root app_root as part of the application install. Also INHERITED = ‘NO’, as it has been created in app_root itself and not inherited from any other container.
APP_ROOT>select username, common, inherited from dba_users where oracle_maintained = 'N';
USERNAME COMMON INHERITED
------------------------------ ---------- ------------
APPADMIN NO NO
SALES_APP_USER YES NO
C##_CDB_ROOT_USER YES YES
APP_ROOT_USER YES NO
- In the application container app_root, create and open an application PDB app_pdb1.
APP_ROOT>ho mkdir -p /u02/app/oracle/oradata/orclcdb/app_root/app_pdb1
APP_ROOT>alter session set db_create_file_dest =
'/u02/app/oracle/oradata/orclcdb/app_root/app_pdb1/';
APP_ROOT>CREATE PLUGGABLE DATABASE app_pdb1
ADMIN USER pdb_admin IDENTIFIED BY oracle;
APP_ROOT>alter pluggable database app_pdb1 open;
APP_ROOT>@get_app_containers
CON_ID NAME OPEN_MODE APP_ROOT APP_PDB APP_SEED
---------- -------------------- ---------- -------- -------- --------
4 APP_PDB1 READ WRITE NO YES NO
6 APP_ROOT READ WRITE YES NO NO
- Create a local application user app_pdb1_user in the application PDB app_pdb1.
APP_PDB1>CREATE USER app_pdb1_user IDENTIFIED BY oracle ;
APP_PDB1>GRANT CREATE SESSION, create procedure, CREATE TABLE,
unlimited tablespace
TO app_pdb1_user;
- Verify that
- Common / local users belonging to application container app_root, i.e., app_root_user,sales_app_user andapp_pdb1_user, are not visible in CDB root.
- The CDB common user C##_CDB_ROOT_USER has been inherited from CDB root (CDB$ROOT) in the application root app_root and application PDB app_pdb1 (INHERITED = YES). As a result, the CDB common user C##_CDB_ROOT_USER is visible in CDB root CDB$ROOT, application root app_root and application PDB app_pdb1.
- Currently common application users app_root_user and sales_app_user are not visible in the application PDB app_pdb1, as it has not been synced with the application sales_app.
- Local user app_pdb1_user is visible only within the application PDB app_pdb1. It is not visible in CDB root CDB$ROOT or application root app_root.
ORCLCDB$ROOT>select con.name con_name, cu.username, cu.common, cu.inherited
from cdb_users cu , v$containers con
where cu.oracle_maintained = 'N' and cu.con_id = con.con_id;
CON_NAME USERNAME COMMON INHERITED
-------------------- ------------------------------ ---------- ------------
CDB$ROOT C##_CDB_ROOT_USER YES NO
APP_PDB1 PDB_ADMIN NO NO
APP_PDB1 APP_PDB1_USER NO NO
APP_PDB1 C##_CDB_ROOT_USER YES YES
APP_ROOT APPADMIN NO NO
APP_ROOT SALES_APP_USER YES NO
APP_ROOT C##_CDB_ROOT_USER YES YES
APP_ROOT APP_ROOT_USER YES NO
8 rows selected.
- Sync application PDB app_pdb1 with all the applications
APP_PDB1>alter pluggable database application all sync;
Pluggable database altered.
- As soon as the application PDB app_pdb1 is synced, the common application users app_root_user and sales_app_user created in app_root become visible in it. Both these users are application common users inherited from the container app_root
APP_PDB1>select username, common, inherited from dba_users
where oracle_maintained = 'N';
USERNAME COMMON INHERITED
-------------------- ---------- ------------
PDB_ADMIN NO NO
SALES_APP_USER YES YES
APP_PDB1_USER NO NO
C##_CDB_ROOT_USER YES YES
APP_ROOT_USER YES YES
5 rows selected.
It can be seen that within application PDB app_pdb1, we can identify that C##_CDB_ROOT_USER is a CDB common user just by looking at its name, as it is prefixed with C##. However, it is not possible to distinguish common application users from local application users without querying the data dictionary, as COMMON_USER_PREFIX is set to NULL for the application container app_root.
So, we will set the initialization parameter to a non-NULL value for application container app_root.
- First, let us verify that if we try to set COMMON_USER_PREFIX in app_root to ‘C##”, i.e., the same value as in CDB root, we get an error
APP_ROOT>alter system set common_user_prefix = 'C##' scope = spfile;
alter system set common_user_prefix = 'C##' scope = spfile
*
ERROR at line 1:
ORA-32017: failure in updating SPFILE
ORA-65300: PDB common_user_prefix conflicts with common_user_prefix in CDB.
APP_ROOT>ho oerr ora 65300
65300, 00000, "PDB common_user_prefix conflicts with common_user_prefix in CDB."
// *Cause: An attempt was made to set the 'common_user_prefix' parameter in
// a pluggable database (PDB) that conflicts with the value of the
// same parameter in the multitenant container database (CDB.)
// *Action: Choose a different 'common_user_prefix' for PDB.
//
- Let us now set the COMMON_USER_PREFIX in app_root to ‘A1#’.
APP_ROOT>alter system set common_user_prefix = 'A1##' scope = spfile;
System altered.
ORCLCDB$ROOT>alter pluggable database app_root close immediate;
Pluggable database altered.
ORCLCDB$ROOT>alter pluggable database app_root open;
APP_ROOT>sho parameter common
NAME TYPE VALUE
------------------------------------ ----------- -----------------------------
common_user_prefix string A1##
- Drop the common user app_root_user created earlier in app_root
APP_ROOT>drop user app_root_user cascade;
- Verify that it is not possible to create a common user in the application root without any prefix, as COMMON_USER_PREFIX has been set to A1## for app_root.
APP_ROOT>CREATE USER app_root_user IDENTIFIED BY oracle ;
CREATE USER app_root_user IDENTIFIED BY oracle
*
ERROR at line 1:
ORA-65096: invalid common user or role name
APP_ROOT>ho oerr ora 65096
65096, 00000, "invalid common user or role name"
// *Cause: An attempt was made to create a common user or role with a name
// that was not valid for common users or roles. In addition to the
// usual rules for user and role names, common user and role names
// must consist only of ASCII characters, and must contain the prefix
// specified in common_user_prefix parameter.
// *Action: Specify a valid common user or role name.]
- Verify that it is not possible to create a common user in application root app_root with the prefix reserved for CDB root (C##)
APP_ROOT>CREATE USER c##_app_root_user IDENTIFIED BY oracle ;
CREATE USER c##_app_root_userIDENTIFIED BY oracle
*
ERROR at line 1:
ORA-65096: invalid common user or role name
- Finally, we will create a common user A1##_app_root_user in the application root having the prefix defined for app_root (A1##), which succeeds as expected.
APP_ROOT>CREATE USER A1##_app_root_user IDENTIFIED BY oracle ;
User created.
- Upgrade application sales_app and create another common application user from within the application
APP_ROOT>alter pluggable database application sales_app begin upgrade '1.1' TO '1.2';
— Drop the common user sales_app_user created earlier
APP_ROOT>drop user sales_app_user cascade;
— Try to create a common user sales_app_user1 in application without any prefix – fails
APP_ROOT>CREATE USER sales_app_user1 IDENTIFIED BY oracle ;
CREATE USER sales_app_user1 IDENTIFIED BY oracle
*
ERROR at line 1:
ORA-65096: invalid common user or role name
— Try to create a common user c##_sales_app_user in application with prefix for CDB root (c##) – fails
APP_ROOT>CREATE USER c##_sales_app_user IDENTIFIED BY oracle ;
CREATE USER c##_sales_app_user IDENTIFIED BY oracle
*
ERROR at line 1:
ORA-65096: invalid common user or role name
— Try to create a common user A1##_sales_app_user in application root having prefix for application root app_root (A1##) – succeeds
APP_ROOT>CREATE USER A1##_sales_app_user IDENTIFIED BY oracle ;
APP_ROOT>alter pluggable database application sales_app end upgrade TO '1.2';
- Verify that in application PDB app_pdb1, it is not possible to create a local user having the prefix reserved for CDB common users (C##) or application common users (A1##)
APP_PDB1>alter pluggable database open;
Pluggable database altered.
— Drop the local user app_pdb1_user created earlier
APP_PDB1>drop user app_pdb1_user cascade;
User dropped.
— Try to create a local user in application pdb app_pdb1 with prefix reserved for CDB root (C##) – fails
APP_PDB1>CREATE USER c##_app_pdb1_user IDENTIFIED BY oracle ;
CREATE USER c##_app_pdb1_user IDENTIFIED BY oracle
*
ERROR at line 1:
ORA-65094: invalid local user or role name
— Try to create a local user in application pdb app_pdb1 with prefix for reserved for application root app_root (A1##) – fails
APP_PDB1>CREATE USER A1##_app_pdb1_user IDENTIFIED BY oracle ;
CREATE USER A1##_app_pdb1_user IDENTIFIED BY oracle
*
ERROR at line 1:
ORA-65094: invalid local user or role name
— Try to create a common user app_pdb1_user1 in application PDB app_pdb1 without any prefix – succeeds
APP_PDB1>CREATE USER app_pdb1_user1 IDENTIFIED BY oracle ;
User created.
- Synchronize application PDB app_pdb1with the application and note that now it is easy to identify that
- User C##_CDB_ROOT_USER with the name starting with C## (COMMON_USER_PREFIX for CDB root) is a common CDB user inherited from CDB root.
- Users A1##_APP_ROOT_USER and A1##_SALES_APP_USER with names starting with A1## (COMMON_USER_PREFIX for application root app_root) are common application users inherited from application root app_root.
- Users APP_PDB1_USER1 and PDB_ADMIN with names without any of the above prefixes are local application PDB users
APP_PDB1>alter pluggable database application all sync;
Pluggable database altered.
APP_PDB1>select username, common, inherited from dba_users
where oracle_maintained = 'N' order by username;
USERNAME COMMON INHERITED
-------------------- ---------- ------------
A1##_APP_ROOT_USER YES YES
A1##_SALES_APP_USER YES YES
APP_PDB1_USER1 NO NO
C##_CDB_ROOT_USER YES YES
PDB_ADMIN NO NO
Hence, COMMON_USER_PREFIX may be set for an application container to a non-NULL value for easy distinction between local and common application users.
Summary
- With the introduction of Oracle multitenant application containers in Oracle Database 12.2.0.1, there can be two subtypes of user-created common users:
- Application Common User
- CDB Common User
- A user-created common user can be distinguished from a local user from within a PDB by
- Querying the data dictionary
- Looking at the name of the user, if the initialization parameter COMMON_USER_PREFIX has been set to a non-NULL value
- Names for user-created common users must begin with a common user prefix specified by the parameter COMMON_USER_PREFIX.
- Local user names cannot start with the common user prefix.
- The COMMON_USER_PREFIX parameter in CDB$ROOT defines the common user prefix for CDB common users and has a default value of C##. This prefix is reserved for CDB common users and cannot be used to start the name of a user created in any container other than CDB$root.
- From Oracle Database 12.2.0.1 onwards, the COMMON_USER_PREFIX parameter in an Application Root defines the common user prefix for application common users in that container and is, by default, an empty string.
- COMMON_USER_PREFIX may be set for an application container to a non-NULL value for easy distinction between local and common application users within that application container in which case
– The name of an application common user should start with that prefix
– A local user created in an application PDB cannot start with that prefix.
– Local user names in an application PDB cannot start with common user prefix for CDB common users as well.
Start the discussion at forums.toadworld.com