I get error messages similar to the following when I try to drop a user or group in Amazon Redshift. How do I resolve this?

ERROR:  user 'username' cannot be dropped because the user has a privilege on some object
ERROR:  user 'username' cannot be dropped because the user owns some object

Drop a user

Before you drop a user, you must revoke any privileges that the user has and then transfer ownership of any database objects that the user owns.

1.    Download and install the v_generate_user_grant_revoke_ddl.sql script from the AWS Labs GitHub repository. This script creates a view in Amazon Redshift that is useful for the next two steps.

2.    Find all privileges granted to the user and then grant those privileges to the user again, as a superuser or another user, as shown in the following example.

select regexp_replace(ddl,grantor,'<superuser>') from admin.v_generate_user_grant_revoke_ddl where grantor='<username>' and ddltype='grant' and objtype <>'default acl' order by objseq,grantseq;

3.    Find all privileges granted to the user and then revoke those privileges, as shown in the following example.

select ddl from admin.v_generate_user_grant_revoke_ddl where ddltype='revoke' and (grantee='<username>' or grantor='<username>') order by objseq, grantseq desc;

4.    Download and install the v_find_dropuser_objs.sql script from the AWS Labs GitHub repository. This script creates a view in Amazon Redshift that is useful for the next step.

5.    Find all objects owned by the user and then transfer ownership to a different user. In the example below, <username> is the current owner and <newuser> is the new owner.

select ddl||'<newuser>;' as ddl from admin.v_find_dropuser_objs where objowner = '<username>';

6.    Repeat steps 2-5 in each database on the cluster.

7.    Drop the user.

Drop a group

Before you drop a group, you must revoke any privileges that the group has on an object.

1.    Download and install the v_generate_user_grant_revoke_ddl.sql script from the AWS Labs GitHub repository. This script creates a view in Amazon Redshift that is useful for the next step.

2.    Find all privileges granted to the group and then revoke those privileges, as shown in the following example.

select ddl from admin.v_generate_user_grant_revoke_ddl where ddltype='revoke' and username='<groupname>' order by sequence;

3.    Repeat step 2 in each database on the cluster to be sure that the group's privileges are revoked in all databases.

4.    Drop the group.


Did this page help you? Yes | No

Back to the AWS Support Knowledge Center

Need help? Visit the AWS Support Center

Published: 2018-08-16