Its always bugged me that I have to create two user accounts in MySQL, one with a wildcard host (%) and another for localhost.
Finally investigated it enough to understand why. Briefly, three things are going on.
- First, MySQL takes a non-wildcard host as precedence when looking for user accounts
- The mysql_install_db script create an anonymous user account ”@’localhost’
- The anonymous use account takes precedence when the hostname matches.
So the net effect is that when you connect locally, in general MySQL will use the anonymous account to authenticate to, no matter what you specify as a username, if the only account that matches the username also has a wildcard host field.
This is explained in the MySQL reference manual:
It is necessary to have both accounts for
montyto be able to connect from anywhere as
monty. Without the
localhostaccount, the anonymous-user account for
localhostthat is created by mysql_install_db would take precedence when
montyconnects from the local host. As a result,
montywould be treated as an anonymous user. The reason for this is that the anonymous-user account has a more specific
Hostcolumn value than the
'monty'@'%'account and thus comes earlier in the
usertable sort order.
I’m not quite sure why an anonymous user is created by default, some documentation suggests that on Windows, its used as the ‘root’ account.
Interestingly, although you need both user@localhost and user@%, you don’t also need to grant privileges to both users. It appears to be sufficient to grant privileges to user@% and those privileges will also be used for user@localhost (this was another source of confusion for me.)
If it bugs you so much, there are other options
- remove the anonynous account(s) entirely. I’ve seen references in a couple of places suggesting this is an okay thing to do
delete from mysql.user where user = ''
- rename the anonymous account(s). If deleting them seems a little too drastic
update mysql.user set user = 'visitor' where user = ''
I’m not sure which option I like, but now I finally understand MySQL authentication better.