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

[1 Feb 2012 21:21] James Kesser
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
[1 Feb 2012 23:37] MySQL Verification Team
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>
[2 Feb 2012 8:49] Jon Olav Hauglid
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.
[2 Feb 2012 14:03] James Kesser
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.
[2 Feb 2012 14:57] Jon Olav Hauglid
Yes, absolutely. We noticed this as well when investigating your bug report.
We will get this fixed. Thanks for bring it to our attention :-)