If you haven’t read the other installments in our MySQL blog series, we recommend you check them out:
Since the previous blog posts have already walked you through creating a MySQL user, you’re now prepared for the next step: deleting a MySQL user. Deleting a MySQL user is very simple. Unlike the process of creating a user, where you have to add permissions, deleting a user only requires you to enter the command and the username. Deleting a user from your MySQL database will remove the user and all of the user’s privileges. The user will be unable to log back into the database or make changes.
How to Delete a MySQL User
When deleting a MySQL user, you must have the global create user or delete privilege for the database. If you do not have this privilege, you will not be able to delete another user. If this is the case, you can either switch to another user that has the privileges (if you have more than one user designated to you) or ask your admin for privileges. For more information about privileges, see our How to Create a MySQL User blog
The Drop User Statement
You can remove a user by using the DROP USER command. This command removes a user by deleting their information from the grant tables within the database. A user’s information needs to be stored in the grant tables so that the database can grant the user access. Without any information in the grant table, users will not be able to gain access. he DROP USER statement looks something like this:
DROP USER IF EXISTS user
Replace user with the username that you want to delete. The IF EXISTS portion of the command keeps MySQL from throwing an error if the user doesn’t exist.
You can also run the command as:
DROP USER user
However, if the user does not exist, you will receive an error.
Specifying User Hostname
The user can also be specified by hostname, if you want to limit where the user can connect from. This would look something like this:
DROP USER IF EXISTS ‘user’@’localhost’;
If you don’t specify a hostname and just list the user, all users with that username would be deleted, regardless of hostname. For example, if you executed the following, all “joe” usernames would be deleted:
DROP USER joe;
When just a user is listed, the command will default to using a wildcard in the place of a hostname, thus signalling that all hostnames should be affected by the command:
DROP USER ‘joe’@’%’
Removing More than One User at a Time
You can also remove more than one user at a time. To do this, simply list the users at the end of the DROP USER statement and separate them with commas:
DROP USER IF EXISTS user, user;
You can specify hostnames or only list the username so that all instances of the username are deleted.
Things to Keep in Mind
Deleting a user does not delete the databases or objects the user created or worked on. Also, if the user is currently using an instance of MySQL while you are in the process of deleting the user, the instance will not close until the user closes it. After the instance is closed, the user will be deleted from the grant tables.The changes the user makes in that last instance would still be valid. However, the next time they attempt to log in, they will be unable to.
Deleting a User: In Review
By learning how to control your MySQL database users, you learn how to better manage your databases. Better management helps you get the most out of your databases, and consequently, your site. To boost your MySQL knowledge even more, stay tuned for more installments in the MySQL Basics blog series. You can also check out our posts on How to Create a MySQL User and How to Create a MySQL Database for more information about managing your MySQL database.
