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: | |
| Category: | MySQL Server: Views | Severity: | S3 (Non-critical) |
| Version: | 5.1.12 | OS: | Linux (Fedora core 5) |
| Assigned to: | Georgi Kodinov | CPU Architecture: | Any |
[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.
Portions of this website are copyright © 2001, 2002 The PHP Group
Page generated in 0.040 sec. using MySQL 8.4.7-u6-cloud
Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the
respective copyright holders.
It is not reviewed in advance
by Oracle and does not necessarily represent the opinion of
Oracle or any other party.
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.