Amazon Redshift: show permissions

From wikieduonline
Jump to navigation Jump to search
SELECT
    u.usename,
    s.schemaname,
    has_schema_privilege(u.usename,s.schemaname,'create') AS user_has_create_permission,
    has_schema_privilege(u.usename,s.schemaname,'usage') AS user_has_usage_permission
FROM
    pg_user u
CROSS JOIN
    (SELECT DISTINCT schemaname FROM pg_tables) s
WHERE
    u.usename = 'myUserName'
    AND s.schemaname = 'mySchemaName'
;
 usename | schemaname | user_has_select_permission | user_has_usage_permission
---------+------------+----------------------------+---------------------------
(0 rows)
 usename | schemaname | user_has_select_permission | user_has_usage_permission
---------+------------+----------------------------+---------------------------
 your_user  | your_schema  | f                          | f
(1 row)


Grant privileges:

grant select on all tables in schema YOUR_SCHEMA to YOUR_USER;
grant create on all tables in schema YOUR_SCHEMA to YOUR_USER;
grant all on schema YOUR_SCHEMA to group YOUR_USER;

Related

See also

Advertising: