How To Remove User Accounts Using MySQL DROP USER Statement
Summary: in this tutorial, you will learn how to use the MySQL DROP USER statement to remove one or more user accounts from the database.
Introduction to MySQL DROP USER statement
To remove a user account from the MySQL Server, you use the DROP USER statement as follows:
DROP USER account_name;
Code language: SQL (Structured Query Language) (sql)
In this syntax, you specify the name of the user account that you want to remove after the DROP USER keywords.
If you want to remove multiple user accounts at once, you specify a list of comma-separated user accounts in the DROP USER clause:
DROP USER account_name [,account_name2]...Code language: SQL (Structured Query Language) (sql)
If you remove a user account that doesn’t exist, MySQL will issue an error.
In MySQL 5.7.8+, you can use the IF EXISTS clause to conditionally drop a user only if it exists:
DROP USER [IF EXISTS] account_name [,account_name2]...;
Code language: SQL (Structured Query Language) (sql)
Besides removing the user account, the DROP USER statement also removes all privileges of the user from all grant tables.
MySQL DROP USER examples
Let’s take some examples of dropping users.
A) Using MySQL DROP USER statement to drop a user example
First, connect to the MySQL Server using the root account:
mysql -u root -pCode language: SQL (Structured Query Language) (sql)
Type the password for the root user and press Enter:
Enter password: ********Code language: SQL (Structured Query Language) (sql)
Second, create four account users accounts api@localhost, remote, dbadmin@localhost and alice@localhost:
mysql> create user api@localhost, remote, dbadmin@localhost, alice@localhost identified by 'Secure1Pass!';Code language: SQL (Structured Query Language) (sql)
Third, show users from the MySQL Server:
mysql> select user, host from mysql.user;Code language: SQL (Structured Query Language) (sql)
Here is the current user list:
+------------------+-----------+
| user | host |
+------------------+-----------+
| remote | % |
| alice | localhost |
| api | localhost |
| bob | localhost |
| dbadmin | localhost |
| mysql.infoschema | localhost |
| mysql.session | localhost |
| mysql.sys | localhost |
| root | localhost |
+------------------+-----------+
9 rows in set (0.00 sec)Code language: SQL (Structured Query Language) (sql)
Fourth, drop the user dbadmin@localhost by using the DROP USER statement:
mysql> drop user dbadmin@localhostCode language: SQL (Structured Query Language) (sql)
Fifth, show all users again:
mysql> select user, host from mysql.user;
Code language: SQL (Structured Query Language) (sql)
+------------------+-----------+
| user | host |
+------------------+-----------+
| remote | % |
| alice | localhost |
| api | localhost |
| bob | localhost |
| mysql.infoschema | localhost |
| mysql.session | localhost |
| mysql.sys | localhost |
| root | localhost |
+------------------+-----------+
8 rows in set (0.00 sec)Code language: SQL (Structured Query Language) (sql)
The user account dbadmin@localhosthas been removed successfully.
B) Using MySQL DROP USER to drop multiple user accounts at once
First, remove two user accounts api@localhost and remote using the following statement:
mysql> drop user api@localhost, remote;
Code language: SQL (Structured Query Language) (sql)
Second, show users from the current database:
mysql> select user, host from mysql.user;
Code language: SQL (Structured Query Language) (sql)
Here is the output:
+------------------+-----------+
| user | host |
+------------------+-----------+
| alice | localhost |
| bob | localhost |
| mysql.infoschema | localhost |
| mysql.session | localhost |
| mysql.sys | localhost |
| root | localhost |
+------------------+-----------+
6 rows in set (0.00 sec)Code language: SQL (Structured Query Language) (sql)
C) Using MySQL DROP USER to drop a connected user
First, create a new database called people:
mysql> create database people;Code language: SQL (Structured Query Language) (sql)
Second, select the people database:
mysql> use people;Code language: SQL (Structured Query Language) (sql)
Third, create a new table persons in the people database:
mysql> create table persons(
-> id int auto_increment,
-> firstName varchar(100) not null,
-> lastName varchar(100) not null,
-> primary key(id));Code language: SQL (Structured Query Language) (sql)
Fourth, grant all privileges on the people database to the account user alice:
mysql> grant all privileges on people.* to alice@localhost;Code language: SQL (Structured Query Language) (sql)
Fifth, launch another session and connect to the database using the user alice@localhost:
mysql -u alice -pCode language: SQL (Structured Query Language) (sql)
Type the password for the user account alice and press the Enter key:
Enter password: ************Code language: SQL (Structured Query Language) (sql)
Sixth, select the people database:
mysql> use people;Code language: SQL (Structured Query Language) (sql)
Seventh, insert a row into the persons table:
mysql> insert into persons(firstname, lastname) values('John','Doe');Code language: SQL (Structured Query Language) (sql)
Suppose that you want to drop the user alice@localhost.
However, the user account alice@localhost is still connected to MySQL Server. If you drop a currently connected user, the user can operate as normal until the next login. In this case, you should inform the user first.
If you cannot do so, you can kill user sessions first before dropping the user account.
Eighth, use the SHOW PROCESSLIST statement from the root’s session to find the id of the connection:
+----+-----------------+-----------------+--------+---------+-------+------------------------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+-----------------+-----------------+--------+---------+-------+------------------------+------------------+
| 4 | event_scheduler | localhost | NULL | Daemon | 31803 | Waiting on empty queue | NULL |
| 20 | root | localhost:63851 | NULL | Query | 0 | starting | show processlist |
| 21 | alice | localhost:64060 | people | Sleep | 14 | | NULL |
+----+-----------------+-----------------+--------+---------+-------+------------------------+------------------+Code language: PHP (php)
As you see, the user account alice@localhost has the connection id id 21.
Ninth, terminate process 21 by using the KILL statement:
KILL 21;
Code language: SQL (Structured Query Language) (sql)
The user account alicereceived an error message if he/she issue any query:
ERROR 2013 (HY000): Lost connection to MySQL server during queryCode language: SQL (Structured Query Language) (sql)
Finally, execute the DROP USER statement to remove the user account alice@localhost.
DROP USER alice@localhost;
Code language: SQL (Structured Query Language) (sql)
In this tutorial, you have learned how to use the MySQL DROP USER statement to remove one or more user accounts.