Guide on How to Drop Users in Amazon Redshift Database

Hello and welcome to this shared tutorial on how to remove users from any database of your choice. While there may be slight differences across databases, I assure you that these steps should apply to any scenario. Recently, I've been focusing on advancing my career in data engineering,and i have had the opportunity to pick up few task in my current workplace on dropping a couple of users on our redshift database who probably have left or the username created are no longer in use.

I got stuck with the process and went online to source for few articles and blog that could be of help, eventually i got one in amazon AWS tutorial platform which was not practically explanatory but was helpful to prepare this post, so i decided to write a simple tutorial on this topic. If you are exploring how to drop or remove users from your database and you encoutered an error message that states "user cannot be dropped because some objects depend on it".  This tutorial is for you. I'll be outlining the steps you need to take to successfully drop such user.

Prerequisites

• You should be familiar with SQL syntax.
• You must have explored at least one or two database platform (Redshift)

Now let us walk through the steps you need to take in order to successfully drop any user of your choice from your database.

STEP 1: Create View to Generate Grant or Revoke DDL For Users and Groups.

This view should be created with any schema name available on the database you are working with and it is useful for recreating users, group privileges or for revoking privileges before dropping a user or group. you can find the code to creating this below:

Github Link 

STEP 2: Create View to Generate Objects Owned by User.

This is an handy view to create for further indepth check. it will help check if there are some objects created by the user you want to drop, in other to assign them to a super user or ignore if they are not in use. Find below link to the file for your review or download:

Github Link 

STEP 3: Change the Ownership of all Tables Owned by user.

There may be possibilty that the user you want to drop own a couple of tables on your database, if you go ahead to run the drop user command without changing those ownership, be sure to encounter an error stating "User 'Username_to_drop' cannot be dropped because suome objects depend on it". Running the below SQL code should output all the neccessary tables owned by the user that you need to change.

  
  select 'alter table '+schemaname+'.'+tablename+' owner to new_owner_name;' 
     from pg_tables 
 where tableowner like '%user_name_to_drop%';

Note: The output of the above SQL code can be exported into a CSV file, and all queries generated can be rerun on the database to successfully switch ownership of all tables created by the user you want to drop

STEP 4:   Revoke all Schema Privileges from the User to be dropped.

The next step after the above three mentioned, is to revoke all schema privelages from user, this should also output list of queries you'll rerun on the database to do the job. be sure to also export the output produced to a CSV file and reuse accordingly on your database. Find below the SQL code to use:

        
   
    select 
          distinct 'revoke all on schema '+schemaname+' from username_to_drop;' 
    from admin.v_find_dropuser_objs;     

STEP 5:  Revoke all Table Privileges Assigned to User.

Here is another crucial step to take, most users are always assigned privileges to tables on the database if not all, atleast 98% of the time, users get to enjoy this previlege. The SQL code shared below can help output all tables the user has previlege to either SELECT from or UPDATE.

          select 
  distinct  
   'revoke all on all tables in schema '+schemaname+' from username_to_drop;'
from admin.v_find_dropuser_objs;     

STEP 6: Explore The "Grant or Revoke DDL" View You Created in Step 1 For Further Check

There are instance when you are done with step 5, and you use the drop command on the user and everything work fine, but situation where by you still get an error message of not being able to drop the user, you should explore this next step for further investigation. This below query will output what was missed by step 3, 4 and 5. Do the needful by exporting the output as usual and run the queries provided on your database (Limit is optional)

          select 
   ddl from fivetran_sheets.v_generate_user_grant_revoke_ddl 
where ddltype='revoke' and (grantee='username_to_drop' or grantor='username_to_drop') 
order by objseq, grantseq desc
limit 9000;    

STEP 7: Drop User

DROP Username_to_drop;     

If you have successfully carried out the above highlighted steps, you should be able to use the drop command on the user without any error.

I hope this help you solve your problem. Feel free to leave a comment if this was useful and don't forget to share with anyone who might need this. keep an eye on this blog for more tutorials like this.

Thanks