Pluggable Databases - Expert Consolidation in Oracle Database 12c - page 256

Creating common users
To create a common user you need to be connected to the root container, or CDB$ROOT as a common user with
appropriate privileges. A new addition to the “create user” and “grant” command, called “container” allows the
creation of common users. Common users must be prefixed with c## or otherwise you will receive an error. Take this
example for user c##sysbackup:

SYS@CDB$ROOT> create user c##sysbackup identified by secret
2 container = all;

User created.

SYS@CDB$ROOT> grant sysbackup to c##sysbackup;

Grant succeeded.

The “container“-clause seems optional, since all users created in the root are common users by default. Using it
makes the purpose more visible though, especially in automation where someone reading your code can immediately
grasp the intention. Thankfully Oracle prevents the creation of non-common users in the root container:

SYS@CDB$ROOT> create user localuser identified by secret container = current;
create user localuser identified by secret container = current
*
ERROR at line 1:
ORA-65049: creation of local user or role is not allowed in CDB$ROOT

The newly created user exists in all attached PDBs, and could theoretically connect to all of them, if they have the
appropriate permissions. There is no further change to the way that a database user is created in Oracle 12.1. You still
assign quotas to users, profiles, default permanent, and temporary tablespaces. Note that the tablespaces must exist in
the PDBs or you will get an error similar to this one:

SYS@CDB$ROOT> alter user c##donexistinnbcd default tablespace users;
alter user c##donexistinnbcd default tablespace users
*
ERROR at line 1:
ORA-65048: error encountered when processing the current DDL statement in
pluggable database PDB4
ORA-00959: tablespace 'USERS' does not exist

■ user profiles, auditing, and security considerations are not in the scope of this chapter. You need to ensure that
the relevant standards your information security team comes up with are implemented and enforced.

Note

What was true in Oracle before 12.1 is true for the current release: you cannot simply connect to a container
without the “create session” privilege. This is true for the container the user was created in, and any additional
containers. The only exception is if a user is granted the very powerful “set container” system privilege. Due to the way
common users are organized, there cannot be more than one user with the same name in the database. In this respect
common users behave exactly like users in a non-CDB.