MySQL Bugs: #14873: SHOW CREATE VIEW: incorrect output

Bug #14873	SHOW CREATE VIEW: incorrect output
Submitted:	11 Nov 2005 17:47	Modified:	22 May 2006 18:54
Reporter:	Paul DuBois	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Views	Severity:	S3 (Non-critical)
Version:	5.0.13 and up	OS:	Linux (Linux)
Assigned to:	Georgi Kodinov	CPU Architecture:	Any

Description:
Views that were created in versions of MySQL older than 5.0.13
are treated impliciatly as though they were defined with SQL SECURITY
INVOKER (this is according to Sanja).  However, if you create a view
in an old version and then upgrade to 5.0.13 or higher, SHOW CREATE
VIEW displays SQL SECURITY DEFINER. This means that if the view is dumped
and reloaded, it will be recreated with the wrong definition. (This
bug also affects mysqldump, which uses SHOW CREATE VIEW.)

In MySQL 5.0.12:
  
mysql> create view v as select 1;
Query OK, 0 rows affected (0.00 sec)
  
mysql> show create view v;
+------+---------------------------------------------------------------+
| View | Create View                                                   |
+------+---------------------------------------------------------------+
| v    | CREATE ALGORITHM=UNDEFINED VIEW `test`.`v` AS select 1 AS `1` |
+------+---------------------------------------------------------------+
1 row in set (0.00 sec)

Upgrade to 5.0.13 or higher (or simulate by copying v.frm from the 5.0.12
server to the 5.0.13 or higher server, which is how I tested this):    

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

The output should say "SQL SECURITY INVOKER".

How to repeat:
See above.

Thank you for taking the time to write to us, but this is not
a bug. Please double-check the documentation available at
http://www.mysql.com/documentation/ and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php

Additional info:

5.0.12 exposes behaviour typical for SQL SECURITY DEFINER. See the example below.

Tried with version 5.0.12 (from http://downloads.mysql.com/archives.php). 

I have the following test file :
select version();
version()
5.0.12-beta-max-log
create database mysqltest1;
create table mysqltest1.t1 (a int primary key);
insert into mysqltest1.t1 values (1), (2), (3);
create table mysqltest1.t2 (a int primary key);
insert into mysqltest1.t2 values (1), (2), (3);
create view mysqltest1.v as select a from mysqltest1.t1;
show create view mysqltest1.v;
View    Create View
v       CREATE ALGORITHM=UNDEFINED VIEW `mysqltest1`.`v` AS select `mysqltest1`.`t1`.`a` AS `a` from `mysqltest1`.`t1`
grant select on mysqltest1.v to ro@localhost;
select * from mysqltest1.v;
a
1
2
3
select * from mysqltest1.t1;
ERROR 42000: SELECT command denied to user 'ro'@'localhost' for table 't1'
select * from mysqltest1.t2;
ERROR 42000: SELECT command denied to user 'ro'@'localhost' for table 't2'
drop view mysqltest1.v;
drop table mysqltest1.t1;
drop table mysqltest1.t2;
drop database mysqltest1;

Thank you for your bug report. This issue has been addressed in the
documentation. The updated documentation will appear on our website
shortly, and will be included in the next release of the relevant
product(s).

Additional info:

I've updated the views chapter to indicate that old
views are handled as though the DEFINER is the invoker.

Make that: ... as though the invoker is the DEFINER.