Description:
Manual for SHOW VIEW privilege (http://dev.mysql.com/doc/refman/5.6/en/privileges-provided.html#priv_show-view) says:
"The SHOW VIEW privilege enables use of SHOW CREATE VIEW."
and that's all. In reality this privilege is needed to be able to run EXPLAIN for statements involving the view.
How to repeat:
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 24
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> create database db1;
Query OK, 1 row affected (0.02 sec)
mysql> use db1;
Database changed
mysql> create table t(c1 int);
Query OK, 0 rows affected (1.48 sec)
mysql> create view vt as select * from t;
Query OK, 0 rows affected (0.07 sec)
mysql> create user uv@localhost identified by 'uv';
Query OK, 0 rows affected (0.00 sec)
mysql> grant select on db1.t to uv@localhost;
Query OK, 0 rows affected (0.00 sec)
mysql> grant select 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 25
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> select current_user();
+----------------+
| current_user() |
+----------------+
| uv@localhost |
+----------------+
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 ON `db1`.`vt` TO 'uv'@'localhost'
|
+-------------------------------------------------------------------------------
----------------------------+
3 rows in set (0.00 sec)
mysql> select * from vt;
Empty set (0.00 sec)
mysql> show create view vt\G
ERROR 1142 (42000): SHOW VIEW command denied to user 'uv'@'localhost' for table
'vt'
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 t;
+----+-------------+-------+------+---------------+------+---------+------+-----
-+-------+
| 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> explain select * from vt;
ERROR 1345 (HY000): EXPLAIN/SHOW can not be issued; lacking privileges for under
lying table
So, I can SELECT from the view, but I can not run EXPLAIN for the same SELECT successfully (NOT only I can't run SHOW CREATE VIEW for the view...). This is NOT explicitly documented.
mysql> explain vt;
+-------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| c1 | int(11) | YES | | NULL | |
+-------+---------+------+-----+---------+-------+
1 row in set (0.02 sec)
mysql> exit
Bye
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)
Suggested fix:
Explain that SHOW VIEW privilege is/may be needed to run EXPLAIN against query referring to the view in the manual.
