Bug #73258 Unable to analyze ALL queries with EXPLAIN on read-only account
Submitted: 10 Jul 2014 11:19 Modified: 10 Jul 2014 12:23
Reporter: Kamil Dziedzic Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Security: Privileges Severity:S3 (Non-critical)
Version:>= 5.6.3, 5.6.20 OS:Linux
Assigned to: CPU Architecture:Any
Tags: explain, privileges

[10 Jul 2014 11:19] Kamil Dziedzic
Description:
Even though EXPLAIN statement doesn't modify any data (unless I'm wrong?) it is required for user to have privileges like DELETE/INSERT/REPLACE/UPDATE in order to allow explains like EXPLAIN DELETE/INSERT/REPLACE/UPDATE.

In other words, right now it is impossible to create account only for query analysis. If one wants to give someone account to analyze queries, then one needs to create account with privileges that allows to modify data as this is the only way to be able to run all EXPLAIN statements.

How to repeat:
$ mysql -e "GRANT SELECT ON *.* TO 'query-analysis'@'localhost' IDENTIFIED BY '';"
# I should be able to do query analysis with EXPLAIN without granting UPDATE priv
$ mysql -uquery-analysis -e 'EXPLAIN UPDATE mysql.db SET Db="";'
ERROR 1142 (42000) at line 1: UPDATE command denied to user 'query-analysis'@'localhost' for table 'db'
$ mysql -e "GRANT UPDATE ON *.* TO 'query-analysis'@'localhost' IDENTIFIED BY '';"
$ mysql -uquery-analysis -e 'EXPLAIN UPDATE mysql.db SET Db="";'
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
|  1 | SIMPLE      | db    | ALL  | NULL          | NULL | NULL    | NULL |    2 | NULL  |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+

Suggested fix:
1. Allow to run EXPLAIN without checking privileges like DELETE/INSERT/REPLACE/UPDATE.

or

2. Create separate set of privileges to allow e.g. EXPLAIN DELETE without granting privilege to run DELETE statement.
[10 Jul 2014 12:23] Umesh Shastry
Hello Kamil,

Thank you for the bug report.
Verified as described.

Also, see http://bugs.mysql.com/bug.php?id=72858

Thanks,
Umesh