MySQL Bugs: #31061: Host wildcard % which is said in docs that means "all hosts" excludes localhost

Bug #31061	Host wildcard % which is said in docs that means "all hosts" excludes localhost
Submitted:	17 Sep 2007 15:41	Modified:	26 Apr 2012 19:25
Reporter:	Marc Mirandews	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Security: Privileges	Severity:	S3 (Non-critical)
Version:	5.1.21	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
If I create a user with privileges 

'user'@'%' identified by 'pass'

and I try to do "mysql -uuser -ppass" from localhost 
 
it gives me an error of access permission
That doesn't happen when I use it from remote machines, so O have to create a new account for 'user'@'localhost'  

How to repeat:
do exactly what it's explained up

Suggested fix:
asume % as any localhost or remote hosts or create a new wildcard that means both things...

'%' means local host as well. Most likely you have also account for anonmous user ''@'localhost' and MySQL pick up this entry from privilege table.
Make sure you don't have account for anonmous user.
For more info see MySQL manual.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".

I thought this was a bug also, but deleting the @localhost and the @hostname users fixed it for me.

Thanks

Even after deleting the entries with user='' and host='' I can't connect with a username="someuser" and host="%" entry. Only if i add an explicit @'localhost' grant will the grant work.

This is a very annoying bug

Wolfgang,

thank you for the feedback. This looks like wrong usage for me as well. Please provide output of SELECT user, host FROM mysql.user; connection parameters you use and exact error you get.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".

I probably got the reasons(or implement details) from my guess:

mysql matches the account name with records in mysql.user by sequence:

mysql> SELECT User, Host, Password FROM mysql.user;
+-----------+--------------+-------------------------------------------+
| User      | Host         | Password                                  |
+-----------+--------------+-------------------------------------------+
| root      | localhost    |                                           |
|           | localhost    |                                           |
| test      | %            |                                           |
+-----------+--------------+-------------------------------------------+

root can connect to localhost because it's the 1st record;
test cannnot connect localhost becuase it's behand the ''@'localhost' -- that's the anonymous account.

Resolve:
drop anonymous account or assign password to it according to instruction on http://dev.mysql.com/doc/mysql-security-excerpt/5.0/en/default-privileges.html.
(i choose to drop it)
drop user ''@'localhost';
flush privileges;

Thank you for the feedback.

Closed as "Not a Bug" because last comment.

Am I the only one who thinks this is a bug? Shouldn't the anonymous permissions apply only when an anonymous user logs in? It doesn't make any sense that it gets applied. This is not good design.

I agree with Tom Wardrop on this being a bug. I think something should be done about it.

I beg to differ that the mere effect by these anonymous user accounts on other new users'  logging in from host '*' is sufficiently a bug. Granted the anonymous users are created for convenience during the installation and set up, they are on the same token of minimal value if not pointless because there is already a root user with no password. At least MySQL should not eliminate their effect on other valid users. I spent hours yesterday trying to understand why a new user I created could not log in. I bet there were "a few" others like me who had no idea this flawed design had been known for years. Till I spotted Bug#31061.

My above comment was based on MySQL Community Server 5.5.46.