MySQL Lists are EOL. Please join:

List:Commits« Previous MessageNext Message »
From:Tatjana A Nuernberg Date:July 18 2006 3:38pm
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-18 17:38:27+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-18 17:38:20+02:00, tnurnberg@stripped +75 -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.

  mysql-test/t/insert_select.test@stripped, 2006-07-18 17:38:20+02:00, tnurnberg@stripped +113 -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.

  sql/sql_insert.cc@stripped, 2006-07-18 17:38:20+02:00, tnurnberg@stripped +8 -2
    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/mysql-5.0-release

--- 1.194/sql/sql_insert.cc	2006-07-18 17:38:36 +02:00
+++ 1.195/sql/sql_insert.cc	2006-07-18 17:38:36 +02:00
@@ -742,11 +742,17 @@ 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, 
+                                    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-18 17:38:36 +02:00
+++ 1.39/mysql-test/r/insert_select.result	2006-07-18 17:38:36 +02:00
@@ -695,3 +695,78 @@ 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 VIEW view_target AS SELECT mexs_id,messzeit FROM table_target2;
+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;
+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_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       view_target AS old
+USING           (mexs_id);
+ERROR 42000: INSERT,DELETE command denied to user 'user20989'@'localhost' for table 'view_target'
+GRANT  INSERT,DELETE        ON table_target    TO   user20989@localhost;
+GRANT  INSERT,DELETE,SELECT ON view_target     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_target   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_target AS old
+USING           (mexs_id);
+mexs_id	messzeit
+87654321	2006-07-12 07:50:00
+REPLACE INTO    view_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       view_target AS old
+USING           (mexs_id);
+SELECT * FROM table_target;
+mexs_id	messzeit
+87654321	2006-07-12 07:50:00
+SELECT * FROM view_target;
+mexs_id	messzeit
+12X45Y78	2006-07-12 07:50:00
+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 VIEW  view_target;
+DROP USER  user20989@localhost;
+DROP DATABASE meow;

--- 1.31/mysql-test/t/insert_select.test	2006-07-18 17:38:36 +02:00
+++ 1.32/mysql-test/t/insert_select.test	2006-07-18 17:38:36 +02:00
@@ -247,3 +247,116 @@ 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 VIEW view_target AS SELECT mexs_id,messzeit FROM table_target2;
+
+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;
+
+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_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       view_target AS old
+USING           (mexs_id);
+
+connection root;
+disconnect user20989;
+
+GRANT  INSERT,DELETE        ON table_target    TO   user20989@localhost;
+GRANT  INSERT,DELETE,SELECT ON view_target     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_target   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_target AS old
+USING           (mexs_id);
+
+REPLACE INTO    view_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       view_target AS old
+USING           (mexs_id);
+
+connection root;
+disconnect user20989;
+
+SELECT * FROM table_target;
+SELECT * FROM view_target;
+
+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 VIEW  view_target;
+DROP USER  user20989@localhost;
+
+disconnect root;
+
+connection default;
+
+DROP DATABASE meow;
Thread
bk commit into 5.0 tree (tnurnberg:1.2228) BUG#20989Tatjana A Nuernberg18 Jul