MySQL security: inconsistencies

Doing some work with MySQL security, I’ve noticed a few inconsistencies. They’re mostly not-too-terrible for daily work, except they get in my way right now.

The ALL PRIVILEGES inconsistency

The preferred way of assigning account privileges in MySQL is by way of using GRANT.

With GRANT, one assigns one or more privileges to an account, such as SELECT, UPDATE, ALTER, SUPER ,etc. Sometimes it makes sense for an account to have complete control over a domain. For example, the root account is typically assigned with all privileges. Or, some user may require all possible privileges on a certain schema.

Instead of listing the entire set of privileges, the ALL PRIVILEGES meta-privilege can be used. There is a fine issue to notice here; typically this is not a problem, but I see it as a flaw. Assume the following account:

root@mysql-5.1.51> GRANT ALL PRIVILEGES ON world.* TO 'world_user'@'localhost';

root@mysql-5.1.51> SHOW GRANTS FOR 'world_user'@'localhost';
+---------------------------------------------------------------+
| Grants for world_user@localhost                               |
+---------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'world_user'@'localhost'                |
| GRANT ALL PRIVILEGES ON `world`.* TO 'world_user'@'localhost' |
+---------------------------------------------------------------

This makes sense. We granted ALL PRIVILEGES and we see that the account is granted with ALL PRIVILEGES.

Now notice the following:

root@mysql-5.1.51> 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 `world`.* TO 'other_user'@'localhost';

root@mysql-5.1.51> SHOW GRANTS FOR 'other_user'@'localhost';
+---------------------------------------------------------------+
| Grants for other_user@localhost                               |
+---------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'other_user'@'localhost'                |
| GRANT ALL PRIVILEGES ON `world`.* TO 'other_user'@'localhost' |
+---------------------------------------------------------------+

I didn’t ask for ALL PRIVILEGES. I explicitly listed what I thought should be an account’s privileges. It just so happens that these make for the entire set of privileges available on the schema domain.

You might think this is a nice feature, an ease out MySQL provides with. I do not see it this way.

My preferred way of upgrading MySQL version involves exporting and importing of the GRANTs. That is, I do not dump and load the mysql system tables, but rather export all the SHOW GRANTS FOR … (e.g. with mk-show-grants), then execute these on the new version. This process was extremely useful on upgrades from 5.0 to 5.1, where some mysql system tables were modified.

Now, consider the case where some new MySQL version introduced a new set of privileges. My ‘other_user’@’localhost’ was not created with that set of privileges, nor did I intend it to have them. However, when exporting with SHOW GRANTS, the account is said to have ALL PRIVILEGES. When executed on the new version, the account will have privileges which I never assigned it.

Typically, this is not an issue. I mean, how many times do I assign an account with the entire set of privileges, yet do not intend it to have all privileges? Nevertheless, this makes for an inconsistency. It is unclear, by way of definition, which privileges are assigned to a user, without knowing the context of the version and the set of privileges per version. It makes for an inconsistency when moving between versions. And right now I’m working on some code which doesn’t like these inconsistencies.

The WITH GRANT OPTION inconsistency

An account can be granted with the WITH GRANT OPTION privilege, which means the account’s user can assign her privileges to other accounts. The inconsistency I found is that the GRANT mechanism is fuzzy with regard to GRANT OPTION, and falsely presents us with the wrong impression.

Let’s begin with the bottom line: the WITH GRANT OPTION can only be set globally for an account-domain combination. Consider:

root@mysql-5.1.51> GRANT INSERT, DELETE, UPDATE ON world.City TO 'gromit'@'localhost';
Query OK, 0 rows affected (0.00 sec)

root@mysql-5.1.51> GRANT SELECT ON world.City TO 'gromit'@'localhost' WITH GRANT OPTION;
Query OK, 0 rows affected (0.00 sec)

root@mysql-5.1.51> SHOW GRANTS FOR 'gromit'@'localhost';
+--------------------------------------------------------------------------------------------------+
| Grants for gromit@localhost                                                                      |
+--------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'gromit'@'localhost'                                                       |
| GRANT SELECT, INSERT, UPDATE, DELETE ON `world`.`City` TO 'gromit'@'localhost' WITH GRANT OPTION |
+--------------------------------------------------------------------------------------------------+

The syntax of first two queries leads us to believe that we’re only providing the WITH GRANT OPTION for the SELECT privilege. But that is not so: the WITH GRANT OPTION is assigned for all privileges on world.City to ‘gromit’@’localhost’.

The syntax would be more correct if we were to write something like:

GRANT GRANT_OPTION ON world.* TO 'gromit'@'localhost';

That would make it clear that this privilege does not depend on other privileges set on the specified domain.

The USAGE inconsistency

You can GRANT the USAGE privilege, but you may never REVOKE it. To revoke USAGE means to DROP USER.

The missing ROUTINES_PRIVILEGES inconsistency

INFORMATION_SCHEMA provides with four privileges tables: USER_PRIVILEGES, SCHEMA_PRIVILEGES, TABLE_PRIVILEGES, COLUMN_PRIVILEGES, which map well to mysql‘s user, db, tables_priv and columns_priv tables, respectively.

Ahem, which INFORMATION_SCHEMA table maps to mysql.procs_priv?

4 thoughts on “MySQL security: inconsistencies

  1. @Shlomi ..

    Most of this is known and have been discussed various places before (in Blogs, MySQL Forums and bugs.mysql). But ther is one particular good catch the I did not notice before myself and that is:

    root@mysql-5.1.51> GRANT INSERT, DELETE, UPDATE ON world.City TO ‘gromit’@’localhost’;
    root@mysql-5.1.51> GRANT SELECT ON world.City TO ‘gromit’@’localhost’ WITH GRANT OPTION;

    .. what results in
    GRANT SELECT, INSERT, UPDATE, DELETE ON `world`.`City` TO ‘gromit’@’localhost’ WITH GRANT OPTION

    .. so “GRANT OPTION” cannot be narrowed to a subset of user’s privileges on a database object. It has effect for all or none privileges. If it is supposed to be like this then the commandline syntax is confusing. GUI tools (those I know) are actually less confusing in this respect than commandline.

  2. @Daniël,

    oops, will do.

    @Peter,
    You can also see it in the INFORMATION_SCHEMA.TABLE_PRIVILEGES table:
    there is a IS_GRANTABLE column, which makes the table to be in 2nd normal form.

    It *looks* as if eash privilige can be assigned with IS_GRANTABLE of its own, but this is not the case.

Leave a Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.