script to check select access for entire schema in AWS Redshift database.

 

script to check  select access for entire schema in AWS Redshift database.

SELECT 

    tablename,

    usename

FROM

    pg_catalog.pg_tables AS tables,

    pg_catalog.pg_user AS users

WHERE 

    tables.schemaname = '<schema_name>'

    AND users.usename = 'username@abc.com'

    AND tables.schemaname not  like 'pg_%'

    AND NOT HAS_TABLE_PRIVILEGE(users.usename, tables.tablename, 'select');