Redshift automatically update permissions on new table creation

0

Hi

I recently started working with Redshift and have an issue with permission management.

Assume multiple users need to access a single schema in the Redshift database. However, only a single user must have CREATE permission to create new tables with all other users only having SELECT permissions.

I have provisioned two roles user_write and user_read and assigned CREATE and SELECT permissions, respectively, and assigned the relevant users.

Can I know how to achieve the following

  1. Automatically allow users assigned to the user_read role to be able to view and read any new table created by the user_write role user. Any new table that is created by the user assigned to user_write role must be visible to anyone assigned to user_read role.
  2. Can we provide GRANT OPTION to a role so that the users assigned to that role can manage the permissions to the objects they create? Or a workaround to achieve the same.

If possible I would like to avoid granting permissions directly to the user. This is simply to reduce the management overhead and easily onboard and offboard users without having to individually manage each user.

Thank you, any help in this matter is greatly appreciated.

profile picture
Bisina
asked a year ago1114 views
1 Answer
1

To provide access on newly created tables in the schema to your role, you can use ALTER DEFAULT PRIVILEGES

Each "user_write" role user should execute the ALTER DEFAULT PRIVILEGE statement once before creating new tables. By executing this statement, the user is defining the privileges that should be automaticllay applied for the objects they are creating. Syntax for "Alter default privilege" is:

ALTER DEFAULT PRIVILEGES
    [ FOR USER target_user [, ...] ]
    [ IN SCHEMA schema_name [, ...] ]
    grant_or_revoke_clause

For example: consider user1 is assigned the role user_write. Before creating tables, user1 should execute the below ALTER DEFAULT PRIVILEGE statement

ALTER DEFAULT PRIVILEGES IN SCHEMA schema1 GRANT SELECT ON TABLES to role user_read;

By executing this, user1 is indicating that for all the tables they create, a SELECT is automatically granted to user_read role. From that point, any table that user1 creates will have an automatic SELECT grant to user_read role.

If there is another user user2 assigned to role user_write, they need to follow the same mentioned above

AWS
EXPERT
answered a year ago

You are not logged in. Log in to post an answer.

A good answer clearly answers the question and provides constructive feedback and encourages professional growth in the question asker.

Guidelines for Answering Questions