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:

[email protected]> GRANT ALL PRIVILEGES ON world.* TO 'world_user'@'localhost';

[email protected]> 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:

[email protected]> 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';

[email protected]> 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:

[email protected]> GRANT INSERT, DELETE, UPDATE ON world.City TO 'gromit'@'localhost';
Query OK, 0 rows affected (0.00 sec)

[email protected]> GRANT SELECT ON world.City TO 'gromit'@'localhost' WITH GRANT OPTION;
Query OK, 0 rows affected (0.00 sec)

[email protected]> 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?