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.


mysql-test/r/sp-error.result:
  Added test for bug #11555 "Stored procedures: current SP tables locking make 
  impossible view security".
mysql-test/r/view.result:
  We should not expose tables which are expicitly/implicitly used in view in
  check table statement.
mysql-test/t/sp-error.test:
  Added test for bug #11555 "Stored procedures: current SP tables locking make 
  impossible view security".
mysql-test/t/view.test:
  Removed comment obsoleted by bugfix.
sql/sp.cc:
  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/sp.h:
  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_head.cc:
  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.h:
  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/sql_base.cc:
  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/sql_trigger.h:
  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.

6 files changed, 69 insertions, 42 deletions

diff --git a/sql/sp.cc b/sql/sp.cc
index 8991cc78b5e..ff54120f54d 100644
--- a/sql/sp.cc
+++ b/sql/sp.cc
@@ -1199,6 +1199,12 @@ struct Sroutine_hash_entry
     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 @@ void sp_get_prelocking_info(THD *thd, bool *need_prelocking,
 
   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 @@ void sp_get_prelocking_info(THD *thd, bool *need_prelocking,
 */
 
 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 @@ static bool add_used_routine(LEX *lex, Query_arena *arena,
     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 @@ void sp_add_used_routine(LEX *lex, Query_arena *arena,
                          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 @@ void sp_update_sp_used_routines(HASH *dst, HASH *src)
 
   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 @@ static void sp_update_stmt_used_routines(THD *thd, LEX *lex, HASH *src)
 
   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 @@ sp_cache_routines_and_add_tables_aux(THD *thd, LEX *lex,
     {
       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 @@ sp_cache_routines_and_add_tables(THD *thd, LEX *lex, bool first_no_prelock)
 
   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 @@ sp_cache_routines_and_add_tables_for_view(THD *thd, LEX *lex, LEX *aux_lex)
 
   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 @@ sp_cache_routines_and_add_tables_for_triggers(THD *thd, LEX *lex,
       {
         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);
         }
       }
     }
diff --git a/sql/sp.h b/sql/sp.h
index 7f314b8903e..04e03596c09 100644
--- a/sql/sp.h
+++ b/sql/sp.h
@@ -87,9 +87,9 @@ void sp_update_sp_used_routines(HASH *dst, HASH *src);
 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);
 
diff --git a/sql/sp_head.cc b/sql/sp_head.cc
index a6e88c08789..39f09b74e64 100644
--- a/sql/sp_head.cc
+++ b/sql/sp_head.cc
@@ -3101,10 +3101,12 @@ sp_head::merge_table_list(THD *thd, TABLE_LIST *table, LEX *lex_for_tmp_check)
 
   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 @@ sp_head::merge_table_list(THD *thd, TABLE_LIST *table, LEX *lex_for_tmp_check)
 
 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 @@ sp_head::add_used_tables_to_table_list(THD *thd,
       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 */
 
diff --git a/sql/sp_head.h b/sql/sp_head.h
index 6334bca0fc6..734442724fb 100644
--- a/sql/sp_head.h
+++ b/sql/sp_head.h
@@ -308,7 +308,8 @@ public:
 
   /* 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
diff --git a/sql/sql_base.cc b/sql/sql_base.cc
index 39e15675e47..1062c4330b0 100644
--- a/sql/sql_base.cc
+++ b/sql/sql_base.cc
@@ -2117,8 +2117,7 @@ int open_tables(THD *thd, TABLE_LIST **start, uint *counter, uint flags)
       {
         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 @@ process_view_routines:
       /* 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;
diff --git a/sql/sql_trigger.h b/sql/sql_trigger.h
index 6be42d7b868..205e08b0f85 100644
--- a/sql/sql_trigger.h
+++ b/sql/sql_trigger.h
@@ -118,7 +118,7 @@ public:
 
   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);