Good news for the RedShift customers now we can GRANT column-level permissions on the tables. It’s not only limited to tables, but we can also grant on views and materialized views as well. When the Lake formation was announced, this feature was a part of it. But unfortunately, we need to use Redshift Spectrum to achieve this. The wait is over now. Redshift natively supports the column level restrictions. Im experimenting and walk through this feature and test all the statements mentioned in the Redshift documentation.
Import a sample table:
For our experiment, we need sample data. So I have download a .csv file from mockaroo and then uploaded the CSV file into my S3 bucket.
I have executed the copy command for multiple times to make my table as some decent amount of rows.
Notes about column level ACL:
You can control the column level access only for SELECT and UPDATE.
Once you assigned some column level restriction, then that user should specifically mention the column names in the query. Select * will not work.
You can control the table, view and materialized views.
If you want to give both select and update to a user, then just use GRANT ALL (column names) will give both access to those columns.
Table Owner and Superusers can grant the column ACL.
Before we start our experiment, we can create a user for this.
#1 Grant select only access:
Let’s grant select access for a few columns and see how the user can access it in different ways.
Lets query other column and all columns.
#2 Grant UPDATE access
Now test with other columns.
#3 select + update togehter
#4 Test the statements from the RedShift Doc
If a user has a table-level privilege on a table, then granting the same privilege at the column level has no effect.
Anyhow this clearly explains the logic. So we can skip this.
If a user has a table-level privilege on a table, then revoking the same privilege for one or more columns of the table returns an error. Instead, revoke the privilege at the table level.
If a user has a column-level privilege, then granting the same privilege at the table level returns an error.
If a user has a column-level privilege, then revoking the same privilege at the table level revokes both column and table privileges for all columns on the table.
Note: If you want to revoke the select/update from a column level privilege user, then if you use just revoke select on or revoke update on will revoke the access. You can use this syntax for revoking access on table level/column level privilege users.
You can’t grant column-level privileges on late-binding views.
Try the grant table level access:
Try the same on late-binding view:
You must have table-level SELECT privilege on the base tables to create a materialized view. Even if you have column-level privileges on specific columns, you can’t create a materialized view on only those columns.
oh ho!!! What is this?
Because by default you have full access on public schema for all the users. Thats why its created.
Thanks AWS Support team for clarifying this. Lets see what happen if have your base table on the different schema?
Its not a big deal to work with column level ACL. But its worth to test every small feature. So you have better visibility about the feature and find the bugs like what we found above. I’ll update this blog once the AWS team confirms this as a bug or not.