Today’s Gotcha: Localhost is not included in the ‘%’ wildcard when granting MySQL permissions

Thoughts from the team
By    | January 13, 2012 | Bug of the Day,

MySQL administration is one of those things I have to do pretty infrequently, so when I do there’s always something that trips me up and wastes a bit of time.

This week it was granting permissions on a database.

To grant permissions on a database to a user you use something like…

GRANT ALL ON db1.* TO 'user'@'hostname';

Hostname can also be ‘%’, which is a wildcard for any host.

My gotcha was that ‘%’ does not include ‘localhost’, so the locally hosted application couldn’t access the database that I could connect to remotely.

It’s not immediately obvious in the documentation that this is the case.  It says that ‘%’ ” applies to user_name for any host in the 192.168.1 class C subnet”, which of course in hind-sight does not include ‘localhost’.  While the local machine is in that class-C in most networks, the connection will come via the loopback address, not through an external network, so it’s origin host will not be in the ‘%’ set.

It can seem slightly counter intuitive but it makes sense when you understand a little more about what’s happening.

More like this