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

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.

---