MySQL Bugs: #7014: Views: EXPLAIN allowed without proper privileges

Bug #7014	Views: EXPLAIN allowed without proper privileges
Submitted:	5 Dec 2004 0:23	Modified:	6 May 2005 13:39
Reporter:	Trudy Pelzer	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	5.0.3-alpha-debug	OS:	Linux (SuSE 9.1)
Assigned to:	Oleksandr Byelkin	CPU Architecture:	Any

Description:
Our agreement with SAP (and WL#1806/941) requires the 
following behaviour: 
 
1) If a user has the SHOW VIEW privilege on a view, the user 
can execute EXPLAIN and SHOW statements on that view  
even if he/she has no privileges on the underlying table(s). 
Otherwise, some privilege on every underlying table is 
required. 
 
That is, to execute either EXPLAIN or SHOW on a view, one 
must have either the SHOW VIEW privilege on that view 
or any privilege on each of the underlying tables. 
 
2) If the user does not have the correct privileges, an 
attempt to execute EXPLAIN or SHOW on a view must 
fail with SQLSTATE HY000 "EXPLAIN/SHOW can not be issued; 
lacking privileges for underlying table". 
 
But I can execute some EXPLAIN/SHOW commands 
on a view without the proper privileges. 

How to repeat:
mysql> select current_user(); 
+----------------+ 
| current_user() | 
+----------------+ 
| root@localhost | 
+----------------+ 
 
mysql> create table t1 (col1 int); 
mysql> create view v1 as select * from t1; 
 
mysql> create user trudy; 
mysql> grant select on v1 to trudy; 
 
-- switch users 
 
mysql> select current_user; 
+--------------+ 
| current_user | 
+--------------+ 
| trudy@%      | 
+--------------+ 
 
mysql> show grants for trudy; 
+--------------------------------------------------+ 
| Grants for trudy@%                               | 
+--------------------------------------------------+ 
| GRANT USAGE ON *.* TO 'trudy'@'%'                | 
| GRANT SELECT ON `tp`.`v1` TO 'trudy'@'%'         | 
+--------------------------------------------------+ 
 
mysql> explain v1; 
+-------+---------+------+-----+---------+-------+ 
| Field | Type    | Null | Key | Default | Extra | 
+-------+---------+------+-----+---------+-------+ 
| col1  | int(11) | YES  |     | NULL    |       | 
+-------+---------+------+-----+---------+-------+ 
-- This is the incorrect response. The EXPLAIN should 
fail with SQLSTATE HY000: EXPLAIN/SHOW can not be issued;  
lacking privileges for underlying table 
 
mysql> explain v1 col1; 
+-------+---------+------+-----+---------+-------+ 
| Field | Type    | Null | Key | Default | Extra | 
+-------+---------+------+-----+---------+-------+ 
| col1  | int(11) | YES  |     | NULL    |       | 
+-------+---------+------+-----+---------+-------+ 
-- This is the incorrect response. The EXPLAIN should 
fail with SQLSTATE HY000: EXPLAIN/SHOW can not be issued;  
lacking privileges for underlying table 
 
mysql> explain select * from v1; 
ERROR 1345 (HY000): EXPLAIN/SHOW can not be issued; lacking  
privileges for underlying table 
-- This is the correct response. 
 
mysql> show create view v1; 
ERROR 1142 (42000): show create view command denied to user  
'trudy'@'localhost' for table 'v1' 
-- The rsponse is correct, but the error message returned 
is not. The return should be as above: SQLSTATE HY000:  
EXPLAIN/SHOW can not be issued; lacking privileges for  
underlying table

Verified with latest 5.0 BK tree

But I didn't get any error message for the last two statements:

mysql> explain select * from v1;
+----+-------------+-------+--------+---------------+------+---------+------+------+---------------------+
| id | select_type | table | type   | possible_keys | key  | key_len | ref  | rows | Extra               |
+----+-------------+-------+--------+---------------+------+---------+------+------+---------------------+
|  1 | PRIMARY     | t1    | system | NULL          | NULL | NULL    | NULL |    0 | const row not found |
+----+-------------+-------+--------+---------------+------+---------+------+------+---------------------+
1 row in set (0.00 sec)

mysql> show create view v1;
+------+-----------------------------------------------------------------------------------------------------+
| View | Create View                                                                                         |
+------+-----------------------------------------------------------------------------------------------------+
| v1   | CREATE ALGORITHM=UNDEFINED VIEW `test`.`v1` AS select `test`.`t1`.`col1` AS `col1` from `test`.`t1` |
+------+-----------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)

Victoria is unable to reproduce the results showing the
correct error message responses becuase she is working
with the test database and thus has all privileges.

The EXPLAIN v1 and EXPLAIN v1 col1 statements do
not need to fail, since they do not show any information
on the table9s) that underlie the view. 

Thus there is no bug; this was merely a communication
failure of the actual expectation.s