Common Grant Vs Local Grant
2 min readPrivilege 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
