In our earlier post, we discussed how to create a new user account in MySQL. Now in this post, we will discuss how to delete a MySQL user account using the MySQL shell or command line.
The general SQL syntax to delete a user from MySQL is
DROP USER account_name;where account_name is the name of the account in the format of ‘user_name'@'host_name‘.
If you need to delete multiple users in MySQL then use the following syntax:
DROP USER account_name [, account_name_2] ...To use DROP USER, you must have the global CREATE USER privilege or the DELETE privilege for the MySQL system database.
On deleting a user, their privileges for all grants also get deleted.
Example
Before we look into the examples, it is important to know the user name and host name for the MySQL user account which needs to be deleted.
You can use the following syntax in MySQL to list all users along with their hostnames.
SELECT user, host FROM mysql.user;
Let’s try deleting the ‘pencil_programmer’ user.
DROP USER 'pencil_programmer'@'localhost';Query OK, 0 rows affected (0.23 sec)
If we again look at the user table then we notice that ‘pencil_programmer’ has been deleted successfully.
The important point to note is that while executing the above statements we were connected to MySQL as ‘root’ user.
What could have happened if we were connected to MySQL as ‘pencil_programmer’ and tried to delete the same user?
In that case, the user will get deleted but at the same time continue to work as normal until the session expires (i.e next login).
That’s it for this tutorial. Today we have learned how to delete MySQL users using the DROP USER statement in MySQL.