From: Date: September 19 2005 1:19am Subject: bk commit into 5.0 tree (bell:1.1965) BUG#9505 List-Archive: http://lists.mysql.com/internals/30026 X-Bug: 9505 Message-Id: <20050918231916.E1878456071@sanja.is.com.ua> Below is the list of changes that have just been committed into a local 5.0 repository of bell. When bell 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.1965 05/09/19 02:19:08 bell@stripped +24 -0 WL#2787 (part3) underlying view object privileges check BUG#9505 fix sql/table.h 1.115 05/09/19 02:19:04 bell@stripped +26 -5 relamed methods and fields preparing underlying table privileges check sql/table.cc 1.190 05/09/19 02:19:04 bell@stripped +170 -29 relamed methods and fields preparing underlying table privileges check sql/sql_view.cc 1.65 05/09/19 02:19:04 bell@stripped +30 -5 preparing underlying table privileges check sql/sql_update.cc 1.171 05/09/19 02:19:03 bell@stripped +5 -1 preparing underlying table privileges check sql/sql_prepare.cc 1.155 05/09/19 02:19:03 bell@stripped +4 -1 preparing underlying table privileges check sql/sql_parse.cc 1.490 05/09/19 02:19:03 bell@stripped +2 -0 registration of wanted access for underlying tables sql/sql_lex.h 1.198 05/09/19 02:19:03 bell@stripped +1 -0 comment added sql/sql_derived.cc 1.77 05/09/19 02:19:03 bell@stripped +24 -24 we have not -1/0/1 result codes sql/sql_delete.cc 1.162 05/09/19 02:19:03 bell@stripped +3 -2 renamed list sql/sql_cache.cc 1.85 05/09/19 02:19:03 bell@stripped +7 -5 renamed list sql/sql_base.cc 1.304 05/09/19 02:19:03 bell@stripped +113 -27 addeed check of definer rights in underlying tables sql/sql_acl.cc 1.172 05/09/19 02:19:03 bell@stripped +31 -5 debug output added registration of wanted access for underlying tables sql/share/errmsg.txt 1.45 05/09/19 02:19:03 bell@stripped +2 -2 error message changed sql/mysql_priv.h 1.353 05/09/19 02:19:03 bell@stripped +8 -6 we have not -1/0/1 result codes security context added sql/item_func.cc 1.253 05/09/19 02:19:03 bell@stripped +13 -5 Name resolution context refers to security context now (for view underlying SP) sql/item.h 1.169 05/09/19 02:19:03 bell@stripped +7 -4 Name resolution context refers to security context now (for view underlying SP) sql/item.cc 1.180 05/09/19 02:19:02 bell@stripped +6 -10 Name resolution context refers to security context now (for view underlying SP) mysql-test/t/view_grant.test 1.5 05/09/19 02:19:02 bell@stripped +62 -0 tests of underlying object check mysql-test/t/sql_mode.test 1.14 05/09/19 02:19:02 bell@stripped +2 -1 fixed reseting environment mysql-test/r/view_grant.result 1.5 05/09/19 02:19:02 bell@stripped +39 -0 tests of underlying object check mysql-test/r/view.result 1.118 05/09/19 02:19:02 bell@stripped +12 -12 error message changed mysql-test/r/sql_mode.result 1.29 05/09/19 02:19:02 bell@stripped +2 -1 fixed reseting environment mysql-test/r/sp.result 1.156 05/09/19 02:19:02 bell@stripped +1 -1 error message changed mysql-test/r/information_schema.result 1.81 05/09/19 02:19:02 bell@stripped +6 -6 error message changed # 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: bell # Host: sanja.is.com.ua # Root: /home/bell/mysql/bk/work-owner4-5.0 --- 1.179/sql/item.cc 2005-09-15 22:29:00 +03:00 +++ 1.180/sql/item.cc 2005-09-19 02:19:02 +03:00 @@ -3206,8 +3206,7 @@ context->last_name_resolution_table, reference, IGNORE_EXCEPT_NON_UNIQUE, - !any_privileges && - context->check_privileges, + !any_privileges, TRUE)) == not_found_field) { @@ -3253,9 +3252,7 @@ last_name_resolution_table, reference, IGNORE_EXCEPT_NON_UNIQUE, - outer_context-> - check_privileges, - TRUE)) != + TRUE, TRUE)) != not_found_field) { if (from_field) @@ -3330,7 +3327,7 @@ context->last_name_resolution_table, reference, REPORT_ALL_ERRORS, !any_privileges && - context->check_privileges, TRUE); + TRUE, TRUE); } goto error; } @@ -3405,7 +3402,7 @@ We can leave expression substituted from view for next PS/SP rexecution (i.e. do not register this substitution for reverting on cleupup() (register_item_tree_changing())), because this subtree will be - fix_field'ed during setup_tables()->setup_ancestor() (i.e. before + fix_field'ed during setup_tables()->setup_underlying() (i.e. before all other expressions of query, and references on tables which do not present in query will not make problems. @@ -4500,8 +4497,7 @@ last_name_resolution_table, reference, IGNORE_EXCEPT_NON_UNIQUE, - outer_context->check_privileges, - TRUE); + TRUE, TRUE); if (! from_field) goto error; if (from_field == view_ref_found) @@ -5114,7 +5110,7 @@ set field_idx properly. */ (void)find_field_in_table(thd, table, field_name, (uint) strlen(field_name), - 0, 0, &field_idx); + 0, 0, &field_idx, 0); thd->set_query_id= save_set_query_id; triggers= table->triggers; } --- 1.168/sql/item.h 2005-09-08 19:25:37 +03:00 +++ 1.169/sql/item.h 2005-09-19 02:19:03 +03:00 @@ -291,15 +291,18 @@ bool resolve_in_select_list; /* - When FALSE we do not check columns right of resolving items, used to - prevent rights check on underlying tables of view + security context of this name resolution (used for views) */ - bool check_privileges; + st_security_context *security_ctx; + /* + We have to use above st_security_context for this name resolution context + */ + bool suid; Name_resolution_context() :outer_context(0), table_list(0), select_lex(0), error_processor_data(0), - check_privileges(TRUE) + security_ctx(0), suid(FALSE) {} void init() --- 1.252/sql/item_func.cc 2005-09-15 22:29:00 +03:00 +++ 1.253/sql/item_func.cc 2005-09-19 02:19:03 +03:00 @@ -4713,7 +4713,7 @@ Sub_statement_state statement_state; #ifndef NO_EMBEDDED_ACCESS_CHECKS - st_security_context *save_ctx; + st_security_context *save_security_ctx= 0, *save_ctx_func; #endif if (! m_sp && ! (m_sp= sp_find_function(thd, m_name, TRUE))) @@ -4723,12 +4723,18 @@ } #ifndef NO_EMBEDDED_ACCESS_CHECKS + if (context->security_ctx) + { + /* Set view definer security context */ + save_security_ctx= thd->security_ctx; + thd->security_ctx= context->security_ctx; + } if (check_routine_access(thd, EXECUTE_ACL, m_sp->m_db.str, m_sp->m_name.str, 0, 0) || - sp_change_security_context(thd, m_sp, &save_ctx)) + sp_change_security_context(thd, m_sp, &save_ctx_func)) goto error; - if (save_ctx && - check_routine_access(thd, EXECUTE_ACL, + if (save_ctx_func && + check_routine_access(thd, EXECUTE_ACL, m_sp->m_db.str, m_sp->m_name.str, 0, 0)) goto error_check_ctx; #endif @@ -4750,7 +4756,9 @@ #ifndef NO_EMBEDDED_ACCESS_CHECKS error_check_ctx: - sp_restore_security_context(thd, save_ctx); + sp_restore_security_context(thd, save_ctx_func); + if (save_security_ctx) + thd->security_ctx= save_security_ctx; #endif error: --- 1.352/sql/mysql_priv.h 2005-09-15 22:29:00 +03:00 +++ 1.353/sql/mysql_priv.h 2005-09-19 02:19:03 +03:00 @@ -678,11 +678,11 @@ select_result *result); bool mysql_union(THD *thd, LEX *lex, select_result *result, SELECT_LEX_UNIT *unit, ulong setup_tables_done_option); -int mysql_handle_derived(LEX *lex, int (*processor)(THD *thd, - LEX *lex, - TABLE_LIST *table)); -int mysql_derived_prepare(THD *thd, LEX *lex, TABLE_LIST *t); -int mysql_derived_filling(THD *thd, LEX *lex, TABLE_LIST *t); +bool mysql_handle_derived(LEX *lex, bool (*processor)(THD *thd, + LEX *lex, + TABLE_LIST *table)); +bool mysql_derived_prepare(THD *thd, LEX *lex, TABLE_LIST *t); +bool mysql_derived_filling(THD *thd, LEX *lex, TABLE_LIST *t); Field *create_tmp_field(THD *thd, TABLE *table,Item *item, Item::Type type, Item ***copy_func, Field **from_field, bool group, bool modify_item, @@ -792,7 +792,9 @@ Field * find_field_in_table(THD *thd, TABLE *table, const char *name, uint length, bool check_grants, bool allow_rowid, - uint *cached_field_index_ptr); + uint *cached_field_index_ptr, + st_security_context *sctx); + #ifdef HAVE_OPENSSL #include struct st_des_keyblock --- 1.171/sql/sql_acl.cc 2005-09-15 22:29:01 +03:00 +++ 1.172/sql/sql_acl.cc 2005-09-19 02:19:03 +03:00 @@ -919,6 +919,9 @@ ACL_USER *acl_user= 0; DBUG_ENTER("acl_getroot_no_password"); + DBUG_PRINT("enter", ("Host: '%s', Ip: '%s', User: '%s', db: '%s'", + (host ? host : "(NULL)"), (ip ? ip : "(NULL)"), + (user ? user : "(NULL)"), (db ? db : "(NULL)"))); sctx->user= user; sctx->host= host; sctx->ip= ip; @@ -3493,10 +3496,16 @@ uint show_table, uint number, bool no_errors) { TABLE_LIST *table; + uint i= 0; st_security_context *sctx= thd->security_ctx; DBUG_ENTER("check_grant"); DBUG_ASSERT(number > 0); + for (table= tables; table && i < number; table= table->next_global) + { + table->register_want_access(want_access); + } + want_access&= ~sctx->master_access; if (!want_access) DBUG_RETURN(0); // ok @@ -3512,7 +3521,8 @@ It is subquery in the FROM clause. VIEW set table->derived after table opening, but this function always called before table opening. */ - table->grant.want_privilege= 0; + if (!table->dependent_view) + table->grant.want_privilege= 0; continue; // Already checked } if (!(grant_table= table_hash_search(sctx->host, sctx->ip, @@ -5820,24 +5830,38 @@ const char *db, const char *table) { st_security_context *sctx= thd->security_ctx; + DBUG_ENTER("fill_effective_table_privileges"); + DBUG_PRINT("enter", ("Host: '%s', Ip: '%s', User: '%s', table: `%s`.`%s`", + sctx->priv_host, (sctx->ip ? sctx->ip : "(NULL)"), + (sctx->priv_user ? sctx->priv_user : "(NULL)"), + db, table)); /* --skip-grants */ if (!initialized) { + DBUG_PRINT("info", ("skip grants")); grant->privilege= ~NO_ACCESS; // everything is allowed - return; + DBUG_PRINT("info", ("privilege 0x%lx", grant->privilege)); + DBUG_VOID_RETURN; } /* global privileges */ grant->privilege= sctx->master_access; if (!sctx->priv_user) - return; // it is slave + { + DBUG_PRINT("info", ("privilege 0x%lx", grant->privilege)); + DBUG_VOID_RETURN; // it is slave + } /* db privileges */ - grant->privilege|= acl_get(sctx->host, sctx->ip, sctx->priv_user, db, 0); + grant->privilege|= acl_get(sctx->priv_host, sctx->ip, sctx->priv_user, db, + 0); if (!grant_option) - return; + { + DBUG_PRINT("info", ("privilege 0x%lx", grant->privilege)); + DBUG_VOID_RETURN; + } /* table privileges */ if (grant->version != grant_version) @@ -5854,6 +5878,8 @@ { grant->privilege|= grant->grant_table->privs; } + DBUG_PRINT("info", ("privilege 0x%lx", grant->privilege)); + DBUG_VOID_RETURN; } #else /* NO_EMBEDDED_ACCESS_CHECKS */ --- 1.303/sql/sql_base.cc 2005-09-15 22:29:01 +03:00 +++ 1.304/sql/sql_base.cc 2005-09-19 02:19:03 +03:00 @@ -1945,11 +1945,11 @@ derived/information schema tables and views possible. Thus "counter" may be still zero for prelocked statement... - NOTE: The above notes may be out of date. Please wait for psergey to + NOTE: The above notes may be out of date. Please wait for psergey to document new prelocked behavior. */ - - if (!thd->prelocked_mode && !thd->lex->requires_prelocking() && + + if (!thd->prelocked_mode && !thd->lex->requires_prelocking() && thd->lex->sroutines_list.elements) { bool first_no_prelocking, need_prelocking; @@ -1999,7 +1999,7 @@ /* VIEW placeholder */ (*counter)--; - /* + /* tables->next_global list consists of two parts: 1) Query tables and underlying tables of views. 2) Tables used by all stored routines that this statement invokes on @@ -2293,7 +2293,8 @@ DBUG_RETURN(-1); close_tables_for_reopen(thd, tables); } - if (mysql_handle_derived(thd->lex, &mysql_derived_prepare) || + if (prepare_underlying_privilege(thd, tables) || + mysql_handle_derived(thd->lex, &mysql_derived_prepare) || (thd->fill_derived_tables() && mysql_handle_derived(thd->lex, &mysql_derived_filling))) DBUG_RETURN(TRUE); /* purecov: inspected */ @@ -2327,6 +2328,7 @@ DBUG_ENTER("open_normal_and_derived_tables"); DBUG_ASSERT(!thd->fill_derived_tables()); if (open_tables(thd, &tables, &counter, flags) || + prepare_underlying_privilege(thd, tables) || mysql_handle_derived(thd->lex, &mysql_derived_prepare)) DBUG_RETURN(TRUE); /* purecov: inspected */ DBUG_RETURN(0); @@ -2651,6 +2653,88 @@ } +#ifndef NO_EMBEDDED_ACCESS_CHECKS +/* + Check column rights in view + + SYNOPSIS + check_grant_column_in_view() + thd therad handler + table_list view to check + name column name + length column name lengh + check_grants need to check grants + + RETURN + FALSE OK + TRUE access deny +} +*/ + +static bool check_grant_column_in_view(THD *thd, TABLE_LIST *table_list, + const char *name, uint length, + bool check_grants) +{ + if (!check_grants) + return FALSE; + st_security_context *save_security_ctx= 0; + st_security_context *new_sctx= table_list->security_ctx; + bool res; + if (new_sctx) + { + save_security_ctx= thd->security_ctx; + thd->security_ctx= new_sctx; + } + res= check_grant_column(thd, &table_list->grant, + table_list->view_db.str, + table_list->view_name.str, + name, length); + if (save_security_ctx) + thd->security_ctx= save_security_ctx; + return res; +} +/* + Check column rights in table + + SYNOPSIS + check_grant_column_in_table() + thd therad handler + table table to check + name column name + length column name lengh + check_grants need to check grants + sctx 0 or security context + + RETURN + FALSE OK + TRUE access deny +} +*/ + +static bool check_grant_column_in_table(THD *thd, TABLE *table, + const char *name, uint length, + bool check_grants, + st_security_context *sctx) +{ + if (!check_grants) + return FALSE; + st_security_context *save_security_ctx= 0; + bool res; + if (sctx) + { + save_security_ctx= thd->security_ctx; + thd->security_ctx= sctx; + } + res= check_grant_column(thd, &table->grant, + table->s->db, + table->s->table_name, name, length); + if (save_security_ctx) + thd->security_ctx= save_security_ctx; + return res; +} +#endif + + /* Find a field by name in a view that uses merge algorithm. @@ -2701,11 +2785,8 @@ */ DBUG_RETURN(((Item_field*) (field_it.item()))->field); #ifndef NO_EMBEDDED_ACCESS_CHECKS - if (check_grants && - check_grant_column(thd, &table_list->grant, - table_list->view_db.str, - table_list->view_name.str, - name, length)) + if (check_grant_column_in_view(thd, table_list, name, length, + check_grants)) DBUG_RETURN(WRONG_GRANT); #endif // in PS use own arena or data will be freed after prepare @@ -2874,7 +2955,8 @@ Field * find_field_in_table(THD *thd, TABLE *table, const char *name, uint length, bool check_grants, bool allow_rowid, - uint *cached_field_index_ptr) + uint *cached_field_index_ptr, + st_security_context *sctx) { Field **field_ptr, *field; uint cached_field_index= *cached_field_index_ptr; @@ -2883,7 +2965,7 @@ /* We assume here that table->field < NO_CACHED_FIELD_INDEX = UINT_MAX */ if (cached_field_index < table->s->fields && - !my_strcasecmp(system_charset_info, + !my_strcasecmp(system_charset_info, table->field[cached_field_index]->field_name, name)) field_ptr= table->field + cached_field_index; else if (table->s->name_hash.records) @@ -2914,9 +2996,8 @@ update_field_dependencies(thd, field, table); #ifndef NO_EMBEDDED_ACCESS_CHECKS - if (check_grants && check_grant_column(thd, &table->grant, - table->s->db, - table->s->table_name, name, length)) + if (check_grant_column_in_table(thd, table, name, length, + check_grants, sctx)) field= WRONG_GRANT; #endif DBUG_RETURN(field); @@ -3036,16 +3117,15 @@ { if ((fld= find_field_in_table(thd, table_list->table, name, length, check_grants_table, allow_rowid, - cached_field_index_ptr))) + cached_field_index_ptr, + table_list->security_ctx))) *actual_table= table_list; #ifndef NO_EMBEDDED_ACCESS_CHECKS /* check for views with temporary table algorithm */ - if (check_grants_view && table_list->view && + if (table_list->view && fld && fld != WRONG_GRANT && - check_grant_column(thd, &table_list->grant, - table_list->view_db.str, - table_list->view_name.str, - name, length)) + check_grant_column_in_view(thd, table_list, name, length, + check_grants_view)) fld= WRONG_GRANT; #endif } @@ -3134,7 +3214,8 @@ test(table_ref->table-> grant.want_privilege) && check_privileges, - 1, &(item->cached_field_index)); + 1, &(item->cached_field_index), + table_ref->security_ctx); else found= find_field_in_table_ref(thd, table_ref, name, item->name, NULL, NULL, length, ref, @@ -4270,8 +4351,12 @@ { for (TABLE_LIST *table= tables; table; table= table->next_local) { - if (table->view && table->effective_algorithm == VIEW_ALGORITHM_MERGE) - list= make_leaves_list(list, table->ancestor); + if (table->merge_underlying_list) + { + DBUG_ASSERT(table->view && + table->effective_algorithm == VIEW_ALGORITHM_MERGE); + list= make_leaves_list(list, table->merge_underlying_list); + } else { *list= table; @@ -4371,16 +4456,17 @@ table_list; table_list= table_list->next_local) { - if (table_list->ancestor) + if (table_list->merge_underlying_list) { - DBUG_ASSERT(table_list->view); + DBUG_ASSERT(table_list->view && + table_list->effective_algorithm == VIEW_ALGORITHM_MERGE); Query_arena *arena= thd->stmt_arena, backup; bool res; if (arena->is_conventional()) arena= 0; // For easier test else thd->set_n_backup_active_arena(arena, &backup); - res= table_list->setup_ancestor(thd); + res= table_list->setup_underlying(thd); if (arena) thd->restore_active_arena(arena, &backup); if (res) --- 1.84/sql/sql_cache.cc 2005-09-06 20:51:08 +03:00 +++ 1.85/sql/sql_cache.cc 2005-09-19 02:19:03 +03:00 @@ -2203,9 +2203,10 @@ HA_CACHE_TBL_NONTRANSACT, 0, 0)) DBUG_RETURN(0); { - TABLE_COUNTER_TYPE inc= register_tables_from_list(tables_used->ancestor, - n + 1, - block_table + 1); + TABLE_COUNTER_TYPE inc= + register_tables_from_list(tables_used->merge_underlying_list, + n + 1, + block_table + 1); if (!inc) DBUG_RETURN(0); n+= inc; @@ -2828,8 +2829,9 @@ *tables_type|= HA_CACHE_TBL_NONTRANSACT; { TABLE_COUNTER_TYPE subcount; - if (!(subcount= process_and_count_tables(tables_used->ancestor, - tables_type))) + if (!(subcount= + process_and_count_tables(tables_used->merge_underlying_list, + tables_type))) DBUG_RETURN(0); table_count+= subcount; } --- 1.161/sql/sql_delete.cc 2005-09-01 22:42:21 +03:00 +++ 1.162/sql/sql_delete.cc 2005-09-19 02:19:03 +03:00 @@ -402,8 +402,9 @@ if (!(target_tbl->table= target_tbl->correspondent_table->table)) { DBUG_ASSERT(target_tbl->correspondent_table->view && - target_tbl->correspondent_table->ancestor && - target_tbl->correspondent_table->ancestor->next_local); + target_tbl->correspondent_table->merge_underlying_list && + target_tbl->correspondent_table->merge_underlying_list-> + next_local); my_error(ER_VIEW_DELETE_MERGE_VIEW, MYF(0), target_tbl->correspondent_table->view_db.str, target_tbl->correspondent_table->view_name.str); --- 1.197/sql/sql_lex.h 2005-09-15 02:57:52 +03:00 +++ 1.198/sql/sql_lex.h 2005-09-19 02:19:03 +03:00 @@ -796,6 +796,7 @@ */ uint table_count; uint8 describe; + /* derived tables present in the query (DERIVED_SUBQUERY, DERIVED_VIEW)*/ uint8 derived_tables; uint8 create_view_algorithm; uint8 create_view_check; --- 1.489/sql/sql_parse.cc 2005-09-15 22:29:01 +03:00 +++ 1.490/sql/sql_parse.cc 2005-09-19 02:19:03 +03:00 @@ -4971,6 +4971,8 @@ information_schema_name.str); return TRUE; } + /* register acess for view underlying table */ + tables->register_want_access(want_access); if (tables->derived || tables->schema_table || tables->belong_to_view || (tables->table && (int)tables->table->s->tmp_table) || my_tz_check_n_skip_implicit_tables(&tables, --- 1.170/sql/sql_update.cc 2005-09-15 02:57:52 +03:00 +++ 1.171/sql/sql_update.cc 2005-09-19 02:19:03 +03:00 @@ -160,7 +160,8 @@ close_tables_for_reopen(thd, table_list); } - if (mysql_handle_derived(thd->lex, &mysql_derived_prepare) || + if (prepare_underlying_privilege(thd, table_list) || + mysql_handle_derived(thd->lex, &mysql_derived_prepare) || (thd->fill_derived_tables() && mysql_handle_derived(thd->lex, &mysql_derived_filling))) DBUG_RETURN(1); @@ -194,6 +195,7 @@ /* Check the fields we are going to modify */ #ifndef NO_EMBEDDED_ACCESS_CHECKS table_list->grant.want_privilege= table->grant.want_privilege= want_privilege; + table_list->register_want_access(want_privilege); #endif if (setup_fields_with_no_wrap(thd, 0, fields, 1, 0, 0)) DBUG_RETURN(1); /* purecov: inspected */ @@ -557,6 +559,7 @@ #ifndef NO_EMBEDDED_ACCESS_CHECKS table_list->grant.want_privilege= table->grant.want_privilege= (SELECT_ACL & ~table->grant.privilege); + table_list->register_want_access(SELECT_ACL); #endif bzero((char*) &tables,sizeof(tables)); // For ORDER BY @@ -648,6 +651,7 @@ /* open tables and create derived ones, but do not lock and fill them */ if (((original_multiupdate || need_reopen) && open_tables(thd, &table_list, &table_count, 0)) || + prepare_underlying_privilege(thd, table_list) || mysql_handle_derived(lex, &mysql_derived_prepare)) DBUG_RETURN(TRUE); /* --- 1.189/sql/table.cc 2005-09-14 14:44:40 +03:00 +++ 1.190/sql/table.cc 2005-09-19 02:19:04 +03:00 @@ -1713,7 +1713,7 @@ /* - set ancestor TABLE for table place holder of VIEW + set underlying TABLE for table place holder of VIEW DESCRIPTION Replace all views that only uses one table with the table itself. @@ -1721,33 +1721,35 @@ it SYNOPSIS - st_table_list::set_ancestor() + st_table_list::set_underlying_merge() */ -void st_table_list::set_ancestor() +void st_table_list::set_underlying_merge() { TABLE_LIST *tbl; - if ((tbl= ancestor)) + if ((tbl= merge_underlying_list)) { /* This is a view. Process all tables of view */ - DBUG_ASSERT(view); + DBUG_ASSERT(view && effective_algorithm == VIEW_ALGORITHM_MERGE); do { - if (tbl->ancestor) // This is a view + if (tbl->merge_underlying_list) // This is a view { + DBUG_ASSERT(tbl->view && + tbl->effective_algorithm == VIEW_ALGORITHM_MERGE); /* This is the only case where set_ancestor is called on an object that may not be a view (in which case ancestor is 0) */ - tbl->ancestor->set_ancestor(); + tbl->merge_underlying_list->set_underlying_merge(); } } while ((tbl= tbl->next_local)); if (!multitable_view) { - table= ancestor->table; - schema_table= ancestor->schema_table; + table= merge_underlying_list->table; + schema_table= merge_underlying_list->schema_table; } } } @@ -1757,12 +1759,9 @@ setup fields of placeholder of merged VIEW SYNOPSIS - st_table_list::setup_ancestor() + st_table_list::setup_underlying() thd - thread handler - NOTES - ancestor is list of tables and views used by view (underlying tables/views) - DESCRIPTION It is: - preparing translation table for view columns @@ -1773,10 +1772,11 @@ TRUE - error */ -bool st_table_list::setup_ancestor(THD *thd) +bool st_table_list::setup_underlying(THD *thd) { - DBUG_ENTER("st_table_list::setup_ancestor"); - if (!field_translation) + DBUG_ENTER("st_table_list::setup_underlying"); + + if (!field_translation && merge_underlying_list) { Field_translator *transl; SELECT_LEX *select= &view->select_lex; @@ -1790,10 +1790,10 @@ DBUG_RETURN(TRUE); } - for (tbl= ancestor; tbl; tbl= tbl->next_local) + for (tbl= merge_underlying_list; tbl; tbl= tbl->next_local) { - if (tbl->ancestor && - tbl->setup_ancestor(thd)) + if (tbl->merge_underlying_list && + tbl->setup_underlying(thd)) { DBUG_RETURN(TRUE); } @@ -1856,7 +1856,7 @@ { DBUG_ENTER("st_table_list::prep_where"); - for (TABLE_LIST *tbl= ancestor; tbl; tbl= tbl->next_local) + for (TABLE_LIST *tbl= merge_underlying_list; tbl; tbl= tbl->next_local) { if (tbl->view && tbl->prep_where(thd, conds, no_where_clause)) { @@ -1938,7 +1938,7 @@ { DBUG_ENTER("st_table_list::prep_check_option"); - for (TABLE_LIST *tbl= ancestor; tbl; tbl= tbl->next_local) + for (TABLE_LIST *tbl= merge_underlying_list; tbl; tbl= tbl->next_local) { /* see comment of check_opt_type parameter */ if (tbl->view && @@ -1961,7 +1961,7 @@ } if (check_opt_type == VIEW_CHECK_CASCADED) { - for (TABLE_LIST *tbl= ancestor; tbl; tbl= tbl->next_local) + for (TABLE_LIST *tbl= merge_underlying_list; tbl; tbl= tbl->next_local) { if (tbl->check_option) item= and_conds(item, tbl->check_option); @@ -2000,7 +2000,8 @@ { /* Hide "Unknown column" or "Unknown function" error */ if (thd->net.last_errno == ER_BAD_FIELD_ERROR || - thd->net.last_errno == ER_SP_DOES_NOT_EXIST) + thd->net.last_errno == ER_SP_DOES_NOT_EXIST || + thd->net.last_errno == ER_COLUMNACCESS_DENIED_ERROR) { thd->clear_error(); my_error(ER_VIEW_INVALID, MYF(0), view_db.str, view_name.str); @@ -2030,10 +2031,10 @@ st_table_list *st_table_list::find_underlying_table(TABLE *table_to_find) { /* is this real table and table which we are looking for? */ - if (table == table_to_find && ancestor == 0) + if (table == table_to_find && merge_underlying_list == 0) return this; - for (TABLE_LIST *tbl= ancestor; tbl; tbl= tbl->next_local) + for (TABLE_LIST *tbl= merge_underlying_list; tbl; tbl= tbl->next_local) { TABLE_LIST *result; if ((result= tbl->find_underlying_table(table_to_find))) @@ -2116,7 +2117,7 @@ bool st_table_list::check_single_table(st_table_list **table, table_map map, st_table_list *view) { - for (TABLE_LIST *tbl= ancestor; tbl; tbl= tbl->next_local) + for (TABLE_LIST *tbl= merge_underlying_list; tbl; tbl= tbl->next_local) { if (tbl->table) { @@ -2158,8 +2159,8 @@ } else { - DBUG_ASSERT(view && ancestor); - for (TABLE_LIST *tbl= ancestor; tbl; tbl= tbl->next_local) + DBUG_ASSERT(view && merge_underlying_list); + for (TABLE_LIST *tbl= merge_underlying_list; tbl; tbl= tbl->next_local) if (tbl->set_insert_values(mem_root)) return TRUE; } @@ -2303,6 +2304,135 @@ } +/* + Register access mode which we need for underlying tables + + SYNOPSIS + register_want_access() + want_access Acess which we require +*/ + +void st_table_list::register_want_access (ulong want_access) +{ + /* Remove SHOW_VIEW_ACL, because it will be checked during making view */ + want_access&= ~SHOW_VIEW_ACL; + grant.orig_want_access= want_access; + if (belong_to_view) + { + grant.want_privilege= want_access; + if (table) + table->grant.want_privilege= want_access; + } + for (TABLE_LIST *tbl= merge_underlying_list; tbl; tbl= tbl->next_local) + tbl->register_want_access(want_access); +} + +/* + Load user information for view and acess information for underlying tables + + SYNOPSIS + prepare_underlying_privilege() + + RETURN + FALSE OK + TRUE Error +*/ + +bool prepare_underlying_privilege(THD *thd, TABLE_LIST *tables) +{ + DBUG_ENTER("prepare_underlying_privilege"); + for(; tables; tables= tables->next_global) + { + if (tables->prepare_underlying_privilege(thd)) + { + DBUG_RETURN(TRUE); + } + } + DBUG_RETURN(FALSE); +} + + +/* + Load user information for view and acess information for underlying tables + + SYNOPSIS + st_table_list::prepare_underlying_privilege() + + RETURN + FALSE OK + TRUE Error +*/ + +bool st_table_list::prepare_underlying_privilege(THD *thd) +{ + DBUG_ENTER("st_table_list::prepare_underlying_privilege"); + DBUG_PRINT("enter", ("table: %s", alias)); + if (prelocking_placeholder) + { + DBUG_PRINT("info", ("I am really sorry, it is placeholder")); + DBUG_RETURN(FALSE); + } +#ifndef NO_EMBEDDED_ACCESS_CHECKS + if (view_suid) + { + DBUG_PRINT("info", ("This table is suid view => load contest")); + DBUG_ASSERT(view && view_sctx); + if (acl_getroot_no_password(view_sctx, + definer.user.str, + definer.host.str, + definer.host.str, + thd->db)) + { + my_error(ER_NO_SUCH_USER, MYF(0), definer.user.str, definer.host.str); + DBUG_RETURN(TRUE); + } + } + + if (dependent_view) + { + DBUG_PRINT("info", ("This table belong to view %s", + dependent_view->alias)); + TABLE_LIST *upper_view= dependent_view; + st_security_context *sctx, *save_security_ctx; + sctx= save_security_ctx= thd->security_ctx; + + /* find security context for this table */ + while (upper_view && !upper_view->view_suid && + !upper_view->prelocking_placeholder) + upper_view= upper_view->dependent_view; + if (upper_view) + { + DBUG_PRINT("info", ("Securety context of view %s will be used", + upper_view->alias)); + if (upper_view->prelocking_placeholder) + { + DBUG_PRINT("info", ("I am really really sorry, %s is placeholder", + upper_view->alias)); + DBUG_RETURN(FALSE); + } + sctx= upper_view->view_sctx; + DBUG_ASSERT(sctx); + } + else + { + DBUG_PRINT("info", ("Current global context will be used")); + } + + thd->security_ctx= sctx; + fill_effective_table_privileges(thd, &grant, db, table_name); + if (table) + table->grant= grant; + thd->security_ctx= save_security_ctx; + } +#else + if (dependent_view) + { + grant.privilege= ~NO_ACCESS; + } +#endif + DBUG_RETURN(FALSE); +} + Natural_join_column::Natural_join_column(Field_translator *field_param, TABLE_LIST *tab) { @@ -2427,6 +2557,9 @@ GRANT_INFO *grant; const char *db_name; const char *table_name; + st_security_context *save_security_ctx= 0; + st_security_context *new_sctx= table_ref->security_ctx; + bool res; if (view_field) { @@ -2443,7 +2576,15 @@ table_name= table_ref->table->s->table_name; } - return check_grant_column(thd, grant, db_name, table_name, name, length); + if (new_sctx) + { + save_security_ctx= thd->security_ctx; + thd->security_ctx= new_sctx; + } + res= check_grant_column(thd, grant, db_name, table_name, name, length); + if (save_security_ctx) + thd->security_ctx= save_security_ctx; + return res; } #endif --- 1.114/sql/table.h 2005-09-14 11:00:31 +03:00 +++ 1.115/sql/table.h 2005-09-19 02:19:04 +03:00 @@ -22,6 +22,7 @@ class st_select_lex_unit; class st_select_lex; class COND_EQUAL; +class st_security_context; /* Order clause list element */ @@ -47,6 +48,11 @@ uint version; ulong privilege; ulong want_privilege; + /* + Store wanted acess of top table list. + Used to check underlying tables access. + */ + ulong orig_want_access; } GRANT_INFO; enum tmp_table_type {NO_TMP_TABLE=0, TMP_TABLE=1, TRANSACTIONAL_TMP_TABLE=2, @@ -356,7 +362,6 @@ #define VIEW_CHECK_SKIP 2 struct st_lex; -struct st_table_list; class select_union; class TMP_TABLE_PARAM; @@ -522,10 +527,20 @@ Field_translator *field_translation; /* array of VIEW fields */ /* pointer to element after last one in translation table above */ Field_translator *field_translation_end; - /* list of ancestor(s) of this table (underlying table(s)/view(s) */ - st_table_list *ancestor; + /* + list of underlying tables of this table (table(s)/view(s) of + main select), i.e it do not include tables of subqueries. + Set only for merged views. + */ + st_table_list *merge_underlying_list; /* most upper view this table belongs to */ st_table_list *belong_to_view; + /* reference to view which this table belongs to */ + st_table_list *dependent_view; + /* security context (valid only for view underlying tables */ + st_security_context *security_ctx; + /* this view security context (valid only for views) */ + st_security_context *view_sctx; /* List of all base tables local to a subquery including all view tables. Unlike 'next_local', this in this list views are *not* @@ -594,9 +609,9 @@ bool prelocking_placeholder; void calc_md5(char *buffer); - void set_ancestor(); + void set_underlying_merge(); int view_check_option(THD *thd, bool ignore_failure); - bool setup_ancestor(THD *thd); + bool setup_underlying(THD *thd); void cleanup_items(); bool placeholder() {return derived || view; } void print(THD *thd, String *str); @@ -624,10 +639,16 @@ return prep_where(thd, conds, no_where_clause); return FALSE; } + + void register_want_access(ulong want_access); + bool prepare_underlying_privilege(THD *thd); + private: bool prep_check_option(THD *thd, uint8 check_opt_type); bool prep_where(THD *thd, Item **conds, bool no_where_clause); } TABLE_LIST; + +bool prepare_underlying_privilege(THD *thd, TABLE_LIST *tables); class Item; --- 1.76/sql/sql_derived.cc 2005-08-15 18:31:01 +03:00 +++ 1.77/sql/sql_derived.cc 2005-09-19 02:19:03 +03:00 @@ -35,15 +35,14 @@ processor procedure of derived table processing RETURN - 0 ok - -1 Error - 1 Error and error message given + FALSE OK + TRUE Error */ -int -mysql_handle_derived(LEX *lex, int (*processor)(THD*, LEX*, TABLE_LIST*)) +bool +mysql_handle_derived(LEX *lex, bool (*processor)(THD*, LEX*, TABLE_LIST*)) { - int res= 0; + int res= FALSE; if (lex->derived_tables) { lex->thd->derived_tables_processing= TRUE; @@ -96,16 +95,15 @@ close_thread_tables() RETURN - 0 ok - 1 Error - -1 Error and error message given - */ + FALSE OK + TRUE Error +*/ -int mysql_derived_prepare(THD *thd, LEX *lex, TABLE_LIST *orig_table_list) +bool mysql_derived_prepare(THD *thd, LEX *lex, TABLE_LIST *orig_table_list) { SELECT_LEX_UNIT *unit= orig_table_list->derived; - int res= 0; DBUG_ENTER("mysql_derived_prepare"); + int res= FALSE; if (unit) { SELECT_LEX *first_select= unit->first_select(); @@ -127,7 +125,7 @@ if (check_duplicate_names(unit->types, 0)) { - res= -1; + res= TRUE; goto exit; } @@ -151,7 +149,7 @@ HA_POS_ERROR, orig_table_list->alias))) { - res= -1; + res= TRUE; goto exit; } derived_result->set_table(table); @@ -194,7 +192,10 @@ table->derived_select_number= first_select->select_number; table->s->tmp_table= TMP_TABLE; #ifndef NO_EMBEDDED_ACCESS_CHECKS - table->grant.privilege= SELECT_ACL; + if (orig_table_list->dependent_view) + table->grant= orig_table_list->grant; + else + table->grant.privilege= SELECT_ACL; #endif orig_table_list->db= (char *)""; orig_table_list->db_length= 0; @@ -205,8 +206,8 @@ thd->derived_tables= table; } } - else if (orig_table_list->ancestor) - orig_table_list->set_ancestor(); + else if (orig_table_list->merge_underlying_list) + orig_table_list->set_underlying_merge(); DBUG_RETURN(res); } @@ -230,16 +231,15 @@ Due to evaluation of LIMIT clause it can not be used at prepared stage. RETURN - 0 ok - 1 Error - -1 Error and error message given - */ + FALSE OK + TRUE Error +*/ -int mysql_derived_filling(THD *thd, LEX *lex, TABLE_LIST *orig_table_list) +bool mysql_derived_filling(THD *thd, LEX *lex, TABLE_LIST *orig_table_list) { TABLE *table= orig_table_list->table; SELECT_LEX_UNIT *unit= orig_table_list->derived; - int res= 0; + int res= FALSE; /*check that table creation pass without problem and it is derived table */ if (table && unit) @@ -282,7 +282,7 @@ there were no derived tables */ if (derived_result->flush()) - res= 1; + res= TRUE; if (!lex->describe) unit->cleanup(); --- 1.44/sql/share/errmsg.txt 2005-09-14 13:37:36 +03:00 +++ 1.45/sql/share/errmsg.txt 2005-09-19 02:19:03 +03:00 @@ -5210,8 +5210,8 @@ rus "Обновляемый view не содержит ключа использованных(ой) в нем таблиц(ы)" ukr "View, що оновлюеться, не м╕стить повного ключа таблиц╕(ь), що викор╕стана в ньюому" ER_VIEW_INVALID - eng "View '%-.64s.%-.64s' references invalid table(s) or column(s) or function(s)" - rus "View '%-.64s.%-.64s' ссылается на несуществующие таблицы или столбцы или функции" + eng "View '%-.64s.%-.64s' references invalid table(s) or column(s) or function(s) or definer of view lack rights to use them" + rus "View '%-.64s.%-.64s' ссылается на несуществующие таблицы, столбцы или функции, или создатель не имеет прав использовать их" ER_SP_NO_DROP_SP eng "Can't drop or alter a %s from within another stored routine" ER_SP_GOTO_IN_HNDLR --- 1.117/mysql-test/r/view.result 2005-09-14 23:27:52 +03:00 +++ 1.118/mysql-test/r/view.result 2005-09-19 02:19:02 +03:00 @@ -574,10 +574,10 @@ drop table t1; create table t1 (col1 char(5),newcol2 char(5)); insert into v1 values('a','aa'); -ERROR HY000: View 'test.v1' references invalid table(s) or column(s) or function(s) +ERROR HY000: View 'test.v1' references invalid table(s) or column(s) or function(s) or definer of view lack rights to use them drop table t1; select * from v1; -ERROR HY000: View 'test.v1' references invalid table(s) or column(s) or function(s) +ERROR HY000: View 'test.v1' references invalid table(s) or column(s) or function(s) or definer of view lack rights to use them drop view v1; create view v1 (a,a) as select 'a','a'; ERROR 42S21: Duplicate column name 'a' @@ -809,11 +809,11 @@ create view v1 as select x1() from t1; drop function x1; select * from v1; -ERROR HY000: View 'test.v1' references invalid table(s) or column(s) or function(s) +ERROR HY000: View 'test.v1' references invalid table(s) or column(s) or function(s) or definer of view lack rights to use them show table status; Name Engine Version Row_format Rows Avg_row_length Data_length Max_data_length Index_length Data_free Auto_increment Create_time Update_time Check_time Collation Checksum Create_options Comment t1 MyISAM 10 Fixed 0 0 0 # 1024 0 NULL # # NULL latin1_swedish_ci NULL -v1 NULL NULL NULL NULL NULL NULL # NULL NULL NULL # # NULL NULL NULL NULL View 'test.v1' references invalid table(s) or column(s) or function(s) +v1 NULL NULL NULL NULL NULL NULL # NULL NULL NULL # # NULL NULL NULL NULL View 'test.v1' references invalid table(s) or column(s) or function(s) or define drop view v1; drop table t1; create view v1 as select 99999999999999999999999999999999999999999999999999999 as col1; @@ -1348,7 +1348,7 @@ drop table t1; check table v1; Table Op Msg_type Msg_text -test.v1 check error View 'test.v1' references invalid table(s) or column(s) or function(s) +test.v1 check error View 'test.v1' references invalid table(s) or column(s) or function(s) or definer of view lack rights to use them drop view v1; create table t1 (a int); create table t2 (a int); @@ -1872,11 +1872,11 @@ DROP TABLE t1; CHECK TABLE v1, v2, v3, v4, v5, v6; Table Op Msg_type Msg_text -test.v1 check error View 'test.v1' references invalid table(s) or column(s) or function(s) +test.v1 check error View 'test.v1' references invalid table(s) or column(s) or function(s) or definer of view lack rights to use them test.v2 check status OK -test.v3 check error View 'test.v3' references invalid table(s) or column(s) or function(s) +test.v3 check error View 'test.v3' references invalid table(s) or column(s) or function(s) or definer of view lack rights to use them test.v4 check status OK -test.v5 check error View 'test.v5' references invalid table(s) or column(s) or function(s) +test.v5 check error View 'test.v5' references invalid table(s) or column(s) or function(s) or definer of view lack rights to use them test.v6 check status OK drop view v1, v2, v3, v4, v5, v6; drop table t2; @@ -1896,11 +1896,11 @@ drop function f1; CHECK TABLE v1, v2, v3, v4, v5, v6; Table Op Msg_type Msg_text -test.v1 check error View 'test.v1' references invalid table(s) or column(s) or function(s) +test.v1 check error View 'test.v1' references invalid table(s) or column(s) or function(s) or definer of view lack rights to use them test.v2 check status OK -test.v3 check error View 'test.v3' references invalid table(s) or column(s) or function(s) +test.v3 check error View 'test.v3' references invalid table(s) or column(s) or function(s) or definer of view lack rights to use them test.v4 check status OK -test.v5 check error View 'test.v5' references invalid table(s) or column(s) or function(s) +test.v5 check error View 'test.v5' references invalid table(s) or column(s) or function(s) or definer of view lack rights to use them test.v6 check status OK create function f1 () returns int return (select max(col1) from t1); DROP TABLE t1; @@ -2142,7 +2142,7 @@ f4 char(5) YES NULL ALTER TABLE t1 CHANGE COLUMN f4 f4x CHAR(5); DESCRIBE v1; -ERROR HY000: View 'test.v1' references invalid table(s) or column(s) or function(s) +ERROR HY000: View 'test.v1' references invalid table(s) or column(s) or function(s) or definer of view lack rights to use them DROP TABLE t1; DROP VIEW v1; create table t1 (f1 char); --- 1.64/sql/sql_view.cc 2005-09-15 22:29:01 +03:00 +++ 1.65/sql/sql_view.cc 2005-09-19 02:19:04 +03:00 @@ -766,7 +766,7 @@ if (!table->timestamp.str) table->timestamp.str= table->timestamp_buffer; /* prepare default values for old format */ - table->view_suid= 1; + table->view_suid= TRUE; table->definer.user.str= table->definer.host.str= 0; table->definer.user.length= table->definer.host.length= 0; @@ -854,6 +854,7 @@ TABLE_LIST *view_tables= lex->query_tables; TABLE_LIST *view_tables_tail= 0; TABLE_LIST *tbl; + /*uint i;*/ /* Check rights to run commands (EXPLAIN SELECT & SHOW CREATE) which show @@ -887,6 +888,8 @@ { tbl->skip_temporary= 1; tbl->belong_to_view= top_view; + tbl->dependent_view= table; + tbl->register_want_access(top_view->grant.orig_want_access); } /* @@ -929,7 +932,7 @@ if (table->prelocking_placeholder) goto ok2; - old_lex->derived_tables|= DERIVED_VIEW; + old_lex->derived_tables|= (DERIVED_VIEW | lex->derived_tables); /* move SQL_NO_CACHE & Co to whole query */ old_lex->safe_to_cache_query= (old_lex->safe_to_cache_query && @@ -938,6 +941,28 @@ if (view_select->options & OPTION_TO_QUERY_CACHE) old_lex->select_lex.options|= OPTION_TO_QUERY_CACHE; + if (table->view_suid) + { + /* + prepare context for view underlying objects check + */ + st_security_context *save_security_ctx= thd->security_ctx; + if (!(table->view_sctx= (st_security_context *) + thd->stmt_arena->alloc(sizeof(st_security_context)))) + goto err; + /* assign context to tables on which view depends */ + for (tbl= view_tables; tbl; tbl= tbl->next_global) + { + tbl->security_ctx= table->view_sctx; + } + /* assign security context to SELECT name resolution contexts of view */ + for(SELECT_LEX *sl= lex->all_selects_list; + sl; + sl= sl->next_select_in_list()) + { + sl->context.security_ctx= table->view_sctx; + } + } /* check MERGE algorithm ability - algorithm is not explicit TEMPORARY TABLE @@ -961,7 +986,8 @@ old_lex->get_effective_with_check(table); /* prepare view context */ - lex->select_lex.context.resolve_in_table_list_only(table->ancestor= + lex->select_lex.context.resolve_in_table_list_only(table-> + merge_underlying_list= view_tables); lex->select_lex.context.outer_context= 0; lex->select_lex.context.select_lex= table->select_lex; @@ -973,7 +999,6 @@ sl; sl= sl->next_select_in_list()) { - sl->context.check_privileges= FALSE; sl->context.error_processor= &view_error_processor; sl->context.error_processor_data= (void *)table; } @@ -1009,7 +1034,7 @@ } } - /* Store WHERE clause for post-processing in setup_ancestor */ + /* Store WHERE clause for post-processing in setup_underlying */ table->where= view_select->where; /* Add subqueries units to SELECT into which we merging current view. --- 1.80/mysql-test/r/information_schema.result 2005-09-13 14:06:27 +03:00 +++ 1.81/mysql-test/r/information_schema.result 2005-09-19 02:19:02 +03:00 @@ -647,21 +647,21 @@ where table_schema='test'; table_name Warnings: -Warning 1356 View 'test.t2' references invalid table(s) or column(s) or function(s) -Warning 1356 View 'test.t3' references invalid table(s) or column(s) or function(s) +Warning 1356 View 'test.t2' references invalid table(s) or column(s) or function(s) or definer of view lack rights to use them +Warning 1356 View 'test.t3' references invalid table(s) or column(s) or function(s) or definer of view lack rights to use them select table_name from information_schema.views where table_schema='test'; table_name Warnings: -Warning 1356 View 'test.t2' references invalid table(s) or column(s) or function(s) -Warning 1356 View 'test.t3' references invalid table(s) or column(s) or function(s) +Warning 1356 View 'test.t2' references invalid table(s) or column(s) or function(s) or definer of view lack rights to use them +Warning 1356 View 'test.t3' references invalid table(s) or column(s) or function(s) or definer of view lack rights to use them select column_name from information_schema.columns where table_schema='test'; column_name f1 Warnings: -Warning 1356 View 'test.t2' references invalid table(s) or column(s) or function(s) -Warning 1356 View 'test.t3' references invalid table(s) or column(s) or function(s) +Warning 1356 View 'test.t2' references invalid table(s) or column(s) or function(s) or definer of view lack rights to use them +Warning 1356 View 'test.t3' references invalid table(s) or column(s) or function(s) or definer of view lack rights to use them select index_name from information_schema.statistics where table_schema='test'; index_name f1_key --- 1.28/mysql-test/r/sql_mode.result 2005-09-14 12:24:10 +03:00 +++ 1.29/mysql-test/r/sql_mode.result 2005-09-19 02:19:02 +03:00 @@ -1,4 +1,5 @@ -drop table if exists t1; +drop table if exists t1,t2,v1,v2; +drop view if exists t1,t2,v1,v2; CREATE TABLE `t1` ( a int not null auto_increment, `pseudo` varchar(35) character set latin2 NOT NULL default '', --- 1.13/mysql-test/t/sql_mode.test 2005-07-28 17:09:48 +03:00 +++ 1.14/mysql-test/t/sql_mode.test 2005-09-19 02:19:02 +03:00 @@ -1,5 +1,6 @@ --disable_warnings -drop table if exists t1; +drop table if exists t1,t2,v1,v2; +drop view if exists t1,t2,v1,v2; --enable_warnings CREATE TABLE `t1` ( --- 1.4/mysql-test/r/view_grant.result 2005-09-14 10:53:02 +03:00 +++ 1.5/mysql-test/r/view_grant.result 2005-09-19 02:19:02 +03:00 @@ -305,5 +305,44 @@ grant all privileges on mysqltest.* to mysqltest_1@localhost; use mysqltest; create view v1 as select * from t1; +use test; revoke all privileges on mysqltest.* from mysqltest_1@localhost; +drop database mysqltest; +create database mysqltest; +create table mysqltest.t1 (a int, b int); +grant select on mysqltest.t1 to mysqltest_1@localhost; +grant create view,select on test.* to mysqltest_1@localhost; +create view v1 as select * from mysqltest.t1; +show create view v1; +View Create View +v1 CREATE ALGORITHM=UNDEFINED DEFINER=`mysqltest_1`@`localhost` SQL SECURITY DEFINER VIEW `test`.`v1` AS select `mysqltest`.`t1`.`a` AS `a`,`mysqltest`.`t1`.`b` AS `b` from `mysqltest`.`t1` +revoke select on mysqltest.t1 from mysqltest_1@localhost; +select * from v1; +ERROR HY000: View 'test.v1' references invalid table(s) or column(s) or function(s) or definer of view lack rights to use them +grant select on mysqltest.t1 to mysqltest_1@localhost; +select * from v1; +a b +REVOKE ALL PRIVILEGES, GRANT OPTION FROM mysqltest_1@localhost; +drop view v1; +drop database mysqltest; +create database mysqltest; +use mysqltest; +create table t2 (s1 int); +drop function if exists f2; +create function f2 () returns int begin declare v int; select s1 from t2 +into v; return v; end// +create view v2 as select f2(); +grant select on v2 to mysqltest_1@localhost; +use mysqltest; +select * from v2; +f2() +NULL +Warnings: +Warning 1329 No data to FETCH +use test; +drop function f2; +drop view v2; +drop table t2; +use test; +REVOKE ALL PRIVILEGES, GRANT OPTION FROM mysqltest_1@localhost; drop database mysqltest; --- 1.4/mysql-test/t/view_grant.test 2005-09-14 10:53:02 +03:00 +++ 1.5/mysql-test/t/view_grant.test 2005-09-19 02:19:02 +03:00 @@ -401,8 +401,70 @@ connection user1; use mysqltest; create view v1 as select * from t1; +use test; connection root; revoke all privileges on mysqltest.* from mysqltest_1@localhost; drop database mysqltest; +# +# view definer grants revoking +# +connection root; +--disable_warnings +create database mysqltest; +--enable_warnings + +create table mysqltest.t1 (a int, b int); + +grant select on mysqltest.t1 to mysqltest_1@localhost; +grant create view,select on test.* to mysqltest_1@localhost; + +connection user1; + +create view v1 as select * from mysqltest.t1; + +connection root; +# check view definer information +show create view v1; +revoke select on mysqltest.t1 from mysqltest_1@localhost; +-- error ER_VIEW_INVALID +select * from v1; +grant select on mysqltest.t1 to mysqltest_1@localhost; +select * from v1; +REVOKE ALL PRIVILEGES, GRANT OPTION FROM mysqltest_1@localhost; +drop view v1; +drop database mysqltest; + +# +# rights on execution of view underlying functiond (BUG#9505) +# +connection root; +--disable_warnings +create database mysqltest; +--enable_warnings + +use mysqltest; +create table t2 (s1 int); +--disable_warnings +drop function if exists f2; +--enable_warnings +delimiter //; +create function f2 () returns int begin declare v int; select s1 from t2 +into v; return v; end// +delimiter ;// +create view v2 as select f2(); +grant select on v2 to mysqltest_1@localhost; + +connection user1; +use mysqltest; +select * from v2; +use test; + +connection root; +drop function f2; +drop view v2; +drop table t2; +use test; +REVOKE ALL PRIVILEGES, GRANT OPTION FROM mysqltest_1@localhost; +drop database mysqltest; --- 1.155/mysql-test/r/sp.result 2005-09-14 13:54:42 +03:00 +++ 1.156/mysql-test/r/sp.result 2005-09-19 02:19:02 +03:00 @@ -1032,7 +1032,7 @@ 3 1 drop function f1| select * from v1| -ERROR HY000: View 'test.v1' references invalid table(s) or column(s) or function(s) +ERROR HY000: View 'test.v1' references invalid table(s) or column(s) or function(s) or definer of view lack rights to use them create function f1() returns int return (select sum(data) from t1) + (select sum(data) from v1)| drop function f1| --- 1.154/sql/sql_prepare.cc 2005-09-15 02:57:52 +03:00 +++ 1.155/sql/sql_prepare.cc 2005-09-19 02:19:03 +03:00 @@ -1135,7 +1135,8 @@ thd->fill_derived_tables() is false here for sure (because it is preparation of PS, so we even do not check it). */ - if (mysql_handle_derived(thd->lex, &mysql_derived_prepare)) + if (prepare_underlying_privilege(thd, table_list) || + mysql_handle_derived(thd->lex, &mysql_derived_prepare)) goto error; #ifndef NO_EMBEDDED_ACCESS_CHECKS @@ -1151,6 +1152,7 @@ #ifndef NO_EMBEDDED_ACCESS_CHECKS table_list->grant.want_privilege= want_privilege; table_list->table->grant.want_privilege= want_privilege; + table_list->register_want_access(want_privilege); #endif thd->lex->select_lex.no_wrap_view_item= TRUE; res= setup_fields(thd, 0, select->item_list, 1, 0, 0); @@ -1162,6 +1164,7 @@ table_list->grant.want_privilege= table_list->table->grant.want_privilege= (SELECT_ACL & ~table_list->table->grant.privilege); + table_list->register_want_access(SELECT_ACL); #endif if (setup_fields(thd, 0, stmt->lex->value_list, 0, 0, 0)) goto error;