Bug #9506 Views: SHOW VIEW privilege works oddly

• Submitted: 31 Mar 2005 1:00
• Modified: 28 Jun 2005 9:43
• Reporter: Peter Gulutzan
• Status: Can't repeat
• Category: MySQL Server
• Severity: S3 (Non-critical)
• Version: 5.0.4-beta-debug
• OS: Linux (SUSE 9.2)
• Assigned to: Oleksandr Byelkin
• CPU Architecture: Any

[31 Mar 2005 1:00] Peter Gulutzan

Description:
SHOW CREATE VIEW should be possible for any user who has (1) SHOW VIEW privilege, and (2) SELECT privilege on the view,  or (3) SELECT privilege on all tables (or columns) underlying the view. But even if a user meets all these requirements, MySQL disallows SHOW CREATE VIEW.

How to repeat:
As user root, say:

mysql> create table t4 (s1 int);
Query OK, 0 rows affected (0.00 sec)

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

mysql> grant show view on *.* to user22@localhost;
Query OK, 0 rows affected (0.00 sec)

mysql> grant select on v4 to user22@localhost;
Query OK, 0 rows affected (0.00 sec)

mysql> grant select on t4 to user22@localhost;
Query OK, 0 rows affected (0.00 sec)

As user user22@localhost, say:

mysql> show create view v4;
ERROR 1142 (42000): SHOW VIEW command denied to user'user22'@'localhost' for table 'v4'

[28 Jun 2005 9:43] Oleksandr Byelkin

Thank you for bugreport but I can't repeat it anymore on current 5.0:
mysql> use mysqltest
Database changed
mysql> create table t4 (s1 int);
Query OK, 0 rows affected (0.02 sec)

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

mysql> grant show view on *.* to user22@localhost;
Query OK, 0 rows affected (0.01 sec)

mysql> grant select on v4 to user22@localhost;
Query OK, 0 rows affected (0.00 sec)

mysql> grant select on t4 to user22@localhost;
Query OK, 0 rows affected (0.01 sec)

mysql> \q
Bye
[bell@sanja client]$ ./mysql -u user22 mysqltest
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 4 to server version: 5.0.9-beta-debug-log

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

mysql> show create view v4;
+------+----------------------------------------------------------------------------------------------------------------+
| View | Create View                                                                                                    |
+------+----------------------------------------------------------------------------------------------------------------+
| v4   | CREATE ALGORITHM=UNDEFINED VIEW `mysqltest`.`v4` AS select `mysqltest`.`t4`.`s1` AS `s1` from `mysqltest`.`t4` |
+------+----------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)