Redshift Permissions 101: Types & How to Grant and Revoke Access?

Organizations using traditional Data Warehouses face not just storage constraints, but also processing challenges as the volume of data grows. Amazon Redshift, on the other hand, offers a Cloud-based quick & dependable Data Warehouse Solution that removes Scalability concerns and helps analysts acquire important insights using Business Intelligence tools. It’s critical to know who has access to which tables in Amazon Redshift. 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 the Redshift Permissions and how to quickly discover what Redshift Permissions users in your Database have been granted.

Table Of Contents

• What is Amazon Redshift?
  • Key Features of Amazon Redshift
• What are the Types of Access Privileges & Redshift Permissions?
  • Schema-level Redshift Permissions
  • User-level Redshift Permissions
• How to View Redshift Permissions and Acces Privileges?
  • Viewing Redshift Permissions for a Single Redshift User
  • Viewing Redshift Permissions for all Redshift Users
• How to use the GRANT Command for Redshift Permissions?
  • Syntax
  • Parameters
  • Example Queries
• How to use the Revoke Command for Redshift Permissions?
  • Syntax
  • Parameters
  • Example Queries
• Conclusion

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 that can help them become Data-Driven businesses. The cost per TB each year is roughly $1000, which is much cheaper than the cost of establishing and maintaining On-Site solutions. 

Columnar Storage, Data Compression, and Zone Mapping are examples of current systems and methodologies that seek to give at par performance. Amazon Redshift integrates seamlessly with AWS’s other services and provides a variety of connectors and integrations.

Key Features of Amazon Redshift

• Parallel Processing: To process massive datasets, parallel processing is implemented utilizing a distributed design method that employs several CPUs.
• Error Tolerance: Organizations may rely on the Data Warehouse’s Fault and Error Tolerance to ensure uninterrupted operation when executing mission-critical operations in the Cloud.
• End-to-end Encryption: To protect users’ Privacy and Security, all data handled on the Cloud is encrypted. Key sharing for encrypted data can be done in a variety of ways.
• Network Isolation: Parts of the deployment can be separated from the rest of the network and the Internet, and made accessible only via IPsec VPN.
• Speed: The usage of MPP technology allows for rapid processing and execution of a huge number of queries and data. Other cloud service providers can’t match the prices AWS charges for services.
• Data encryption: The Amazon server provides data encryption for your Redshift operation. The user has the option of deciding which processes should be encrypted and which should not. Data encryption adds another layer of protection.
• Familiarity: Redshift is based on the PostgreSQL database. It supports all SQL queries. You’ll also select SQL, ETL (extract, transform, load), and business intelligence (BI) technologies that you’re familiar with. You are not obligated to utilize the Amazon tools.

Official documentation regarding Amazon Redshift can be found here. 

What are the Types of Access Privileges & Redshift Permissions?

1) Schema-level Redshift Permissions

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

2) User-level Redshift Permissions

You become the owner of a Database object when you create it. Only a superuser or the object’s owner can query, change, or grant rights on the object by default. You must grant the necessary privileges to the user or the group that contains the user in order for them to use an item. The privileges of Database superusers are the same as those of database owners. 

The rights SELECT, INSERT, UPDATE, DELETE, REFERENCES, CREATE, TEMPORARY, and USAGE are supported by Amazon Redshift. Different object kinds are connected with different 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. For UPDATE and DELETE operations, the SELECT privilege is also required to reference existing column values.
• 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 gives you the ability 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 will be unable to 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.
  • Build lets users create items within a schema for schemas. The user must have the CREATE privilege and own the object to be renamed in order to rename it.
  • External Amazon Redshift Spectrum schemas do not enable CREATE ON SCHEMA. 
  • 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 in order to conduct Amazon Redshift Spectrum queries.
• USAGE: Grants USAGE privilege to a given schema, allowing users to access objects in that schema. Individual permissions for specific activities on these objects must be provided separately (for example, SELECT or UPDATE privileges on tables). On the PUBLIC schema, all users have CREATE and USAGE access by default.

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 certain user for a specific Schema. Simply remove the entire 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 certain table, simply replace the bold User Name and Table Name in the following code with the User and Table of interest. Eliminate the entire 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 GRANT Command. Privileges provide the ability to read data from Tables and Views, Write Data, Create Tables, and Drop Tables, among other things. Give specified privileges to a Table, Database, Schema, Function, Procedure, Language, or Column with this command.

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, there are a lot of other parameters 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 that is enabled for Lake Formation can be granted ALL privileges. Individual rights (such as SELECT, ALTER, and others) are recorded in the Data Catalog in this situation.
• ALTER: This allows you to make changes to 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 a user group permissions.
• 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 everyone group.

For further information on the Usage Parameters, check out the official documentation here.

C) Example Queries

• Below is an example of utilizing GRANT for sharing Data Access privileges on Amazon Redshift. 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 on an external schema to database users and user groups. 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 in querying 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. In case you want to export data from various sources into your desired Database/destination like Redshift, then Hevo Data is the right choice for you! 

Visit our Website to Explore Hevo

Hevo Data provides its users with a simpler platform for integrating data from 100+ sources for Analysis. It is a No-code Data Pipeline that can help you combine data from multiple sources. You can use it to transfer data from multiple data sources into your Data Warehouses such as Amazon Redshift, Database, or a destination of your choice. It provides you with a consistent and reliable solution to managing data in real-time, ensuring that you always have Analysis-ready data in your desired destination.

Want to take Hevo for a spin? Sign Up for a 14-day free trial and experience the feature-rich Hevo suite first hand. 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!