Creating Roles in Oracle

Back to: Oracle DBA Tutorials

Creating Roles in Oracle with Examples

In this article, I am going to discuss Creating Roles in Oracle with Examples. Please read our previous article where we discussed System and Object Privileges in Oracle with Examples.

Creating Roles in Oracle with Examples

Previously we understood how to create a user and grant him system privileges and object privileges. Now, we will try to understand roles in oracle. We have many dictionary tables related to privileges. Below is the table which determines the data dictionary view and the description of the data dictionary views.

We will be using this table here as well. Let’s go ahead and try to learn about roles with the below steps

Step 1: Connect to the Pluggable Database.

Let us check the current container.

Command: show con_name

Now, we are connected to the root database. Let’s go ahead and connect to the pluggable database because we are performing the tasks in the pluggable database.

Command: alter session set container=JAYAPDB;

Step 2: Create a Role and Assign Privileges in Oracle.

Let us go ahead and create a role. The role is like a bucket that consists of a bunch of privileges.

Statement: Create role manager;

Now, that we have created a role. Let’s go ahead and grant a few privileges to the role. We will be granting create table, create view, and create sequence privileges to the manager role.

Statement: grant create table, create view, create sequence to manager;

This means the role manager is a bucket that contains create table, create view, and create sequence privileges. We can check these privileges in role_sys_privs.

Query: select * from role_sys_privs where role=’manager’;

We can see there are three roles assigned to the manager role.

Step 3: Create a user and assign the role to the user in Oracle.

Let us learn more about the behavior of roles by creating a user and start assigning the role to the user.

Statement: create user demouser identified by Goodluck;

Now, that we have created a user demouser let’s go ahead and grant create a session and unlimited tablespace privileges to the user. These are the default privileges that we grant to any user.

Statement: grant create session to demouser;

Statement: grant unlimited tablespace to demouser;

Now, that we have granted default privileges to the user. Let’s go ahead and grant the role to the user demouser.

Statement: grant manager to demouser;

So, the user demouser has system privileges that are directly assigned to the user and some privileges that are assigned indirectly through the roles.

Step 4: Create a role with select privileges and assign it to the user in Oracle

Let us go ahead and create another role named queryonly.

Statement: create role queryonly;

Let’s go ahead and grant select table privileges to this role.

Statement: grant select any table to queryonly;

Now, the role has a privilege, let’s assign that role to the user.

Statement: grant queryonly to demouser;

Step 5: Create Another Role with Object Privileges in Oracle.

Let us go ahead and create a role.

Statement: create role uid_emp;

Now, that the role is created, let’s grant the object privileges to the role.

Statement: grant insert, update, delete on hr.employees to UID_EMP;

Let’s go ahead and assign this role to the demouser. Then the demouser can insert, update, and delete on hr.employees table. Let us grant this role to demouser.

Statement: grant uid_emp to demouser;

We can check these privileges under the role_tab_privs table.

Query: select * from role_tab_privs where role=’UID_EMP’;

We can see all three privileges to the demouser using the role UID_EMP; Now, we have granted sufficient privileges to the user demouser. Let’s go ahead and connect to the user and try checking these roles and privileges.

Step 1: Connect to the demouser.

As the role and user are created in JAYAPDB pluggable database. Let’s go ahead and connect to JAYAPDB using demouser.

We can see the connection is successful to the JAYAPDB using the new demouser. Let’s check the privileges of the user by session_privs

Query: select * from session_privs;

We can see all the privileges that are associated with the user directly and indirectly. Let’s check that privileges.

Step 2: Check the Privileges and Roles in Oracle.

Let us go ahead and check the privileges using the table user_sys_privs. This table contains only privileges that are directly assigned to demouser.

Query: select * from user_sys_privs;

We can see there are only two privileges to create the session and unlimited tablespace that are directly assigned to the user. We have granted only these two roles to demouser. Let’s go ahead and check the privileges for a role that is granted to a user.

Query: select * from user_role_privs;

We can see the demouser has three roles assigned to it. Manager, queryonly, and UID_EMP are the roles that are assigned to the demouser. There is another table that displays all the system privileges that are assigned to a role.

Query: select * from role_sys_privs;

We can see there are only four system privileges that are assigned to the roles. There is another table to check the object privileges that are assigned to the role.

Query: select * from role_tab_privs;

The role UID_EMP has three object privileges on the hr schema.

Step 3: Test the system privileges and object privileges that are assigned to the user.

Now, let us go ahead and test if the create privilege works in the demouser or not. We will be creating a table called student.

Statement:
Create table student
(
      student_id number,
      Student_name varchar2(100)
);

We can see the create table privilege is working fine. This table is created by demouser so, he has all the privileges of the table student. So, we will try to grant the privilege of table student to public.

Statement: grant select on student to public;

Previously we have granted select privileges on the table of another user. Let’s try to check if the privilege is working or not.

Query: select * from hr.locations;

We can see we are able to view the table employees. Previously we have created a user called demo and a table in the user called emp. Let’s try to query that table because we have selected any table privilege.

Query: select * from demo.emp;

We have been granted object privileges. Let’s try to update the employees table.

Statement: Update hr.employees Set salary = salary+10 Where employee_id=100;

So, even updating a table is also working fine. So, we learned about how to create a role and how to assign a role and we tested the privileges assigned to that role.

In the next article, I am going to discuss Common Users and Common Privileges in Oracle with Examples. Here, in this article, I try to explain Creating Roles in Oracle with Examples and I hope you enjoy this Creating Role in Oracle article.