Description:
Flush tables's privilege check does not distinguish between a global flush (no table names given) and a limited flush (table names given). It always checks for global RELOAD privileges, even if the scope of the actual operation is well contained to a single table.
There is no way to grant RELOAD privileges on a single database or table.
mysql> GRANT RELOAD ON dbname.* TO maintenance_user@localhost;
ERROR 1221 (HY000): Incorrect usage of DB GRANT and GLOBAL PRIVILEGES
mysql> GRANT RELOAD ON dbname.tablename TO maintenance_user@localhost;
ERROR 1144 (42000): Illegal GRANT/REVOKE command; please consult the manual to see which privileges can be used
It is rarely desireable to grant global RELOAD to anyone. Not when the operations they need to perform already contain the necessary semantics to check that their actions are well contained.
How to repeat:
$ mysql -u root -p
mysql> CREATE DATABASE testing;
Query OK, 1 row affected (0.06 sec)
mysql> GRANT ALL PRIVILEGES ON testing.* TO maintenance_user@localhost IDENTIFIED BY 'xxx';
Query OK, 0 rows affected (0.05 sec)
<hangup>
$ mysql -u maintenance_user -p
mysql> USE testing;
Database changed
mysql> CREATE TABLE flushme (`id` int unsigned NOT NULL AUTO_INCREMENT, PRIMARY KEY(`id`));
Query OK, 0 rows affected (1.35 sec)
mysql> FLUSH TABLES `testing`.`flushme`;
ERROR 1227 (42000): Access denied; you need (at least one of) the RELOAD privilege(s) for this operation
Suggested fix:
Simple, quick, somewhat hackish version:
When encountering a list of tables in FLUSH TABLES, do not check for global reload, but for localized privileges (on ALL of the specified tables) that are "just as bad". ALTER TABLE or INDEX come to mind.
Rationale: a table rebuild impacts global performance at least as much than a worst-case-scenario FLUSH TABLES <list> ever can. If a user is allowed to trigger such table rebuilds, allowing them a contained FLUSH TABLES operation does no longer pose any incremental danger to server performance.
Proper, more upsetting version:
Extend GRANT schema and logic to allow localized a)reload and b)a new localizeable flush tables privilege. Make "GRANT RELOAD ON dbpattern.tablepattern TO ..." and "GRANT FLUSH TABLES ON dbpattern.tablepattern TO ..." valid syntax, and make them the checked privileges for FLUSH TABLES <with an explicit list> queries.
