MySQL security tasks easily solved with common_schema

Here are three security tasks I handled, which I’m happy to say were easily solved with common_schema‘s views and routines (with no prior planning). Two are so easy, that I actually now integrated them into common_schema 1.3:

• Duplicate a user (create new user with same privileges as another’s)
• Find users with identical set of grants (same roles)
• Finding redundant users (users who only have privileges on non-existing objects); I was approached on this by Sheeri K. Cabral from Mozilla.

Duplicate user

How would you duplicate a grantee? That’s easy! Just get the SHOW GRANTS output, then do text search and replace: replace the existing account (e.g. ‘existing’@’localhost’) with the new account (e.g. ‘newcomer’@’localhost’).

Ahem. And how would you get the output of SHOW GRANTS? That’s right: you can’t do this from within the server. You have to go outside the server, incoke mysql client, sed your way into it, then connect to MySQL again to invoke the GRANT query… Or you can do this by hand, of course, or you can use the new mysqluserclone tool from MySQL utilities. Bottom line: you have to go outside the server. You can’t directly do this with your favorite GUI tool unless it has this function integrated.

But to have a truly automated, scriptable, server-side user-duplication you don’t need to go far, since the sql_show_grants view simulates a SHOW GRANTS output, but using plain old SQL. It produces the GRANT statement as SQL output. Which means you can REPLACE() the grantee. It’s actually a one liner, but is such a common operation that I created the duplicate_grantee() function for convenience. Just:

call duplicate_grantee('existing@localhost', 'newcomer@localhost');

(and yes, using a more relaxed form of grantee, allowing dropping of all these gory quotes).

Finding similar grantees

If the grants of an account are available via SQL, I’m also able to compare it with the grants of another account. I just need to ignore the account’s name in itself. Since sql_show_grants normalizes the grants in expected order, this works well. Again, this is a simple query, but since it is of importance, I created a view for it: just SELECT * FROM similar_grants to find out about accounts sharing same set of privileges..

Drop redundant users

Sheeri approached me with the following problem (she authorized me to publicize): as she came to work for Mozilla, she inherited the databases to manage. When she reviewed them, she realized there were numerous accounts which were redundant. Redundant how? Well, obviously not being used, but moreover not having privileges on existing objects.

She found out there were many accounts that only had privileges on non-existing schema, something like:

GRANT ALL PRIVILEGES ON penguin.* TO 'gromit'@'localhost'

Where the penguin schema does not exist anymore – it was some developer’s test database. The developer long since not working there, the database long since DROPped.

How do you quickly find and drop such accounts? Should we do crazy parsing on pt-show-grants output? Any other method?

Well, common_schema to the rescue: the sql_grants view breaks the GRANT statement into domains (in fact, this is not a true explanation; it actually builds the GRANT statements per domain; but it’s more convenient to view it as breaking). You actually get the different GRANT statement per account per domain: the GRANT the account has on *.*, the GRANT it has on world.*, the GRANT it has on sakila.*, the GRANT it has on ….

Now this one is not entirely simple. Let me introduce the solution, then explain it. The way to find & drop all such users is:

call eval("SELECT sql_revoke FROM sql_grants LEFT JOIN INFORMATION_SCHEMA.SCHEMATA ON(object_name = SCHEMA_NAME) WHERE priv_level_name='schema' AND DEFAULT_CHARACTER_SET_NAME IS NULL");
call eval("SELECT sql_drop_user FROM sql_grants GROUP BY GRANTEE HAVING COUNT(*)=1 AND SUM(current_privileges='USAGE')=1");

Explanation:

1. The LEFT JOIN query finds all GRANTs on non existing schemas.
2. But we do more than that: we request the sql_revoke column which negates such GRANTs. sql_grants provides such info: the REVOKE statement for said GRANTs.
3. We can actually evaluate the REVOKE statement via eval(). There! These privileges are now gone.
4. Now that we have revoked privileges on non-existing domains, we should be left with accounts whose only privilege is USAGE. Any such account is redundant by definition, and should be dropped. The second query looks up such accounts.
5. And, it asks for the sql_drop_user statement column for those accounts
6. Finally, it evaluates the DROP USER statement via eval().

Here is the statements output without executing the eval():

root@mysql-5.1.51> SELECT sql_revoke FROM sql_grants LEFT JOIN INFORMATION_SCHEMA.SCHEMATA ON(object_name = SCHEMA_NAME) WHERE  priv_level_name='schema'  AND DEFAULT_CHARACTER_SET_NAME IS NULL;
+-----------------------------------------------------------------+
| sql_revoke                                                      |
+-----------------------------------------------------------------+
| REVOKE INSERT, SELECT ON `not_here`.* FROM 'gromit'@'localhost' |
+-----------------------------------------------------------------+

root@mysql-5.1.51> SELECT sql_drop_user FROM sql_grants GROUP BY GRANTEE HAVING COUNT(*)=1 AND SUM(current_privileges='USAGE')=1;
+--------------------------------+
| sql_drop_user                  |
+--------------------------------+
| DROP USER 'gromit'@'localhost' |
+--------------------------------+