How to View Permissions in Amazon Redshift

Knowing who has access to what tables in Amazon Redshift is very important. You want to make sure users have the data they need to do their jobs, but also want to keep your data secure. This tutorial will show you an easy way to see what permissions have been granted to users in your database.

Access Types

Amazon Redshift allows many types of permissions. More details on the access types and how to grant them in this AWS documentation.

Schema level permissions

• Usage: Allows users to access objects in the schema. User still needs specific table-level permissions for each table within the schema

• Create: Allows users to create objects within a schema using CREATE statement

Table level permissions

• Select: Allows user to read data using SELECT statement

• Insert: Allows user to load data into a table using INSERT statement

• Update: Allows user to modify a column using UPDATE statement

• Delete: Alows user to delete rows using DELETE statement

• References: Allows user to create a foreign key constraint

How to View Permissions

To view the permissions of a specific user on a specific schema, simply change the bold user name and schema name to the user and schema of interest on the following code. For a full list of every user - schema permission status, simply delete the entire WHERE clause.

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'
;

Similarly, to view the permissions of a specific user on a specific table, simply change the bold user name and table name to the user and table of interest on the following code. For a full list of every user - table permission status, simply delete the entire WHERE clause.

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'
;

Conclusion

With these queries you’ll be able to track who has been granted what type of permission to schemas and tables in your database.

You can alter permissions by using the REVOKE and GRANT commands as appropriate.