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

哪个不起作用，因为relacl的:: text强制转换失败，并带有以下内容：

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

将查询修改为

CREATE OR REPLACE VIEW view_all_grants AS 
SELECT 
  use.usename as subject,c.relacl 
  --,)' || 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 ~ ('({|,item

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

如何修改视图以使用红移？还是有更好的/替代方法来查看红移上的授权？

更新：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,'insert') AND has_schema_privilege(usrs.usename,'usage')  AS ins,'update') AND has_schema_privilege(usrs.usename,'usage')  AS upd,'delete') AND has_schema_privilege(usrs.usename,'usage')  AS del,'references') AND has_schema_privilege(usrs.usename,'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>';

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!