Redshift如何通过元数据查询外部Schema组级访问权限列表

Redshift中所有Schema（含外部Schema）的用户/用户组授权信息都存储在系统视图svv_schema_privileges中，两类需求都可以直接查询该视图实现，无需关联多张底层系统表。

直接筛选视图中对应用户组的USAGE权限记录即可，SQL示例如下：

SELECT
  schemaname AS schema_name,
  privilege_type
FROM svv_schema_privileges
WHERE
  groname IN ('Test_Group_A', 'Test_Group_AB') -- 括号内替换为需要查询的目标用户组名即可
  AND privilege_type = 'USAGE'; -- USAGE即为Schema的访问权限

如果只需要查询用户组有权访问的外部Schema，可以关联外部Schema系统视图做过滤：

SELECT
  p.schemaname AS external_schema_name,
  p.privilege_type
FROM svv_schema_privileges p
INNER JOIN svv_external_schemas s
  ON p.schemaname = s.schemaname
WHERE
  p.groname IN ('Test_Group_A', 'Test_Group_AB')
  AND p.privilege_type = 'USAGE';

反向筛选视图中对应Schema的USAGE权限记录即可，SQL示例如下：

SELECT
  groname AS user_group_name,
  privilege_type
FROM svv_schema_privileges
WHERE
  schemaname IN ('External_Schema_A', 'External_Schema_B') -- 括号内替换为需要查询的目标Schema名即可
  AND privilege_type = 'USAGE';

按照你之前配置的授权规则执行上述查询，会返回匹配预期的结果：

• External_Schema_A对应授权组：Test_Group_A、Test_Group_AB
• External_Schema_B对应授权组：Test_Group_AB

注意：执行上述查询的账号需要拥有系统视图的SELECT权限，数据库超级用户、被授予系统表查看权限的用户均可正常返回结果。

内容的提问来源于stack exchange，提问作者AeyJey