Permission to user to read system tables

Step 1: Create a New Role First, create a new role (if you haven't already) that will be granted permissions.

CREATE ROLE my_read_role;

Step 2: Grant Permissions to the Role

Grant the necessary permissions to this role. While Redshift doesn't allow direct permission grants on system views, you can grant some common permissions that might help the user get closer to what they need.

GRANT SELECT ON ALL TABLES IN SCHEMA pg_catalog TO my_read_role;
GRANT SELECT ON ALL TABLES IN SCHEMA information_schema TO my_read_role;

Step 3: Assign the Role to the User

Assign the role to the user who needs access to the system views.

GRANT ROLE my_read_role TO my_regular_user;

Alternative: Use Views or Stored Procedures

If granting direct access to the system views does not work as expected, consider creating custom views or stored procedures that expose the necessary information. Here is an example of how you might create a view that a regular user can query:

CREATE VIEW my_user_grants AS
SELECT *
FROM svv_user_grants
WHERE user_name = CURRENT_USER;

GRANT SELECT ON my_user_grants TO my_regular_user;

Step 4: Test the Access

Finally, test the access to ensure that the user can view the required information.

-- As the regular user
SELECT * FROM my_user_grants;

If you need to access to all system tables, your admin user can grant a system role called sys:monitor to regular users to provide them the ability to monitor system tables.

https://docs.aws.amazon.com/redshift/latest/dg/r_roles-default.html

Hello,

To provide access to a system tables, you can grant SELECT privilege on that table to the regular user. Visibility of data in system tables and views and for more information https://docs.aws.amazon.com/redshift/latest/dg/r_GRANT.html

Example statement:

GRANT SELECT ON TABLE_NAME TO USER_NAME;