MySQL Bugs: #21261: Insert into an updatable view with no WHERE clause requiring SELECT

Bug #21261	Insert into an updatable view with no WHERE clause requiring SELECT - Regression
Submitted:	24 Jul 2006 23:17	Modified:	6 Sep 2006 23:31
Reporter:	Erica Moss	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Views	Severity:	S2 (Serious)
Version:	5.1	OS:	Linux (Fedora core 5)
Assigned to:	Evgeny Potemkin	CPU Architecture:	Any

Description:
This behavior is not seen in GA 5.0.22.

This simple INSERT statement is failing for the user unless SELECT is also added to the user's privileges.  Alternatively, if the INSERT statement is changed from v1 to t1 it will also succeed.  So, an INSERT grant is sufficient to insert into a table, but not sufficient to insert into a derived table.

How to repeat:
CREATE DATABASE viewDB;
use viewDB;

GRANT INSERT ON viewDB.* TO
         'view_insert'@'localhost' IDENTIFIED BY 'view_insert';
connect (view_insert, localhost, view_insert, view_insert,viewDB);

connection default;
CREATE TABLE t1 (x INT);

CREATE SQL SECURITY INVOKER VIEW v1 AS
          SELECT x FROM t1;

connection view_insert;
INSERT INTO v1 (x) VALUES (5);

connection default;
SELECT * FROM t1;
REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'view_insert'@'localhost';
DROP USER 'view_insert'@'localhost';
DROP VIEW v1;
DROP TABLE t1;
DROP DATABASE viewDB;

###########
OUTPUT

mysqltest: At line 15: query 'INSERT INTO v1 (x) VALUES (5)' failed: 1356: View 'viewDB.v1' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them

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/10322

ChangeSet@1.2246, 2006-08-12 02:14:56+04:00, evgen@sunlight.local +9 -0
  Fixed bug#21261: Wrong access rights was required for an insert to a view
  
  SELECT right instead of INSERT right was required for an insert to a view
  instead.
  This change is done in fix for bug 20989. Its intention was to ask only SELECT
  right for all tables except the very first for a complex INSERT query.
  But that patch has done it in a wrong way and lead to asking a wrong
  access right for an insert to a view.
  
  The setup_tables_and_check_access() function now accepts two want_access
  parameters. One will be used for the first table and the second for other
  tables.

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/10587

ChangeSet@1.2262, 2006-08-17 18:50:53+04:00, evgen@moonbone.local +3 -0
  ndb_condition_pushdown.result:
    Corrected test case result after fix for bug#18165
  view.result, view.test:
    Corrected test case for bug#21261

Fixed in 5.0.25

Fixed in 5.1.12

Thank you for your bug report. This issue has been committed to our source repository of that product and will be incorporated into the next release.

If necessary, you can access the source repository and build the latest available version, including the bug fix. More information about accessing the source trees is available at

    http://dev.mysql.com/doc/en/installing-source.html

Documented bugfix in 5.0.25 and 5.1.12 changelogs.