Amazon Redshift Permissions: Types, Commands, and Examples

Organizations using traditional data warehouses face not just storage constraints but also processing challenges as the volume of data grows. On the other hand, Amazon Redshift offers a cloud-based quick & dependable data warehouse solution that removes scalability concerns and helps analysts acquire important insights using business intelligence tools. Knowing who has access to which tables in Amazon Redshift is critical. You want to ensure users have access to the information they need to complete their jobs, but you also want to keep your data safe. 

This blog will show you everything about Redshift permissions and how to discover what users in your database have been granted quickly.

What is Amazon Redshift?

AWS (Amazon Web Services) offers Amazon Redshift, a cloud data warehouse solution. It’s a low-cost platform that provides firms with analytical services to help them become data-driven businesses.  At approximately $1000 per TB per year, Amazon Redshift is significantly more cost-effective than setting up and maintaining on-premise solutions.

Columnar storage, data compression, and zone mapping are current systems and methodologies that seek to perform at par. Amazon Redshift integrates seamlessly with AWS’s other services and provides a variety of connectors and integrations.

Key Features of Amazon Redshift

• Parallel processing: Uses a distributed design method with multiple CPUs to handle large datasets efficiently.
• Error tolerance: The data warehouse’s fault and error tolerance ensures uninterrupted operation during mission-critical tasks in the cloud.
• End-to-end encryption: All data handled in the cloud is encrypted to protect user privacy and security. Encryption keys can be shared in various ways.
• Network isolation: Parts of the deployment can be isolated from the rest of the network and the internet, accessible only through IPsec VPN.
• Speed: MPP technology enables rapid query execution and data processing. AWS offers competitive pricing compared to other cloud providers.
• Data encryption: Redshift provides data encryption options, allowing users to choose which processes should be encrypted for an added layer of security.
• Familiarity: Based on the PostgreSQL database, Redshift supports all SQL queries and works with familiar SQL, ETL, and BI tools without requiring exclusive use of Amazon tools.

What are the Types of Access Privileges & Redshift Permissions?

1) Schema-level Redshift Permissions

• USAGE: Allows users to access Schema objects. The user still needs appropriate table-level rights for each table in the Schema.
• CONSTRUCT: The CREATE statement allows users to create objects within a Schema.

2) User-level Redshift Permissions

The privileges SELECT, INSERT, UPDATE, DELETE, REFERENCES, CREATE, TEMPORARY, and USAGE are supported by Amazon Redshift. Different object kinds are connected with other rights. All these User-level permissions are a part of GRANT and REVOKE privileges:

• SELECT: grants the ability to use a SELECT statement to fetch data from a table or view. The SELECT privilege is also required to reference existing column values for UPDATE and DELETE operations.
• INSERT: grants permission to use an INSERT or COPY statement to load data into a table.
• UPDATE: Edit grants the ability to use an UPDATE Statement to update a table column. Since they must reference table columns to decide which rows to update or to compute new values for columns, UPDATE procedures also require the SELECT privilege.
• DELETE: This allows you to delete a data row from a table. Since DELETE operations must reference table fields to decide which rows to delete; they also require the SELECT privilege.
• REFERENCES: Gives you the ability to make a Foreign Key Constraint. This privilege must be granted on both the referenced and referring tables; otherwise, the user cannot create the constraint.
• CREATE:
  • Grants the following privileges to the user or user group, depending on the database object.
  • CREATE is a database tool that lets users create schemas within the database.
  • The CREATE privilege allows users to create objects within a schema. The user must have the privilege and own the object to be renamed to rename it.
  • External Amazon Redshift Spectrum schemas do not support the CREATE privilege.
  • Grant USAGE ON SCHEMA to the users who require access to external tables in an external schema. External tables in an external schema can only be created by the external schema’s owner or a superuser. 
  • To change the owner of an external schema, use the ALTER SCHEMA command.
• TEMPORARY: The database user must have the authority to create temporary tables in the database to conduct Amazon Redshift Spectrum queries.
• USAGE: Grants USAGE privilege to a given schema, allowing users to access objects. Individual permissions for specific activities on these objects must be provided separately (for example, SELECT or UPDATE privileges on tables). All users have CREATE and USAGE access by default on the PUBLIC schema.

How to View Redshift Permissions and Acces Privileges?

1) Viewing Redshift Permissions for a Single Redshift User

Simply replace the bold user name and schema name in the following code with the user and schema of interest to see the permissions of a particular user for a specific schema. Simply remove the WHERE clause to get a complete list of every user’s Schema Permission Status. Example Query:

SELECT
    u.usename,
    s.schemaname,
    has_schema_privilege(u.usename,s.schemaname,'create') AS user_has_select_permission,
    has_schema_privilege(u.usename,s.schemaname,'usage') AS user_has_usage_permission
FROM
    pg_user u
CROSS JOIN
    (SELECT DISTINCT schemaname FROM pg_tables) s
WHERE
    u.usename = 'myUserName'
    AND s.schemaname = 'mySchemaName'

2) Viewing Redshift Permissions for all Redshift Users

To view the rights of a given user on a particular table, simply replace the bold User Name and Table Name in the following code with the User and Table of interest. Eliminate the WHERE clause to get a complete list of every user’s Table Permission Status. Example Query:

SELECT
    u.usename,
    t.schemaname||'.'||t.tablename,
    has_table_privilege(u.usename,t.tablename,'select') AS user_has_select_permission,
    has_table_privilege(u.usename,t.tablename,'insert') AS user_has_insert_permission,
    has_table_privilege(u.usename,t.tablename,'update') AS user_has_update_permission,
    has_table_privilege(u.usename,t.tablename,'delete') AS user_has_delete_permission,
    has_table_privilege(u.usename,t.tablename,'references') AS user_has_references_permission
FROM
    pg_user u
CROSS JOIN
    pg_tables t
WHERE
    u.usename = 'myUserName'
    AND t.tablename = 'myTableName'

How To Use the GRANT Command for Redshift Permissions?

A user’s or a user group’s access privileges are defined with the help of the GRANT command. Privileges provide the ability to read data from tables and views, write data, create tables, and drop tables, among other things. This command gives specified privileges to a table, database, schema, function, procedure, language, or column.

A) 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 ] | 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 } [, ...]     

B) Parameters

Apart from the parameters discussed in the user-level Permissions section, many other parameters are available.

• ALL [PRIVILEGES]:
  • Gives the given User or User Group all accessible rights at once. The keyword PRIVILEGES is optional.
  • For external schemas GRANT ALL ON SCHEMA, does not grant CREATE access.
  • A table in an AWS Glue Data Catalog enabled for Lake Formation can be granted ALL privileges. In this situation, individual rights (such as SELECT, ALTER, and others) are recorded in the Data Catalog.
• ALTER: This allows you to change a table in an AWS Glue Data Catalog that has Lake Formation enabled. This benefit is only available when you’re using Lake Formation.
• DROP: This allows you to drop a table. This capability is valid in Amazon Redshift and an AWS Glue Data Catalog with Lake Formation enabled.
• ASSUMEROLE: Allows users and groups with a specific role to run the COPY, UNLOAD, EXTERNAL FUNCTION, and CREATE MODEL commands. When the supplied command executes, the user or group assumes that role. 
• ON [TABLE] table_name: Assigns a set of privileges to a table or view. The keyword TABLE is optional. Multiple tables and views can be listed in a single sentence.
• ON ALL TABLES IN SCHEMA schema_name: The provided privileges are granted to all tables and views in the linked schema by schema name.
• ( column_name [,…] ) ON TABLE table_name: On the selected columns of the Amazon Redshift table or view, ON TABLE table name grants the required access to users, groups, or PUBLIC.
• ( column_list ) ON EXTERNAL TABLE schema_name.table_name: Grants an IAM role the provided privileges on the Lake Formation table in the linked schema’s specified columns.
• ON EXTERNAL SCHEMA schema_name: Gives an IAM role on the referenced schema the requested privileges.
• ON iam_role: Gives an IAM role the given privileges.
• TO IAM_ROLE iam_role: Indicates the IAM role receiving the privileges.
• TO username: The user who will be granted the privileges is indicated by the TO username.
• WITH GRANT OPTION: The person who receives the privileges can then grant the same privileges to others. A group or the PUBLIC cannot be given with the GRANT OPTION.
• GROUP group_name: Gives permissions to a user group.
• PUBLIC:
  • All users, even those added later, will have the stated privileges. PUBLIC denotes a group that includes all users at all times. The sum of privileges granted to PUBLIC, privileges awarded to any groups to which the user belongs, and any privileges granted to the user individually make up an individual user’s privileges.
  • When a Lake Formation EXTERNAL TABLE is made PUBLIC, the privilege is granted to the Lake formation of every group.

Read more about the Usage Parameters.

C) Example Queries

• Below is an example of using GRANT to share Amazon Redshift data access privileges. In this situation, the only privileges you may give to Users and User groups are ALTER and SHARE.

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

• Below is an example of Machine Learning Model privileges on Amazon Redshift.

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

GRANT { EXECUTE | ALL [ PRIVILEGES ] }
    ON MODEL model_name [, ...]
    TO { username [ WITH GRANT OPTION ] | GROUP group_name | PUBLIC } [, ...]

How To Use the Revoke Command for Redshift Permissions?

REVOKE Command removes access privileges from a User or User Group, such as the ability to Create, Drop, or Update Tables.

Using the ON SCHEMA syntax, you can only GRANT or REVOKE USAGE permissions to database users and user groups on an external schema. You can only GRANT and REVOKE access to an AWS Identity and Access Management (IAM) role when using ON EXTERNAL SCHEMA with AWS Lake Formation.

A) Syntax

REVOKE [ GRANT OPTION FOR ]
{ { SELECT | INSERT | UPDATE | DELETE | DROP | REFERENCES } [,...] | ALL [ PRIVILEGES ] }
ON { [ TABLE ] table_name [, ...] | ALL TABLES IN SCHEMA schema_name [, ...] }
FROM { username | GROUP group_name | PUBLIC } [, ...]
[ RESTRICT ]

REVOKE [ GRANT OPTION FOR ]
{ { CREATE | TEMPORARY | TEMP } [,...] | ALL [ PRIVILEGES ] }
ON DATABASE db_name [, ...]
FROM { username | GROUP group_name | PUBLIC } [, ...]
[ RESTRICT ]

REVOKE [ GRANT OPTION FOR ]
{ { CREATE | USAGE } [,...] | ALL [ PRIVILEGES ] }
ON SCHEMA schema_name [, ...]
FROM { username | GROUP group_name | PUBLIC } [, ...]
[ RESTRICT ]

REVOKE [ GRANT OPTION FOR ]
EXECUTE 
    ON FUNCTION function_name ( [ [ argname ] argtype [, ...] ] ) [, ...]
    FROM { username | GROUP group_name | PUBLIC } [, ...]
[ RESTRICT ]

REVOKE [ GRANT OPTION FOR ]
{ { EXECUTE } [,...] | ALL [ PRIVILEGES ] }
    ON PROCEDURE procedure_name ( [ [ argname ] argtype [, ...] ] ) [, ...]
    FROM { username | GROUP group_name | PUBLIC } [, ...]
[ RESTRICT ]

REVOKE [ GRANT OPTION FOR ]
USAGE 
    ON LANGUAGE language_name [, ...]
    FROM { username | GROUP group_name | PUBLIC } [, ...]
[ RESTRICT ]     

B) Parameters

REVOKE can be used with the same parameters discussed in the User-level permissions and GRANT: Parameters section.

C) Example Queries

• Below is an example query for revocation of Data Share Privileges in Amazon Redshift.

REVOKE { ALTER | SHARE } ON DATASHARE datashare_name     
    FROM { username [ WITH GRANT OPTION ] | GROUP group_name | PUBLIC } [, ...]

• Below is an example query for revocation of Data Share Usage Privileges in Amazon Redshift.

REVOKE USAGE 
    ON DATASHARE datashare_name 
    FROM NAMESPACE 'namespaceGUID' [, ...] | ACCOUNT 'accountnumber' [, ...]

Conclusion

While traditional data warehouses battle to query large datasets, Amazon Redshift clocks one of the fastest data queries with its massively parallel processing capabilities. Moreover, the Redshift Permissions helps to give and restrict the access privileges for Data Security. If you want to export data from various sources into your desired Database/destination like Redshift, then Hevo Data is your choice!

Want to take Hevo for a spin? Try Hevo’s 14-day free trial and experience the feature-rich Hevo suite firsthand. You can also have a look at the unbeatable pricing that will help you choose the right plan for your business needs.

Share your experience of learning about Redshift Permissions! Let us know in the comments section below!

FAQ on Redshift Permissions