GRANT - Amazon Redshift

GRANT

Defines access privileges for a user or user group.

Privileges include access options such as being able to read data in tables and views, write data, and create tables. Use this command to give specific privileges for a table, database, schema, function, procedure, language, or column. To revoke privileges from a database object, use the REVOKE command.

You can only GRANT or REVOKE USAGE permissions on an external schema to database users and user groups that use the ON SCHEMA syntax. When using ON EXTERNAL SCHEMA with AWS Lake Formation, you can only GRANT and REVOKE privileges to an AWS Identity and Access Management (IAM) role. For the list of privileges, see the syntax.

For stored procedures, the only privilege that you can grant is EXECUTE.

You can't run GRANT (on an external resource) within a transaction block (BEGIN ... END). For more information about transactions, see Serializable isolation.

Syntax

GRANT { { SELECT | INSERT | UPDATE | DELETE | REFERENCES } [,...] | ALL [ PRIVILEGES ] } ON { [ TABLE ] table_name [, ...] | ALL TABLES IN SCHEMA schema_name [, ...] } TO { username [ WITH GRANT OPTION ] | GROUP group_name | PUBLIC } [, ...] GRANT { { CREATE | TEMPORARY | TEMP } [,...] | ALL [ PRIVILEGES ] } ON DATABASE db_name [, ...] TO { username [ WITH GRANT OPTION ] | GROUP group_name | PUBLIC } [, ...] GRANT { { CREATE | USAGE } [,...] | ALL [ PRIVILEGES ] } ON SCHEMA schema_name [, ...] TO { username [ WITH GRANT OPTION ] | GROUP group_name | PUBLIC } [, ...] GRANT { EXECUTE | ALL [ PRIVILEGES ] } ON { FUNCTION function_name ( [ [ argname ] argtype [, ...] ] ) [, ...] | ALL FUNCTIONS IN SCHEMA schema_name [, ...] } TO { username [ WITH GRANT OPTION ] | GROUP group_name | PUBLIC } [, ...] GRANT { EXECUTE | ALL [ PRIVILEGES ] } ON { PROCEDURE procedure_name ( [ [ argname ] argtype [, ...] ] ) [, ...] | ALL PROCEDURES IN SCHEMA schema_name [, ...] } TO { username [ WITH GRANT OPTION ] | GROUP group_name | PUBLIC } [, ...] GRANT USAGE ON LANGUAGE language_name [, ...] TO { username [ WITH GRANT OPTION ] | GROUP group_name | PUBLIC } [, ...]

The following is the syntax for column-level privileges on Amazon Redshift tables and views.

GRANT { { SELECT | UPDATE } ( column_name [, ...] ) [, ...] | ALL [ PRIVILEGES ] ( column_name [,...] ) } ON { [ TABLE ] table_name [, ...] } TO { username | GROUP group_name | PUBLIC } [, ...]

The following is the syntax for the ASSUMEROLE privilege granted to users and groups with a specified role.

GRANT ASSUMEROLE ON { 'iam_role' [, ...] | ALL } TO { username | GROUP group_name | PUBLIC } [, ...] FOR { ALL | COPY | UNLOAD } [, ...]

The following is the syntax for Redshift Spectrum integration with Lake Formation.

GRANT { SELECT | ALL [ PRIVILEGES ] } ( column_list ) ON EXTERNAL TABLE schema_name.table_name TO { IAM_ROLE iam_role } [, ...] [ WITH GRANT OPTION ] GRANT { { SELECT | ALTER | DROP | DELETE | INSERT } [, ...] | ALL [ PRIVILEGES ] } ON EXTERNAL TABLE schema_name.table_name [, ...] TO { { IAM_ROLE iam_role } [, ...] | PUBLIC } [ WITH GRANT OPTION ] GRANT { { CREATE | ALTER | DROP } [, ...] | ALL [ PRIVILEGES ] } ON EXTERNAL SCHEMA schema_name [, ...] TO { IAM_ROLE iam_role } [, ...] [ WITH GRANT OPTION ]

Parameters

SELECT

Grants privilege to select data from a table or view using a SELECT statement. The SELECT privilege is also required to reference existing column values for UPDATE or DELETE operations.

INSERT

Grants privilege to load data into a table using an INSERT statement or a COPY statement.

UPDATE

Grants privilege to update a table column using an UPDATE statement. UPDATE operations also require the SELECT privilege, because they must reference table columns to determine which rows to update, or to compute new values for columns.

DELETE

Grants privilege to delete a data row from a table. DELETE operations also require the SELECT privilege, because they must reference table columns to determine which rows to delete.

REFERENCES

Grants privilege to create a foreign key constraint. You need to grant this privilege on both the referenced table and the referencing table; otherwise, the user can't create the constraint.

ALL [ PRIVILEGES ]

Grants all available privileges at once to the specified user or user group. The PRIVILEGES keyword is optional.

GRANT ALL ON SCHEMA doesn't grant CREATE privileges for external schemas.

You can grant ALL privilege to a table in an AWS Glue Data Catalog that is enabled for Lake Formation. In this case, individual privileges (such as SELECT, ALTER, and so on) are recorded in the Data Catalog.

ALTER

Grants privilege to alter a table in an AWS Glue Data Catalog that is enabled for Lake Formation. This privilege only applies when using Lake Formation.

DROP

Grants privilege to drop a table in an AWS Glue Data Catalog that is enabled for Lake Formation. This privilege only applies when using Lake Formation.

ASSUMEROLE

Grants privilege to run COPY and UNLOAD commands to users and groups with a specified role. The user or group assumes that role when running the specified command.

ON [ TABLE ] table_name

Grants the specified privileges on a table or a view. The TABLE keyword is optional. You can list multiple tables and views in one statement.

ON ALL TABLES IN SCHEMA schema_name

Grants the specified privileges on all tables and views in the referenced schema.

( column_name [,...] ) ON TABLE table_name

Grants the specified privileges to users, groups, or PUBLIC on the specified columns of the Amazon Redshift table or view.

( column_list ) ON EXTERNAL TABLE schema_name.table_name

Grants the specified privileges to an IAM role on the specified columns of the Lake Formation table in the referenced schema.

ON EXTERNAL TABLE schema_name.table_name

Grants the specified privileges to an IAM role on the specified Lake Formation tables in the referenced schema.

ON EXTERNAL SCHEMA schema_name

Grants the specified privileges to an IAM role on the referenced schema.

ON iam_role

Grants the specified privileges to an IAM role.

TO username

Indicates the user receiving the privileges.

TO IAM_ROLE iam_role

Indicates the IAM role receiving the privileges.

WITH GRANT OPTION

Indicates that the user receiving the privileges can in turn grant the same privileges to others. WITH GRANT OPTION can not be granted to a group or to PUBLIC.

GROUP group_name

Grants the privileges to a user group.

PUBLIC

Grants the specified privileges to all users, including users created later. PUBLIC represents a group that always includes all users. An individual user's privileges consist of the sum of privileges granted to PUBLIC, privileges granted to any groups that the user belongs to, and any privileges granted to the user individually.

Granting PUBLIC to a Lake Formation EXTERNAL TABLE results in granting the privilege to the Lake Formation everyone group.

CREATE

Depending on the database object, grants the following privileges to the user or user group:

  • For databases, CREATE allows users to create schemas within the database.

  • For schemas, CREATE allows users to create objects within a schema. To rename an object, the user must have the CREATE privilege and own the object to be renamed.

  • CREATE ON SCHEMA isn't supported for Amazon Redshift Spectrum external schemas. To grant usage of external tables in an external schema, grant USAGE ON SCHEMA to the users that need access. Only the owner of an external schema or a superuser is permitted to create external tables in the external schema. To transfer ownership of an external schema, use ALTER SCHEMA to change the owner.

TEMPORARY | TEMP

Grants the privilege to create temporary tables in the specified database. To run Amazon Redshift Spectrum queries, the database user must have permission to create temporary tables in the database.

Note

By default, users are granted permission to create temporary tables by their automatic membership in the PUBLIC group. To remove the privilege for any users to create temporary tables, revoke the TEMP permission from the PUBLIC group. Then explicitly grant the permission to create temporary tables to specific users or groups of users.

ON DATABASE db_name

Grants the specified privileges on a database.

USAGE

Grants USAGE privilege on a specific schema, which makes objects in that schema accessible to users. Specific actions on these objects must be granted separately (for example, SELECT or UPDATE privileges on tables). By default, all users have CREATE and USAGE privileges on the PUBLIC schema.

ON SCHEMA schema_name

Grants the specified privileges on a schema.

GRANT CREATE ON SCHEMA and the CREATE privilege in GRANT ALL ON SCHEMA aren't supported for Amazon Redshift Spectrum external schemas. To grant usage of external tables in an external schema, grant USAGE ON SCHEMA to the users that need access. Only the owner of an external schema or a superuser is permitted to create external tables in the external schema. To transfer ownership of an external schema, use ALTER SCHEMA to change the owner.

EXECUTE ON FUNCTION function_name

Grants the EXECUTE privilege on a specific function. Because function names can be overloaded, you must include the argument list for the function. For more information, see Naming UDFs.

EXECUTE ON ALL FUNCTIONS IN SCHEMA schema_name

Grants the specified privileges on all functions in the referenced schema.

EXECUTE ON PROCEDURE procedure_name

Grants the EXECUTE privilege on a specific stored procedure. Because stored procedure names can be overloaded, you must include the argument list for the procedure. For more information, see Naming stored procedures.

EXECUTE ON ALL PROCEDURES IN SCHEMA schema_name

Grants the specified privileges on all stored procedures in the referenced schema.

USAGE ON LANGUAGE language_name

Grants the USAGE privilege on a language.

The USAGE ON LANGUAGE privilege is required to create user-defined functions (UDFs) by running the CREATE FUNCTION command. For more information, see UDF security and privileges.

The USAGE ON LANGUAGE privilege is required to create stored procedures by running the CREATE PROCEDURE command. For more information, see Security and privileges for stored procedures .

For Python UDFs, use plpythonu. For SQL UDFs, use sql. For stored procedures, use plpgsql.

FOR { ALL | COPY | UNLOAD } [, ...]

Specifes the SQL command for which the privilege is granted. You can specify ALL to grant the privilege on the COPY and UNLOAD statements. This clause applies only to granting the ASSUMEROLE privilege.