MySQL Bugs: #27374: Incorrect error message when SHOW VIEW is denied

Bug #27374	Incorrect error message when SHOW VIEW is denied
Submitted:	22 Mar 2007 13:49	Modified:	14 Jul 2007 15:23
Reporter:	Baron Schwartz (Basic Quality Contributor)	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	5.0.38, 5.0.26-log	OS:	Linux (Gentoo)
Assigned to:		CPU Architecture:	Any
Tags:	qc, show create view, show view, VIEW

Description:
When a non-privileged user runs SHOW CREATE TABLE or SHOW CREATE VIEW on a view, the error message reports that the SHOW VIEW command is denied, but there is no such command.  The error message conflates a command and a privilege.

How to repeat:
baron@kansascity:~$ mysql -uroot -ppassword

Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8 to server version: 5.0.24a-Debian_9ubuntu0.1-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> drop table test1;
Query OK, 0 rows affected (0.01 sec)

mysql> create table test(a int);
Query OK, 0 rows affected (0.01 sec)

mysql> create view vw_test as select * from test;
Query OK, 0 rows affected (0.01 sec)

mysql> grant select on vw_test to 'baron'@'%' identified by 'password';
Query OK, 0 rows affected (0.00 sec)

mysql> Bye
baron@kansascity:~$ mysql -ubaron -ppassword
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8 to server version: 5.0.24a-Debian_9ubuntu0.1-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> show create table vw_test\G
ERROR 1142 (42000): SHOW VIEW command denied to user 'baron'@'localhost' for table 'vw_test'
mysql> show create view vw_test\G
ERROR 1142 (42000): SHOW VIEW command denied to user 'baron'@'localhost' for table 'vw_test'

Suggested fix:
The error message should clarify the difference between the SHOW VIEW privilege
   http://dev.mysql.com/doc/refman/5.0/en/privileges-provided.html
and the SHOW CREATE VIEW command
   http://dev.mysql.com/doc/refman/5.0/en/show-create-view.html

Perhaps it could say

ERROR 1142 (42000): SHOW CREATE VIEW command denied to user 'baron'@'localhost' for table 'vw_test'

or

ERROR 1142 (42000): user 'baron'@'localhost' does not have SHOW VIEW privilege for table 'vw_test'

Thank you for a problem report, and sorry for a delay with its processing. Can you try to repeat with a newer version, 5.0.37/5.0.38?

On 5.0.42-BK I've got:

openxs@suse:~/dbs/5.0> bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.0.42 Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> drop table test;
Query OK, 0 rows affected (0.01 sec)

mysql> create table test(a int);
Query OK, 0 rows affected (0.01 sec)

mysql> create view vw_test as select * from test;
Query OK, 0 rows affected (0.00 sec)

mysql> grant select on vw_test to 'baron'@'localhost' identified by 'password';
Query OK, 0 rows affected (0.00 sec)

mysql> exit
Bye
openxs@suse:~/dbs/5.0> bin/mysql -ubaron -ppassword test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.0.42 Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> show create table vw_test\G
*************************** 1. row ***************************
       View: vw_test
Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY
DEFINER VIEW `vw_test` AS select `test`.`a` AS `a` from `test`
1 row in set (0.01 sec)

mysql> select current_user();
+-----------------+
| current_user()  |
+-----------------+
| baron@localhost |
+-----------------+
1 row in set (0.00 sec)

Please, check if my test is correct also.

I am still getting the same behavior on 5.0.38.  On this server 'nepal' a non-privileged 'baron' user account already exists, so I log in as root, create the view, log in again as baron, and am denied the SHOW VIEW privilege; but again it tells me I don't have the SHOW VIEW _command_, instead of saying I lack the SHOW VIEW _privilege_.

baron@keywest ~ $ mysql -uroot -p -hnepal
Enter password: 
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 13213 to server version: 5.0.38

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> use test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> create table test(a int);
Query OK, 0 rows affected (0.05 sec)

mysql> create view vw_test as select * from test;
Query OK, 0 rows affected (0.00 sec)

mysql> grant select on vw_test to 'baron'@'%';
Query OK, 0 rows affected (0.13 sec)

mysql> Bye
baron@keywest ~ $ mysql -ubaron -hnepal
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 13217 to server version: 5.0.38

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> use test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show create view vw_test\G
ERROR 1142 (42000): SHOW VIEW command denied to user 'baron'@'keywest.office' for table 'vw_test'
mysql> show grants;
+----------------------------------------------------------------------------------------------------------+
| Grants for baron@%                                                                                       |
+----------------------------------------------------------------------------------------------------------+
| GRANT SELECT, PROCESS, SUPER ON *.* TO 'baron'@'%' IDENTIFIED BY PASSWORD '[omitted]'             | 
| GRANT SELECT, INSERT, UPDATE, DELETE, INDEX, ALTER, CREATE TEMPORARY TABLES ON `test`.* TO 'baron'@'%' | 
| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, INDEX, ALTER ON `scratch`.* TO 'baron'@'%'           | 
| GRANT SELECT ON `test`.`vw_test` TO 'baron'@'%'                                                        | 
+----------------------------------------------------------------------------------------------------------+
4 rows in set (0.00 sec)

Sorry for a delay with this report. Can you, please, try to repeat with a newer version, 5.0.41/5.0.42. According to my test on 5.0.42-BK it should work properly or I missed something?

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".