amazon-web-services - 跨 Amazon Redshift 中所有架构的用户或组的授权
问题描述
您如何使用 SQL 在 Amazon Redshift 中的所有架构中列出用户或组的所有授权?
解决方案
以下查询将为您提供所有用户和组权限。
SELECT derived_table1.schemaname,
derived_table1.objectname,
derived_table1.usename username,
'USER' usertype,
derived_table1.select_flag,
derived_table1.insert_flag,
derived_table1.update_flag,
derived_table1.delete_flag,
derived_table1.reference_flag
FROM ( SELECT objs.schemaname, objs.objectname, usrs.usename,
CASE
WHEN has_table_privilege(usrs.usename, objs.fullobj::text, 'select'::text) THEN 1
ELSE 0
END AS select_flag,
CASE
WHEN has_table_privilege(usrs.usename, objs.fullobj::text, 'insert'::text) THEN 1
ELSE 0
END AS insert_flag,
CASE
WHEN has_table_privilege(usrs.usename, objs.fullobj::text, 'update'::text) THEN 1
ELSE 0
END AS update_flag,
CASE
WHEN has_table_privilege(usrs.usename, objs.fullobj::text, 'delete'::text) THEN 1
ELSE 0
END AS delete_flag,
CASE
WHEN has_table_privilege(usrs.usename, objs.fullobj::text, 'references'::text) THEN 1
ELSE 0
END AS reference_flag
FROM ( SELECT pg_tables.schemaname, 't'::character varying AS obj_type, pg_tables.tablename AS objectname, (pg_tables.schemaname::text + '.'::text + pg_tables.tablename::text)::character varying AS fullobj
FROM pg_tables
UNION
SELECT pg_views.schemaname, 'v'::character varying AS obj_type, pg_views.viewname AS objectname, (pg_views.schemaname::text + '.'::text + pg_views.viewname::text)::character varying AS fullobj
FROM pg_views) objs,
(
SELECT pg_user.usename
FROM pg_user
) usrs
ORDER BY objs.fullobj) derived_table1
WHERE (derived_table1.select_flag + derived_table1.insert_flag + derived_table1.update_flag + derived_table1.delete_flag + derived_table1.reference_flag) > 0
and schemaname not in ('information_schema','pg_catalog')
union all
select schemname ,
objectname ,
username ,
usertype ,
CASE WHEN CHARINDEX('r', char_perms ) > 0 THEN 1 else 0 end select_flag,
CASE WHEN CHARINDEX('a', char_perms ) > 0 THEN 1 else 0 end insert_flag,
CASE WHEN CHARINDEX('w', char_perms ) > 0 THEN 1 else 0 end update_flag,
CASE WHEN CHARINDEX('d', char_perms ) > 0 THEN 1 else 0 end delete_flag,
CASE WHEN CHARINDEX('x', char_perms ) > 0 THEN 1 else 0 end references_flag
from
(
select namespace schemname,
item objectname,
groname username,
'GROUP' usertype,
SPLIT_PART( SPLIT_PART( ARRAY_TO_STRING( RELACL, '|' ), pu.groname, 2 ) , '/', 1 ) char_perms
from
(
SELECT use.usename AS subject
,nsp.nspname AS namespace
,cls.relname AS item
,cls.relkind AS type
,use2.usename AS owner
,cls.relacl
FROM pg_user use
CROSS JOIN pg_class cls
LEFT JOIN pg_namespace nsp
ON cls.relnamespace = nsp.oid
LEFT JOIN pg_user use2
ON cls.relowner = use2.usesysid
WHERE cls.relowner = use.usesysid
--AND nsp.nspname NOT IN ('pg_catalog', 'pg_toast', 'information_schema')
ORDER BY subject
,namespace
,item )
JOIN pg_group pu ON array_to_string(relacl, '|') LIKE '%'|| pu.groname ||'%'
);
推荐阅读
- python - 数据摄取:实时接收和解码消息文件(.txt)
- angular - Angular Flex 布局:圣杯设计
- java - Java/GlassFish MySQLNonTransientConnectionException
- angular - 根据上一个路由页面添加不同的动画
- django - 基于 request.user.is_staff 的类别链接
- android - 片段交易后RecyclerView项目消失
- javascript - 做出反应。如何仅对现有密钥使用 fetch 和 setState 处理 API 响应中缺少的密钥?
- php - 从 Mysql 数据库中选择,直到出现特定行问题
- python - python 如何使用 jumpssh 无密码连接
- regex - 匹配重复 3 次或更多次