Redshift table level security

awsredshift

I have a common scenario where I need to drop and create an Amazon Redshift table or view (more often with views). There are situations where an ALTER is not going to work, which would preserve privileges.

What query can I use to identify all of the privileges on a particular table or view so that I can script out the reapplication of those privileges, after I perform a drop and create operation?

Best Answer

we have a bunch of utility scripts and views for this kind of thing on GitHub. This one is for table privileges: v_get_tbl_priv_by_user .

A couple of things to keep in mind to simplify your admin tasks:

Use late binding views to minimize drop-recreate scenarios: WITH NO SCHEMA BINDING
Use ALTER APPEND to move a large volume of rows between tables. Again avoiding expensive drop-rename or insert scenarios.

Related Solutions

Managing code and deployments to Amazon Redshift

In general there is very little tool support at present for Redshift. We do all of our automation and deployment via psql shell scripts and we manage the code base using git version control.

As far as schema changes we use a "migrations" approach (inspired by Rails) where we create ALTER TABLE …ADD COLUMN and ALTER TABLE …DROP COLUMN scripts to roll the database forward/back from one deployment to the next.

We also use 3 separate database on the same cluster for Dev/Test/Prod. We then specify the target database in the psql script using the -d option.

Hope that helps.

Issue using COPY command with AWS Redshift

Perhaps the JSON you have is not quite correct. When I paste your JSON into jsonlint.com I get the following:

Parse error on line 7: 
...        "username": “user23"} ] }
-----------------------^
Expecting 'STRING', 'NUMBER', 'NULL', 'TRUE', 'FALSE', '{', '['

The AWS Redshift documentation on COPY SSH parameters has the following

The quote characters must be simple quotation marks (0x22), not slanted or "smart" quotation marks.

So double check you quote marks around the username you've provided, and if they are how you've provided them above, change it from to “user23" >> "user23" (note the first quote mark is different).

The first quote mark in your code is unicode : U+201C and not U+0022

Best Answer

Related Solutions

Managing code and deployments to Amazon Redshift

Issue using COPY command with AWS Redshift

Related Question