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

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