Skip to content

Instantly share code, notes, and snippets.

@bweigel

bweigel/get_userpermissions_for_schema.sql

Last active May 17, 2018 07:56
Show Gist options
  • Save bweigel/434edfc9e75e610e978aaee4854a7d3d to your computer and use it in GitHub Desktop.
Save bweigel/434edfc9e75e610e978aaee4854a7d3d to your computer and use it in GitHub Desktop.
Download ZIP
Get a view of the Users permission on all tables of a specified schema [Redshift]
Raw
get_userpermissions_for_schema.sql
SELECT *
FROM
(
SELECT
schemaname
,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 schemaname, 't' AS obj_type, tablename AS objectname, schemaname + '.' + tablename AS fullobj FROM pg_tables
WHERE schemaname not in ('pg_internal')
UNION
SELECT schemaname, 'v' AS obj_type, viewname AS objectname, schemaname + '.' + viewname AS fullobj FROM pg_views
WHERE schemaname not in ('pg_internal')
) AS objs
,(SELECT * FROM pg_user) AS usrs
ORDER BY fullobj
)
WHERE schemaname='schema'
and usename = 'user';
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment