amazon-web-services - 如何显示 Redshift Spectrum(外部架构)GRANTS？

Question

This post对于显示 Redshift GRANTS 很有用，但不会在外部表/架构上显示 GRANTS。

如何显示外部架构(和相关表)权限？

Answer 1

由于在外部表中只能 select 数据，因此这一项足以检查外部表的 usage 权限:

SELECT schemaname, objectname, usename,
       has_schema_privilege(usrs.usename, schemaname, 'usage')  AS usage
FROM(
    SELECT schemaname, tablename AS objectname, schemaname + '.' + tablename AS fullobj
    FROM SVV_EXTERNAL_TABLES
    ) AS objs,(SELECT * FROM pg_user) AS usrs