bk commit into 5.0 tree (dlenev:1.2011) BUG#11555

[dlenev]

Below is the list of changes that have just been committed into a local
5.0 repository of dlenev. When dlenev 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
  1.2011 05/12/07 12:27:17 dlenev@stripped +10 -0
  Fix for bug #11555 "Stored procedures: current SP tables locking make
  impossible view security".
  
  We should not expose names of tables which are explicitly or implicitly (via
  routine or trigger) used by view even if we find that they are missing.
  So during building of list of prelocked tables for statement we track which
  routines (and therefore tables for these routines) are used from views. We
  mark elements of LEX::routines set which correspond to routines used in views
  by setting Sroutine_hash_entry::belong_to_view member to point to TABLE_LIST
  object for topmost view which uses routine. We propagate this mark to all
  routines which are used by this routine and which we add to this set. We also
  mark tables used by such routine which we add to the list of tables for
  prelocking as belonging to this view.

  sql/sql_trigger.h
    1.16 05/12/07 12:27:11 dlenev@stripped +1 -1
    Table_triggers_list:
      sp_cache_routines_and_add_tables_for_triggers() now accept pointer to table
      list element as last argument, this allows to determine correctly uppermost
      view which uses this table with trigger.

  sql/sql_base.cc
    1.323 05/12/07 12:27:11 dlenev@stripped +2 -3
    open_tables():
      sp_cache_routines_and_add_tables_for_view()/for_triggers() now accept
      pointer to table list element as last argument, this allows them to determine
      correctly uppermost view which uses this view/table with trigger.

  sql/sp_head.h
    1.78 05/12/07 12:27:11 dlenev@stripped +2 -1
    sp_head::add_used_tables_to_table_list():
      Added new argument which allows to mark tables which are added to table
      list for prelocking as belonging to view (this allows properly hide names
      of tables which are used in routines used by views).

  sql/sp_head.cc
    1.203 05/12/07 12:27:10 dlenev@stripped +9 -5
    sp_head::add_used_tables_to_table_list():
      Added new argument which allows to mark tables which are added to table
      list for prelocking as belonging to view (this allows properly hide names
      of tables which are used in routines used by views).

  sql/sp.h
    1.31 05/12/07 12:27:10 dlenev@stripped +2 -2
    sp_cache_routines_and_add_tables_for_view()/for_triggers():
      To be able to determine correctly uppermost view which uses this view/table
      with trigger we have to pass pointer to TABLE_LIST object instead of pointer
      to view's LEX or to Table_triggers_list object.

  sql/sp.cc
    1.103 05/12/07 12:27:10 dlenev@stripped +53 -30
    We should not expose names of tables which are explicitly or implicitly
    (via routine or trigger) used by view even if we find that they are missing.
    So during building of list of prelocked tables for statement we track which
    routines (and therefore tables for these routines) are used from views. We
    mark elements of LEX::routines set which correspond to routines used in views
    by setting Sroutine_hash_entry::belong_to_view member to point to TABLE_LIST
    object for topmost view which uses routine. We propagate this mark to all
    routines which are used by this routine and which we add to this set. We also
    mark tables used by such routine which we add to the list of tables for
    prelocking as belonging to this view.

  mysql-test/t/view.test
    1.127 05/12/07 12:27:10 dlenev@stripped +0 -1
    Removed comment obsoleted by bugfix.

  mysql-test/t/sp-error.test
    1.98 05/12/07 12:27:10 dlenev@stripped +61 -0
    Added test for bug #11555 "Stored procedures: current SP tables locking make 
    impossible view security".

  mysql-test/r/view.result
    1.137 05/12/07 12:27:10 dlenev@stripped +3 -3
    We should not expose tables which are expicitly/implicitly used in view in
    check table statement.

  mysql-test/r/sp-error.result
    1.94 05/12/07 12:27:10 dlenev@stripped +40 -0
    Added test for bug #11555 "Stored procedures: current SP tables locking make 
    impossible view security".

# 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:	dlenev
# Host:	brandersnatch.site
# Root:	/home/dlenev/src/mysql-5.0-bg11555-2

--- 1.322/sql/sql_base.cc	2005-11-24 03:36:25 +03:00
+++ 1.323/sql/sql_base.cc	2005-12-07 12:27:11 +03:00
@@ -2117,8 +2117,7 @@
       {
         if (!query_tables_last_own)
             query_tables_last_own= thd->lex->query_tables_last;
-        sp_cache_routines_and_add_tables_for_triggers(thd, thd->lex,
-                                                      tables->table->triggers);
+        sp_cache_routines_and_add_tables_for_triggers(thd, thd->lex, tables);
       }
       free_root(&new_frm_mem, MYF(MY_KEEP_PREALLOC));
     }
@@ -2139,7 +2138,7 @@
       /* We have at least one table in TL here. */
       if (!query_tables_last_own)
         query_tables_last_own= thd->lex->query_tables_last;
-      sp_cache_routines_and_add_tables_for_view(thd, thd->lex, tables->view);
+      sp_cache_routines_and_add_tables_for_view(thd, thd->lex, tables);
     }
   }
   thd->proc_info=0;

--- 1.136/mysql-test/r/view.result	2005-11-18 17:26:43 +03:00
+++ 1.137/mysql-test/r/view.result	2005-12-07 12:27:10 +03:00
@@ -1933,11 +1933,11 @@
 DROP TABLE t1;
 CHECK TABLE v1, v2, v3, v4, v5, v6;
 Table	Op	Msg_type	Msg_text
-test.v1	check	error	Table 'test.t1' doesn't exist
+test.v1	check	error	View 'test.v1' references invalid table(s) or column(s) or
function(s) or definer/invoker of view lack rights to use them
 test.v2	check	status	OK
-test.v3	check	error	Table 'test.t1' doesn't exist
+test.v3	check	error	View 'test.v3' references invalid table(s) or column(s) or
function(s) or definer/invoker of view lack rights to use them
 test.v4	check	status	OK
-test.v5	check	error	Table 'test.t1' doesn't exist
+test.v5	check	error	View 'test.v5' references invalid table(s) or column(s) or
function(s) or definer/invoker of view lack rights to use them
 test.v6	check	status	OK
 drop function f1;
 drop function f2;

--- 1.126/mysql-test/t/view.test	2005-11-18 17:26:43 +03:00
+++ 1.127/mysql-test/t/view.test	2005-12-07 12:27:10 +03:00
@@ -1744,7 +1744,6 @@
 CHECK TABLE v1, v2, v3, v4, v5, v6;
 create function f1 () returns int return (select max(col1) from t1);
 DROP TABLE t1;
-# following will show underlying table until BUG#11555 fix
 CHECK TABLE v1, v2, v3, v4, v5, v6;
 drop function f1;
 drop function f2;

--- 1.15/sql/sql_trigger.h	2005-11-10 22:24:54 +03:00
+++ 1.16/sql/sql_trigger.h	2005-12-07 12:27:11 +03:00
@@ -118,7 +118,7 @@
 
   friend class Item_trigger_field;
   friend void sp_cache_routines_and_add_tables_for_triggers(THD *thd, LEX *lex,
-                Table_triggers_list *triggers);
+                                                            TABLE_LIST *table);
 
 private:
   bool prepare_record1_accessors(TABLE *table);

--- 1.93/mysql-test/r/sp-error.result	2005-11-23 02:12:33 +03:00
+++ 1.94/mysql-test/r/sp-error.result	2005-12-07 12:27:10 +03:00
@@ -1055,3 +1055,43 @@
 mysqltest2	p1	PROCEDURE	root@localhost	0000-00-00 00:00:00	0000-00-00 00:00:00	DEFINER	
 drop database mysqltest2;
 use test;
+drop function if exists bug11555_1;
+drop function if exists bug11555_2;
+drop view if exists v1, v2, v3, v4;
+create function bug11555_1() returns int return (select max(i) from t1);
+create function bug11555_2() returns int return bug11555_1();
+create view v1 as select bug11555_1();
+ERROR 42S02: Table 'test.t1' doesn't exist
+create view v2 as select bug11555_2();
+ERROR 42S02: Table 'test.t1' doesn't exist
+create table t1 (i int);
+create view v1 as select bug11555_1();
+create view v2 as select bug11555_2();
+create view v3 as select * from v1;
+drop table t1;
+select * from v1;
+ERROR HY000: View 'test.v1' references invalid table(s) or column(s) or function(s) or
definer/invoker of view lack rights to use them
+select * from v2;
+ERROR HY000: View 'test.v2' references invalid table(s) or column(s) or function(s) or
definer/invoker of view lack rights to use them
+select * from v3;
+ERROR HY000: View 'test.v3' references invalid table(s) or column(s) or function(s) or
definer/invoker of view lack rights to use them
+create view v4 as select * from v1;
+ERROR HY000: View 'test.v1' references invalid table(s) or column(s) or function(s) or
definer/invoker of view lack rights to use them
+drop view v1, v2, v3;
+drop function bug11555_1;
+drop function bug11555_2;
+create table t1 (i int);
+create table t2 (i int);
+create trigger t1_ai after insert on t1 for each row insert into t2 values (new.i);
+create view v1 as select * from t1;
+drop table t2;
+insert into v1 values (1);
+ERROR HY000: View 'test.v1' references invalid table(s) or column(s) or function(s) or
definer/invoker of view lack rights to use them
+drop trigger t1_ai;
+create function bug11555_1() returns int return (select max(i) from t2);
+create trigger t1_ai after insert on t1 for each row set @a:=bug11555_1();
+insert into v1 values (2);
+ERROR HY000: View 'test.v1' references invalid table(s) or column(s) or function(s) or
definer/invoker of view lack rights to use them
+drop function bug11555_1;
+drop table t1;
+drop view v1;

--- 1.97/mysql-test/t/sp-error.test	2005-11-23 02:12:33 +03:00
+++ 1.98/mysql-test/t/sp-error.test	2005-12-07 12:27:10 +03:00
@@ -1519,6 +1519,67 @@
 use test;
 
 
+# BUG#11555 "Stored procedures: current SP tables locking make 
+# impossible view security". We should not expose names of tables
+# which are implicitly used by view (via stored routines/triggers).
+#
+# Note that SQL standard assumes that you simply won't be able drop table
+# and leave some objects (routines/views/triggers) which were depending on
+# it. Such objects should be dropped in advance (by default) or will be
+# dropped simultaneously with table (DROP TABLE with CASCADE clause).
+# So these tests probably should go away once we will implement standard
+# behavior.
+--disable_warnings
+drop function if exists bug11555_1;
+drop function if exists bug11555_2;
+drop view if exists v1, v2, v3, v4;
+--enable_warnings
+create function bug11555_1() returns int return (select max(i) from t1);
+create function bug11555_2() returns int return bug11555_1();
+# It is OK to report name of implicitly used table which is missing
+# when we create view.
+--error ER_NO_SUCH_TABLE
+create view v1 as select bug11555_1();
+--error ER_NO_SUCH_TABLE
+create view v2 as select bug11555_2();
+# But we should hide name of missing implicitly used table when we use view
+create table t1 (i int);
+create view v1 as select bug11555_1();
+create view v2 as select bug11555_2();
+create view v3 as select * from v1;
+drop table t1;
+--error ER_VIEW_INVALID
+select * from v1;
+--error ER_VIEW_INVALID
+select * from v2;
+--error ER_VIEW_INVALID
+select * from v3;
+# Note that creation of view which depends on broken view is yet 
+# another form of view usage.
+--error ER_VIEW_INVALID
+create view v4 as select * from v1;
+drop view v1, v2, v3;
+# We also should hide details about broken triggers which are
+# invoked for view.
+drop function bug11555_1;
+drop function bug11555_2;
+create table t1 (i int);
+create table t2 (i int);
+create trigger t1_ai after insert on t1 for each row insert into t2 values (new.i);
+create view v1 as select * from t1;
+drop table t2;
+--error ER_VIEW_INVALID
+insert into v1 values (1);
+drop trigger t1_ai;
+create function bug11555_1() returns int return (select max(i) from t2);
+create trigger t1_ai after insert on t1 for each row set @a:=bug11555_1();
+--error ER_VIEW_INVALID
+insert into v1 values (2);
+drop function bug11555_1;
+drop table t1;
+drop view v1;
+
+
 # BUG#NNNN: New bug synopsis
 #
 #--disable_warnings

--- 1.102/sql/sp.cc	2005-11-23 03:49:40 +03:00
+++ 1.103/sql/sp.cc	2005-12-07 12:27:10 +03:00
@@ -1199,6 +1199,12 @@
     for LEX::sroutine/sroutine_list and sp_head::m_sroutines.
   */
   Sroutine_hash_entry *next;
+  /*
+    Uppermost view which directly or indirectly uses this routine.
+    0 if routine is not used in view. Note that it also can be 0 if
+    statement uses routine both via view and directly.
+  */
+  TABLE_LIST *belong_to_view;
 };
 
 
@@ -1253,9 +1259,11 @@
 
   SYNOPSIS
     add_used_routine()
-      lex     - LEX representing statement
-      arena   - arena in which memory for new element will be allocated
-      key     - key for the hash representing set
+      lex             LEX representing statement
+      arena           Arena in which memory for new element will be allocated
+      key             Key for the hash representing set
+      belong_to_view  Uppermost view which uses this routine
+                      (0 if routine is not used by view)
 
   NOTES
     Will also add element to end of 'LEX::sroutines_list' list.
@@ -1278,7 +1286,8 @@
 */
 
 static bool add_used_routine(LEX *lex, Query_arena *arena,
-                             const LEX_STRING *key)
+                             const LEX_STRING *key,
+                             TABLE_LIST *belong_to_view)
 {
   if (!hash_search(&lex->sroutines, (byte *)key->str, key->length))
   {
@@ -1292,6 +1301,7 @@
     memcpy(rn->key.str, key->str, key->length);
     my_hash_insert(&lex->sroutines, (byte *)rn);
     lex->sroutines_list.link_in_list((byte *)rn, (byte **)&rn->next);
+    rn->belong_to_view= belong_to_view;
     return TRUE;
   }
   return FALSE;
@@ -1322,7 +1332,7 @@
                          sp_name *rt, char rt_type)
 {
   rt->set_routine_type(rt_type);
-  (void)add_used_routine(lex, arena, &rt->m_sroutines_key);
+  (void)add_used_routine(lex, arena, &rt->m_sroutines_key, 0);
   lex->sroutines_list_own_last= lex->sroutines_list.next;
   lex->sroutines_list_own_elements= lex->sroutines_list.elements;
 }
@@ -1392,20 +1402,23 @@
 
   SYNOPSIS
     sp_update_stmt_used_routines()
-      thd - thread context
-      lex - LEX representing statement
-      src - hash representing set from which routines will be added
+      thd             Thread context
+      lex             LEX representing statement
+      src             Hash representing set from which routines will be added
+      belong_to_view  Uppermost view which uses these routines, 0 if none
 
   NOTE
     It will also add elements to end of 'LEX::sroutines_list' list.
 */
 
-static void sp_update_stmt_used_routines(THD *thd, LEX *lex, HASH *src)
+static void
+sp_update_stmt_used_routines(THD *thd, LEX *lex, HASH *src,
+                             TABLE_LIST *belong_to_view)
 {
   for (uint i=0 ; i < src->records ; i++)
   {
     Sroutine_hash_entry *rt= (Sroutine_hash_entry *)hash_element(src, i);
-    (void)add_used_routine(lex, thd->stmt_arena, &rt->key);
+    (void)add_used_routine(lex, thd->stmt_arena, &rt->key, belong_to_view);
   }
 }
 
@@ -1416,19 +1429,21 @@
 
   SYNOPSIS
     sp_update_stmt_used_routines()
-      thd  Thread context
-      lex  LEX representing statement
-      src  List representing set from which routines will be added
+      thd             Thread context
+      lex             LEX representing statement
+      src             List representing set from which routines will be added
+      belong_to_view  Uppermost view which uses these routines, 0 if none
 
   NOTE
     It will also add elements to end of 'LEX::sroutines_list' list.
 */
 
-static void sp_update_stmt_used_routines(THD *thd, LEX *lex, SQL_LIST *src)
+static void sp_update_stmt_used_routines(THD *thd, LEX *lex, SQL_LIST *src,
+                                         TABLE_LIST *belong_to_view)
 {
   for (Sroutine_hash_entry *rt= (Sroutine_hash_entry *)src->first;
        rt; rt= rt->next)
-    (void)add_used_routine(lex, thd->stmt_arena, &rt->key);
+    (void)add_used_routine(lex, thd->stmt_arena, &rt->key, belong_to_view);
 }
 
 
@@ -1493,8 +1508,11 @@
     {
       if (!(first && first_no_prelock))
       {
-        sp_update_stmt_used_routines(thd, lex, &sp->m_sroutines);
-        result|= sp->add_used_tables_to_table_list(thd,
&lex->query_tables_last);
+        sp_update_stmt_used_routines(thd, lex, &sp->m_sroutines,
+                                     rt->belong_to_view);
+        result|= sp->add_used_tables_to_table_list(thd,
+                                                   &lex->query_tables_last,
+                                                   rt->belong_to_view);
       }
     }
     first= FALSE;
@@ -1536,17 +1554,18 @@
 
   SYNOPSIS
     sp_cache_routines_and_add_tables_for_view()
-      thd     - thread context
-      lex     - LEX representing statement
-      aux_lex - LEX representing view
+      thd   Thread context
+      lex   LEX representing statement
+      view  Table list element representing view
 */
 
 void
-sp_cache_routines_and_add_tables_for_view(THD *thd, LEX *lex, LEX *aux_lex)
+sp_cache_routines_and_add_tables_for_view(THD *thd, LEX *lex, TABLE_LIST *view)
 {
   Sroutine_hash_entry **last_cached_routine_ptr=
                           (Sroutine_hash_entry **)lex->sroutines_list.next;
-  sp_update_stmt_used_routines(thd, lex, &aux_lex->sroutines_list);
+  sp_update_stmt_used_routines(thd, lex, &view->view->sroutines_list,
+                               view->top_table());
   (void)sp_cache_routines_and_add_tables_aux(thd, lex, 
                                              *last_cached_routine_ptr, FALSE);
 }
@@ -1559,16 +1578,18 @@
 
   SYNOPSIS
     sp_cache_routines_and_add_tables_for_triggers()
-      thd      - thread context
-      lex      - LEX respresenting statement
-      triggers - triggers of the table
+      thd    thread context
+      lex    LEX respresenting statement
+      table  Table list element for table with trigger
 */
 
 void
 sp_cache_routines_and_add_tables_for_triggers(THD *thd, LEX *lex,
-                                              Table_triggers_list *triggers)
+                                              TABLE_LIST *table)
 {
-  if (add_used_routine(lex, thd->stmt_arena, &triggers->sroutines_key))
+  Table_triggers_list *triggers= table->table->triggers;
+  if (add_used_routine(lex, thd->stmt_arena, &triggers->sroutines_key,
+                       table->belong_to_view))
   {
     Sroutine_hash_entry **last_cached_routine_ptr=
                             (Sroutine_hash_entry **)lex->sroutines_list.next;
@@ -1578,10 +1599,12 @@
       {
         if (triggers->bodies[i][j])
         {
-          (void)triggers->bodies[i][j]->add_used_tables_to_table_list(thd,
-                                          &lex->query_tables_last);
+          (void)triggers->bodies[i][j]->
+                add_used_tables_to_table_list(thd, &lex->query_tables_last,
+                                              table->belong_to_view);
           sp_update_stmt_used_routines(thd, lex,
-                                       &triggers->bodies[i][j]->m_sroutines);
+                                       &triggers->bodies[i][j]->m_sroutines,
+                                       table->belong_to_view);
         }
       }
     }

--- 1.30/sql/sp.h	2005-11-23 02:11:08 +03:00
+++ 1.31/sql/sp.h	2005-12-07 12:27:10 +03:00
@@ -87,9 +87,9 @@
 bool sp_cache_routines_and_add_tables(THD *thd, LEX *lex, 
                                       bool first_no_prelock);
 void sp_cache_routines_and_add_tables_for_view(THD *thd, LEX *lex,
-                                               LEX *aux_lex);
+                                               TABLE_LIST *view);
 void sp_cache_routines_and_add_tables_for_triggers(THD *thd, LEX *lex,
-                                         Table_triggers_list *triggers);
+                                                   TABLE_LIST *table);
 
 extern "C" byte* sp_sroutine_key(const byte *ptr, uint *plen, my_bool first);
 

--- 1.202/sql/sp_head.cc	2005-11-23 02:12:35 +03:00
+++ 1.203/sql/sp_head.cc	2005-12-07 12:27:10 +03:00
@@ -3101,10 +3101,12 @@
 
   SYNOPSIS
     add_used_tables_to_table_list()
-      thd                   - thread context
-      query_tables_last_ptr - (in/out) pointer the next_global member of last
-                              element of the list where tables will be added
-                              (or to its root).
+      thd                    [in]     Thread context
+      query_tables_last_ptr  [in/out] Pointer to the next_global member of
+                                      last element of the list where tables
+                                      will be added (or to its root).
+      belong_to_view         [in]     Uppermost view which uses this routine,
+                                      0 if none.
 
   DESCRIPTION
     Converts multi-set of tables used by this routine to table list and adds
@@ -3119,7 +3121,8 @@
 
 bool
 sp_head::add_used_tables_to_table_list(THD *thd,
-                                       TABLE_LIST ***query_tables_last_ptr)
+                                       TABLE_LIST ***query_tables_last_ptr,
+                                       TABLE_LIST *belong_to_view)
 {
   uint i;
   Query_arena *arena, backup;
@@ -3162,6 +3165,7 @@
       table->lock_type= stab->lock_type;
       table->cacheable_table= 1;
       table->prelocking_placeholder= 1;
+      table->belong_to_view= belong_to_view;
 
       /* Everyting else should be zeroed */
 

--- 1.77/sql/sp_head.h	2005-11-23 02:12:35 +03:00
+++ 1.78/sql/sp_head.h	2005-12-07 12:27:11 +03:00
@@ -308,7 +308,8 @@
 
   /* Add tables used by routine to the table list. */
   bool add_used_tables_to_table_list(THD *thd,
-                                     TABLE_LIST ***query_tables_last_ptr);
+                                     TABLE_LIST ***query_tables_last_ptr,
+                                     TABLE_LIST *belong_to_view);
 
   /*
     Check if this stored routine contains statements disallowed