MySQL Bugs: #40639: SQL SECURITY DEFINER

Bug #40639	SQL SECURITY DEFINER < 5.0.13
Submitted:	11 Nov 2008 15:21	Modified:	12 Nov 2008 18:39
Reporter:	Olaf van der Spek (Basic Quality Contributor)	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Views	Severity:	S3 (Non-critical)
Version:	5.0	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	qc

Description:
http://dev.mysql.com/doc/refman/5.0/en/create-view.html

> If you invoke a view that was created before MySQL 5.0.13, it is treated as though it was created with a SQL SECURITY DEFINER clause and with a DEFINER  value that is the same as your account. 

Isn't that equivalent to "it is treated as though it was created with a SQL SECURITY INVOKER"?

It seems a lot simpler to just say that.

How to repeat:
-

Thank you for the report.

But this text reflects current situation:

$./bin/mysql --socket=/tmp/mysql_ssmirnova.sock  -uroot test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 5.0.12-beta-max

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

mysql> create view v1 as select 1;
Query OK, 0 rows affected (0.00 sec)

mysql> show create view v1;
+------+----------------------------------------------------------------+
| View | Create View                                                    |
+------+----------------------------------------------------------------+
| v1   | CREATE ALGORITHM=UNDEFINED VIEW `test`.`v1` AS select 1 AS `1` |
+------+----------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> \q
Bye

Start 5.0.74 server with same data directory, then issue SHOW CREATE VIEW again:

$./bin/mysql --socket=/tmp/mysql_ssmirnova.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
Server version: 5.0.74-debug-log Source distribution

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

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

mysql> show warnings;
+---------+------+----------------------------------------------------------------------------------------------------------------------------+
| Level   | Code | Message                                                                                                                    |
+---------+------+----------------------------------------------------------------------------------------------------------------------------+
| Warning | 1447 | View 'test'.'v1' has no definer information (old table format). Current user is used as definer. Please recreate the view! | 
+---------+------+----------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

> CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW
`v1` AS select 1 AS `1`

What happened if you login as the anonymous user and run that statement again?
Wouldn't it show DEFINER=''@'localhost'?
So it doesn't really reflect what is going on and showing SQL SECURITY INVOKER would be more accurate, as that's what is really happening until you redefine the view.

Thank you for the feedback.

But if I create view with SQL SECURITY INVOKER query SHOW CREATE VIEW will show this:

mysql> CREATE SQL SECURITY invoker VIEW v2 as select 2;
Query OK, 0 rows affected (0.01 sec)

mysql> show create view v2;
+------+---------------------------------------------------------------------------------------------------------+
| View | Create View                                                                                             |
+------+---------------------------------------------------------------------------------------------------------+
| v2   | CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY INVOKER VIEW `v2` AS select 2 AS `2` | 
+------+---------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

So documentation is correct. Additionally I see no sense to change current server behavior, because version 5.0.12 is very old and was not GA yet.