Bug #21080 ALTER VIEW makes user restate SQL SECURITY mode, and ALGORITHM
Submitted: 15 Jul 2006 23:42 Modified: 3 Aug 2006 17:37
Reporter: Erica Moss Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Views Severity:S3 (Non-critical)
Version:5.1.12 OS:Linux (Fedora core 5)
Assigned to: Georgi Kodinov CPU Architecture:Any

[15 Jul 2006 23:42] Erica Moss
Description:
As the code below demonstrates, if you alter a view which has been defined as SQL SECURITY INVOKER, or that specifies an ALGORITHM to use, and you fail to restate such choices, then those values will be spontaneously reset to the default values of SQL SECURITY DEFINER, and or ALGORITHM=UNDEFINED.

By way of comparison, the same steps are done with a routine, and in that case, only the things that are specifically chosen for replacement are replaced.  It would be cleaner, more consistent and predictable if ALTER VIEW worked the same way.

How to repeat:
DROP DATABASE IF EXISTS fooDB;
CREATE DATABASE fooDB;
use fooDB;

CREATE TABLE t1 (x INT, y INT);

CREATE PROCEDURE proc() SQL SECURITY INVOKER
   select * FROM v1;
SHOW CREATE PROCEDURE proc;
ALTER PROCEDURE proc
      comment 'this is a comment';
SHOW CREATE PROCEDURE proc;

CREATE ALGORITHM=TEMPTABLE SQL SECURITY INVOKER VIEW v1
       AS SELECT x FROM t1;
SHOW CREATE VIEW v1;
ALTER VIEW v1 AS SELECT x, y FROM t1;
SHOW CREATE VIEW v1;

DROP VIEW v1;
DROP TABLE t1;
DROP PROCEDURE proc;
DROP DATABASE fooDB;

################
partial output##
################
ALTER PROCEDURE proc comment 'this is a comment';
SHOW CREATE PROCEDURE proc;
Procedure     sql_mode        Create Procedure
proc          CREATE DEFINER=`root`@`localhost` PROCEDURE `proc`()
    SQL SECURITY INVOKER
    COMMENT 'this is a comment'

ALTER VIEW v1 AS SELECT x, y FROM t1;
SHOW CREATE VIEW v1;
View  Create View
v1    CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `t1`.`x` AS `x`,`t1`.`y` AS `y` from `t1`

Suggested fix:
Since it is possible for the user to manually reset the values for security and algorithm, there is no reason to reset them.  The value for definer would need to change, as would the value for check option since the user has no way to specify 'no check option', thus omitting the value is the only way to cause the alter statement to remove it.

Note: if this can't be resolved in this manner for fears of creating field issues, then the ALTER VIEW page of the manual should be updated to describe this behavior.
[27 Jul 2006 14:53] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/9670
[2 Aug 2006 19:00] Evgeny Potemkin
When executing ALTER TABLE all the attributes of the view were overwritten.
This is contrary to the user's expectations.

Fixed in 5.0.25, 5.1.12
[3 Aug 2006 17:37] Paul DuBois
Noted in 5.0.25, 5.1.12 changelogs.

ALTER VIEW did not retain existing values of attributes that had been
originally specified but were not changed in the ALTER VIEW statement.