| Bug #5215 | Illegal INSERT/UPDATE Possible with VIEW | ||
|---|---|---|---|
| Submitted: | 25 Aug 2004 20:19 | Modified: | 1 Sep 2004 21:55 |
| Reporter: | Mike Hillyer | Email Updates: | |
| Status: | Not a Bug | Impact on me: | |
| Category: | MySQL Server | Severity: | S2 (Serious) |
| Version: | 5.0.1-Snapshot | OS: | Linux (Redhat Enterpise Linux 3) |
| Assigned to: | Oleksandr Byelkin | CPU Architecture: | Any |
[25 Aug 2004 21:16]
Peter Gulutzan
This doesn't look like a bug. It's the sort of thing that WITH CHECK OPTION should handle, though.
[26 Aug 2004 6:44]
MySQL Verification Team
Verified your sample on 5.0.2 BK source and assigned Sanja for his comment regarding PeterG comment as not a bug.
[1 Sep 2004 21:55]
Oleksandr Byelkin
Thank you for bug report! As it was mentioned by Peter Gulutzan this behaviour should be controled by WITH CHECK OPTION, but this option is not implemented yet (after recognizing by parser it do nothing). This feature planed to be implemented in September.
Description: If you create a VIEW with a WHERE clause that restricts the VIEW, you can still INSERT/UPDATE a row to values that will cause it to not be part of the VIEW. How to repeat: Using world database: mysql> CREATE VIEW NorthAmerica AS SELECT * FROM Country WHERE Continent = 'North America'; Query OK, 0 rows affected (0.00 sec) mysql> INSERT INTO NorthAmerica(Name, Continent, Population) VALUES('MikeLand','South America',1); Query OK, 1 row affected (0.00 sec) mysql> SELECT * FROM Country WHERE Name = 'MikeLand'; +------+----------+---------------+--------+-------------+-----------+------------+----------------+------+--------+-----------+----------------+-------------+---------+-------+ | Code | Name | Continent | Region | SurfaceArea | IndepYear | Population | LifeExpectancy | GNP | GNPOld | LocalName | GovernmentForm | HeadOfState | Capital | Code2 | +------+----------+---------------+--------+-------------+-----------+------------+----------------+------+--------+-----------+----------------+-------------+---------+-------+ | | MikeLand | South America | | 0.00 | NULL | 1 | NULL | NULL | NULL | | | NULL | NULL | | +------+----------+---------------+--------+-------------+-----------+------------+----------------+------+--------+-----------+----------------+-------------+---------+-------+ 1 row in set (0.00 sec) mysql> As you can see, while the VIEW only allows 'North America' rows, I can still insert a row for 'South America' Suggested fix: Check VIEW WHERE clause, if the new/updated row would not be present in the view, deny the user from making the change.