sql - 我如何查看Redshift上的赠款

我想查看关于红移的补助金。

我发现this view for postgres:

CREATE OR REPLACE VIEW view_all_grants AS
SELECT
  use.usename as subject,
  nsp.nspname as namespace,
  c.relname as item,
  c.relkind as type,
  use2.usename as owner,
  c.relacl,
  (use2.usename != use.usename and c.relacl::text !~ ('({|,)' || use.usename || '=')) as public
FROM
  pg_user use
  cross join pg_class c
  left join pg_namespace nsp on (c.relnamespace = nsp.oid)
  left join pg_user use2 on (c.relowner = use2.usesysid)
WHERE
  c.relowner = use.usesysid or
  c.relacl::text ~ ('({|,)(|' || use.usename || ')=')
ORDER BY
  subject,
  namespace,
  item

这不起作用，因为::text的relacl强制转换失败并显示以下内容:

ERROR: cannot cast type aclitem[] to character varying [SQL State=42846]

将查询修改为

CREATE OR REPLACE VIEW view_all_grants AS
SELECT
  use.usename as subject,
  nsp.nspname as namespace,
  c.relname as item,
  c.relkind as type,
  use2.usename as owner,
  c.relacl
  -- , (use2.usename != use.usename and c.relacl::text !~ ('({|,)' || use.usename || '=')) as public
FROM
  pg_user use
  cross join pg_class c
  left join pg_namespace nsp on (c.relnamespace = nsp.oid)
  left join pg_user use2 on (c.relowner = use2.usesysid)
WHERE
  c.relowner = use.usesysid
  -- or c.relacl::text ~ ('({|,)(|' || use.usename || ')=')
ORDER BY
  subject,
  namespace,
  item

允许创建 View ，但是我担心这不会显示所有相关数据。

如何修改 View 以在redshift上工作，或者是否有更好的/替代方法来查看redshift上的授权？

更新: Redshift具有HAS_TABLE_PRIVILEGE函数来检查授予。 (请参阅here)

最佳答案

另一个变化是:

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>';

关于sql - 我如何查看Redshift上的赠款，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/18741334/