11.2.3. Statements for Revoking Privileges

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

REVOKE

Used for

Revoking privileges or role assignments

Available in

DSQL, ESQL

Syntax

REVOKE [GRANT OPTION FOR]
  { <privileges> ON [TABLE] {tablename | viewname} |
    EXECUTE ON PROCEDURE procname }
  FROM <grantee_list>
  [{GRANTED BY | AS} [USER] grantor]
REVOKE [ADMIN OPTION FOR] <role_granted>
  FROM {PUBLIC | <role_grantee_list>}
  [{GRANTED BY | AS} [USER] grantor]
REVOKE ALL ON ALL FROM <grantee_list>
<privileges> ::= ALL [PRIVILEGES] | <privilege_list>
<privilege_list> ::= {<privilege> [, <privilege> [, … ] ] }
<privilege> ::=
    SELECT
  | DELETE
  | INSERT
  | UPDATE [(col [, col …])]
  | REFERENCES [(col [, col …])]
<grantee_list> ::= {<grantee> [, <grantee> [, …] ]}
<grantee>  ::=
  [USER] username | [ROLE] rolename | GROUP Unix_group
  | PROCEDURE procname | TRIGGER trigname | VIEW viewname | PUBLIC
<role_granted> ::= rolename [, rolename …]
<role_grantee_list> ::= [USER] <role_grantee> [,[USER] <role_grantee> [, …]]
<role_grantee> ::= {username | PUBLIC }

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

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

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 privileges for reading and inserting into the SALES

   REVOKE SELECT, INSERT ON TABLE SALES FROM USER ALEX;

2. Revoking the privilege for reading 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 authority 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 reading the COUNTRY table and the authority 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 reading 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 DIRECTOR and MANAGER roles from the user IVAN:

   REVOKE DIRECTOR, MANAGER FROM USER IVAN;

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

    REVOKE ADMIN OPTION FOR MANAGER FROM USER IVAN;

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

    REVOKE ALL ON ALL FROM IVAN;

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

See also

GRANT