REVOKE

    Used for

    Revoking privileges or role assignments

    Available in

    DSQL, ESQL

    Syntax

    The REVOKE statement is used for revoking privileges from users, roles, stored procedures, triggers and views that were granted using the GRANT statement. See GRANT for detailed descriptions of the various types of privileges.

    Only the user who granted the privilege can revoke it.

    The FROM Clause

    The FROM clause is used to specify the list of users, roles and database objects (procedures, triggers and views) that will have the enumerated privileges revoked. The optional USER and ROLE keywords 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 checks for a role with this name and, if there is none, the privileges are revoked from the user without further checking.

    Revoking the GRANT OPTION

    The optional GRANT OPTION FOR clause revokes the user’s privilege to grant privileges on the table, view, trigger or stored procedure to other users or to roles. It does not revoke the privilege with which the grant option is associated.

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

    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. To revoke a privilege that was obtained by this method, the current user must be logged in either with full administrative privileges or as the user designated as grantor by that GRANTED BY clause.

    Revoking ALL ON ALL

    If the current user is logged in with full administrator privileges in the database, the statement

    1. REVOKE ALL ON ALL FROM <grantee_list>

    can be used to revoke all privileges (including role memberships) on all objects from one or more users and/or roles. All privileges for the user will be removed, regardless of who granted them. It is a quick way to “clear” privileges when access to the database must be blocked for a particular user or role.

    If the current user is not logged in as an administrator, the only privileges revoked will be those that were granted originally by that user.

    The REVOKE ALL ON ALL statement cannot be used to revoke privileges that have been granted to stored procedures, triggers or views.

    Examples using REVOKE

    1. Revoking the privilege for reading the CUSTOMER table from the MANAGER and ENGINEER roles and from the user IVAN:

      1. REVOKE SELECT ON TABLE CUSTOMER
      2. FROM ROLE MANAGER, ROLE ENGINEER, USER IVAN;

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

    3. Revoking the privilege for reading the COUNTRY table and the authority to reference the NAME column of the COUNTRY table from any user, via the special user :

      1. REVOKE SELECT, REFERENCES (NAME) ON TABLE COUNTRY
      2.   FROM PUBLIC;

    4. Revoking the privilege for reading the EMPLOYEE table from the user IVAN, that was granted by the user ALEX:

      1. REVOKE SELECT ON TABLE EMPLOYEE
      2.   FROM USER IVAN GRANTED BY ALEX;

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

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

      1. REVOKE EXECUTE ON PROCEDURE ADD_EMP_PROJ
      2.   FROM ROLE MANAGER;

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

      1. REVOKE DIRECTOR, MANAGER FROM USER IVAN;

    8. Revoke from the user ALEX the authority to assign the MANAGER role to other users:

      1. REVOKE ADMIN OPTION FOR MANAGER FROM USER IVAN;

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

      After this statement is executed, the user will have no privileges whatsoever.

    GRANT