How to Alter and Drop Roles in Oracle

Last updated on May 31 2022
Sankalp Joshi

Table of Contents

How to Alter and Drop Roles in Oracle

Introduction to Oracle ALTER ROLE statement

The ALTER ROLE statement allows you to switch the authorization needed to enable a task .

Here is that the basic syntax of the ALTER ROLE statement:

ALTER ROLE role_name

 { NOT IDENTIFIED | IDENTIFIED BY password }

In this syntax:

  • First, specify the name of the role that you simply want to vary .
  • Second, use the corresponding action like NOT IDENTIFIED to not employing a password, or IDENTIFIED BY password to vary the password of the role.

To execute the ALTER ROLE statement, your account must either are granted the role with ADMIN OPTION or have the ALTER ANY ROLE system privilege.

Note that it’s impossible to vary a NOT IDENTIFIED role to a IDENTIFIED BY password role if the role has been granted to a different role.

Oracle ALTER ROLE statement example

Let’s take an example of using the ALTER ROLE statement to vary an IDENTIFIED BY password role to a NOT IDENTIFIED role.

First, create a replacement role called db_designer:

 

CREATE ROLE db_designer IDENTIFIED BY abcd1234;

Second, grant the CREATE TABLE and make VIEW system privileges to the db_designer role:

GRANT CREATE TABLE, CREATE VIEW TO db_designer;

Third, create a user called michael:

 

CREATE USER michael IDENTIFIED BY xyz123;

Fourth, grant the db_designer and connect roles to the user michael:

GRANT db_designer, hook up with michael;

The following query returns the roles granted to the user michael:

SELECT *

FROM dba_role_privs

WHERE grantee = 'MICHAEL';

image1 23

Fifth, log in to the Oracle Database using the user michael and set the role of michael to db_designer:

SET ROLE db_designer

IDENTIFIED BY abcd1234;

Sixth, return to the primary session and alter the role to a NOT IDENTIFIED role:

ALTER ROLE db_designer NOT IDENTIFIED;

Seventh, go the user michael’s session, and reissue the SET ROLE statement again. this point we don’t need a password since the role has been changed:

SET ROLE db_designer;

Oracle DROP ROLE

Oracle DROP ROLE statement overview

The DROP ROLE statement allows you to get rid of a task from the database. Here is that the syntax of the DROP ROLE statement:

DROP ROLE role_name;

In this syntax, you specify the name of the role that you simply want to drop after the DROP ROLE keywords.

When you drop a task , Oracle revokes it from all users and roles that are previously granted. additionally , Oracle deletes the role from the database.

To drop a task , you want to have the DROP ANY ROLE system privilege or are granted the role with the ADMIN OPTION.

Oracle DROP ROLE examples

Let’s take some samples of using the DROP ROLE statement

1) Oracle DROP ROLE statement basic example

First, log in to the Oracle Database using the ot account.

Enter user-name: ot@pdborcl as sysdba

Enter password:

Next, create a replacement role called developer:

CREATE ROLE developer;

Then, very if the role has been created successfully:

SELECT * from dba_roles

WHERE role = 'DEVELOPER';

After that, drop the developer role:

DROP ROLE developer;

Finally, check if the role has been dropped:

SELECT * from dba_roles

WHERE role = 'DEVELOPER';

Oracle issued the subsequent message indicating that the role developer has been removed successfully:

no rows selected

2) Oracle DROP ROLE statement basic example

First, log in as ot user:

 

Enter user-name: ot@pdborcl as sysdba

Enter password:

Second, create a replacement role called auditor and grant the SELECT object privilege on the orders table within the sample database:

CREATE ROLE auditor;

GRANT SELECT ON orders TO auditor;

Third, create a replacement user named audi, grant the CREATE SESSION system privilege and therefore the auditor role to audi:

 

CREATE USER audi IDENTIFIED BY Abcd1234;

GRANT CREATE SESSION TO auditor;

GRANT auditor TO audi;

Fourth, log in to the Oracle database because the audi user within the second session and issue the subsequent command:

SELECT COUNT(*) FROM ot.orders;

Here is that the output:

 COUNT(*)

----------

 105

Query role of the audi user:

 

SELECT * FROM session_roles;

Here is that the role of the user audi:

 

ROLE

------

AUDITOR

Fifth, return to the primary session and drop the role auditor:

 

DROP ROLE auditor;

Sixth, attend the second session and check the roles of the user audi and issues the subsequent SELECT statement:

 

SELECT * FROM session_roles;

The following shows the output:

no rows selected

It means the audit role has been revoked from the user audi.

Seventh, from the audi’s session, attempt to execute the subsequent query to verify if the role has been revoked completely:

 

SELECT * FROM ot.orders;

Oracle issued this output:

no rows selected

Now user audi couldn’t query data from the ot.orders anymore

So, this brings us to the end of blog. This Tecklearn ‘How to Alter and Drop Roles 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

 

0 responses on "How to Alter and Drop Roles in Oracle"

Leave a Message

Your email address will not be published. Required fields are marked *