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.
1) Using MySQL DROP USER statement to drop a user example
First, connect to the MySQL Server using the root
account:
mysql -u root -p
Code 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
:
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:
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:
drop user dbadmin@localhost;
Code language: SQL (Structured Query Language) (sql)
Fifth, show all users again:
select
user,
host
from
mysql.user;
Code language: SQL (Structured Query Language) (sql)
Output:
+------------------+-----------+
| 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@localhost
has been removed successfully.
2) Using the DROP USER statement to remove multiple user accounts
First, remove two user accounts api@localhost
and remote
using the following statement:
drop user api@localhost, remote;
Code language: SQL (Structured Query Language) (sql)
Second, show all the users:
select user, host from mysql.user;
Code language: SQL (Structured Query Language) (sql)
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)
3) Using the DROP USER statement to remove a connected user
First, create a new database called people
:
create database people;
Code language: SQL (Structured Query Language) (sql)
Second, select the people
database:
use people;
Code language: SQL (Structured Query Language) (sql)
Third, create a new table persons
in the people
database:
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
:
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 -p
Code 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:
use people;
Code language: SQL (Structured Query Language) (sql)
Seventh, insert a row into the persons
table:
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:
show processlist;
Output:
+----+-----------------+-----------------+--------+---------+-------+------------------------+------------------+
| 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)
The output indicates that 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 alice
will receive an error message if he/she issues any query:
ERROR 2013 (HY000): Lost connection to MySQL server during query
Code 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)
Summary
- Use the MySQL
DROP USER
statement to remove one or more user accounts.