MySQL Drop User

Introduction

MySQL DROP User statement allows you to remove one or more users along with all their privileges from the database server. In this article, we will discuss how to accomplish it in detail. We will also see examples of single-user dropping, multiple user dropping, and dropping a connected user step by step.

                                                        

                                                                        Source: memgenerator

DROP USER 

Syntax: The syntax to delete a user along with all privileges from MySQL is given below:

DROP USER ‘account_name’;

where, account_name is the name of the user which you want to remove. Moreover,  ‘account_name’ is identified with the username@hostname syntax.

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

Removing multiple users: To remove multiple user accounts, specify a list of comma-separated user accounts. The syntax for that DROP clause is as follows: 

DROP USER ‘account_name’, ’account_name2’......

But the above clause will cause an error if the mentioned account names don't exist. To deal with that, we can use the 'IF EXISTS' clause, which got introduced in MySQL 5.7.8+.

DROP USER IF EXISTS ‘account_name’, ’account_name2’.....

This conditionally drops a user only if it exists.

Now, let's see some examples of dropping users.

Using MySQL DROP USER Statement To Drop A User 

Follow the steps to drop a user:

NOTE: The outputs are given for better understanding. They might not be exactly similar to the result your system produces.

Step1: Connect to MySQL server using the root account.

mysql -u root -p

 

Step2: Enter the password.

Enter password: ********

 

Step3: Execute the following command to see the current list of user accounts.

mysql> select user, host from mysql.user;

OUTPUT:

+-------------------------------+-----------+
| user                               | host      |
+-------------------------------+-----------+
| test                                | localhost |
| mysql.infoschema         | localhost |
| mysql.session               | localhost |
| mysql.sys                      | localhost |
| root                                | localhost |
+-------------------------------+-----------+
5 rows in set (0.00 sec)

 

Step4: Create some users using the command given below. Here, we are creating 4 new users.

mysql> create user user1@localhost, user2, ninja@localhost, shinzo@localhost identified by 'Secure1Pass!';

We created four users, namely 'user1', 'user2', 'ninja' and 'shinzo.' (To learn more about creating users, you can refer to MySQL create user). 

Step5: Again, execute the command to show the list of users.

mysql> select user, host from mysql.user;

OUTPUT:

+-------------------------------+-----------+
| user                               | host       |
+-------------------------------+-----------+
| user1                             | localhost |
| user2                             |     %       |
| ninja                              | localhost |
| shinzo                           | localhost |
| test                                | localhost |
| mysql.infoschema         | localhost |
| mysql.session               | localhost |
| mysql.sys                      | localhost |
| root                                | localhost |
+-------------------------------+-----------+
9 rows in set (0.00 sec)

We can see now that 4 created users increased in the user account list.

Step6: To drop a user run the following command.

mysql> drop user shinzo@localhost

User name ‘shinzo’ will be dropped.

Step7: Again, execute the command to show the list of users.

mysql> select user, host from mysql.user;

OUTPUT:

+-------------------------------+-----------+
| user                               | host       |
+-------------------------------+-----------+
| user1                             | localhost |
| user2                             |     %       |
| ninja                              | localhost |
| test                                | localhost |
| mysql.infoschema         | localhost |
| mysql.session               | localhost |
| mysql.sys                      | localhost |
| root                                | localhost |
+-------------------------------+-----------+
8 rows in set (0.00 sec)

The user account shinzo@localhost has been removed successfully.

Using MySQL DROP USER Statement To Drop Multiple User Accounts

We can also remove multiple user accounts at once. Let's continue our above example, and let's remove two users simultaneously, which we created before.

Step1: Run the following command.

mysql> drop user user1@localhost, user2;

Here user2’s hostname is not mentioned because at the time of the creation of user we haven’t specified it( as it is optional and user2 means user2@%, and ‘%’ means remote host can login to MySQL server from any other server). 

Therefore, ‘user1’ and ‘user2’ will be dropped simultaneously.

Step2: Show the results.

mysql> select user, host from mysql.user;

OUTPUT:

+-------------------------------+-----------+
| user                               | host       |
+-------------------------------+-----------+
| ninja                              | localhost |
| test                                | localhost |
| mysql.infoschema         | localhost |
| mysql.session               | localhost |
| mysql.sys                      | localhost |
| root                                | localhost |
+-------------------------------+-----------+
6 rows in set (0.00 sec)

The user accounts user1@localhost and user2 have been removed successfully.

Using MySQL DROP USER Statement To Drop A Connected User

Follow the steps given below to understand it better:

Step1: Create a new database called 'school.'

mysql> create database school;

Step2: Select the school database.

mysql> use school;

Step3: Create a new table ‘students’ in the ‘school’ database.

mysql> create table students(
    ->     id int auto_increment,
    ->     firstName varchar(100) not null,
    ->     lastName varchar(100) not null,
    ->     primary key(id));

Step4: Grant all privileges on the 'school' database to the user account 'david.'

mysql> grant all privileges on school.* to david@localhost;

Step 5: Launch another session and connect to the database using user david@localhost.

mysql -u david -p

Step6: Enter the password.

Enter password: ************

Step7: Select 'school' database.

mysql> use school;

Step8:  Insert a row into the students table.

mysql> insert into students(firstname, lastname) values('Alice','Bob');

Note: Now suppose you want to drop the user david@localhost. In this case, you have to inform the user first as it is currently connected to the MySQL server. If you drop a currently connected user without informing him/her, it will operate as normal until the next login. 

Another option is to kill the user session before dropping the user account.

Step 9: Use the SHOW PROCESSLIST command from the root session to find the id of the connection. 

SHOW PROCESSLIST

Step10: Terminate the corresponding Id process.

KILL Id

You will receive an error message if the user issues a query.

Step11: Finally, drop the user using the DROP USER command.

DROP USER david@localhost;

The user account david@localhost has been removed successfully.