如何在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

Which doesn't work because the ::text cast of relacl fails with the following:

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

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

Modifying the query to

将查询修改为

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.

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

How can I modify the view to work on redshift or is there an better/alternative way to view grants on redshift ?

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

UPDATE: Redshift has the HAS_TABLE_PRIVILEGE function to check grants. (see here)

更新：Redshift具有HAS_TABLE_PRIVILEGE函数来检查授权。（看这里）

6 个解决方案

#1

Another variation be like:

另一种变化如下：

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>';

#2

Something along the lines off:

沿线的东西：

select tablename, 
   HAS_TABLE_PRIVILEGE(tablename, 'select') as select,
   HAS_TABLE_PRIVILEGE(tablename, 'insert') as insert,
   HAS_TABLE_PRIVILEGE(tablename, 'update') as update,
   HAS_TABLE_PRIVILEGE(tablename, 'delete') as delete, 
   HAS_TABLE_PRIVILEGE(tablename, 'references') as references 
from pg_tables where schemaname='public' order by tablename;

gives me all I need.

给了我所需要的一切。

#3

The has_table_privilege function is handy, but doesn't always help in administration when you want to manage groups. I morphed your original query to create grant scripts for specific users or groups. This sample query can be easily morphed to accommodate your needs

has_table_privilege函数很方便，但是当您想要管理组时，它并不总是有助于管理。我改变了您的原始查询，以便为特定用户或组创建授权脚本。此示例查询可以轻松变换以满足您的需求

select namespace||'.'||item as tablename , 
'grant ' || substring(
                case when charindex('r',split_part(split_part(array_to_string(relacl, '|'),'group dw_developers=',2 ) ,'/',1)) > 0 then ',select ' else '' end 
              ||case when charindex('w',split_part(split_part(array_to_string(relacl, '|'),'group dw_developers=',2 ) ,'/',1)) > 0 then ',update ' else '' end 
              ||case when charindex('a',split_part(split_part(array_to_string(relacl, '|'),'group dw_developers=',2 ) ,'/',1)) > 0 then ',insert ' else '' end 
              ||case when charindex('d',split_part(split_part(array_to_string(relacl, '|'),'group dw_developers=',2 ) ,'/',1)) > 0 then ',delete ' else '' end 
              ||case when charindex('R',split_part(split_part(array_to_string(relacl, '|'),'group dw_developers=',2 ) ,'/',1)) > 0 then ',rule ' else '' end 
              ||case when charindex('x',split_part(split_part(array_to_string(relacl, '|'),'group dw_developers=',2 ) ,'/',1)) > 0 then ',references ' else '' end 
              ||case when charindex('t',split_part(split_part(array_to_string(relacl, '|'),'group dw_developers=',2 ) ,'/',1)) > 0 then ',trigger ' else '' end 
              ||case when charindex('X',split_part(split_part(array_to_string(relacl, '|'),'group dw_developers=',2 ) ,'/',1)) > 0 then ',execute ' else '' end 
              ||case when charindex('U',split_part(split_part(array_to_string(relacl, '|'),'group dw_developers=',2 ) ,'/',1)) > 0 then ',usage ' else '' end 
              ||case when charindex('C',split_part(split_part(array_to_string(relacl, '|'),'group dw_developers=',2 ) ,'/',1)) > 0 then ',create ' else '' end 
              ||case when charindex('T',split_part(split_part(array_to_string(relacl, '|'),'group dw_developers=',2 ) ,'/',1)) > 0 then ',temporary ' else '' end 
           , 2,10000)
 || ' on '||namespace||'.'||item ||' to group dw_developers;' 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 
 ) where relacl is not null
 and array_to_string(relacl, '|') like '%group dw_developers%' order by 1

#4

A development on the answer from mike_pdb I came up with the following

关于mike_pdb答案的开发我想出了以下内容

 WITH object_list(schema_name,object_name,permission_info)
 AS (
    SELECT N.nspname, C.relname, array_to_string(relacl,',')
    FROM pg_class AS C
        INNER JOIN pg_namespace AS N
        ON C.relnamespace = N.oid
    WHERE C.relkind in ('v','r')
    AND  N.nspname NOT IN ('pg_catalog', 'pg_toast', 'information_schema')
    AND C.relacl[1] IS NOT NULL
  ),
  object_permissions(schema_name,object_name,permission_string)
  AS (
    SELECT schema_name,object_name, SPLIT_PART(permission_info,',',1) FROM object_list
    UNION ALL
    SELECT schema_name,object_name, SPLIT_PART(permission_info,',',2) FROM object_list
    UNION ALL
    SELECT schema_name,object_name, SPLIT_PART(permission_info,',',3) FROM object_list
    UNION ALL
    SELECT schema_name,object_name, SPLIT_PART(permission_info,',',4) FROM object_list
    UNION ALL
    SELECT schema_name,object_name, SPLIT_PART(permission_info,',',5) FROM object_list
    UNION ALL
    SELECT schema_name,object_name, SPLIT_PART(permission_info,',',6) FROM object_list
    UNION ALL
    SELECT schema_name,object_name, SPLIT_PART(permission_info,',',7) FROM object_list
    UNION ALL
    SELECT schema_name,object_name, SPLIT_PART(permission_info,',',8) FROM object_list
    UNION ALL
    SELECT schema_name,object_name, SPLIT_PART(permission_info,',',9) FROM object_list
    UNION ALL
    SELECT schema_name,object_name, SPLIT_PART(permission_info,',',10) FROM object_list
  ),
  permission_parts(schema_name, object_name,security_principal, permission_pattern)
  AS (
      SELECT
          schema_name,
          object_name,
          LEFT(permission_string ,CHARINDEX('=',permission_string)-1),
          SPLIT_PART(SPLIT_PART(permission_string,'=',2),'/',1)
      FROM object_permissions
      WHERE permission_string >''
  )
SELECT
    schema_name,
    object_name,
    'GRANT ' ||
    SUBSTRING(
        case when charindex('r',permission_pattern) > 0 then ',SELECT ' else '' end
      ||case when charindex('w',permission_pattern) > 0 then ',UPDATE ' else '' end
      ||case when charindex('a',permission_pattern) > 0 then ',INSERT ' else '' end
      ||case when charindex('d',permission_pattern) > 0 then ',DELETE ' else '' end
      ||case when charindex('R',permission_pattern) > 0 then ',RULE ' else '' end
      ||case when charindex('x',permission_pattern) > 0 then ',REFERENCES ' else '' end
      ||case when charindex('t',permission_pattern) > 0 then ',TRIGGER ' else '' end
      ||case when charindex('X',permission_pattern) > 0 then ',EXECUTE ' else '' end
      ||case when charindex('U',permission_pattern) > 0 then ',USAGE ' else '' end
      ||case when charindex('C',permission_pattern) > 0 then ',CREATE ' else '' end
      ||case when charindex('T',permission_pattern) > 0 then ',TEMPORARY ' else '' end
    ,2,10000
    )
    || ' ON ' || schema_name||'.'||object_name
     || ' TO ' || security_principal
     || ';' as grantsql
FROM permission_parts

;

There are 3 common table expressions used here.

这里使用了3个常用的表表达式。

object_list - Tables and views with their permissions array as a comma delimited string.
object_list - 将权限数组作为逗号分隔字符串的表和视图。
object_permissions - schema/object (table or view) and a record per permission string. Note that the SPLIT_PART function does not allow a dynamic part position so the assumption here is that there are no more than 10 users or groups assigned direct permissions
object_permissions - 模式/对象（表或视图）和每个权限字符串的记录。请注意，SPLIT_PART函数不允许动态零件位置，因此这里假设不超过10个用户或组分配了直接权限
permission_parts The schema/object, security principal to whom permissions are granted and the security attributes that are set.
permission_parts模式/对象，授予权限的安全主体以及设置的安全属性。

As per mike_pdb's solution the individual permission characters are converted into a concatenated list of grants. As we don't which grants will be used we use SUBSTRING from position 2 to discard the first comma in the list.

根据mike_pdb的解决方案，各个权限字符将转换为连续的授权列表。由于我们没有使用哪个授权，我们使用位置2的SUBSTRING来丢弃列表中的第一个逗号。

You can use exactly the same approach for scripting off schema permissions

您可以使用完全相同的方法来编写架构权限的脚本

WITH schema_list(schema_name, permission_info)
AS (
    SELECT nspname, array_to_string(nspacl,',')
    FROM pg_namespace
    WHERE nspacl[1] IS NOT NULL
    AND nspname NOT LIKE 'pg%' AND nspname NOT IN ('public','information_schema')
),
schema_permissions(schema_name,permission_string)
AS (
    SELECT schema_name,SPLIT_PART(permission_info,',',1) FROM schema_list
    UNION ALL
    SELECT schema_name,SPLIT_PART(permission_info,',',2) FROM schema_list
    UNION ALL
    SELECT schema_name,SPLIT_PART(permission_info,',',3) FROM schema_list
    UNION ALL
    SELECT schema_name,SPLIT_PART(permission_info,',',4) FROM schema_list
    UNION ALL
    SELECT schema_name,SPLIT_PART(permission_info,',',5) FROM schema_list
    UNION ALL
    SELECT schema_name,SPLIT_PART(permission_info,',',6) FROM schema_list
    UNION ALL
    SELECT schema_name,SPLIT_PART(permission_info,',',7) FROM schema_list
    UNION ALL
    SELECT schema_name,SPLIT_PART(permission_info,',',8) FROM schema_list
    UNION ALL
    SELECT schema_name,SPLIT_PART(permission_info,',',9) FROM schema_list
    UNION ALL
    SELECT schema_name,SPLIT_PART(permission_info,',',10) FROM schema_list
),
permission_parts(schema_name, security_principal, permission_pattern)
AS (
    SELECT
        schema_name,
        LEFT(permission_string ,CHARINDEX('=',permission_string)-1),
        SPLIT_PART(SPLIT_PART(permission_string,'=',2),'/',1)
    FROM schema_permissions
    WHERE permission_string >''
)
SELECT
    schema_name,
    'GRANT ' ||
    SUBSTRING(
        case when charindex('r',permission_pattern) > 0 then ',SELECT ' else '' end
      ||case when charindex('w',permission_pattern) > 0 then ',UPDATE ' else '' end
      ||case when charindex('a',permission_pattern) > 0 then ',INSERT ' else '' end
      ||case when charindex('d',permission_pattern) > 0 then ',DELETE ' else '' end
      ||case when charindex('R',permission_pattern) > 0 then ',RULE ' else '' end
      ||case when charindex('x',permission_pattern) > 0 then ',REFERENCES ' else '' end
      ||case when charindex('t',permission_pattern) > 0 then ',TRIGGER ' else '' end
      ||case when charindex('X',permission_pattern) > 0 then ',EXECUTE ' else '' end
      ||case when charindex('U',permission_pattern) > 0 then ',USAGE ' else '' end
      ||case when charindex('C',permission_pattern) > 0 then ',CREATE ' else '' end
      ||case when charindex('T',permission_pattern) > 0 then ',TEMPORARY ' else '' end
    ,2,10000
    )
    || ' ON SCHEMA ' || schema_name
     || ' TO ' || security_principal
     || ';' as grantsql
FROM permission_parts;

#5

Here is another useful query to view grants on schema (usage, create) by user that I created based on the query above by @drtf:

这是另一个有用的查询，用于查看我根据@drtf上面的查询创建的模式（使用，创建）的授权：

SELECT * 
FROM 
    (
    SELECT 
        schemaname
        ,usename
        ,HAS_SCHEMA_PRIVILEGE(usrs.usename, schemaname, 'usage') AS usg
        ,HAS_SCHEMA_PRIVILEGE(usrs.usename, schemaname, 'create') AS crt
    FROM
        (
        SELECT distinct(schemaname) FROM pg_tables
        WHERE schemaname not in ('pg_internal')
        UNION
        SELECT distinct(schemaname) FROM pg_views
        WHERE schemaname not in ('pg_internal')
        ) AS objs
        ,(SELECT * FROM pg_user) AS usrs
    ORDER BY schemaname
    )
WHERE (usg = true or crt = true)
--and schemaname='<opt schemaname>'
--and usename = '<opt username>';

#6

I struggled with this a lot and finally came up with a solution that gives me just what I want to see.

我经历了很多努力，终于提出了一个解决方案，让我能够看到我想要的东西。

WITH tabledef as (
    SELECT schemaname,
        't' AS typename,
        tablename AS objectname,
        tableowner as owner,
        schemaname + '.' + tablename AS fullname
    FROM pg_tables
    UNION 
    SELECT schemaname,
        'v' AS typename,
        viewname AS objectname,
        viewowner as owner,
        schemaname + '.' + viewname AS fullname
    FROM pg_views
),
res AS (
    SELECT t.*,
    CASE HAS_TABLE_PRIVILEGE(u.usename, t.fullname, 'select')
    WHEN true THEN u.usename
    ELSE NULL END AS sel,
    CASE HAS_TABLE_PRIVILEGE(u.usename, t.fullname, 'insert')
    WHEN true THEN u.usename
    ELSE NULL END AS ins,
    CASE HAS_TABLE_PRIVILEGE(u.usename, t.fullname, 'update')
    WHEN true THEN u.usename
    ELSE NULL END AS upd,
    CASE HAS_TABLE_PRIVILEGE(u.usename, t.fullname, 'delete')
    WHEN true THEN u.usename
    ELSE NULL END AS del,
    CASE HAS_TABLE_PRIVILEGE(u.usename, t.fullname, 'references')
    WHEN true THEN u.usename
    ELSE NULL END AS ref
    FROM tabledef AS t
    JOIN pg_user AS u
    ON HAS_TABLE_PRIVILEGE(u.usename, t.fullname, 'select') = true
        OR HAS_TABLE_PRIVILEGE(u.usename, t.fullname, 'insert') = true
        OR HAS_TABLE_PRIVILEGE(u.usename, t.fullname, 'update') = true
        OR HAS_TABLE_PRIVILEGE(u.usename, t.fullname, 'delete') = true
        OR HAS_TABLE_PRIVILEGE(u.usename, t.fullname, 'references') = true
        OR t.owner = u.usename
    WHERE t.schemaname = 'analytics'
)
SELECT schemaname, objectname, owner, sel, ins, upd, del, ref FROM res
WHERE sel not in ('rdsdb', '<superuser>')
ORDER BY schemaname, objectname;

The two important lines - One which points which schema to scan for access

两个重要的行 - 一个指向要扫描访问的模式

WHERE t.schemaname = 'analytics'

And - Second which discards superuser permissions (They have complete permission anyway) from the results.

并且 - 第二个从结果中丢弃超级用户权限（他们仍然拥有完全权限）。

WHERE sel not in ('rdsdb', '<superuser>')

#1