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.
