The TO clause is used for listing the users, roles and database objects (procedures, triggers and views) that are to be granted the privileges enumerated in privileges. The clause is mandatory.

The optional USER and ROLE keywords in the TO clause allow you to specify exactly who or what is granted the privilege. 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 granted to the user without further checking.

Firebird has a predefined user named PUBLIC, that represents all users. Privileges for operations on a particular object that are granted to the user PUBLIC can be exercised by any user that has been authenticated at login.

The optional WITH GRANT OPTION clause allows the users specified in the user list to grant the privileges specified in the privilege list to other users.

By default, when privileges are granted in a database, the current user is recorded as the grantor. The GRANTED BY clause enables the current user to grant those privileges as another user.

If the REVOKE statement is used, it will fail if the current user is not the user that was named in the GRANTED BY clause.

The clauses GRANTED BY and AS can be used only by the database owner and administrators. The object owner cannot use it unless he also has administrator privileges.

In theory, one GRANT statement grants one privilege to one user or object. In practice, the syntax allows multiple privileges to be granted to multiple users in one GRANT statement.

Syntax extract

   |...
   |<privileges> ::= ALL [PRIVILEGES] | <privilege_list>
   | 
   |<privilege_list> ::= {<privilege> [, <privilege> [, … ] ] }
   | 
   |<privilege> ::=
   |    SELECT
   |  | DELETE
   |  | INSERT
   |  | UPDATE [(col [, col …])]
   |  | REFERENCES [(col [, col …])]

The EXECUTE privilege applies to stored procedures. It allows the grantee to execute the stored procedure and, if applicable, to retrieve its output. In the case of selectable stored procedures, it acts somewhat like a SELECT privilege, insofar as this style of stored procedure is executed in response to a SELECT statement.

ExampleGranting the EXECUTE privilege on a stored procedure to a role:

  |GRANT EXECUTE ON PROCEDURE ADD_EMP_PROJ
  |  TO ROLE MANAGER;

Assigning a role is similar to granting a privilege. One or more roles can be assigned to one or more users, including the user PUBLIC, using one GRANT statement.

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.

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.

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.

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.

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.

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 alsoSection 11.2.2.1, “GRANT”