FirebirdSQL logo
 ROLEStatements for Revoking Privileges 

GRANT

Grants privileges and assigns roles

Available in

DSQL, 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 1. GRANT Statement Parameters
Parameter Description

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

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.