MySQL Bugs: #64198: Explain on view causes incorrect error 1345

Bug #64198	Explain on view causes incorrect error 1345
Submitted:	1 Feb 2012 21:21	Modified:	2 Feb 2012 14:03
Reporter:	James Kesser	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Security: Privileges	Severity:	S2 (Serious)
Version:	5.5.19	OS:	Linux (Fedora 16, Centos 5.7)
Assigned to:	Jon Olav Hauglid	CPU Architecture:	Any

Description:
After upgrading to 5.5.20 some of my users that have only global SELECT grants were getting an error message when running an explain on a view:

ERROR 1345 (HY000): EXPLAIN/SHOW can not be issued; lacking privileges for underlying table

As stated previously, the user has SELECT ON *.* and any other user with these grants gets the error. A user with ALL ON *.* does not get the error.

I have verified the error does NOT occur in 5.5.16, but it happens in 5.5.19 and 5.5.20.

How to repeat:
-- as a user with sufficient privs, create a simple table / view:
DROP TABLE IF EXISTS t;
CREATE TABLE t (a INT);

DROP VIEW IF EXISTS v;
CREATE VIEW v AS SELECT * FROM t;

-- as a user with SELECT ON *.*
mysql> SELECT * FROM t;
Empty set (0.00 sec)

mysql> EXPLAIN SELECT * FROM t;
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
|  1 | SIMPLE      | t     | ALL  | NULL          | NULL | NULL    | NULL |    1 |       |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
1 row in set (0.00 sec)

mysql> SELECT * FROM v;
Empty set (0.00 sec)

mysql> EXPLAIN SELECT * FROM v; 
ERROR 1345 (HY000): EXPLAIN/SHOW can not be issued; lacking privileges for underlying table

Thank you for the bug report.

h:\dbs>h:\dbs\5.5\bin\mysql -uroot --port=3540 --prompt="mysql 5.5 >"
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.5.21-log Source distribution

Copyright (c) 2000, 2011, 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 5.5 >create database d1;
Query OK, 1 row affected (0.02 sec)

mysql 5.5 >use d1
Database changed
mysql 5.5 >DROP TABLE IF EXISTS t;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql 5.5 >CREATE TABLE t (a INT);
Query OK, 0 rows affected (0.25 sec)

mysql 5.5 >
mysql 5.5 >DROP VIEW IF EXISTS v;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql 5.5 >CREATE VIEW v AS SELECT * FROM t;
Query OK, 0 rows affected (0.03 sec)

mysql 5.5 >grant select on d1.* to 'user1'@'localhost' identified by "";
Query OK, 0 rows affected (0.03 sec)

mysql 5.5 >exit
Bye

h:\dbs>5.5\bin\mysql -uuser1 -p --port=3540
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.5.21-log Source distribution

Copyright (c) 2000, 2011, 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> use d1
Database changed
mysql> EXPLAIN SELECT * FROM v;
ERROR 1345 (HY000): EXPLAIN/SHOW can not be issued; lacking privileges for underlying table

C:\mysql-5.5.16-winx64\bin>mysql -uroot
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.5.16 MySQL Community Server (GPL)

Copyright (c) 2000, 2011, 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> create database d1;
Query OK, 1 row affected (0.00 sec)

mysql> use d1
Database changed
mysql> DROP TABLE IF EXISTS t;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> CREATE TABLE t (a INT);
Query OK, 0 rows affected (0.06 sec)

mysql> DROP VIEW IF EXISTS v;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> CREATE VIEW v AS SELECT * FROM t;
Query OK, 0 rows affected (0.03 sec)

mysql> grant select on d1.* to 'user1'@'localhost' identified by "";
Query OK, 0 rows affected (0.00 sec)

mysql> exit
Bye

C:\mysql-5.5.16-winx64\bin>mysql -uuser1 -p d1
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.5.16 MySQL Community Server (GPL)

Copyright (c) 2000, 2011, 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 v;
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
|  1 | SIMPLE      | t     | ALL  | NULL          | NULL | NULL    | NULL |    1 |       |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
1 row in set (0.00 sec)

mysql>

Hello and thanks for the bug report!

This was an intended change of behavior.

Simply having the SELECT privilege on the view/tables should not be enough
to see the information returned by EXPLAIN SELECT - e.g. the view's
structure.

We therefore now also require the SHOW VIEW privilege on the view
in order to do EXPLAIN SELECT.

Before submitting this bug I looked in the changelog and documentation for anything related and I didn't see it. Perhaps this change should be noted somewhere.

Yes, absolutely. We noticed this as well when investigating your bug report.
We will get this fixed. Thanks for bring it to our attention :-)