本文介绍了如何查看 Redshift 上的赠款的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

我想查看关于红移的赠款.

I'd like to view grants on redshifts.

我发现 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

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

Which doesn't work because the ::text cast of relacl fails with the following:

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

允许创建视图,但我担心这不会显示所有相关数据.

Allows the view to be created, but I'm concerned that this is not showing all relevant data.

如何修改视图以在 redshift 上工作,或者是否有更好/替代的方式来查看 redshift 上的赠款?

How can I modify the view to work on redshift or is there an better/alternative way to view grants on redshift ?

更新:Redshift 有 HAS_TABLE_PRIVILEGE 函数来检查授权.(请参阅此处)

UPDATE: Redshift has the HAS_TABLE_PRIVILEGE function to check grants. (see 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>';

这篇关于如何查看 Redshift 上的赠款的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!

09-13 06:41
查看更多