MySQL Bugs: #16589: MySQL root user cannot access VIEW

Bug #16589	MySQL root user cannot access VIEW
Submitted:	17 Jan 2006 23:41	Modified:	14 Dec 2006 11:33
Reporter:	Hakan Küçükyılmaz	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Views	Severity:	S3 (Non-critical)
Version:	5.0	OS:	Linux (Linux)
Assigned to:	Georgi Kodinov	CPU Architecture:	Any

Description:
If a user creates a view and the user gets deleted some time after the view was created by that user the view is not accessible anymore. Even the root user cannot access it anymore.

How to repeat:
As root:

USE test;
CREATE USER foo;
GRANT ALL PRIVILEGES ON test.* to foo;
DROP TABLE IF EXISTS t1;
DROP VIEW IF EXISTS v1;
CREATE TABLE t1(a int);
\q

mysql -ufoo test
CREATE VIEW v1 as SELECT a FROM t1;
\q

mysql -uroot test
DROP USER foo;
SELECT * FROM v1;

Error message:
[00:38] root@test>SELECT * FROM v1;
ERROR 1449 (HY000): There is no 'foo'@'%' registered
5.0.19-debug-log

Suggested fix:
The MySQL root user should have access to all objects.

Thank you for a problem report. Looks like it is intended behaviour since 5.0.16. The root user should be able to change the view (including definer) with ALTER VIEW (http://dev.mysql.com/doc/refman/5.0/en/alter-view.html), or just by dropping and creating it.

I tried following:
[12:43] root@test>ALTER DEFINER=CURRENT_USER VIEW v1;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1
5.0.19

which is obvious as I need the VIEW definition as mentioned in
http://dev.mysql.com/doc/refman/5.0/en/alter-view.html

But if I want to get the view definition, I get following:
[12:44] root@test>show create view v1;
ERROR 1449 (HY000): There is no 'foo'@'%' registered
5.0.19

Conclusion: I have to recreate the user foo, then ALTER VIEW, then drop user foo again.

If I have 151 different users and 1532 different views created by some of the 151 users and I want to drop some users, do I have to check every view? What if I need some of the views, regardless of user? This sounds not like ease of use to me.

Sorry, but I was not able to repeat with latest 5.0.19-BK build (ChangeSet@1.2039, 2006-02-07 00:26:47+01:00):

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 4 to server version: 5.0.19

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

mysql> CREATE USER foo;
GQuery OK, 0 rows affected (0.01 sec)

Rmysql> GRANT ALL PRIVILEGES ON test.* to foo;
Query OK, 0 rows affected (0.00 sec)

mysql> DROP TABLE IF EXISTS t1;
Query OK, 0 rows affected (0.02 sec)

mysql> DROP VIEW IF EXISTS v1;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> CREATE TABLE t1(a int);
Query OK, 0 rows affected (0.02 sec)

mysql> exit
Bye
openxs@suse:~/dbs/5.0> bin/mysql -ufoo 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 to server version: 5.0.19

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

mysql> CREATE VIEW v1 as SELECT a FROM t1;
Query OK, 0 rows affected (0.01 sec)

mysql> exit
Bye
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 6 to server version: 5.0.19

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

mysql> DROP USER foo;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM v1;
Empty set (0.00 sec)

mysql> desc v1;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| a     | int(11) | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
1 row in set (0.00 sec)

mysql> show create view v1;
+------+------------------------------------------------------------------------
----------------------------------------------+
| View | Create View
                                              |
+------+------------------------------------------------------------------------
----------------------------------------------+
| v1   | CREATE ALGORITHM=UNDEFINED DEFINER=``@`localhost` SQL SECURITY DEFINER
VIEW `v1` AS select `t1`.`a` AS `a` from `t1` |
+------+------------------------------------------------------------------------
----------------------------------------------+
1 row in set (0.00 sec)

So, looks like the behaviour is already changed due to some other fix.

Sorry, but I got still same behaviour with latest 5.0.19:
Maybe sql_mode makes a difference?
sql_mode="ANSI_QUOTES,NO_BACKSLASH_ESCAPES,TRADITIONAL,IGNORE_SPACE"

[15:32] root@test>CREATE USER foo;
Query OK, 0 rows affected (0.00 sec)

5.0.19-log
[15:32] root@test>GRANT ALL PRIVILEGES ON test.* to foo;
Query OK, 0 rows affected (0.00 sec)

5.0.19-log
[15:32] root@test>DROP TABLE IF EXISTS t1;
Query OK, 0 rows affected, 1 warning (0.00 sec)

5.0.19-log
[15:33] root@test>DROP VIEW IF EXISTS v1;
Query OK, 0 rows affected (0.00 sec)

5.0.19-log
[15:33] root@test>CREATE TABLE t1(a int);
Query OK, 0 rows affected (0.09 sec)

5.0.19-log
[15:33] root@test>exit
Bye
hakan@lu0008:~/work/mysql> mysql -ufoo test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2 to server version: 5.0.19-log

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

5.0.19-log
[15:33] foo@test> CREATE VIEW v1 as SELECT a FROM t1;
Query OK, 0 rows affected (0.00 sec)

5.0.19-log
[15:33] foo@test>exit
Bye
hakan@lu0008:~/work/mysql> mysql -uroot test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3 to server version: 5.0.19-log

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

5.0.19-log
[15:33] root@test>DROP USER foo;
Query OK, 0 rows affected (0.00 sec)

5.0.19-log
[15:34] root@test>SELECT * FROM v1;
ERROR 1449 (HY000): There is no 'foo'@'%' registered
5.0.19-log
[15:34] root@test>desc v1;
ERROR 1449 (HY000): There is no 'foo'@'%' registered
5.0.19-log
[15:34] root@test>show create view v1;
ERROR 1449 (HY000): There is no 'foo'@'%' registered
5.0.19-log

Stated behaviour only happens with following sql_mode:
sql_mode="ANSI_QUOTES,NO_BACKSLASH_ESCAPES,TRADITIONAL,IGNORE_SPACE"

With sql_mode="", SHOW CREATE VIEW v1 works after user foo is dropped by root.

Thank you for your bug report. This issue has been committed to our source repository of that product and will be incorporated into the next release.

If necessary, you can access the source repository and build the latest available version, including the bug fix. More information about accessing the source trees is available at

    http://dev.mysql.com/doc/en/installing-source.html

Seems like a duplicate of bug #14875. Tried with the latest BK repository (5.0.32).
Here's what I get :
+ SET sql_mode="ANSI_QUOTES,NO_BACKSLASH_ESCAPES,TRADITIONAL,IGNORE_SPACE";
+ CREATE USER foo;
+ GRANT ALL PRIVILEGES ON test.* to foo;
+ CREATE TABLE t1(a int);
+ SET sql_mode="ANSI_QUOTES,NO_BACKSLASH_ESCAPES,TRADITIONAL,IGNORE_SPACE";
+ CREATE VIEW v1 as SELECT a FROM t1;
+ SET sql_mode="ANSI_QUOTES,NO_BACKSLASH_ESCAPES,TRADITIONAL,IGNORE_SPACE";
+ DROP USER foo;
+ SELECT * FROM v1;
+ a
+ DESCRIBE v1;
+ Field Type    Null    Key     Default Extra
+ a     int(11) YES             NULL
+ SHOW CREATE VIEW v1;
+ View  Create View
+ v1    CREATE ALGORITHM=UNDEFINED DEFINER=""@"localhost" SQL SECURITY DEFINER VIEW "v1" AS select "t1"."a" AS "a" from "t1"
+ DROP VIEW v1;