How to get an overview of users and roles and their priviliges on Redshift tables

Question

Hi,
With the following query I am able to see the privileges on schema level and I see there also my configured roles (which are connected with Azure Active Directory groups)

```
SELECT DISTINCT
       namespace_name as schema,
       privilege_type,
       identity_id,
       identity_type,
       identity_name
FROM svv_schema_privileges
ORDER BY identity_name;
```

But I would like the same on table(s) level, but here I find only solution where I get the overview of users, not roles. I want to see something like this:

Hope you can help me out.

Thanks!

René

Accepted Answer

Hello rene,

Below query will provide you user permission ( INSERT, UPDATE, DELETE ) at table level using the HAS_TABLE_PRIVILEGE function - https://docs.aws.amazon.com/redshift/latest/dg/r_HAS_TABLE_PRIVILEGE.html

select
    * 
from 
    (
    select schemaname
          ,obj_owner
          ,obj_type
          ,objectname
          ,usename
          ,has_table_privilege(usrs.usename, fullobj, 'select') as sel 
          ,has_table_privilege(usrs.usename, fullobj, 'insert') as ins
          ,has_table_privilege(usrs.usename, fullobj, 'update') as upd
          ,has_table_privilege(usrs.usename, fullobj, 'delete') as del
          ,has_table_privilege(usrs.usename, fullobj, 'references') as ref
      from (
           select pt.schemaname
                 ,pt.tableowner as obj_owner
                 ,'table' as obj_type
                 ,pt.tablename as objectname
                 ,quote_ident(pt.schemaname) || '.' || quote_ident(pt.tablename) as fullobj 
             from pg_tables pt
            where pt.schemaname not in ('pg_internal','pg_catalog','information_schema','pg_automv')
           union
           select pv.schemaname
                 ,pv.viewowner as obj_owner
                 ,case when position('materialized' in lower(pv.definition)) between 1 and 20 then 'materialized view' else 'view' end as obj_type
                 ,pv.viewname as objectname
                 ,quote_ident(pv.schemaname) || '.' || quote_ident(pv.viewname) as fullobj 
             from pg_views pv
           where pv.schemaname not in ('pg_internal','pg_catalog','information_schema','pg_automv')    
           ) 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);

Answer

Thanks, this works flawless!!

How to get an overview of users and roles and their priviliges on Redshift tables

table_name | identity_type | user or role | can select | can insert | can delete | can update

Relevanter Inhalt