Description:
Manual (http://dev.mysql.com/doc/refman/5.6/en/explain.html) says literally nothing about privileges needed to run
EXPLAIN explainable_stmt
successfully, while in some cases you need more privileges for that than to run the statement itself. Look:
mysql> show grants;
+-------------------------------------------------------------------------------
----------------------------+
| Grants for uv@localhost
|
+-------------------------------------------------------------------------------
----------------------------+
| GRANT USAGE ON *.* TO 'uv'@'localhost' IDENTIFIED BY PASSWORD '*B436DA7205F6DA
66C21ED96ECF6ECF9EAD82AA5A' |
| GRANT SELECT ON `db1`.`t` TO 'uv'@'localhost'
|
| GRANT SELECT ON `db1`.`vt` TO 'uv'@'localhost'
|
+-------------------------------------------------------------------------------
----------------------------+
3 rows in set (0.00 sec)
mysql> select * from vt;
Empty set (0.00 sec)
mysql> desc vt;
+-------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| c1 | int(11) | YES | | NULL | |
+-------+---------+------+-----+---------+-------+
1 row in set (0.02 sec)
mysql> explain select * from vt;
ERROR 1345 (HY000): EXPLAIN/SHOW can not be issued; lacking privileges for under
lying table
So, if one guesses that vt is a view and grant SHOW VIEW on top:
C:\Program Files\MySQL\MySQL Server 5.6\bin>mysql -uroot -proot -P3314 test
Warning: Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 26
Server version: 5.6.19-log MySQL Community Server (GPL)
Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> grant show view on db1.vt to uv@localhost;
Query OK, 0 rows affected (0.00 sec)
mysql> exit
Bye
C:\Program Files\MySQL\MySQL Server 5.6\bin>mysql -uuv -puv -P3314 db1
Warning: Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 27
Server version: 5.6.19-log MySQL Community Server (GPL)
Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> explain select * from vt;
+----+-------------+-------+------+---------------+------+---------+------+-----
-+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows
| Extra |
+----+-------------+-------+------+---------------+------+---------+------+-----
-+-------+
| 1 | SIMPLE | t | ALL | NULL | NULL | NULL | NULL | 1
| NULL |
+----+-------------+-------+------+---------------+------+---------+------+-----
-+-------+
1 row in set (0.00 sec)
mysql> show grants;
+-------------------------------------------------------------------------------
----------------------------+
| Grants for uv@localhost
|
+-------------------------------------------------------------------------------
----------------------------+
| GRANT USAGE ON *.* TO 'uv'@'localhost' IDENTIFIED BY PASSWORD '*B436DA7205F6DA
66C21ED96ECF6ECF9EAD82AA5A' |
| GRANT SELECT ON `db1`.`t` TO 'uv'@'localhost'
|
| GRANT SELECT, SHOW VIEW ON `db1`.`vt` TO 'uv'@'localhost'
|
+-------------------------------------------------------------------------------
----------------------------+
3 rows in set (0.00 sec)
EXPLAIN finally works. This must be explicitly documented.
How to repeat:
See bug #73305 for more details about the test case.
Suggested fix:
Explicitly document ALL privileges that may be required for EXPLAIN in all cases.
