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.

3 thoughts on “Implementing a host blacklist with MySQL privileges

  1. Todd,

    This is very interesting! I’ll need to test this with some authentication plugin (or write my own “reject plugin”). If this turns out to work well – it’s a good enough solution for me!


  2. I found this post when searching for ideas on blacklisting in mysql in a similar way.

    I did some testing. If you’ve given access to explicit tables (i.e., not just all privs on *.*) to the less-specific user/host, then the more-specific user/host will see the less-specific user/host’s tables.

    user@192.168.10.% has select privs on *.*, table1, and table2.
    user@ has select privs on table2 only.

    When user logs in from, user can see table1 and table2.

    Maybe not a bug, given that blacklisting in mysql is out-of-documentation hacking, but not what I’d expect.

    You really have to disable access for that more-specific user/host to make this work, which, right, is what you said above.

    1. Thanks for sharing your experience and adding important nuance to user blacklist techniques, Valerie!

Leave a Reply

Your email address will not be published. Required fields are marked *