Archive

Archive for November, 2011

MySQL user@localhost and user@%

November 2, 2011 Leave a comment

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.

  1. First, MySQL takes a non-wildcard host as precedence when looking for user accounts
  2. The mysql_install_db script create an anonymous user account ”@’localhost’
  3. 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 monty to be able to connect from anywhere as monty. Without the localhost account, the anonymous-user account for localhost that is created by mysql_install_db would take precedence when monty connects from the local host. As a result, monty would be treated as an anonymous user. The reason for this is that the anonymous-user account has a more specific Host column value than the 'monty'@'%' account and thus comes earlier in the user table 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.

Categories: Computing