How can I allow Amazon Redshift database regular users permission to view data in system tables from other users for my cluster?

2 minute read
0

I am not able to view data generated by other users in system tables in my Amazon Redshift cluster. How can I view the tables?

Short description

By default, Amazon Redshift regular users don't have permission to view data from other users. Only Amazon Redshift database superusers have permission to view all databases.

Resolution

You can add the SYSLOG ACCESS parameter with UNRESTRICTED access for the regular user to view data generated by other users in system tables.

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

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

2.    Run the SQL command ALTER USER similar to the following:

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

Note: The required privileges for ALTER USER are superuser, users with the ALTER USER privilege, and users that want to change their own passwords.

The regular user now has SYSLOG ACCESS with UNRESTRICTED access.

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

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

5.    Test the regular users access by running a SQL command similar to the following:

test=> select * from stv_inflight;
-[ 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

In the example output, note that the regular user now has access to view another users table.


AWS OFFICIAL
AWS OFFICIALUpdated 2 years ago
6 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;

replied 3 months ago

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

profile pictureAWS
MODERATOR
replied 3 months ago

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

replied 3 months ago

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

profile pictureAWS
MODERATOR
replied 3 months 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?

replied 3 months ago

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

profile pictureAWS
MODERATOR
replied 3 months ago