GRANT

Defines access permissions for a user or role.

Permissions 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 permissions for a table, database, schema, function, procedure, language, or column. To revoke permissions from a database object, use the REVOKE command.

Permissions also include the following datashare producer access options:

Granting datashare access to consumer namespaces and accounts.
Granting permission to alter a datashare by adding or removing objects from the datashare.
Granting permission to share a datashare by adding or removing consumer namespaces from the datashare.

Datashare consumer access options are as follows:

Granting users full access to databases created from a datashare or to external schemas that point to such databases.
Granting users object-level permissions on databases created from a datashare like you can for local database objects. To grant this level of permission, you must use the WITH PERMISSIONS clause when creating a database from the datashare. For more information, see CREATE DATABASE.

For more information about datashare permissions, see Data sharing within and between clusters.

You can also grant roles to manage database permissions and control what users can do relative to your data. By defining roles and assigning roles to users, you can limit the the actions those users can take, such as limiting users to only the CREATE TABLE and INSERT commands. For more information about the CREATE ROLE command, see CREATE ROLE. Amazon Redshift has some system-defined roles that you can also use to grant specific permissions to your users. For more information, see Amazon Redshift system-defined roles.

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 permissions to an AWS Identity and Access Management (IAM) role. For the list of permissions, see the syntax.

For stored procedures, the only permission 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.

To see which permissions users have been granted for a database, use HAS_DATABASE_PRIVILEGE. To see which permissions users have been granted for a schema, use HAS_SCHEMA_PRIVILEGE. To see which permissions users have been granted for a table, use HAS_TABLE_PRIVILEGE.

Syntax


GRANT { { SELECT | INSERT | UPDATE | DELETE | DROP | REFERENCES | ALTER | TRUNCATE } [,...] | 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 | ALTER } [,...] | ALL [ PRIVILEGES ] }
    ON DATABASE db_name [, ...]
    TO { username [ WITH GRANT OPTION ] | ROLE role_name | GROUP group_name | PUBLIC } [, ...]

GRANT { { CREATE | USAGE | ALTER } [,...] | 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 permissions 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 permissions granted to users and groups with a specified role. To begin using the ASSUMEROLE privilege, see Usage notes for granting the ASSUMEROLE permission.


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 ]

Producer-side datashare permissions

The following is the syntax for using GRANT to grant ALTER or SHARE permissions to a user or role. The user can alter the datashare with the ALTER permission, or grant usage to a consumer with the SHARE permission. ALTER and SHARE are the only permissions that you can grant on a datashare to users and roles.


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 permissions on Amazon Redshift. You grant access to a datashare to a consumer using the USAGE permission. You can't grant this permission to users or user groups. This permission also doesn't support the WITH GRANT OPTION for the GRANT statement. Only users or user groups with the SHARE permission 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 DATASHARE salesshare TO ACCOUNT '123456789012' VIA DATA CATALOG;

Consumer-side datashare permissions

The following is the syntax for GRANT data-sharing usage permissions on a specific database or schema created from a datashare.

Further permissions required for consumers to access a database created from a datashare vary depending on whether or not the CREATE DATABASE command used to create the database from the datashare used the WITH PERMISSIONS clause. For more information about the CREATE DATABASE command and WITH PERMISSIONS clause, see CREATE DATABASE.

Databases created without using the WITH PERMISSIONS clause

When you grant USAGE on a database created from a datashare without the WITH PERMISSIONS clause, you don't need to grant permissions separately on the objects in the shared database. Entities granted usage on databases created from datashares without the WITH PERMISSIONS clause automatically have access to all objects in the database.

Databases created using the WITH PERMISSIONS clause

When you grant USAGE on a database where the shared database was created from a datashare with the WITH PERMISSIONS clause, consumer-side identities must still be granted the relevant permissions for database objects in the shared database in order to access them, just as you would grant permissions for local database objects. To grant permissions to objects in a database created from a datashare, use the three-part syntax database_name.schema_name.object_name. To grant permissions to objects in an external schema pointing to a shared schema within the shared database, use the two-part syntax schema_name.object_name.


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

Scoped permissions let you grant permissions to a user or role on all objects of a type within a database or schema. Users and roles with scoped permissions have the specified permissions on all current and future objects within the database or schema.

You can view the scope of database-level scoped permissions in SVV_DATABASE_PRIVILEGES. You can view the scope of schema-level scoped permissions in SVV_SCHEMA_PRIVILEGES.

The following is the syntax for granting scoped permissions to users and roles. For more information about scoped permissions, see Scoped permissions.


GRANT { CREATE | USAGE | ALTER } [,...] | ALL [ PRIVILEGES ] }
FOR SCHEMAS IN
DATABASE db_name 
TO { username [ WITH GRANT OPTION ] | ROLE role_name } [, ...]

GRANT 
{ { SELECT | INSERT | UPDATE | DELETE | DROP | ALTER | TRUNCATE | REFERENCES } [, ...] } | ALL [PRIVILEGES] } }
FOR TABLES IN
{SCHEMA schema_name [DATABASE db_name ] | DATABASE db_name }
TO { username [ WITH GRANT OPTION ] | ROLE role_name} [, ...]

GRANT { EXECUTE | ALL [ PRIVILEGES ] }
FOR FUNCTIONS IN 
{SCHEMA schema_name [DATABASE db_name ] | DATABASE db_name }
TO { username [ WITH GRANT OPTION ] | ROLE role_name | } [, ...]

GRANT { EXECUTE | ALL [ PRIVILEGES ] }
FOR PROCEDURES IN
{SCHEMA schema_name [DATABASE db_name ] | DATABASE db_name }
TO { username [ WITH GRANT OPTION ] | ROLE role_name | } [, ...]

GRANT USAGE
FOR LANGUAGES IN
{DATABASE db_name}
TO { username [ WITH GRANT OPTION ] | ROLE role_name } [, ...]

Note that scoped permissions don’t distinguish between permissions for functions and for procedures. For example, the following statement grants bob the EXECUTE permission for both functions and procedures in the schema Sales_schema.


GRANT EXECUTE FOR FUNCTIONS IN SCHEMA Sales_schema TO bob;

The following is the syntax for machine learning model permissions 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 roles on Amazon Redshift.


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

The following is the syntax for granting system permissions to roles on Amazon Redshift. Note that you can only grant permissions to roles, not users.


GRANT
  {
    { CREATE USER | DROP USER | ALTER USER |
    CREATE SCHEMA | DROP SCHEMA |
    ALTER DEFAULT PRIVILEGES |
    ACCESS CATALOG | ACCESS SYSTEM TABLE
    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 |
    IGNORE RLS | EXPLAIN RLS | 
    EXPLAIN MASKING }[, ...]
  }
  | { 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 [, ...]

The following is the syntax for a superuser to grant COPY JOB permissions for a database to a user.


GRANT { { CREATE | ALTER | DROP} [,...] | ALL [ PRIVILEGES ] }
FOR COPY JOBS 
IN DATABASE db_name
TO { username [ WITH GRANT OPTION ] | ROLE role_name } [, ...]

The following is the syntax for a COPY JOB owner or superuser to grant COPY JOB permissions for an existing COPY JOB to another user.


GRANT { { ALTER | DROP} [,...] | ALL [ PRIVILEGES ] }
ON COPY JOB job_name [,...]
TO { username [ WITH GRANT OPTION ] | ROLE role_name | GROUP group_name | PUBLIC } [, ...]

Parameters

SELECT

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

INSERT

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

UPDATE

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

DELETE

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

DROP

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

REFERENCES

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

ALTER

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

For tables, ALTER grants permission to alter a table or view. For more information, see ALTER TABLE.
For databases, ALTER grants permission to alter a database. For more information, see ALTER DATABASE.
For schemas, ALTER grants permission to alter a schema. For more information, see ALTER SCHEMA.
For external tables, ALTER grants permission to alter a table in an AWS Glue Data Catalog that is enabled for Lake Formation. This permission only applies when using Lake Formation.

TRUNCATE

Grants permission to truncate a table. Without this permission, only the owner of a table or a superuser can truncate a table. For more information about the TRUNCATE command, see TRUNCATE.

ALL [ PRIVILEGES ]

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

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

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

Note

Amazon Redshift doesn't support the RULE and TRIGGER permissions. For more information, go to Unsupported PostgreSQL features.

ASSUMEROLE

Grants permission 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 permission, see Usage notes for granting the ASSUMEROLE permission.

ON [ TABLE ] table_name

Grants the specified permissions 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 permissions on all tables and views in the referenced schema.

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

Grants the specified permissions 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 permissions 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 permissions to an IAM role on the specified Lake Formation tables in the referenced schema.

ON EXTERNAL SCHEMA schema_name

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

ON iam_role

Grants the specified permissions to an IAM role.

TO username

Indicates the user receiving the permissions.

TO IAM_ROLE iam_role

Indicates the IAM role receiving the permissions.

WITH GRANT OPTION

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

ROLE role_name

Grants the permissions to a role.

GROUP group_name

Grants the permissions to a user group. Can be a comma-separated list to specify multiple user groups.

PUBLIC

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

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

CREATE

Depending on the database object, grants the following permissions 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 permission 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 permission 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 permission 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 permissions on a database.

USAGE

Grants USAGE permission 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 permission on tables) for local Amazon Redshift schemas. By default, all users have CREATE and USAGE permission 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 permissions on a schema.

GRANT CREATE ON SCHEMA and the CREATE permission 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 permissions 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 permission 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 permissions on all stored procedures in the referenced schema.

USAGE ON LANGUAGE language_name

Grants the USAGE permission on a language.

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

The USAGE ON LANGUAGE permission 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 permission is granted. You can specify ALL to grant the permission on the COPY, UNLOAD, EXTERNAL FUNCTION, and CREATE MODEL statements. This clause applies only to granting the ASSUMEROLE permission.

ALTER

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

SHARE

Grants pemrissions to users and user groups to add data consumers to a datashare. This permission 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 AWS account, with the same or a different cluster namespace as specified by a globally unique identifier (GUID).

ON DATASHARE datashare_name

Grants the specified permissions on the referenced datashare. For information about consumer access control granularity, see Data sharing 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 permissions 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 permissions 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 permissions on the specified database that is created in the specified datashare.

ON SCHEMA shared_schema

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

FOR { SCHEMAS | TABLES | FUNCTIONS | PROCEDURES | LANGUAGES } IN

Specifies the database objects to grant permission to. The parameters following IN define the scope of the granted permission.

CREATE MODEL

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

ON MODEL model_name

Grants the EXECUTE permission on a specific model.

ACCESS CATALOG

Grants the permission to view relevant metadata of objects that the role has access to.

{ 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 permissions consist of the sum of permissions granted to PUBLIC, permissions granted to any groups that the user belongs to, and any permissions 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 permission 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 permission to bypass row-level security policies for a query to a role.

Usage notes

To learn more about the usage notes for GRANT, see Usage notes.

Examples

For examples of how to use GRANT, see Examples.

Warning Javascript is disabled or is unavailable in your browser.

To use the Amazon Web Services Documentation, Javascript must be enabled. Please refer to your browser's Help pages for instructions.

Document Conventions

FETCH

Usage notes