MySQL Drop User

The MySQL Drop User statement allows us to remove one or more user accounts and their privileges from the database server. If the account does not exist in the database server, it gives an error.

If you want to use the Drop User statement, it is required to have a global privilege of Create User statement or the DELETE privilege for the MySQL system schema.

Syntax

The following syntax is used to delete the user accounts from the database server completely.

DROP USER 'account_name';  

The account_name can be identified with the following syntax:

username@hostname  

Here, the username is the name of the account, which you want to delete from the database server and the hostname is the server name of the user account.

MySQL Drop USER Example

The following are the step required to delete an existing user from the MySQL server database.

Step 1: Open the MySQL server by using the mysql client tool.

Step 2: Enter the password for the account and press Enter.

Enter Password: ********  

Step 3: Execute the following command to show all users in the current MySQL server.

mysql> select user from mysql.user;  

We will get the output as below:

Step 4: To drop a user account, you need to execute the following statement.

DROP USER martin@localhost; 

Here, we are going to remove the username ‘martin‘ from the MySQL server. After the successful execution of the above command, you need to execute the show user statement again. You will get the following output where username martin is not present.

Step 5: The DROP USER statement can also be used to remove more than one user accounts at once. We can drop multiple user accounts by separating account_name with comma operator. To delete multiple user accounts, execute the following command.

DROP USER john@localhost, peter@localhost;  

Here, we are going to remove john and peter accounts from the above image. After the successful execution of the above command, you need to execute the show user statement again. You will get the following output where username john and peter is not present.

NOTE: This statement cannot close any open user sessions automatically. In the case when the DROP USER statement executed and the session of this account is active, this statement does not take effect until its session is closed. The user account is dropped only when the session is closed, and that the user’s next attempt will not be able to log in again.