3

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?

Vzzarr
  • 4,600
  • 2
  • 43
  • 80

2 Answers2

9

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>';
Vzzarr
  • 4,600
  • 2
  • 43
  • 80
1

Slight improvement over the version from @Vzzarr

SELECT DISTINCT schemaname, usrs.usename, 'usage' as privilege,
    has_schema_privilege(usrs.usename, schemaname, 'usage')  AS usage
FROM SVV_EXTERNAL_TABLES, pg_user AS usrs
ORDER BY 1,2
Faiz
  • 5,331
  • 10
  • 45
  • 57