如何查看 Redshift 上的赠款

Question

我想查看红移补助金。

我 为 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

允许创建视图，但我担心这并未显示所有相关数据。

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

更新： Redshift 有 HAS_TABLE_PRIVILEGE 函数来检查授权。 （见 这里）

原文由 sw1nn 发布，翻译遵循 CC BY-SA 4.0 许可协议

Answer 1

另一种变化是：

 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','pg_automv')
        UNION
        SELECT schemaname, 'v' AS obj_type, viewname AS objectname, schemaname + '.' + viewname AS fullobj FROM pg_views
        WHERE schemaname not in ('pg_internal','pg_automv')
        ) 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>';

原文由 drtf 发布，翻译遵循 CC BY-SA 4.0 许可协议

Answer 2

我为此苦苦挣扎，最后想出了一个解决方案，它给了我想要看到的东西。

 WITH tabledef as (
    SELECT schemaname,
        't' AS typename,
        tablename AS objectname,
        tableowner as owner,
        schemaname + '.' + tablename AS fullname
    FROM pg_tables
    UNION
    SELECT schemaname,
        'v' AS typename,
        viewname AS objectname,
        viewowner as owner,
        schemaname + '.' + viewname AS fullname
    FROM pg_views
),
res AS (
    SELECT t.*,
    CASE HAS_TABLE_PRIVILEGE(u.usename, t.fullname, 'select')
    WHEN true THEN u.usename
    ELSE NULL END AS sel,
    CASE HAS_TABLE_PRIVILEGE(u.usename, t.fullname, 'insert')
    WHEN true THEN u.usename
    ELSE NULL END AS ins,
    CASE HAS_TABLE_PRIVILEGE(u.usename, t.fullname, 'update')
    WHEN true THEN u.usename
    ELSE NULL END AS upd,
    CASE HAS_TABLE_PRIVILEGE(u.usename, t.fullname, 'delete')
    WHEN true THEN u.usename
    ELSE NULL END AS del,
    CASE HAS_TABLE_PRIVILEGE(u.usename, t.fullname, 'references')
    WHEN true THEN u.usename
    ELSE NULL END AS ref
    FROM tabledef AS t
    JOIN pg_user AS u
    ON HAS_TABLE_PRIVILEGE(u.usename, t.fullname, 'select') = true
        OR HAS_TABLE_PRIVILEGE(u.usename, t.fullname, 'insert') = true
        OR HAS_TABLE_PRIVILEGE(u.usename, t.fullname, 'update') = true
        OR HAS_TABLE_PRIVILEGE(u.usename, t.fullname, 'delete') = true
        OR HAS_TABLE_PRIVILEGE(u.usename, t.fullname, 'references') = true
        OR t.owner = u.usename
    WHERE t.schemaname = 'analytics'
)
SELECT schemaname, objectname, owner, sel, ins, upd, del, ref FROM res
WHERE sel not in ('rdsdb', '<superuser>')
ORDER BY schemaname, objectname;

两条重要的线 - 一条指出要扫描访问的架构

WHERE t.schemaname = 'analytics'

并且 - 第二个从结果中丢弃超级用户权限（他们仍然拥有完整的权限）。

 WHERE sel not in ('rdsdb', '<superuser>')

原文由 user1741851 发布，翻译遵循 CC BY-SA 3.0 许可协议