Remove MySQL User Account Using DROP USER Statement

In this tutorial, we will learn how can we remove the user accounts and their privileges from the system. Remember that only the root user and the other users with global CREATE and DELETE privileges can remove other users from the database system.

MySQL offers complete control to the user over a database system. Whether it is a database or a user, the administrator can control anything. The versatile structure of a MySQL DBMS makes it one of the most popular DBMS globally.

We will first create a couple of users with all privileges, and then we will perform DROP operations on it. If you don’t know what MySQL user is and how to create them, we have created a complete guide on it.

Prerequisites

Before diving into the tutorial, make sure you have started the MySQL server and logged in with a root user. The root user account is an administrator account and comes with all the privileges you need to perform actions on other users.

We will use the MySQL command-line interface throughout this tutorial. However, you can create and delete users from MySQL workbench.

We will create MySQL users in this tutorial. So, it is recommended to have knowledge about creating users and assigning privileges to them along with other terminologies such as host and passwords. Here we go!

Syntax to Remove User by DROP USER Statement

To remove a single MySQL user account, you can use the DROP USER statement as below-

DROP USER userName;Code language: SQL (Structured Query Language) (sql)

You might want to remove multiple user accounts as well in one go. For that, you can use the following syntax-

DROP USER user1 [, userNames]......;Code language: SQL (Structured Query Language) (sql)

The square bracket indicates the parameters inside it are optional.

If you are trying to drop a user account that doesn’t exist in your system, the query will show an error. You can use IF EXISTS with the DROP USER statement to avoid such errors.

DROP USER IF EXISTS username;Code language: SQL (Structured Query Language) (sql)

The IF EXISTS clause will not show an error, but a warning will display.

Removing a User using DROP USER statement will also remove all the privileges of that user from all grant tables.

MySQL DROP USER Example

We will create three users before using a DROP USER statement to remove a user and use them to perform operations.

CREATE USER jenos@localhost, baki, mini IDENTIFIED BY '123';Code language: SQL (Structured Query Language) (sql)

Here, the user ‘jenos’ is created only for localhost and other users named ‘baki’ and ‘mini’ are created without any particular host.

You can view the user account names using the following command.

SELECT user,host FROM mysql.user;Code language: SQL (Structured Query Language) (sql)

Removing a MySQL User Using DROP USER Statement

Now we will drop the user “jenos” from our system.

DROP USER jenos@localhost;Code language: SQL (Structured Query Language) (sql)

Make sure you write the hostname with the username to avoid errors. If you don’t specify any particular host while creating a user, you can skip the hostname and only specify the user name.

Let’s check if the user is removed from our system.

SELECT user,host FROM mysql.user;Code language: SQL (Structured Query Language) (sql)

As you can see, the user with the name ‘jenos’ is removed from our system.

Using IF EXISTS Clause with DROP USER Statement

Let’s see what happens if we try to remove user ‘jenos’ again.

DROP USER jenos@localhost;Code language: SQL (Structured Query Language) (sql)

As you can see, we get an error when we try to remove a non-existing user account. Let’s execute the same query with the IF EXISTS clause.

DROP USER IF EXISTS jenos@localhost;Code language: SQL (Structured Query Language) (sql)

The query was executed successfully with no errors, but we got one warning.

Removing Multiple MySQL Users Using DROP USER Statement

We will now remove two user accounts, ‘mini’ and ‘baki’, from our database system using the following statement.

DROP USER baki,mini;Code language: SQL (Structured Query Language) (sql)

The users ‘baki’ and ‘mini’ were created without specifying any hostname, so we don’t need to specify hostname while removing them.

Let’s check the user table to see if the above user accounts are removed.

SELECT user,host FROM mysql.user;Code language: SQL (Structured Query Language) (sql)

Both user accounts are removed successfully from our database.

Conclusion

In this tutorial, we learned how to use the DROP USER statement to drop the MySQL user. This is a quick and straightforward tutorial to understand the use of the DROP USER statement. Along with the DROP USER, we also learned about the IF EXISTS clause. I hope you have found this article helpful. See you in the following tutorial!

References

MySQL official documentation on DROP USER statement.