11.2.2. Statements for Granting Privileges

A GRANT statement is used for granting privileges — including roles — to users and other database objects.

GRANT

Used for

Granting privileges and assigning roles

Available in

DSQL, ESQL

Syntax

GRANT
  { <privileges> ON [TABLE] {tablename | viewname}
  | EXECUTE ON PROCEDURE procname }
  TO <grantee_list>
  [WITH GRANT OPTION]} | [{GRANTED BY | AS} [USER] grantor]
GRANT <role_granted>
  TO <role_grantee_list> [WITH ADMIN OPTION]
  [{GRANTED BY | AS} [USER] grantor]
<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 }

A GRANT statement grants one or more privileges on database objects to users, roles, stored procedures, triggers or views.

A regular, authenticated user has no privileges on any database object until they are explicitly granted, either to that individual user or to all users bundled as the user PUBLIC. When an object is created, only the user who has created it (the owner) and administrators have privileges for it and can grant privileges to other users, roles or objects.

Different sets of privileges apply to different types of metadata objects. The different types of privileges will be described separately later.

The TO Clause

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.

Packaging Privileges in a ROLE Object

A role is a “container” object that can be used to package a collection of privileges. Use of the role is then granted to each user that requires those privileges. A role can also be granted to a list of users.

The role must exist before privileges can be granted to it. See CREATE ROLE in the DDL chapter for the syntax and rules. The role is maintained by granting privileges to it and, when required, revoking privileges from it. If a role is dropped (see DROP ROLE), all users lose the privileges acquired through the role. Any privileges that were granted additionally to an affected user by way of a different grant statement are retained.

A user that is granted a role must supply that role with his login credentials in order to exercise the associated privileges. Any other privileges granted to the user are not affected by logging in with a role.

More than one role can be granted to the same user but logging in with multiple roles simultaneously is not supported.

A role can be granted only to a user.

The User PUBLIC

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 WITH GRANT OPTION Clause

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.

The GRANTED BY Clause

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.

Alternative Syntax Using AS *username*

The non-standard AS clause is supported as a synonym of the GRANTED BY clause to simplify migration from other database systems.

Privileges on Tables and Views

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 …])]

Examples of GRANT <privilege> on Tables

1. SELECT and INSERT privileges to the user ALEX:

   GRANT SELECT, INSERT ON TABLE SALES
     TO USER ALEX;

2. The SELECT privilege to the MANAGER, ENGINEER roles and to the user IVAN:

   GRANT SELECT ON TABLE CUSTOMER
     TO ROLE MANAGER, ROLE ENGINEER, USER IVAN;

3. All privileges to the ADMINISTRATOR role, together with the authority to grant the same privileges to others:

   GRANT ALL ON TABLE CUSTOMER
     TO ROLE ADMINISTRATOR
     WITH GRANT OPTION;

4. The SELECT and REFERENCES privileges on the NAME column to all users and objects:

   GRANT SELECT, REFERENCES (NAME) ON TABLE COUNTRY
   TO PUBLIC;

5. The SELECT privilege being granted to the user IVAN by the user ALEX:

   GRANT SELECT ON TABLE EMPLOYEE
     TO USER IVAN
     GRANTED BY ALEX;

6. Granting the UPDATE privilege on the FIRST_NAME, LAST_NAME columns:

   GRANT UPDATE (FIRST_NAME, LAST_NAME) ON TABLE EMPLOYEE
     TO USER IVAN;

7. Granting the INSERT privilege to the stored procedure ADD_EMP_PROJ:

   GRANT INSERT ON EMPLOYEE_PROJECT
     TO PROCEDURE ADD_EMP_PROJ;

The EXECUTE Privilege

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.

Example

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

GRANT EXECUTE ON PROCEDURE ADD_EMP_PROJ
  TO ROLE MANAGER;

Assigning Roles

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 WITH ADMIN OPTION Clause

The optional WITH ADMIN OPTION clause allows the users specified in the user list to grant the role[s] specified to other users.

Examples of Role Assignment

1. Assigning the DIRECTOR and MANAGER roles to the user IVAN:

   GRANT DIRECTOR, MANAGER TO USER IVAN;

2. Assigning the ADMIN role to the user ALEX with the authority to assign this role to other users:

   GRANT MANAGER TO USER ALEX WITH ADMIN OPTION;

See also

REVOKE