From 44d2934f0bf11d86ab7491142a4e5ca9d16f3259 Mon Sep 17 00:00:00 2001 From: unknown Date: Tue, 15 Jun 2004 15:42:28 +0200 Subject: Fixed BUG#3339: Stored procedures in nonexistent schemas are uncallable. Also added some related security tests and corrected related error messages. mysql-test/r/sp-error.result: New test case for BUG#3339, and updated results for other modified error messages. mysql-test/r/sp-security.result: Added tests for creating procedures in another database with and wihout access rights. mysql-test/t/sp-error.test: New test case for BUG#3339. mysql-test/t/sp-security.test: Added tests for creating procedures in another database with and wihout access rights. sql/sp.cc: Check existance (and access rights) for database when creating a stored routine. sql/sp.h: New error return value for sp_create_* functions, for non existing database. sql/sql_parse.cc: Check error return for create stored routine (non existing database), and corrected the error output for some other commands. (Use qualified name, not just name.) --- mysql-test/r/sp-error.result | 14 ++++++++------ mysql-test/r/sp-security.result | 10 ++++++++++ mysql-test/t/sp-error.test | 8 +++++++- mysql-test/t/sp-security.test | 19 +++++++++++++++++++ sql/sp.cc | 12 ++++++++++++ sql/sp.h | 1 + sql/sql_parse.cc | 15 ++++++++++----- 7 files changed, 67 insertions(+), 12 deletions(-) diff --git a/mysql-test/r/sp-error.result b/mysql-test/r/sp-error.result index 0542b32582b..8d92765d1dd 100644 --- a/mysql-test/r/sp-error.result +++ b/mysql-test/r/sp-error.result @@ -33,15 +33,15 @@ ERROR 42000: FUNCTION func1 already exists drop procedure proc1| drop function func1| alter procedure foo| -ERROR 42000: PROCEDURE foo does not exist +ERROR 42000: PROCEDURE test.foo does not exist alter function foo| -ERROR 42000: FUNCTION foo does not exist +ERROR 42000: FUNCTION test.foo does not exist drop procedure foo| -ERROR 42000: PROCEDURE foo does not exist +ERROR 42000: PROCEDURE test.foo does not exist drop function foo| -ERROR 42000: FUNCTION foo does not exist +ERROR 42000: FUNCTION test.foo does not exist call foo()| -ERROR 42000: PROCEDURE foo does not exist +ERROR 42000: PROCEDURE test.foo does not exist drop procedure if exists foo| Warnings: Warning 1298 PROCEDURE foo does not exist @@ -193,7 +193,7 @@ call p()| ERROR 24000: Cursor is not open drop procedure p| alter procedure bar3 sql security invoker| -ERROR 42000: PROCEDURE bar3 does not exist +ERROR 42000: PROCEDURE test.bar3 does not exist alter procedure bar3 name AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA| ERROR 42000: Identifier name 'AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA' is too long @@ -395,4 +395,6 @@ select @x| 0 drop procedure bug3279| drop table t3| +create procedure nodb.bug3339() begin end| +ERROR 42000: Unknown database 'nodb' drop table t1| diff --git a/mysql-test/r/sp-security.result b/mysql-test/r/sp-security.result index 60adad0181c..f78b41764b9 100644 --- a/mysql-test/r/sp-security.result +++ b/mysql-test/r/sp-security.result @@ -3,6 +3,8 @@ grant usage on *.* to user1@localhost; flush privileges; drop database if exists db1_secret; create database db1_secret; +create procedure db1_secret.dummy() begin end; +drop procedure db1_secret.dummy; use db1_secret; create table t1 ( u varchar(64), i int ); create procedure stamp(i int) @@ -27,12 +29,20 @@ db1_secret.db() db1_secret select * from db1_secret.t1; ERROR 42000: Access denied for user: 'user1'@'localhost' to database 'db1_secret' +create procedure db1_secret.dummy() begin end; +ERROR 42000: Unknown database 'db1_secret' +drop procedure db1_secret.dummy; +ERROR 42000: PROCEDURE db1_secret.dummy does not exist call db1_secret.stamp(3); select db1_secret.db(); db1_secret.db() db1_secret select * from db1_secret.t1; ERROR 42000: Access denied for user: ''@'localhost' to database 'db1_secret' +create procedure db1_secret.dummy() begin end; +ERROR 42000: Unknown database 'db1_secret' +drop procedure db1_secret.dummy; +ERROR 42000: PROCEDURE db1_secret.dummy does not exist select * from t1; u i root@localhost 1 diff --git a/mysql-test/t/sp-error.test b/mysql-test/t/sp-error.test index 6e4cd1a62b5..785c2aafa85 100644 --- a/mysql-test/t/sp-error.test +++ b/mysql-test/t/sp-error.test @@ -517,7 +517,6 @@ end case| call bug3287(2)| drop procedure bug3287| - # # BUG#3297 # @@ -548,6 +547,13 @@ select @x| drop procedure bug3279| drop table t3| +# +# BUG#3339 +# +--error 1049 +create procedure nodb.bug3339() begin end| + + drop table t1| delimiter ;| diff --git a/mysql-test/t/sp-security.test b/mysql-test/t/sp-security.test index 8fc51357bc4..046a9018472 100644 --- a/mysql-test/t/sp-security.test +++ b/mysql-test/t/sp-security.test @@ -17,6 +17,10 @@ drop database if exists db1_secret; # Create our secret database create database db1_secret; +# Can create a procedure in other db +create procedure db1_secret.dummy() begin end; +drop procedure db1_secret.dummy; + use db1_secret; create table t1 ( u varchar(64), i int ); @@ -39,6 +43,7 @@ select db(); connect (con2user1,localhost,user1,,); connect (con3anon,localhost,anon,,); + # # User1 can # @@ -52,6 +57,13 @@ select db1_secret.db(); --error 1044 select * from db1_secret.t1; +# ...and not this +--error 1049 +create procedure db1_secret.dummy() begin end; +--error 1298 +drop procedure db1_secret.dummy; + + # # Anonymous can # @@ -65,6 +77,13 @@ select db1_secret.db(); --error 1044 select * from db1_secret.t1; +# ...and not this +--error 1049 +create procedure db1_secret.dummy() begin end; +--error 1298 +drop procedure db1_secret.dummy; + + # # Check it out # diff --git a/sql/sp.cc b/sql/sp.cc index b77f60a32ad..d70da1b1421 100644 --- a/sql/sp.cc +++ b/sql/sp.cc @@ -314,9 +314,19 @@ db_create_routine(THD *thd, int type, sp_head *sp) TABLE *table; TABLE_LIST tables; char definer[HOSTNAME_LENGTH+USERNAME_LENGTH+2]; + char olddb[128]; + bool dbchanged; DBUG_ENTER("db_create_routine"); DBUG_PRINT("enter", ("type: %d name: %*s",type,sp->m_name.length,sp->m_name.str)); + dbchanged= FALSE; + if ((ret= sp_use_new_db(thd, sp->m_db.str, olddb, sizeof(olddb), + 0, &dbchanged))) + { + ret= SP_NO_DB_ERROR; + goto done; + } + memset(&tables, 0, sizeof(tables)); tables.db= (char*)"mysql"; tables.real_name= tables.alias= (char*)"proc"; @@ -370,6 +380,8 @@ db_create_routine(THD *thd, int type, sp_head *sp) done: close_thread_tables(thd); + if (dbchanged) + (void)sp_change_db(thd, olddb, 1); DBUG_RETURN(ret); } diff --git a/sql/sp.h b/sql/sp.h index a6f76876f33..783de2fe7ee 100644 --- a/sql/sp.h +++ b/sql/sp.h @@ -27,6 +27,7 @@ #define SP_GET_FIELD_FAILED -5 #define SP_PARSE_ERROR -6 #define SP_INTERNAL_ERROR -7 +#define SP_NO_DB_ERROR -8 /* Drop all routines in database 'db' */ int diff --git a/sql/sql_parse.cc b/sql/sql_parse.cc index 6e04d00a886..8a2195bae23 100644 --- a/sql/sql_parse.cc +++ b/sql/sql_parse.cc @@ -3410,6 +3410,11 @@ unsent_create_error: delete lex->sphead; lex->sphead= 0; goto error; + case SP_NO_DB_ERROR: + net_printf(thd, ER_BAD_DB_ERROR, lex->sphead->m_db); + delete lex->sphead; + lex->sphead= 0; + goto error; default: net_printf(thd, ER_SP_STORE_FAILED, SP_TYPE_STRING(lex), name); delete lex->sphead; @@ -3425,7 +3430,7 @@ unsent_create_error: if (!(sp= sp_find_procedure(thd, lex->spname))) { net_printf(thd, ER_SP_DOES_NOT_EXIST, "PROCEDURE", - lex->spname->m_name.str); + lex->spname->m_qname.str); goto error; } else @@ -3519,11 +3524,11 @@ unsent_create_error: break; case SP_KEY_NOT_FOUND: net_printf(thd, ER_SP_DOES_NOT_EXIST, SP_COM_STRING(lex), - lex->spname->m_name.str); + lex->spname->m_qname.str); goto error; default: net_printf(thd, ER_SP_CANT_ALTER, SP_COM_STRING(lex), - lex->spname->m_name.str); + lex->spname->m_qname.str); goto error; } break; @@ -3570,11 +3575,11 @@ unsent_create_error: break; } net_printf(thd, ER_SP_DOES_NOT_EXIST, SP_COM_STRING(lex), - lex->spname->m_name.str); + lex->spname->m_qname.str); goto error; default: net_printf(thd, ER_SP_DROP_FAILED, SP_COM_STRING(lex), - lex->spname->m_name.str); + lex->spname->m_qname.str); goto error; } break; -- cgit v1.2.1