Redshift table level security

Question

I have a common scenario where I need to drop and create an Amazon Redshift table or view (more often with views). There are situations where an ALTER is not going to work, which would preserve privileges.

What query can I use to identify all of the privileges on a particular table or view so that I can script out the reapplication of those privileges, after I perform a drop and create operation?

Answer 1

we have a bunch of utility scripts and views for this kind of thing on GitHub. This one is for table privileges: v_get_tbl_priv_by_user .

A couple of things to keep in mind to simplify your admin tasks:

• Use late binding views to minimize drop-recreate scenarios: WITH NO SCHEMA BINDING
• Use ALTER APPEND to move a large volume of rows between tables. Again avoiding expensive drop-rename or insert scenarios.

Answer 2

I modified a script that I found online. See below. This seems to meet my needs.

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
;