14.5. Statements for Granting Privileges

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

14.5.1. GRANT

Grants privileges and assigns 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 14.8. GRANT Statement Parameters
ParameterDescription

grantor

The user granting the privilege(s)

table_name

The name of a table

view_name

The name of a view

col

The name of table column

proc_name

The name of a stored procedure

func_name

The name of a stored function (or UDF)

package_name

The name of a package

exception_name

The name of an exception

sequence_name

The name of a sequence (generator)

object_type

The type of metadata object

trig_name

The name of a trigger

role_name

Role name

username

The username to which the privileges are granted to or to which the role is assigned. If the USER keyword is absent, it can also be a role.

Unix_group

The name of a user group in a POSIX operating system

sys_privilege

A system privilege

user_or_role_name

Name of a user or role

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

14.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, 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.

14.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, 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.

14.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 14.5.1.1.3, “Default Roles” for the effects of DEFAULT with cumulative roles, and Section 14.5.1.10.1, “The WITH ADMIN OPTION Clause” for effects on granting.

14.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 linked list 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.

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

14.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!

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

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