Create Common Users in 12c container database

********************************** Create Common Users**********************************

When creating a common user the following requirements must all be met.

You must be connected to a common user with the CREATE USER privilege.
The current container must be the root container.
The username for the common user must be prefixed with "C##" or "c##" and contain only ASCII or EBCDIC characters.
The username must be unique across all containers.
The DEFAULT TABLESPACE, TEMPORARY TABLESPACE, QUOTA and PROFILE must all reference objects that exist in all containers.
You can either specify the CONTAINER=ALL clause, or omit it, as this is the default setting when the current container is the root.
The following example shows how to create common users with and without the CONTAINER clause from the root container.

oracle@Linux03 echo $ORACLE_SID
ORCL

CONN / AS SYSDBA

SQL> show con_name
con_name CDB$ROOT

Lets query some local users by filtering con_id >2. ususally CON_ID greater that 2 is user created PDB's.

SQL> set echo on
SQL> show con_name
con_name CDB$ROOT
SQL> column USERNAME format a40
SQL> column CON_ID format 99
SQL> select username,CON_ID,common from CDB_USERS where CON_ID >2 and common='NO';

USERNAME                                 CON_ID COM
---------------------------------------- ------ ---
PDBORCL1_USR2                                 5 NO
PDBORCL1                                      5 NO
PDBORCL1_USR1                                 5 NO
PDBORCL1_USR2                                 4 NO
PDBORCL2                                      4 NO
IX                                            3 NO
SH                                            3 NO
PDBADMIN                                      3 NO
BI                                            3 NO
OE                                            3 NO
SCOTT                                         3 NO
HR                                            3 NO
PM                                            3 NO

13 rows selected

******************************************************
Now I want to create user C##CDBADMIN1
******************************************************

SQL> select username,CON_ID,common from CDB_USERS where username like '%CDB%';

no rows selected


-- Create the common user using the CONTAINER clause.
CREATE USER C##CDBADMIN1 IDENTIFIED BY oracle CONTAINER=ALL;
GRANT CREATE SESSION TO C##CDBADMIN1 CONTAINER=ALL;

SQL> select username,CON_ID,common from CDB_USERS where username like '%CDB%';

USERNAME CON_ID COM
---------------------------------------- ------ ---
C##CDBADMIN1      3 YES
C##CDBADMIN1      5 YES
C##CDBADMIN1      1 YES
C##CDBADMIN1      4 YES

******************************************************
-- Create the common user using the default CONTAINER setting.
******************************************************

SQL> CREATE USER C##CDBADMIN1 IDENTIFIED BY oracle;

User created.

SQL> select username,CON_ID,common from CDB_USERS where username like '%CDB%';

USERNAME CON_ID COM
---------------------------------------- ------ ---
C##CDBADMIN1      1 YES
C##CDBADMIN1      5 YES
C##CDBADMIN1      3 YES
C##CDBADMIN1      4 YES

SQL> alter session set container=pdborcl1;

Session altered.

***************************************************************
We can grant permission only on current pdb to common user.
****************************************************************

SQL> grant create session TO C##CDBADMIN1 CONTAINER=CURRENT;

Grant succeeded.

**********************************
Lets test connecting to current pdb:
**********************************
SQL> conn C##CDBADMIN1/oracle@pdborcl1
Connected.
SQL> show user
USER is "C##CDBADMIN1"
SQL> show con_name

CON_NAME
------------------------------
PDBORCL1

*************************************
Lets test connecting to other pdb now:
**************************************

SQL> conn C##CDBADMIN1/oracle@pdborcl2
ERROR:
ORA-01045: user C##CDBADMIN1 lacks CREATE SESSION privilege; logon denied

Warning: You are no longer connected to ORACLE.


*****************************************************************
YOU can revoke the grants on CDBADMIN11 using REVOKE cmd:
*****************************************************************

SQL> revoke CREATE SESSION from C##CDBADMIN1 CONTAINER=ALL;

Revoke succeeded.

SQL> select username,CON_ID,common from CDB_USERS where username like '%CDB%';

USERNAME CON_ID COM
---------------------------------------- ------ ---
C##CDBADMIN1      3 YES
C##CDBADMIN1      1 YES
C##CDBADMIN1      5 YES
C##CDBADMIN1      4 YES

*******************************************************
You can drop COMMON USER as below connecting to sys:
*******************************************************

SQL> drop user C##CDBADMIN1 ;

User dropped.

SQL> select username,CON_ID,common from CDB_USERS where username like '%CDB%';

no rows selected