summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
Diffstat
-rw-r--r--mysql-test/r/sp-error.result40
-rw-r--r--mysql-test/r/view.result6
-rw-r--r--mysql-test/t/sp-error.test61
-rw-r--r--mysql-test/t/view.test1
-rw-r--r--sql-common/client.c2
-rw-r--r--sql/sp.cc85
-rw-r--r--sql/sp.h9
-rw-r--r--sql/sp_head.cc14
-rw-r--r--sql/sp_head.h3
-rw-r--r--sql/sql_base.cc5
-rw-r--r--sql/sql_trigger.h2
-rw-r--r--tests/mysql_client_test.c35
12 files changed, 212 insertions, 51 deletions
diff --git a/mysql-test/r/sp-error.result b/mysql-test/r/sp-error.result
index b384a1c1649..d7bed7e88a7 100644
--- a/mysql-test/r/sp-error.result
+++ b/mysql-test/r/sp-error.result
@@ -1088,3 +1088,43 @@ ERROR 0A000: Not allowed to return a result set from a function
drop table t1|
drop procedure bug13012_1|
drop function bug13012_2|
+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;
diff --git a/mysql-test/r/view.result b/mysql-test/r/view.result
index f0305544eb3..3d209e91236 100644
--- a/mysql-test/r/view.result
+++ b/mysql-test/r/view.result
@@ -1933,11 +1933,11 @@ create function f1 () returns int return (select max(col1) from t1);
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;
diff --git a/mysql-test/t/sp-error.test b/mysql-test/t/sp-error.test
index b6c7d5476e7..cf8f8dfc79c 100644
--- a/mysql-test/t/sp-error.test
+++ b/mysql-test/t/sp-error.test
@@ -1556,6 +1556,67 @@ drop procedure bug13012_1|
drop function bug13012_2|
delimiter ;|
+# 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
diff --git a/mysql-test/t/view.test b/mysql-test/t/view.test
index 9c73e22eb7e..3a0e33f6aae 100644
--- a/mysql-test/t/view.test
+++ b/mysql-test/t/view.test
@@ -1744,7 +1744,6 @@ drop function f1;
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;
diff --git a/sql-common/client.c b/sql-common/client.c
index 976d59d83a4..4c2debd41ff 100644
--- a/sql-common/client.c
+++ b/sql-common/client.c
@@ -715,6 +715,7 @@ void free_old_query(MYSQL *mysql)
init_alloc_root(&mysql->field_alloc,8192,0); /* Assume rowlength < 8192 */
mysql->fields= 0;
mysql->field_count= 0; /* For API */
+ mysql->warning_count= 0;
mysql->info= 0;
DBUG_VOID_RETURN;
}
@@ -2484,7 +2485,6 @@ get_info:
DBUG_RETURN(1);
mysql->status= MYSQL_STATUS_GET_RESULT;
mysql->field_count= (uint) field_count;
- mysql->warning_count= 0;
DBUG_PRINT("exit",("ok"));
DBUG_RETURN(0);
}
diff --git a/sql/sp.cc b/sql/sp.cc
index 9185d91d544..81164131910 100644
--- a/sql/sp.cc
+++ b/sql/sp.cc
@@ -1200,6 +1200,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;
};
@@ -1254,9 +1260,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.
@@ -1279,7 +1287,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))
{
@@ -1293,6 +1302,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;
@@ -1323,7 +1333,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;
}
@@ -1393,20 +1403,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);
}
}
@@ -1417,19 +1430,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);
}
@@ -1534,9 +1549,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);
+ sp_update_stmt_used_routines(thd, lex, &sp->m_sroutines,
+ rt->belong_to_view);
tabschnd|=
- sp->add_used_tables_to_table_list(thd, &lex->query_tables_last);
+ sp->add_used_tables_to_table_list(thd, &lex->query_tables_last,
+ rt->belong_to_view);
}
}
first= FALSE;
@@ -1582,21 +1599,22 @@ 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
+
RETURN VALUE
0 - success
non-0 - failure
*/
int
-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());
return sp_cache_routines_and_add_tables_aux(thd, lex,
*last_cached_routine_ptr, FALSE,
NULL);
@@ -1610,9 +1628,9 @@ 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
RETURN VALUE
0 - success
@@ -1621,11 +1639,12 @@ sp_cache_routines_and_add_tables_for_view(THD *thd, LEX *lex, LEX *aux_lex)
int
sp_cache_routines_and_add_tables_for_triggers(THD *thd, LEX *lex,
- Table_triggers_list *triggers)
+ TABLE_LIST *table)
{
int ret= 0;
-
- 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;
@@ -1635,10 +1654,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 235eb5cba37..53343e0fb25 100644
--- a/sql/sp.h
+++ b/sql/sp.h
@@ -84,12 +84,13 @@ void sp_add_used_routine(LEX *lex, Query_arena *arena,
sp_name *rt, char rt_type);
void sp_remove_not_own_routines(LEX *lex);
void sp_update_sp_used_routines(HASH *dst, HASH *src);
-int sp_cache_routines_and_add_tables(THD *thd, LEX *lex,
- bool first_no_prelock, bool *tabs_changed);
+int sp_cache_routines_and_add_tables(THD *thd, LEX *lex,
+ bool first_no_prelock,
+ bool *tabs_changed);
int sp_cache_routines_and_add_tables_for_view(THD *thd, LEX *lex,
- LEX *aux_lex);
+ TABLE_LIST *view);
int 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 956de7d9c36..47f9c362b5c 100644
--- a/sql/sp_head.cc
+++ b/sql/sp_head.cc
@@ -3244,10 +3244,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
@@ -3262,7 +3264,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;
@@ -3305,6 +3308,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 a487ffc52fc..d99b600f6b2 100644
--- a/sql/sp_head.h
+++ b/sql/sp_head.h
@@ -310,7 +310,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 155f28a7ddf..f7494b694c5 100644
--- a/sql/sql_base.cc
+++ b/sql/sql_base.cc
@@ -2760,7 +2760,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;
if (sp_cache_routines_and_add_tables_for_triggers(thd, thd->lex,
- tables->table->triggers))
+ tables))
{
/*
Serious error during reading stored routines from mysql.proc table.
@@ -2790,8 +2790,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;
- if (sp_cache_routines_and_add_tables_for_view(thd, thd->lex,
- tables->view))
+ if (sp_cache_routines_and_add_tables_for_view(thd, thd->lex, tables))
{
/*
Serious error during reading stored routines from mysql.proc table.
diff --git a/sql/sql_trigger.h b/sql/sql_trigger.h
index 143f9f4d5bd..51002683897 100644
--- a/sql/sql_trigger.h
+++ b/sql/sql_trigger.h
@@ -118,7 +118,7 @@ public:
friend class Item_trigger_field;
friend int 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);
diff --git a/tests/mysql_client_test.c b/tests/mysql_client_test.c
index 36ab8094e5b..216961b3a80 100644
--- a/tests/mysql_client_test.c
+++ b/tests/mysql_client_test.c
@@ -14590,6 +14590,40 @@ static void test_bug14845()
myquery(rc);
}
+
+/*
+ Bug #15510: mysql_warning_count returns 0 after mysql_stmt_fetch which
+ should warn
+*/
+static void test_bug15510()
+{
+ MYSQL_STMT *stmt;
+ MYSQL_RES *res;
+ int rc;
+ const char *query= "select 1 from dual where 1/0";
+
+ myheader("test_bug15510");
+
+ rc= mysql_query(mysql, "set @@sql_mode='ERROR_FOR_DIVISION_BY_ZERO'");
+ myquery(rc);
+
+ stmt= mysql_stmt_init(mysql);
+
+ rc= mysql_stmt_prepare(stmt, query, strlen(query));
+ check_execute(stmt, rc);
+
+ rc= mysql_stmt_execute(stmt);
+ check_execute(stmt, rc);
+
+ rc= mysql_stmt_fetch(stmt);
+ DIE_UNLESS(mysql_warning_count(mysql));
+
+ /* Cleanup */
+ mysql_stmt_close(stmt);
+ rc= mysql_query(mysql, "set @@sql_mode=''");
+ myquery(rc);
+}
+
/*
Read and parse arguments and MySQL options from my.cnf
*/
@@ -14849,6 +14883,7 @@ static struct my_tests_st my_tests[]= {
{ "test_bug13488", test_bug13488 },
{ "test_bug13524", test_bug13524 },
{ "test_bug14845", test_bug14845 },
+ { "test_bug15510", test_bug15510},
{ 0, 0 }
};
View Lorry Controller Status