Amazon Redshift User Management Productive Queries

Amazon Redshift works really great for big data. You can run complex analytical queries on petabyte-scale data. If you are beginner with Amazon Web Service you can get started with below articles.

Amazon Redshift documentation is quite good. Having said that I find lack of Redshift query examples for handling user access control. Below I am going to explain list of Amazon Redshift queries which will be very helpful in your day to day work.

Create New Schema

Drop Existing Schema

CASCADE keyword indicates to automatically drop all objects in the schema, such as tables and functions. If you are trying to delete SCHEMA without deleting it’s tables and functions and no CASCADE option, it will throw error.

Create New User

Drop Existing User

 Revoke User Access from Table Name

You need to revoke user access from table before dropping user. Without revoking you will not be able to drop the user.

Create New Group

Amazon Redshift Create User in a Group

Grant All Privileges Access to User to All Tables

Grant All Privileges Access to User to All Tables with Grant Option

Grant Only Select Privileges to All Tables of Schema

Grant Privileges to Public

Grant Privileges to Group

Create Read Only Users

Creating read only users can be very tricky in Amazon Redshift. After reading Amazon Redshift documentation I ran below query:

I tried accessing [schema-name].[table-name] using below query after login from user [read-only-user-name].

Unfortunately it didn’t work. Below error was thrown:

Below is the right way to create read only users in Amazon Redshift.

Insert Complete Table Rows from One Schema Table to Other Schema Table

 List All Tables Present in a Schema

 View Table and Access Control by Users

View Live Running Queries in Amazon Redshift

Committing the Operation

Don’t forget to commit after every operation. If you are using SQL workbench client to connect with Amazon Redshift DB you can check auto-commit ON for all queries.

Cross DB Reference

Cross DB reference is not allowed in Amazon Redshift. For example below query is not permitted.

You will get below error if you try to do above:

Amazon Redshift has built in JSON functions that allow extracting data out of JSON. You can read from below how to handle big data using JSON function in Redshift.

You can read all AWS articles from below:

Thank you for reading my article. If you face any problem or having any doubts, let me know in comment. If you like my article please like our Facebook page and also follow us on Twitter.  For regular updates you can also subscribe to with your email.

Please also share on Facebook and Twitter to help other Amazon Redshift users.