Redshift 表級安全性

我有一個常見的場景，我需要刪除並創建一個 Amazon Redshift 表或視圖（更常見的是視圖）。在某些情況下 ALTER 不起作用，這將保留特權。

在執行刪除和創建操作後，我可以使用什麼查詢來辨識特定表或視圖上的所有權限，以便我可以編寫重新應用這些權限的腳本？

我們在 GitHub 上有很多實用程序腳本和視圖來處理這類事情。這一個用於表權限：v_get_tbl_priv_by_user。

為了簡化您的管理任務，需要牢記以下幾點：

• 使用後期綁定視圖來最大程度地減少 drop-recreate 場景：WITH NO SCHEMA BINDING
• 用於ALTER APPEND在表之間移動大量行。再次避免昂貴的刪除重命名或插入場景。

我修改了我在網上找到的一個腳本。見下文。這似乎滿足了我的需求。

select 
   --dt.relacl, 
   dt.subject,
   dt.subject_type,
   dt.namespace,
   dt.item,
   'grant ' || substring(
           case when charindex('r',split_part(split_part(array_to_string(relacl, '|'),dt.subject,2 ) ,'/',1)) > 0 and dt.subject_type = 'user' then ',select ' else '' end 
         ||case when charindex('w',split_part(split_part(array_to_string(relacl, '|'),dt.subject,2 ) ,'/',1)) > 0 and dt.subject_type = 'user' then ',update ' else '' end 
         ||case when charindex('a',split_part(split_part(array_to_string(relacl, '|'),dt.subject,2 ) ,'/',1)) > 0 and dt.subject_type = 'user' then ',insert ' else '' end 
         ||case when charindex('d',split_part(split_part(array_to_string(relacl, '|'),dt.subject,2 ) ,'/',1)) > 0 and dt.subject_type = 'user' then ',delete ' else '' end 
         ||case when charindex('R',split_part(split_part(array_to_string(relacl, '|'),dt.subject,2 ) ,'/',1)) > 0 and dt.subject_type = 'user' then ',rule ' else '' end 
         ||case when charindex('x',split_part(split_part(array_to_string(relacl, '|'),dt.subject,2 ) ,'/',1)) > 0 and dt.subject_type = 'user' then ',references ' else '' end 
         ||case when charindex('t',split_part(split_part(array_to_string(relacl, '|'),dt.subject,2 ) ,'/',1)) > 0 and dt.subject_type = 'user' then ',trigger ' else '' end 
         ||case when charindex('X',split_part(split_part(array_to_string(relacl, '|'),dt.subject,2 ) ,'/',1)) > 0 and dt.subject_type = 'user' then ',execute ' else '' end 
         ||case when charindex('U',split_part(split_part(array_to_string(relacl, '|'),dt.subject,2 ) ,'/',1)) > 0 and dt.subject_type = 'user' then ',usage ' else '' end 
         ||case when charindex('C',split_part(split_part(array_to_string(relacl, '|'),dt.subject,2 ) ,'/',1)) > 0 and dt.subject_type = 'user' then ',create ' else '' end 
         ||case when charindex('T',split_part(split_part(array_to_string(relacl, '|'),dt.subject,2 ) ,'/',1)) > 0 and dt.subject_type = 'user' then ',temporary ' else '' end 
         || case when charindex('r',split_part(split_part(array_to_string(relacl, '|'),'group '||dt.subject,2 ) ,'/',1)) > 0 and dt.subject_type = 'group' then ',select ' else '' end 
         ||case when charindex('w',split_part(split_part(array_to_string(relacl, '|'),'group '||dt.subject,2 ) ,'/',1)) > 0 and dt.subject_type = 'group' then ',update ' else '' end 
         ||case when charindex('a',split_part(split_part(array_to_string(relacl, '|'),'group '||dt.subject,2 ) ,'/',1)) > 0 and dt.subject_type = 'group' then ',insert ' else '' end 
         ||case when charindex('d',split_part(split_part(array_to_string(relacl, '|'),'group '||dt.subject,2 ) ,'/',1)) > 0 and dt.subject_type = 'group' then ',delete ' else '' end 
         ||case when charindex('R',split_part(split_part(array_to_string(relacl, '|'),'group '||dt.subject,2 ) ,'/',1)) > 0 and dt.subject_type = 'group' then ',rule ' else '' end 
         ||case when charindex('x',split_part(split_part(array_to_string(relacl, '|'),'group '||dt.subject,2 ) ,'/',1)) > 0 and dt.subject_type = 'group' then ',references ' else '' end 
         ||case when charindex('t',split_part(split_part(array_to_string(relacl, '|'),'group '||dt.subject,2 ) ,'/',1)) > 0 and dt.subject_type = 'group' then ',trigger ' else '' end 
         ||case when charindex('X',split_part(split_part(array_to_string(relacl, '|'),'group '||dt.subject,2 ) ,'/',1)) > 0 and dt.subject_type = 'group' then ',execute ' else '' end 
         ||case when charindex('U',split_part(split_part(array_to_string(relacl, '|'),'group '||dt.subject,2 ) ,'/',1)) > 0 and dt.subject_type = 'group' then ',usage ' else '' end 
         ||case when charindex('C',split_part(split_part(array_to_string(relacl, '|'),'group '||dt.subject,2 ) ,'/',1)) > 0 and dt.subject_type = 'group' then ',create ' else '' end 
         ||case when charindex('T',split_part(split_part(array_to_string(relacl, '|'),'group '||dt.subject,2 ) ,'/',1)) > 0 and dt.subject_type = 'group' then ',temporary ' else '' end 
      , 2,10000)
   || ' on '||namespace||'.'||item ||' to '||(case when dt.subject_type = 'group' then 'group ' else '' end)||'"'||dt.subject||'";' as grant_sql
from 
(
   SELECT 
       use.usename as subject, 
       'user' as subject_type,
       nsp.nspname as namespace, 
       c.relname as item, 
       c.relkind as type, 
       own.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 own on (c.relowner = own.usesysid)
   WHERE 
       array_to_string(c.relacl, '|') LIKE ('%' + use.usename + '%')
       and nsp.nspname   NOT IN ('pg_catalog', 'pg_toast', 'information_schema')
   UNION ALL
   SELECT 
       grp.groname as subject,  
       'group' as subject_type,
       nsp.nspname as namespace, 
       c.relname as item, 
       c.relkind as type, 
       own.usename as owner, 
       c.relacl
   FROM 
       pg_group grp
       cross join pg_class c 
       left join pg_namespace nsp on (c.relnamespace = nsp.oid) 
       left join pg_user own on (c.relowner = own.usesysid)
   WHERE 
       array_to_string(c.relacl, '|') LIKE ('%group ' + grp.groname + '%')
       and nsp.nspname   NOT IN ('pg_catalog', 'pg_toast', 'information_schema')
) dt
where dt.relacl is not null
order by dt.subject
;

引用自：https://dba.stackexchange.com/questions/215097