Implementing a host blacklist with MySQL privileges

When I saw Shlomi’s recent post which asked (in part) for blacklist support in MySQL, I started thinking about ways in which this could be done using the tools we have today.  Here’s the example requirements Shlomi noted:

Speaking of whitelist, it would be great to have a host blacklist. If I wanted to grant access to ‘gromit’@’192.168.%’ except for ’192.168.10.%’ — well, I would have to whitelist all the possible subnets. I can’t exclude a set of hosts.

I think that’s entirely possible without the overhead of whitelisting all possible subnets – let’s give it a go!

This solution will rely on the fact that the first step in authentication in MySQL is finding the most applicable host for the incoming connection.  That’s caused all sorts of annoyances in the past with the anonymous user, where some unfortunate MySQL user creates a named account with a wildcard host, like ‘somebody’@'%’, and then proceeds to test locally, getting access denied because they are using a password that doesn’t match the ”@’localhost’ account that MySQL chose to use instead.  We can leverage that behavior to implement a blacklist.  First, we create the most generic user account:

CREATE USER 'gromit'@'192.168.%' IDENTIFIED BY 'password';

Now we can create a second user with 192.168.10.% as the host, and we’ll make sure they can’t log in.  You can use something like my system_user_auth plugin, here, if you like, but there are other ways to make logins impossible:

CREATE USER 'gromit'@'192.168.10.%' IDENTIFIED WITH system_user_auth';

Now you can log in from any host on the 192.168.% subnet, except those hosts on 192.168.10.%.  I admit it’s not the prettiest solution in the world, but it works with the MySQL tools we have today.