MySQL Lists are EOL. Please join:

List:Commits« Previous MessageNext Message »
From:Tatjana A Nuernberg Date:July 19 2006 9:49am
Subject:bk commit into 5.0 tree (tnurnberg:1.2228) BUG#20989
View as plain text  
Below is the list of changes that have just been committed into a local
5.0 repository of tnurnberg. When tnurnberg does a push these changes will
be propagated to the main repository and, within 24 hours after the
push, to the public repository.
For information on how to access the public repository
see http://dev.mysql.com/doc/mysql/en/installing-source-tree.html

ChangeSet@stripped, 2006-07-19 11:49:07+02:00, tnurnberg@stripped +3 -0
  Bug#20989: View '(null).(null)' references invalid table(s)... on SQL SECURITY INVOKER
  
  REPLACE ... SELECT would require INSERT privileges on certain tables
  when SELECT really suffices. Require INSERT only on target table.

  mysql-test/r/insert_select.result@stripped, 2006-07-19 11:49:01+02:00, tnurnberg@stripped +100 -0
    Bug#20989: View '(null).(null)' references invalid table(s)... on SQL SECURITY INVOKER
    
    Show that REPLACE ... SELECT requires INSERT privileges only on target table.
    (revised test with more view-fu)

  mysql-test/t/insert_select.test@stripped, 2006-07-19 11:49:01+02:00, tnurnberg@stripped +138 -0
    Bug#20989: View '(null).(null)' references invalid table(s)... on SQL SECURITY INVOKER
    
    Show that REPLACE ... SELECT requires INSERT privileges only on target table.
    (revised test with more view-fu)

  sql/sql_insert.cc@stripped, 2006-07-19 11:49:01+02:00, tnurnberg@stripped +8 -1
    Bug#20989: View '(null).(null)' references invalid table(s)... on SQL SECURITY INVOKER
    
    require SELECT rather than INSERT privs on tables that constitute the views we'll read

# This is a BitKeeper patch.  What follows are the unified diffs for the
# set of deltas contained in the patch.  The rest of the patch, the part
# that BitKeeper cares about, is below these diffs.
# User:	tnurnberg
# Host:	salvation.intern.azundris.com
# Root:	/home/tnurnberg/work/mysql-5.0-release-20989

--- 1.194/sql/sql_insert.cc	2006-07-19 11:49:14 +02:00
+++ 1.195/sql/sql_insert.cc	2006-07-19 11:49:14 +02:00
@@ -742,11 +742,18 @@ static bool mysql_prepare_insert_check_t
   bool insert_into_view= (table_list->view != 0);
   DBUG_ENTER("mysql_prepare_insert_check_table");
 
+  /*
+     first table in list is the one we'll INSERT into, requires INSERT_ACL.
+     all others require SELECT_ACL only. the ACL requirement below is for
+     new leaves only anyway (view-constituents), so check for SELECT rather
+     than INSERT.
+  */
+
   if (setup_tables_and_check_access(thd, &thd->lex->select_lex.context,
                                     &thd->lex->select_lex.top_join_list,
                                     table_list, where, 
                                     &thd->lex->select_lex.leaf_tables,
-                                    select_insert, INSERT_ACL))
+                                    select_insert, SELECT_ACL))
     DBUG_RETURN(TRUE);
 
   if (insert_into_view && !fields.elements)

--- 1.38/mysql-test/r/insert_select.result	2006-07-19 11:49:14 +02:00
+++ 1.39/mysql-test/r/insert_select.result	2006-07-19 11:49:14 +02:00
@@ -695,3 +695,103 @@ CREATE TABLE t2 (z int, y int);
 CREATE TABLE t3 (a int, b int);
 INSERT INTO t3 (SELECT x, y FROM t1 JOIN t2 USING (y) WHERE z = 1);
 DROP TABLE IF EXISTS t1,t2,t3;
+CREATE DATABASE meow;
+CREATE TABLE table_target   ( mexs_id CHAR(8), messzeit TIMESTAMP, PRIMARY KEY (mexs_id));
+CREATE TABLE table_target2  ( mexs_id CHAR(8), messzeit TIMESTAMP, PRIMARY KEY (mexs_id));
+CREATE TABLE table_target3  ( mexs_id CHAR(8), messzeit TIMESTAMP, PRIMARY KEY (mexs_id));
+CREATE VIEW view_target2 AS SELECT mexs_id,messzeit FROM table_target2;
+CREATE SQL SECURITY INVOKER VIEW view_target3 AS SELECT mexs_id,messzeit FROM table_target3;
+CREATE TABLE table_stations ( mexs_id VARCHAR(8), icao VARCHAR(4), country CHAR(2), PRIMARY KEY (mexs_id), UNIQUE KEY icao (icao), KEY country (country), CONSTRAINT stations_ibfk_8 FOREIGN KEY (country) REFERENCES countries (country) ON UPDATE CASCADE);
+INSERT INTO table_stations VALUES ('87654321','XXXX','YY');
+CREATE TABLE table_countries ( country CHAR(2), iso_short_en VARCHAR(64), PRIMARY KEY (country));
+INSERT INTO table_countries VALUES ('YY','Entenhausen');
+CREATE ALGORITHM=MERGE SQL SECURITY INVOKER VIEW view_stations AS select table_stations.mexs_id AS mexs_id, table_stations.icao AS icao, table_stations.country AS landescode from (table_stations join table_countries on((table_stations.country = table_countries.country)));
+CREATE TABLE table_source ( id varchar(4), datetime TIMESTAMP, PRIMARY KEY (id));
+INSERT INTO  table_source VALUES ('XXXX','2006-07-12 07:50:00');
+GRANT  SELECT                ON table_source    TO   user20989@localhost;
+GRANT  SELECT                ON table_countries TO   user20989@localhost;
+GRANT  SELECT                ON table_stations  TO   user20989@localhost;
+GRANT  SELECT                ON view_stations   TO   user20989@localhost;
+GRANT  SELECT                ON table_target    TO   user20989@localhost;
+GRANT  SELECT                ON table_target2   TO   user20989@localhost;
+GRANT  INSERT,DELETE,SELECT  ON view_target3    TO   user20989@localhost;
+REPLACE INTO    table_target
+SELECT          stations.mexs_id AS mexs_id, datetime AS messzeit
+FROM            table_source
+INNER JOIN      view_stations AS stations
+ON              table_source.id = stations.icao
+LEFT JOIN       table_target AS old
+USING           (mexs_id);
+ERROR 42000: INSERT,DELETE command denied to user 'user20989'@'localhost' for table 'table_target'
+REPLACE INTO    view_target2
+SELECT          stations.mexs_id AS mexs_id, datetime AS messzeit
+FROM            table_source
+INNER JOIN      view_stations AS stations
+ON              table_source.id = stations.icao
+LEFT JOIN       view_target2 AS old
+USING           (mexs_id);
+ERROR 42000: INSERT,DELETE command denied to user 'user20989'@'localhost' for table 'view_target2'
+REPLACE INTO    view_target3
+SELECT          stations.mexs_id AS mexs_id, datetime AS messzeit
+FROM            table_source
+INNER JOIN      view_stations AS stations
+ON              table_source.id = stations.icao
+LEFT JOIN       view_target3 AS old
+USING           (mexs_id);
+ERROR HY000: View 'meow.view_target3' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
+GRANT  INSERT,DELETE         ON table_target    TO   user20989@localhost;
+GRANT  INSERT,DELETE,SELECT  ON view_target2    TO   user20989@localhost;
+GRANT  INSERT,DELETE,SELECT  ON table_target3   TO   user20989@localhost;
+REPLACE INTO    table_target
+SELECT          stations.mexs_id AS mexs_id, datetime AS messzeit
+FROM            table_source
+INNER JOIN      view_stations AS stations
+ON              table_source.id = stations.icao
+LEFT JOIN       table_target AS old
+USING           (mexs_id);
+REPLACE INTO    table_target2 VALUES ('00X45Y78','2006-07-12 07:50:00');
+ERROR 42000: INSERT,DELETE command denied to user 'user20989'@'localhost' for table 'table_target2'
+REPLACE INTO    view_target2  VALUES ('12X45Y78','2006-07-12 07:50:00');
+SELECT          stations.mexs_id AS mexs_id, datetime AS messzeit
+FROM            table_source
+INNER JOIN      view_stations AS stations
+ON              table_source.id = stations.icao
+LEFT JOIN       view_target2 AS old
+USING           (mexs_id);
+mexs_id	messzeit
+87654321	2006-07-12 07:50:00
+REPLACE INTO    view_target2
+SELECT          stations.mexs_id AS mexs_id, datetime AS messzeit
+FROM            table_source
+INNER JOIN      view_stations AS stations
+ON              table_source.id = stations.icao
+LEFT JOIN       view_target2 AS old
+USING           (mexs_id);
+REPLACE INTO    view_target3
+SELECT          stations.mexs_id AS mexs_id, datetime AS messzeit
+FROM            table_source
+INNER JOIN      view_stations AS stations
+ON              table_source.id = stations.icao
+LEFT JOIN       view_target3 AS old
+USING           (mexs_id);
+SELECT * FROM table_target;
+mexs_id	messzeit
+87654321	2006-07-12 07:50:00
+SELECT * FROM view_target2;
+mexs_id	messzeit
+12X45Y78	2006-07-12 07:50:00
+87654321	2006-07-12 07:50:00
+SELECT * FROM view_target3;
+mexs_id	messzeit
+87654321	2006-07-12 07:50:00
+DROP VIEW  view_stations;
+DROP TABLE table_source;
+DROP TABLE table_countries;
+DROP TABLE table_stations;
+DROP TABLE table_target;
+DROP TABLE table_target2;
+DROP TABLE table_target3;
+DROP VIEW  view_target2;
+DROP VIEW  view_target3;
+DROP USER  user20989@localhost;
+DROP DATABASE meow;

--- 1.31/mysql-test/t/insert_select.test	2006-07-19 11:49:14 +02:00
+++ 1.32/mysql-test/t/insert_select.test	2006-07-19 11:49:14 +02:00
@@ -247,3 +247,141 @@ CREATE TABLE t2 (z int, y int);
 CREATE TABLE t3 (a int, b int);
 INSERT INTO t3 (SELECT x, y FROM t1 JOIN t2 USING (y) WHERE z = 1);
 DROP TABLE IF EXISTS t1,t2,t3;
+
+#
+# Bug #20989: View '(null).(null)' references invalid table(s)... on
+#             SQL SECURITY INVOKER
+#
+# this is really the fact that REPLACE ... SELECT required additional
+# INSERT privs (on tables that are part of a view) over the related
+# REPLACE, SELECT
+#
+
+CREATE DATABASE meow;
+
+connect (root,localhost,root,,meow);
+connection root;
+
+CREATE TABLE table_target   ( mexs_id CHAR(8), messzeit TIMESTAMP, PRIMARY KEY (mexs_id));
+CREATE TABLE table_target2  ( mexs_id CHAR(8), messzeit TIMESTAMP, PRIMARY KEY (mexs_id));
+CREATE TABLE table_target3  ( mexs_id CHAR(8), messzeit TIMESTAMP, PRIMARY KEY (mexs_id));
+CREATE VIEW view_target2 AS SELECT mexs_id,messzeit FROM table_target2;
+CREATE SQL SECURITY INVOKER VIEW view_target3 AS SELECT mexs_id,messzeit FROM table_target3;
+
+CREATE TABLE table_stations ( mexs_id VARCHAR(8), icao VARCHAR(4), country CHAR(2), PRIMARY KEY (mexs_id), UNIQUE KEY icao (icao), KEY country (country), CONSTRAINT stations_ibfk_8 FOREIGN KEY (country) REFERENCES countries (country) ON UPDATE CASCADE);
+INSERT INTO table_stations VALUES ('87654321','XXXX','YY');
+
+CREATE TABLE table_countries ( country CHAR(2), iso_short_en VARCHAR(64), PRIMARY KEY (country));
+INSERT INTO table_countries VALUES ('YY','Entenhausen');
+
+CREATE ALGORITHM=MERGE SQL SECURITY INVOKER VIEW view_stations AS select table_stations.mexs_id AS mexs_id, table_stations.icao AS icao, table_stations.country AS landescode from (table_stations join table_countries on((table_stations.country = table_countries.country)));
+
+CREATE TABLE table_source ( id varchar(4), datetime TIMESTAMP, PRIMARY KEY (id));
+INSERT INTO  table_source VALUES ('XXXX','2006-07-12 07:50:00');
+
+GRANT  SELECT                ON table_source    TO   user20989@localhost;
+GRANT  SELECT                ON table_countries TO   user20989@localhost;
+GRANT  SELECT                ON table_stations  TO   user20989@localhost;
+GRANT  SELECT                ON view_stations   TO   user20989@localhost;
+GRANT  SELECT                ON table_target    TO   user20989@localhost;
+GRANT  SELECT                ON table_target2   TO   user20989@localhost;
+GRANT  INSERT,DELETE,SELECT  ON view_target3    TO   user20989@localhost;
+
+connect (user20989,localhost,user20989,,meow);
+connection user20989;
+
+--error 1142
+REPLACE INTO    table_target
+SELECT          stations.mexs_id AS mexs_id, datetime AS messzeit
+FROM            table_source
+INNER JOIN      view_stations AS stations
+ON              table_source.id = stations.icao
+LEFT JOIN       table_target AS old
+USING           (mexs_id);
+
+--error 1142
+REPLACE INTO    view_target2
+SELECT          stations.mexs_id AS mexs_id, datetime AS messzeit
+FROM            table_source
+INNER JOIN      view_stations AS stations
+ON              table_source.id = stations.icao
+LEFT JOIN       view_target2 AS old
+USING           (mexs_id);
+
+--error 1356
+REPLACE INTO    view_target3
+SELECT          stations.mexs_id AS mexs_id, datetime AS messzeit
+FROM            table_source
+INNER JOIN      view_stations AS stations
+ON              table_source.id = stations.icao
+LEFT JOIN       view_target3 AS old
+USING           (mexs_id);
+
+connection root;
+disconnect user20989;
+
+GRANT  INSERT,DELETE         ON table_target    TO   user20989@localhost;
+GRANT  INSERT,DELETE,SELECT  ON view_target2    TO   user20989@localhost;
+GRANT  INSERT,DELETE,SELECT  ON table_target3   TO   user20989@localhost;
+
+connect (user20989,localhost,user20989,,meow);
+connection user20989;
+
+REPLACE INTO    table_target
+SELECT          stations.mexs_id AS mexs_id, datetime AS messzeit
+FROM            table_source
+INNER JOIN      view_stations AS stations
+ON              table_source.id = stations.icao
+LEFT JOIN       table_target AS old
+USING           (mexs_id);
+
+--error 1142
+REPLACE INTO    table_target2 VALUES ('00X45Y78','2006-07-12 07:50:00');
+REPLACE INTO    view_target2  VALUES ('12X45Y78','2006-07-12 07:50:00');
+
+SELECT          stations.mexs_id AS mexs_id, datetime AS messzeit
+FROM            table_source
+INNER JOIN      view_stations AS stations
+ON              table_source.id = stations.icao
+LEFT JOIN       view_target2 AS old
+USING           (mexs_id);
+
+REPLACE INTO    view_target2
+SELECT          stations.mexs_id AS mexs_id, datetime AS messzeit
+FROM            table_source
+INNER JOIN      view_stations AS stations
+ON              table_source.id = stations.icao
+LEFT JOIN       view_target2 AS old
+USING           (mexs_id);
+
+REPLACE INTO    view_target3
+SELECT          stations.mexs_id AS mexs_id, datetime AS messzeit
+FROM            table_source
+INNER JOIN      view_stations AS stations
+ON              table_source.id = stations.icao
+LEFT JOIN       view_target3 AS old
+USING           (mexs_id);
+
+connection root;
+disconnect user20989;
+
+SELECT * FROM table_target;
+SELECT * FROM view_target2;
+SELECT * FROM view_target3;
+
+DROP VIEW  view_stations;
+DROP TABLE table_source;
+DROP TABLE table_countries;
+DROP TABLE table_stations;
+DROP TABLE table_target;
+DROP TABLE table_target2;
+DROP TABLE table_target3;
+DROP VIEW  view_target2;
+DROP VIEW  view_target3;
+DROP USER  user20989@localhost;
+
+disconnect root;
+
+connection default;
+
+DROP DATABASE meow;
Thread
bk commit into 5.0 tree (tnurnberg:1.2228) BUG#20989Tatjana A Nuernberg19 Jul