Bug #23488 5.0.2x global DROP USER command fails without host restriction
Submitted: 20 Oct 2006 5:18 Modified: 30 Oct 2006 18:38
Reporter: Nate Angell Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Documentation Severity:S3 (Non-critical)
Version:5.0.2x OS:
Assigned to: Paul DuBois CPU Architecture:Any

[20 Oct 2006 5:18] Nate Angell
Description:
We tried the global DROP USER command on a number of mysql 5 servers of
differing versions (all greater than 5.0.2 where the command was
supposedly initiated as per the documentation on this page).

However, the command only succeeds if any host restriction for the user is included, which is not mentioned in the documentation.

This seems to contradict the manual but was very consistent across
multiple servers/versions > 5.0.2.

How to repeat:
If you have an entry in the db table with no host restriction (eg, for
$user@%) you can drop the user and all privileges with the DROP USER
command:

mysql> select user,host,db from db where db like 'database';
+----------+-----------+----------+
| user     | host      | db       |
+----------+-----------+----------+
| fooble7  | %         | database |
+----------+-----------+----------+

mysql> DROP USER fooble7;
Query OK, 0 rows affected (0.01 sec)

If the user is specified with a host restriction, you have to drop with
that restriction (ie, the global DROP USER command does not work without
the correct host):

+----------+-------------+----------+
| fooble7  | 123.123.%.% | database | 
+----------+-------------+----------+

mysql> drop user fooble7@'123.123.%.%';
Query OK, 0 rows affected (0.00 sec)

Suggested fix:
Either change documentation to reflect that host restrictions must be indicated in global DROP USER commands...
OR
modify mysql server so a global DROP USER command removes all grants and user regardless of host restrictions.
[20 Oct 2006 5:19] Nate Angell
recategorizing as documentation
[20 Oct 2006 6:30] Sveta Smirnova
Thank you for taking the time to write to us, but this is not a bug. Please double-check the documentation available at http://dev.mysql.com/doc/ and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php

This behaviour fully documented. See http://dev.mysql.com/doc/refman/5.0/en/privileges.html
[20 Oct 2006 16:15] Nate Angell
I agree it's not a bug, but it would be nice if the DROP USER documentation were more clear, ie: It says at 13.5.1.2. DROP USER Syntax:
http://dev.mysql.com/doc/refman/5.0/en/drop-user.html

<snip>
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.
</snip>

This language suggests that all privileges for a user would be removed with this syntax, regardless of host restrictions.

Or at least that's how I read it, so clearly it's confusing to at least one reader.
[20 Oct 2006 16:30] Sveta Smirnova
There are numerous place where described what MySQL uses username and hostname to identify person who connects to. For example on page link to I provided:

As a user, when you connect to a MySQL server, your identity is determined by the host from which you connect and the username you specify. When you issue requests after connecting, the system grants privileges according to your identity and what you want to do.

MySQL considers both your hostname and username in identifying you because there is little reason to assume that a given username belongs to the same person everywhere on the Internet. For example, the user joe who connects from office.example.com need not be the same person as the user joe who connects from home.example.com. MySQL handles this by allowing you to distinguish users on different hosts that happen to have the same name: You can grant one set of privileges for connections by joe from office.example.com, and a different set of privileges for connections by joe from home.example.com. 

(http://dev.mysql.com/doc/refman/5.0/en/privileges.html)
[20 Oct 2006 16:40] Nate Angell
I'm sure that if one had read and fully aborbed the complete mysql documentation on privileges, one might be aware that the sweeping statement on the 5.0 DROP USER documentation page is subject to constraints not mentioned therein.

However, four competent IT professionals at my University interpreted that sweeping statement broadly and then spent some time testing various mysql environments to see if our installation/practices were broken in some way as they were not behaving as per our interpretation of the documentation.

Why resist making documentation more user-friendly as per user observations? If mysql is asking for input from the mysql community via bugs/comments, this is what you get: real users with real observations and (hoepfully constructive) suggestions.

I'd be happy to suggest a very simple change to the documentation that would have clarified it for us...maybe it will save someone else some time down the line and help ensure the continuing adoption and development of mysql.

Remember, none of us are as expert as you and your colleages in mysql or in the generally excellent documentation.
[23 Oct 2006 7:28] Sveta Smirnova
Below is quote from DROP USER page (http://dev.mysql.com/doc/refman/5.0/en/drop-user.html):

"Each account is named using the same format as for the GRANT statement; for example, 'jeffrey'@'localhost'."

Is it not clear?
[30 Oct 2006 18:38] Paul DuBois
Thank you for your bug report. This issue has been addressed in the documentation. The updated documentation will appear on our website shortly, and will be included in the next release of the relevant products.

I have updated the manual to point out that a username with
no hostname is equivalent to specifying a hostname of '%'.