Role Granted to Another Role in Oracle

In this article, I am going to discuss Role Granted to Another Role in Oracle with Examples. Please read our previous article where we discussed Pluggable Database Admin in Oracle with Examples.

Role Granted to Another Role in Oracle

Now, we will learn about granting a role to another role. This is a complicated concept but we will try to explain it simply.

Step 1: Open Linux virtual machine and start the listener and start the database. Once the database is started, open the SQL developer, and connect to the database as “sqlplus / as sysdba”. Let us go ahead and check the database name and the user.

Let us go ahead and connect to the pluggable database.

Command: alter session set container=jayapdb;

Now, we have connected to the pluggable database. Let us check the container id and the container name.

Command: show con_id

Step 2: Create a role and grant privileges

We are creating a role named master_role. The role is a bucket where it contains other privileges. Once the role is created, we will be granted to create a session and create a table for the role.

So, the role is created, let us grant create a session and create table privilege.

Step 3: Check the privileges

Let us check the privileges of the role assigned.

Query: select * from role_sys_privs where role=upper(‘master_role’);

We can see the role contains two privileges.

Step 4: Create another role

Let us go ahead and create a sub-master role named sub_master. We are going to grant only one privilege to the role.

Statement: create role sub_master;

As we have created the sub_master role, let us go ahead and grant create view privilege.

Command: grant create privilege to sub_master;

This means we have two roles which are master_role and sub_master where master_role has two privileges and sub_master has only one privilege. Now, we are going to grant the sub_master role to the master_role.

Command: grant sub_master to master_role;

So, we have granted one role to another role.

Step 5: Check the Privileges

Let us run a query to check the privileges that are granted to master_role.

Query: select * from role_sys_privs where role=upper(‘master_role’);

We can see the master_role has only two direct privileges. This role does not have the sub_master role privilege because that s an indirect privilege. Let us check the view dba_role_privs whether there are any role privileges assigned to the master_role.

Query: select * from dba_role_privs where grantee=upper(‘master_role’);

Let us go ahead and check the privileges for the role sub_master using the view role_sys_privs.

Query: select * from role_sys_privs where role=upper(‘master_role’);

We can see the privilege is create view.

Step 6: Create a user and grant privileges.

Let us test these roles by creating a user and granting these roles to the user.

Statement: create user newdemo identified by goodluck;

So, the user is created. Let us grant the master_role to the user newdemo. The role master_role has the privileges with another role sub_master.

The user does not know what all the privileges does master_role has but the user has few privileges.

Step 7: Connect to the user and check privileges.

Let us go ahead and connect to the user using the terminal.

Command: sqlplus newdemo/goodluck@jayapdb;

Let us check the database name and user name.

Let us go ahead and check the session privileges of the user logged in.

We can see there are three privileges. The user does not know how these privileges are granted. But as a DBA we know that there are two roles. One role is assigned to another role and the role is assigned to the user newdemo. This is how DBA grants a role to a new user and the new user does not know how he got all these privileges.

In the next article, I am going to discuss Understanding PDB_DBA in Oracle with Examples. Here, in this article, I try to explain the Role Granted to Another Role in Oracle with Examples. I hope you enjoy this Role Granted to Another Role in Oracle article.