13.6 Statements for Revoking Privileges

A REVOKE statement is used for revoking privileges — including roles — from users and other database objects.

13.6.1 REVOKE

Used forRevoking privileges or role assignments

Available inDSQL, ESQL

Syntax (revoking privileges)

REVOKE [GRANT OPTION FOR] <privileges>
  FROM <grantee_list>
  [{GRANTED BY | AS} [USER] grantor]
 
<privileges> ::=
  !! See GRANT syntax !!

Syntax (revoking roles)

REVOKE [ADMIN OPTION FOR] <role_granted_list>
  FROM <role_grantee_list>
  [{GRANTED BY | AS} [USER] grantor]
 
<role_granted_list> ::=
  !! See GRANT syntax !!
 
<role_grantee_list> ::=
  !! See GRANT syntax !!

Syntax (revoking all)

REVOKE ALL ON ALL FROM <grantee_list>
 
<grantee_list> ::=
  !! See GRANT syntax !!

Table 13.6.1.1 REVOKE Statement Parameters

The REVOKE statement revokes privileges that were granted using the GRANT statement from users, roles, and other database objects. See GRANT for detailed descriptions of the various types of privileges.

Only the user who granted the privilege can revoke it.

13.6.1.1 The DEFAULT Clause

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

13.6.1.2 The FROM Clause

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.

Tip

• Although the USER keyword is optional, it is advisable to use them in order to avoid ambiguity with roles.

• The REVOKE statement does not check for the existence of the user from which the privileges are being revoked.

• When revoking a privilege from a database object other than USER or ROLE, you must specify its object type

Revoking Privileges from user PUBLIC

Privileges that were granted to the special user named PUBLIC must be revoked from the user PUBLIC. User PUBLIC provides a way to grant privileges to all users at once, but it is not a group of users.

13.6.1.3 Revoking the GRANT OPTION

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.

13.6.1.4 Removing the Privilege to One or More Roles

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.

13.6.1.5 Revoking Privileges That Were GRANTED BY

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.

Note

Not even the owner of a role can use GRANTED BY unless they have administrative privileges.

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

13.6.1.6 Revoking ALL ON ALL

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.

Note

The GRANTED BY clause is not supported

13.6.1.7 Examples using REVOKE

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 slecting 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