| List: | Internals | « Previous MessageNext Message » | |
| From: | Alexander Nozdrin | Date: | November 10 2005 8:25pm |
| Subject: | bk commit into 5.0 tree (anozdrin:1.1960) | ||
| View as plain text | |||
Below is the list of changes that have just been committed into a local 5.0 repository of alik. When alik 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.1960 05/11/10 22:25:03 anozdrin@stripped +32 -0 WL#2818 (Add creator to the trigger definition for privilege checks on trigger activation) mysql-test/t/trigger-grant.test 1.1 05/11/10 22:24:56 anozdrin@stripped +475 -0 Test for WL#2818 -- check that DEFINER support in triggers works properly mysql-test/t/trigger-grant.test 1.0 05/11/10 22:24:56 anozdrin@stripped +0 -0 BitKeeper file /home/alik/MySQL/devel/5.0-wl2818/mysql-test/t/trigger-grant.test mysql-test/t/trigger-compat.test 1.1 05/11/10 22:24:55 anozdrin@stripped +83 -0 Triggers backward compatibility test: check that the server still can load triggers w/o definer attribute and modify tables with such triggers (add a new trigger, etc). mysql-test/r/trigger-grant.result 1.1 05/11/10 22:24:55 anozdrin@stripped +238 -0 Result file of the test for WL#2818. mysql-test/t/trigger-compat.test 1.0 05/11/10 22:24:55 anozdrin@stripped +0 -0 BitKeeper file /home/alik/MySQL/devel/5.0-wl2818/mysql-test/t/trigger-compat.test mysql-test/r/trigger-grant.result 1.0 05/11/10 22:24:55 anozdrin@stripped +0 -0 BitKeeper file /home/alik/MySQL/devel/5.0-wl2818/mysql-test/r/trigger-grant.result mysql-test/r/trigger-compat.result 1.1 05/11/10 22:24:55 anozdrin@stripped +40 -0 Result file for triggers backward compatibility test. sql/sql_yacc.yy 1.440 05/11/10 22:24:54 anozdrin@stripped +213 -154 Add support for DEFINER-clause in CREATE TRIGGER statement. Since CREATE TRIGGER and CREATE VIEW can be similar at the start, yacc is unable to distinguish between them. So, had to modify both statements in order to make it parsable by yacc. sql/sql_view.cc 1.76 05/11/10 22:24:54 anozdrin@stripped +10 -11 Rename create_view_definer to definer. sql/sql_trigger.h 1.15 05/11/10 22:24:54 anozdrin@stripped +18 -2 Add DEFINER support for triggers. sql/sql_trigger.cc 1.31 05/11/10 22:24:54 anozdrin@stripped +277 -34 Add DEFINER support for triggers. sql/sql_show.cc 1.294 05/11/10 22:24:54 anozdrin@stripped +38 -11 Add DEFINER column. sql/sql_parse.cc 1.510 05/11/10 22:24:54 anozdrin@stripped +65 -16 - Add a new check: exit from the cycle if the table is NULL; - Implement definer-related functions. mysql-test/r/trigger-compat.result 1.0 05/11/10 22:24:54 anozdrin@stripped +0 -0 BitKeeper file /home/alik/MySQL/devel/5.0-wl2818/mysql-test/r/trigger-compat.result sql/sql_lex.h 1.207 05/11/10 22:24:53 anozdrin@stripped +14 -1 - Rename create_view_definer to definer, since it is used for views and triggers; - Change st_lex_user to LEX_USER, since st_lex_user is a structure. So, formally, it should be "struct st_lex_user", which is longer than just LEX_USER; - Add trigger_definition_begin. sql/sql_acl.cc 1.180 05/11/10 22:24:52 anozdrin@stripped +1 -1 Add a new check: exit from the cycle if the table is NULL. sql/sp_head.h 1.74 05/11/10 22:24:52 anozdrin@stripped +3 -2 set_info() was split into set_info() and set_definer(). sql/sp_head.cc 1.194 05/11/10 22:24:52 anozdrin@stripped +29 -13 set_info() was split into set_info() and set_definer(). sql/sp.cc 1.97 05/11/10 22:24:52 anozdrin@stripped +2 -2 set_info() was split into set_info() and set_definer(). sql/share/errmsg.txt 1.55 05/11/10 22:24:52 anozdrin@stripped +6 -4 - Rename ER_NO_VIEW_USER to ER_MALFORMED_DEFINER in order to be shared for view and trigger implementations; - Fix a typo; - Add a new error code for trigger warning. sql/mysql_priv.h 1.366 05/11/10 22:24:52 anozdrin@stripped +6 -1 A try to minimize copy&paste: - introduce operations to be used from sql_yacc.yy; - introduce an operation to be used from trigger and view processing code. sql/item_func.cc 1.266 05/11/10 22:24:52 anozdrin@stripped +1 -1 Fix typo in comments. mysql-test/t/view_grant.test 1.7 05/11/10 22:24:51 anozdrin@stripped +1 -1 Error tag has been changed. mysql-test/t/view.test 1.122 05/11/10 22:24:51 anozdrin@stripped +1 -1 Error tag has been renamed. mysql-test/t/skip_grants.test 1.6 05/11/10 22:24:51 anozdrin@stripped +1 -1 Error tag has been renamed. mysql-test/t/rpl_trigger.test 1.3 05/11/10 22:24:51 anozdrin@stripped +23 -0 Add tests for new column in information schema. mysql-test/t/mysqldump.test 1.74 05/11/10 22:24:51 anozdrin@stripped +1 -0 Drop created procedure to not affect further tests. mysql-test/r/view_grant.result 1.7 05/11/10 22:24:51 anozdrin@stripped +1 -1 Error messages have been changed. mysql-test/r/view.result 1.132 05/11/10 22:24:51 anozdrin@stripped +2 -2 Error messages have been changed. mysql-test/r/trigger.result 1.21 05/11/10 22:24:51 anozdrin@stripped +5 -5 Added DEFINER column. mysql-test/r/skip_grants.result 1.6 05/11/10 22:24:51 anozdrin@stripped +1 -1 Error message has been changed. mysql-test/r/rpl_trigger.result 1.2 05/11/10 22:24:51 anozdrin@stripped +16 -0 Results for new test cases were added. mysql-test/r/rpl_sp.result 1.10 05/11/10 22:24:51 anozdrin@stripped +1 -1 Update result file: a new clause DEFINER has been added to CREATE TRIGGER statement. mysql-test/r/rpl_ddl.result 1.9 05/11/10 22:24:51 anozdrin@stripped +6 -6 Update result file: a new column DEFINER has been added to INFORMATION_SCHEMA.TRIGGERS. mysql-test/r/mysqldump.result 1.81 05/11/10 22:24:51 anozdrin@stripped +12 -11 Update result file: a new column DEFINER has been added to INFORMATION_SCHEMA.TRIGGERS. mysql-test/r/information_schema.result 1.88 05/11/10 22:24:50 anozdrin@stripped +9 -8 Update result file: a new column DEFINER has been added to INFORMATION_SCHEMA.TRIGGERS. # 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: anozdrin # Host: booka. # Root: /home/alik/MySQL/devel/5.0-wl2818 --- 1.265/sql/item_func.cc 2005-11-03 10:05:27 +03:00 +++ 1.266/sql/item_func.cc 2005-11-10 22:24:52 +03:00 @@ -4894,7 +4894,7 @@ /* - Find the function and chack access rigths to the function + Find the function and check access rights to the function SYNOPSIS find_and_check_access() --- 1.365/sql/mysql_priv.h 2005-11-05 05:46:39 +03:00 +++ 1.366/sql/mysql_priv.h 2005-11-10 22:24:52 +03:00 @@ -522,8 +522,9 @@ bool insert_precheck(THD *thd, TABLE_LIST *tables); bool create_table_precheck(THD *thd, TABLE_LIST *tables, TABLE_LIST *create_table); -bool default_view_definer(Security_context *sctx, st_lex_user *definer); +bool get_default_definer(THD *thd, LEX_USER *definer); +LEX_USER *create_definer(THD *thd, LEX_STRING *user_name, LEX_STRING *host_name); enum enum_mysql_completiontype { ROLLBACK_RELEASE=-2, ROLLBACK=1, ROLLBACK_AND_CHAIN=7, @@ -846,6 +847,10 @@ bool mysqld_show_column_types(THD *thd); bool mysqld_help (THD *thd, const char *text); void calc_sum_of_all_status(STATUS_VAR *to); + +void append_definer(THD *thd, String *buffer, const LEX_STRING *definer_user, + const LEX_STRING *definer_host); + /* information schema */ extern LEX_STRING information_schema_name; --- 1.179/sql/sql_acl.cc 2005-10-31 23:11:29 +03:00 +++ 1.180/sql/sql_acl.cc 2005-11-10 22:24:52 +03:00 @@ -3532,7 +3532,7 @@ of other queries). For simple queries first_not_own_table is 0. */ for (i= 0, table= tables; - table != first_not_own_table && i < number; + table && table != first_not_own_table && i < number; table= table->next_global, i++) { /* Remove SHOW_VIEW_ACL, because it will be checked during making view */ --- 1.206/sql/sql_lex.h 2005-11-01 16:54:16 +03:00 +++ 1.207/sql/sql_lex.h 2005-11-10 22:24:53 +03:00 @@ -737,10 +737,15 @@ TABLE_LIST **query_tables_last; /* store original leaf_tables for INSERT SELECT and PS/SP */ TABLE_LIST *leaf_tables_insert; - st_lex_user *create_view_definer; char *create_view_start; char *create_view_select_start; + /* + The definer of the object being created (view, trigger, stored routine). + I.e. the value of DEFINER clause. + */ + LEX_USER *definer; + List<key_part_spec> col_list; List<key_part_spec> ref_list; List<String> interval_list; @@ -886,6 +891,14 @@ being opened is probably enough). */ SQL_LIST trg_table_fields; + + /* + trigger_definition_begin points to the beginning of the word "TRIGGER" in + CREATE TRIGGER statement. This is used to add possibly omitted DEFINER + clause to the trigger definition statement before dumping it to the + binlog. + */ + const char *trigger_definition_begin; /* If non-0 then indicates that query requires prelocking and points to --- 1.509/sql/sql_parse.cc 2005-11-04 12:54:45 +03:00 +++ 1.510/sql/sql_parse.cc 2005-11-10 22:24:54 +03:00 @@ -5062,7 +5062,7 @@ the given table list refers to the list for prelocking (contains tables of other queries). For simple queries first_not_own_table is 0. */ - for (; tables != first_not_own_table; tables= tables->next_global) + for (; tables && tables != first_not_own_table; tables= tables->next_global) { if (tables->schema_table && (want_access & ~(SELECT_ACL | EXTRA_ACL | FILE_ACL))) @@ -7466,32 +7466,81 @@ return new Item_func_not(expr); } +/* + Set the specified definer to the default value, which is the current user in + the thread. Also check that the current user satisfies to the definers + requirements. + + SYNOPSIS + get_default_definer() + thd [in] thread handler + definer [out] definer + + RETURN + error status, that is: + - FALSE -- on success; + - TRUE -- on error (current user can not be a definer). +*/ + +bool get_default_definer(THD *thd, LEX_USER *definer) +{ + /* Check that current user has non-empty host name. */ + + const Security_context *sctx= thd->security_ctx; + + if (sctx->priv_host[0] == 0) + { + my_error(ER_MALFORMED_DEFINER, MYF(0)); + return TRUE; + } + + /* Fill in. */ + + definer->user.str= (char *) sctx->priv_user; + definer->user.length= strlen(definer->user.str); + + definer->host.str= (char *) sctx->priv_host; + definer->host.length= strlen(definer->host.str); + + return FALSE; +} + /* - Assign as view definer current user + Create definer with the given user and host names. Also check that the user + and host names satisfy definers requirements. SYNOPSIS - default_view_definer() - sctx current security context - definer structure where it should be assigned + create_definer() + thd [in] thread handler + user_name [in] user name + host_name [in] host name RETURN - FALSE OK - TRUE Error + On success, return a valid pointer to the created and initialized + LEX_STRING, which contains definer information. + On error, return 0. */ -bool default_view_definer(Security_context *sctx, st_lex_user *definer) +LEX_USER *create_definer(THD *thd, LEX_STRING *user_name, LEX_STRING *host_name) { - definer->user.str= sctx->priv_user; - definer->user.length= strlen(sctx->priv_user); + LEX_USER *definer; + + /* Check that specified host name is valid. */ - if (!*sctx->priv_host) + if (host_name->length == 0) { - my_error(ER_NO_VIEW_USER, MYF(0)); - return TRUE; + my_error(ER_MALFORMED_DEFINER, MYF(0)); + return 0; } - definer->host.str= sctx->priv_host; - definer->host.length= strlen(sctx->priv_host); - return FALSE; + /* Create and initialize. */ + + if (! (definer= (LEX_USER*) thd->alloc(sizeof (LEX_USER)))) + return 0; + + definer->user= *user_name; + definer->host= *host_name; + + return definer; } --- 1.293/sql/sql_show.cc 2005-11-03 17:28:11 +03:00 +++ 1.294/sql/sql_show.cc 2005-11-10 22:24:54 +03:00 @@ -1060,18 +1060,36 @@ default: DBUG_ASSERT(0); // never should happen } - buff->append("DEFINER=", 8); - append_identifier(thd, buff, - table->definer.user.str, table->definer.user.length); - buff->append('@'); - append_identifier(thd, buff, - table->definer.host.str, table->definer.host.length); + append_definer(thd, buff, &table->definer.user, &table->definer.host); if (table->view_suid) - buff->append(" SQL SECURITY DEFINER ", 22); + buff->append("SQL SECURITY DEFINER ", 21); else - buff->append(" SQL SECURITY INVOKER ", 22); + buff->append("SQL SECURITY INVOKER ", 21); } + +/* + Append DEFINER clause to the given buffer. + + SYNOPSIS + append_definer() + thd [in] thread handle + buffer [inout] buffer to hold DEFINER clause + definer_user [in] user name part of definer + definer_host [in] host name part of definer +*/ + +void append_definer(THD *thd, String *buffer, const LEX_STRING *definer_user, + const LEX_STRING *definer_host) +{ + buffer->append(STRING_WITH_LEN("DEFINER=")); + append_identifier(thd, buffer, definer_user->str, definer_user->length); + buffer->append('@'); + append_identifier(thd, buffer, definer_host->str, definer_host->length); + buffer->append(' '); +} + + static int view_store_create_info(THD *thd, TABLE_LIST *table, String *buff) { @@ -3094,7 +3112,8 @@ enum trg_event_type event, enum trg_action_time_type timing, LEX_STRING *trigger_stmt, - ulong sql_mode) + ulong sql_mode, + LEX_STRING *definer_buffer) { CHARSET_INFO *cs= system_charset_info; byte *sql_mode_str; @@ -3119,6 +3138,7 @@ sql_mode, &sql_mode_len); table->field[17]->store((const char*)sql_mode_str, sql_mode_len, cs); + table->field[18]->store((const char *)definer_buffer->str, definer_buffer->length, cs); return schema_table_store_record(thd, table); } @@ -3152,15 +3172,21 @@ LEX_STRING trigger_name; LEX_STRING trigger_stmt; ulong sql_mode; + char definer_holder[HOSTNAME_LENGTH + USERNAME_LENGTH + 2]; + LEX_STRING definer_buffer; + definer_buffer.str= definer_holder; if (triggers->get_trigger_info(thd, (enum trg_event_type) event, (enum trg_action_time_type)timing, &trigger_name, &trigger_stmt, - &sql_mode)) + &sql_mode, + &definer_buffer)) continue; + if (store_trigger(thd, table, base_name, file_name, &trigger_name, (enum trg_event_type) event, (enum trg_action_time_type) timing, &trigger_stmt, - sql_mode)) + sql_mode, + &definer_buffer)) DBUG_RETURN(1); } } @@ -4064,6 +4090,7 @@ {"ACTION_REFERENCE_NEW_ROW", 3, MYSQL_TYPE_STRING, 0, 0, 0}, {"CREATED", 0, MYSQL_TYPE_TIMESTAMP, 0, 1, "Created"}, {"SQL_MODE", 65535, MYSQL_TYPE_STRING, 0, 0, "sql_mode"}, + {"DEFINER", 65535, MYSQL_TYPE_STRING, 0, 0, "Definer"}, {0, 0, MYSQL_TYPE_STRING, 0, 0, 0} }; --- 1.439/sql/sql_yacc.yy 2005-11-09 18:50:53 +03:00 +++ 1.440/sql/sql_yacc.yy 2005-11-10 22:24:54 +03:00 @@ -776,7 +776,7 @@ %type <symbol> FUNC_ARG0 FUNC_ARG1 FUNC_ARG2 FUNC_ARG3 keyword keyword_sp -%type <lex_user> user grant_user +%type <lex_user> user grant_user get_definer %type <charset> opt_collate @@ -827,10 +827,12 @@ subselect_end select_var_list select_var_list_init help opt_len opt_extended_describe prepare prepare_src execute deallocate - statement sp_suid opt_view_list view_list or_replace algorithm + statement sp_suid sp_c_chistics sp_a_chistics sp_chistic sp_c_chistic xa load_data opt_field_or_var_spec fields_or_vars opt_load_data_set_spec - view_user view_suid + definer view_replace_or_algorithm view_replace view_algorithm_opt + view_algorithm view_or_trigger_tail view_suid view_tail view_list_opt + view_list view_select view_check_option trigger_tail END_OF_INPUT %type <NONE> call sp_proc_stmts sp_proc_stmts1 sp_proc_stmt @@ -1258,80 +1260,14 @@ YYTHD->client_capabilities |= CLIENT_MULTI_QUERIES; sp->restore_thd_mem_root(YYTHD); } - | CREATE or_replace algorithm view_user view_suid VIEW_SYM table_ident + | CREATE { - THD *thd= YYTHD; - LEX *lex= thd->lex; - lex->sql_command= SQLCOM_CREATE_VIEW; - lex->create_view_start= thd->query; - /* first table in list is target VIEW name */ - if (!lex->select_lex.add_table_to_list(thd, $7, NULL, 0)) - YYABORT; + Lex->create_view_mode= VIEW_CREATE_NEW; + Lex->create_view_algorithm= VIEW_ALGORITHM_UNDEFINED; + Lex->create_view_suid= TRUE; } - opt_view_list AS select_view_init check_option + view_or_trigger {} - | CREATE TRIGGER_SYM sp_name trg_action_time trg_event - ON table_ident FOR_SYM EACH_SYM ROW_SYM - { - LEX *lex= Lex; - sp_head *sp; - - if (lex->sphead) - { - my_error(ER_SP_NO_RECURSIVE_CREATE, MYF(0), "TRIGGER"); - YYABORT; - } - - if (!(sp= new sp_head())) - YYABORT; - sp->reset_thd_mem_root(YYTHD); - sp->init(lex); - - sp->m_type= TYPE_ENUM_TRIGGER; - lex->sphead= sp; - lex->spname= $3; - /* - We have to turn of CLIENT_MULTI_QUERIES while parsing a - stored procedure, otherwise yylex will chop it into pieces - at each ';'. - */ - sp->m_old_cmq= YYTHD->client_capabilities & CLIENT_MULTI_QUERIES; - YYTHD->client_capabilities &= ~CLIENT_MULTI_QUERIES; - - bzero((char *)&lex->sp_chistics, sizeof(st_sp_chistics)); - lex->sphead->m_chistics= &lex->sp_chistics; - lex->sphead->m_body_begin= lex->ptr; - } - sp_proc_stmt - { - LEX *lex= Lex; - sp_head *sp= lex->sphead; - - lex->sql_command= SQLCOM_CREATE_TRIGGER; - sp->init_strings(YYTHD, lex, $3); - /* Restore flag if it was cleared above */ - if (sp->m_old_cmq) - YYTHD->client_capabilities |= CLIENT_MULTI_QUERIES; - sp->restore_thd_mem_root(YYTHD); - - if (sp->is_not_allowed_in_function("trigger")) - YYABORT; - - /* - We have to do it after parsing trigger body, because some of - sp_proc_stmt alternatives are not saving/restoring LEX, so - lex->query_tables can be wiped out. - - QQ: What are other consequences of this? - - QQ: Could we loosen lock type in certain cases ? - */ - if (!lex->select_lex.add_table_to_list(YYTHD, $7, - (LEX_STRING*) 0, - TL_OPTION_UPDATING, - TL_WRITE)) - YYABORT; - } | CREATE USER clear_privileges grant_list { Lex->sql_command = SQLCOM_CREATE_USER; @@ -3435,7 +3371,8 @@ lex->sql_command= SQLCOM_ALTER_FUNCTION; lex->spname= $3; } - | ALTER algorithm view_user view_suid VIEW_SYM table_ident + | ALTER view_algorithm_opt definer view_suid + VIEW_SYM table_ident { THD *thd= YYTHD; LEX *lex= thd->lex; @@ -3445,7 +3382,7 @@ /* first table in list is target VIEW name */ lex->select_lex.add_table_to_list(thd, $6, NULL, 0); } - opt_view_list AS select_view_init check_option + view_list_opt AS view_select view_check_option {} ; @@ -4013,18 +3950,6 @@ | '(' select_paren ')' union_opt; -select_view_init: - SELECT_SYM remember_name select_init2 - { - Lex->create_view_select_start= $2; - } - | - '(' remember_name select_paren ')' union_opt - { - Lex->create_view_select_start= $2; - } - ; - select_paren: SELECT_SYM select_part2 { @@ -8963,8 +8888,119 @@ lex->current_select = lex->current_select->return_after_parsing(); }; -opt_view_list: - /* empty */ {} +definer: + get_definer + { + THD *thd= YYTHD; + + if (! (thd->lex->definer= create_definer(thd, &$1->user, &$1->host))) + YYABORT; + } + ; + +get_definer: + opt_current_definer + { + THD *thd= YYTHD; + + if (!($$=(LEX_USER*) thd->alloc(sizeof(st_lex_user)))) + YYABORT; + + if (get_default_definer(thd, $$)) + YYABORT; + } + | DEFINER_SYM EQ ident_or_text '@' ident_or_text + { + if (!($$=(LEX_USER*) YYTHD->alloc(sizeof(st_lex_user)))) + YYABORT; + + $$->user= $3; + $$->host= $5; + } + ; + +opt_current_definer: + /* empty */ + | DEFINER_SYM EQ CURRENT_USER optional_braces + ; + +/************************************************************************** + + CREATE VIEW statement options. + +**************************************************************************/ + +view_replace_or_algorithm: + view_replace + {} + | view_replace view_algorithm + {} + | view_algorithm + {} + ; + +view_replace: + OR_SYM REPLACE + { Lex->create_view_mode= VIEW_CREATE_OR_REPLACE; } + ; + +view_algorithm: + ALGORITHM_SYM EQ UNDEFINED_SYM + { Lex->create_view_algorithm= VIEW_ALGORITHM_UNDEFINED; } + | ALGORITHM_SYM EQ MERGE_SYM + { Lex->create_view_algorithm= VIEW_ALGORITHM_MERGE; } + | ALGORITHM_SYM EQ TEMPTABLE_SYM + { Lex->create_view_algorithm= VIEW_ALGORITHM_TMPTABLE; } + ; + +view_algorithm_opt: + /* empty */ + { Lex->create_view_algorithm= VIEW_ALGORITHM_UNDEFINED; } + | view_algorithm + {} + ; + +view_or_trigger: + definer view_or_trigger_tail + {} + | view_replace_or_algorithm definer view_tail + {} + ; + +view_or_trigger_tail: + view_tail + {} + | trigger_tail + {} + ; + +view_suid: + /* empty */ + { Lex->create_view_suid= TRUE; } + | SQL_SYM SECURITY_SYM DEFINER_SYM + { Lex->create_view_suid= TRUE; } + | SQL_SYM SECURITY_SYM INVOKER_SYM + { Lex->create_view_suid= FALSE; } + ; + +view_tail: + view_suid VIEW_SYM table_ident + { + THD *thd= YYTHD; + LEX *lex= thd->lex; + lex->sql_command= SQLCOM_CREATE_VIEW; + lex->create_view_start= thd->query; + /* first table in list is target VIEW name */ + if (!lex->select_lex.add_table_to_list(thd, $3, NULL, 0)) + YYABORT; + } + view_list_opt AS view_select view_check_option + {} + ; + +view_list_opt: + /* empty */ + {} | '(' view_list ')' ; @@ -8981,79 +9017,102 @@ } ; -or_replace: - /* empty */ { Lex->create_view_mode= VIEW_CREATE_NEW; } - | OR_SYM REPLACE { Lex->create_view_mode= VIEW_CREATE_OR_REPLACE; } +view_select: + SELECT_SYM remember_name select_init2 + { + Lex->create_view_select_start= $2; + } + | '(' remember_name select_paren ')' union_opt + { + Lex->create_view_select_start= $2; + } ; -algorithm: +view_check_option: /* empty */ - { Lex->create_view_algorithm= VIEW_ALGORITHM_UNDEFINED; } - | ALGORITHM_SYM EQ UNDEFINED_SYM - { Lex->create_view_algorithm= VIEW_ALGORITHM_UNDEFINED; } - | ALGORITHM_SYM EQ MERGE_SYM - { Lex->create_view_algorithm= VIEW_ALGORITHM_MERGE; } - | ALGORITHM_SYM EQ TEMPTABLE_SYM - { Lex->create_view_algorithm= VIEW_ALGORITHM_TMPTABLE; } + { Lex->create_view_check= VIEW_CHECK_NONE; } + | WITH CHECK_SYM OPTION + { Lex->create_view_check= VIEW_CHECK_CASCADED; } + | WITH CASCADED CHECK_SYM OPTION + { Lex->create_view_check= VIEW_CHECK_CASCADED; } + | WITH LOCAL_SYM CHECK_SYM OPTION + { Lex->create_view_check= VIEW_CHECK_LOCAL; } ; -view_user: - /* empty */ - { - THD *thd= YYTHD; - if (!(thd->lex->create_view_definer= - (LEX_USER*) thd->alloc(sizeof(st_lex_user)))) - YYABORT; - if (default_view_definer(thd->security_ctx, - thd->lex->create_view_definer)) - YYABORT; - } - | DEFINER_SYM EQ CURRENT_USER optional_braces - { - THD *thd= YYTHD; - if (!(thd->lex->create_view_definer= - (LEX_USER*) thd->alloc(sizeof(st_lex_user)))) - YYABORT; - if (default_view_definer(thd->security_ctx, - thd->lex->create_view_definer)) - YYABORT; - } - | DEFINER_SYM EQ ident_or_text '@' ident_or_text +/************************************************************************** + + CREATE TRIGGER statement parts. + +**************************************************************************/ + +trigger_tail: + TRIGGER_SYM remember_name sp_name trg_action_time trg_event + ON table_ident FOR_SYM EACH_SYM ROW_SYM + { + LEX *lex= Lex; + sp_head *sp; + + if (lex->sphead) { - THD *thd= YYTHD; - st_lex_user *view_user; - if (!(thd->lex->create_view_definer= view_user= - (LEX_USER*) thd->alloc(sizeof(st_lex_user)))) - YYABORT; - view_user->user = $3; view_user->host=$5; - if (view_user->host.length == 0) - { - my_error(ER_NO_VIEW_USER, MYF(0)); - YYABORT; - } + my_error(ER_SP_NO_RECURSIVE_CREATE, MYF(0), "TRIGGER"); + YYABORT; } - ; - -view_suid: - /* empty */ - { Lex->create_view_suid= TRUE; } - | - SQL_SYM SECURITY_SYM DEFINER_SYM - { Lex->create_view_suid= TRUE; } - | SQL_SYM SECURITY_SYM INVOKER_SYM - { Lex->create_view_suid= FALSE; } + + if (!(sp= new sp_head())) + YYABORT; + sp->reset_thd_mem_root(YYTHD); + sp->init(lex); + + lex->trigger_definition_begin= $2; + + sp->m_type= TYPE_ENUM_TRIGGER; + lex->sphead= sp; + lex->spname= $3; + /* + We have to turn of CLIENT_MULTI_QUERIES while parsing a + stored procedure, otherwise yylex will chop it into pieces + at each ';'. + */ + sp->m_old_cmq= YYTHD->client_capabilities & CLIENT_MULTI_QUERIES; + YYTHD->client_capabilities &= ~CLIENT_MULTI_QUERIES; + + bzero((char *)&lex->sp_chistics, sizeof(st_sp_chistics)); + lex->sphead->m_chistics= &lex->sp_chistics; + lex->sphead->m_body_begin= lex->ptr; + } + sp_proc_stmt + { + LEX *lex= Lex; + sp_head *sp= lex->sphead; + + lex->sql_command= SQLCOM_CREATE_TRIGGER; + sp->init_strings(YYTHD, lex, $3); + /* Restore flag if it was cleared above */ + if (sp->m_old_cmq) + YYTHD->client_capabilities |= CLIENT_MULTI_QUERIES; + sp->restore_thd_mem_root(YYTHD); + + if (sp->is_not_allowed_in_function("trigger")) + YYABORT; + + /* + We have to do it after parsing trigger body, because some of + sp_proc_stmt alternatives are not saving/restoring LEX, so + lex->query_tables can be wiped out. + + QQ: What are other consequences of this? + + QQ: Could we loosen lock type in certain cases ? + */ + if (!lex->select_lex.add_table_to_list(YYTHD, $7, + (LEX_STRING*) 0, + TL_OPTION_UPDATING, + TL_WRITE)) + YYABORT; + } ; -check_option: - /* empty */ - { Lex->create_view_check= VIEW_CHECK_NONE; } - | WITH CHECK_SYM OPTION - { Lex->create_view_check= VIEW_CHECK_CASCADED; } - | WITH CASCADED CHECK_SYM OPTION - { Lex->create_view_check= VIEW_CHECK_CASCADED; } - | WITH LOCAL_SYM CHECK_SYM OPTION - { Lex->create_view_check= VIEW_CHECK_LOCAL; } - ; +/*************************************************************************/ xa: XA_SYM begin_or_start xid opt_join_or_resume { --- 1.54/sql/share/errmsg.txt 2005-11-06 09:41:30 +03:00 +++ 1.55/sql/share/errmsg.txt 2005-11-10 22:24:52 +03:00 @@ -5405,14 +5405,14 @@ eng "The prepared statement contains a stored routine call that refers to that same statement. It's not allowed to execute a prepared statement in such a recursive manner" ER_SP_CANT_SET_AUTOCOMMIT eng "Not allowed to set autocommit from a stored function or trigger" -ER_NO_VIEW_USER - eng "View definer is not fully qualified" +ER_MALFORMED_DEFINER + eng "Definer is not fully qualified" ER_VIEW_FRM_NO_USER eng "View %-.64s.%-.64s has not definer information (old table format). Current user is used as definer. Please recreate view!" ER_VIEW_OTHER_USER - eng "You need the SUPER privilege for creation view with %-.64s@%-.64s definer" + eng "You need the SUPER privilege for creation view with %-.64s@%-.64s definer" ER_NO_SUCH_USER - eng "There is not %-.64s@%-.64s registered" + eng "There is no '%-.64s'@'%-.64s' registered" ER_FORBID_SCHEMA_CHANGE eng "Changing schema from '%-.64s' to '%-.64s' is not allowed." ER_ROW_IS_REFERENCED_2 23000 @@ -5421,3 +5421,5 @@ eng "Cannot add or update a child row: a foreign key constraint fails (%.192s)" ER_SP_BAD_VAR_SHADOW 42000 eng "Variable '%-.64s' must be quoted with `...`, or renamed" +ER_TRG_NO_DEFINER + eng "No definer attribute for trigger '%-.64s'.'%-.64s'. The trigger will be activated under the authorization of the invoker, which may have insufficient privileges. Please recreate the trigger." --- New file --- +++ mysql-test/r/trigger-compat.result 05/11/10 22:24:54 DELETE FROM mysql.user WHERE User LIKE 'mysqltest_%'; DELETE FROM mysql.db WHERE User LIKE 'mysqltest_%'; DELETE FROM mysql.tables_priv WHERE User LIKE 'mysqltest_%'; DELETE FROM mysql.columns_priv WHERE User LIKE 'mysqltest_%'; FLUSH PRIVILEGES; DROP DATABASE IF EXISTS mysqltest_db1; CREATE DATABASE mysqltest_db1; CREATE USER mysqltest_dfn@localhost; CREATE USER mysqltest_inv@localhost; GRANT SUPER ON *.* TO mysqltest_dfn@localhost; GRANT CREATE ON mysqltest_db1.* TO mysqltest_dfn@localhost; ---> connection: wl2818_definer_con CREATE TABLE t1(num_value INT); CREATE TABLE t2(user_str TEXT); CREATE TRIGGER wl2818_trg1 BEFORE INSERT ON t1 FOR EACH ROW INSERT INTO t2 VALUES(CURRENT_USER()); ---> patching t1.TRG... CREATE TRIGGER wl2818_trg2 AFTER INSERT ON t1 FOR EACH ROW INSERT INTO t2 VALUES(CURRENT_USER()); Warnings: Warning 1454 No definer attribute for trigger 'mysqltest_db1'.'wl2818_trg1'. The trigger will be activated under the authorization of the invoker, which may have insufficient privileges. Please recreate the trigger. SELECT trigger_name, definer FROM INFORMATION_SCHEMA.TRIGGERS ORDER BY trigger_name; trigger_name definer wl2818_trg1 wl2818_trg2 mysqltest_dfn@localhost Warnings: Warning 1454 No definer attribute for trigger 'mysqltest_db1'.'wl2818_trg1'. The trigger will be activated under the authorization of the invoker, which may have insufficient privileges. Please recreate the trigger. SELECT * FROM INFORMATION_SCHEMA.TRIGGERS ORDER BY trigger_name; TRIGGER_CATALOG TRIGGER_SCHEMA TRIGGER_NAME EVENT_MANIPULATION EVENT_OBJECT_CATALOG EVENT_OBJECT_SCHEMA EVENT_OBJECT_TABLE ACTION_ORDER ACTION_CONDITION ACTION_STATEMENT ACTION_ORIENTATION ACTION_TIMING ACTION_REFERENCE_OLD_TABLE ACTION_REFERENCE_NEW_TABLE ACTION_REFERENCE_OLD_ROW ACTION_REFERENCE_NEW_ROW CREATED SQL_MODE DEFINER NULL mysqltest_db1 wl2818_trg1 INSERT NULL mysqltest_db1 t1 0 NULL INSERT INTO t2 VALUES(CURRENT_USER()) ROW BEFORE NULL NULL OLD NEW NULL NULL mysqltest_db1 wl2818_trg2 INSERT NULL mysqltest_db1 t1 0 NULL INSERT INTO t2 VALUES(CURRENT_USER()) ROW AFTER NULL NULL OLD NEW NULL mysqltest_dfn@localhost --- New file --- +++ mysql-test/r/trigger-grant.result 05/11/10 22:24:55 DELETE FROM mysql.user WHERE User LIKE 'mysqltest_%'; DELETE FROM mysql.db WHERE User LIKE 'mysqltest_%'; DELETE FROM mysql.tables_priv WHERE User LIKE 'mysqltest_%'; DELETE FROM mysql.columns_priv WHERE User LIKE 'mysqltest_%'; FLUSH PRIVILEGES; DROP DATABASE IF EXISTS mysqltest_db1; CREATE DATABASE mysqltest_db1; CREATE USER mysqltest_dfn@localhost; CREATE USER mysqltest_inv@localhost; GRANT SUPER ON *.* TO mysqltest_dfn@localhost; GRANT CREATE ON mysqltest_db1.* TO mysqltest_dfn@localhost; ---> connection: wl2818_definer_con CREATE TABLE t1(num_value INT); CREATE TABLE t2(user_str TEXT); CREATE TRIGGER trg1 AFTER INSERT ON t1 FOR EACH ROW INSERT INTO t2 VALUES(CURRENT_USER()); ---> connection: default GRANT ALL PRIVILEGES ON mysqltest_db1.t1 TO mysqltest_dfn@localhost; GRANT ALL PRIVILEGES ON mysqltest_db1.t2 TO mysqltest_dfn@localhost; GRANT ALL PRIVILEGES ON mysqltest_db1.t1 TO 'mysqltest_inv'@localhost; GRANT SELECT ON mysqltest_db1.t2 TO 'mysqltest_inv'@localhost; ---> connection: wl2818_definer_con use mysqltest_db1; INSERT INTO t1 VALUES(1); SELECT * FROM t1; num_value 1 SELECT * FROM t2; user_str mysqltest_dfn@localhost ---> connection: wl2818_invoker_con use mysqltest_db1; INSERT INTO t1 VALUES(2); SELECT * FROM t1; num_value 1 2 SELECT * FROM t2; user_str mysqltest_dfn@localhost mysqltest_dfn@localhost ---> connection: default use mysqltest_db1; REVOKE INSERT ON mysqltest_db1.t2 FROM mysqltest_dfn@localhost; ---> connection: wl2818_invoker_con use mysqltest_db1; INSERT INTO t1 VALUES(3); ERROR 42000: INSERT command denied to user 'mysqltest_dfn'@'localhost' for table 't2' SELECT * FROM t1; num_value 1 2 3 SELECT * FROM t2; user_str mysqltest_dfn@localhost mysqltest_dfn@localhost ---> connection: default use mysqltest_db1; REVOKE SELECT ON mysqltest_db1.t1 FROM mysqltest_dfn@localhost; ---> connection: wl2818_definer_con use mysqltest_db1; DROP TRIGGER trg1; SET @new_sum = 0; SET @old_sum = 0; ---> INSERT INTO statement; BEFORE timing CREATE TRIGGER trg1 BEFORE INSERT ON t1 FOR EACH ROW SET @new_sum = @new_sum + NEW.num_value; INSERT INTO t1 VALUES(4); ERROR 42000: SELECT command denied to user 'mysqltest_dfn'@'localhost' for table 't1' ---> INSERT INTO statement; AFTER timing DROP TRIGGER trg1; CREATE TRIGGER trg1 AFTER INSERT ON t1 FOR EACH ROW SET @new_sum = @new_sum + NEW.num_value; INSERT INTO t1 VALUES(5); ERROR 42000: SELECT command denied to user 'mysqltest_dfn'@'localhost' for table 't1' ---> UPDATE statement; BEFORE timing DROP TRIGGER trg1; CREATE TRIGGER trg1 BEFORE UPDATE ON t1 FOR EACH ROW SET @old_sum = @old_sum + OLD.num_value; UPDATE t1 SET num_value = 10; ERROR 42000: SELECT command denied to user 'mysqltest_dfn'@'localhost' for table 't1' ---> UPDATE statement; AFTER timing DROP TRIGGER trg1; CREATE TRIGGER trg1 AFTER UPDATE ON t1 FOR EACH ROW SET @new_sum = @new_sum + NEW.num_value; UPDATE t1 SET num_value = 20; ERROR 42000: SELECT command denied to user 'mysqltest_dfn'@'localhost' for table 't1' ---> DELETE statement; BEFORE timing DROP TRIGGER trg1; CREATE TRIGGER trg1 BEFORE DELETE ON t1 FOR EACH ROW SET @old_sum = @old_sum + OLD.num_value; DELETE FROM t1; ERROR 42000: SELECT command denied to user 'mysqltest_dfn'@'localhost' for table 't1' ---> DELETE statement; AFTER timing DROP TRIGGER trg1; CREATE TRIGGER trg1 AFTER DELETE ON t1 FOR EACH ROW SET @old_sum = @old_sum + OLD.num_value; DELETE FROM t1; ERROR 42000: SELECT command denied to user 'mysqltest_dfn'@'localhost' for table 't1' ---> connection: default use mysqltest_db1; GRANT SELECT ON mysqltest_db1.t1 TO mysqltest_dfn@localhost; REVOKE UPDATE ON mysqltest_db1.t1 FROM mysqltest_dfn@localhost; ---> connection: wl2818_definer_con use mysqltest_db1; DROP TRIGGER trg1; SET @new_sum = 0; SET @old_sum = 0; ---> INSERT INTO statement; BEFORE timing CREATE TRIGGER trg1 BEFORE INSERT ON t1 FOR EACH ROW SET @new_sum = @new_sum + NEW.num_value; INSERT INTO t1 VALUES(4); ERROR 42000: UPDATE command denied to user 'mysqltest_dfn'@'localhost' for table 't1' ---> INSERT INTO statement; AFTER timing DROP TRIGGER trg1; CREATE TRIGGER trg1 AFTER INSERT ON t1 FOR EACH ROW SET @new_sum = @new_sum + NEW.num_value; INSERT INTO t1 VALUES(5); ERROR 42000: UPDATE command denied to user 'mysqltest_dfn'@'localhost' for table 't1' ---> UPDATE statement; BEFORE timing DROP TRIGGER trg1; CREATE TRIGGER trg1 BEFORE UPDATE ON t1 FOR EACH ROW SET @old_sum = @old_sum + OLD.num_value; UPDATE t1 SET num_value = 10; ERROR 42000: UPDATE command denied to user 'mysqltest_dfn'@'localhost' for table 't1' ---> UPDATE statement; AFTER timing DROP TRIGGER trg1; CREATE TRIGGER trg1 AFTER UPDATE ON t1 FOR EACH ROW SET @new_sum = @new_sum + NEW.num_value; UPDATE t1 SET num_value = 20; ERROR 42000: UPDATE command denied to user 'mysqltest_dfn'@'localhost' for table 't1' ---> DELETE statement; BEFORE timing DROP TRIGGER trg1; CREATE TRIGGER trg1 BEFORE DELETE ON t1 FOR EACH ROW SET @old_sum = @old_sum + OLD.num_value; DELETE FROM t1; ERROR 42000: UPDATE command denied to user 'mysqltest_dfn'@'localhost' for table 't1' ---> DELETE statement; AFTER timing DROP TRIGGER trg1; CREATE TRIGGER trg1 AFTER DELETE ON t1 FOR EACH ROW SET @old_sum = @old_sum + OLD.num_value; DELETE FROM t1; ERROR 42000: UPDATE command denied to user 'mysqltest_dfn'@'localhost' for table 't1' ---> connection: wl2818_definer_con use mysqltest_db1; DROP TRIGGER trg1; CREATE DEFINER='mysqltest_inv'@'localhost' TRIGGER trg1 BEFORE INSERT ON t1 FOR EACH ROW SET @new_sum = 0; CREATE DEFINER='mysqltest_nonexs'@'localhost' TRIGGER trg2 AFTER INSERT ON t1 FOR EACH ROW SET @new_sum = 0; Warnings: Note 1449 There is no 'mysqltest_nonexs'@'localhost' registered INSERT INTO t1 VALUES(6); ERROR 42000: Access denied; you need the SUPER privilege for this operation SHOW TRIGGERS; Trigger Event Table Statement Timing Created sql_mode Definer trg1 INSERT t1 SET @new_sum = 0 BEFORE NULL mysqltest_inv@localhost trg2 INSERT t1 SET @new_sum = 0 AFTER NULL mysqltest_nonexs@localhost DROP TRIGGER trg1; DROP TRIGGER trg2; CREATE TRIGGER trg1 BEFORE INSERT ON t1 FOR EACH ROW SET @a = 1; CREATE TRIGGER trg2 AFTER INSERT ON t1 FOR EACH ROW SET @a = 2; CREATE TRIGGER trg3 BEFORE UPDATE ON t1 FOR EACH ROW SET @a = 3; CREATE TRIGGER trg4 AFTER UPDATE ON t1 FOR EACH ROW SET @a = 4; CREATE TRIGGER trg5 BEFORE DELETE ON t1 FOR EACH ROW SET @a = 5; SELECT trigger_name, definer FROM INFORMATION_SCHEMA.TRIGGERS ORDER BY trigger_name; trigger_name definer trg1 trg2 @ trg3 @abc@def@@ trg4 @hostname trg5 @abcdef@@@hostname Warnings: Warning 1454 No definer attribute for trigger 'mysqltest_db1'.'trg1'. The trigger will be activated under the authorization of the invoker, which may have insufficient privileges. Please recreate the trigger. SELECT * FROM INFORMATION_SCHEMA.TRIGGERS ORDER BY trigger_name; TRIGGER_CATALOG TRIGGER_SCHEMA TRIGGER_NAME EVENT_MANIPULATION EVENT_OBJECT_CATALOG EVENT_OBJECT_SCHEMA EVENT_OBJECT_TABLE ACTION_ORDER ACTION_CONDITION ACTION_STATEMENT ACTION_ORIENTATION ACTION_TIMING ACTION_REFERENCE_OLD_TABLE ACTION_REFERENCE_NEW_TABLE ACTION_REFERENCE_OLD_ROW ACTION_REFERENCE_NEW_ROW CREATED SQL_MODE DEFINER NULL mysqltest_db1 trg1 INSERT NULL mysqltest_db1 t1 0 NULL SET @a = 1 ROW BEFORE NULL NULL OLD NEW NULL NULL mysqltest_db1 trg2 INSERT NULL mysqltest_db1 t1 0 NULL SET @a = 2 ROW AFTER NULL NULL OLD NEW NULL @ NULL mysqltest_db1 trg3 UPDATE NULL mysqltest_db1 t1 0 NULL SET @a = 3 ROW BEFORE NULL NULL OLD NEW NULL @abc@def@@ NULL mysqltest_db1 trg4 UPDATE NULL mysqltest_db1 t1 0 NULL SET @a = 4 ROW AFTER NULL NULL OLD NEW NULL @hostname NULL mysqltest_db1 trg5 DELETE NULL mysqltest_db1 t1 0 NULL SET @a = 5 ROW BEFORE NULL NULL OLD NEW NULL @abcdef@@@hostname ---> connection: default DROP USER mysqltest_dfn@localhost; DROP USER mysqltest_inv@localhost; DROP DATABASE mysqltest_db1; Warnings: Warning 1454 No definer attribute for trigger 'mysqltest_db1'.'trg1'. The trigger will be activated under the authorization of the invoker, which may have insufficient privileges. Please recreate the trigger. --- New file --- +++ mysql-test/t/trigger-compat.test 05/11/10 22:24:55 # Test case(s) in this file contain(s) GRANT/REVOKE statements, which are not # supported in embedded server. So, this test should not be run on embedded # server. -- source include/not_embedded.inc ########################################################################### # # Tests for WL#2818: # - Check that triggers created w/o DEFINER information work well: # - create the first trigger; # - manually remove definer information from corresponding TRG file; # - create the second trigger (the first trigger will be reloaded; check # that we receive a warning); # - check that the triggers loaded correctly; # ########################################################################### # # Prepare environment. # DELETE FROM mysql.user WHERE User LIKE 'mysqltest_%'; DELETE FROM mysql.db WHERE User LIKE 'mysqltest_%'; DELETE FROM mysql.tables_priv WHERE User LIKE 'mysqltest_%'; DELETE FROM mysql.columns_priv WHERE User LIKE 'mysqltest_%'; FLUSH PRIVILEGES; --disable_warnings DROP DATABASE IF EXISTS mysqltest_db1; --enable_warnings CREATE DATABASE mysqltest_db1; CREATE USER mysqltest_dfn@localhost; CREATE USER mysqltest_inv@localhost; GRANT SUPER ON *.* TO mysqltest_dfn@localhost; GRANT CREATE ON mysqltest_db1.* TO mysqltest_dfn@localhost; # # Create a table and the first trigger. # --connect (wl2818_definer_con,localhost,mysqltest_dfn,,mysqltest_db1) --connection wl2818_definer_con --echo --echo ---> connection: wl2818_definer_con CREATE TABLE t1(num_value INT); CREATE TABLE t2(user_str TEXT); CREATE TRIGGER wl2818_trg1 BEFORE INSERT ON t1 FOR EACH ROW INSERT INTO t2 VALUES(CURRENT_USER()); # # Remove definers from TRG file. # --echo --echo ---> patching t1.TRG... --exec grep --text -v 'definers=' $MYSQL_TEST_DIR/var/master-data/mysqltest_db1/t1.TRG > $MYSQL_TEST_DIR/var/tmp/t1.TRG --exec mv $MYSQL_TEST_DIR/var/tmp/t1.TRG $MYSQL_TEST_DIR/var/master-data/mysqltest_db1/t1.TRG # # Create a new trigger. # --echo CREATE TRIGGER wl2818_trg2 AFTER INSERT ON t1 FOR EACH ROW INSERT INTO t2 VALUES(CURRENT_USER()); --echo SELECT trigger_name, definer FROM INFORMATION_SCHEMA.TRIGGERS ORDER BY trigger_name; --echo SELECT * FROM INFORMATION_SCHEMA.TRIGGERS ORDER BY trigger_name; --- New file --- +++ mysql-test/t/trigger-grant.test 05/11/10 22:24:56 # Test case(s) in this file contain(s) GRANT/REVOKE statements, which are not # supported in embedded server. So, this test should not be run on embedded # server. -- source include/not_embedded.inc ########################################################################### # # Tests for WL#2818: # - Check that triggers are executed under the authorization of the definer. # - Check that if trigger contains NEW/OLD variables, the definer must have # SELECT privilege on the subject table. # - Check DEFINER clause of CREATE TRIGGER statement; # - Check that SUPER privilege required to create a trigger with different # definer. # - Check that if the user specified as DEFINER does not exist, a warning # is emitted. # - Check that the definer of a trigger does not exist, the trigger will # not be activated. # - Check that SHOW TRIGGERS statement provides "Definer" column. # # Let's also check that user name part of definer can contain '@' symbol (to # check that triggers are not affected by BUG#13310 "incorrect user parsing # by SP"). # ########################################################################### # # Prepare environment. # DELETE FROM mysql.user WHERE User LIKE 'mysqltest_%'; DELETE FROM mysql.db WHERE User LIKE 'mysqltest_%'; DELETE FROM mysql.tables_priv WHERE User LIKE 'mysqltest_%'; DELETE FROM mysql.columns_priv WHERE User LIKE 'mysqltest_%'; FLUSH PRIVILEGES; --disable_warnings DROP DATABASE IF EXISTS mysqltest_db1; --enable_warnings CREATE DATABASE mysqltest_db1; CREATE USER mysqltest_dfn@localhost; CREATE USER mysqltest_inv@localhost; GRANT SUPER ON *.* TO mysqltest_dfn@localhost; GRANT CREATE ON mysqltest_db1.* TO mysqltest_dfn@localhost; # # Check that triggers are executed under the authorization of the definer: # - create two tables under "definer"; # - grant all privileges on the test db to "definer"; # - grant all privileges on the first table to "invoker"; # - grant only select privilege on the second table to "invoker"; # - create a trigger, which inserts a row into the second table after # inserting into the first table. # - insert a row into the first table under "invoker". A row also should be # inserted into the second table. # --connect (wl2818_definer_con,localhost,mysqltest_dfn,,mysqltest_db1) --connection wl2818_definer_con --echo --echo ---> connection: wl2818_definer_con CREATE TABLE t1(num_value INT); CREATE TABLE t2(user_str TEXT); CREATE TRIGGER trg1 AFTER INSERT ON t1 FOR EACH ROW INSERT INTO t2 VALUES(CURRENT_USER()); --connection default --echo --echo ---> connection: default # Setup definer's privileges. GRANT ALL PRIVILEGES ON mysqltest_db1.t1 TO mysqltest_dfn@localhost; GRANT ALL PRIVILEGES ON mysqltest_db1.t2 TO mysqltest_dfn@localhost; # Setup invoker's privileges. GRANT ALL PRIVILEGES ON mysqltest_db1.t1 TO 'mysqltest_inv'@localhost; GRANT SELECT ON mysqltest_db1.t2 TO 'mysqltest_inv'@localhost; --connection wl2818_definer_con --echo --echo ---> connection: wl2818_definer_con use mysqltest_db1; INSERT INTO t1 VALUES(1); SELECT * FROM t1; SELECT * FROM t2; --connect (wl2818_invoker_con,localhost,mysqltest_inv,,mysqltest_db1) --connection wl2818_invoker_con --echo --echo ---> connection: wl2818_invoker_con use mysqltest_db1; INSERT INTO t1 VALUES(2); SELECT * FROM t1; SELECT * FROM t2; # # Check that if definer lost some privilege required to execute (activate) a # trigger, the trigger will not be activated: # - create a trigger on insert into the first table, which will insert a row # into the second table; # - revoke INSERT privilege on the second table from the definer; # - insert a row into the first table; # - check that an error has been risen; # - check that no row has been inserted into the second table; # --connection default --echo --echo ---> connection: default use mysqltest_db1; REVOKE INSERT ON mysqltest_db1.t2 FROM mysqltest_dfn@localhost; --connection wl2818_invoker_con --echo --echo ---> connection: wl2818_invoker_con use mysqltest_db1; --error ER_TABLEACCESS_DENIED_ERROR INSERT INTO t1 VALUES(3); SELECT * FROM t1; SELECT * FROM t2; # # Check that if trigger contains NEW/OLD variables, the definer must have # SELECT/UPDATE privilege on the subject table: # - drop the trigger; # - create a new trigger, which will use NEW variable; # - create another new trigger, which will use OLD variable; # - revoke SELECT/UPDATE privilege on the first table from "definer"; # - insert a row into the first table; # - analyze error code; # # # SELECT privilege. # --connection default --echo --echo ---> connection: default use mysqltest_db1; REVOKE SELECT ON mysqltest_db1.t1 FROM mysqltest_dfn@localhost; --connection wl2818_definer_con --echo --echo ---> connection: wl2818_definer_con use mysqltest_db1; DROP TRIGGER trg1; SET @new_sum = 0; SET @old_sum = 0; # INSERT INTO statement; BEFORE timing --echo ---> INSERT INTO statement; BEFORE timing CREATE TRIGGER trg1 BEFORE INSERT ON t1 FOR EACH ROW SET @new_sum = @new_sum + NEW.num_value; --error ER_TABLEACCESS_DENIED_ERROR INSERT INTO t1 VALUES(4); # INSERT INTO statement; AFTER timing --echo ---> INSERT INTO statement; AFTER timing DROP TRIGGER trg1; CREATE TRIGGER trg1 AFTER INSERT ON t1 FOR EACH ROW SET @new_sum = @new_sum + NEW.num_value; --error ER_TABLEACCESS_DENIED_ERROR INSERT INTO t1 VALUES(5); # UPDATE statement; BEFORE timing --echo ---> UPDATE statement; BEFORE timing DROP TRIGGER trg1; CREATE TRIGGER trg1 BEFORE UPDATE ON t1 FOR EACH ROW SET @old_sum = @old_sum + OLD.num_value; --error ER_TABLEACCESS_DENIED_ERROR UPDATE t1 SET num_value = 10; # UPDATE statement; AFTER timing --echo ---> UPDATE statement; AFTER timing DROP TRIGGER trg1; CREATE TRIGGER trg1 AFTER UPDATE ON t1 FOR EACH ROW SET @new_sum = @new_sum + NEW.num_value; --error ER_TABLEACCESS_DENIED_ERROR UPDATE t1 SET num_value = 20; # DELETE statement; BEFORE timing --echo ---> DELETE statement; BEFORE timing DROP TRIGGER trg1; CREATE TRIGGER trg1 BEFORE DELETE ON t1 FOR EACH ROW SET @old_sum = @old_sum + OLD.num_value; --error ER_TABLEACCESS_DENIED_ERROR DELETE FROM t1; # DELETE statement; AFTER timing --echo ---> DELETE statement; AFTER timing DROP TRIGGER trg1; CREATE TRIGGER trg1 AFTER DELETE ON t1 FOR EACH ROW SET @old_sum = @old_sum + OLD.num_value; --error ER_TABLEACCESS_DENIED_ERROR DELETE FROM t1; # # UPDATE privilege # # NOTE: At the moment, UPDATE privilege is required if the trigger contains # NEW/OLD variables, whenever the trigger modifies them or not. Moreover, # UPDATE privilege is checked for whole table, not for individual columns. # # The following test cases should be changed when full support of UPDATE # privilege will be done. # --connection default --echo --echo ---> connection: default use mysqltest_db1; GRANT SELECT ON mysqltest_db1.t1 TO mysqltest_dfn@localhost; REVOKE UPDATE ON mysqltest_db1.t1 FROM mysqltest_dfn@localhost; --connection wl2818_definer_con --echo --echo ---> connection: wl2818_definer_con use mysqltest_db1; DROP TRIGGER trg1; SET @new_sum = 0; SET @old_sum = 0; # INSERT INTO statement; BEFORE timing --echo ---> INSERT INTO statement; BEFORE timing CREATE TRIGGER trg1 BEFORE INSERT ON t1 FOR EACH ROW SET @new_sum = @new_sum + NEW.num_value; --error ER_TABLEACCESS_DENIED_ERROR INSERT INTO t1 VALUES(4); # INSERT INTO statement; AFTER timing --echo ---> INSERT INTO statement; AFTER timing DROP TRIGGER trg1; CREATE TRIGGER trg1 AFTER INSERT ON t1 FOR EACH ROW SET @new_sum = @new_sum + NEW.num_value; --error ER_TABLEACCESS_DENIED_ERROR INSERT INTO t1 VALUES(5); # UPDATE statement; BEFORE timing --echo ---> UPDATE statement; BEFORE timing DROP TRIGGER trg1; CREATE TRIGGER trg1 BEFORE UPDATE ON t1 FOR EACH ROW SET @old_sum = @old_sum + OLD.num_value; --error ER_TABLEACCESS_DENIED_ERROR UPDATE t1 SET num_value = 10; # UPDATE statement; AFTER timing --echo ---> UPDATE statement; AFTER timing DROP TRIGGER trg1; CREATE TRIGGER trg1 AFTER UPDATE ON t1 FOR EACH ROW SET @new_sum = @new_sum + NEW.num_value; --error ER_TABLEACCESS_DENIED_ERROR UPDATE t1 SET num_value = 20; # DELETE statement; BEFORE timing --echo ---> DELETE statement; BEFORE timing DROP TRIGGER trg1; CREATE TRIGGER trg1 BEFORE DELETE ON t1 FOR EACH ROW SET @old_sum = @old_sum + OLD.num_value; --error ER_TABLEACCESS_DENIED_ERROR DELETE FROM t1; # DELETE statement; AFTER timing --echo ---> DELETE statement; AFTER timing DROP TRIGGER trg1; CREATE TRIGGER trg1 AFTER DELETE ON t1 FOR EACH ROW SET @old_sum = @old_sum + OLD.num_value; --error ER_TABLEACCESS_DENIED_ERROR DELETE FROM t1; # # Check DEFINER clause of CREATE TRIGGER statement. # # NOTE: there is no dedicated TRIGGER privilege for CREATE TRIGGER statement. # SUPER privilege is used instead. I.e., if one invokes CREATE TRIGGER, it should # have SUPER privilege, so this test is meaningless right now. # # - Check that SUPER privilege required to create a trigger with different # definer: # - try to create a trigger with DEFINER="definer@localhost" under # "invoker"; # - analyze error code; # - Check that if the user specified as DEFINER does not exist, a warning is # emitted: # - create a trigger with DEFINER="non_existent_user@localhost" from # "definer"; # - check that a warning emitted; # - Check that the definer of a trigger does not exist, the trigger will not # be activated: # - activate just created trigger; # - check error code; # --connection wl2818_definer_con --echo --echo ---> connection: wl2818_definer_con use mysqltest_db1; DROP TRIGGER trg1; # Check that SUPER is required to specify different DEFINER. # NOTE: meaningless at the moment CREATE DEFINER='mysqltest_inv'@'localhost' TRIGGER trg1 BEFORE INSERT ON t1 FOR EACH ROW SET @new_sum = 0; # Create with non-existent user. CREATE DEFINER='mysqltest_nonexs'@'localhost' TRIGGER trg2 AFTER INSERT ON t1 FOR EACH ROW SET @new_sum = 0; # Check that trg2 will not be activated. --error ER_SPECIFIC_ACCESS_DENIED_ERROR INSERT INTO t1 VALUES(6); # # Check that SHOW TRIGGERS statement provides "Definer" column. # SHOW TRIGGERS; # # Check that weird definer values do not break functionality. I.e. check the # following definer values: # - ''; # - '@'; # - '@abc@def@@'; # - '@hostname'; # - '@abc@def@@@hostname'; # DROP TRIGGER trg1; DROP TRIGGER trg2; CREATE TRIGGER trg1 BEFORE INSERT ON t1 FOR EACH ROW SET @a = 1; CREATE TRIGGER trg2 AFTER INSERT ON t1 FOR EACH ROW SET @a = 2; CREATE TRIGGER trg3 BEFORE UPDATE ON t1 FOR EACH ROW SET @a = 3; CREATE TRIGGER trg4 AFTER UPDATE ON t1 FOR EACH ROW SET @a = 4; CREATE TRIGGER trg5 BEFORE DELETE ON t1 FOR EACH ROW SET @a = 5; --exec egrep --text -v '^definers=' $MYSQL_TEST_DIR/var/master-data/mysqltest_db1/t1.TRG > $MYSQL_TEST_DIR/var/tmp/t1.TRG --exec echo "definers='' '@' '@abc@def@@' '@hostname' '@abcdef@@@hostname'" >> $MYSQL_TEST_DIR/var/tmp/t1.TRG --exec mv $MYSQL_TEST_DIR/var/tmp/t1.TRG $MYSQL_TEST_DIR/var/master-data/mysqltest_db1/t1.TRG --echo SELECT trigger_name, definer FROM INFORMATION_SCHEMA.TRIGGERS ORDER BY trigger_name; --echo SELECT * FROM INFORMATION_SCHEMA.TRIGGERS ORDER BY trigger_name; # # Cleanup # --connection default --echo --echo ---> connection: default DROP USER mysqltest_dfn@localhost; DROP USER mysqltest_inv@localhost; DROP DATABASE mysqltest_db1; --- 1.131/mysql-test/r/view.result 2005-11-09 18:50:52 +03:00 +++ 1.132/mysql-test/r/view.result 2005-11-10 22:24:51 +03:00 @@ -2199,10 +2199,10 @@ drop view v1, v2; drop table t1, t2; create definer=some_user@`` sql security invoker view v1 as select 1; -ERROR HY000: View definer is not fully qualified +ERROR HY000: Definer is not fully qualified create definer=some_user@localhost sql security invoker view v1 as select 1; Warnings: -Note 1449 There is not some_user@localhost registered +Note 1449 There is no 'some_user'@'localhost' registered show create view v1; View Create View v1 CREATE ALGORITHM=UNDEFINED DEFINER=`some_user`@`localhost` SQL SECURITY INVOKER VIEW `v1` AS select 1 AS `1` --- 1.5/mysql-test/r/skip_grants.result 2005-10-18 22:42:20 +04:00 +++ 1.6/mysql-test/r/skip_grants.result 2005-11-10 22:24:51 +03:00 @@ -4,7 +4,7 @@ use test; create table t1 (field1 INT); CREATE VIEW v1 AS SELECT field1 FROM t1; -ERROR HY000: View definer is not fully qualified +ERROR HY000: Definer is not fully qualified drop table t1; create procedure f1() select 1; drop procedure f1; --- 1.121/mysql-test/t/view.test 2005-11-09 18:50:52 +03:00 +++ 1.122/mysql-test/t/view.test 2005-11-10 22:24:51 +03:00 @@ -2081,7 +2081,7 @@ # # DEFINER information check # --- error ER_NO_VIEW_USER +-- error ER_MALFORMED_DEFINER create definer=some_user@`` sql security invoker view v1 as select 1; create definer=some_user@localhost sql security invoker view v1 as select 1; show create view v1; --- 1.5/mysql-test/t/skip_grants.test 2005-10-18 22:42:20 +04:00 +++ 1.6/mysql-test/t/skip_grants.test 2005-11-10 22:24:51 +03:00 @@ -9,7 +9,7 @@ # test that we can create VIEW if privileges check switched off # create table t1 (field1 INT); --- error ER_NO_VIEW_USER +-- error ER_MALFORMED_DEFINER CREATE VIEW v1 AS SELECT field1 FROM t1; drop table t1; --- 1.75/sql/sql_view.cc 2005-11-03 17:28:11 +03:00 +++ 1.76/sql/sql_view.cc 2005-11-10 22:24:54 +03:00 @@ -214,29 +214,28 @@ - same as current user - current user has SUPER_ACL */ - if (strcmp(lex->create_view_definer->user.str, + if (strcmp(lex->definer->user.str, thd->security_ctx->priv_user) != 0 || my_strcasecmp(system_charset_info, - lex->create_view_definer->host.str, + lex->definer->host.str, thd->security_ctx->priv_host) != 0) { if (!(thd->security_ctx->master_access & SUPER_ACL)) { - my_error(ER_VIEW_OTHER_USER, MYF(0), lex->create_view_definer->user.str, - lex->create_view_definer->host.str); + my_error(ER_SPECIFIC_ACCESS_DENIED_ERROR, MYF(0), "SUPER"); res= TRUE; goto err; } else { - if (!is_acl_user(lex->create_view_definer->host.str, - lex->create_view_definer->user.str)) + if (!is_acl_user(lex->definer->host.str, + lex->definer->user.str)) { push_warning_printf(thd, MYSQL_ERROR::WARN_LEVEL_NOTE, ER_NO_SUCH_USER, ER(ER_NO_SUCH_USER), - lex->create_view_definer->user.str, - lex->create_view_definer->host.str); + lex->definer->user.str, + lex->definer->host.str); } } } @@ -658,8 +657,8 @@ lex->create_view_algorithm= VIEW_ALGORITHM_UNDEFINED; } view->algorithm= lex->create_view_algorithm; - view->definer.user= lex->create_view_definer->user; - view->definer.host= lex->create_view_definer->host; + view->definer.user= lex->definer->user; + view->definer.host= lex->definer->host; view->view_suid= lex->create_view_suid; view->with_check= lex->create_view_check; if ((view->updatable_view= (can_be_merged && @@ -807,7 +806,7 @@ push_warning_printf(thd, MYSQL_ERROR::WARN_LEVEL_WARN, ER_VIEW_FRM_NO_USER, ER(ER_VIEW_FRM_NO_USER), table->db, table->table_name); - if (default_view_definer(thd->security_ctx, &table->definer)) + if (get_default_definer(thd, &table->definer)) goto err; } --- 1.20/mysql-test/r/trigger.result 2005-09-03 03:13:09 +04:00 +++ 1.21/mysql-test/r/trigger.result 2005-11-10 22:24:51 +03:00 @@ -611,9 +611,9 @@ @a 10 show triggers; -Trigger Event Table Statement Timing Created sql_mode -t1_bi INSERT t1 set new."t1 column" = 5 BEFORE # REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ANSI -t1_af INSERT t1 set @a=10 AFTER # +Trigger Event Table Statement Timing Created sql_mode Definer +t1_bi INSERT t1 set new."t1 column" = 5 BEFORE # REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ANSI root@localhost +t1_af INSERT t1 set @a=10 AFTER # root@localhost drop table t1; set sql_mode="traditional"; create table t1 (a date); @@ -633,8 +633,8 @@ `a` date default NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 show triggers; -Trigger Event Table Statement Timing Created sql_mode -t1_bi INSERT t1 set new.a = '2004-01-00' BEFORE # +Trigger Event Table Statement Timing Created sql_mode Definer +t1_bi INSERT t1 set new.a = '2004-01-00' BEFORE # root@localhost drop table t1; create table t1 (id int); create trigger t1_ai after insert on t1 for each row flush tables; --- 1.30/sql/sql_trigger.cc 2005-10-17 22:37:08 +04:00 +++ 1.31/sql/sql_trigger.cc 2005-11-10 22:24:54 +03:00 @@ -32,15 +32,36 @@ */ static File_option triggers_file_parameters[]= { - {{(char*)"triggers", 8}, + { + { (char *) STRING_WITH_LEN("triggers") }, offsetof(class Table_triggers_list, definitions_list), - FILE_OPTIONS_STRLIST}, - {{(char*)"sql_modes", 13}, + FILE_OPTIONS_STRLIST + }, + { + /* + FIXME: Length specified for "sql_modes" key is erroneous, problem caused + by this are reported as BUG#14090 and should be fixed ASAP. + */ + { (char *) "sql_modes", 13 }, offsetof(class Table_triggers_list, definition_modes_list), - FILE_OPTIONS_ULLLIST}, - {{0, 0}, 0, FILE_OPTIONS_STRING} + FILE_OPTIONS_ULLLIST + }, + { + { (char *) STRING_WITH_LEN("definers") }, + offsetof(class Table_triggers_list, definers_list), + FILE_OPTIONS_STRLIST + }, + { { 0, 0 }, 0, FILE_OPTIONS_STRING } }; +/* + This must be kept up to date whenever a new option is added to the list + above, as it specifies the number of required parameters of the trigger in + .trg file. +*/ + +static const int TRG_NUM_REQUIRED_PARAMETERS= 4; +static const int TRG_MAX_VERSIONS= 3; /* Structure representing contents of .TRN file which are used to support @@ -58,9 +79,16 @@ static File_option trigname_file_parameters[]= { - {{(char*)"trigger_table", 15}, offsetof(struct st_trigname, trigger_table), - FILE_OPTIONS_ESTRING}, - {{0, 0}, 0, FILE_OPTIONS_STRING} + { + /* + FIXME: Length specified for "trigger_table" key is erroneous, problem + caused by this are reported as BUG#14090 and should be fixed ASAP. + */ + { (char *) "trigger_table", 15 }, + offsetof(struct st_trigname, trigger_table), + FILE_OPTIONS_ESTRING + }, + { { 0, 0 }, 0, FILE_OPTIONS_STRING } }; @@ -104,6 +132,9 @@ { TABLE *table; bool result= TRUE; + LEX_STRING definer_user; + LEX_STRING definer_host; + DBUG_ENTER("mysql_create_or_drop_trigger"); /* @@ -184,7 +215,7 @@ } result= (create ? - table->triggers->create_trigger(thd, tables): + table->triggers->create_trigger(thd, tables, &definer_user, &definer_host): table->triggers->drop_trigger(thd, tables)); end: @@ -192,17 +223,30 @@ start_waiting_global_read_lock(thd); if (!result) + { + if (mysql_bin_log.is_open()) { - if (mysql_bin_log.is_open()) + thd->clear_error(); + + String log_query(thd->query, thd->query_length, system_charset_info); + + if (create) { - thd->clear_error(); - /* Such a statement can always go directly to binlog, no trans cache */ - Query_log_event qinfo(thd, thd->query, thd->query_length, 0, FALSE); - mysql_bin_log.write(&qinfo); + log_query.set((char *) 0, 0, system_charset_info); /* reset log_query */ + + log_query.append("CREATE "); + append_definer(thd, &log_query, &definer_user, &definer_host); + log_query.append(thd->lex->trigger_definition_begin); } - send_ok(thd); + + /* Such a statement can always go directly to binlog, no trans cache. */ + Query_log_event qinfo(thd, log_query.ptr(), log_query.length(), 0, FALSE); + mysql_bin_log.write(&qinfo); } + send_ok(thd); + } + DBUG_RETURN(result); } @@ -212,15 +256,26 @@ SYNOPSIS create_trigger() - thd - current thread context (including trigger definition in LEX) - tables - table list containing one open table for which trigger is - created. + thd - current thread context (including trigger definition in + LEX) + tables - table list containing one open table for which the + trigger is created. + definer_user - [out] after a call it points to 0-terminated string, + which contains user name part of the actual trigger + definer. The caller is responsible to provide memory for + storing LEX_STRING object. + definer_host - [out] after a call it points to 0-terminated string, + which contains host name part of the actual trigger + definer. The caller is responsible to provide memory for + storing LEX_STRING object. RETURN VALUE False - success True - error */ -bool Table_triggers_list::create_trigger(THD *thd, TABLE_LIST *tables) +bool Table_triggers_list::create_trigger(THD *thd, TABLE_LIST *tables, + LEX_STRING *definer_user, + LEX_STRING *definer_host) { LEX *lex= thd->lex; TABLE *table= tables->table; @@ -229,6 +284,8 @@ LEX_STRING dir, file, trigname_file; LEX_STRING *trg_def, *name; ulonglong *trg_sql_mode; + char trg_definer_holder[HOSTNAME_LENGTH + USERNAME_LENGTH + 2]; + LEX_STRING *trg_definer; Item_trigger_field *trg_field; struct st_trigname trigname; @@ -250,6 +307,31 @@ } /* + Definer attribute of the Lex instance is always set in sql_yacc.yy when + trigger is created. + */ + + DBUG_ASSERT(lex->definer); + + /* + If the specified definer differs from the current user, we should check + that the current user has SUPER privilege (in order to create trigger + under another user one must have SUPER privilege). + */ + + if (strcmp(lex->definer->user.str, thd->security_ctx->priv_user) || + my_strcasecmp(system_charset_info, + lex->definer->host.str, + thd->security_ctx->priv_host)) + { + if (check_global_access(thd, SUPER_ACL)) + { + my_error(ER_SPECIFIC_ACCESS_DENIED_ERROR, MYF(0), "SUPER"); + return TRUE; + } + } + + /* Let us check if all references to fields in old/new versions of row in this trigger are ok. @@ -318,15 +400,39 @@ definitions_list.push_back(trg_def, &table->mem_root) || !(trg_sql_mode= (ulonglong*)alloc_root(&table->mem_root, sizeof(ulonglong))) || - definition_modes_list.push_back(trg_sql_mode, &table->mem_root)) + definition_modes_list.push_back(trg_sql_mode, &table->mem_root) || + !(trg_definer= (LEX_STRING*) alloc_root(&table->mem_root, + sizeof(LEX_STRING))) || + definers_list.push_back(trg_definer, &table->mem_root)) goto err_with_cleanup; trg_def->str= thd->query; trg_def->length= thd->query_length; *trg_sql_mode= thd->variables.sql_mode; +#ifndef NO_EMBEDDED_ACCESS_CHECKS + if (!is_acl_user(lex->definer->host.str, + lex->definer->user.str)) + { + push_warning_printf(thd, + MYSQL_ERROR::WARN_LEVEL_NOTE, + ER_NO_SUCH_USER, + ER(ER_NO_SUCH_USER), + lex->definer->user.str, + lex->definer->host.str); + } +#endif /* NO_EMBEDDED_ACCESS_CHECKS */ + + *definer_user= lex->definer->user; + *definer_host= lex->definer->host; + + trg_definer->str= trg_definer_holder; + trg_definer->length= strxmov(trg_definer->str, definer_user->str, "@", + definer_host->str, NullS) - trg_definer->str; + if (!sql_create_definition_file(&dir, &file, &triggers_file_type, - (gptr)this, triggers_file_parameters, 3)) + (gptr)this, triggers_file_parameters, + TRG_MAX_VERSIONS)) return 0; err_with_cleanup: @@ -403,12 +509,14 @@ List_iterator_fast<LEX_STRING> it_name(names_list); List_iterator<LEX_STRING> it_def(definitions_list); List_iterator<ulonglong> it_mod(definition_modes_list); + List_iterator<LEX_STRING> it_definer(definers_list); char path[FN_REFLEN]; while ((name= it_name++)) { it_def++; it_mod++; + it_definer++; if (my_strcasecmp(table_alias_charset, lex->spname->m_name.str, name->str) == 0) @@ -419,6 +527,7 @@ */ it_def.remove(); it_mod.remove(); + it_definer.remove(); if (definitions_list.is_empty()) { @@ -446,7 +555,7 @@ if (sql_create_definition_file(&dir, &file, &triggers_file_type, (gptr)this, triggers_file_parameters, - 3)) + TRG_MAX_VERSIONS)) return 1; } @@ -568,7 +677,7 @@ DBUG_RETURN(0); /* - File exists so we got to load triggers + File exists so we got to load triggers. FIXME: A lot of things to do here e.g. how about other funcs and being more paranoical ? */ @@ -584,13 +693,16 @@ DBUG_RETURN(1); /* - We don't have sql_modes in old versions of .TRG file, so we should - initialize list for safety. + We don't have the following attributes in old versions of .TRG file, so + we should initialize the list for safety: + - sql_modes; + - definers; */ triggers->definition_modes_list.empty(); + triggers->definers_list.empty(); if (parser->parse((gptr)triggers, &table->mem_root, - triggers_file_parameters, 2)) + triggers_file_parameters, TRG_NUM_REQUIRED_PARAMETERS)) DBUG_RETURN(1); List_iterator_fast<LEX_STRING> it(triggers->definitions_list); @@ -612,7 +724,7 @@ DBUG_RETURN(1); // EOM } *trg_sql_mode= global_system_variables.sql_mode; - while ((trg_create_str= it++)) + while (it++) { if (triggers->definition_modes_list.push_back(trg_sql_mode, &table->mem_root)) @@ -623,8 +735,43 @@ it.rewind(); } + if (triggers->definers_list.is_empty() && + !triggers->definitions_list.is_empty()) + { + /* + It is old file format => we should fill list of definers. + + If there is no definer information, we should not switch context to + definer when checking privileges. I.e. privileges for such triggers + are checked for "invoker" rather than for "definer". + */ + + LEX_STRING *trg_definer; + + if (! (trg_definer= (LEX_STRING*)alloc_root(&table->mem_root, + sizeof(LEX_STRING)))) + DBUG_RETURN(1); // EOM + + trg_definer->str= ""; + trg_definer->length= 0; + + while (it++) + { + if (triggers->definers_list.push_back(trg_definer, + &table->mem_root)) + { + DBUG_RETURN(1); // EOM + } + } + + it.rewind(); + } + DBUG_ASSERT(triggers->definition_modes_list.elements == triggers->definitions_list.elements); + DBUG_ASSERT(triggers->definers_list.elements == + triggers->definitions_list.elements); + table->triggers= triggers; /* @@ -647,6 +794,8 @@ char *trg_name_buff; List_iterator_fast<ulonglong> itm(triggers->definition_modes_list); + List_iterator_fast<LEX_STRING> it_definer(triggers-> + definers_list); LEX *old_lex= thd->lex, lex; ulong save_sql_mode= thd->variables.sql_mode; @@ -659,22 +808,55 @@ while ((trg_create_str= it++)) { trg_sql_mode= itm++; + LEX_STRING *trg_definer= it_definer++; thd->variables.sql_mode= (ulong)*trg_sql_mode; lex_start(thd, (uchar*)trg_create_str->str, trg_create_str->length); if (yyparse((void *)thd) || thd->is_fatal_error) { /* - Free lex associated resources + Free lex associated resources. QQ: Do we really need all this stuff here ? */ delete lex.sphead; goto err_with_lex_cleanup; } - lex.sphead->m_sql_mode= *trg_sql_mode; + lex.sphead->set_info(0, 0, &lex.sp_chistics, *trg_sql_mode); + triggers->bodies[lex.trg_chistics.event] [lex.trg_chistics.action_time]= lex.sphead; + + if (!trg_definer->length) + { + /* + This trigger was created/imported from the previous version of + MySQL, which does not support triggers definers. We should emit + warning here. + */ + + push_warning_printf(thd, MYSQL_ERROR::WARN_LEVEL_WARN, + ER_TRG_NO_DEFINER, ER(ER_TRG_NO_DEFINER), + (const char*) db, + (const char*) lex.sphead->m_name.str); + + /* + Set definer to the '' to correct displaying in the information + schema. + */ + + lex.sphead->set_definer("", 0); + + /* + Triggers without definer information are executed under the + authorization of the invoker. + */ + + lex.sphead->m_chistics->suid= SP_IS_NOT_SUID; + } + else + lex.sphead->set_definer(trg_definer->str, trg_definer->length); + if (triggers->names_list.push_back(&lex.sphead->m_name, &table->mem_root)) goto err_with_lex_cleanup; @@ -701,6 +883,10 @@ trg_field= trg_field->next_trg_field) trg_field->setup_field(thd, table); + triggers->m_spec_var_used[lex.trg_chistics.event] + [lex.trg_chistics.action_time]= + lex.trg_table_fields.first ? TRUE : FALSE; + lex_end(&lex); } thd->db= save_db.str; @@ -744,6 +930,9 @@ name - returns name of trigger stmt - returns statement of trigger sql_mode - returns sql_mode of trigger + definer_user - returns definer/creator of trigger. The caller is + responsible to allocate enough space for storing definer + information. RETURN VALUE False - success @@ -754,7 +943,8 @@ trg_action_time_type time_type, LEX_STRING *trigger_name, LEX_STRING *trigger_stmt, - ulong *sql_mode) + ulong *sql_mode, + LEX_STRING *definer) { sp_head *body; DBUG_ENTER("get_trigger_info"); @@ -763,6 +953,18 @@ *trigger_name= body->m_name; *trigger_stmt= body->m_body; *sql_mode= body->m_sql_mode; + + if (body->m_chistics->suid == SP_IS_NOT_SUID) + { + definer->str[0]= 0; + definer->length= 0; + } + else + { + definer->length= strxmov(definer->str, body->m_definer_user.str, "@", + body->m_definer_host.str, NullS) - definer->str; + } + DBUG_RETURN(0); } DBUG_RETURN(1); @@ -898,8 +1100,9 @@ bool old_row_is_record1) { int res= 0; + sp_head *sp_trigger= bodies[event][time_type]; - if (bodies[event][time_type]) + if (sp_trigger) { Sub_statement_state statement_state; @@ -914,14 +1117,54 @@ old_field= table->field; } +#ifndef NO_EMBEDDED_ACCESS_CHECKS + Security_context *save_ctx; + + if (sp_change_security_context(thd, sp_trigger, &save_ctx)) + return TRUE; + + /* + NOTE: TRIGGER_ACL should be used below. + */ + + if (check_global_access(thd, SUPER_ACL)) + { + sp_restore_security_context(thd, save_ctx); + return TRUE; + } + /* - FIXME: We should juggle with security context here (because trigger - should be invoked with creator rights). + If the trigger uses special variables (NEW/OLD), check that we have + SELECT and UPDATE privileges on the subject table. */ + + if (is_special_var_used(event, time_type)) + { + TABLE_LIST table_list; + bzero((char *) &table_list, sizeof (table_list)); + table_list.db= (char *) table->s->db; + table_list.db_length= strlen(table_list.db); + table_list.table_name= (char *) table->s->table_name; + table_list.table_name_length= strlen(table_list.table_name); + table_list.alias= (char *) table->alias; + table_list.table= table; + + if (check_table_access(thd, SELECT_ACL | UPDATE_ACL, &table_list, 0)) + { + sp_restore_security_context(thd, save_ctx); + return TRUE; + } + } + +#endif // NO_EMBEDDED_ACCESS_CHECKS thd->reset_sub_statement_state(&statement_state, SUB_STMT_TRIGGER); - res= bodies[event][time_type]->execute_function(thd, 0, 0, 0); + res= sp_trigger->execute_function(thd, 0, 0, 0); thd->restore_sub_statement_state(&statement_state); + +#ifndef NO_EMBEDDED_ACCESS_CHECKS + sp_restore_security_context(thd, save_ctx); +#endif // NO_EMBEDDED_ACCESS_CHECKS } return res; --- 1.14/sql/sql_trigger.h 2005-09-15 03:56:04 +04:00 +++ 1.15/sql/sql_trigger.h 2005-11-10 22:24:54 +03:00 @@ -55,6 +55,12 @@ */ LEX_STRING sroutines_key; + /* + is_special_var_used specifies whether trigger body contains special + variables (NEW/OLD). + */ + bool m_spec_var_used[TRG_EVENT_MAX][TRG_ACTION_MAX]; + public: /* Field responsible for storing triggers definitions in file. @@ -66,6 +72,8 @@ */ List<ulonglong> definition_modes_list; + List<LEX_STRING> definers_list; + Table_triggers_list(TABLE *table_arg): record1_field(0), table(table_arg) { @@ -73,7 +81,9 @@ } ~Table_triggers_list(); - bool create_trigger(THD *thd, TABLE_LIST *table); + bool create_trigger(THD *thd, TABLE_LIST *table, + LEX_STRING *definer_user, + LEX_STRING *definer_host); bool drop_trigger(THD *thd, TABLE_LIST *table); bool process_triggers(THD *thd, trg_event_type event, trg_action_time_type time_type, @@ -81,7 +91,8 @@ bool get_trigger_info(THD *thd, trg_event_type event, trg_action_time_type time_type, LEX_STRING *trigger_name, LEX_STRING *trigger_stmt, - ulong *sql_mode); + ulong *sql_mode, + LEX_STRING *definer); static bool check_n_load(THD *thd, const char *db, const char *table_name, TABLE *table, bool names_only); @@ -96,6 +107,11 @@ bool has_before_update_triggers() { return test(bodies[TRG_EVENT_UPDATE][TRG_ACTION_BEFORE]); + } + + inline bool is_special_var_used(int event, int action_time) const + { + return m_spec_var_used[event][action_time]; } void set_table(TABLE *new_table); --- 1.9/mysql-test/r/rpl_sp.result 2005-10-12 23:42:40 +04:00 +++ 1.10/mysql-test/r/rpl_sp.result 2005-11-10 22:24:51 +03:00 @@ -256,7 +256,7 @@ show binlog events in 'master-bin.000002' from 98; Log_name Pos Event_type Server_id End_log_pos Info master-bin.000002 # Query 1 # use `mysqltest1`; delete from t1 -master-bin.000002 # Query 1 # use `mysqltest1`; create trigger trg before insert on t1 for each row set new.a= 10 +master-bin.000002 # Query 1 # use `mysqltest1`; CREATE DEFINER=`root`@`localhost` trigger trg before insert on t1 for each row set new.a= 10 master-bin.000002 # Query 1 # use `mysqltest1`; insert into t1 values (1) master-bin.000002 # Query 1 # use `mysqltest1`; delete from t1 master-bin.000002 # Query 1 # use `mysqltest1`; drop trigger trg --- 1.87/mysql-test/r/information_schema.result 2005-10-28 01:24:02 +04:00 +++ 1.88/mysql-test/r/information_schema.result 2005-11-10 22:24:50 +03:00 @@ -722,6 +722,7 @@ information_schema TRIGGERS ACTION_CONDITION information_schema TRIGGERS ACTION_STATEMENT information_schema TRIGGERS SQL_MODE +information_schema TRIGGERS DEFINER information_schema VIEWS VIEW_DEFINITION select table_name, column_name, data_type from information_schema.columns where data_type = 'datetime'; @@ -800,45 +801,45 @@ end if; end| show triggers; -Trigger Event Table Statement Timing Created sql_mode +Trigger Event Table Statement Timing Created sql_mode Definer trg1 INSERT t1 begin if new.j > 10 then set new.j := 10; end if; -end BEFORE NULL +end BEFORE NULL root@localhost trg2 UPDATE t1 begin if old.i % 2 = 0 then set new.j := -1; end if; -end BEFORE NULL +end BEFORE NULL root@localhost trg3 UPDATE t1 begin if new.j = -1 then set @fired:= "Yes"; end if; -end AFTER NULL +end AFTER NULL root@localhost select * from information_schema.triggers; -TRIGGER_CATALOG TRIGGER_SCHEMA TRIGGER_NAME EVENT_MANIPULATION EVENT_OBJECT_CATALOG EVENT_OBJECT_SCHEMA EVENT_OBJECT_TABLE ACTION_ORDER ACTION_CONDITION ACTION_STATEMENT ACTION_ORIENTATION ACTION_TIMING ACTION_REFERENCE_OLD_TABLE ACTION_REFERENCE_NEW_TABLE ACTION_REFERENCE_OLD_ROW ACTION_REFERENCE_NEW_ROW CREATED SQL_MODE +TRIGGER_CATALOG TRIGGER_SCHEMA TRIGGER_NAME EVENT_MANIPULATION EVENT_OBJECT_CATALOG EVENT_OBJECT_SCHEMA EVENT_OBJECT_TABLE ACTION_ORDER ACTION_CONDITION ACTION_STATEMENT ACTION_ORIENTATION ACTION_TIMING ACTION_REFERENCE_OLD_TABLE ACTION_REFERENCE_NEW_TABLE ACTION_REFERENCE_OLD_ROW ACTION_REFERENCE_NEW_ROW CREATED SQL_MODE DEFINER NULL test trg1 INSERT NULL test t1 0 NULL begin if new.j > 10 then set new.j := 10; end if; -end ROW BEFORE NULL NULL OLD NEW NULL +end ROW BEFORE NULL NULL OLD NEW NULL root@localhost NULL test trg2 UPDATE NULL test t1 0 NULL begin if old.i % 2 = 0 then set new.j := -1; end if; -end ROW BEFORE NULL NULL OLD NEW NULL +end ROW BEFORE NULL NULL OLD NEW NULL root@localhost NULL test trg3 UPDATE NULL test t1 0 NULL begin if new.j = -1 then set @fired:= "Yes"; end if; -end ROW AFTER NULL NULL OLD NEW NULL +end ROW AFTER NULL NULL OLD NEW NULL root@localhost drop trigger trg1; drop trigger trg2; drop trigger trg3; --- 1.6/mysql-test/r/view_grant.result 2005-10-28 16:54:56 +04:00 +++ 1.7/mysql-test/r/view_grant.result 2005-11-10 22:24:51 +03:00 @@ -13,7 +13,7 @@ grant select on mysqltest.t1 to mysqltest_1@localhost; grant create view,select on test.* to mysqltest_1@localhost; create definer=root@localhost view v1 as select * from mysqltest.t1; -ERROR HY000: You need the SUPER privilege for creation view with root@localhost definer +ERROR 42000: Access denied; you need the SUPER privilege for this operation create view v1 as select * from mysqltest.t1; alter view v1 as select * from mysqltest.t1; ERROR 42000: DROP command denied to user 'mysqltest_1'@'localhost' for table 'v1' --- 1.6/mysql-test/t/view_grant.test 2005-10-28 16:54:56 +04:00 +++ 1.7/mysql-test/t/view_grant.test 2005-11-10 22:24:51 +03:00 @@ -24,7 +24,7 @@ connect (user1,localhost,mysqltest_1,,test); connection user1; --- error ER_VIEW_OTHER_USER +-- error ER_SPECIFIC_ACCESS_DENIED create definer=root@localhost view v1 as select * from mysqltest.t1; create view v1 as select * from mysqltest.t1; # try to modify view without DROP privilege on it --- 1.8/mysql-test/r/rpl_ddl.result 2005-10-13 13:12:07 +04:00 +++ 1.9/mysql-test/r/rpl_ddl.result 2005-11-10 22:24:51 +03:00 @@ -1465,13 +1465,13 @@ -------- switch to master ------- SHOW TRIGGERS; -Trigger Event Table Statement Timing Created sql_mode -trg1 INSERT t1 SET @a:=1 BEFORE NULL +Trigger Event Table Statement Timing Created sql_mode Definer +trg1 INSERT t1 SET @a:=1 BEFORE NULL root@localhost -------- switch to slave ------- SHOW TRIGGERS; -Trigger Event Table Statement Timing Created sql_mode -trg1 INSERT t1 SET @a:=1 BEFORE NULL +Trigger Event Table Statement Timing Created sql_mode Definer +trg1 INSERT t1 SET @a:=1 BEFORE NULL root@localhost ######## DROP TRIGGER trg1 ######## @@ -1520,11 +1520,11 @@ -------- switch to master ------- SHOW TRIGGERS; -Trigger Event Table Statement Timing Created sql_mode +Trigger Event Table Statement Timing Created sql_mode Definer -------- switch to slave ------- SHOW TRIGGERS; -Trigger Event Table Statement Timing Created sql_mode +Trigger Event Table Statement Timing Created sql_mode Definer ######## CREATE USER user1@localhost ######## --- 1.1/mysql-test/r/rpl_trigger.result 2005-08-15 19:15:05 +04:00 +++ 1.2/mysql-test/r/rpl_trigger.result 2005-11-10 22:24:51 +03:00 @@ -89,8 +89,24 @@ select a=b && a=c from t1; a=b && a=c 1 +SELECT routine_name, definer +FROM information_schema.routines; +routine_name definer +bug12480 root@localhost +SELECT trigger_name, definer +FROM information_schema.triggers; +trigger_name definer +t1_first root@localhost --- On slave -- +SELECT routine_name, definer +FROM information_schema.routines; +routine_name definer +bug12480 @ +SELECT trigger_name, definer +FROM information_schema.triggers; +trigger_name definer +t1_first root@localhost select a=b && a=c from t1; a=b && a=c 1 --- 1.2/mysql-test/t/rpl_trigger.test 2005-08-15 21:08:54 +04:00 +++ 1.3/mysql-test/t/rpl_trigger.test 2005-11-10 22:24:51 +03:00 @@ -87,12 +87,35 @@ select a=b && a=c from t1; let $time=`select a from t1`; +# Check that definer attribute is replicated properly: +# - dump definers on the master; +# - wait for the slave to synchronize with the master; +# - dump definers on the slave; + +SELECT routine_name, definer +FROM information_schema.routines; + +SELECT trigger_name, definer +FROM information_schema.triggers; + save_master_pos; connection slave; sync_with_master; --disable_query_log select "--- On slave --" as ""; --enable_query_log + +# XXX: Definers of stored procedures and functions are not replicated. WL#2897 +# (Complete definer support in the stored routines) addresses this issue. So, +# the result file is expected to be changed after implementation of this WL +# item. + +SELECT routine_name, definer +FROM information_schema.routines; + +SELECT trigger_name, definer +FROM information_schema.triggers; + select a=b && a=c from t1; --disable_query_log eval select a='$time' as 'test' from t1; --- 1.96/sql/sp.cc 2005-10-07 04:37:20 +04:00 +++ 1.97/sql/sp.cc 2005-11-10 22:24:52 +03:00 @@ -441,8 +441,8 @@ if (dbchanged && (ret= mysql_change_db(thd, olddb, 1))) goto done; *sphp= thd->lex->sphead; - (*sphp)->set_info((char *)definer, (uint)strlen(definer), - created, modified, &chistics, sql_mode); + (*sphp)->set_definer((char*) definer, (uint) strlen(definer)); + (*sphp)->set_info(created, modified, &chistics, sql_mode); (*sphp)->optimize(); } thd->lex->sql_command= oldcmd; --- 1.193/sql/sp_head.cc 2005-10-21 15:07:50 +04:00 +++ 1.194/sql/sp_head.cc 2005-11-10 22:24:52 +03:00 @@ -1569,21 +1569,9 @@ } void -sp_head::set_info(char *definer, uint definerlen, - longlong created, longlong modified, +sp_head::set_info(longlong created, longlong modified, st_sp_chistics *chistics, ulong sql_mode) { - char *p= strchr(definer, '@'); - uint len; - - if (! p) - p= definer; // Weird... - len= p-definer; - m_definer_user.str= strmake_root(mem_root, definer, len); - m_definer_user.length= len; - len= definerlen-len-1; - m_definer_host.str= strmake_root(mem_root, p+1, len); - m_definer_host.length= len; m_created= created; m_modified= modified; m_chistics= (st_sp_chistics *) memdup_root(mem_root, (char*) chistics, @@ -1596,6 +1584,34 @@ m_chistics->comment.length); m_sql_mode= sql_mode; } + + +void +sp_head::set_definer(char *definer, uint definerlen) +{ + char *p= strrchr(definer, '@'); + + if (!p) + { + m_definer_user.str= strmake_root(mem_root, "", 0); + m_definer_user.length= 0; + + m_definer_host.str= strmake_root(mem_root, "", 0); + m_definer_host.length= 0; + } + else + { + const uint user_name_len= p - definer; + const uint host_name_len= definerlen - user_name_len - 1; + + m_definer_user.str= strmake_root(mem_root, definer, user_name_len); + m_definer_user.length= user_name_len; + + m_definer_host.str= strmake_root(mem_root, p + 1, host_name_len); + m_definer_host.length= host_name_len; + } +} + void sp_head::reset_thd_mem_root(THD *thd) --- 1.73/sql/sp_head.h 2005-09-23 00:46:49 +04:00 +++ 1.74/sql/sp_head.h 2005-11-10 22:24:52 +03:00 @@ -251,9 +251,10 @@ Field *make_field(uint max_length, const char *name, TABLE *dummy); - void set_info(char *definer, uint definerlen, - longlong created, longlong modified, + void set_info(longlong created, longlong modified, st_sp_chistics *chistics, ulong sql_mode); + + void set_definer(char *definer, uint definerlen); void reset_thd_mem_root(THD *thd); --- 1.80/mysql-test/r/mysqldump.result 2005-11-04 11:02:40 +03:00 +++ 1.81/mysql-test/r/mysqldump.result 2005-11-10 22:24:51 +03:00 @@ -1926,23 +1926,23 @@ end| set sql_mode=default| show triggers like "t1"; -Trigger Event Table Statement Timing Created sql_mode +Trigger Event Table Statement Timing Created sql_mode Definer trg1 INSERT t1 begin if new.a > 10 then set new.a := 10; set new.a := 11; end if; -end BEFORE 0000-00-00 00:00:00 +end BEFORE 0000-00-00 00:00:00 root@localhost trg2 UPDATE t1 begin if old.a % 2 = 0 then set new.b := 12; end if; -end BEFORE 0000-00-00 00:00:00 +end BEFORE 0000-00-00 00:00:00 root@localhost trg3 UPDATE t1 begin if new.a = -1 then set @fired:= "Yes"; end if; -end AFTER 0000-00-00 00:00:00 STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_AUTO_CREATE_USER +end AFTER 0000-00-00 00:00:00 STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_AUTO_CREATE_USER root@localhost INSERT INTO t1 (a) VALUES (1),(2),(3),(22); update t1 set a = 4 where a=3; @@ -2085,29 +2085,29 @@ t1 t2 show triggers; -Trigger Event Table Statement Timing Created sql_mode +Trigger Event Table Statement Timing Created sql_mode Definer trg1 INSERT t1 begin if new.a > 10 then set new.a := 10; set new.a := 11; end if; -end BEFORE # +end BEFORE # root@localhost trg2 UPDATE t1 begin if old.a % 2 = 0 then set new.b := 12; end if; -end BEFORE # +end BEFORE # root@localhost trg3 UPDATE t1 begin if new.a = -1 then set @fired:= "Yes"; end if; -end AFTER # STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_AUTO_CREATE_USER +end AFTER # STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_AUTO_CREATE_USER root@localhost trg4 INSERT t2 begin if new.a > 10 then set @fired:= "No"; end if; -end BEFORE # STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_AUTO_CREATE_USER +end BEFORE # STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_AUTO_CREATE_USER root@localhost DROP TABLE t1, t2; --port=1234 --port=1234 @@ -2130,9 +2130,9 @@ a2 1 SHOW TRIGGERS; -Trigger Event Table Statement Timing Created sql_mode +Trigger Event Table Statement Timing Created sql_mode Definer testref INSERT test1 BEGIN -INSERT INTO test2 SET a2 = NEW.a1; END BEFORE NULL +INSERT INTO test2 SET a2 = NEW.a1; END BEFORE NULL root@localhost SELECT * FROM `test1`; a1 1 @@ -2147,6 +2147,7 @@ DROP FUNCTION IF EXISTS bug9056_func2; DROP PROCEDURE IF EXISTS bug9056_proc1; DROP PROCEDURE IF EXISTS bug9056_proc2; +DROP PROCEDURE IF EXISTS `a'b`; CREATE TABLE t1 (id int); INSERT INTO t1 VALUES(1), (2), (3), (4), (5); CREATE FUNCTION `bug9056_func1`(a INT, b INT) RETURNS int(11) RETURN a+b // --- 1.73/mysql-test/t/mysqldump.test 2005-11-04 11:02:40 +03:00 +++ 1.74/mysql-test/t/mysqldump.test 2005-11-10 22:24:51 +03:00 @@ -882,6 +882,7 @@ DROP FUNCTION IF EXISTS bug9056_func2; DROP PROCEDURE IF EXISTS bug9056_proc1; DROP PROCEDURE IF EXISTS bug9056_proc2; +DROP PROCEDURE IF EXISTS `a'b`; --enable_warnings CREATE TABLE t1 (id int);
| Thread | ||
|---|---|---|
| • bk commit into 5.0 tree (anozdrin:1.1960) | Alexander Nozdrin | 10 Nov |
© 1995-2008 MySQL AB, 2008-2009 Sun Microsystems, Inc.
Page generated in 1.171 sec. using MySQL 5.1.14-beta-log