FirebirdSQL logo

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