How to create a common and a local user in a 12c multitenant database

In a multitenant container database, there are two types of users:

* Common users, who are known in both the root containers and in all the pluggable database containers
* Local users, who are only known in a single pluggable database container

Common users can, if granted the necessary privileges, perform administrative tasks across all the PDBs in multitenant database.
They can also perform tasks specific to the container database, also called the ROOT container.

To create a common user, make sure you are connected to the ROOT container

On my client, my tnsnames.ora entry looks as follows:

# container database
CDB =
   (DESCRIPTION =
     (ADDRESS = (PROTOCOL = TCP)(HOST = myhost.mydomain.no)(PORT = 1531))
     (CONNECT_DATA =
       (SERVER = DEDICATED)
       (SERVICE_NAME = "container#db01")
     )
   )

Since my client is a windows pc, I start sqlplus from the start menu, and connect as system@cdb and enter the password.
My default container will be CDB$ROOT, which is what you need to create a common user:

show con_name

CON_NAME
------------------------------
CDB$ROOT

Create the common user:

create user C##DBAMASTER
identified by ****
default tablespace USERS
temporary tablespace TEMP
quota unlimited on USERS
container=all;

SQL> user C##DBAMASTER created.

Note the new rules for creating common users in a CDB database:
In Oracle Database 12c Release 1 (12.1.0.1), the name of a common user must begin with C## or c##

Verify that the common user was created:

select USERNAME,CREATED,LAST_LOGIN,COMMON
from dba_USERS
where trunc(created) = trunc(sysdate)

USERNAME             CREATED   LAST_LOGIN                               COM
-------------------- --------- ---------------------------------------- ---
C##DBAMASTER         18-DEC-16 18-DEC-16 03.16.16.000000000 PM +01:00   YES

Give the common user the right to create a session, to switch between all containers in the CDB and the right to create and drop users in all the containers in the CDB:

grant connect to C##DBAMASTER;
grant set container to C##DBAMASTER container=all;
grant create user to C##DBAMASTER container=all;
grant drop user to C##DBAMASTER container=all;

SQL> Grant succeeded.

Connect to ROOT container as the new common user:

connect C##DBAMASTER/***@cdb
Connected.
show user
USER is "C##DBAMASTER"

Switch container:

alter session set container=vpdb01;

SQL> Session altered.

Create a new local user in the current container:

create user "1Z0061"
identified by ****
default tablespace users
quota unlimited on users
temporary tablespace TEMP
container=current;

SQL> User created.

Note that without the "container=all" privilege, the new common user C##DBAMASTER cannot connect directly to the vpdb01 pluggable database

Here is what happened:

SQL> connect C##DBAMASTER/****@vpdb01
ERROR:
ORA-01045: user C##DBAMASTER lacks CREATE SESSION privilege; logon denied


Warning: You are no longer connected to ORACLE.
SQL> connect system/*****@vpdb01
Connected.
SQL> show con_name

CON_NAME
------------------------------
VPDB01
SQL> grant create session to C##DBAMASTER container=current;

Grant succeeded.

SQL> connect C##DBAMASTER/****@vpdb01
Connected.
SQL> show con_name

CON_NAME
------------------------------
VPDB01

This means that these two privileges are distinctly different:

grant create session to C##DBAADMIN container=all;

vs

grant set container to C#DBAADMIN container=all;