A role may be a group of privileges. rather than granting individual privileges to users, you’ll group related privileges into a task and grant this role to users. Roles help manage privileges more efficiently.

To create a replacement role, you employ the CREATE ROLE statement. the essential syntax of the CREATE ROLE statement is as follows:

CREATE ROLE role_name

[IDENTIFIED BY password]

[NOT IDENTIFIED]

In this syntax:

First, specify the name of the role that you simply want to make .
Second, use IDENTIFIED BY password choice to create an area role and indicate that the user, who was granted the role, must provide the password to the database when enabling the role.
Third, use NOT IDENTIFIED to point that the role is permitted by the database and therefore the user, who was granted this role, don’t need a password to enable the role.

After a task is made , it’s empty. To grant privileges to a task , you employ the GRANT statement:

GRANT {system_privileges | object_privileges} TO role_name;

In addition, you’ll use the GRANT statement to grant privileges of a task to a different role:

GRANT role_name TO another_role_name;

Oracle CREATE ROLE statement examples

Let’s take some samples of using the CREATE ROLE statement.

1) Using Oracle CREATE ROLE without a password example

First, create a replacement role named mdm (master data management) within the sample database:

CREATE ROLES mdm;

Second, grant object privileges on customers, contacts, products, product_categories, warehouses, locations, employees tables to the mdm role:

GRANT SELECT, INSERT, UPDATE, DELETE

ON customers

TO mdm;

GRANT SELECT, INSERT, UPDATE, DELETE

ON contacts

TO mdm;

GRANT SELECT, INSERT, UPDATE, DELETE

ON products

TO mdm;

GRANT SELECT, INSERT, UPDATE, DELETE

ON product_categories

TO mdm;

GRANT SELECT, INSERT, UPDATE, DELETE

ON warehouses

TO mdm;

GRANT SELECT, INSERT, UPDATE, DELETE

ON locations

TO mdm;

GRANT SELECT, INSERT, UPDATE, DELETE

ON employees

TO mdm;

Third, create a replacement user named alice and grant the CREATE SESSION privilege to alice:

CREATE USER alice IDENTIFIED BY abcd1234;

GRANT CREATE SESSION TO alice;

Fourth, log in to the database as alice:

Enter user-name: alice@pdborcl

Enter password:

and plan to query data from the ot.employees table:

SELECT * FROM ot.employees;

Oracle issued the subsequent error:

ORA-00942: table or view doesn’t exist

Go back to the primary session and grant alice the mdm role:

GRANT mdm TO alice;

Go to the alice’s session and enable role using the SET ROLE statement:

SET ROLE mdm;

To query all roles of the present user, you employ the subsequent query:

SELECT * FROM session_roles;

Here is that the role of alice:

ROLE

——

MDM

Now, alice can manipulate data within the master data tables like customers and employees.

2) Using Oracle CREATE ROLE to make a task with IDENTIFIED BY password example

First, create a replacement role named order_entry with the password xyz123:

CREATE ROLE order_entry IDENTIFIED BY xyz123;

Next, grant object privileges on the orders and order_items tables to the order_entry role:

GRANT SELECT, INSERT, UPDATE, DELETE

ON orders

TO order_entry;

GRANT SELECT, INSERT, UPDATE, DELETE

ON order_items

TO order_entry;

Then, grant the order_entry role to the user alice:

GRANT order_entry TO alice;

After that, log in as alice and enable the order_entry role by using the SET ROLE statement:

SET ROLE

order_entry IDENTIFIED BY xyz123,

mdm;

Finally, use the subsequent statement to urge the present roles of alice:

SELECT * FROM session_roles;

Here are the present roles of alice:

ROLE

————-

MDM

ORDER_ENTRY

So, this brings us to the end of blog. This Tecklearn ‘How to Create Role in Oracle’ blog helps you with commonly asked questions if you are looking out for a job in Oracle DB. If you wish to learn Oracle DB and build a career in Database domain, then check out our interactive, Oracle 12c DBA Training, that comes with 24*7 support to guide you throughout your learning period. Please find the link for course details:

https://www.tecklearn.com/course/oracle-12c-dba-course/

Oracle 12c DBA Course Training

About the Course

Oracle Database Administration training will help you master the Oracle DBA. As part of the training, you will master the Oracle Database architecture, the database processes, memory structures, schema objects, security, data recovery and backup through hands-on projects and case studies. Oracle DBA Training helps students gain the skills and knowledge required to install, configure, and administer Oracle databases. Besides, this training also develops the full skill set required to become a successful Oracle Database Administrator.

Why Should you take Oracle DBA Training?

The Average salary of an Oracle DBA is $108,837 per annum – GlassDoor.com
According to Gartner, Oracle is the market leader in the Relational Database with 48.35% market share globally.
IBM, TCS, Tech Mahindra, Oracle, Wipro & other MNCs worldwide use Pl-SQL for their database deployments.

What you will Learn in this Course?

Introduction to Oracle Database

What is the Oracle Database
Pre-requisites for oracle database
Oracle database 12c installation
Multi-tenant architecture

Exploring the Oracle 12c Database Architecture

Explain the Memory Structures
Describe the Process Structures
Overview of Storage Structures

Preparing the Database Environment

Identify the tools for Administering an Oracle Database
Plan an Oracle Database Installation
Install the Oracle Software by using Oracle Universal Installer (OUI)
Create a Database by using the Database Configuration Assistant (DBCA)

Managing the Oracle Instance

Setting database initialization parameters
Describe the stages of database startup and shutdown

Database Storage Structures and Schema Objects

Managing Database Storage Structures
Overview of table space and data files
Overview of Primary Components
Identify the importance of checkpoints, redo log files, and archived log files
Configure ARHIVELOG mode

Database Startup & User Requests and Oracle Network Environment

Describe the stages of database startup and shutdown
Using alert log and trace files
Configure and Manage the Oracle Network
pFile and spFile
Tablespace Management
Storage and Relationship Structure

Oracle Backup & Recovery

Redo log File Management
Performing Database Recovery
Overview of Data Recovery Advisor
Use Data Recovery Advisor to Perform recovery (Control file, Redo log file and Data file)

Deep Dive into Oracle Recovery Manager (RMAN)

Using the RMAN Recovery Catalog
Identify situations that require RMAN recovery catalog
Create and configure a recovery catalog
Synchronize the recovery catalog
Create and Use RMAN stored scripts
Back up the recovery catalog
Create and use a virtual private catalog

Configuring Backup Specifications

Using RMAN to Create Backups
Create image file backups
Create a whole database backup
Enable fast incremental backup
Using RMAN to Perform Recovery
Perform complete recovery from a critical or noncritical data file loss using RMAN
Perform incomplete recovery using RMAN

Data Movement and Oracle Data Pump

Moving Data
Describe and use methods to use move data (Directory objects, SQL* Loader, External tables)
Explain the general architecture of Oracle Data Pump

Data Dictionary & Dynamic Performance Tables

Oracle Data Dictionary contains information related to database privileges
Data dictionary views
Media failure
Flashback CDB

Database Tuning

What is database tuning
How to tune the database
Tuning the data dictionary

451