When the DEFAULT clause is specified, the role itself is not revoked, only its DEFAULT property is removed without revoking the role itself.

The FROM clause specifies a list of users, roles and other database objects that will have the enumerated privileges revoked. The optional USER keyword in the FROM clause allow you to specify exactly which type is to have the privilege revoked. If a USER (or ROLE) keyword is not specified, the server first checks for a role with this name and, if there is no such role, the privileges are revoked from the user with that name without further checking.

The optional GRANT OPTION FOR clause revokes the user’s privilege to grant the specified privileges to other users, roles, or database objects (as previously granted with the WITH GRANT OPTION). It does not revoke the specified privilege itself.

One usage of the REVOKE statement is to remove roles that were assigned to a user, or a group of users, by a GRANT statement. In the case of multiple roles and/or multiple grantees, the REVOKE verb is followed by the list of roles that will be removed from the list of users specified after the FROM clause.

The optional ADMIN OPTION FOR clause provides the means to revoke the grantee’s administrator privilege, the ability to assign the same role to other users, without revoking the grantee’s privilege to the role.

Multiple roles and grantees can be processed in a single statement.

A privilege that has been granted using the GRANTED BY clause is internally attributed explicitly to the grantor designated by that original GRANT statement. Only that user can revoke the granted privilege. Using the GRANTED BY clause you can revoke privileges as if you are the specified user. To revoke a privilege with GRANTED BY, the current user must be logged in either with full administrative privileges, or as the user designated as grantor by that GRANTED BY clause.

The non-standard AS clause is supported as a synonym of the GRANTED BY clause to simplify migration from other database systems.

The REVOKE ALL ON ALL statement allows a user to revoke all privileges (including roles) on all object from one or more users, roles or other database objects. It is a quick way to clear privileges when access to the database must be blocked for a particular user or role.

When the current user is logged in with full administrator privileges in the database, the REVOKE ALL ON ALL will remove all privileges, no matter who granted them. Otherwise, only the privileges granted by the current user are removed.

1. Revoking the privileges for selecting and inserting into the table (or view) SALES

     |REVOKE SELECT, INSERT ON TABLE SALES
     |  FROM USER ALEX;
   

2. Revoking the privilege for selecting from the CUSTOMER table from the MANAGER and ENGINEER roles and from the user IVAN:

     |REVOKE SELECT ON TABLE CUSTOMER
     |  FROM ROLE MANAGER, ROLE ENGINEER, USER IVAN;
   

3. Revoking from the ADMINISTRATOR role the privilege to grant any privileges on the CUSTOMER table to other users or roles:

     |REVOKE GRANT OPTION FOR ALL ON TABLE CUSTOMER
     |  FROM ROLE ADMINISTRATOR;
   

4. Revoking the privilege for selecting from the COUNTRY table and the privilege to reference the NAME column of the COUNTRY table from any user, via the special user PUBLIC:

     |REVOKE SELECT, REFERENCES (NAME) ON TABLE COUNTRY
     |  FROM PUBLIC;
   

5. Revoking the privilege for selecting form the EMPLOYEE table from the user IVAN, that was granted by the user ALEX:

     |REVOKE SELECT ON TABLE EMPLOYEE
     |  FROM USER IVAN GRANTED BY ALEX;
   

6. Revoking the privilege for updating the FIRST_NAME and LAST_NAME columns of the EMPLOYEE table from the user IVAN:

     |REVOKE UPDATE (FIRST_NAME, LAST_NAME) ON TABLE EMPLOYEE
     |  FROM USER IVAN;
   

7. Revoking the privilege for inserting records into the EMPLOYEE_PROJECT table from the ADD_EMP_PROJ procedure:

     |REVOKE INSERT ON EMPLOYEE_PROJECT
     |  FROM PROCEDURE ADD_EMP_PROJ;
   

8. Revoking the privilege for executing the procedure ADD_EMP_PROJ from the MANAGER role:

     |REVOKE EXECUTE ON PROCEDURE ADD_EMP_PROJ
     |  FROM ROLE MANAGER;
   

9. Revoking the privilege to grant the EXECUTE privilege for the function GET_BEGIN_DATE to other users from the role MANAGER:

     |REVOKE GRANT OPTION FOR EXECUTE
     |  ON FUNCTION GET_BEGIN_DATE
     |  FROM ROLE MANAGER;
   

10. Revoking the EXECUTE privilege on the package DATE_UTILS from user ALEX:

      |REVOKE EXECUTE ON PACKAGE DATE_UTILS
      |  FROM USER ALEX;
    

11. Revoking the USAGE privilege on the sequence GEN_AGE from the role MANAGER:

      |REVOKE USAGE ON SEQUENCE GEN_AGE
      |  FROM ROLE MANAGER;
    

12. Revoking the USAGE privilege on the sequence GEN_AGE from the trigger TR_AGE_BI:

      |REVOKE USAGE ON SEQUENCE GEN_AGE
      |  FROM TRIGGER TR_AGE_BI;
    

13. Revoking the USAGE privilege on the exception E_ACCESS_DENIED from the package PKG_BILL:

      |REVOKE USAGE ON EXCEPTION E_ACCESS_DENIED
      |  FROM PACKAGE PKG_BILL;
    

14. Revoking the privilege to create tables from user JOE:

      |REVOKE CREATE TABLE
      |  FROM USER Joe;
    

15. Revoking the privilege to alter any procedure from user JOE:

      |REVOKE ALTER ANY PROCEDURE
      |  FROM USER Joe;
    

16. Revoking the privilege to create databases from user SUPERUSER:

      |REVOKE CREATE DATABASE
      |  FROM USER Superuser;
    

17. Revoking the DIRECTOR and MANAGER roles from the user IVAN:

      |REVOKE DIRECTOR, MANAGER FROM USER IVAN;
    

18. Revoke from the user ALEX the privilege to grant the MANAGER role to other users:

      |REVOKE ADMIN OPTION FOR MANAGER FROM USER ALEX;
    

19. Revoking all privileges (including roles) on all objects from the user IVAN:

      |REVOKE ALL ON ALL
      |  FROM USER IVAN;
    

    After this statement is executed by an administrator, the user IVAN will have no privileges whatsoever, except those granted through PUBLIC.

20. Revoking the DEFAULT property of the DIRECTOR role from user ALEX, while the role itself remains granted:

      |REVOKE DEFAULT DIRECTOR
      |  FROM USER ALEX;
    

See alsoGRANT