MySQL security tasks easily solved with common_schema

January 17, 2013

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' |
+--------------------------------+

tags: ,
posted in MySQL by shlomi

« | »

Follow comments via the RSS Feed | Leave a comment | Trackback URL

6 Comments to "MySQL security tasks easily solved with common_schema"

  1. Mrten wrote:

    Be careful with tables that have a _ in the name (mysql 5.1 here), they'll show up because the name does not match (it has a \ in front of the _).

    REVOKE [....] ON `it\_test`.* FROM [...]

  2. Mrten wrote:

    Be careful with tables that have a _ in the name (mysql 5.1 here), they'll show up because the name does not match (it has a \ in front of the _).

    REVOKE [....] ON `it\_test`.* FROM [...]

    Do a dry run!

  3. shlomi wrote:

    @Mrten, I'm sorry, I do not follow. See following: where's the problem with "_"?

    root@mysql-5.1.51> create database a_b;
    
    root@mysql-5.1.51> grant all privileges on a_b.* to 'shu'@localhost;
    
    root@mysql-5.1.51> select * from sql_grants where user='shu' \G
    *************************** 1. row ***************************
               GRANTEE: 'shu'@'localhost'
                  user: shu
                  host: localhost
            priv_level: *.*
       priv_level_name: user
         object_schema: NULL
           object_name: NULL
    current_privileges: USAGE
          IS_GRANTABLE: NO
             sql_grant: GRANT USAGE ON *.* TO 'shu'@'localhost' IDENTIFIED BY PASSWORD ''
            sql_revoke:
         sql_drop_user: DROP USER 'shu'@'localhost'
    *************************** 2. row ***************************
               GRANTEE: 'shu'@'localhost'
                  user: shu
                  host: localhost
            priv_level: `a_b`.*
       priv_level_name: schema
         object_schema: NULL
           object_name: a_b
    current_privileges: ALTER, ALTER ROUTINE, CREATE, CREATE ROUTINE, CREATE TEMPORARY TABLES, CREATE VIEW, DELETE, DROP, EVENT, EXECUTE, INDEX, INSERT, LOCK TABLES, REFERENCES, SELECT, SHOW VIEW, TRIGGER, UPDATE
          IS_GRANTABLE: NO
             sql_grant: GRANT ALTER, ALTER ROUTINE, CREATE, CREATE ROUTINE, CREATE TEMPORARY TABLES, CREATE VIEW, DELETE, DROP, EVENT, EXECUTE, INDEX, INSERT, LOCK TABLES, REFERENCES, SELECT, SHOW VIEW, TRIGGER, UPDATE ON `a_b`.* TO 'shu'@'localhost'
            sql_revoke: REVOKE ALTER, ALTER ROUTINE, CREATE, CREATE ROUTINE, CREATE TEMPORARY TABLES, CREATE VIEW, DELETE, DROP, EVENT, EXECUTE, INDEX, INSERT, LOCK TABLES, REFERENCES, SELECT, SHOW VIEW, TRIGGER, UPDATE ON `a_b`.* FROM 'shu'@'localhost'
         sql_drop_user: DROP USER 'shu'@'localhost'
    2 rows in set (0.01 sec)
    
    root@mysql-5.1.51> select * from information_schema.schemata;
    +--------------+--------------------+----------------------------+------------------------+----------+
    | CATALOG_NAME | SCHEMA_NAME        | DEFAULT_CHARACTER_SET_NAME | DEFAULT_COLLATION_NAME | SQL_PATH |
    +--------------+--------------------+----------------------------+------------------------+----------+
    | NULL         | a_b                | utf8                       | utf8_general_ci        | NULL     |
    ...
    +--------------+--------------------+----------------------------+------------------------+----------+
    16 rows in set (0.00 sec)
    
  4. Joe wrote:

    I am not sure if it is a problem, but what @Mrten is trying to say is this.

    root@mysql-5.1.51> create database a_b;

    root@mysql-5.1.51> create database acb;

    root@mysql-5.1.51> grant all privileges on a_b.* to 'shu'@localhost;

    Which schema would 'shu'@localhost have access to? Both.

    If you want the user to only have access to a_b you would have to do the following.

    root@mysql-5.1.51> grant all privileges on a\_b.* to 'shu'@localhost;

  5. shlomi wrote:

    @joe, @Mrten

    Thank you for pointing this out. I am shocked to find this in the documentation:

    "The “_” and “%” wildcards are permitted when specifying database names in GRANT statements that grant privileges at the global or database levels. This means, for example, that if you want to use a “_” character as part of a database name, you should specify it as “\_” in the GRANT statement, to prevent the user from being able to access additional databases matching the wildcard pattern; for example, GRANT ... ON `foo\_bar`.* TO "

    News to me.

    The current code The code proposed in this post does not use "\_", and in this respect @Mrten's warning is irrelevant. But since I will have to fix the code, the warning WILL become relevant in future code. Also, this means a search would have to take place using LIKE instead of "=".
    Will update in common_schema.

  6. common_schema: MySQL Security // RLASKEY::words wrote:

    [...] from Shlomi Noach. Very cool set of functions to audit your MySQL users and grants. It’s worth installing on your DB and running once or twice. It’s very easy to do, as you only need to download and import the SQL file, which creates its DB with various functions and views. [...]

Leave Your Comment

 
Powered by Wordpress and MySQL. Theme by openark.org