Common Grant Vs Local Grant

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