* 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;
