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