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.
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.
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.
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;