本文介绍了如何查看 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 上的赠款的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!