如何找出在redshift中授予特定组的权限是什么 [英] How to find out what are the privileges granted to a specific group in redshift

问题描述

我使用的是亚马逊 AWS Redshift(8.0.2 版本).我创建了一个群组，授予了以下 2 项权限，并向该群组添加了一个用户.

I am using amazon AWS Redshift (8.0.2 version). I have created a group, granted the 2 permissions below, and added one user to that group.

如果我检查pg_group，我可以看到属于这个组的用户.但我还想查看授予该组的权限.

If I check pg_group, I can see the users who are members of this group. But I would also like to see the permissions granted to that group.

例如

redshift=# create group group1;
CREATE GROUP

redshift=# grant select on public.table_mar19_test2 to group group1;
GRANT

redshift=# alter group group1 add user user001;
ALTER GROUP

redshift=# select * from pg_group

    groname    | grosysid |  grolist
---------------+----------+-----------
 group1        |      101 | {148}
 (1 rows)

是否有任何 sql 查询可以找出授予该组的 select 授权?来自多个数据库的多个授权被授予一个组，我想看看哪个授权来自集群中的哪个数据库.

Is there any sql query to find out the select grant that was given to this group? Multiple grants from multiple databases are granted to one single group and I would like to see which grant is from which database in the cluster.

推荐答案

由于授权是针对每个对象的，因此您需要查询所有对象的权限.在这里您可以看到列出权限的 relacl 以及为授予这些权限而生成的授权语句:

Since grants are per object you need to query permissions of all objects. Here you can see the relacl that lists permissions and the grant statement generated to grant those permissions:

select relacl , 
'grant ' || substring(
            case when charindex('r',split_part(split_part(array_to_string(relacl, '|'),pu.groname,2 ) ,'/',1)) > 0 then ',select ' else '' end 
          ||case when charindex('w',split_part(split_part(array_to_string(relacl, '|'),pu.groname,2 ) ,'/',1)) > 0 then ',update ' else '' end 
          ||case when charindex('a',split_part(split_part(array_to_string(relacl, '|'),pu.groname,2 ) ,'/',1)) > 0 then ',insert ' else '' end 
          ||case when charindex('d',split_part(split_part(array_to_string(relacl, '|'),pu.groname,2 ) ,'/',1)) > 0 then ',delete ' else '' end 
          ||case when charindex('R',split_part(split_part(array_to_string(relacl, '|'),pu.groname,2 ) ,'/',1)) > 0 then ',rule ' else '' end 
          ||case when charindex('x',split_part(split_part(array_to_string(relacl, '|'),pu.groname,2 ) ,'/',1)) > 0 then ',references ' else '' end 
          ||case when charindex('t',split_part(split_part(array_to_string(relacl, '|'),pu.groname,2 ) ,'/',1)) > 0 then ',trigger ' else '' end 
          ||case when charindex('X',split_part(split_part(array_to_string(relacl, '|'),pu.groname,2 ) ,'/',1)) > 0 then ',execute ' else '' end 
          ||case when charindex('U',split_part(split_part(array_to_string(relacl, '|'),pu.groname,2 ) ,'/',1)) > 0 then ',usage ' else '' end 
          ||case when charindex('C',split_part(split_part(array_to_string(relacl, '|'),pu.groname,2 ) ,'/',1)) > 0 then ',create ' else '' end 
          ||case when charindex('T',split_part(split_part(array_to_string(relacl, '|'),pu.groname,2 ) ,'/',1)) > 0 then ',temporary ' else '' end 
       , 2,10000)
|| ' on '||namespace||'.'||item ||' to "'||pu.groname||'";' as grantsql
from 
(SELECT 
 use.usename as subject, 
 nsp.nspname as namespace, 
 c.relname as item, 
 c.relkind as type, 
 use2.usename as owner, 
 c.relacl 
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  
 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||'%' 
where relacl is not null
  and pu.groname='group1'
order by 2

这篇关于如何找出在redshift中授予特定组的权限是什么的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！