Oracle Multitenant Application Containers – Part XII Application Containers and COMMON_USER_PREFIX

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 and app_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_user IDENTIFIED 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.