GRANT

Reference and syntax for the GRANT command.

Grants permission or assignment to a role. GRANT can also be used to assign a role to another role or a user.

For more information, see Role-based access control.

GRANT PRIVILEGE

Grants a permission to a role.

Only an account_admin or a role owner can grant a permission to a role. To grant a permission, you must first have that permission granted to you.

Syntax

GRANT <permission> ON <object_type> <object_name> [IN <object_type> <object_name>] TO <role_name>
GRANT <account_permission> TO <role_name>

Account-level permissions such as CREATE DATABASE or USAGE ANY DATABASE apply to the whole deployment and take no ON clause. For the list, see Account permissions.

To grant SELECT on specific columns of a table, use the column-list syntax:

GRANT SELECT (<column1> [, <column2>, ...]) ON TABLE <table_name> TO <role_name>

Column-list syntax is only valid for SELECT on TABLE objects. For more information, see Column-level security.

Parameters

Parameter	Description
<permission>	The name of the permission to grant to a role. Available permissions vary depending on the object that they apply to. For a full list, see Permissions.
<account_permission>	An account-level permission. It applies to every current and future object of its kind and is granted without an ON clause.
<object_type>	The type of object to grant permissions on.
<object_name>	The name of the object to grant permissions on.
<role_name>	The name of the role to grant the permission to.

Examples

Grant USAGE on a single database

The following code example grants the USAGE privilege on the db database to the role user_role, allowing it to access the database:

GRANT USAGE ON DATABASE db TO user_role;

Grant USAGE on all databases

The following code example grants the USAGE privilege on all current and future databases to the role user_role, allowing access to them:

GRANT USAGE ANY DATABASE TO user_role;

Grant access to a database, schema, and a specific table

The following code example grants the role user_role access to the db database, the public schema within the db database, and permission to read data from the my_table table in the public schema:

GRANT USAGE ON DATABASE db TO user_role;
GRANT USAGE ON SCHEMA public IN DATABASE db TO user_role;
USE DATABASE db;
GRANT SELECT ON TABLE my_table IN SCHEMA public TO user_role;

Grant SELECT on specific columns of a table

The following code example grants the role user_role permission to read only the name and email columns from my_table:

GRANT SELECT (name, email) ON TABLE my_table TO user_role;

Column-level grants are additive. If a subsequent grant adds more columns, the role can read the union of all granted columns. For more information, see Column-level security.

Grant access to a database, schema, and all operations on a specific table

The following code example grants the role user_role access to the db database, the public schema within the db database, and all permissions on the my_table table in the public schema:

GRANT USAGE ON DATABASE db TO user_role;
GRANT USAGE ON SCHEMA public IN DATABASE db TO user_role;
USE DATABASE db;
GRANT ALL ON TABLE my_table IN SCHEMA public TO user_role;

Grant access to all existing and future tables or views in a schema

The following code example grants user_role access to the db database, the public schema within the db database, and permission to query all existing and future tables or views in the public schema:

GRANT USAGE ON DATABASE db TO user_role;
GRANT USAGE ON SCHEMA public IN DATABASE db TO user_role;
GRANT SELECT ANY ON SCHEMA public IN DATABASE db TO user_role;

GRANT ROLE

Grants a role to either a user or another role, allowing the recipient to inherit the permissions associated with the granted role.

Syntax

GRANT ROLE <role_name> TO { USER <user_name> | ROLE <role_name_2> }

Parameters

Parameter	Description
<role_name>	The name of the role to grant.
<user_name>	The name of the user to grant <role_name> to.
<role_name_2>	The name of the role to assign the role to.

Examples

Grant a role to another role

The following code example assigns the role_name role to role_name_2, allowing role_name_2 to inherit all the permissions granted to role_name:

GRANT ROLE role_name TO ROLE role_name_2;

Grant a role to a user

The following code example assigns the role_name role to user_name, allowing the user to inherit all the permissions granted to role_name:

GRANT ROLE role_name TO USER user_name;