Scribd
Upload
14 views28 pages

Creating Roles in Oracle: A Guide

This document provides a comprehensive guide on creating roles in Oracle, including steps to connect to a pluggable database, create roles, assign privileges, and manage users. It covers the creation of multiple roles, such as 'manager' and 'queryonly', and demonstrates how to assign these roles to a user named 'demouser'. Additionally, it explains how to check the privileges associated with the roles and test their functionality within the database.

Uploaded by

palh890_742448442
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
14 views28 pages

Creating Roles in Oracle: A Guide

This document provides a comprehensive guide on creating roles in Oracle, including steps to connect to a pluggable database, create roles, assign privileges, and manage users. It covers the creation of multiple roles, such as 'manager' and 'queryonly', and demonstrates how to assign these roles to a user named 'demouser'. Additionally, it explains how to check the privileges associated with the roles and test their functionality within the database.

Uploaded by

palh890_742448442
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd

Creating Roles in Oracle with Examples - Dot Net Tutorials [Link]

net/lesson/creating-roles-in-oracle/

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

1 of 28 12/15/2025, 2:55 PM
Creating Roles in Oracle with Examples - Dot Net Tutorials [Link]

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;

2 of 28 12/15/2025, 2:55 PM
Creating Roles in Oracle with Examples - Dot Net Tutorials [Link]

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

3 of 28 12/15/2025, 2:55 PM
Creating Roles in Oracle with Examples - Dot Net Tutorials [Link]

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’;

4 of 28 12/15/2025, 2:55 PM
Creating Roles in Oracle with Examples - Dot Net Tutorials [Link]

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;

5 of 28 12/15/2025, 2:55 PM
Creating Roles in Oracle with Examples - Dot Net Tutorials [Link]

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;

6 of 28 12/15/2025, 2:55 PM
Creating Roles in Oracle with Examples - Dot Net Tutorials [Link]

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.

7 of 28 12/15/2025, 2:55 PM
Creating Roles in Oracle with Examples - Dot Net Tutorials [Link]

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;

8 of 28 12/15/2025, 2:55 PM
Creating Roles in Oracle with Examples - Dot Net Tutorials [Link]

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.

9 of 28 12/15/2025, 2:55 PM
Creating Roles in Oracle with Examples - Dot Net Tutorials [Link]

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.

10 of 28 12/15/2025, 2:55 PM
Creating Roles in Oracle with Examples - Dot Net Tutorials [Link]

Statement: grant insert, update, delete on [Link] to UID_EMP;

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

Statement: grant uid_emp to demouser;

11 of 28 12/15/2025, 2:55 PM
Creating Roles in Oracle with Examples - Dot Net Tutorials [Link]

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.

12 of 28 12/15/2025, 2:55 PM
Creating Roles in Oracle with Examples - Dot Net Tutorials [Link]

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;

13 of 28 12/15/2025, 2:55 PM
Creating Roles in Oracle with Examples - Dot Net Tutorials [Link]

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;

14 of 28 12/15/2025, 2:55 PM
Creating Roles in Oracle with Examples - Dot Net Tutorials [Link]

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;

15 of 28 12/15/2025, 2:55 PM
Creating Roles in Oracle with Examples - Dot Net Tutorials [Link]

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;

16 of 28 12/15/2025, 2:55 PM
Creating Roles in Oracle with Examples - Dot Net Tutorials [Link]

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;

17 of 28 12/15/2025, 2:55 PM
Creating Roles in Oracle with Examples - Dot Net Tutorials [Link]

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
(

18 of 28 12/15/2025, 2:55 PM
Creating Roles in Oracle with Examples - Dot Net Tutorials [Link]

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;

19 of 28 12/15/2025, 2:55 PM
Creating Roles in Oracle with Examples - Dot Net Tutorials [Link]

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 [Link];

20 of 28 12/15/2025, 2:55 PM
Creating Roles in Oracle with Examples - Dot Net Tutorials [Link]

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 [Link];

21 of 28 12/15/2025, 2:55 PM
Creating Roles in Oracle with Examples - Dot Net Tutorials [Link]

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

Statement: Update [Link] 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
Online Oracle article.
DBA Training Program

22 of 28 12/15/2025, 2:55 PM
Creating Roles in Oracle with Examples - Dot Net Tutorials [Link]

 Oracle DBA Online Training

Introduction and Environment Setup


Dot Net Tutorials
 Introduction to Oracle Database
About the Author: Pranaya Rout
 Oracle Database Vs Database Instance
Pranaya Rout has published more than 3,000 articles in his 11-year
 Oracle Database Instance Architecture
career. Pranaya Rout has very good experience with Microsoft
 Oracle Database Files
Technologies, Including C#, VB, [Link] MVC, [Link] Web API, EF, EF Core, [Link],
 Oracle
LINQ,Storage Structures
SQL Server, MYSQL, Oracle, [Link] Core, Cloud Computing, Microservices, Design
Patterns
 Oracle and stillArchitecture
Multitenant learning new technologies.

 Oracle DBA Tasks and Tools


 Oracle Installation Steps
 Downloading and Installing Oracle Virtual Box
 Downloading Oracle Linux image
 Creating Virtual Machine in Virtual Box and Installing Linux
 Installing Guest Additions for Oracle Linux
Previous
 Lesson
Basic Linux Commands Next Lesson
System
 and Object
Prerequisites Privileges
For Installing in Oracle
Oracle Database Common Users and Common Privileges in
Oracle
 Downloading and Installing Oracle Database

Database Startup, Shutdown and Connection


 How to Startup Oracle Database
Leave a Reply
 How to Shutdown Oracle Database
Your email address will not be published. Required fields are marked *
 How to Connect to Oracle Database
 Installing
Comment * Putty Software and Testing Connections

 Installing Winscp Software and Transferring Files


 Installing SQL Developer
 Multiple Ways to Connect as sys User in Oracle
 Shifting from Container to Pluggable Database
 Starting and Stopping Listeners in Oracle
 Easy Connect Method to Connecting with Oracle
 Connecting to Oracle using [Link]
 Locking and Unlocking User Accounts in Oracle
 Saving and Running Scripts in Oracle
Name*
Helpful Queries to Explore Oracle DB Architecture
 Oracle Data Dictionary
 Common Users vs Local Users in Oracle

23 of 28 12/15/2025, 2:55 PM
Creating Roles in Oracle with Examples - Dot Net Tutorials [Link]

 Dynamic Performance Views in Oracle


Email*
 Data Dictionary Differences inside a Pluggable Database in Oracle
 Query Data Files and Temp Files in Oracle
 V$DATABASE/ V$INSTANCE in Oracle
Website

 V$Containers/cdb_pdbs in Oracle Database


 V$datafile in Oracle
PostTablespaces
 Comment in Oracle
 Controlfile and Logfile in Oracle
 Pluggable Database Save State in Oracle

Managing Database Instance


 Initialization Parameters in Oracle
 Parameter File in Oracle
 Types of Initialization Parameters in Oracle
 Modifying Initialization Parameters in Oracle
 Alter Session in Oracle with Examples
 V$PARAMETER vs V$PARAMETER2 in Oracle
 V$SYSTEM_PARAMETER vs V$SYSTEM_PARAMETER2 in Oracle
 Search Order for a Parameter file in Oracle
 Non-Default Name for Pfile in Oracle
 Alter System Examples in Oracle
 Oracle Default Scope in Alter Statement
 Container Clause in Oracle
 Automatic Diagnostic Repository in Oracle
 Alert Log Files in Oracle
 Alert Log using ADRCI in Oracle
 Trace Files Purging in Oracle
 Enable DDL Logging in Oracle
 V$SPPARAMETER in Oracle

Creating PDBs
 Creating Pluggable Databases in Oracle
 Creating Pluggable Database from SEED in Oracle
 Connection to Pluggable Database in Oracle
 Creating Pluggable Database using DBCA in Oracle
 Drop Pluggable Database in Oracle

24 of 28 12/15/2025, 2:55 PM
Creating Roles in Oracle with Examples - Dot Net Tutorials [Link]

 Cloning Pluggable Database in Oracle


 Plugging and Unplugging in PDBs
 Plugging and Unplugging in PDBs METHOD -II

Configuring Oracle Network Environment


 Establishing a Connection and Session in Oracle
 Default Listener in Oracle
 Default Listener Examples in Oracle
 Non-Default Port in Oracle
 Dynamic Listener in Oracle
 Static Listener in Oracle
 Oracle Net Configuration Assistant
 Oracle Net Manager
 Database Link in Oracle
 Create Database Link in Oracle

Administering User Security


 Controlling User Access in Oracle
 System and Object Privileges in Oracle
 Creating Roles in Oracle
 Common Users and Common Privileges in Oracle
 Common Roles in Oracle
 Information about User Accounts in Oracle
 Oracle Supplied Administrator Accounts
 Special System Privileges for Administrators in Oracle
 Sys User Example in Oracle
 Pluggable Database Admin in Oracle
 Role Granted to Another Role in Oracle
 PDB_DBA in Oracle
 Admin Option in Oracle
 User Profiles in Oracle
 User Password in Oracle
 Resource Parameters in Oracle
 Creation of Common Profile in Oracle
 Creation of Profile in Oracle Pluggable Database
 Password Verification in Oracle

25 of 28 12/15/2025, 2:55 PM
Creating Roles in Oracle with Examples - Dot Net Tutorials [Link]

Creating and Managing Table Space


 Tablespaces and Stored Structures in Oracle
 How Table Data is Stored in Oracle
 How to Create Tablespace in Oracle
 Creating a new PDB for Tablespace Practice in Oracle
 Queries for Tablespace in Oracle
 How to Create Permanent Tablespace in Oracle
 Tablespace Management in Oracle
 Alter Tablespace and Datafile in Oracle
 Moving or Renaming Datafile in Oracle

Managing Storage Space in Oracle DBA


 Managing Storage Space in Oracle
 Segments, Extents and Data Blocks in Oracle
 Availability and Optimization of Free Space in Oracle
 Row Chaining and Migrating in Oracle
 Types of Segments in Oracle
 Deferred Segment Creation in Oracle
 Space Saving Features in Oracle
 Basic Compression in Oracle
 Advanced Row Compression in Oracle
 Monitoring Tablespace Usage in Oracle
 DBMS_SERVER_ALERT in Oracle
 Reclaiming Wasted Space in Oracle
 Segment Advisor in Oracle
 Managing Resumable Space in Oracle

Managing UNDO Data


 Managing UNDO Data in Oracle
 Transactions and Undo Data in Oracle
 Comparing Undo Data and Redo Data in Oracle
 Creating a New Pluggable Database in Oracle
 Local Undo Mode vs Shared Undo in Oracle
 Automatic Undo Management in Oracle
 Undo Retention Period in Oracle
 Categories of UNDO in Oracle

26 of 28 12/15/2025, 2:55 PM
Creating Roles in Oracle with Examples - Dot Net Tutorials [Link]

 Flashback Table and Flashback Query in Oracle


 Automatic Tuning of Undo Retention in Oracle

27 of 28 12/15/2025, 2:55 PM
Creating Roles in Oracle with Examples - Dot Net Tutorials [Link]

About Us Privacy Policy Contact [Link] Tutorial Angular Tutorials [Link] Core Blazor Tuturials
[Link] Core Tutorials [Link] MVC Tutorials [Link] Web API Tutorials C Tutorials
C#.NET Programs Tutorials C#.NET Tutorials Cloud Computing Tutorials
Data Structures and Algorithms Tutorials Design Patterns Tutorials
DotNet Interview Questions and Answers Core Java Tutorials Entity Framework Tutorials
JavaScript Tutorials LINQ Tutorials Python Tutorials SOLID Principles Tutorials SQL Server Tutorials
Trading Tutorials JDBC Tutorials Java Servlets Tutorials Java Struts Tutorials C++ Tutorials
JSP Tutorials MySQL Tutorials Oracle Tutorials [Link] Core Web API Tutorials HTML Tutorials

© Dot Net Tutorials | Website Design by Sunrise Pixel

28 of 28 12/15/2025, 2:55 PM

Common questions

Powered by AI

Testing both system and object privileges is essential to confirm that the user can perform intended actions without unauthorized access errors. This is accomplished by attempting operations the privileges are supposed to allow. For example, create a table to test create privilege, or update data to test object privileges like insert/update on tables. In the case of 'demouser', creating a 'student' table verified system privileges were properly assigned, and updating 'hr.employees' confirmed object privileges were working .

The 'pluggable database' is significant because it allows for individual databases to be managed within the same container database environment. This structure supports easier database management, such as moving databases between containers. Connecting to a specific pluggable database, like 'JAYAPDB', is important for performing operations within the correct context. Without this connection, roles and privileges configured may not appropriately apply or reflect within the intended user operations .

Role interactions describe how roles interact both with users and other roles in terms of privilege inheritance. Assigning a role to a user grants all the privileges the role comprises, affecting the user's ability to execute various actions. Conversely, revoking a role can immediately strip all its permissions from the user. These changes directly affect a user’s access and must be handled carefully to prevent privilege escalation or denial of necessary access. For example, when the 'manager' role is granted to 'demouser', all privileges within are inherited until potentially revoked .

Granting roles like 'queryonly' requires careful planning to ensure users can view necessary information without exposing sensitive data. Select permissions give visibility into database values, so roles should be narrowly defined to allow access only to pertinent tables. This protects sensitive information while providing access to necessary datasets. Poor configuration can lead to excessive data access, potentially leaking information. Therefore, understanding the tables and data is critical before role permissions like ‘select any table’ are included .

Assigning multiple roles involves first creating the roles with the required privileges and then granting these roles to the user. For example, roles 'manager', 'queryonly', and 'UID_EMP' were created, each with a specific set of privileges like 'create table' or 'select any table'. These roles were then granted to the user 'demouser'. The impact of this process is that 'demouser' gains both the specific system privileges directly assigned and additional privileges bundled within the roles assigned to them .

Differentiation between system and object privileges helps establish clearer lines of control, enabling finer-grained security management. System privileges allow actions affecting the entire database, while object privileges are limited to specific data objects. Mismanagement of system privileges can lead to broad escalations of access rights, whereas controlling object privileges helps prevent unauthorized access to critical data. Separating these privileges allows for tailored security policies, such as granting limited access to specific tables while restricting broader database functions .

The process involves querying the 'user_sys_privs' table to see privileges directly assigned to a user. In contrast, 'user_role_privs' displays roles granted to the user. To differentiate, 'role_sys_privs' and 'role_tab_privs' provide the privileges contained within roles. For 'demouser', direct privileges like 'create session' and 'unlimited tablespace' are listed under 'user_sys_privs', while roles like 'manager', which hold additional privileges, are detailed in 'role_sys_privs' and 'role_tab_privs' .

Oracle allows granting object privileges to roles, which can then be assigned to users, providing indirect access. This centralizes privilege management, reduces administrative overhead, and enhances security by systematically assigning rights through roles rather than directly to users. For example, the 'UID_EMP' role was granted privileges like insert, update, delete on the 'hr.employees' table, centralizing control of sensitive operations and allowing easy updates to user rights by modifying role privileges .

Roles in Oracle act as containers for a set of privileges, simplifying the management of permissions across multiple users. Instead of granting individual privileges directly to each user, a role—a collection of privileges—can be assigned, which can subsequently be granted to users. This streamlines both the assignment and management of privileges. For example, creating a 'manager' role that includes 'create table', 'create view', and 'create sequence' privileges enables Oracle administrators to efficiently manage access .

An administrator would use the query `SELECT * FROM role_sys_privs` to verify system privileges granted to different Oracle roles. This verification is important to ensure that roles have the correct set of permissions expected for their purpose, preventing misuse or security risks associated with excessive privileges. Accurate privilege tracking helps maintain the integrity and security of database operations by identifying role misconfigurations or privilege duplications .

You might also like