Statements For Granting 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`

Statements For Granting Privileges

Statements for Granting Privileges

GRANT

`GRANT`