Search code examples
sqlamazon-web-servicesamazon-redshiftamazon-redshift-spectrum

How to show Redshift Spectrum (external schema) GRANTS?

This post is useful to show Redshift GRANTS but doesn't show GRANTS over external tables / schema.

How to show external schema (and relative tables) privileges?

Solution

  • Since that in external tables it is possible to only select data this one is enough to check usage permission over the external tables:

    SELECT schemaname, tablename, usename,
       has_schema_privilege(usrs.usename, schemaname, 'usage')  AS usage
FROM SVV_EXTERNAL_TABLES, pg_user AS usrs
WHERE schemaname = '<my-schema-name>'
  and usename = '<my-user>';