GRANT - Amazon Redshift
SyntaxParameters
Services or capabilities described in Amazon Web Services documentation might vary by Region. To see the differences applicable to the China Regions, see Getting Started with Amazon Web Services in China (PDF).

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, create tables, and drop 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.

Privileges also include access options such as being able to add objects or consumers to or remove objects or consumers from a datashare. To add database objects to or remove database objects from a datashare for a user or user group, use the ALTER privilege. Similarly, to add or remove consumers from a datashare, use the SHARE privilege. To revoke privileges from a database object, use the REVOKE command. ALTER and SHARE are the only privileges that you can grant to users and user groups.

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 Amazon Lake Formation, you can only GRANT and REVOKE privileges to an Amazon 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 | DROP | REFERENCES } [,...] | ALL [ PRIVILEGES ] }
    ON { [ TABLE ] table_name [, ...] | ALL TABLES IN SCHEMA schema_name [, ...] }
    TO { username [ WITH GRANT OPTION ] | ROLE role_name | GROUP group_name | PUBLIC } [, ...]

GRANT { { CREATE | TEMPORARY | TEMP } [,...] | ALL [ PRIVILEGES ] }
    ON DATABASE db_name [, ...]
    TO { username [ WITH GRANT OPTION ] | ROLE role_name | GROUP group_name | PUBLIC } [, ...]

GRANT { { CREATE | USAGE } [,...] | ALL [ PRIVILEGES ] }
    ON SCHEMA schema_name [, ...]
    TO { username [ WITH GRANT OPTION ] | ROLE role_name | 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 ] | ROLE role_name | 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 ] | ROLE role_name | GROUP group_name | PUBLIC } [, ...]

GRANT USAGE
    ON LANGUAGE language_name [, ...]
    TO { username [ WITH GRANT OPTION ] | ROLE role_name | 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 | ROLE role_name | GROUP group_name | PUBLIC } [, ...]

The following is the syntax for the ASSUMEROLE privilege granted to users and groups with a specified role. To begin using the ASSUMEROLE privilege, see Usage notes for granting the ASSUMEROLE privilege .

GRANT ASSUMEROLE
    ON { 'iam_role' [, ...] | default | ALL }
    TO { username | ROLE role_name | GROUP group_name | PUBLIC } [, ...]
    FOR { ALL | COPY | UNLOAD | EXTERNAL FUNCTION | CREATE MODEL } [, ...]

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 ]

The following is the syntax for using GRANT for datashare privileges on Amazon Redshift. ALTER and SHARE are the only privileges that you can grant to users and user groups in this case.

GRANT { ALTER | SHARE } ON DATASHARE datashare_name
    TO { username [ WITH GRANT OPTION ] | ROLE role_name | GROUP group_name | PUBLIC } [, ...]

The following is the syntax for using GRANT for datashare usage privileges on Amazon Redshift. You grant access to a datashare to a consumer using the USAGE privilege. You can't grant this privilege to users or user groups. This privilege also doesn't support the WITH GRANT OPTION for the GRANT statement. Only users or user groups with the SHARE privilege previously granted to them FOR the datashare can run this type of GRANT statement.

GRANT USAGE
    ON DATASHARE datashare_name
    TO NAMESPACE 'namespaceGUID' [, ...] | ACCOUNT 'accountnumber' [ VIA DATA CATALOG ] [, ...]

The following is an example of how to grant usage of a datashare to a Lake Formation account.

GRANT USAGE ON salesshare TO ACCOUNT '123456789012' VIA DATA CATALOG;

The following is the syntax for GRANT data-sharing usage permissions on a specific database or schema created from a datashare. This USAGE permission doesn't grant usage permission to databases that aren't created from the specified datashare. You can only GRANT or REVOKE ALTER or SHARE permissions on a datashare to users and user groups.

GRANT USAGE ON { DATABASE shared_database_name [, ...] | SCHEMA shared_schema}
    TO { username | ROLE role_name | GROUP group_name | PUBLIC } [, ...]

The following is the syntax for machine learning model privileges on Amazon Redshift.

GRANT CREATE MODEL
    TO { username [ WITH GRANT OPTION ] | ROLE role_name | GROUP group_name | PUBLIC } [, ...]

GRANT { EXECUTE | ALL [ PRIVILEGES ] }
    ON MODEL model_name [, ...]

    TO { username [ WITH GRANT OPTION ] | ROLE role_name | GROUP group_name | PUBLIC } [, ...]

The following is the syntax for granting role privileges on Amazon Redshift.

GRANT { ROLE role_name } [, ...] TO { { user_name [ WITH ADMIN OPTION ] } | ROLE role_name }[, ...]

The following is the syntax for granting system privileges to roles on Amazon Redshift.

GRANT
  {
    { CREATE USER | DROP USER | ALTER USER |
    CREATE SCHEMA | DROP SCHEMA |
    ALTER DEFAULT PRIVILEGES |
    CREATE TABLE | DROP TABLE | ALTER TABLE |
    CREATE OR REPLACE FUNCTION | CREATE OR REPLACE EXTERNAL FUNCTION |
    DROP FUNCTION |
    CREATE OR REPLACE PROCEDURE | DROP PROCEDURE |
    CREATE OR REPLACE VIEW | DROP VIEW |
    CREATE MODEL | DROP MODEL |
    CREATE DATASHARE | ALTER DATASHARE | DROP DATASHARE |
    CREATE LIBRARY | DROP LIBRARY |
    CREATE ROLE | DROP ROLE
    TRUNCATE TABLE
    VACUUM | ANALYZE | CANCEL }[, ...]
  }
  | { ALL [ PRIVILEGES ] }
TO { ROLE role_name } [, ...]

The following is the syntax for granting permissions to explain the row-level security policy filters of a query in the EXPLAIN plan. You can revoke the privilege using the REVOKE statement.

GRANT EXPLAIN RLS TO ROLE rolename

The following is the syntax for granting permissions to bypass row-level security policies for a query. 

GRANT IGNORE RLS TO ROLE rolename

The following is the syntax for granting permissions to the specified row-level security policy.

GRANT SELECT ON [ TABLE ] table_name [, ...]
TO RLS POLICY policy_name [, ...]

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 Amazon 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 Amazon Glue Data Catalog that is enabled for Lake Formation. This privilege only applies when using Lake Formation.

DROP

Grants privilege to drop a table. This privilege applies in Amazon Redshift and in an Amazon Glue Data Catalog that is enabled for Lake Formation.

ASSUMEROLE

Grants privilege to run COPY, UNLOAD, EXTERNAL FUNCTION, and CREATE MODEL commands to users, roles, or groups with a specified role. The user, role, or group assumes that role when running the specified command. To begin using the ASSUMEROLE privilege, see Usage notes for granting the ASSUMEROLE privilege .

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't be granted to a group or to PUBLIC.

ROLE role_name

Grants the privileges to a role.

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) for local Amazon Redshift schemas. By default, all users have CREATE and USAGE privileges on the PUBLIC schema.

When you grant USAGE to external schemas using ON SCHEMA syntax, you don't need to grant actions separately on the objects in the external schema. The corresponding catalog permissions control granular permissions on the external schema objects.

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 ALL FUNCTIONS IN SCHEMA schema_name

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

Amazon Redshift doesn't support GRANT or REVOKE statements for pg_proc builtin entries defined in pg_catalog namespace.

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 | EXTERNAL FUNCTION | CREATE MODEL } [, ...]

Specifies the SQL command for which the privilege is granted. You can specify ALL to grant the privilege on the COPY, UNLOAD, EXTERNAL FUNCTION, and CREATE MODEL statements. This clause applies only to granting the ASSUMEROLE privilege.

ALTER

Grants the ALTER privilege to users to add or remove objects from a datashare, or to set the property PUBLICACCESSIBLE. For more information, see ALTER DATASHARE.

SHARE

Grants privileges to users and user groups to add data consumers to a datashare. This privilege is required to enable the particular consumer (account or namespace) to access the datashare from their clusters. The consumer can be the same or a different Amazon account, with the same or a different cluster namespace as specified by a globally unique identifier (GUID).

ON DATASHARE datashare_name

Grants the specified privileges on the referenced datashare. For information about consumer access control granularity, see Sharing data at different levels in Amazon Redshift.

USAGE

When USAGE is granted to a consumer account or namespace within the same account, the specific consumer account or namespace within the account can access the datashare and the objects of the datashare in read-only fashion.

TO NAMESPACE 'clusternamespace GUID'

Indicates a namespace in the same account where consumers can receive the specified privileges to the datashare. Namespaces use a 128-bit alphanumeric GUID.

TO ACCOUNT 'accountnumber' [ VIA DATA CATALOG ]

Indicates the number of another account whose consumers can receive the specified privileges to the datashare. Specifying ‘VIA DATA CATALOG’ indicates that you are granting usage of the datashare to a Lake Formation account. Omitting this parameter means you're granting usage to an account that owns the cluster.

ON DATABASE shared_database_name> [, ...]

Grants the specified usage privileges on the specified database that is created in the specified datashare.

ON SCHEMA shared_schema

Grants the specified privileges on the specified schema that is created in the specified datashare.

CREATE MODEL

Grants the CREATE MODEL privilege to specific users or user groups.

ON MODEL model_name

Grants the EXECUTE privilege on a specific model.

{ role } [, ...]

The role to be granted to another role, a user, or PUBLIC.

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.

TO { { user_name [ WITH ADMIN OPTION ] } | role }[, ...]

Grants the specified role to a specified user with the WITH ADMIN OPTION, another role, or PUBLIC.

The WITH ADMIN OPTION clause provides the administration options for all the granted roles to all the grantees.

EXPLAIN RLS TO ROLE rolename

Grants the privilege to explain the row-level security policy filters of a query in the EXPLAIN plan to a role.

IGNORE RLS TO ROLE rolename

Grants the privilege to bypass row-level security policies for a query to a role.