Menu
I have a bit of a funny situation in Amazon Redshift where I have a user X who has grant select on all tables in schema public, but once a new table is created, this grant doesn't seem to apply to the new table. Is this normal behaviour? If yes, how does one deal with it such that the schema level grants are maintained. Thank you.
336
You can use following command, to give select access of specific table to specific user. GRANT SELECT on SCHEMA_NAME. TABLE_NAME TO USER_NAME; NOTE: user still list and describe other tables in the given schema.
To view the permissions of a specific user on a specific schema, simply change the bold user name and schema name to the user and schema of interest on the following code. For a full list of every user - schema permission status, simply delete the entire WHERE clause. SELECT u. usename, s.
Grant SELECT on all tables in a schema to a user Unfortunately, Oracle doesn't directly support this using a single SQL statement. To work around this, you can select all table names of a user (or a schema) and grant the SELECT object privilege on each table to a grantee.
If you are new to the AWS RedShift database and need to create schemas and grant access you can use the below SQL to manage this process To create a schema in your existing database run the below SQL and replace
Redshift GRANT command is used to control the security and access to the database and its objects for users and groups of users in Amazon Redshift. We can specify the options inside the command as for reading or writing the data from and to the database, tables, columns, schema, procedures, functions or language.
[ WITH GRANT OPTION ] The following is the syntax for using GRANT for datashare privileges on Amazon Redshift. ALTER and SHARE are the only privileges that you can grant to users and user groups in this case. GRANT { ALTER | SHARE } ON DATASHARE datashare_name TO { username [ WITH GRANT OPTION ] | GROUP group_name | PUBLIC } [, ...]
The following sequence of commands shows how access to a schema doesn't grant privileges on a table in the schema. The following sequence of commands shows how access to a view doesn't imply access to its underlying tables.
Executing the following command as super user (master):
alter default privileges for user staging_user in schema staging grant select on tables to reporting_user; will allow reporting_user to select data from all future tables created by staging_user in schema staging.
146
In Redshift tables and views do not automatically inherit the permissions of their parent schema. Your newly created tables are only accessible to the user who created them, and the superuser.
In a recent patch to Redshift a new feature to grant default privileges was implemented that addresses this issue.
Alter Default Privileges
The following code snippet will grant select privileges only for all future tables in the sales schema to the sales_admin group. If you want this to apply to existing tables in a schema you will need to combine it with a second grant statement.
alter default privileges in schema sales grant select on tables to group sales_admin; If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
