Oracle 12c Local & Common Privileges

              Oracle 12c introduced the concept of common users and local users which could help us in a multitude of ways. However, we need to understand some features while dealing with them.

 Privileges granted Locally & Commonly.

When we create a common user, we know that we need to suffix CONTAINER=ALL with the command.  We should use this suffix if we want to grant a privilege applicable in all containers. If we omit CONTAINER=ALL  while granting privileges, the grant will be valid only for the local container.

Here is an example.

I run the below query  when connected to CDB$ROOT container as  sys 

SQL> select name from v$pdbs;

NAME
------------------------------
PDB$SEED
TESTPDB1

SQL> create user c##manager identified by manager container=all;   --create a common user

User created.

SQL> grant create session to c##manager;   --grant privilege locally

Grant succeeded.

SQL> select CON_ID,PRIVILEGE,COMMON from cdb_sys_privs where GRANTEE='C##MANAGER';  --note COMMON=NO, meaning privilege is LOCAL

    CON_ID PRIVILEGE                                COM
---------- ---------------------------------------- ---
         1 CREATE SESSION                           NO

As expected, connection attempt to a Pluggable will fail.

SQL> conn c##manager/manager@TESTPDB1
ERROR:
ORA-01045: user C##MANAGER lacks CREATE SESSION privilege; logon denied

Warning: You are no longer connected to ORACLE.

SQL> conn /as sysdba   --connect to CDB$ROOT
Connected.
SQL> alter session set container=TESTPDB1;    --switch to Pluggable database TESTPDB1

Session altered.

SQL> grant create session to c##manager;   --grant privilege locally in TESTPDB1 container.

Grant succeeded.

SQL> conn /as sysdba  --connect to CDB$ROOT
Connected.
SQL> select CON_ID,PRIVILEGE,COMMON from cdb_sys_privs where GRANTEE='C##MANAGER';    --note COMMON=NO, meaning privilege is LOCAL in both containers.

    CON_ID PRIVILEGE                                COM
---------- ---------------------------------------- ---
         1 CREATE SESSION                           NO
         3 CREATE SESSION                           NO

SQL> conn c##manager/manager@TESTPDB1    --this allows to connect as the user has privilge granted locally
Connected.
SQL>  conn /as sysdba  --connect to CDB$ROOT
Connected.
SQL> grant create session to c##manager container=ALL;   --grant common privilege.

Grant succeeded.

SQL>  select CON_ID,PRIVILEGE,COMMON from cdb_sys_privs where GRANTEE='C##MANAGER';  --note COMMON=YES, meaning privilege is granted to all PDBs and any new PDBs that will be created in the future.

    CON_ID PRIVILEGE                                COM
---------- ---------------------------------------- ---
         1 CREATE SESSION                           NO
         1 CREATE SESSION                           YES
         3 CREATE SESSION                           NO
         3 CREATE SESSION                           YES

SQL> revoke create session from c##manager;  --revoking privilege locally , will have no effect as privilege is still granted commonly.

Revoke succeeded.

SQL> select CON_ID,PRIVILEGE,COMMON from cdb_sys_privs where GRANTEE='C##MANAGER';

    CON_ID PRIVILEGE                                COM
---------- ---------------------------------------- ---
         1 CREATE SESSION                           YES
         3 CREATE SESSION                           NO
         3 CREATE SESSION                           YES

SQL> alter session set container=TESTPDB1;

Session altered.

SQL> revoke create session from c##manager;   --revoking privilege locally , will have no effect as privilege is still granted commonly.

Revoke succeeded.

SQL> conn /as sysdba
Connected.
SQL> select CON_ID,PRIVILEGE,COMMON from cdb_sys_privs where GRANTEE='C##MANAGER';

    CON_ID PRIVILEGE                                COM
---------- ---------------------------------------- ---
         1 CREATE SESSION                           YES
         3 CREATE SESSION                           YES

SQL> conn c##manager/manager@TESTPDB1    --As the privilege is still granted commonly, oracle allows to connect.
Connected.

Takeaways:

1. Privileges can be granted locally or commonly
2. Revoking local privilege has no effect if privilege is granted commonly