{"id":5434,"date":"2017-01-05T23:29:25","date_gmt":"2017-01-06T06:29:25","guid":{"rendered":"http:\/\/www.mysqltutorial.org\/?page_id=5434"},"modified":"2023-11-13T19:55:58","modified_gmt":"2023-11-14T02:55:58","slug":"mysql-drop-user","status":"publish","type":"page","link":"https:\/\/www.mysqltutorial.org\/mysql-administration\/mysql-drop-user\/","title":{"rendered":"MySQL DROP USER"},"content":{"rendered":"\n

Summary<\/strong>: in this tutorial, you will learn how to use the MySQL DROP USER<\/code> statement to remove one or more user accounts from the database.<\/p>\n\n\n\n

Introduction to MySQL DROP USER statement<\/h2>\n\n\n\n

To remove a user account from the MySQL Server, you use the DROP USER<\/code> statement as follows:<\/p>\n\n\n

DROP<\/span> USER<\/span> account_name;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n
\n
\"MySQL<\/figure>\n<\/div>\n\n\n
In this syntax, you specify the name of the user account that you want to remove after the DROP USER<\/code> keywords.<\/p>\n\n\n\n
If you want to remove multiple user accounts at once, you specify a list of comma-separated user accounts in the DROP USER<\/code> clause:<\/p>\n\n\n
DROP<\/span> USER<\/span> account_name [,account_name2]...<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
If you remove a user account that doesn’t exist, MySQL will issue an error.<\/p>\n\n\n\n
In MySQL 5.7.8+, you can use the IF EXISTS<\/code> clause to conditionally drop a user only if it exists:<\/p>\n\n\n
DROP<\/span> USER<\/span> [IF<\/span> EXISTS<\/span>] account_name [,account_name2]...;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Besides removing the user account, the DROP USER<\/code> statement also removes all privileges of the user from all grant tables.<\/p>\n\n\n\n
MySQL DROP USER examples<\/h2>\n\n\n\n
Let’s take some examples of dropping users.<\/p>\n\n\n\n
1) Using MySQL DROP USER statement to drop a user example<\/h3>\n\n\n\n
First, connect to the MySQL Server using the root<\/code> account:<\/p>\n\n\n
mysql -u root -p<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Type the password for the root<\/code> user and press Enter<\/code>:<\/p>\n\n\n
Enter password: ********<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Second, create four account users  accounts api@localhost<\/code>, remote<\/code>, dbadmin@localhost<\/code> and alice@localhost<\/code>:<\/p>\n\n\n
create<\/span> user<\/span> api@localhost, \nremote, \ndbadmin@localhost, \nalice@localhost identified<\/span> by<\/span> 'Secure1Pass!'<\/span>;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Third, show users from the MySQL Server:<\/p>\n\n\n
select<\/span> \n  user<\/span>, \n  host \nfrom<\/span> \n  mysql.user;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Here is the current user list:<\/p>\n\n\n
+------------------+-----------+<\/span>\n| user             | host      |\n+------------------+-----------+<\/span>\n| remote           | %         |\n| alice            | localhost |\n| api              | localhost |\n| bob              | localhost |\n| dbadmin          | localhost |\n| mysql.infoschema | localhost |\n| mysql.session    | localhost |\n| mysql.sys        | localhost |\n| root             | localhost |\n+------------------+-----------+<\/span>\n9 rows in set<\/span> (0.00<\/span> sec)<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Fourth, drop the user dbadmin@localhost<\/code> by using the DROP USER<\/code> statement:<\/p>\n\n\n
drop<\/span> user<\/span> dbadmin@localhost;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Fifth, show all users again:<\/p>\n\n\n
select<\/span> \n  user<\/span>, \n  host \nfrom<\/span> \n  mysql.user;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Output:<\/p>\n\n\n
+------------------+-----------+<\/span>\n| user             | host      |\n+------------------+-----------+<\/span>\n| remote           | %         |\n| alice            | localhost |\n| api              | localhost |\n| bob              | localhost |\n| mysql.infoschema | localhost |\n| mysql.session    | localhost |\n| mysql.sys        | localhost |\n| root             | localhost |\n+------------------+-----------+<\/span>\n8 rows in set<\/span> (0.00<\/span> sec)<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
The user account dbadmin@localhost<\/code> has been removed successfully.<\/p>\n\n\n\n
2) Using the DROP USER statement to remove multiple user accounts<\/h3>\n\n\n\n
First, remove two user accounts api@localhost<\/code> and remote<\/code> using the following statement:<\/p>\n\n\n
drop<\/span> user<\/span> api@localhost, remote;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Second, show all the users:<\/p>\n\n\n
select<\/span> user<\/span>, host from<\/span> mysql.user;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Output:<\/p>\n\n\n
+------------------+-----------+<\/span>\n| user             | host      |\n+------------------+-----------+<\/span>\n| alice            | localhost |\n| bob              | localhost |\n| mysql.infoschema | localhost |\n| mysql.session    | localhost |\n| mysql.sys        | localhost |\n| root             | localhost |\n+------------------+-----------+<\/span>\n6 rows in set<\/span> (0.00<\/span> sec)<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
3) Using the DROP USER statement to remove a connected user<\/h3>\n\n\n\n
First, create a new database<\/a> called people<\/code>:<\/p>\n\n\n
create<\/span> database<\/span> people;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Second, select the people<\/code> database:<\/p>\n\n\n
use<\/span> people;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
create<\/span> table<\/span> persons(\n  id<\/span> int<\/span> auto_increment, \n  firstName varchar<\/span>(100<\/span>) not<\/span> null<\/span>, \n  lastName varchar<\/span>(100<\/span>) not<\/span> null<\/span>, \n  primary key<\/span>(id<\/span>)\n);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Fourth, grant all privileges on the people<\/code> database to the account user alice<\/code>:<\/p>\n\n\n
grant<\/span> all<\/span> privileges<\/span> on<\/span> people.* to<\/span> alice@localhost;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Fifth, launch another session and connect to the database using the user alice@localhost<\/code>:<\/p>\n\n\n
mysql -u alice -p<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Type the password for the user account alice<\/code> and press the Enter<\/code> key:<\/p>\n\n\n
Enter password: ************<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Sixth, select the people<\/code> database:<\/p>\n\n\n
use<\/span> people;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Seventh, insert a row into the persons<\/code> table:<\/p>\n\n\n
insert<\/span> into<\/span> persons(firstname, lastname) \nvalues<\/span> \n  ('John'<\/span>, 'Doe'<\/span>);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Suppose that you want to drop the user alice@localhost<\/code>.<\/p>\n\n\n\n
However, the user account alice@localhost<\/code> 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.<\/p>\n\n\n\n
If you cannot do so, you can kill user sessions first before dropping the user account.<\/p>\n\n\n\n
show processlist;<\/code><\/span><\/pre>\n\n\n
Output:<\/p>\n\n\n
+----+-----------------+-----------------+--------+---------+-------+------------------------+------------------+\n| Id | User            | Host            | db     | Command | Time  | State                  | Info             |\n+----+-----------------+-----------------+--------+---------+-------+------------------------+------------------+\n|  4<\/span> | event_scheduler | localhost       | NULL<\/span>   | Daemon  | 31803<\/span> | Waiting on empty<\/span> queue | NULL<\/span>             |\n| 20<\/span> | root            | localhost:63851<\/span> | NULL<\/span>   | Query   |     0<\/span> | starting               | show processlist |\n| 21<\/span> | alice           | localhost:64060<\/span> | people | Sleep   |    14<\/span> |                        | NULL<\/span>             |\n+----+-----------------+-----------------+--------+---------+-------+------------------------+------------------+<\/code><\/span>Code language:<\/span> PHP<\/span> (<\/span>php<\/span>)<\/span><\/small><\/pre>\n\n\n
The output indicates that the user account alice@localhost<\/code> has the connection id id 21<\/code>.<\/p>\n\n\n\n
Ninth, terminate process 21 by using the KILL<\/code> statement:<\/p>\n\n\n
KILL<\/span> 21<\/span>;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
The user account alice<\/code> will receive an error message if he\/she issues any query:<\/p>\n\n\n
ERROR 2013 (HY000): Lost connection to MySQL server during query<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Finally, execute the DROP USER<\/code> statement to remove the user account alice@localhost<\/code>.<\/p>\n\n\n
drop<\/span> user<\/span> alice@localhost;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Summary<\/h2>\n\n\n\n
    \n
  • Use the MySQL DROP USER<\/code> statement to remove one or more user accounts.<\/li>\n<\/ul>\n
    \n\t
    \n\t\t
    Was this tutorial helpful? <\/div>\n\t\t
    \n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t Yes <\/span>\n\t\t\t<\/button>\n\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t No <\/span>\n\t\t\t<\/button>\n\t\t<\/div>\n\t<\/header>\n\n\t
    \n\t\t
    \n\t\t\t
    <\/div>\n\t\t\t\n\t\t\t
Eighth, use the SHOW PROCESSLIST<\/a><\/code> statement from the root’s session to find the id of the connection:<\/p>\n\n\n
Third, create a new table<\/a> persons<\/code> in the people<\/code> database:<\/p>\n\n\n