Difference between revisions of "Amazon Redshift: show permissions"
Jump to navigation
Jump to search
| (5 intermediate revisions by one other user not shown) | |||
| Line 2: | Line 2: | ||
u.usename, | u.usename, | ||
s.schemaname, | s.schemaname, | ||
| − | [[has_schema_privilege]](u.usename,s.schemaname,'[[create]]') AS | + | [[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 | has_schema_privilege(u.usename,s.schemaname,'[[usage]]') AS user_has_usage_permission | ||
FROM | FROM | ||
[[pg_user]] u | [[pg_user]] u | ||
[[CROSS JOIN]] | [[CROSS JOIN]] | ||
| − | (SELECT DISTINCT schemaname FROM [[pg_tables]]) s | + | ([[SELECT DISTINCT]] schemaname FROM [[pg_tables]]) s |
WHERE | WHERE | ||
u.usename = 'myUserName' | u.usename = 'myUserName' | ||
AND s.schemaname = 'mySchemaName' | AND s.schemaname = 'mySchemaName' | ||
; | ; | ||
| + | |||
| + | <pre> | ||
| + | usename | schemaname | user_has_select_permission | user_has_usage_permission | ||
| + | ---------+------------+----------------------------+--------------------------- | ||
| + | (0 rows) | ||
| + | </pre> | ||
| + | |||
| + | <pre> | ||
| + | usename | schemaname | user_has_select_permission | user_has_usage_permission | ||
| + | ---------+------------+----------------------------+--------------------------- | ||
| + | your_user | your_schema | f | f | ||
| + | (1 row) | ||
| + | </pre> | ||
Latest revision as of 13:46, 8 November 2022
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[edit]
See also[edit]
GRANT,\ddp,GRANT USAGE,GRANT ALL PRIVILEGES,GRANT SELECT, View GRANTs on Redshift,has_table_privilege,has_schema_privilege,HAS_DATABASE_PRIVILEGE,SCHEMA, Privileges,GRANT EXECUTE- Amazon Redshift,
aws redshift, Amazon Redshift query editor, GRANT, AQUA,has schema privilege, WLM, Amazon Redshift Spectrum, metrics, VACUUM, SET
Advertising:
