Oracle REVOKE

Summary: in this tutorial, you will learn how to use the Oracle REVOKE statement to revoke system and object privileges from a specific user.

Introduction to Oracle REVOKE statement

The Oracle REVOKE statement revokes system and object privileges from a user. Here is the basic syntax of the Oracle REVOKE statement:

REVOKE {system_privilege | object_privilege } FROM user;Code language: SQL (Structured Query Language) (sql)

In this syntax:

• First, specify the system or object privileges that you want to revoke from the user.
• Second, specify the user from which you want to revoke the privileges.

In order to revoke a system privilege from a user, you must have been granted the system privilege with the ADMIN OPTION.

To revoke an object privilege from a user, you must previously granted the object privilege to the user or you must have the GRANT ANY OBJECT PRIVILEGE system privilege.

On top of this, you can use the REVOKE statement to revoke only privileges that were granted directly with a GRANT statement. In other words, you cannot use the REVOKE statement to revoke privileges that were granted through the operating system or roles.

To revoke all system privileges from a user, you can use the following statement:

REVOKE ALL PRIVILEGES FROM user;Code language: SQL (Structured Query Language) (sql)

Oracle REVOKE statement example

First, create a user names bob and grant him the CREATE SESSION system privilege so that he can log in to the Oracle Database:

CREATE USER bob IDENTIFIED BY abcd1234;
GRANT CREATE SESSION TO bob;Code language: SQL (Structured Query Language) (sql)

Second, grant the CREATE TABLE system privilege to bob:

GRANT CREATE TABLE TO bob;Code language: SQL (Structured Query Language) (sql)

Third, grant the SELECT, INSERT, UPDATE and DELETE object privileges to bob on ot.customers table:

GRANT SELECT, INSERT, UPDATE, DELETE ON ot.customers
TO bob;Code language: SQL (Structured Query Language) (sql)

Now, bob can create a new table in his own schema and manipulate data in the ot.customers table.

Fourth, log in to the Oracle Database as bob and execute the following statements:

CREATE TABLE  t1(id int);

SELECT 
    name
FROM 
    customers
ORDER BY 
    name
FETCH FIRST 5 ROWS ONLY; 
Code language: SQL (Structured Query Language) (sql)

Both queries are executed successfully because the user bob has sufficient privileges.

Fifth, revoke the object privileges from bob:

REVOKE SELECT, INSERT, UPDATE, DELETE ON ot.customers
FROM bob;Code language: SQL (Structured Query Language) (sql)

Sixth, go to the bob‘s session and select data from the ot.customers table:

SELECT 
    name
FROM 
    customers
ORDER BY 
    name
FETCH FIRST 5 ROWS ONLY; Code language: SQL (Structured Query Language) (sql)

Oracle issued the following error:

ORA-00942: table or view does not existCode language: SQL (Structured Query Language) (sql)

This is correct because bob is no longer has the SELECT object privilege on the ot.customers table.

Seventh, revoke the CREATE TABLE system privilege from bob:

REVOKE CREATE TABLE FROM bob;
Code language: SQL (Structured Query Language) (sql)

Eighth, go to bob’s session and attempt to create a new table:

CREATE TABLE t2(id INT);
Code language: SQL (Structured Query Language) (sql)

Oracle issued the following error, which is what we expected.

ORA-01031: insufficient privilegesCode language: SQL (Structured Query Language) (sql)

If you don’t want bob to log in, you can revoke the CREATE SESSION system privilege as shown in the following statement:

REVOKE CREATE SESSION FROM bob;Code language: SQL (Structured Query Language) (sql)

Next time, bob won’t be able to log in to the Oracle Database anymore.

In this tutorial, you have learned how to use the Oracle REVOKE statement to revoke system and object privileges from a user.