13.7.1.2 DROP USER Syntax

DROP USER user [, user] ...

The DROP USER statement removes one or more MySQL accounts. An error occurs for accounts that do not exist. To use this statement, you must have the global CREATE USER privilege or the DELETE privilege for the mysql database.

Each account name uses the format described in Section 6.2.3, “Specifying Account Names”. For example:

DROP USER 'jeffrey'@'localhost';

If you specify only the user name part of the account name, a host name part of '%' is used.

DROP USER as present in MySQL 5.0.0 removes only accounts that have no privileges. In MySQL 5.0.2, it was modified to remove account privileges as well. This means that the procedure for removing an account depends on your version of MySQL.

As of MySQL 5.0.2, you can remove an account and its privileges as follows:

DROP USER user;

The statement removes privilege rows for the account from all grant tables.

Before MySQL 5.0.2, DROP USER serves only to remove account rows from the user table for accounts that have no privileges. To remove a MySQL account completely (including all of its privileges), you should use the following procedure, performing these steps in the order shown:

  1. Use SHOW GRANTS to determine what privileges the account has. See Section 13.7.5.17, “SHOW GRANTS Syntax”.

    However, an account with no password is insecure. To avoid this, use SET PASSWORD to set the account password.

  2. Use REVOKE to revoke the privileges displayed by SHOW GRANTS. This removes rows for the account from all the grant tables except the user table, and revokes any global privileges listed in the user table. See Section 13.7.1.3, “GRANT Syntax”.

  3. Delete the account by using DROP USER to remove the user table row.

Important

DROP USER does not automatically close any open user sessions. Rather, in the event that a user with an open session is dropped, the statement does not take effect until that user's session is closed. Once the session is closed, the user is dropped, and that user's next attempt to log in will fail. This is by design.

DROP USER does not automatically drop or invalidate databases or objects within them that the old user created. This includes stored programs or views for which the DEFINER attribute names the dropped user. Attempts to access such objects may produce an error if they execute in definer security context. (For information about security context, see Section 18.5, “Access Control for Stored Programs and Views”.)