mysql> drop user ‘test_s99’@’%’;
ERROR 1268 (HY000): Can’t drop one or more of the requested users
revoke all on test_s99.* from ‘test_s99’@’localhost’;
drop user ‘test_s99’@’localhost’;
The DROP USER statement removes one or more MySQL accounts. To use it, you must have the DELETE privilege for the mysql database. Each account is named using the same format as for the GRANT statement; for example, ‘jeffrey’@’localhost’. If you specify only the user name part of the account name, a host name part of ‘%’ is used. For additional information about specifying account names, see Section 12.5.1.2, “GRANT Syntax”.
DROP USER was added in MySQL 4.1.1. In MySQL 4.1, it 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 the steps in the order shown:
1.
Use SHOW GRANTS to determine what privileges the account has. See Section 12.5.5.12, “SHOW GRANTS Syntax”.
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 12.5.1.2, “GRANT Syntax”.
3.
Delete the account by using DROP USER to remove the user table row.
In MySQL 5.0.2 and up, DROP USER removes the account row in the user table and also revokes the privileges held by the account. It is not necessary to use DROP USER in conjunction with REVOKE.
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.
Before MySQL 4.1.1, DROP USER is not available. You should first revoke the account privileges using SHOW GRANTS and REVOKE as just described. Then delete the user table row and flush the grant tables as shown here: