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