By using AWS re:Post, you agree to the AWS re:Post Terms of Use
AWS re:Postre:Post

How do I allow an Amazon Redshift user to view system table data from other users in a cluster?

2 minute read
0

I want to allow an Amazon Redshift user to view data that other users generate in system tables in an Amazon Redshift cluster.

Resolution

By default, only Amazon Redshift database superusers have permission to view all databases. To view data that other users generate in system tables, add the SYSLOG ACCESS parameter with UNRESTRICTED access.

Note: Users with SYSLOG ACCESS can't view superuser tables. Only superusers can view other superuser tables.

Complete the following steps:

  1. Connect to the Amazon Redshift database as a superuser.

  2. Run the SQL ALTER USER command:

    test=# ALTER USER testuser WITH SYSLOG ACCESS UNRESTRICTED;
ALTER USER

    Note: Replace testuser with the username that you're providing access to. Also, see the required permissions for ALTER USER.

  3. Disconnect from the Amazon Redshift database as the superuser.

  4. Connect to the Amazon Redshift database as the user that has SYSLOG ACCESS with UNRESTRICTED access.

  5. Run the following command to test your access:

    test=> select * from stv_inflight;

    The following example output shows that the user you granted UNRESTRICTED access to now has access to view users' tables.

    -[ RECORD 1 ]--------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
userid                     | 100
slice                      | 12811
query                      | 3036
label                      | default                                  
xid                        | 35079530
pid                        | 1073746910
starttime                  | 2022-09-15 07:09:15.894317
text                       | select * from my_schema.dw_lu_tiers_test a, my_schema.dw_lu_tiers_test;                         
       
suspended                  | 0
insert_pristine            | 0
concurrency_scaling_status | 0
-[ RECORD 2 ]--------------+---------------------------------------------------------------------------------------------------------------------------------------------------
userid                     | 181
slice                      | 12811
query                      | 3038
label                      | default
xid                        | 35079531
pid                        | 1073877909
starttime                  | 2022-09-15 07:09:17.694285
text                       | select * from stv_inflight;                                       
                                     
suspended                  | 0
insert_pristine            | 0
concurrency_scaling_status | 0
Topics
Analytics
Tags
Amazon Redshift
Language
English
AWS OFFICIAL
AWS OFFICIALUpdated 11 days ago
8 Comments

How can we do the same for a group to view data in system tables?

Below does not seem to be working.

alter group operator_group with SYSLOG ACCESS UNRESTRICTED;

Raj Sasidharan
replied a year ago

Thank you for your comment. We'll review and update the Knowledge Center article as needed.

profile pictureAWS
MODERATOR
AWS Official
replied a year ago

But is it even possible to grant System tables access to GROUPS?

Raj Sasidharan
replied a year ago

Thank you for your comment. We'll review and update the Knowledge Center article as needed.

profile pictureAWS
EXPERT
AWS Official
replied a year ago

This method does not work for the ability to have non-superusers to be able to view svv_datashares. Will this change or if this is even possible, how can this be achieved? even creating a view to query svv_datashares under a superuser and granting a non-superuser select on said view does not work..even if you grant usage on the superuser schema....when you query the superuser view from the non-super user account who has syslog access unrestricted it returns empty rows... can this be achieved in redshift to be able to have non-superusers be able to view/query svv_datashares?

Nate Banks
replied a year ago

Thank you for your comment. We'll review and update the Knowledge Center article as needed.

profile pictureAWS
EXPERT
AWS Official
replied a year ago

hi, I tried alter a non-super user account to add with SYSLOG ACCESS UNRESTRICTED; I still receive permission denied errors (provisioned redshift cluster). Need to have a way to grant a non-privileged user access to troubleshoot issues.

ERROR: permission denied for relation svl_statementtext ERROR: permission denied for relation stl_query ERROR: permission denied for relation stv_exec_state ERROR: permission denied for relation stl_tr_conflict

hsullivan
replied 2 months ago

Found a solution for this:

There are 2 options to do this, use role name 'test_role' as example:

  1. grant ACCESS SYSTEM TABLE to role <test_role>;

System permissions for RBAC - https://docs.aws.amazon.com/redshift/latest/dg/r_roles-system-privileges.html

  1. grant role sys:monitor to role <test_role>;

This system defined role sys:monitor has the permission to access catalog or system tables. Amazon Redshift system-defined roles - https://docs.aws.amazon.com/redshift/latest/dg/r_roles-default.html

Raj Sasidharan
replied a month ago

Relevant content