# Login as sysdba
show con_name
CON_NAME
------------------------------
CDB$ROOT
show user
USER is "SYS"
alter session set container=orclpdb
Session altered.
show con_name
CON_NAME
------------------------------
ORCLPDB
# Create role
SQL> create role master_role;
Role MASTER_ROLE created.
SQL> grant create session to master_role;
Grant succeeded.
SQL> grant create table to master_role;
Grant succeeded.
SQL> SELECT * FROM ROLE_SYS_PRIVS where role= upper('master_role');
ROLE PRIVILEGE ADM COM INH
-------------- -------------------- --- --- ---
MASTER_ROLE CREATE TABLE NO NO NO
MASTER_ROLE CREATE SESSION NO NO NO
# Create role
SQL> create role sub_master_role;
Role created.
SQL> grant create view to sub_master_role;
Grant succeeded.
# Grant role to another role
SQL> grant sub_master_role to master_role;
Grant succeeded.
SQL> SELECT * FROM ROLE_SYS_PRIVS where role= upper('master_role');
ROLE PRIVILEGE ADM COM INH
-------------- -------------------- --- --- ---
MASTER_ROLE CREATE TABLE NO NO NO
MASTER_ROLE CREATE SESSION NO NO NO
SQL> select * from DBA_role_PRIVS where GRANTEE=upper('master_role');
GRANTEE GRANTED_ROLE ADM DEL DEF COM INH
--------------- --------------- --- --- --- --- ---
MASTER_ROLE SUB_MASTER_ROLE NO NO YES NO NO
SQL> SELECT * FROM ROLE_SYS_PRIVS where role= upper('SUB_MASTER_ROLE');
ROLE PRIVILEGE ADM COM INH
-------------------- -------------------- --- --- ---
SUB_MASTER_ROLE CREATE VIEW NO NO NO
# create new user
SQL> create user kh111 identified by kh111;
User created.
SQL> grant master_role to kh111;
Grant succeeded.
*/
SQL> conn kh111/kh111@orclpdb
Connected.
SQL> show user
USER is "KH111"
SQL> show con_name
CON_NAME
------------------------------
ORCLPDB
SQL> select * from session_privs;
PRIVILEGE
--------------------
CREATE SESSION
CREATE TABLE
CREATE VIEW