Privilege Management:
Privilege management has been classified into 2 sub divisions. They are: 1) Grants 2) Roles
Roles:
Collection of privileges called role.
In 12c we have 2 different types of role. They are: 1) Common role 2) Local role
Common Role:
We have grant the common role to both common & local user.
SQL> show con_name
CON_NAME
------------------------------
CDB$ROOT
SQL>
SQL>
SQL> CREATE ROLE C##COMMONROLE1;
Role created.
SQL> GRANT select any table to C##COMMONROLE1;
Grant succeeded.
SQL> GRANT create any table to C##COMMONROLE1;
Grant succeeded.
SQL> grant C##COMMONROLE1 to C##USERTEST; ### without container class
Grant succeeded.
SQL> select CON_ID,GRANTEE,GRANTED_ROLE,common from cdb_role_privs where grantee like 'C##USERTEST';
CON_ID GRANTEE GRANTED_ROLE COM
---------- --------------- --------------- ---
1 C##USERTEST C##COMMONROLE1 NO
SQL> grant C##COMMONROLE1 to C##USERTEST container=all; ### with container class
Grant succeeded.
SQL> select CON_ID,GRANTEE,GRANTED_ROLE,common from cdb_role_privs where grantee like 'C##USERTEST';
CON_ID GRANTEE GRANTED_ROLE COM
---------- --------------- --------------- ---
1 C##USERTEST C##COMMONROLE1 NO
1 C##USERTEST C##COMMONROLE1 YES
4 C##USERTEST C##COMMONROLE1 YES
Granting common role to local user.
SQL> show con_name
CON_NAME
------------------------------
SHARK
SQL> grant C##COMMONROLE1 to localuser1;
Grant succeeded.
SQL> select CON_ID,GRANTEE,GRANTED_ROLE,common from cdb_role_privs where grantee like 'LOCALUSER1';
CON_ID GRANTEE GRANTED_ROLE COMM
---------- --------------- --------------- ----
4 LOCALUSER1 C##COMMONROLE1 NO
Local role:
Local role can be grant able to both common & local user. But for common user that role will only work for particular PDB only.
Granting local role to common user.
CON_NAME
------------------------------
SHARK
SQL> create role localrole1;
Role created.
SQL> grant select any table to localrole1;
Grant succeeded.
SQL> grant create any table to localrole1;
Grant succeeded.
SQL> grant localrole1 to C##USERTEST;
Grant succeeded.
SQL> select CON_ID,GRANTEE,GRANTED_ROLE,common from cdb_role_privs where grantee like 'C##USERTEST';
CON_ID GRANTEE GRANTED_ROLE COM
---------- --------------- --------------- ---
1 C##USERTEST C##COMMONROLE1 NO
1 C##USERTEST C##COMMONROLE1 YES
4 C##USERTEST LOCALROLE1 NO
4 C##USERTEST C##COMMONROLE1 YES