如何找出在redshift中授予一组的特权 [英] How to find out what are the privileges granted to one group in redshift

问题描述

团队，我使用的是Amazon redshift(8.0.2版) 我创建了一个组，并授予了以下2个权限，并将一个用户添加到该组中. 如果我勾选"pg_group"，我将能够看到该组成员的用户名. 但是我应该能够看到授予该组的许可是什么.

Team, I am using amazon redshift (8.0.2 version ) I have created one group and given below 2 permission, and added one user to that group. If i check 'pg_group', i will be able to see the user name who are members of this group. But i should be able to see what are the permissons 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查询来找出给予该组的选择授权? 来自多个数据库的多个授权将被分为一组.因此，我应该能够看到哪个授权来自集群中的哪个数据库.

now, do we have any sql query to find out the select grant given to this group ? Multiple grants from multiple databases are grnated one single group. so, i should be able to see which grant is from which database in the cluster.

请指导我.

推荐答案

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

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屋！