How to remove user accounts in MySQL with Examples
This tutorial explains how to use the DROP USER statement to delete user accounts and their privileges in MySQL.
Learn more: How to create users in MySQL
DROP USER Syntax
The syntax of the DROP USER statement is as follows:
DROP USER user [, user] ...
Remarks
- To delete a user account, specify the account name in user_name@host_name
- The user account to be deleted must exist, or MySQL will issue an error if it does not exist
- To delete a user account, you must have global CREATE or DELETE privileges in the MySQL system database
- You can delete multiple user accounts with a single DROP USER statement
Examples
Example 1: Drop a single user
In this example, we will first create a new user account named db_user1 with the password pass12345. To do this, we will use the following statement:
CREATE USER db_user1 IDENTIFIED BY "pass12345";
Next, use the following statement to select all users of MySQL:
SELECT user, host
FROM mysql.user;
Now, if the user account db_user1 is created successfully, you can see it in the list of user accounts.
To remove this user account from MySQL, use the following statement:
DROP USER db_user1;
Now, select all user accounts and then check again. If nothing wrong, this user account has been removed from the MySQL database server already.
Example 2: Delete multiple users
This example shows you how to delete two user accounts with a single DROP USER statement.
Deleting multiple user accounts in MySQL is easy, as you can see in the following statement:
DROP USER db_user1, db_user2;
Here, db_user1 and db_user2, if they exist, are deleted with this statement. Note that MySQL only deletes existing user accounts and will issue an error for any non-existent user account that you are trying to delete.
For example, if both user accounts do not exist, you will see a message like the following:
[Err] 1396 - Operation DROP USER failed for 'db_user1'@'%','db_user2'@'%'
Summary
This tutorial described how to delete one or more user accounts in MySQL using the MySQL DROP USER statement. To delete a user account from the MySQL database server, you must have the global CREATE or DELETE permission. To add users in MySQL, use CREATE USER statement.