Drop User Using the DROP USER Statement in MySQL

This article describes how to drop one or more user accounts from a MySQL server using the DROP USER statement.

This article describes how to drop one or more user accounts from a MySQL server using the DROP USER statement.

In some specific cases, you may want to drop some existing users, such as:

  • This user account is no longer in use.
  • This user account has been leaked.

To drop a user account from the MySQL server, use the DROP USER statement.

MySQL DROP USER Syntax

The following is the basic syntax of the DROP USER statement :

DROP USER [IF EXISTS] account_name [,account_name2]...

In this syntax, you should specify the name of the user account you want to drop after the DROP USER keyword.

If you want to drop multiple user accounts at once, specify a comma-separated list of user accounts in the DROP USER statement.

MySQL will issue an error if a user account that does not exist. You can use the IF EXISTS clause to conditionally drop a user only if the user exists. However, MySQL 5.7.8+ versions support the IF EXISTS clause.

The DROP USER statement not only removes the user account, but also removes all the user’s privileges from all grant tables.

Note: If the dropped user has logged in a session before the deletion, the deletion will not affect the session until the session terminates.

MySQL DROP USER Examples

Let’s look at an example of dropping users.

  1. Connect to the MySQL server using the root account :

    mysql -u root -p

    Enter the root user ’s password and press Enter:

    Enter password: ********

  2. Show all users from the current MySQL server:

    SELECT user, host FROM mysql.user;

    Here is the current user list:

    +------------------+-----------+
| user             | host      |
+------------------+-----------+
| root             | %         |
| sqliz            | %         |
| test_role1       | %         |
| test_role2       | %         |
| testuser         | %         |
| mysql.infoschema | localhost |
| mysql.session    | localhost |
| mysql.sys        | localhost |
| root             | localhost |
| sqliz            | localhost |
+------------------+-----------+

  3. Drop the user sqliz@localhost using the following DROP USER statement:

    DROP USER sqliz@localhost;

  4. Show all users again:

    SELECT user, host FROM mysql.user;

    +------------------+-----------+
| user             | host      |
+------------------+-----------+
| root             | %         |
| sqliz            | %         |
| test_role1       | %         |
| test_role2       | %         |
| testuser         | %         |
| mysql.infoschema | localhost |
| mysql.session    | localhost |
| mysql.sys        | localhost |
| root             | localhost |
+------------------+-----------+

    The user account sqliz@localhost was dropped.

Kill user’s session

If the dropped user has already logged in a session before the deletion, the deletion will not affect the session until the session ends. It may be dangerous.

You can use the following SHOW PROCESSLIST statement to list all sessions, and use the KILL command to terminate a dropped user’s session, as following:

SHOW PROCESSLIST

Here is the sessions list for the current server:

+----+-----------------+---------------------+---------+---------+---------+------------------------+------------------+
| Id | User            | Host                | db      | Command | Time    | State                  | Info             |
+----+-----------------+---------------------+---------+---------+---------+------------------------+------------------+
|  5 | event_scheduler | localhost           | NULL    | Daemon  | 4061212 | Waiting on empty queue | NULL             |
| 24 | root            | 192.168.0.204:54684 | NULL    | Query   |       0 | starting               | SHOW PROCESSLIST |
| 26 | sqliz           | localhost:54688     | sqlizdb | Sleep   |    1965 |                        | NULL             |
+----+-----------------+---------------------+---------+---------+---------+------------------------+------------------+

As you can see, the ID of the user account session for sqliz@localhost is 26.

You can terminate the session 26 with the following KILL statement:

KILL 26;

Conclusion

In this article, you learned how to use MySQL DROP USER statement to drop one or more user accounts from a MySQL database server.