GRANT Statement (grant_statement)

The GRANT statement (grant_statement) grants privileges for tables, individual columns, roles, and schemas, the SELECT privilege for sequences, and the execution privilege (EXECUTE privilege) for database procedures or database functions.

Structure

Syntax Syntax

<grant_statement> ::=
  GRANT <priv_spec>,... TO <grantee>,... [WITH GRANT OPTION]
| GRANT <schema_priv_spec> TO <grantee>,...
| GRANT EXECUTE ON <dbproc_name> TO <grantee>,...
| GRANT EXECUTE ON <dbfunction_name> TO <grantee>,...
| GRANT SELECT ON <sequence_name> TO <grantee>,... [WITH GRANT OPTION]

<grantee> ::=
  <user_name>
| <usergroup_name>
| <role_name>
| PUBLIC

End of the code.

Examples

SQL Tutorial, Database Users and Their Privileges, Roles

Explanation

The privileges in the privilege specification (priv_spec or schema_priv_spec) are granted to the database users, user groups and roles specified in the grantee list.

<grantee>

The owner of an object for which privileges are granted is not allowed to be contained in the grantee list. A database user in the grantee list must not denote a member of a user group.

<role_name>

If a role is granted to a database user or user group, it extends the set of roles which can be activated for this database user or user group. The database user activates the role either with the SET statement or by including the role in the set of roles automatically activated when a session was opened with the ALTER USER statement or ALTER USERGROUP statement.

A cycle may not be created when a role is assigned to a role, meaning:

A role may not be assigned to itself.
If a role R1 is assigned to a role R2, then R2 may not be assigned to R1.
If a role R1 is assigned to a role R2 and R2 is assigned to a role R3, then R3 may not be assigned to either R2 or R1.
etc.

PUBLIC

The listed privileges are granted to all database users, both to current ones and to any created later.

A role cannot be assigned to PUBLIC.

WITH GRANT OPTION

Users or user groups identified as grantee are allowed to pass on their privileges to other users. The current user must have the relevant authorization to pass on these privileges.

The WITH GRANT OPTION cannot be specified if grantee identifies a role.

GRANT <schema_priv_spec>

GRANT CREATEIN ON is used to grant the user identified by grantee authorization to create database objects in the specified schema.

GRANT DROPIN ON is used to grant the user identified by grantee authorization to delete database objects in the specified schema.

GRANT EXECUTE ON

GRANT EXECUTE ON allows the user identified by grantee to execute the specified database procedure or database function. The current user must be the owner of the database procedure or database function.

GRANT SELECT ON

GRANT SELECT ON allows the user identified by grantee to use the specified sequence.

More Information

Privileges: Overview