MySQL DROP USER Statement with Examples


How to Drop User Account in MySQL

In this tutorial, you'll learn how to remove one or more MySQL user accounts and their privileges using the DROP USER statement.

DROP USER Syntax

The syntax of the DROP USER statement is as follows:

DROP USER user [, user] ...

Remarks

  • To remove a user account, specify the account name in the user_name@host_name.
  • User accounts you want to remove must exist; otherwise, MySQL issues an error.
  • To remove user accounts, you must have the global CREATE privilege or the DELETE privilege in the MySQL system database.
  • You can remove more than one user accounts for a single DROP USER statement.

Examples

Example 1: Drop a Single User

In this example, first, let's create a new user account named db_user1 with the password pass12345. To do this, you use the following statement:

CREATE USER db_user1 IDENTIFIED BY "pass12345";

Next, you can use the following statement to select all users in MySQL:

SELECT user, host
FROM mysql.user;

Now, if the user account db_user1 created successfully, you can see it in the user accounts list.

To remove this user account from MySQL, we 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: Drop Multiple Users

In this example, we're going to remove two user accounts in a single DROP USER statement.

Removing multiple user accounts in MySQL is easy as you can see in the following statement:

DROP USER db_user1, db_user2;

Here, this statement removes db_user1 and db_user2 if they exist. Note that MySQL removes only the existing user accounts and issues an error for the non-existing user accounts we're trying to remove.

For example, let's say both user accounts do not exist, then you'll see a message as shown below:

[Err] 1396 - Operation DROP USER failed for 'db_user1'@'%','db_user2'@'%'

This tutorial described how to use the MySQL DROP USER statement to remove one or more user accounts in MySQL. You must have the global CREATE privilege, or the DELETE privilege in the MySQL system database if you want to remove user accounts from the MySQL database server.

Leave a Reply

Your email address will not be published. Required fields are marked *