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.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 | 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.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 | 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 |