FirebirdSQL logo
  • Anglaiskeyboard_arrow_down
 Statements for Granting PrivilegesMapping of Users to Objects 

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

REVOKE

Revokes privileges or role assignments

Available in

DSQL, 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 1. REVOKE Statement Parameters
Parameter Description

grantor

The grantor user on whose behalf the privilege(s) are being revoked

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.

docnext count = 7

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.

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

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.

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

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 with ALL ON ALL.

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

GRANT