View GRANTs on Redshift

https://stackoverflow.com/questions/18741334/how-do-i-view-grants-on-redshift

SELECT * 
FROM 
    (
    SELECT 
        schemaname
        ,objectname
        ,usename
        ,HAS_TABLE_PRIVILEGE(usrs.usename, fullobj, 'select') AND has_schema_privilege(usrs.usename, schemaname, 'usage')  AS sel
        ,HAS_TABLE_PRIVILEGE(usrs.usename, fullobj, 'insert') AND has_schema_privilege(usrs.usename, schemaname, 'usage')  AS ins
        ,HAS_TABLE_PRIVILEGE(usrs.usename, fullobj, 'update') AND has_schema_privilege(usrs.usename, schemaname, 'usage')  AS upd
        ,HAS_TABLE_PRIVILEGE(usrs.usename, fullobj, 'delete') AND has_schema_privilege(usrs.usename, schemaname, 'usage')  AS del
        ,HAS_TABLE_PRIVILEGE(usrs.usename, fullobj, 'references') AND has_schema_privilege(usrs.usename, schemaname, 'usage')  AS ref
    FROM
        (
        SELECT schemaname, 't' AS obj_type, tablename AS objectname, schemaname + '.' + tablename AS fullobj FROM pg_tables
        WHERE schemaname not in ('pg_internal')
        UNION
        SELECT schemaname, 'v' AS obj_type, viewname AS objectname, schemaname + '.' + viewname AS fullobj FROM pg_views
        WHERE schemaname not in ('pg_internal')
        ) AS objs
        ,(SELECT * FROM pg_user) AS usrs
    ORDER BY fullobj
    )
WHERE (sel = true or ins = true or upd = true or del = true or ref = true)
and schemaname='<opt schema>'
and usename = '<opt username>';

 schemaname | objectname | usename | sel | ins | upd | del | ref
------------+------------+---------+-----+-----+-----+-----+-----
(0 rows)

Related

• Amazon Redshift: show permissions
• \dn++
• GRANT

See also

• GRANT, \ddp, GRANT USAGE, GRANT ALL PRIVILEGES, GRANT SELECT, View GRANTs on Redshift, has_table_privilege, has_schema_privilege, HAS_DATABASE_PRIVILEGE, SCHEMA, Privileges, GRANT EXECUTE
• Amazon Redshift, aws redshift, Amazon Redshift query editor, GRANT, AQUA, has schema privilege, WLM, Amazon Redshift Spectrum, metrics, VACUUM, SET