DROP USER

If a user has no privileges at all—meaning, after you've revoke all of a user's privileges with the REVOKE statement—you can use this statement to then remove a user from the MySQL completely.

Syntax

Below is the syntax for this command. Text within square brackets (i.e, [ and ] ) are optional. Choices are separated by bars (i.e, | ). Ellipses preceded by a comma indicates a repeating pattern. Ellipses before or after syntax are used to highlight a relevant except from the larger syntax. Text displayed in italic letters represent text that should be replaced with the specific names related to the database (e.g., column with the name of the column).

DROP USER 'user'@'host'

Explanation

Use this statement to delete a user account for the MySQL server. As of version 5.0.2 of MySQL, this statement will delete the user account and its privileges from all grant tables. The user name is given within quotes, followed by the at-sign (@) and the host IP address or host name within quotes. This statement requires CREATE USER privilege or DELETE privilege for the mysql database, which contains user account information and privileges. Dropping a user account does not affect current sessions for the user account. It will take effect when any sessions opened by the user terminate. Use the KILL statement to terminate an open client session for a user that has been dropped.

Examples

Some users may have more than one user account (i.e., user and host combinations). You should check the server's mysql.user table to be sure:

SELECT User,Host 
FROM mysql.user 
WHERE User LIKE 'paola';

+-------+--------------+
| User  | Host         |
+-------+--------------+
| paola | localhost    | 
| paola | caporale.com | 
+-------+--------------+

DROP USER 'paola'@'localhost',
'paola'@'caporale.com';

Prior to version 5.0.2 of MySQL, the DROP USER statement won't delete a user that has any privileges set to 'Y'. To be eliminate the user account's privileges, issue the REVOKE statement before using DROP USER:

REVOKE ALL ON *.* FROM 'paola'@'localhost';

DROP USER 'paola'@'localhost';

The ALL option is used to ensure revocation of all privileges. The *.* covers all tables in all databases. Prior to version 4.1.1 of MySQL, you would have to issue the following instead of a DROP USER statement:

DELETE FROM mysql.user
WHERE User='paola' AND Host='localhost';

FLUSH PRIVILEGES;

Notice that the FLUSH PRIVILEGES statement is necessary so that the preceding DELETE statement takes effect immediately. It's not necessary after the DROP USER statement, though.