Oracle Tutorial

  • Home
  • Start Here
  • Basics
  • Advanced
    • Oracle View
    • Oracle Index
    • Oracle Synonym
    • Oracle Sequence
    • Oracle Administration
  • PL/SQL
  • Functions
    • Aggregate Functions
    • Analytic Functions
    • Comparison Functions
    • Date Functions
    • String Functions
  • API
    • Python Oracle
Home / Oracle Database Administration / Oracle REVOKE

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;

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;

Oracle REVOKE statement example

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

CREATE USER bob IDENTIFIED BY abcd1234; GRANT CREATE SESSION TO bob;

Second, grant the CREATE TABLE system privilege to bob:

GRANT CREATE TABLE TO bob;

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;

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;

Both queries 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;

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;

Oracle issued the following error:

ORA-00942: table or view does not exist

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;

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

CREATE TABLE t2(id INT);

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

ORA-01031: insufficient privileges

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;

Next time, bob won’t be able to log in 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.

  • Was this tutorial helpful?
  • YesNo
Previous Oracle GRANT
Next Oracle ALTER USER

Getting Started

  • What Is Oracle Database
  • Install Oracle Database Server
  • Download Oracle Sample Database
  • Create Oracle Sample Database
  • Connect To Oracle Database Server

Oracle Data Manipulation

  • SELECT
  • Oracle DUAL Table
  • ORDER BY
  • SELECT DISTINCT
  • WHERE
  • Table & Column Aliases
  • AND
  • OR
  • FETCH
  • BETWEEN
  • IN
  • LIKE
  • IS NULL
  • Joins
  • INNER JOIN
  • LEFT JOIN
  • RIGHT JOIN
  • FULL OUTER JOIN
  • CROSS JOIN
  • Self Join
  • GROUP BY
  • HAVING
  • UNION
  • INTERSECT
  • MINUS
  • GROUPING SETS
  • CUBE
  • ROLLUP
  • PIVOT
  • UNPIVOT
  • INSERT
  • INSERT INTO SELECT
  • INSERT ALL
  • UPDATE
  • DELETE
  • MERGE
  • Subquery
  • Correlated Subquery
  • EXISTS
  • NOT EXISTS
  • ANY
  • ALL

Oracle Data Types

  • Oracle Data Types
  • NUMBER
  • FLOAT
  • BINARY_FLOAT
  • CHAR
  • NCHAR
  • VARCHAR2
  • NVARCHAR2
  • DATE
  • INTERVAL
  • TIMESTAMP
  • TIMESTAMP WITH TIME ZONE

Oracle Data Definition

  • CREATE TABLE
  • Identity Column
  • ALTER TABLE
  • ALTER TABLE ADD Column
  • ALTER TABLE MODIFY Column
  • Drop Columns
  • DROP TABLE
  • TRUNCATE TABLE
  • RENAME Table
  • Oracle Virtual Column

Oracle Constraints

  • PRIMARY KEY
  • FOREIGN KEY
  • UNIQUE
  • CHECK
  • NOT NULL

Oracle Views

  • CREATE VIEW
  • DROP VIEW
  • Updatable Views
  • Inline Views
  • WITH CHECK OPTION

About Oracle Tutorial

OracleTututorial.com website provides Developers and Database Administrators with the updated Oracle tutorials, scripts, and tips.

Search

Recent Tutorials

  • Oracle Implicit Statement Results
  • Calling PL/SQL Stored Functions in Python
  • Calling PL/SQL Procedures in Python
  • Managing Transaction in Python
  • Deleting Data From Oracle Database in Python

Site Links

  • Oracle Books
  • About
  • Contact
  • Privacy Policy
  • Terms of Use

Copyright © 2021 Oracle Tutorial. All Rights Reserved.