Managing MySQL Grants

MySQL has an unusual grants system that allows a user to be specified by host, ip or network address. That is you identify a user as ‘some_user’@'host.host.name’, ‘some_user’@’1.2.3.4′ or ‘some_user’@’10.3.%’.

That is quite a nice facility but using it is rather tricky. This potentially provides a lot more security as it allows you to specify that different types of database users can only perform certain actions from different types of hosts. So even if you know the user and password you may have trouble getting into a mysqld server. That’s good.

However, this flexibility comes at a price. There are no tools to help you manage this and I have often seen people resorting to using the simplest type of grant, for some_user@’%', or some_user@’10.%’.

I recently wrote a smalltemplate script which would allow me to configure similar grants for the same user but in different networks in a way which made this process more manageable. This is only a partial solution to the problem but does make it possible to configure in a reasonably simple fashion grants for a user with access coming from one of several locations.

An Example

An example might make this clearer.

A simple grant for a web user connection from webserver1 might be.

GRANT USAGE TO 'web'@'webserver1.example.com' IDENTIFIED BY 'some password';
GRANT SELECT ON web_db.* TO 'web'@'webserver1.example.com';

If we want to duplicate this for 3 web servers we need 2 more sets of grants:

GRANT USAGE TO 'web'@'webserver2.example.com' IDENTIFIED BY 'some password';
GRANT SELECT ON web_db.* TO 'web'@'webserver2.example.com';
GRANT USAGE TO 'web'@'webserver3.example.com' IDENTIFIED BY 'some password';
GRANT SELECT ON web_db.* TO 'web'@'webserver3.example.com';

If this is for a larger number of locations the grants become unmanageable (for the human to follow easily).

So my script allows me to do:

-- VARIABLE: %WEB_USER% = 'web'@'webserver1.example.com'
-- VARIABLE: %WEB_USER% = 'web'@'webserver2.example.com'
-- VARIABLE: %WEB_USER% = 'web'@'webserver3.example.com'
-- Grants for %WEB_USER%
GRANT USAGE TO %WEB_USER% IDENTIFIED BY 'some password';
GRANT SELECT ON web_db.* TO %WEB_USER%;

Adding more “web servers” just requires adding a single extra line.

With a simple case like this where there are only 2 grant lines for each user this is not so complicated, but if we had 20 or 30 grants for each user then this simplifies things a huge amount.

The script can be found here and generates the following output:

$ create_grants_from_template grants.template.sql
-- VARIABLE: %WEB_USER% = 'web'@'webserver1.example.com'
-- VARIABLE: %WEB_USER% = 'web'@'webserver2.example.com'
-- VARIABLE: %WEB_USER% = 'web'@'webserver3.example.com'
-- Grants for 'web'@'webserver1.example.com'
GRANT USAGE TO 'web'@'webserver1.example.com' IDENTIFIED BY 'some password';
GRANT SELECT ON web_db.* TO 'web'@'webserver1.example.com';
-- Grants for 'web'@'webserver2.example.com'
GRANT USAGE TO 'web'@'webserver2.example.com' IDENTIFIED BY 'some password';
GRANT SELECT ON web_db.* TO 'web'@'webserver2.example.com';
-- Grants for 'web'@'webserver3.example.com'
GRANT USAGE TO 'web'@'webserver3.example.com' IDENTIFIED BY 'some password';
GRANT SELECT ON web_db.* TO 'web'@'webserver3.example.com';
$

This output can be piped directly into the MySQL command line.

Further Improvements

While this might be helpful, it does not really solve the problem of managing grants, especially if you have a large number of database users and are managing quite a few database servers. I’ve been thinking about what I would like to do to solve this problem and while the ideas are not yet fully set think that something like the /etc/sudoers file is a good base to work from.

That is use a file which defines various things:

  • database users and their passwords
  • database servers and database server groups
  • individual grants and groups of grants

and combines this so that a script can create the specific rights for a specific database server, the results being different from one server to another.

Tags: , , ,

6 Responses to “Managing MySQL Grants”

  1. LenZ says:

    Have you looked at Darren Cassar’s Securich yet? It may already provide what you are looking for. He just released a new version at http://www.securich.com/

  2. Many MySQL users ho have run into the following error: “Client does not support authentication protocol requested by server; consider upgrading MySQL client”

    The remedy is to use MySQL’s OLD_PASSWORD command.

    Unfortunately, OLD_PASSWORD does not work with the GRANT statement. The following produces an error (I’m using 5.1.40 MySQL Community Server):

    mysql> GRANT SELECT,INSERT,UPDATE,DELETE ON guestbook.* TO ‘testuser’@'localhost’ IDENTIFIED BY OLD_PASSWORD(‘testpassword’);

    So I have to do this workaround:

    mysql> GRANT SELECT,INSERT,UPDATE,DELETE ON guestbook.* TO ‘testuser’@'localhost’;
    mysql> SET PASSWORD FOR ‘testuser’@'localhost’ = OLD_PASSWORD(‘testpassword’);

  3. sjmudd says:

    Hi Lenz,

    I wasn’t aware of securich, so thanks for the pointer.

  4. [...] All that aside, things keep rolling, and DBAs keep DBAing. Simon Mudd shared his thoughts and some suggestions on managing MySQL grants. [...]

  5. Simon Mudd says:

    I looked briefly at securich and it seems to be trying to solve a slightly different problem: better management of the grants on a single server. It looks like an interesting solution but I did not see anything that showed the actual grants that it generates, just the commands used to set the appropriate permissions.

    The problem I’m trying to solve is slightly different. I want to manage a set of similar grants on a large number of db servers, but managed in a central manner. I’m also not entirely keen on setting up a dedicated database on each specific server just to manage the grants. That looks too complex. I’d rather have a script which generates the appropriate grant commands in native MySQL format which I can apply from a script and perhaps another to check the expected grants from the actual ones so that mistakes can be corrected. That would be better for me.

  6. Hi Simon,

    Securich does much more than provide grant and revoke commands (if that is all you need you can still use securich) and you can obtain all commands from the audit tables.

    mysql Thu Jan 21 10:28:07 2010 > select * from aud_grant_revoke limit 10;
    +—-+———-+———–+——————————————————————————-+———————+
    | ID | USERNAME | HOSTNAME | COMMAND | TIMESTAMP |
    +—-+———-+———–+——————————————————————————-+———————+
    | 1 | root | localhost | revoke SELECT on world.country from “darren5″@”localhost” | 2010-01-01 21:47:15 |
    | 2 | root | localhost | grant SELECT on world.country to “michael”@”localhost” | 2010-01-01 21:48:49 |
    | 3 | root | localhost | grant execute on procedure securich.set_my_password to “michael”@”localhost”; | 2010-01-01 21:48:49 |
    | 4 | root | localhost | grant DELETE on world.country to “michael”@”localhost” | 2010-01-01 21:53:14 |
    | 5 | root | localhost | grant EXECUTE on world.* to “michael”@”localhost” | 2010-01-01 21:53:53 |
    | 6 | root | localhost | revoke SELECT on employees.* from “darren”@”localhost” | 2010-01-01 21:56:35 |

    etc. Commands are sorted in order given and you can distinguish by grantee if you like too.

    Regards,
    Darren

Leave a Reply