Jun 24, 2018 11:47:06 AM by Anju Garg
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
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.
Here, we have an Oracle Database 12.2.0.1 CDB called orclcdb, having CDB Seed PDB pdb$seed and a regular PDB orcl .
- 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
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
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
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.
//
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.
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
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
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
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
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;
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.
APP_PDB1>alter pluggable database application all sync;
Pluggable database altered.
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.
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.
//
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##
APP_ROOT>drop user app_root_user cascade;
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.]
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>CREATE USER A1##_app_root_user IDENTIFIED BY oracle ;
User created.
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';
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.
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.
– 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.
Tags: Oracle
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 :
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/
We use cookies to improve your experience with our site. By continuing to use this site, you consent to our use of cookies. Learn more.