Question:
I have an Amazon Redshift cluster with four schemas (Schema1, Schema2, Schema3 and Schema 4).
I created a user User1 in this cluster. I want this user to have only read-only access to all the tables in Schema1. Currently, this user has access(Select, Insert, Update, Delete) to all the tables from all the schemas.
I tried all the commands from the Redshift manual, but looks like nothing is working.
Example:
|
1 2 3 4 5 |
REVOKE ALL on schema schema1 from User1 REVOKE ALL on schema schema2 from User1 REVOKE ALL on schema schema3 from User1 REVOKE ALL on schema schema4 from User1 |
I also tried to revoke individual permissions (Insert, Update, Delete).
I also tried to revoke permissions (Insert, Update, Delete) from individual table
Tried all the combinations from the manual. I am using SQL Workbench and all the statements were successfully executed without any syntax error.
Not able to figure it. Any help is appreciated.
P.S. I have 15 years of database experience working on roles and permissions.
Answer:
In my case the issue I had was that I had 3 users belonging to the same group.
The group had been granted ALL privileges to all the tables of the schema.
Therefore revoking the permissions for a single user did not work since group permissions supersede user permissions.
TL;DR
The solution in my case was to create a group for each user and revoke access to the schema for the other groups.
REVOKE ALL ON SCHEMA my_schema FROM group my_group;