Summary: in this tutorial, you will learn how to use the Oracle DROP ROLE
statement to remove a role from the database.
Oracle DROP ROLE statement overview #
The DROP ROLE
statement allows you to remove a role from the database. Here is the syntax of the DROP ROLE
statement:
DROP ROLE role_name;
Code language: SQL (Structured Query Language) (sql)
In this syntax, you specify the name of the role that you want to drop after the DROP ROLE
keywords.
When you drop a role, Oracle revokes it from all users and roles that have been previously granted. In addition, Oracle deletes the role from the database.
To drop a role, you must have the DROP ANY ROLE
system privilege or have been granted the role with the ADMIN OPTION
.
Oracle DROP ROLE examples #
Let’s take some examples 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: <ot_password>
Code language: SQL (Structured Query Language) (sql)
Next, create a new role called developer
:
CREATE ROLE developer;
Code language: SQL (Structured Query Language) (sql)
Then, verify if the role has been created successfully:
SELECT * from dba_roles
WHERE role = 'DEVELOPER';
Code language: SQL (Structured Query Language) (sql)
After that, drop the developer role:
DROP ROLE developer;
Code language: SQL (Structured Query Language) (sql)
Finally, check if the role has been dropped:
SELECT * from dba_roles
WHERE role = 'DEVELOPER';
Code language: SQL (Structured Query Language) (sql)
Oracle issued the following message indicating that the role developer has been removed successfully:
no rows selected
Code language: SQL (Structured Query Language) (sql)
2) Oracle DROP ROLE statement basic example #
First, log in as ot
user:
Enter user-name: ot@pdborcl as sysdba
Enter password: <ot_password>
Code language: SQL (Structured Query Language) (sql)
Second, create a new role called auditor
and grant the SELECT
object privilege on the orders
table in the sample database:
CREATE ROLE auditor;
GRANT SELECT ON orders TO auditor;
Code language: SQL (Structured Query Language) (sql)
Third, create a new user named audi
, grant the CREATE SESSION
system privilege and the auditor
role to audi
:
CREATE USER audi IDENTIFIED BY Abcd1234;
GRANT CREATE SESSION TO auditor;
GRANT auditor TO audi;
Code language: SQL (Structured Query Language) (sql)
Fourth, log in to the Oracle database as the audi
user in the second session and issue the following command:
SELECT COUNT(*) FROM ot.orders;
Code language: SQL (Structured Query Language) (sql)
Here is the output:
COUNT(*)
----------
105
Code language: SQL (Structured Query Language) (sql)
Query role of the audi
user:
SELECT * FROM session_roles;
Code language: SQL (Structured Query Language) (sql)
Here is the role of the user audi
:
ROLE
------
AUDITOR
Code language: SQL (Structured Query Language) (sql)
Fifth, go back to the first session and drop the role auditor
:
DROP ROLE auditor;
Code language: SQL (Structured Query Language) (sql)
Sixth, go to the second session and check the roles of the user audi
and issues the following SELECT
statement:
SELECT * FROM session_roles;
Code language: SQL (Structured Query Language) (sql)
The following shows the output:
no rows selected
Code language: SQL (Structured Query Language) (sql)
It means that the audit
role has been revoked from the user audi
.
Seventh, from the audi’s session, try to execute the following query to verify if the role has been revoked completely:
SELECT * FROM ot.orders;
Code language: SQL (Structured Query Language) (sql)
Oracle issued this output:
no rows selected
Code language: SQL (Structured Query Language) (sql)
Now user audi
couldn’t query data from the ot.orders
anymore.
In this tutorial, you have learned how to use Oracle DROP ROLE
statement to delete a role from the database.