CODEWITHSUNDEEP
amazon-web-servicesamazon-redshift
Andres Urrego Angel
Andres Urrego Angel

Reputation: 1932

drop a user in aws redshift

I've created a user in redshift for a database, then I granted few SELECT permissions in a schema. Now I need to delete but I can't because the system insists that even after revoking all permissions the user can't be dropped because it has still access to some object.

When I create the user:

CREATE USER etlglue WITH
  PASSWORD '******';

grant select on all tables in schema tbl to etlglue;

Now when I try to drop:

REVOKE ALL PRIVILEGES  ON SCHEMA tbl FROM etlglue;
REVOKE ALL PRIVILEGES ON SCHEMA public FROM etlglue;
REVOKE ALL PRIVILEGES  ON DATABASE db FROM etlglue;

DROP USER etlglue;

I have tried to apply even a CASCADE in the REVOKE command but neither, I went to the documentation here. But the output:

enter image description here

Upvotes: 3

Views: 8235

Answers (2)

algorithmor
algorithmor

Reputation: 37

Use the following script from aws-labs to find out what objects are owned by the user that you are trying to drop.

https://github.com/awslabs/amazon-redshift-utils/blob/master/src/AdminScripts/user_to_be_dropped_objs.sql

After that edit/drop object and proceed.

Upvotes: 1

user_default
user_default

Reputation: 416

Revoking privileges off schema does not automatically revoke the privileges granted on tables in that schema. Try executing

REVOKE ALL PRIVILEGES ON ALL TABLES IN SCHEMA tbl FROM etlglue.

along with your other REVOKE statements.

Upvotes: 2

Related Questions