13.5 Statements for Granting Privileges

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

13.5.1 GRANT

Used forGranting privileges and assigning roles

Available inDSQL, ESQL

Syntax (granting privileges)

GRANT <privileges>
  TO <grantee_list>
  [WITH GRANT OPTION]
  [{GRANTED BY | AS} [USER] grantor]
 
<privileges> ::=
    <table_privileges> | <execute_privilege>
  | <usage_privilege>  | <ddl_privileges>
  | <db_ddl_privilege>
 
<table_privileges> ::=
  {ALL [PRIVILEGES] | <table_privilege_list> }
    ON [TABLE] {table_name | view_name}
 
<table_privilege_list> ::=
  <table_privilege> [, <tableprivilege> ...]
 
<table_privilege> ::=
    SELECT | DELETE | INSERT
  | UPDATE [(col [, col ...])]
  | REFERENCES [(col [, col ...)]
 
<execute_privilege> ::= EXECUTE ON
  { PROCEDURE proc_name | FUNCTION func_name
  | PACKAGE package_name }
 
<usage_privilege> ::= USAGE ON
  { EXCEPTION exception_name
  | {GENERATOR | SEQUENCE} sequence_name }
 
<ddl_privileges> ::=
  {ALL [PRIVILEGES] | <ddl_privilege_list>} <object_type>
 
<ddl_privilege_list> ::=
  <ddl_privilege> [, <ddl_privilege> ...]
 
<ddl_privilege> ::= CREATE | ALTER ANY | DROP ANY
 
<object_type> ::=
    CHARACTER SET | COLLATION | DOMAIN | EXCEPTION
  | FILTER | FUNCTION | GENERATOR | PACKAGE
  | PROCEDURE | ROLE | SEQUENCE | TABLE | VIEW
 
<db_ddl_privileges> ::=
  {ALL [PRIVILEGES] | <db_ddl_privilege_list>} {DATABASE | SCHEMA}
 
<db_ddl_privilege_list> ::=
  <db_ddl_privilege> [, <db_ddl_privilege> ...]
 
<db_ddl_privilege> ::= CREATE | ALTER | DROP
 
<grantee_list> ::= <grantee> [, <grantee> ...]
 
<grantee> ::=
    PROCEDURE proc_name  | FUNCTION func_name
  | PACKAGE package_name | TRIGGER trig_name
  | VIEW view_name       | ROLE role_name
  | [USER] username      | GROUP Unix_group
  | SYSTEM PRIVILEGE <sys_privilege>
 
<sys_privilege> ::=
 !! See CREATE ROLE !!

Syntax (granting roles)

GRANT <role_granted_list>
  TO <role_grantee_list>
  [WITH ADMIN OPTION]
  [{GRANTED BY | AS} [USER] grantor]
 
<role_granted_list> ::=
  <role_granted> [, <role_granted ...]
 
<role_granted> ::= [DEFAULT] role_name
 
<role_grantee_list> ::=
  <role_grantee> [, <role_grantee> ...]
 
<role_grantee> ::=
    user_or_role_name
  | USER username
  | ROLE role_name

Table 13.5.1.1 GRANT Statement Parameters

The GRANT statement grants one or more privileges on database objects to users, roles, or other database objects.

A regular, authenticated user has no privileges on any database object until they are explicitly granted to that individual user, to a role granted to the user as a default role, or to all users bundled as the user PUBLIC. When an object is created, only its creator (the owner) and administrators have privileges to 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 in this section.

Note

SCHEMA is currently a synonym for DATABASE; this may change in a future version, so we recommend to always use DATABASE

13.5.1.1 The TO Clause

The TO clause specifies the users, roles, and other database objects that are to be granted the privileges enumerated in privileges. The clause is mandatory.

The optional USER keyword 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 first checks for a role with this name and, if there is no such role, the privileges are granted to the user with that name without further checking.

Tip

It is recommended to always explicitly specify USER and ROLE to avoid ambiguity. Future versions of Firebird may make USER mandatory.

Important

• When a GRANT statement is executed, the security database is not checked for the existence of the grantee user. This is not a bug: SQL permissions are concerned with controlling data access for authenticated users, both native and trusted, and trusted operating system users are not stored in the security database.

• When granting a privilege to a database object other than user or role, such as a procedure, trigger or view, you must specify the object type.

• Although the USER keyword is optional, it is advisable to use it, in order to avoid ambiguity with roles.

• Privileges granted to a system privilege will be applied when the user is logged in with a role that has that system privilege.

13.5.1.1.1 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 or role that requires those privileges. A role can also be granted to a list of users or roles.

The role must exist before privileges can be granted to it. See CREATE ROLE for the syntax and rules. The role is maintained by granting privileges to it and, when required, revoking privileges from it. When 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.

Unless the role is granted as a default role, a user that is granted a role must explicitly specify that role, either with their login credentials or activating it using SET ROLE, in order to exercise the associated privileges. Any other privileges granted to the user or received through default roles are not affected by explicitly specifying a role.

More than one role can be granted to the same user. Although only one role can be explicitly specified, multiple roles can be active for a user, either as default roles, or as roles granted to the current role.

A role can be granted to a user or to another role.

13.5.1.1.2 Cumulative Roles

The ability to grant roles to other roles and default roles results in so-called cumulative roles. Multiple roles can be active for a user, and the user receives the cumulative privileges of all those roles.

When a role is explicitly specified on connect or using SET ROLE, the user will assume all privileges granted to that role, including those privileges granted to the secondary roles (including roles granted on that secondary role, etc). Or in other words, when the primary role is explicitly specified, the secondary roles are also activated. The function RDB$ROLE_IN_USE can be used to check if a role is currently active.

See also Section 13.5.1.1.3, Default Roles for the effects of DEFAULT with cumulative roles, and Section 13.5.1.10.1, The WITH ADMIN OPTION Clause for effects on granting.

13.5.1.1.3 Default Roles

A role can be granted as a default role by prefixing the role with DEFAULT in the GRANT statement. Granting roles as a default role to users simplifies management of privileges, as this makes it possible to group privileges on a role and granting that group of privileges to a user without requiring the user to explicitly specify the role. Users can receive multiple default roles, granting them all privileges of those default roles.

The effects of a default role depend on whether the role is granted to a user or to another role:

• When a role is granted to a user as a default role, the role will be activated automatically, and its privileges will be applied to the user without the need to explicitly specify the role.

  Roles that are active by default are not returned from CURRENT_ROLE, but the function RDB$ROLE_IN_USE can be used to check if a role is currently active.

• When a role is granted to another role as a default role, the rights of that role will only be automatically applied to the user if the primary role is granted as a default role to the user, otherwise the primary role needs to be specified explicitly (in other words, it behaves the same as when the secondary role was granted without the DEFAULT clause).

  For a string of granted roles, all roles need to be granted as a default role for them to be applied automatically. That is, for GRANT DEFAULT ROLEA TO ROLE ROLEB, GRANT ROLEB TO ROLE ROLEC, GRANT DEFAULT ROLEC TO USER USER1 only ROLEC is active by default for USER1. To assume the privileges of ROLEA and ROLEB, ROLEC needs to be explicitly specified, or ROLEB needs to be granted DEFAULT to ROLEC.

13.5.1.1.4 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 authenticated user.

Important

If privileges are granted to the user PUBLIC, they should be revoked from the user PUBLIC as well.

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

Caution

It is possible to assign this option to the user PUBLIC. Do not do this!

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

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

The GRANTED BY (and AS) clause can be used only by the database owner and other administrators. The object owner cannot use GRANTED BY unless they also have administrator privileges.

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

13.5.1.4 Privileges on Tables and Views

For tables and views, unlike other metadata objects, it is possible to grant several privileges at once.

List of Privileges on Tables

SELECT

Permits the user or object to SELECT data from the table or view

INSERT

Permits the user or object to INSERT rows into the table or view

DELETE

Permits the user or object to DELETE rows from the table or view

UPDATE

Permits the user or object to UPDATE rows in the table or view, optionally restricted to specific columns

REFERENCES

Permits the user or object to reference the table via a foreign key, optionally restricted to the specified columns. If the primary or unique key referenced by the foreign key of the other table is composite then all columns of the key must be specified.

ALL [PRIVILEGES]

Combines SELECT, INSERT, UPDATE, DELETE and REFERENCES privileges in a single package

13.5.1.4.1 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;

13.5.1.5 The EXECUTE Privilege

The EXECUTE privilege applies to stored procedures, stored functions (including UDFs), and packages. It allows the grantee to execute the specified object, 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.

Note

For packages, the EXECUTE privilege can only be granted for the package as a whole, ot for individual subroutines.

13.5.1.5.1 Examples of Granting the EXECUTE Privilege

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

   GRANT EXECUTE ON PROCEDURE ADD_EMP_PROJ
     TO ROLE MANAGER;

2. Granting the EXECUTE privilege on a stored function to a role:

   GRANT EXECUTE ON FUNCTION GET_BEGIN_DATE
     TO ROLE MANAGER;

3. Granting the EXECUTE privilege on a package to user PUBLIC:

   GRANT EXECUTE ON PACKAGE APP_VAR
     TO USER PUBLIC;

4. Granting the EXECUTE privilege on a function to a package:

   GRANT EXECUTE ON FUNCTION GET_BEGIN_DATE
     TO PACKAGE APP_VAR;

13.5.1.6 The USAGE Privilege

To be able to use metadata objects other than tables, views, stored procedures or functions, triggers and packages, it is necessary to grant the user (or database object like trigger, procedure or function) the USAGE privilege on these objects.

Since Firebird executes stored procedures and functions, triggers, and package routines with the privileges of the caller, it is necessary that either the user or otherwise the routine itself has been granted the USAGE privilege.

Note

In Firebird 3.0 and Firebird 4.0, the USAGE privilege is only available for exceptions and sequences (in gen_id(*gen_name*, *n*) or `next value for *gen_name*). Support for the USAGE privilege for other metadata objects may be added in future releases.

Note

For sequences (generators), the USAGE privilege only grants the right to increment the sequence using the GEN_ID function or NEXT VALUE FOR. The SET GENERATOR statement is a synonym for ALTER SEQUENCE …​ RESTART WITH …​, and is considered a DDL statement. By default, only the owner of the sequence and administrators have the rights to such operations. The right to set the initial value of any sequence can be granted with GRANT ALTER ANY SEQUENCE, which is not recommend for general users.

13.5.1.6.1 Examples of Granting the USAGE Privilege

1. Granting the USAGE privilege on a sequence to a role:

   GRANT USAGE ON SEQUENCE GEN_AGE
     TO ROLE MANAGER;

2. Granting the USAGE privilege on a sequence to a trigger:

   GRANT USAGE ON SEQUENCE GEN_AGE
     TO TRIGGER TR_AGE_BI;

3. Granting the USAGE privilege on an exception to a package:

   GRANT USAGE ON EXCEPTION
     TO PACKAGE PKG_BILL;

13.5.1.7 DDL Privileges

By default, only administrators can create new metadata objects; altering or dropping these objects is restricted to the owner of the object (its creator) and administrators. DDL privileges can be used to grant privileges for these operations to other users.

Available DDL Privileges

CREATE

Allows creation of an object of the specified type

ALTER ANY

Allows modification of any object of the specified type

DROP ANY

Allows deletion of any object of the specified type

ALL [PRIVILEGES]

Combines the CREATE, ALTER ANY and DROP ANY privileges for the specified type

Note

There are no separate DDL privileges for triggers and indexes. The necessary privileges are inherited from the table or view. Creating, altering or dropping a trigger or index requires the ALTER ANY TABLE or ALTER ANY VIEW privilege.

13.5.1.7.1 Examples of Granting DDL Privileges

1. Allow user JOE to create tables

   GRANT CREATE TABLE
     TO USER Joe;

2. Allow user JOE to alter any procedure

   GRANT ALTER ANY PROCEDURE
     TO USER Joe;

13.5.1.8 Database DDL Privileges

The syntax for granting privileges to create, alter or drop a database deviates from the normal syntax of granting DDL privileges for other object types.

Available Database DDL Privileges

CREATE

Allows creation of a database

ALTER

Allows modification of the current database

DROP

Allows deletion of the current database

ALL [PRIVILEGES]

Combines the ALTER and DROP privileges. ALL does not include the CREATE privilege.

The ALTER DATABASE and DROP DATABASE privileges apply only to the current database, whereas DDL privileges ALTER ANY and DROP ANY on other object types apply to all objects of the specified type in the current database. The privilege to alter or drop the current database can only be granted by administrators.

The CREATE DATABASE privilege is a special kind of privilege as it is saved in the security database. A list of users with the CREATE DATABASE privilege is available from the virtual table SEC$DB_CREATORS. Only administrators in the security database can grant the privilege to create a new database.

Note

SCHEMA is currently a synonym for DATABASE; this may change in a future version, so we recommend to always use DATABASE

13.5.1.9 Examples of Granting Database DDL Privileges

1. Granting SUPERUSER the privilege to create databases:

   GRANT CREATE DATABASE
     TO USER Superuser;

2. Granting JOE the privilege to execute ALTER DATABASE for the current database:

   GRANT ALTER DATABASE
     TO USER Joe;

3. Granting FEDOR the privilege to drop the current database:

   GRANT DROP DATABASE
     TO USER Fedor;

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

13.5.1.10.1 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 or roles.

Caution

It is possible to assign this option to PUBLIC. Do not do this!

For cumulative roles, a user can only exercise the WITH ADMIN OPTION of a secondary role if all intermediate roles are also granted WITH ADMIN OPTION. That is, GRANT ROLEA TO ROLE ROLEB WITH ADMIN OPTION, GRANT ROLEB TO ROLE ROLEC, GRANT ROLEC TO USER USER1 WITH ADMIN OPTION only allows USER1 to grant ROLEC to other users or roles, while using GRANT ROLEB TO ROLE ROLEC WITH ADMIN OPTION allows USER1 to grant ROLEA, ROLEB and ROLEC to other users.

13.5.1.10.2 Examples of Role Assignment

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

   GRANT DIRECTOR, MANAGER
     TO USER IVAN;

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

   GRANT MANAGER
     TO USER ALEX WITH ADMIN OPTION;

3. Assigning the DIRECTOR role to user ALEX as a default role:

   GRANT DEFAULT DIRECTOR
     TO USER ALEX;

4. Assigning the MANAGER role to role DIRECTOR:

   GRANT MANAGER
     TO ROLE DIRECTOR;

See alsoREVOKE