How do I view grants on Redshift
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','pg_automv')
UNION
SELECT schemaname, 'v' AS obj_type, viewname AS objectname, schemaname + '.' + viewname AS fullobj FROM pg_views
WHERE schemaname not in ('pg_internal','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)
and schemaname='<opt schema>'
and usename = '<opt username>';
How to show Redshift Spectrum (external schema) GRANTS?
Since that in external tables it is possible to only select
data this one is enough to check usage
permission over the external tables:
SELECT schemaname, objectname, usename,
has_schema_privilege(usrs.usename, schemaname, 'usage') AS usage
FROM(
SELECT schemaname, tablename AS objectname, schemaname + '.' + tablename AS fullobj
FROM SVV_EXTERNAL_TABLES
) AS objs,(SELECT * FROM pg_user) AS usrs
Related Topics
How to Copy a Record in a SQL Table But Swap Out the Unique Id of the New Row
How in H2Db Get SQL Dump Like in MySQL
Ora-00972 Identifier Is Too Long Alias Column Name
How to Get a List of Element Names from an Xml Value in SQL Server
Check Whether String Is a Date Postgresql
SQL Server Select into @Variable
Is There Any Query for Cassandra as Same as SQL:Like Condition
Difference Between "On .. And" and "On .. Where" in SQL Left Join
How to Select Only the First Rows for Each Unique Value of a Column
Sql: Using Null Values VS. Default Values
Order by Items Must Appear in the Select List If Select Distinct Is Specified
Base 36 to Base 10 Conversion Using SQL Only
Hql: How to Perform an Inner Join on a Subquery
Oracle- Split String Comma Delimited (String Contains Spaces and Consecutive Commas)