A technical troubleshooting blog about Oracle with other Databases & Cloud Technologies.

Common Grant Vs Local Grant

2 min read

Privilege Management:

Privilege management has been classified into 2 sub divisions. They are:
1) Grants
2) Roles 

Grants:

oracle12c have two variant of grants.
They are:

1) Common grants
2) Local grants

Common grants:

Common grants will be only applicable for particular user present in both CDB & PDB as well.

CDB level grant:
=============================
SQL> show con_name

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


SQL> grant create session to C##USERTEST; ### Without container class

Grant succeeded.


SQL> select CON_ID,GRANTEE,PRIVILEGE,ADMIN_OPTION,COMMON from cdb_sys_privs where GRANTEE like 'C##USERTEST';

CON_ID   GRANTEE          PRIVILEGE                    ADM COMM
---------   ---------------    --------------------------- ----  ----
1             C##USERTEST CREATE SESSION            NO  NO


SQL> grant create session to C##USERTEST container=all; ### With container class

Grant succeeded.


SQL> select CON_ID,GRANTEE,PRIVILEGE,ADMIN_OPTION,COMMON from cdb_sys_privs where GRANTEE like 'C##USERTEST';

CON_ID GRANTEE PRIVILEGE ADM COMM
---------- --------------- ---------------------------------------- --- ----
1 C##USERTEST CREATE SESSION NO NO
1 C##USERTEST CREATE SESSION NO YES
4 C##USERTEST CREATE SESSION NO YES


Con_id => 1 denote the CBB
Con_id => 4 denotes the PDB


Note: Common grants cannot be revoke in PDB level.

Ex:

SQL> select CON_ID,GRANTEE,PRIVILEGE,ADMIN_OPTION,COMMON from cdb_sys_privs where GRANTEE like 'C##USERTEST';

CON_ID GRANTEE PRIVILEGE ADM COMM
---------- --------------- ---------------------------------------- --- ----
1 C##USERTEST CREATE SESSION NO NO
1 C##USERTEST CREATE SESSION NO YES
4 C##USERTEST CREATE SESSION NO YES

SQL> show con_name

CON_NAME
------------------------------
DEV18

SQL> revoke create session from C##USERTEST;
revoke create session from C##USERTEST
*
ERROR at line 1:
ORA-65092: system privilege granted with a different scope to 'C##USERTEST'


SQL> revoke create session from C##USERTEST container=all;
revoke create session from C##USERTEST container=all
*
ERROR at line 1:
ORA-65040: operation not allowed from within a pluggable database


SQL> revoke create session from C##USERTEST container=current;
revoke create session from C##USERTEST container=current
*
ERROR at line 1:
ORA-65092: system privilege granted with a different scope to 'C##USERTEST'

Local grants: 

Local grant will be applicable on the particular PDB level even though user is common. 

SQL> select CON_ID,GRANTEE,PRIVILEGE,ADMIN_OPTION,COMMON from cdb_sys_privs where GRANTEE like 'C##USERTEST';

CON_ID GRANTEE PRIVILEGE ADM COMM
---------- --------------- ---------------------------------------- --- ----
1 C##USERTEST CREATE SESSION NO NO

CON_NAME
------------------------------
DEV18

SQL>
SQL> grant create session to C##USERTEST;

Grant succeeded.


SQL> select CON_ID,GRANTEE,PRIVILEGE,ADMIN_OPTION,COMMON from cdb_sys_privs where GRANTEE like 'C##USERTEST';

CON_ID GRANTEE PRIVILEGE ADM COMM
---------- --------------- ---------------------------------------- --- ----
1 C##USERTEST CREATE SESSION NO NO
4 C##USERTEST CREATE SESSION NO NO