MySQL Bugs: #22062: View Definer unable to Show View

Bug #22062	View Definer unable to Show View - results in mysql-dump failure
Submitted:	6 Sep 2006 19:30	Modified:	16 Oct 2006 14:57
Reporter:	Erica Moss	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Views	Severity:	S2 (Serious)
Version:	5.0.25 -log	OS:	Linux (Fedora core 5)
Assigned to:		CPU Architecture:	Any

Description:
There exists a shortcoming with the current implementation of Views.  If a user is granted the basic privileges necessary to create a view (CREATE VIEW, SELECT), that user will be unable to call SHOW CREATE VIEW on that object unless the user is also granted SHOW VIEW.

There are a few good reasons why a view definer should be implicitly granted SHOW VIEW at the object level for that view.
* A user should be able to view something he created
* This would be a more consistent behavior given that routines work that way.  A procedure definer can call SHOW CREATE PROCEDURE on that object, and receive a full description of the definition.
* Most importantly this shortcoming becomes problematic when a super user like root tries to perform an export using mysql-dump.  This client evidently uses SHOW CREATE VIEW in order to read out the contents of views, and if the view is SQL SECURITY DEFINER, AND that definer lacks SHOW VIEW, then the export will fail.  The below code demonstrates this problem.

For some reason this behavior has changed between .22, and .25.  On .22 I was able to do an export, however on .25 I had the failure shown below.  It appears that the problem is that although root does have SHOW VIEW and can call SHOW CREATE VIEW v2, once inside v2, the privileges of the definer of v2 are in effect, and user 'create' won't be able to call SHOW CREATE VIEW on view v1, resulting in the failure.

How to repeat:
# do as root
CREATE DATABASE myDB;
use myDB;

GRANT CREATE VIEW, SELECT ON myDB.* TO
        'create'@'localhost' IDENTIFIED BY 'create';

CREATE TABLE t1 (c1 INT);
INSERT INTO t1 VALUES (30);

# do as user 'create'
CREATE VIEW myDB.v1 (c) AS SELECT c1 FROM myDB.t1;
CREATE VIEW myDB.v2 (c) AS SELECT c+1 FROM myDB.v1;

# Attempt to export the database
[emalossi@ericm mysql-test]$ ../client/mysqldump --user=root --all-databases --add-drop-table --socket=./var/tmp/master.sock > ~/dump.out
mysqldump: mysqldump: Couldn't execute 'show create table `v2`': SHOW VIEW command denied to user 'create'@'localhost' for table 'v1' (1142)

Suggested fix:
Trudy suggested a potential fix would be to create a dedicated 'backup' user who would have the ability to perform this action without failures.  I think that a simpler suggestion would be to follow the model that we have for routines.  In that case when a user defines a routine that user is explicitly granted, EXECUTE, and ALTER on that routine.  If a view definer were to be granted SHOW VIEW explicitly at the object level for that view, all of the above problems would be solved.

Currently a workaround for the mysql-dump problem would be for the administrator to grant SHOW VIEW to anyone who would potentially be creating views.  While this would be a solution, it is certainly more troublesome to manage, but since there is a workaround, I'm setting the priority to P3.

Thank you for the report.

Is command "[emalossi@ericm mysql-test]$ ../client/mysqldump --user=root
--all-databases --add-drop-table --socket=./var/tmp/master.sock >
~/dump.out" correct? Do you dump as user root?

If so, I can not repeat it using current sources:

ssmirnova@shella ~/mysql5.0b
$bin/mysql --socket=/tmp/mysql.sock -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 1 to server version: 5.0.26-debug

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

mysql> CREATE DATABASE myDB;
Query OK, 1 row affected (0.05 sec)

mysql> use myDB;
Database changed
mysql>
mysql> GRANT CREATE VIEW, SELECT ON myDB.* TO
    ->         'create'@'localhost' IDENTIFIED BY 'create';
Query OK, 0 rows affected (0.05 sec)

mysql>
mysql> CREATE TABLE t1 (c1 INT);
Query OK, 0 rows affected (0.18 sec)

mysql> INSERT INTO t1 VALUES (30);
Query OK, 1 row affected (0.00 sec)

mysql> \q
Bye

ssmirnova@shella ~/mysql5.0b
$bin/mysql --socket=/tmp/mysql.sock -ucreate myDB -p
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 3 to server version: 5.0.26-debug

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

mysql> CREATE VIEW myDB.v1 (c) AS SELECT c1 FROM myDB.t1;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE VIEW myDB.v2 (c) AS SELECT c+1 FROM myDB.v1;
Query OK, 0 rows affected (0.02 sec)

mysql> \q
Bye

ssmirnova@shella ~/mysql5.0b
$bin/mysqldump --user=root --all-databases --add-drop-table  --socket=/tmp/mysql.sock > bug22062.sql

ssmirnova@shella ~/mysql5.0b

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".

I tried this again with 5.0.26.  This problem does appear to have been resolved in this release.

There is duplicate bug #24070

This problem has re-appeared in version 5.0.45 but was not present in the previous version that we had which was 5.0.15.

Sorry forgot to add that I dump the database as user root.  I have moved over scripts that were executing with no problems on MySQL version 5.0.15, but on version 5.0.45 I get the following error message:

mysqldump: Couldn't execute 'SHOW FIELDS FROM `v': View 'myDB.v' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them (1356)

I can not repeat described behaviour with version 5.0.45

just for reference, I got this problem on 5.0.27