maria-developers team mailing list archive
-
maria-developers team
-
Mailing list archive
-
Message #08807
Re: PLEASE REVIEW: (MDEV-7574) Security definer views don't work with CONNECT ODBC tables
Hi Sergei,
Sorry for delay, I was busy with 10.1 issues.
Thanks for review. A new patch is attached.
This is a major rewrite since last time.
I think the code now looks much easier to understand.
Please see comments below.
On 04/29/2015 06:44 PM, Sergei Golubchik wrote:
Hi, Alexander!
On Feb 24, Alexander Barkov wrote:
There is only one problem with that. In case of embedded server
table->grant.privilege is always 0, because the embedded version
of check_table_access() is just an empty function.
This change in sql/handler.cc, in handler::ha_external_lock() helps:
+#ifdef NO_EMBEDDED_ACCESS_CHECKS
+ table->grant.privilege= ~NO_ACCESS;
+#endif
May be, it'd be better to do it in check_table_access() ?
With a comment "plugins (e.g. CONNECT engine) should not depend on
whether embedded is built with NO_EMBEDDED_ACCESS_CHECKS or without".
Thanks for the idea. Note, check_table_access() is not called
in case of some SQLCOM_XXXX. So I slightly extended your idea
and added this code into st_select_lex::add_table_to_list() instead.
This is the place where TABLE_LIST is first initialized.
=== modified file 'sql/handler.cc'
--- sql/handler.cc 2015-01-21 11:03:02 +0000
+++ sql/handler.cc 2015-02-24 11:47:05 +0000
@@ -5873,6 +5873,9 @@ int handler::ha_external_lock(THD *thd,
ha_statistic_increment(&SSV::ha_external_lock_count);
+#ifdef NO_EMBEDDED_ACCESS_CHECKS
+ table->grant.privilege= ~NO_ACCESS;
+#endif
See above.
If you could't put this in check_table_access(), then, at least, add
this comment here.
/*
We cache the table flags if the locking succeeded. Otherwise, we
keep them as they were when they were fetched in ha_open().
=== modified file 'storage/connect/ha_connect.cc'
--- storage/connect/ha_connect.cc 2015-02-11 20:39:41 +0000
+++ storage/connect/ha_connect.cc 2015-02-24 12:03:25 +0000
@@ -3922,7 +3922,21 @@ int ha_connect::delete_all_rows()
} // end of delete_all_rows
-bool ha_connect::check_privileges(THD *thd, PTOS options, char *dbn)
+/**
+ Check privileges.
+ @param THD - Current thread
+ @param options - Connect table options
+ @param dbn - database name
+ @param using_table_privilege - whether check table->grant.privilege,
+ or execute check_access(FILE_ACL).
+
+ Using table->grant.privilege is important in cases when we need to take into
+ account privileges of the VIEW definer when accessing to a view created with
+ "CREATE VIEW v1 SQL SECURITY DEFINER".
+ See ha_connect::check_privileges_external_lock() for details.
+*/
+bool ha_connect::check_privileges(THD *thd, PTOS options,
+ char *dbn, bool using_table_privilege)
{
const char *db= (dbn && *dbn) ? dbn : NULL;
TABTYPE type=GetRealType(options);
@@ -4143,6 +4180,67 @@ MODE ha_connect::CheckMode(PGLOBAL g, TH
return newmode;
} // end of check_mode
+
+/**
+ A check_privilege() wrapper for external_lock().
+ Decides if check_privilege():
+ - should test table->grant.privilege for FILE_ACL
+ - or should call check_access(FILE_ACL)
+ depending on the current SQL command and lock type.
+*/
+bool ha_connect::check_privileges_external_lock(PGLOBAL g, THD *thd,
+ PTOS options, int lock_type)
+{
+ bool use_table_priv;
+ switch (thd_sql_command(thd))
+ {
+ case SQLCOM_SELECT:
+ case SQLCOM_UPDATE:
+ case SQLCOM_INSERT:
+ case SQLCOM_DELETE:
+ case SQLCOM_REPLACE:
+ case SQLCOM_LOAD:
+ use_table_priv= true; // use table->grant.privilege
+ break;
+
+ case SQLCOM_CREATE_TABLE:
+ case SQLCOM_INSERT_SELECT:
+ case SQLCOM_REPLACE_SELECT:
+ case SQLCOM_UPDATE_MULTI:
+ case SQLCOM_DELETE_MULTI:
+ /*
+ CREATE TABLE target_table AS SELECT * FROM source_table;
+ INSERT INTO target_table SELECT * FROM source_table;
+ REPLACE INTO target_table SELECT * FROM source_table;
+ UPDATE target_table,source_table SET target_table.column=xxx WHERE ...;
+ DELETE target_table FROM target_table,source_table WHERE ...;
+
+ If we're working with "source_table", use table->grant.privilege.
+ If we're working with "target_table", use check_access().
+ */
+ use_table_priv= lock_type != F_WRLCK;
I don't quite understand that. Why use_table_priv is FALSE for these
commands? Like, why it's true for SQLCOM_INSERT, but false for
SQLCOM_INSERT_SELECT? True for SQLCOM_UPDATE, false for
SQLCOM_UPDATE_MULTI?
Other cases below aren't clear either.
Could you explain the rule - when one should use table->grant.privilege
and when check_access()? I mean, not a list of cases, but a general
underlying rule.
The main problem was that the result of check_access(),
which is done in sql_parse.cc through a number of various
SQLCOM_XXX dependent functions (e.g. insert_precheck),
and which extracts the privileges for the current effective user
(which can be invoker or definer, depending on SQL SECURITY clause, e.g.
in case of VIEW), was not always available in external_lock()
in table.grant->privileges (which was just 0 in some case)
So I composed this code heuristically, just testing what works.
The goal was:
- to reuse table.grant->privileges when it has a valid value that
originates from check_access() made in sql_parse.cc
- to call its own check_access() when table.grant->privileges is
just set to 0. Of course, using check_access() could be be wrong,
because it could check privileges for invoker instead of definer
in some case. This problem would be important for calling
the affected statements from stored procedures.
There were no general rule, because availability of the check_access()
result heavily depended on the exact SQLCOM_XXXX command.
So that was just a switch() that worked somehow.
Now I rewrote the code to make the original check_access() result
be available in much more cases and extended the test a lot.
So external_lock() now uses table.grant->privilege for all SQLCOM_XXXX.
Note, check_access() is still called in ha_connect::create() and
ha_connect::delete_or_rename_table(). I'd like to get rid of this
eventually, in a separate change. You'll find related comments
in the patch.
Thanks.
+ break;
+
+ case SQLCOM_TRUNCATE:
+ case SQLCOM_LOCK_TABLES:
+ case SQLCOM_DROP_TABLE:
+ case SQLCOM_RENAME_TABLE:
+ case SQLCOM_CREATE_VIEW:
+ case SQLCOM_DROP_VIEW:
+ case SQLCOM_ALTER_TABLE:
+ case SQLCOM_DROP_INDEX:
+ case SQLCOM_CREATE_INDEX:
+ case SQLCOM_OPTIMIZE:
+ use_table_priv= false; // use check_access()
+ break;
+ default:
+ report_unsupported_sql_command(g, thd);
+ return true; // Something went wrong, deny access.
+ }
+ return check_privileges(thd, options, table->s->db.str, use_table_priv);
+}
+
+
int ha_connect::start_stmt(THD *thd, thr_lock_type lock_type)
{
int rc= 0;
@@ -4614,7 +4712,7 @@ int ha_connect::delete_or_rename_table(c
if (!open_table_def(thd, share)) {
// Now we can work
if ((pos= share->option_struct)) {
- if (check_privileges(thd, pos, db))
+ if (check_privileges(thd, pos, db, false))
rc= HA_ERR_INTERNAL_ERROR; // ???
else
if (IsFileType(GetRealType(pos)) && !pos->filename)
@@ -5592,7 +5690,7 @@ int ha_connect::create(const char *name,
DBUG_RETURN(HA_ERR_INTERNAL_ERROR);
} // endif ttp
- if (check_privileges(thd, options, GetDBfromName(name)))
+ if (check_privileges(thd, options, GetDBfromName(name), false))
DBUG_RETURN(HA_ERR_INTERNAL_ERROR);
inward= IsFileType(type) && !options->filename;
Regards,
Sergei
diff --git a/sql/datadict.cc b/sql/datadict.cc
index 62d60ed..2f01bf2 100644
--- a/sql/datadict.cc
+++ b/sql/datadict.cc
@@ -169,7 +169,7 @@ bool dd_recreate_table(THD *thd, const char *db, const char *table_name,
}
/* Attempt to reconstruct the table. */
- error= ha_create_table(thd, path, db, table_name, &create_info, NULL);
+ error= ha_create_table(thd, path, db, table_name, &create_info, NULL, 0);
DBUG_RETURN(error);
}
diff --git a/sql/handler.cc b/sql/handler.cc
index 1f8daf3..a44e3b1 100644
--- a/sql/handler.cc
+++ b/sql/handler.cc
@@ -4630,7 +4630,8 @@ void handler::update_global_index_stats()
*/
int ha_create_table(THD *thd, const char *path,
const char *db, const char *table_name,
- HA_CREATE_INFO *create_info, LEX_CUSTRING *frm)
+ HA_CREATE_INFO *create_info, LEX_CUSTRING *frm,
+ ulong privilege)
{
int error= 1;
TABLE table;
@@ -4667,7 +4668,8 @@ int ha_create_table(THD *thd, const char *path,
share.m_psi= PSI_CALL_get_table_share(temp_table, &share);
- if (open_table_from_share(thd, &share, "", 0, READ_ALL, 0, &table, true))
+ if (open_table_from_share(thd, &share, "", 0, READ_ALL, 0,
+ &table, true, privilege))
goto err;
update_create_info_from_table(create_info, &table);
diff --git a/sql/handler.h b/sql/handler.h
index 2e219d5..e488f79 100644
--- a/sql/handler.h
+++ b/sql/handler.h
@@ -4043,7 +4043,8 @@ void ha_checkpoint_state(bool disable);
void ha_commit_checkpoint_request(void *cookie, void (*pre_hook)(void *));
int ha_create_table(THD *thd, const char *path,
const char *db, const char *table_name,
- HA_CREATE_INFO *create_info, LEX_CUSTRING *frm);
+ HA_CREATE_INFO *create_info, LEX_CUSTRING *frm,
+ ulong privilege);
int ha_delete_table(THD *thd, handlerton *db_type, const char *path,
const char *db, const char *alias, bool generate_warning);
diff --git a/sql/sql_admin.cc b/sql/sql_admin.cc
index a6b97ce..f761db4 100644
--- a/sql/sql_admin.cc
+++ b/sql/sql_admin.cc
@@ -135,7 +135,7 @@ static int prepare_for_repair(THD *thd, TABLE_LIST *table_list,
if (share == NULL)
DBUG_RETURN(0); // Can't open frm file
- if (open_table_from_share(thd, share, "", 0, 0, 0, &tmp_table, FALSE))
+ if (open_table_from_share(thd, share, "", 0, 0, 0, &tmp_table, FALSE, 0))
{
tdc_release_share(share);
DBUG_RETURN(0); // Out of memory
diff --git a/sql/sql_base.cc b/sql/sql_base.cc
index 13b8625..68aed5c 100644
--- a/sql/sql_base.cc
+++ b/sql/sql_base.cc
@@ -2511,7 +2511,7 @@ bool open_table(THD *thd, TABLE_LIST *table_list, MEM_ROOT *mem_root,
HA_TRY_READ_ONLY),
(READ_KEYINFO | COMPUTE_TYPES |
EXTRA_RECORD),
- thd->open_options, table, FALSE);
+ thd->open_options, table, FALSE, 0);
if (error)
{
@@ -3346,7 +3346,7 @@ static bool auto_repair_table(THD *thd, TABLE_LIST *table_list)
HA_TRY_READ_ONLY),
READ_KEYINFO | COMPUTE_TYPES | EXTRA_RECORD,
ha_open_options | HA_OPEN_FOR_REPAIR,
- entry, FALSE) || ! entry->file ||
+ entry, FALSE, 0) || ! entry->file ||
(entry->file->is_crashed() && entry->file->ha_check_and_repair(thd)))
{
/* Give right error message */
@@ -5621,7 +5621,8 @@ TABLE *open_table_uncached(THD *thd, handlerton *hton,
const char *path, const char *db,
const char *table_name,
bool add_to_temporary_tables_list,
- bool open_in_engine)
+ bool open_in_engine,
+ ulong privilege)
{
TABLE *tmp_table;
TABLE_SHARE *share;
@@ -5681,7 +5682,7 @@ TABLE *open_table_uncached(THD *thd, handlerton *hton,
Set "is_create_table" if the table does not
exist in SE
*/
- open_in_engine ? false : true))
+ open_in_engine ? false : true, privilege))
{
/* No need to lock share->mutex as this is not needed for tmp tables */
free_table_share(share);
@@ -5690,7 +5691,7 @@ TABLE *open_table_uncached(THD *thd, handlerton *hton,
}
tmp_table->reginfo.lock_type= TL_WRITE; // Simulate locked
- tmp_table->grant.privilege= TMP_TABLE_ACLS;
+ tmp_table->grant.privilege= privilege;
share->tmp_table= (tmp_table->file->has_transactions() ?
TRANSACTIONAL_TMP_TABLE : NON_TRANSACTIONAL_TMP_TABLE);
diff --git a/sql/sql_base.h b/sql/sql_base.h
index a6d9019..d9b1a1f 100644
--- a/sql/sql_base.h
+++ b/sql/sql_base.h
@@ -130,7 +130,7 @@ bool get_key_map_from_key_list(key_map *map, TABLE *table,
TABLE *open_table_uncached(THD *thd, handlerton *hton, const char *path,
const char *db, const char *table_name,
bool add_to_temporary_tables_list,
- bool open_in_engine);
+ bool open_in_engine, ulong privilege);
TABLE *find_locked_table(TABLE *list, const char *db, const char *table_name);
TABLE *find_write_locked_table(TABLE *list, const char *db,
const char *table_name);
diff --git a/sql/sql_insert.cc b/sql/sql_insert.cc
index 1ec33a0..d0cb0f7 100644
--- a/sql/sql_insert.cc
+++ b/sql/sql_insert.cc
@@ -3952,6 +3952,7 @@ static TABLE *create_table_from_items(THD *thd, HA_CREATE_INFO *create_info,
}
/* Restore */
create_table->open_strategy= save_open_strategy;
+ create_table->table->grant.privilege= create_table->grant.privilege;
}
else
{
diff --git a/sql/sql_parse.cc b/sql/sql_parse.cc
index 5635e9a..55cc4be 100644
--- a/sql/sql_parse.cc
+++ b/sql/sql_parse.cc
@@ -6936,6 +6936,17 @@ TABLE_LIST *st_select_lex::add_table_to_list(THD *thd,
#ifdef WITH_PARTITION_STORAGE_ENGINE
ptr->partition_names= partition_names;
#endif /* WITH_PARTITION_STORAGE_ENGINE */
+#ifdef NO_EMBEDDED_ACCESS_CHECKS
+ /*
+ Plugins (e.g. CONNECT engine) should not depend on whether
+ embedded library is built with or without NO_EMBEDDED_ACCESS_CHECKS.
+ ha_xxx::external_lock() expects that tables->grant.privilege
+ is initialized to allow all privileges if NO_EMBEDDED_ACCESS_CHECKS.
+ E.g. ha_connect::external_lock() needs FILE_ACL.
+ */
+ ptr->grant.privilege= ~NO_ACCESS;
+#endif
+
/* Link table in global list (all used tables) */
lex->add_to_query_tables(ptr);
diff --git a/sql/sql_show.cc b/sql/sql_show.cc
index 2413cb5..fd2c118 100644
--- a/sql/sql_show.cc
+++ b/sql/sql_show.cc
@@ -4634,7 +4634,7 @@ static int fill_schema_table_from_frm(THD *thd, TABLE_LIST *tables,
if (!open_table_from_share(thd, share, table_name->str, 0,
(EXTRA_RECORD | OPEN_FRM_FILE_ONLY),
- thd->open_options, &tbl, FALSE))
+ thd->open_options, &tbl, FALSE, 0))
{
tbl.s= share;
table_list.table= &tbl;
diff --git a/sql/sql_table.cc b/sql/sql_table.cc
index 20cfcca..bf1e4fb 100644
--- a/sql/sql_table.cc
+++ b/sql/sql_table.cc
@@ -4837,7 +4837,8 @@ int create_table_impl(THD *thd,
*/
TABLE *table= open_table_uncached(thd, create_info->db_type, path,
- db, table_name, true, true);
+ db, table_name, true, true,
+ TMP_TABLE_ACLS);
if (!table)
{
@@ -4871,7 +4872,7 @@ int create_table_impl(THD *thd,
bool result= (open_table_def(thd, &share, GTS_TABLE) ||
open_table_from_share(thd, &share, "", 0, (uint) READ_ALL,
- 0, &table, true));
+ 0, &table, true, 0));
if (!result)
(void) closefrm(&table, 0);
@@ -8720,7 +8721,15 @@ bool mysql_alter_table(THD *thd,char *new_db, char *new_name,
alter_ctx.get_tmp_path(),
alter_ctx.new_db,
alter_ctx.tmp_name,
- true, false)))
+ true, false,
+ /*
+ Some engine, e.g. CONNECT,
+ need to know if FILE_ACL
+ presents.
+ */
+ TMP_TABLE_ACLS |
+ (table_list->grant.privilege &
+ FILE_ACL))))
goto err_new_table_cleanup;
/* Set markers for fields in TABLE object for altered table. */
@@ -8865,7 +8874,7 @@ bool mysql_alter_table(THD *thd,char *new_db, char *new_name,
if (ha_create_table(thd, alter_ctx.get_tmp_path(),
alter_ctx.new_db, alter_ctx.tmp_name,
- create_info, &frm))
+ create_info, &frm, table_list->grant.privilege))
goto err_new_table_cleanup;
/* Mark that we have created table in storage engine. */
@@ -8876,7 +8885,7 @@ bool mysql_alter_table(THD *thd,char *new_db, char *new_name,
if (!open_table_uncached(thd, new_db_type,
alter_ctx.get_tmp_path(),
alter_ctx.new_db, alter_ctx.tmp_name,
- true, true))
+ true, true, TMP_TABLE_ACLS))
goto err_new_table_cleanup;
}
@@ -8897,7 +8906,7 @@ bool mysql_alter_table(THD *thd,char *new_db, char *new_name,
/* Open our intermediate table. */
new_table= open_table_uncached(thd, new_db_type, alter_ctx.get_tmp_path(),
alter_ctx.new_db, alter_ctx.tmp_name,
- true, true);
+ true, true, table_list->grant.privilege);
}
if (!new_table)
goto err_new_table_cleanup;
@@ -9111,7 +9120,7 @@ bool mysql_alter_table(THD *thd,char *new_db, char *new_name,
TABLE *t_table;
t_table= open_table_uncached(thd, new_db_type, alter_ctx.get_new_path(),
alter_ctx.new_db, alter_ctx.new_name,
- false, true);
+ false, true, TMP_TABLE_ACLS);
if (t_table)
intern_close_table(t_table);
else
diff --git a/sql/sql_truncate.cc b/sql/sql_truncate.cc
index 05869b7..92acf32 100644
--- a/sql/sql_truncate.cc
+++ b/sql/sql_truncate.cc
@@ -297,7 +297,8 @@ static bool recreate_temporary_table(THD *thd, TABLE *table)
if ((new_table= open_table_uncached(thd, table_type, share->path.str,
share->db.str,
- share->table_name.str, true, true)))
+ share->table_name.str, true, true,
+ TMP_TABLE_ACLS)))
{
error= FALSE;
thd->thread_specific_used= TRUE;
diff --git a/sql/table.cc b/sql/table.cc
index 053269a..4cd4fa3 100644
--- a/sql/table.cc
+++ b/sql/table.cc
@@ -2543,7 +2543,8 @@ bool unpack_vcol_info_from_frm(THD *thd,
enum open_frm_error open_table_from_share(THD *thd, TABLE_SHARE *share,
const char *alias, uint db_stat, uint prgflag,
uint ha_open_flags, TABLE *outparam,
- bool is_create_table)
+ bool is_create_table,
+ ulong privilege)
{
enum open_frm_error error;
uint records, i, bitmap_size;
@@ -2559,6 +2560,7 @@ enum open_frm_error open_table_from_share(THD *thd, TABLE_SHARE *share,
error= OPEN_FRM_ERROR_ALREADY_ISSUED; // for OOM errors below
bzero((char*) outparam, sizeof(*outparam));
+ outparam->grant.privilege= privilege;
outparam->in_use= thd;
outparam->s= share;
outparam->db_stat= db_stat;
diff --git a/sql/table.h b/sql/table.h
index 39faa8b..6a668b0 100644
--- a/sql/table.h
+++ b/sql/table.h
@@ -2557,7 +2557,7 @@ void init_mdl_requests(TABLE_LIST *table_list);
enum open_frm_error open_table_from_share(THD *thd, TABLE_SHARE *share,
const char *alias, uint db_stat, uint prgflag,
uint ha_open_flags, TABLE *outparam,
- bool is_create_table);
+ bool is_create_table, ulong privilege);
bool unpack_vcol_info_from_frm(THD *thd, MEM_ROOT *mem_root,
TABLE *table, Field *field,
LEX_STRING *vcol_expr, bool *error_reported);
diff --git a/sql/unireg.cc b/sql/unireg.cc
index 12d3c26..e8fa92e 100644
--- a/sql/unireg.cc
+++ b/sql/unireg.cc
@@ -375,7 +375,7 @@ int rea_create_table(THD *thd, LEX_CUSTRING *frm,
if (!no_ha_create_table)
{
- if (ha_create_table(thd, path, db, table_name, create_info, frm))
+ if (ha_create_table(thd, path, db, table_name, create_info, frm, 0))
goto err_part;
}
diff --git a/storage/connect/ha_connect.cc b/storage/connect/ha_connect.cc
index c2fb648..51e7102 100644
--- a/storage/connect/ha_connect.cc
+++ b/storage/connect/ha_connect.cc
@@ -955,7 +955,7 @@ ulonglong ha_connect::table_flags() const
flags|= (HA_HAS_RECORDS | HA_STATS_RECORDS_IS_EXACT);
// No data change on ALTER for outward tables
- if (!IsFileType(type) || hp->FileExists(pos->filename, true))
+ if (!IsFileType(type) || hp->FileExists(pos->filename, true, true))
flags|= HA_NO_COPY_ON_ALTER;
} // endif pos
@@ -3964,7 +3964,21 @@ int ha_connect::delete_all_rows()
} // end of delete_all_rows
-bool ha_connect::check_privileges(THD *thd, PTOS options, char *dbn)
+/**
+ Check privileges.
+ @param THD - Current thread
+ @param options - Connect table options
+ @param dbn - database name
+ @param using_table_privilege - whether check table->grant.privilege,
+ or execute check_access(FILE_ACL).
+
+ Using table->grant.privilege is important in cases when we need to take into
+ account privileges of the VIEW definer when accessing to a view created with
+ "CREATE VIEW v1 SQL SECURITY DEFINER".
+ See ha_connect::check_privileges_external_lock() for details.
+*/
+bool ha_connect::check_privileges(THD *thd, PTOS options, char *dbn,
+ bool using_table_privilege)
{
const char *db= (dbn && *dbn) ? dbn : NULL;
TABTYPE type=GetRealType(options);
@@ -4020,7 +4034,33 @@ bool ha_connect::check_privileges(THD *thd, PTOS options, char *dbn)
case TAB_MAC:
case TAB_WMI:
case TAB_OEM:
- return check_access(thd, FILE_ACL, db, NULL, NULL, 0, 0);
+ if (using_table_privilege)
+ {
+ // Called from ::external_lock(), respect VIEW's definer
+ if (table->grant.privilege & FILE_ACL)
+ return false;
+ else
+ {
+ my_error(ER_ACCESS_DENIED_ERROR, MYF(0),
+ thd->security_ctx->priv_user,
+ thd->security_ctx->priv_host,
+ (thd->password ? ER(ER_YES) : ER(ER_NO)));
+ return true;
+ }
+ }
+ else
+ {
+ /*
+ Called from ::create() or ::delete_or_rename_table().
+ TODO:
+ - change create() to use table->grant.privilege as well
+ (needs some additional refactoring in /sql)
+ - change delete_or_rename_table() not to require FILE_ACL,
+ This should be safe, as DROP and RENAME queries do not
+ actually read or modify the underlying data file.
+ */
+ return check_access(thd, FILE_ACL, db, NULL, NULL, 0, 0);
+ }
// This is temporary until a solution is found
case TAB_TBL:
@@ -4061,6 +4101,15 @@ bool ha_connect::IsSameIndex(PIXDEF xp1, PIXDEF xp2)
return b;
} // end of IsSameIndex
+
+void ha_connect::report_unsupported_sql_command(PGLOBAL g, THD *thd)
+{
+ htrc("Unsupported sql_command=%d\n", thd_sql_command(thd));
+ strcpy(g->Message, "CONNECT Unsupported command");
+ my_message(ER_NOT_ALLOWED_COMMAND, g->Message, MYF(0));
+}
+
+
MODE ha_connect::CheckMode(PGLOBAL g, THD *thd,
MODE newmode, bool *chk, bool *cras)
{
@@ -4121,9 +4170,7 @@ MODE ha_connect::CheckMode(PGLOBAL g, THD *thd,
// } // endif partitioned
default:
- htrc("Unsupported sql_command=%d\n", thd_sql_command(thd));
- strcpy(g->Message, "CONNECT Unsupported command");
- my_message(ER_NOT_ALLOWED_COMMAND, g->Message, MYF(0));
+ report_unsupported_sql_command(g, thd);
newmode= MODE_ERROR;
break;
} // endswitch newmode
@@ -4175,9 +4222,7 @@ MODE ha_connect::CheckMode(PGLOBAL g, THD *thd,
newmode= MODE_READ;
break;
default:
- htrc("Unsupported sql_command=%d\n", thd_sql_command(thd));
- strcpy(g->Message, "CONNECT Unsupported command");
- my_message(ER_NOT_ALLOWED_COMMAND, g->Message, MYF(0));
+ report_unsupported_sql_command(g, thd);
newmode= MODE_ERROR;
break;
} // endswitch newmode
@@ -4190,6 +4235,43 @@ MODE ha_connect::CheckMode(PGLOBAL g, THD *thd,
return newmode;
} // end of check_mode
+
+/**
+ A check_privileges() wrapper for external_lock().
+*/
+bool ha_connect::check_privileges_external_lock(PGLOBAL g, THD *thd,
+ PTOS options)
+{
+ switch (thd_sql_command(thd))
+ {
+ case SQLCOM_SELECT:
+ case SQLCOM_UPDATE:
+ case SQLCOM_INSERT:
+ case SQLCOM_DELETE:
+ case SQLCOM_REPLACE:
+ case SQLCOM_LOAD:
+ case SQLCOM_CREATE_TABLE:
+ case SQLCOM_INSERT_SELECT:
+ case SQLCOM_REPLACE_SELECT:
+ case SQLCOM_UPDATE_MULTI:
+ case SQLCOM_DELETE_MULTI:
+ case SQLCOM_TRUNCATE:
+ case SQLCOM_LOCK_TABLES:
+ case SQLCOM_DROP_TABLE:
+ case SQLCOM_RENAME_TABLE:
+ case SQLCOM_CREATE_VIEW:
+ case SQLCOM_DROP_INDEX:
+ case SQLCOM_CREATE_INDEX:
+ case SQLCOM_OPTIMIZE:
+ case SQLCOM_ALTER_TABLE:
+ return check_privileges(thd, options, table->s->db.str, true);
+ default:
+ report_unsupported_sql_command(g, thd);
+ }
+ return true; // Something went wrong, deny access.
+}
+
+
int ha_connect::start_stmt(THD *thd, thr_lock_type lock_type)
{
int rc= 0;
@@ -4410,7 +4492,7 @@ int ha_connect::external_lock(THD *thd, int lock_type)
DBUG_ASSERT(table && table->s);
- if (check_privileges(thd, options, table->s->db.str)) {
+ if (check_privileges_external_lock(g, thd, options)) {
strcpy(g->Message, "This operation requires the FILE privilege");
htrc("%s\n", g->Message);
DBUG_RETURN(HA_ERR_INTERNAL_ERROR);
@@ -4661,7 +4743,7 @@ int ha_connect::delete_or_rename_table(const char *name, const char *to)
if (!open_table_def(thd, share)) {
// Now we can work
if ((pos= share->option_struct)) {
- if (check_privileges(thd, pos, db))
+ if (check_privileges(thd, pos, db, false))
rc= HA_ERR_INTERNAL_ERROR; // ???
else
if (IsFileType(GetRealType(pos)) && !pos->filename)
@@ -5670,6 +5752,14 @@ int ha_connect::create(const char *name, TABLE *table_arg,
PGLOBAL g= xp->g;
DBUG_ENTER("ha_connect::create");
+ /*
+ This assignment fixes sporadic test failures if some
+ "ALTER TABLE t1 ADD KEY(a)" query exits on ER_ACCESS_DENIED_ERROR
+ (e.g. on missing FILE_ACL). All following "CREATE TABLE" failed with
+ "ERROR 1105: CONNECT index modification should be in-place"
+ TODO: check with Olivier.
+ */
+ g->Xchk= NULL;
int sqlcom= thd_sql_command(table_arg->in_use);
PTOS options= GetTableOptionStruct(table_arg->s);
@@ -5699,7 +5789,7 @@ int ha_connect::create(const char *name, TABLE *table_arg,
DBUG_RETURN(HA_ERR_INTERNAL_ERROR);
} // endif ttp
- if (check_privileges(thd, options, GetDBfromName(name)))
+ if (check_privileges(thd, options, GetDBfromName(name), false))
DBUG_RETURN(HA_ERR_INTERNAL_ERROR);
inward= IsFileType(type) && !options->filename;
@@ -6066,7 +6156,7 @@ int ha_connect::create(const char *name, TABLE *table_arg,
#endif // WITH_PARTITION_STORAGE_ENGINE
if (g->Alchecked == 0 &&
- (!IsFileType(type) || FileExists(options->filename, false))) {
+ (!IsFileType(type) || FileExists(options->filename, false, true))) {
if (part_info) {
sprintf(g->Message, "Data repartition in %s is unchecked", partname);
push_warning(thd, Sql_condition::WARN_LEVEL_WARN, 0, g->Message);
@@ -6146,7 +6236,7 @@ int ha_connect::create(const char *name, TABLE *table_arg,
- file does not exist or is void
- user has file privilege
*/
-bool ha_connect::FileExists(const char *fn, bool bf)
+bool ha_connect::FileExists(const char *fn, bool bf, bool check_privilege)
{
if (!fn || !*fn)
return false;
@@ -6159,8 +6249,7 @@ bool ha_connect::FileExists(const char *fn, bool bf)
int n;
struct stat info;
- if (check_access(ha_thd(), FILE_ACL, table->s->db.str,
- NULL, NULL, 0, 0))
+ if (check_privilege && !(table->grant.privilege & FILE_ACL))
return true;
#if defined(__WIN__)
@@ -6426,7 +6515,7 @@ ha_connect::check_if_supported_inplace_alter(TABLE *altered_table,
char *fn= GetStringOption("filename");
tshp= NULL;
- if (FileExists(fn, false)) {
+ if (FileExists(fn, false, false)) {
strcpy(g->Message, "Operation denied. Table data would be lost.");
my_message(ER_UNKNOWN_ERROR, g->Message, MYF(0));
DBUG_RETURN(HA_ALTER_ERROR);
diff --git a/storage/connect/ha_connect.h b/storage/connect/ha_connect.h
index 611f9ba..89af0df 100644
--- a/storage/connect/ha_connect.h
+++ b/storage/connect/ha_connect.h
@@ -213,7 +213,7 @@ class ha_connect: public handler
bool SetIntegerOption(char *opname, int n);
bool SameInt(TABLE *tab, char *opn);
bool SameBool(TABLE *tab, char *opn);
- bool FileExists(const char *fn, bool bf);
+ bool FileExists(const char *fn, bool bf, bool check_privilege);
bool NoFieldOptionChange(TABLE *tab);
PFOS GetFieldOptionStruct(Field *fp);
void *GetColumnOption(PGLOBAL g, void *field, PCOLINFO pcf);
@@ -534,8 +534,11 @@ int index_prev(uchar *buf);
private:
DsMrr_impl ds_mrr;
+ void report_unsupported_sql_command(PGLOBAL g, THD *thd);
protected:
- bool check_privileges(THD *thd, PTOS options, char *dbn);
+ bool check_privileges(THD *thd, PTOS options, char *dbn,
+ bool using_table_privilege);
+ bool check_privileges_external_lock(PGLOBAL g, THD *thd, PTOS options);
MODE CheckMode(PGLOBAL g, THD *thd, MODE newmode, bool *chk, bool *cras);
char *GetDBfromName(const char *name);
diff --git a/storage/connect/mysql-test/connect/r/grant.result b/storage/connect/mysql-test/connect/r/grant.result
index ba57287..9a0a549 100644
--- a/storage/connect/mysql-test/connect/r/grant.result
+++ b/storage/connect/mysql-test/connect/r/grant.result
@@ -46,7 +46,7 @@ ERROR 28000: Access denied for user 'user'@'localhost' (using password: NO)
SELECT user();
user()
root@localhost
-CREATE VIEW v1 AS SELECT * FROM t1;
+CREATE SQL SECURITY INVOKER VIEW v1 AS SELECT * FROM t1;
SELECT user();
user()
user@localhost
@@ -130,7 +130,7 @@ ERROR 28000: Access denied for user 'user'@'localhost' (using password: NO)
SELECT user();
user()
root@localhost
-CREATE VIEW v1 AS SELECT * FROM t1;
+CREATE SQL SECURITY INVOKER VIEW v1 AS SELECT * FROM t1;
SELECT user();
user()
user@localhost
@@ -224,7 +224,7 @@ ERROR 28000: Access denied for user 'user'@'localhost' (using password: NO)
SELECT user();
user()
root@localhost
-CREATE VIEW v1 AS SELECT * FROM t1;
+CREATE SQL SECURITY INVOKER VIEW v1 AS SELECT * FROM t1;
SELECT user();
user()
user@localhost
@@ -318,7 +318,7 @@ ERROR 28000: Access denied for user 'user'@'localhost' (using password: NO)
SELECT user();
user()
root@localhost
-CREATE VIEW v1 AS SELECT * FROM t1;
+CREATE SQL SECURITY INVOKER VIEW v1 AS SELECT * FROM t1;
SELECT user();
user()
user@localhost
@@ -412,7 +412,7 @@ ERROR 28000: Access denied for user 'user'@'localhost' (using password: NO)
SELECT user();
user()
root@localhost
-CREATE VIEW v1 AS SELECT * FROM t1;
+CREATE SQL SECURITY INVOKER VIEW v1 AS SELECT * FROM t1;
SELECT user();
user()
user@localhost
@@ -506,7 +506,7 @@ ERROR 28000: Access denied for user 'user'@'localhost' (using password: NO)
SELECT user();
user()
root@localhost
-CREATE VIEW v1 AS SELECT * FROM t1;
+CREATE SQL SECURITY INVOKER VIEW v1 AS SELECT * FROM t1;
SELECT user();
user()
user@localhost
diff --git a/storage/connect/mysql-test/connect/r/grant2.result b/storage/connect/mysql-test/connect/r/grant2.result
new file mode 100644
index 0000000..b99841e
--- /dev/null
+++ b/storage/connect/mysql-test/connect/r/grant2.result
@@ -0,0 +1,667 @@
+#
+# MDEV-7574 Security definer views don't work with CONNECT ODBC tables
+#
+GRANT ALL PRIVILEGES ON *.* TO user@localhost;
+REVOKE FILE ON *.* FROM user@localhost;
+# Testing SQLCOM_SELECT
+CREATE TABLE t1 (a INT) ENGINE=CONNECT TABLE_TYPE=fix FILE_NAME='t1.fix';
+INSERT INTO t1 VALUES (10);
+CREATE SQL SECURITY INVOKER VIEW v1_invoker AS SELECT * FROM t1;
+CREATE SQL SECURITY DEFINER VIEW v1_definer AS SELECT * FROM t1;
+SELECT * FROM t1;
+a
+10
+SELECT * FROM v1_invoker;
+a
+10
+SELECT * FROM v1_definer;
+a
+10
+SELECT * FROM t1;
+ERROR 28000: Access denied for user 'user'@'localhost' (using password: NO)
+SELECT * FROM v1_invoker;
+ERROR 28000: Access denied for user 'user'@'localhost' (using password: NO)
+SELECT * FROM v1_definer;
+a
+10
+DROP VIEW v1_invoker, v1_definer;
+DROP TABLE t1;
+# Testing SQLCOM_UPDATE
+CREATE TABLE t1 (a INT) ENGINE=CONNECT TABLE_TYPE=fix FILE_NAME='t1.fix';
+INSERT INTO t1 VALUES (10);
+CREATE SQL SECURITY INVOKER VIEW v1_invoker AS SELECT * FROM t1;
+CREATE SQL SECURITY DEFINER VIEW v1_definer AS SELECT * FROM t1;
+UPDATE t1 SET a=11;
+UPDATE v1_invoker SET a=12;
+UPDATE v1_definer SET a=13;
+UPDATE t1 SET a=21;
+ERROR 28000: Access denied for user 'user'@'localhost' (using password: NO)
+UPDATE v1_invoker SET a=22;
+ERROR 28000: Access denied for user 'user'@'localhost' (using password: NO)
+UPDATE v1_definer SET a=23;
+DROP VIEW v1_invoker, v1_definer;
+DROP TABLE t1;
+# Testing SQLCOM_INSERT
+CREATE TABLE t1 (a INT) ENGINE=CONNECT TABLE_TYPE=fix FILE_NAME='t1.fix';
+INSERT INTO t1 VALUES (10);
+CREATE SQL SECURITY INVOKER VIEW v1_invoker AS SELECT * FROM t1;
+CREATE SQL SECURITY DEFINER VIEW v1_definer AS SELECT * FROM t1;
+INSERT INTO t1 VALUES (11);
+INSERT INTO v1_invoker VALUES (12);
+INSERT INTO v1_definer VALUES (13);
+INSERT INTO t1 VALUES (21);
+ERROR 28000: Access denied for user 'user'@'localhost' (using password: NO)
+INSERT INTO v1_invoker VALUES (22);
+ERROR 28000: Access denied for user 'user'@'localhost' (using password: NO)
+INSERT INTO v1_definer VALUES (23);
+DROP VIEW v1_invoker, v1_definer;
+DROP TABLE t1;
+# Testing SQLCOM_REPLACE
+CREATE TABLE t1 (a INT) ENGINE=CONNECT TABLE_TYPE=fix FILE_NAME='t1.fix';
+INSERT INTO t1 VALUES (10);
+CREATE SQL SECURITY INVOKER VIEW v1_invoker AS SELECT * FROM t1;
+CREATE SQL SECURITY DEFINER VIEW v1_definer AS SELECT * FROM t1;
+REPLACE INTO t1 VALUES (11);
+ERROR 42000: CONNECT Unsupported command
+REPLACE INTO v1_invoker VALUES (12);
+ERROR 42000: CONNECT Unsupported command
+REPLACE INTO v1_definer VALUES (13);
+ERROR 42000: CONNECT Unsupported command
+REPLACE INTO t1 VALUES (21);
+ERROR 28000: Access denied for user 'user'@'localhost' (using password: NO)
+REPLACE INTO v1_invoker VALUES (22);
+ERROR 28000: Access denied for user 'user'@'localhost' (using password: NO)
+REPLACE INTO v1_definer VALUES (23);
+ERROR 42000: CONNECT Unsupported command
+DROP VIEW v1_invoker, v1_definer;
+DROP TABLE t1;
+# Testing SQLCOM_DELETE
+CREATE TABLE t1 (a INT) ENGINE=CONNECT TABLE_TYPE=fix FILE_NAME='t1.fix';
+INSERT INTO t1 VALUES (10),(11),(12),(13),(21),(22),(23);
+CREATE SQL SECURITY INVOKER VIEW v1_invoker AS SELECT * FROM t1;
+CREATE SQL SECURITY DEFINER VIEW v1_definer AS SELECT * FROM t1;
+DELETE FROM t1 WHERE a=11;
+DELETE FROM v1_invoker WHERE a=12;
+DELETE FROM v1_definer WHERE a=13;
+DELETE FROM t1 WHERE a=21;
+ERROR 28000: Access denied for user 'user'@'localhost' (using password: NO)
+DELETE FROM v1_invoker WHERE a=22;
+ERROR 28000: Access denied for user 'user'@'localhost' (using password: NO)
+DELETE FROM v1_definer WHERE a=23;
+DROP VIEW v1_invoker, v1_definer;
+DROP TABLE t1;
+# Testing SQLCOM_LOAD
+CREATE TABLE t1 (a VARCHAR(128)) ENGINE=CONNECT TABLE_TYPE=fix FILE_NAME='t1.fix';
+INSERT INTO t1 VALUES (10);
+CREATE SQL SECURITY INVOKER VIEW v1_invoker AS SELECT * FROM t1;
+CREATE SQL SECURITY DEFINER VIEW v1_definer AS SELECT * FROM t1;
+LOAD DATA LOCAL INFILE 'MTR_SUITE_DIR/std_data/boys.txt' INTO TABLE t1;
+LOAD DATA LOCAL INFILE 'MTR_SUITE_DIR/std_data/boys.txt' INTO TABLE v1_invoker;
+LOAD DATA LOCAL INFILE 'MTR_SUITE_DIR/std_data/boys.txt' INTO TABLE v1_definer;
+LOAD DATA LOCAL INFILE 'MTR_SUITE_DIR/std_data/boys.txt' INTO TABLE t1;
+ERROR 28000: Access denied for user 'user'@'localhost' (using password: NO)
+LOAD DATA LOCAL INFILE 'MTR_SUITE_DIR/std_data/boys.txt' INTO TABLE v1_invoker;
+ERROR 28000: Access denied for user 'user'@'localhost' (using password: NO)
+LOAD DATA LOCAL INFILE 'MTR_SUITE_DIR/std_data/boys.txt' INTO TABLE v1_definer;
+DROP VIEW v1_invoker, v1_definer;
+DROP TABLE t1;
+# Testing SQLCOM_TRUNCATE
+CREATE TABLE t1 (a INT) ENGINE=CONNECT TABLE_TYPE=fix FILE_NAME='t1.fix';
+INSERT INTO t1 VALUES (10);
+TRUNCATE TABLE t1;
+INSERT INTO t1 VALUES (11);
+TRUNCATE TABLE t1;
+ERROR 28000: Access denied for user 'user'@'localhost' (using password: NO)
+DROP TABLE t1;
+# Testing SQLCOM_DROP_TABLE
+CREATE TABLE t1 (a INT) ENGINE=CONNECT TABLE_TYPE=fix FILE_NAME='t1.fix';
+INSERT INTO t1 VALUES (10);
+DROP TABLE t1;
+ERROR 28000: Access denied for user 'user'@'localhost' (using password: NO)
+DROP TABLE t1;
+# Testing SQLCOM_DROP_VIEW
+# DROP VIEW does not need FILE_ACL.
+CREATE TABLE t1 (a INT) ENGINE=CONNECT TABLE_TYPE=fix FILE_NAME='t1.fix';
+INSERT INTO t1 VALUES (10),(11),(12),(13),(21),(22),(23);
+CREATE SQL SECURITY INVOKER VIEW v1_invoker AS SELECT * FROM t1;
+CREATE SQL SECURITY DEFINER VIEW v1_definer AS SELECT * FROM t1;
+DROP VIEW v1_invoker, v1_definer;
+CREATE SQL SECURITY INVOKER VIEW v1_invoker AS SELECT * FROM t1;
+CREATE SQL SECURITY DEFINER VIEW v1_definer AS SELECT * FROM t1;
+DROP VIEW v1_invoker;
+DROP VIEW v1_definer;
+DROP TABLE t1;
+# Testing SQLCOM_CREATE_TABLE
+CREATE TABLE t1 (a INT) ENGINE=CONNECT TABLE_TYPE=fix FILE_NAME='t1.fix';
+ERROR 28000: Access denied for user 'user'@'localhost' (using password: NO)
+# Testing SQLCOM_LOCK_TABLES
+CREATE TABLE t1 (a INT) ENGINE=CONNECT TABLE_TYPE=fix FILE_NAME='t1.fix';
+INSERT INTO t1 VALUES (10);
+CREATE SQL SECURITY INVOKER VIEW v1_invoker AS SELECT * FROM t1;
+CREATE SQL SECURITY DEFINER VIEW v1_definer AS SELECT * FROM t1;
+LOCK TABLE t1 READ;
+UNLOCK TABLES;
+LOCK TABLE t1 WRITE;
+UNLOCK TABLES;
+LOCK TABLE v1_invoker READ;
+UNLOCK TABLES;
+LOCK TABLE v1_invoker WRITE;
+UNLOCK TABLES;
+LOCK TABLE v1_definer READ;
+UNLOCK TABLES;
+LOCK TABLE v1_definer WRITE;
+UNLOCK TABLES;
+LOCK TABLE t1 READ;
+ERROR 28000: Access denied for user 'user'@'localhost' (using password: NO)
+LOCK TABLE t1 WRITE;
+ERROR 28000: Access denied for user 'user'@'localhost' (using password: NO)
+LOCK TABLE v1_invoker READ;
+ERROR 28000: Access denied for user 'user'@'localhost' (using password: NO)
+LOCK TABLE v1_invoker WRITE;
+ERROR 28000: Access denied for user 'user'@'localhost' (using password: NO)
+LOCK TABLE v1_definer READ;
+UNLOCK TABLES;
+LOCK TABLE v1_definer WRITE;
+UNLOCK TABLES;
+DROP VIEW v1_invoker, v1_definer;
+DROP TABLE t1;
+# Testing SQLCOM_UPDATE_MULTI
+CREATE TABLE t1 (a INT) ENGINE=CONNECT TABLE_TYPE=fix FILE_NAME='t1.fix';
+CREATE TABLE t2 (a INT) ENGINE=CONNECT TABLE_TYPE=fix FILE_NAME='t2.fix';
+CREATE TABLE t3 (a INT);
+INSERT INTO t1 VALUES (10);
+INSERT INTO t2 VALUES (20);
+INSERT INTO t3 VALUES (30);
+CREATE SQL SECURITY INVOKER VIEW v1_invoker AS SELECT * FROM t1;
+CREATE SQL SECURITY DEFINER VIEW v1_definer AS SELECT * FROM t1;
+CREATE SQL SECURITY INVOKER VIEW v2_invoker AS SELECT * FROM t2;
+CREATE SQL SECURITY DEFINER VIEW v2_definer AS SELECT * FROM t2;
+UPDATE t1 a1,t1 a2 SET a1.a=50 WHERE a1.a=a2.a;
+UPDATE t1 a1,t2 a2 SET a1.a=50 WHERE a1.a=a2.a;
+UPDATE t1 a1,t3 a2 SET a1.a=50 WHERE a1.a=a2.a;
+UPDATE t1 a1,v1_invoker a2 SET a1.a=50 WHERE a1.a=a2.a;
+UPDATE t1 a1,v1_definer a2 SET a1.a=50 WHERE a1.a=a2.a;
+UPDATE t1 a1,v2_invoker a2 SET a1.a=50 WHERE a1.a=a2.a;
+UPDATE t1 a1,v2_definer a2 SET a1.a=50 WHERE a1.a=a2.a;
+UPDATE t2 a1,t1 a2 SET a1.a=50 WHERE a1.a=a2.a;
+UPDATE t2 a1,t2 a2 SET a1.a=50 WHERE a1.a=a2.a;
+UPDATE t2 a1,t3 a2 SET a1.a=50 WHERE a1.a=a2.a;
+UPDATE t2 a1,v1_invoker a2 SET a1.a=50 WHERE a1.a=a2.a;
+UPDATE t2 a1,v1_definer a2 SET a1.a=50 WHERE a1.a=a2.a;
+UPDATE t2 a1,v2_invoker a2 SET a1.a=50 WHERE a1.a=a2.a;
+UPDATE t2 a1,v2_definer a2 SET a1.a=50 WHERE a1.a=a2.a;
+UPDATE t3 a1,t1 a2 SET a1.a=50 WHERE a1.a=a2.a;
+UPDATE t3 a1,t2 a2 SET a1.a=50 WHERE a1.a=a2.a;
+UPDATE t3 a1,t3 a2 SET a1.a=50 WHERE a1.a=a2.a;
+UPDATE t3 a1,v1_invoker a2 SET a1.a=50 WHERE a1.a=a2.a;
+UPDATE t3 a1,v1_definer a2 SET a1.a=50 WHERE a1.a=a2.a;
+UPDATE t3 a1,v2_invoker a2 SET a1.a=50 WHERE a1.a=a2.a;
+UPDATE t3 a1,v2_definer a2 SET a1.a=50 WHERE a1.a=a2.a;
+UPDATE v1_invoker a1,t1 a2 SET a1.a=50 WHERE a1.a=a2.a;
+UPDATE v1_invoker a1,t2 a2 SET a1.a=50 WHERE a1.a=a2.a;
+UPDATE v1_invoker a1,t3 a2 SET a1.a=50 WHERE a1.a=a2.a;
+UPDATE v1_invoker a1,v1_invoker a2 SET a1.a=50 WHERE a1.a=a2.a;
+UPDATE v1_invoker a1,v1_definer a2 SET a1.a=50 WHERE a1.a=a2.a;
+UPDATE v1_invoker a1,v2_invoker a2 SET a1.a=50 WHERE a1.a=a2.a;
+UPDATE v1_invoker a1,v2_definer a2 SET a1.a=50 WHERE a1.a=a2.a;
+UPDATE v1_definer a1,t1 a2 SET a1.a=50 WHERE a1.a=a2.a;
+UPDATE v1_definer a1,t2 a2 SET a1.a=50 WHERE a1.a=a2.a;
+UPDATE v1_definer a1,t3 a2 SET a1.a=50 WHERE a1.a=a2.a;
+UPDATE v1_definer a1,v1_invoker a2 SET a1.a=50 WHERE a1.a=a2.a;
+UPDATE v1_definer a1,v1_definer a2 SET a1.a=50 WHERE a1.a=a2.a;
+UPDATE v1_definer a1,v2_invoker a2 SET a1.a=50 WHERE a1.a=a2.a;
+UPDATE v1_definer a1,v2_definer a2 SET a1.a=50 WHERE a1.a=a2.a;
+UPDATE v2_invoker a1,t1 a2 SET a1.a=50 WHERE a1.a=a2.a;
+UPDATE v2_invoker a1,t2 a2 SET a1.a=50 WHERE a1.a=a2.a;
+UPDATE v2_invoker a1,t3 a2 SET a1.a=50 WHERE a1.a=a2.a;
+UPDATE v2_invoker a1,v1_invoker a2 SET a1.a=50 WHERE a1.a=a2.a;
+UPDATE v2_invoker a1,v1_definer a2 SET a1.a=50 WHERE a1.a=a2.a;
+UPDATE v2_invoker a1,v2_invoker a2 SET a1.a=50 WHERE a1.a=a2.a;
+UPDATE v2_invoker a1,v2_definer a2 SET a1.a=50 WHERE a1.a=a2.a;
+UPDATE v2_definer a1,t1 a2 SET a1.a=50 WHERE a1.a=a2.a;
+UPDATE v2_definer a1,t2 a2 SET a1.a=50 WHERE a1.a=a2.a;
+UPDATE v2_definer a1,t3 a2 SET a1.a=50 WHERE a1.a=a2.a;
+UPDATE v2_definer a1,v1_invoker a2 SET a1.a=50 WHERE a1.a=a2.a;
+UPDATE v2_definer a1,v1_definer a2 SET a1.a=50 WHERE a1.a=a2.a;
+UPDATE v2_definer a1,v2_invoker a2 SET a1.a=50 WHERE a1.a=a2.a;
+UPDATE v2_definer a1,v2_definer a2 SET a1.a=50 WHERE a1.a=a2.a;
+UPDATE t1 a1,t1 a2 SET a1.a=50 WHERE a1.a=a2.a;
+ERROR 28000: Access denied for user 'user'@'localhost' (using password: NO)
+UPDATE t1 a1,t2 a2 SET a1.a=50 WHERE a1.a=a2.a;
+ERROR 28000: Access denied for user 'user'@'localhost' (using password: NO)
+UPDATE t1 a1,t3 a2 SET a1.a=50 WHERE a1.a=a2.a;
+ERROR 28000: Access denied for user 'user'@'localhost' (using password: NO)
+UPDATE t1 a1,v1_invoker a2 SET a1.a=50 WHERE a1.a=a2.a;
+ERROR 28000: Access denied for user 'user'@'localhost' (using password: NO)
+UPDATE t1 a1,v1_definer a2 SET a1.a=50 WHERE a1.a=a2.a;
+ERROR 28000: Access denied for user 'user'@'localhost' (using password: NO)
+UPDATE t1 a1,v2_invoker a2 SET a1.a=50 WHERE a1.a=a2.a;
+ERROR 28000: Access denied for user 'user'@'localhost' (using password: NO)
+UPDATE t1 a1,v2_definer a2 SET a1.a=50 WHERE a1.a=a2.a;
+ERROR 28000: Access denied for user 'user'@'localhost' (using password: NO)
+UPDATE t2 a1,t1 a2 SET a1.a=50 WHERE a1.a=a2.a;
+ERROR 28000: Access denied for user 'user'@'localhost' (using password: NO)
+UPDATE t2 a1,t2 a2 SET a1.a=50 WHERE a1.a=a2.a;
+ERROR 28000: Access denied for user 'user'@'localhost' (using password: NO)
+UPDATE t2 a1,t3 a2 SET a1.a=50 WHERE a1.a=a2.a;
+ERROR 28000: Access denied for user 'user'@'localhost' (using password: NO)
+UPDATE t2 a1,v1_invoker a2 SET a1.a=50 WHERE a1.a=a2.a;
+ERROR 28000: Access denied for user 'user'@'localhost' (using password: NO)
+UPDATE t2 a1,v1_definer a2 SET a1.a=50 WHERE a1.a=a2.a;
+ERROR 28000: Access denied for user 'user'@'localhost' (using password: NO)
+UPDATE t2 a1,v2_invoker a2 SET a1.a=50 WHERE a1.a=a2.a;
+ERROR 28000: Access denied for user 'user'@'localhost' (using password: NO)
+UPDATE t2 a1,v2_definer a2 SET a1.a=50 WHERE a1.a=a2.a;
+ERROR 28000: Access denied for user 'user'@'localhost' (using password: NO)
+UPDATE t3 a1,t1 a2 SET a1.a=50 WHERE a1.a=a2.a;
+ERROR 28000: Access denied for user 'user'@'localhost' (using password: NO)
+UPDATE t3 a1,t2 a2 SET a1.a=50 WHERE a1.a=a2.a;
+ERROR 28000: Access denied for user 'user'@'localhost' (using password: NO)
+UPDATE t3 a1,t3 a2 SET a1.a=50 WHERE a1.a=a2.a;
+UPDATE t3 a1,v1_invoker a2 SET a1.a=50 WHERE a1.a=a2.a;
+ERROR 28000: Access denied for user 'user'@'localhost' (using password: NO)
+UPDATE t3 a1,v1_definer a2 SET a1.a=50 WHERE a1.a=a2.a;
+UPDATE t3 a1,v2_invoker a2 SET a1.a=50 WHERE a1.a=a2.a;
+ERROR 28000: Access denied for user 'user'@'localhost' (using password: NO)
+UPDATE t3 a1,v2_definer a2 SET a1.a=50 WHERE a1.a=a2.a;
+UPDATE v1_invoker a1,t1 a2 SET a1.a=50 WHERE a1.a=a2.a;
+ERROR 28000: Access denied for user 'user'@'localhost' (using password: NO)
+UPDATE v1_invoker a1,t2 a2 SET a1.a=50 WHERE a1.a=a2.a;
+ERROR 28000: Access denied for user 'user'@'localhost' (using password: NO)
+UPDATE v1_invoker a1,t3 a2 SET a1.a=50 WHERE a1.a=a2.a;
+ERROR 28000: Access denied for user 'user'@'localhost' (using password: NO)
+UPDATE v1_invoker a1,v1_invoker a2 SET a1.a=50 WHERE a1.a=a2.a;
+ERROR 28000: Access denied for user 'user'@'localhost' (using password: NO)
+UPDATE v1_invoker a1,v1_definer a2 SET a1.a=50 WHERE a1.a=a2.a;
+ERROR 28000: Access denied for user 'user'@'localhost' (using password: NO)
+UPDATE v1_invoker a1,v2_invoker a2 SET a1.a=50 WHERE a1.a=a2.a;
+ERROR 28000: Access denied for user 'user'@'localhost' (using password: NO)
+UPDATE v1_invoker a1,v2_definer a2 SET a1.a=50 WHERE a1.a=a2.a;
+ERROR 28000: Access denied for user 'user'@'localhost' (using password: NO)
+UPDATE v1_definer a1,t1 a2 SET a1.a=50 WHERE a1.a=a2.a;
+ERROR 28000: Access denied for user 'user'@'localhost' (using password: NO)
+UPDATE v1_definer a1,t2 a2 SET a1.a=50 WHERE a1.a=a2.a;
+ERROR 28000: Access denied for user 'user'@'localhost' (using password: NO)
+UPDATE v1_definer a1,t3 a2 SET a1.a=50 WHERE a1.a=a2.a;
+UPDATE v1_definer a1,v1_invoker a2 SET a1.a=50 WHERE a1.a=a2.a;
+ERROR 28000: Access denied for user 'user'@'localhost' (using password: NO)
+UPDATE v1_definer a1,v1_definer a2 SET a1.a=50 WHERE a1.a=a2.a;
+UPDATE v1_definer a1,v2_invoker a2 SET a1.a=50 WHERE a1.a=a2.a;
+ERROR 28000: Access denied for user 'user'@'localhost' (using password: NO)
+UPDATE v1_definer a1,v2_definer a2 SET a1.a=50 WHERE a1.a=a2.a;
+UPDATE v2_invoker a1,t1 a2 SET a1.a=50 WHERE a1.a=a2.a;
+ERROR 28000: Access denied for user 'user'@'localhost' (using password: NO)
+UPDATE v2_invoker a1,t2 a2 SET a1.a=50 WHERE a1.a=a2.a;
+ERROR 28000: Access denied for user 'user'@'localhost' (using password: NO)
+UPDATE v2_invoker a1,t3 a2 SET a1.a=50 WHERE a1.a=a2.a;
+ERROR 28000: Access denied for user 'user'@'localhost' (using password: NO)
+UPDATE v2_invoker a1,v1_invoker a2 SET a1.a=50 WHERE a1.a=a2.a;
+ERROR 28000: Access denied for user 'user'@'localhost' (using password: NO)
+UPDATE v2_invoker a1,v1_definer a2 SET a1.a=50 WHERE a1.a=a2.a;
+ERROR 28000: Access denied for user 'user'@'localhost' (using password: NO)
+UPDATE v2_invoker a1,v2_invoker a2 SET a1.a=50 WHERE a1.a=a2.a;
+ERROR 28000: Access denied for user 'user'@'localhost' (using password: NO)
+UPDATE v2_invoker a1,v2_definer a2 SET a1.a=50 WHERE a1.a=a2.a;
+ERROR 28000: Access denied for user 'user'@'localhost' (using password: NO)
+UPDATE v2_definer a1,t1 a2 SET a1.a=50 WHERE a1.a=a2.a;
+ERROR 28000: Access denied for user 'user'@'localhost' (using password: NO)
+UPDATE v2_definer a1,t2 a2 SET a1.a=50 WHERE a1.a=a2.a;
+ERROR 28000: Access denied for user 'user'@'localhost' (using password: NO)
+UPDATE v2_definer a1,t3 a2 SET a1.a=50 WHERE a1.a=a2.a;
+UPDATE v2_definer a1,v1_invoker a2 SET a1.a=50 WHERE a1.a=a2.a;
+ERROR 28000: Access denied for user 'user'@'localhost' (using password: NO)
+UPDATE v2_definer a1,v1_definer a2 SET a1.a=50 WHERE a1.a=a2.a;
+UPDATE v2_definer a1,v2_invoker a2 SET a1.a=50 WHERE a1.a=a2.a;
+ERROR 28000: Access denied for user 'user'@'localhost' (using password: NO)
+UPDATE v2_definer a1,v2_definer a2 SET a1.a=50 WHERE a1.a=a2.a;
+DROP VIEW v1_invoker, v1_definer, v2_invoker, v2_definer;
+DROP TABLE t1, t2, t3;
+# Testing SQLCOM_DELETE_MULTI
+CREATE TABLE t1 (a INT) ENGINE=CONNECT TABLE_TYPE=fix FILE_NAME='t1.fix';
+CREATE TABLE t2 (a INT) ENGINE=CONNECT TABLE_TYPE=fix FILE_NAME='t2.fix';
+CREATE TABLE t3 (a INT);
+INSERT INTO t1 VALUES (10);
+INSERT INTO t2 VALUES (20);
+INSERT INTO t3 VALUES (30);
+CREATE SQL SECURITY INVOKER VIEW v1_invoker AS SELECT * FROM t1;
+CREATE SQL SECURITY DEFINER VIEW v1_definer AS SELECT * FROM t1;
+CREATE SQL SECURITY INVOKER VIEW v2_invoker AS SELECT * FROM t2;
+CREATE SQL SECURITY DEFINER VIEW v2_definer AS SELECT * FROM t2;
+DELETE a1 FROM t1 a1,t1 a2 WHERE a1.a=a2.a;
+DELETE a1 FROM t1 a1,t2 a2 WHERE a1.a=a2.a;
+DELETE a1 FROM t1 a1,t3 a2 WHERE a1.a=a2.a;
+DELETE a1 FROM t1 a1,v1_invoker a2 WHERE a1.a=a2.a;
+DELETE a1 FROM t1 a1,v1_definer a2 WHERE a1.a=a2.a;
+DELETE a1 FROM t1 a1,v2_invoker a2 WHERE a1.a=a2.a;
+DELETE a1 FROM t1 a1,v2_definer a2 WHERE a1.a=a2.a;
+DELETE a1 FROM t2 a1,t1 a2 WHERE a1.a=a2.a;
+DELETE a1 FROM t2 a1,t2 a2 WHERE a1.a=a2.a;
+DELETE a1 FROM t2 a1,t3 a2 WHERE a1.a=a2.a;
+DELETE a1 FROM t2 a1,v1_invoker a2 WHERE a1.a=a2.a;
+DELETE a1 FROM t2 a1,v1_definer a2 WHERE a1.a=a2.a;
+DELETE a1 FROM t2 a1,v2_invoker a2 WHERE a1.a=a2.a;
+DELETE a1 FROM t2 a1,v2_definer a2 WHERE a1.a=a2.a;
+DELETE a1 FROM t3 a1,t1 a2 WHERE a1.a=a2.a;
+DELETE a1 FROM t3 a1,t2 a2 WHERE a1.a=a2.a;
+DELETE a1 FROM t3 a1,t3 a2 WHERE a1.a=a2.a;
+DELETE a1 FROM t3 a1,v1_invoker a2 WHERE a1.a=a2.a;
+DELETE a1 FROM t3 a1,v1_definer a2 WHERE a1.a=a2.a;
+DELETE a1 FROM t3 a1,v2_invoker a2 WHERE a1.a=a2.a;
+DELETE a1 FROM t3 a1,v2_definer a2 WHERE a1.a=a2.a;
+DELETE a1 FROM v1_invoker a1,t1 a2 WHERE a1.a=a2.a;
+DELETE a1 FROM v1_invoker a1,t2 a2 WHERE a1.a=a2.a;
+DELETE a1 FROM v1_invoker a1,t3 a2 WHERE a1.a=a2.a;
+DELETE a1 FROM v1_invoker a1,v1_invoker a2 WHERE a1.a=a2.a;
+DELETE a1 FROM v1_invoker a1,v1_definer a2 WHERE a1.a=a2.a;
+DELETE a1 FROM v1_invoker a1,v2_invoker a2 WHERE a1.a=a2.a;
+DELETE a1 FROM v1_invoker a1,v2_definer a2 WHERE a1.a=a2.a;
+DELETE a1 FROM v1_definer a1,t1 a2 WHERE a1.a=a2.a;
+DELETE a1 FROM v1_definer a1,t2 a2 WHERE a1.a=a2.a;
+DELETE a1 FROM v1_definer a1,t3 a2 WHERE a1.a=a2.a;
+DELETE a1 FROM v1_definer a1,v1_invoker a2 WHERE a1.a=a2.a;
+DELETE a1 FROM v1_definer a1,v1_definer a2 WHERE a1.a=a2.a;
+DELETE a1 FROM v1_definer a1,v2_invoker a2 WHERE a1.a=a2.a;
+DELETE a1 FROM v1_definer a1,v2_definer a2 WHERE a1.a=a2.a;
+DELETE a1 FROM v2_invoker a1,t1 a2 WHERE a1.a=a2.a;
+DELETE a1 FROM v2_invoker a1,t2 a2 WHERE a1.a=a2.a;
+DELETE a1 FROM v2_invoker a1,t3 a2 WHERE a1.a=a2.a;
+DELETE a1 FROM v2_invoker a1,v1_invoker a2 WHERE a1.a=a2.a;
+DELETE a1 FROM v2_invoker a1,v1_definer a2 WHERE a1.a=a2.a;
+DELETE a1 FROM v2_invoker a1,v2_invoker a2 WHERE a1.a=a2.a;
+DELETE a1 FROM v2_invoker a1,v2_definer a2 WHERE a1.a=a2.a;
+DELETE a1 FROM v2_definer a1,t1 a2 WHERE a1.a=a2.a;
+DELETE a1 FROM v2_definer a1,t2 a2 WHERE a1.a=a2.a;
+DELETE a1 FROM v2_definer a1,t3 a2 WHERE a1.a=a2.a;
+DELETE a1 FROM v2_definer a1,v1_invoker a2 WHERE a1.a=a2.a;
+DELETE a1 FROM v2_definer a1,v1_definer a2 WHERE a1.a=a2.a;
+DELETE a1 FROM v2_definer a1,v2_invoker a2 WHERE a1.a=a2.a;
+DELETE a1 FROM v2_definer a1,v2_definer a2 WHERE a1.a=a2.a;
+DELETE a1 FROM t1 a1,t1 a2 WHERE a1.a=a2.a;
+ERROR 28000: Access denied for user 'user'@'localhost' (using password: NO)
+DELETE a1 FROM t1 a1,t2 a2 WHERE a1.a=a2.a;
+ERROR 28000: Access denied for user 'user'@'localhost' (using password: NO)
+DELETE a1 FROM t1 a1,t3 a2 WHERE a1.a=a2.a;
+ERROR 28000: Access denied for user 'user'@'localhost' (using password: NO)
+DELETE a1 FROM t1 a1,v1_invoker a2 WHERE a1.a=a2.a;
+ERROR 28000: Access denied for user 'user'@'localhost' (using password: NO)
+DELETE a1 FROM t1 a1,v1_definer a2 WHERE a1.a=a2.a;
+ERROR 28000: Access denied for user 'user'@'localhost' (using password: NO)
+DELETE a1 FROM t1 a1,v2_invoker a2 WHERE a1.a=a2.a;
+ERROR 28000: Access denied for user 'user'@'localhost' (using password: NO)
+DELETE a1 FROM t1 a1,v2_definer a2 WHERE a1.a=a2.a;
+ERROR 28000: Access denied for user 'user'@'localhost' (using password: NO)
+DELETE a1 FROM t2 a1,t1 a2 WHERE a1.a=a2.a;
+ERROR 28000: Access denied for user 'user'@'localhost' (using password: NO)
+DELETE a1 FROM t2 a1,t2 a2 WHERE a1.a=a2.a;
+ERROR 28000: Access denied for user 'user'@'localhost' (using password: NO)
+DELETE a1 FROM t2 a1,t3 a2 WHERE a1.a=a2.a;
+ERROR 28000: Access denied for user 'user'@'localhost' (using password: NO)
+DELETE a1 FROM t2 a1,v1_invoker a2 WHERE a1.a=a2.a;
+ERROR 28000: Access denied for user 'user'@'localhost' (using password: NO)
+DELETE a1 FROM t2 a1,v1_definer a2 WHERE a1.a=a2.a;
+ERROR 28000: Access denied for user 'user'@'localhost' (using password: NO)
+DELETE a1 FROM t2 a1,v2_invoker a2 WHERE a1.a=a2.a;
+ERROR 28000: Access denied for user 'user'@'localhost' (using password: NO)
+DELETE a1 FROM t2 a1,v2_definer a2 WHERE a1.a=a2.a;
+ERROR 28000: Access denied for user 'user'@'localhost' (using password: NO)
+DELETE a1 FROM t3 a1,t1 a2 WHERE a1.a=a2.a;
+ERROR 28000: Access denied for user 'user'@'localhost' (using password: NO)
+DELETE a1 FROM t3 a1,t2 a2 WHERE a1.a=a2.a;
+ERROR 28000: Access denied for user 'user'@'localhost' (using password: NO)
+DELETE a1 FROM t3 a1,t3 a2 WHERE a1.a=a2.a;
+DELETE a1 FROM t3 a1,v1_invoker a2 WHERE a1.a=a2.a;
+ERROR 28000: Access denied for user 'user'@'localhost' (using password: NO)
+DELETE a1 FROM t3 a1,v1_definer a2 WHERE a1.a=a2.a;
+DELETE a1 FROM t3 a1,v2_invoker a2 WHERE a1.a=a2.a;
+ERROR 28000: Access denied for user 'user'@'localhost' (using password: NO)
+DELETE a1 FROM t3 a1,v2_definer a2 WHERE a1.a=a2.a;
+DELETE a1 FROM v1_invoker a1,t1 a2 WHERE a1.a=a2.a;
+ERROR 28000: Access denied for user 'user'@'localhost' (using password: NO)
+DELETE a1 FROM v1_invoker a1,t2 a2 WHERE a1.a=a2.a;
+ERROR 28000: Access denied for user 'user'@'localhost' (using password: NO)
+DELETE a1 FROM v1_invoker a1,t3 a2 WHERE a1.a=a2.a;
+ERROR 28000: Access denied for user 'user'@'localhost' (using password: NO)
+DELETE a1 FROM v1_invoker a1,v1_invoker a2 WHERE a1.a=a2.a;
+ERROR 28000: Access denied for user 'user'@'localhost' (using password: NO)
+DELETE a1 FROM v1_invoker a1,v1_definer a2 WHERE a1.a=a2.a;
+ERROR 28000: Access denied for user 'user'@'localhost' (using password: NO)
+DELETE a1 FROM v1_invoker a1,v2_invoker a2 WHERE a1.a=a2.a;
+ERROR 28000: Access denied for user 'user'@'localhost' (using password: NO)
+DELETE a1 FROM v1_invoker a1,v2_definer a2 WHERE a1.a=a2.a;
+ERROR 28000: Access denied for user 'user'@'localhost' (using password: NO)
+DELETE a1 FROM v1_definer a1,t1 a2 WHERE a1.a=a2.a;
+ERROR 28000: Access denied for user 'user'@'localhost' (using password: NO)
+DELETE a1 FROM v1_definer a1,t2 a2 WHERE a1.a=a2.a;
+ERROR 28000: Access denied for user 'user'@'localhost' (using password: NO)
+DELETE a1 FROM v1_definer a1,t3 a2 WHERE a1.a=a2.a;
+DELETE a1 FROM v1_definer a1,v1_invoker a2 WHERE a1.a=a2.a;
+ERROR 28000: Access denied for user 'user'@'localhost' (using password: NO)
+DELETE a1 FROM v1_definer a1,v1_definer a2 WHERE a1.a=a2.a;
+DELETE a1 FROM v1_definer a1,v2_invoker a2 WHERE a1.a=a2.a;
+ERROR 28000: Access denied for user 'user'@'localhost' (using password: NO)
+DELETE a1 FROM v1_definer a1,v2_definer a2 WHERE a1.a=a2.a;
+DELETE a1 FROM v2_invoker a1,t1 a2 WHERE a1.a=a2.a;
+ERROR 28000: Access denied for user 'user'@'localhost' (using password: NO)
+DELETE a1 FROM v2_invoker a1,t2 a2 WHERE a1.a=a2.a;
+ERROR 28000: Access denied for user 'user'@'localhost' (using password: NO)
+DELETE a1 FROM v2_invoker a1,t3 a2 WHERE a1.a=a2.a;
+ERROR 28000: Access denied for user 'user'@'localhost' (using password: NO)
+DELETE a1 FROM v2_invoker a1,v1_invoker a2 WHERE a1.a=a2.a;
+ERROR 28000: Access denied for user 'user'@'localhost' (using password: NO)
+DELETE a1 FROM v2_invoker a1,v1_definer a2 WHERE a1.a=a2.a;
+ERROR 28000: Access denied for user 'user'@'localhost' (using password: NO)
+DELETE a1 FROM v2_invoker a1,v2_invoker a2 WHERE a1.a=a2.a;
+ERROR 28000: Access denied for user 'user'@'localhost' (using password: NO)
+DELETE a1 FROM v2_invoker a1,v2_definer a2 WHERE a1.a=a2.a;
+ERROR 28000: Access denied for user 'user'@'localhost' (using password: NO)
+DELETE a1 FROM v2_definer a1,t1 a2 WHERE a1.a=a2.a;
+ERROR 28000: Access denied for user 'user'@'localhost' (using password: NO)
+DELETE a1 FROM v2_definer a1,t2 a2 WHERE a1.a=a2.a;
+ERROR 28000: Access denied for user 'user'@'localhost' (using password: NO)
+DELETE a1 FROM v2_definer a1,t3 a2 WHERE a1.a=a2.a;
+DELETE a1 FROM v2_definer a1,v1_invoker a2 WHERE a1.a=a2.a;
+ERROR 28000: Access denied for user 'user'@'localhost' (using password: NO)
+DELETE a1 FROM v2_definer a1,v1_definer a2 WHERE a1.a=a2.a;
+DELETE a1 FROM v2_definer a1,v2_invoker a2 WHERE a1.a=a2.a;
+ERROR 28000: Access denied for user 'user'@'localhost' (using password: NO)
+DELETE a1 FROM v2_definer a1,v2_definer a2 WHERE a1.a=a2.a;
+DROP VIEW v1_invoker, v1_definer, v2_invoker, v2_definer;
+DROP TABLE t1, t2, t3;
+# Testing SQLCOM_CREATE_VIEW
+CREATE TABLE t1 (a INT) ENGINE=CONNECT TABLE_TYPE=fix FILE_NAME='t1.fix';
+INSERT INTO t1 VALUES (10);
+CREATE SQL SECURITY INVOKER VIEW v1_invoker AS SELECT * FROM t1;
+CREATE SQL SECURITY DEFINER VIEW v1_definer AS SELECT * FROM t1;
+CREATE VIEW v2 AS SELECT * FROM v1_invoker;
+DROP VIEW v2;
+CREATE VIEW v2 AS SELECT * FROM v1_definer;
+DROP VIEW v2;
+CREATE VIEW v2 AS SELECT * FROM t1;
+ERROR 28000: Access denied for user 'user'@'localhost' (using password: NO)
+CREATE VIEW v2 AS SELECT * FROM v1_invoker;
+ERROR 28000: Access denied for user 'user'@'localhost' (using password: NO)
+CREATE VIEW v2 AS SELECT * FROM v1_definer;
+DROP VIEW v2;
+DROP VIEW v1_invoker, v1_definer;
+DROP TABLE t1;
+# Testing SQLCOM_INSERT_SELECT
+CREATE TABLE t1 (a INT) ENGINE=CONNECT TABLE_TYPE=fix FILE_NAME='t1.fix';
+INSERT INTO t1 VALUES (10);
+CREATE SQL SECURITY INVOKER VIEW v1_invoker AS SELECT * FROM t1;
+CREATE SQL SECURITY DEFINER VIEW v1_definer AS SELECT * FROM t1;
+INSERT INTO t1 SELECT * FROM t1 WHERE a=20;
+INSERT INTO t1 SELECT * FROM v1_invoker WHERE a=20;
+INSERT INTO t1 SELECT * FROM v1_definer WHERE a=20;
+INSERT INTO v1_invoker SELECT * FROM t1 WHERE a=20;
+INSERT INTO v1_invoker SELECT * FROM v1_invoker WHERE a=20;
+INSERT INTO v1_invoker SELECT * FROM v1_definer WHERE a=20;
+INSERT INTO v1_definer SELECT * FROM t1 WHERE a=20;
+INSERT INTO v1_definer SELECT * FROM v1_invoker WHERE a=20;
+INSERT INTO v1_definer SELECT * FROM v1_definer WHERE a=20;
+INSERT INTO t1 SELECT * FROM t1 WHERE a=20;
+ERROR 28000: Access denied for user 'user'@'localhost' (using password: NO)
+INSERT INTO t1 SELECT * FROM v1_invoker WHERE a=20;
+ERROR 28000: Access denied for user 'user'@'localhost' (using password: NO)
+INSERT INTO t1 SELECT * FROM v1_definer WHERE a=20;
+ERROR 28000: Access denied for user 'user'@'localhost' (using password: NO)
+INSERT INTO v1_invoker SELECT * FROM t1 WHERE a=20;
+ERROR 28000: Access denied for user 'user'@'localhost' (using password: NO)
+INSERT INTO v1_invoker SELECT * FROM v1_invoker WHERE a=20;
+ERROR 28000: Access denied for user 'user'@'localhost' (using password: NO)
+INSERT INTO v1_invoker SELECT * FROM v1_definer WHERE a=20;
+ERROR 28000: Access denied for user 'user'@'localhost' (using password: NO)
+INSERT INTO v1_definer SELECT * FROM t1 WHERE a=20;
+ERROR 28000: Access denied for user 'user'@'localhost' (using password: NO)
+INSERT INTO v1_definer SELECT * FROM v1_invoker WHERE a=20;
+ERROR 28000: Access denied for user 'user'@'localhost' (using password: NO)
+INSERT INTO v1_definer SELECT * FROM v1_definer WHERE a=20;
+DROP VIEW v1_invoker, v1_definer;
+DROP TABLE t1;
+# Testing SQLCOM_REPLACE_SELECT
+CREATE TABLE t1 (a INT) ENGINE=CONNECT TABLE_TYPE=fix FILE_NAME='t1.fix';
+INSERT INTO t1 VALUES (10);
+CREATE SQL SECURITY INVOKER VIEW v1_invoker AS SELECT * FROM t1;
+CREATE SQL SECURITY DEFINER VIEW v1_definer AS SELECT * FROM t1;
+REPLACE INTO t1 SELECT * FROM t1 WHERE a=20;
+ERROR 42000: CONNECT Unsupported command
+REPLACE INTO t1 SELECT * FROM v1_invoker WHERE a=20;
+ERROR 42000: CONNECT Unsupported command
+REPLACE INTO t1 SELECT * FROM v1_definer WHERE a=20;
+ERROR 42000: CONNECT Unsupported command
+REPLACE INTO v1_invoker SELECT * FROM t1 WHERE a=20;
+ERROR 42000: CONNECT Unsupported command
+REPLACE INTO v1_invoker SELECT * FROM v1_invoker WHERE a=20;
+ERROR 42000: CONNECT Unsupported command
+REPLACE INTO v1_invoker SELECT * FROM v1_definer WHERE a=20;
+ERROR 42000: CONNECT Unsupported command
+REPLACE INTO v1_definer SELECT * FROM t1 WHERE a=20;
+ERROR 42000: CONNECT Unsupported command
+REPLACE INTO v1_definer SELECT * FROM v1_invoker WHERE a=20;
+ERROR 42000: CONNECT Unsupported command
+REPLACE INTO v1_definer SELECT * FROM v1_definer WHERE a=20;
+ERROR 42000: CONNECT Unsupported command
+REPLACE INTO t1 SELECT * FROM t1 WHERE a=20;
+ERROR 28000: Access denied for user 'user'@'localhost' (using password: NO)
+REPLACE INTO t1 SELECT * FROM v1_invoker WHERE a=20;
+ERROR 28000: Access denied for user 'user'@'localhost' (using password: NO)
+REPLACE INTO t1 SELECT * FROM v1_definer WHERE a=20;
+ERROR 28000: Access denied for user 'user'@'localhost' (using password: NO)
+REPLACE INTO v1_invoker SELECT * FROM t1 WHERE a=20;
+ERROR 28000: Access denied for user 'user'@'localhost' (using password: NO)
+REPLACE INTO v1_invoker SELECT * FROM v1_invoker WHERE a=20;
+ERROR 28000: Access denied for user 'user'@'localhost' (using password: NO)
+REPLACE INTO v1_invoker SELECT * FROM v1_definer WHERE a=20;
+ERROR 28000: Access denied for user 'user'@'localhost' (using password: NO)
+REPLACE INTO v1_definer SELECT * FROM t1 WHERE a=20;
+ERROR 42000: CONNECT Unsupported command
+REPLACE INTO v1_definer SELECT * FROM v1_invoker WHERE a=20;
+ERROR 42000: CONNECT Unsupported command
+REPLACE INTO v1_definer SELECT * FROM v1_definer WHERE a=20;
+ERROR 42000: CONNECT Unsupported command
+DROP VIEW v1_invoker, v1_definer;
+DROP TABLE t1;
+# Testing SQLCOM_RENAME_TABLE
+CREATE TABLE t1 (a INT) ENGINE=CONNECT TABLE_TYPE=fix FILE_NAME='t1.fix';
+INSERT INTO t1 VALUES (10);
+RENAME TABLE t1 TO t2;
+SHOW CREATE TABLE t2;
+Table Create Table
+t2 CREATE TABLE `t2` (
+ `a` int(11) DEFAULT NULL
+) ENGINE=CONNECT DEFAULT CHARSET=latin1 `TABLE_TYPE`=fix `FILE_NAME`='t1.fix'
+RENAME TABLE t2 TO t1;
+RENAME TABLE t1 TO t2;
+ERROR 28000: Access denied for user 'user'@'localhost' (using password: NO)
+DROP TABLE t1;
+# Testing SQLCOM_ALTER_TABLE (for ALTER..RENAME)
+CREATE TABLE t1 (a INT) ENGINE=CONNECT TABLE_TYPE=fix FILE_NAME='t1.fix';
+INSERT INTO t1 VALUES (10);
+ALTER TABLE t1 RENAME TO t2;
+SHOW CREATE TABLE t2;
+Table Create Table
+t2 CREATE TABLE `t2` (
+ `a` int(11) DEFAULT NULL
+) ENGINE=CONNECT DEFAULT CHARSET=latin1 `TABLE_TYPE`=fix `FILE_NAME`='t1.fix'
+ALTER TABLE t2 RENAME TO t1;
+ALTER TABLE t1 RENAME TO t2;
+ERROR 28000: Access denied for user 'user'@'localhost' (using password: NO)
+DROP TABLE t1;
+# Testing SQLCOM_ALTER_TABLE (changing ENGINE to non-CONNECT)
+CREATE TABLE t1 (a INT) ENGINE=CONNECT TABLE_TYPE=fix FILE_NAME='t1.fix';
+INSERT INTO t1 VALUES (10);
+ALTER TABLE t1 ENGINE=MyISAM;
+DROP TABLE t1;
+CREATE TABLE t1 (a INT) ENGINE=CONNECT TABLE_TYPE=fix FILE_NAME='t1.fix';
+INSERT INTO t1 VALUES (10);
+ALTER TABLE t1 ENGINE=MyISAM;
+ERROR 28000: Access denied for user 'user'@'localhost' (using password: NO)
+DROP TABLE t1;
+# Testing SQLCOM_ALTER_TABLE (changing ENGINE to CONNECT)
+CREATE TABLE t1 (a INT) ENGINE=MyISAM;
+INSERT INTO t1 VALUES (10);
+SELECT * FROM t1;
+a
+10
+ALTER TABLE t1 ENGINE=CONNECT TABLE_TYPE=fix FILE_NAME='t1.fix';
+DROP TABLE t1;
+CREATE TABLE t1 (a INT) ENGINE=MyISAM;
+INSERT INTO t1 VALUES (10);
+ALTER TABLE t1 ENGINE=CONNECT TABLE_TYPE=fix FILE_NAME='t1.fix';
+ERROR 28000: Access denied for user 'user'@'localhost' (using password: NO)
+DROP TABLE t1;
+# Testing SQLCOM_OPTIMIZE
+CREATE TABLE t1 (a INT NOT NULL, KEY(a)) ENGINE=CONNECT TABLE_TYPE=fix FILE_NAME='t1.fix';
+INSERT INTO t1 VALUES (10);
+OPTIMIZE TABLE t1;
+Table Op Msg_type Msg_text
+test.t1 optimize status OK
+OPTIMIZE TABLE t1;
+Table Op Msg_type Msg_text
+test.t1 optimize Error Access denied for user 'user'@'localhost' (using password: NO)
+test.t1 optimize Error Can't lock file (errno: 122 "Internal (unspecified) error in handler")
+test.t1 optimize error Corrupt
+DROP TABLE t1;
+# Testing SQLCOM_ALTER_TABLE (adding columns)
+CREATE TABLE t1 (a INT) ENGINE=CONNECT TABLE_TYPE=fix FILE_NAME='t1.fix';
+INSERT INTO t1 VALUES (10);
+ALTER TABLE t1 ADD b INT;
+Warnings:
+Warning 1105 This is an outward table, table data were not modified.
+ALTER TABLE t1 ADD c INT;
+ERROR 28000: Access denied for user 'user'@'localhost' (using password: NO)
+DROP TABLE t1;
+# Testing SQLCOM_ALTER_TABLE (removing columns)
+CREATE TABLE t1 (a INT,b INT,c INT) ENGINE=CONNECT TABLE_TYPE=fix FILE_NAME='t1.fix';
+INSERT INTO t1 VALUES (10,10,10);
+ALTER TABLE t1 DROP b;
+Warnings:
+Warning 1105 This is an outward table, table data were not modified.
+ALTER TABLE t1 DROP c;
+ERROR 28000: Access denied for user 'user'@'localhost' (using password: NO)
+DROP TABLE t1;
+# Testing SQLCOM_ALTER_TABLE (adding keys)
+CREATE TABLE t1 (a INT NOT NULL,b INT NOT NULL) ENGINE=CONNECT TABLE_TYPE=fix FILE_NAME='t1.fix';
+INSERT INTO t1 VALUES (10,10);
+ALTER TABLE t1 ADD KEY(a);
+ALTER TABLE t1 ADD KEY(b);
+ERROR 28000: Access denied for user 'user'@'localhost' (using password: NO)
+DROP TABLE t1;
+# Testing SQLCOM_ALTER_TABLE (removing keys)
+CREATE TABLE t1 (a INT NOT NULL,b INT NOT NULL, KEY a(a), KEY b(b)) ENGINE=CONNECT TABLE_TYPE=fix FILE_NAME='t1.fix';
+INSERT INTO t1 VALUES (10,10);
+ALTER TABLE t1 DROP KEY a;
+ALTER TABLE t1 DROP KEY b;
+ERROR 28000: Access denied for user 'user'@'localhost' (using password: NO)
+DROP TABLE t1;
+# Testing SQLCOM_CREATE_INDEX and SQLCOM_DROP_INDEX
+CREATE TABLE t1 (a INT NOT NULL,b INT NOT NULL) ENGINE=CONNECT TABLE_TYPE=fix FILE_NAME='t1.fix';
+INSERT INTO t1 VALUES (10,10);
+CREATE INDEX a ON t1 (a);
+DROP INDEX a ON t1;
+CREATE INDEX a ON t1 (a);
+CREATE INDEX b ON t1 (b);
+ERROR 28000: Access denied for user 'user'@'localhost' (using password: NO)
+DROP INDEX a ON t1;
+ERROR 28000: Access denied for user 'user'@'localhost' (using password: NO)
+DROP TABLE t1;
+DROP USER user@localhost;
diff --git a/storage/connect/mysql-test/connect/r/ini_grant.result b/storage/connect/mysql-test/connect/r/ini_grant.result
index c3acf7c..1901142 100644
--- a/storage/connect/mysql-test/connect/r/ini_grant.result
+++ b/storage/connect/mysql-test/connect/r/ini_grant.result
@@ -59,7 +59,7 @@ ERROR 28000: Access denied for user 'user'@'localhost' (using password: NO)
SELECT user();
user()
root@localhost
-CREATE VIEW v1 AS SELECT * FROM t1;
+CREATE SQL SECURITY INVOKER VIEW v1 AS SELECT * FROM t1;
SELECT user();
user()
user@localhost
diff --git a/storage/connect/mysql-test/connect/r/mysql_grant.result b/storage/connect/mysql-test/connect/r/mysql_grant.result
index f8d0ee6..554e6f4 100644
--- a/storage/connect/mysql-test/connect/r/mysql_grant.result
+++ b/storage/connect/mysql-test/connect/r/mysql_grant.result
@@ -40,7 +40,7 @@ ERROR 28000: Access denied for user 'user'@'localhost' (using password: NO)
SELECT user();
user()
root@localhost
-CREATE VIEW v1 AS SELECT * FROM t1;
+CREATE SQL SECURITY INVOKER VIEW v1 AS SELECT * FROM t1;
SELECT user();
user()
user@localhost
diff --git a/storage/connect/mysql-test/connect/r/odbc_sqlite3_grant.result b/storage/connect/mysql-test/connect/r/odbc_sqlite3_grant.result
index 364f340..06b4239 100644
--- a/storage/connect/mysql-test/connect/r/odbc_sqlite3_grant.result
+++ b/storage/connect/mysql-test/connect/r/odbc_sqlite3_grant.result
@@ -49,10 +49,11 @@ ERROR 28000: Access denied for user 'user'@'localhost' (using password: NO)
CREATE VIEW v1 AS SELECT * FROM t1;
ERROR 28000: Access denied for user 'user'@'localhost' (using password: NO)
# Testing a VIEW created with FILE privileges but accessed with no FILE
+# using SQL SECIRITY INVOKER
SELECT user();
user()
root@localhost
-CREATE VIEW v1 AS SELECT * FROM t1;
+CREATE SQL SECURITY INVOKER VIEW v1 AS SELECT * FROM t1;
SELECT user();
user()
user@localhost
@@ -64,6 +65,19 @@ UPDATE v1 SET a=123;
ERROR 28000: Access denied for user 'user'@'localhost' (using password: NO)
DELETE FROM v1;
ERROR 28000: Access denied for user 'user'@'localhost' (using password: NO)
+# Testing a VIEW created with FILE privileges but accessed with no FILE
+# using SQL SECIRITY DEFINER
+DROP VIEW v1;
+SELECT user();
+user()
+root@localhost
+CREATE SQL SECURITY DEFINER VIEW v1 AS SELECT * FROM t1;
+SELECT user();
+user()
+user@localhost
+SELECT * FROM v1 WHERE a='test1';
+a
+test1
SELECT user();
user()
root@localhost
diff --git a/storage/connect/mysql-test/connect/r/xml_grant.result b/storage/connect/mysql-test/connect/r/xml_grant.result
index ea38e57..f6dc725 100644
--- a/storage/connect/mysql-test/connect/r/xml_grant.result
+++ b/storage/connect/mysql-test/connect/r/xml_grant.result
@@ -63,7 +63,7 @@ ERROR 28000: Access denied for user 'user'@'localhost' (using password: NO)
SELECT user();
user()
root@localhost
-CREATE VIEW v1 AS SELECT * FROM t1;
+CREATE SQL SECURITY INVOKER VIEW v1 AS SELECT * FROM t1;
SELECT user();
user()
user@localhost
diff --git a/storage/connect/mysql-test/connect/t/grant.inc b/storage/connect/mysql-test/connect/t/grant.inc
index 7bb214d..8f605a7 100644
--- a/storage/connect/mysql-test/connect/t/grant.inc
+++ b/storage/connect/mysql-test/connect/t/grant.inc
@@ -53,7 +53,7 @@ CREATE VIEW v1 AS SELECT * FROM t1;
--echo # Testing a VIEW created with FILE privileges but accessed with no FILE
--connection default
SELECT user();
-CREATE VIEW v1 AS SELECT * FROM t1;
+CREATE SQL SECURITY INVOKER VIEW v1 AS SELECT * FROM t1;
--connection user
SELECT user();
--error ER_ACCESS_DENIED_ERROR
diff --git a/storage/connect/mysql-test/connect/t/grant.test b/storage/connect/mysql-test/connect/t/grant.test
index 909bb41..31a596c 100644
--- a/storage/connect/mysql-test/connect/t/grant.test
+++ b/storage/connect/mysql-test/connect/t/grant.test
@@ -49,7 +49,7 @@ CREATE VIEW v1 AS SELECT * FROM t1;
--echo # Testing a VIEW created with FILE privileges but accessed with no FILE
--connection default
SELECT user();
-CREATE VIEW v1 AS SELECT * FROM t1;
+CREATE SQL SECURITY INVOKER VIEW v1 AS SELECT * FROM t1;
--connection user
SELECT user();
--error ER_ACCESS_DENIED_ERROR
diff --git a/storage/connect/mysql-test/connect/t/grant2.test b/storage/connect/mysql-test/connect/t/grant2.test
new file mode 100644
index 0000000..dbcdfca
--- /dev/null
+++ b/storage/connect/mysql-test/connect/t/grant2.test
@@ -0,0 +1,871 @@
+-- source include/not_embedded.inc
+
+# Tests that involve SQL SECURITY DEFINER (e.g. in VIEWs)
+# TODO: add test with stored routines eventually.
+
+let $MYSQLD_DATADIR= `select @@datadir`;
+
+--echo #
+--echo # MDEV-7574 Security definer views don't work with CONNECT ODBC tables
+--echo #
+
+GRANT ALL PRIVILEGES ON *.* TO user@localhost;
+REVOKE FILE ON *.* FROM user@localhost;
+--connect(user,localhost,user,,)
+
+
+--echo # Testing SQLCOM_SELECT
+--connection default
+CREATE TABLE t1 (a INT) ENGINE=CONNECT TABLE_TYPE=fix FILE_NAME='t1.fix';
+INSERT INTO t1 VALUES (10);
+CREATE SQL SECURITY INVOKER VIEW v1_invoker AS SELECT * FROM t1;
+CREATE SQL SECURITY DEFINER VIEW v1_definer AS SELECT * FROM t1;
+SELECT * FROM t1;
+SELECT * FROM v1_invoker;
+SELECT * FROM v1_definer;
+--connection user
+--error ER_ACCESS_DENIED_ERROR
+SELECT * FROM t1;
+--error ER_ACCESS_DENIED_ERROR
+SELECT * FROM v1_invoker;
+SELECT * FROM v1_definer;
+--connection default
+DROP VIEW v1_invoker, v1_definer;
+DROP TABLE t1;
+--remove_file $MYSQLD_DATADIR/test/t1.fix
+
+
+--echo # Testing SQLCOM_UPDATE
+--connection default
+CREATE TABLE t1 (a INT) ENGINE=CONNECT TABLE_TYPE=fix FILE_NAME='t1.fix';
+INSERT INTO t1 VALUES (10);
+CREATE SQL SECURITY INVOKER VIEW v1_invoker AS SELECT * FROM t1;
+CREATE SQL SECURITY DEFINER VIEW v1_definer AS SELECT * FROM t1;
+UPDATE t1 SET a=11;
+UPDATE v1_invoker SET a=12;
+UPDATE v1_definer SET a=13;
+--connection user
+--error ER_ACCESS_DENIED_ERROR
+UPDATE t1 SET a=21;
+--error ER_ACCESS_DENIED_ERROR
+UPDATE v1_invoker SET a=22;
+UPDATE v1_definer SET a=23;
+--connection default
+DROP VIEW v1_invoker, v1_definer;
+DROP TABLE t1;
+--remove_file $MYSQLD_DATADIR/test/t1.fix
+
+
+--echo # Testing SQLCOM_INSERT
+--connection default
+CREATE TABLE t1 (a INT) ENGINE=CONNECT TABLE_TYPE=fix FILE_NAME='t1.fix';
+INSERT INTO t1 VALUES (10);
+CREATE SQL SECURITY INVOKER VIEW v1_invoker AS SELECT * FROM t1;
+CREATE SQL SECURITY DEFINER VIEW v1_definer AS SELECT * FROM t1;
+INSERT INTO t1 VALUES (11);
+INSERT INTO v1_invoker VALUES (12);
+INSERT INTO v1_definer VALUES (13);
+--connection user
+--error ER_ACCESS_DENIED_ERROR
+INSERT INTO t1 VALUES (21);
+--error ER_ACCESS_DENIED_ERROR
+INSERT INTO v1_invoker VALUES (22);
+INSERT INTO v1_definer VALUES (23);
+--connection default
+DROP VIEW v1_invoker, v1_definer;
+DROP TABLE t1;
+--remove_file $MYSQLD_DATADIR/test/t1.fix
+
+
+--echo # Testing SQLCOM_REPLACE
+# REPLACE is not supported by ConnectSE, so we're testing the difference
+# between ER_ACCESS_DENIED_ERROR vs ER_NOT_ALLOWED_COMMAND
+--connection default
+CREATE TABLE t1 (a INT) ENGINE=CONNECT TABLE_TYPE=fix FILE_NAME='t1.fix';
+INSERT INTO t1 VALUES (10);
+CREATE SQL SECURITY INVOKER VIEW v1_invoker AS SELECT * FROM t1;
+CREATE SQL SECURITY DEFINER VIEW v1_definer AS SELECT * FROM t1;
+--error ER_NOT_ALLOWED_COMMAND
+REPLACE INTO t1 VALUES (11);
+--error ER_NOT_ALLOWED_COMMAND
+REPLACE INTO v1_invoker VALUES (12);
+--error ER_NOT_ALLOWED_COMMAND
+REPLACE INTO v1_definer VALUES (13);
+--connection user
+--error ER_ACCESS_DENIED_ERROR
+REPLACE INTO t1 VALUES (21);
+--error ER_ACCESS_DENIED_ERROR
+REPLACE INTO v1_invoker VALUES (22);
+--error ER_NOT_ALLOWED_COMMAND
+REPLACE INTO v1_definer VALUES (23);
+--connection default
+DROP VIEW v1_invoker, v1_definer;
+DROP TABLE t1;
+--remove_file $MYSQLD_DATADIR/test/t1.fix
+
+
+--echo # Testing SQLCOM_DELETE
+--connection default
+CREATE TABLE t1 (a INT) ENGINE=CONNECT TABLE_TYPE=fix FILE_NAME='t1.fix';
+INSERT INTO t1 VALUES (10),(11),(12),(13),(21),(22),(23);
+CREATE SQL SECURITY INVOKER VIEW v1_invoker AS SELECT * FROM t1;
+CREATE SQL SECURITY DEFINER VIEW v1_definer AS SELECT * FROM t1;
+DELETE FROM t1 WHERE a=11;
+DELETE FROM v1_invoker WHERE a=12;
+DELETE FROM v1_definer WHERE a=13;
+--connection user
+--error ER_ACCESS_DENIED_ERROR
+DELETE FROM t1 WHERE a=21;
+--error ER_ACCESS_DENIED_ERROR
+DELETE FROM v1_invoker WHERE a=22;
+DELETE FROM v1_definer WHERE a=23;
+--connection default
+DROP VIEW v1_invoker, v1_definer;
+DROP TABLE t1;
+--remove_file $MYSQLD_DATADIR/test/t1.fix
+
+
+
+--echo # Testing SQLCOM_LOAD
+--connection default
+CREATE TABLE t1 (a VARCHAR(128)) ENGINE=CONNECT TABLE_TYPE=fix FILE_NAME='t1.fix';
+INSERT INTO t1 VALUES (10);
+CREATE SQL SECURITY INVOKER VIEW v1_invoker AS SELECT * FROM t1;
+CREATE SQL SECURITY DEFINER VIEW v1_definer AS SELECT * FROM t1;
+--replace_result $MTR_SUITE_DIR MTR_SUITE_DIR
+--eval LOAD DATA LOCAL INFILE '$MTR_SUITE_DIR/std_data/boys.txt' INTO TABLE t1
+--replace_result $MTR_SUITE_DIR MTR_SUITE_DIR
+--eval LOAD DATA LOCAL INFILE '$MTR_SUITE_DIR/std_data/boys.txt' INTO TABLE v1_invoker
+--replace_result $MTR_SUITE_DIR MTR_SUITE_DIR
+--eval LOAD DATA LOCAL INFILE '$MTR_SUITE_DIR/std_data/boys.txt' INTO TABLE v1_definer
+--connection user
+--replace_result $MTR_SUITE_DIR MTR_SUITE_DIR
+--error ER_ACCESS_DENIED_ERROR
+--eval LOAD DATA LOCAL INFILE '$MTR_SUITE_DIR/std_data/boys.txt' INTO TABLE t1
+--replace_result $MTR_SUITE_DIR MTR_SUITE_DIR
+--error ER_ACCESS_DENIED_ERROR
+--eval LOAD DATA LOCAL INFILE '$MTR_SUITE_DIR/std_data/boys.txt' INTO TABLE v1_invoker
+--replace_result $MTR_SUITE_DIR MTR_SUITE_DIR
+--eval LOAD DATA LOCAL INFILE '$MTR_SUITE_DIR/std_data/boys.txt' INTO TABLE v1_definer
+--connection default
+DROP VIEW v1_invoker, v1_definer;
+DROP TABLE t1;
+--remove_file $MYSQLD_DATADIR/test/t1.fix
+
+
+--echo # Testing SQLCOM_TRUNCATE
+--connection default
+CREATE TABLE t1 (a INT) ENGINE=CONNECT TABLE_TYPE=fix FILE_NAME='t1.fix';
+INSERT INTO t1 VALUES (10);
+TRUNCATE TABLE t1;
+INSERT INTO t1 VALUES (11);
+--connection user
+--error ER_ACCESS_DENIED_ERROR
+TRUNCATE TABLE t1;
+--connection default
+DROP TABLE t1;
+--remove_file $MYSQLD_DATADIR/test/t1.fix
+
+
+# TODO: Perhaps FILE_ACL is not needed for DROP TABLE. Discuss with Olivier.
+--echo # Testing SQLCOM_DROP_TABLE
+--connection default
+CREATE TABLE t1 (a INT) ENGINE=CONNECT TABLE_TYPE=fix FILE_NAME='t1.fix';
+INSERT INTO t1 VALUES (10);
+--connection user
+--error ER_ACCESS_DENIED_ERROR
+DROP TABLE t1;
+--connection default
+DROP TABLE t1;
+--remove_file $MYSQLD_DATADIR/test/t1.fix
+
+
+--echo # Testing SQLCOM_DROP_VIEW
+--echo # DROP VIEW does not need FILE_ACL.
+--connection default
+CREATE TABLE t1 (a INT) ENGINE=CONNECT TABLE_TYPE=fix FILE_NAME='t1.fix';
+INSERT INTO t1 VALUES (10),(11),(12),(13),(21),(22),(23);
+CREATE SQL SECURITY INVOKER VIEW v1_invoker AS SELECT * FROM t1;
+CREATE SQL SECURITY DEFINER VIEW v1_definer AS SELECT * FROM t1;
+DROP VIEW v1_invoker, v1_definer;
+CREATE SQL SECURITY INVOKER VIEW v1_invoker AS SELECT * FROM t1;
+CREATE SQL SECURITY DEFINER VIEW v1_definer AS SELECT * FROM t1;
+--connection user
+DROP VIEW v1_invoker;
+DROP VIEW v1_definer;
+--connection default
+DROP TABLE t1;
+--remove_file $MYSQLD_DATADIR/test/t1.fix
+
+
+--echo # Testing SQLCOM_CREATE_TABLE
+--connection user
+--error ER_ACCESS_DENIED_ERROR
+CREATE TABLE t1 (a INT) ENGINE=CONNECT TABLE_TYPE=fix FILE_NAME='t1.fix';
+--connection default
+
+
+--echo # Testing SQLCOM_LOCK_TABLES
+--connection default
+CREATE TABLE t1 (a INT) ENGINE=CONNECT TABLE_TYPE=fix FILE_NAME='t1.fix';
+INSERT INTO t1 VALUES (10);
+CREATE SQL SECURITY INVOKER VIEW v1_invoker AS SELECT * FROM t1;
+CREATE SQL SECURITY DEFINER VIEW v1_definer AS SELECT * FROM t1;
+LOCK TABLE t1 READ;
+UNLOCK TABLES;
+LOCK TABLE t1 WRITE;
+UNLOCK TABLES;
+LOCK TABLE v1_invoker READ;
+UNLOCK TABLES;
+LOCK TABLE v1_invoker WRITE;
+UNLOCK TABLES;
+LOCK TABLE v1_definer READ;
+UNLOCK TABLES;
+LOCK TABLE v1_definer WRITE;
+UNLOCK TABLES;
+--connection user
+--error ER_ACCESS_DENIED_ERROR
+LOCK TABLE t1 READ;
+--error ER_ACCESS_DENIED_ERROR
+LOCK TABLE t1 WRITE;
+--error ER_ACCESS_DENIED_ERROR
+LOCK TABLE v1_invoker READ;
+--error ER_ACCESS_DENIED_ERROR
+LOCK TABLE v1_invoker WRITE;
+LOCK TABLE v1_definer READ;
+UNLOCK TABLES;
+LOCK TABLE v1_definer WRITE;
+UNLOCK TABLES;
+--connection default
+DROP VIEW v1_invoker, v1_definer;
+DROP TABLE t1;
+--remove_file $MYSQLD_DATADIR/test/t1.fix
+
+
+--echo # Testing SQLCOM_UPDATE_MULTI
+--connection default
+# t1 and t2 require FILE_ACL, t3 does not
+CREATE TABLE t1 (a INT) ENGINE=CONNECT TABLE_TYPE=fix FILE_NAME='t1.fix';
+CREATE TABLE t2 (a INT) ENGINE=CONNECT TABLE_TYPE=fix FILE_NAME='t2.fix';
+CREATE TABLE t3 (a INT);
+INSERT INTO t1 VALUES (10);
+INSERT INTO t2 VALUES (20);
+INSERT INTO t3 VALUES (30);
+CREATE SQL SECURITY INVOKER VIEW v1_invoker AS SELECT * FROM t1;
+CREATE SQL SECURITY DEFINER VIEW v1_definer AS SELECT * FROM t1;
+CREATE SQL SECURITY INVOKER VIEW v2_invoker AS SELECT * FROM t2;
+CREATE SQL SECURITY DEFINER VIEW v2_definer AS SELECT * FROM t2;
+UPDATE t1 a1,t1 a2 SET a1.a=50 WHERE a1.a=a2.a;
+UPDATE t1 a1,t2 a2 SET a1.a=50 WHERE a1.a=a2.a;
+UPDATE t1 a1,t3 a2 SET a1.a=50 WHERE a1.a=a2.a;
+UPDATE t1 a1,v1_invoker a2 SET a1.a=50 WHERE a1.a=a2.a;
+UPDATE t1 a1,v1_definer a2 SET a1.a=50 WHERE a1.a=a2.a;
+UPDATE t1 a1,v2_invoker a2 SET a1.a=50 WHERE a1.a=a2.a;
+UPDATE t1 a1,v2_definer a2 SET a1.a=50 WHERE a1.a=a2.a;
+UPDATE t2 a1,t1 a2 SET a1.a=50 WHERE a1.a=a2.a;
+UPDATE t2 a1,t2 a2 SET a1.a=50 WHERE a1.a=a2.a;
+UPDATE t2 a1,t3 a2 SET a1.a=50 WHERE a1.a=a2.a;
+UPDATE t2 a1,v1_invoker a2 SET a1.a=50 WHERE a1.a=a2.a;
+UPDATE t2 a1,v1_definer a2 SET a1.a=50 WHERE a1.a=a2.a;
+UPDATE t2 a1,v2_invoker a2 SET a1.a=50 WHERE a1.a=a2.a;
+UPDATE t2 a1,v2_definer a2 SET a1.a=50 WHERE a1.a=a2.a;
+UPDATE t3 a1,t1 a2 SET a1.a=50 WHERE a1.a=a2.a;
+UPDATE t3 a1,t2 a2 SET a1.a=50 WHERE a1.a=a2.a;
+UPDATE t3 a1,t3 a2 SET a1.a=50 WHERE a1.a=a2.a;
+UPDATE t3 a1,v1_invoker a2 SET a1.a=50 WHERE a1.a=a2.a;
+UPDATE t3 a1,v1_definer a2 SET a1.a=50 WHERE a1.a=a2.a;
+UPDATE t3 a1,v2_invoker a2 SET a1.a=50 WHERE a1.a=a2.a;
+UPDATE t3 a1,v2_definer a2 SET a1.a=50 WHERE a1.a=a2.a;
+UPDATE v1_invoker a1,t1 a2 SET a1.a=50 WHERE a1.a=a2.a;
+UPDATE v1_invoker a1,t2 a2 SET a1.a=50 WHERE a1.a=a2.a;
+UPDATE v1_invoker a1,t3 a2 SET a1.a=50 WHERE a1.a=a2.a;
+UPDATE v1_invoker a1,v1_invoker a2 SET a1.a=50 WHERE a1.a=a2.a;
+UPDATE v1_invoker a1,v1_definer a2 SET a1.a=50 WHERE a1.a=a2.a;
+UPDATE v1_invoker a1,v2_invoker a2 SET a1.a=50 WHERE a1.a=a2.a;
+UPDATE v1_invoker a1,v2_definer a2 SET a1.a=50 WHERE a1.a=a2.a;
+UPDATE v1_definer a1,t1 a2 SET a1.a=50 WHERE a1.a=a2.a;
+UPDATE v1_definer a1,t2 a2 SET a1.a=50 WHERE a1.a=a2.a;
+UPDATE v1_definer a1,t3 a2 SET a1.a=50 WHERE a1.a=a2.a;
+UPDATE v1_definer a1,v1_invoker a2 SET a1.a=50 WHERE a1.a=a2.a;
+UPDATE v1_definer a1,v1_definer a2 SET a1.a=50 WHERE a1.a=a2.a;
+UPDATE v1_definer a1,v2_invoker a2 SET a1.a=50 WHERE a1.a=a2.a;
+UPDATE v1_definer a1,v2_definer a2 SET a1.a=50 WHERE a1.a=a2.a;
+UPDATE v2_invoker a1,t1 a2 SET a1.a=50 WHERE a1.a=a2.a;
+UPDATE v2_invoker a1,t2 a2 SET a1.a=50 WHERE a1.a=a2.a;
+UPDATE v2_invoker a1,t3 a2 SET a1.a=50 WHERE a1.a=a2.a;
+UPDATE v2_invoker a1,v1_invoker a2 SET a1.a=50 WHERE a1.a=a2.a;
+UPDATE v2_invoker a1,v1_definer a2 SET a1.a=50 WHERE a1.a=a2.a;
+UPDATE v2_invoker a1,v2_invoker a2 SET a1.a=50 WHERE a1.a=a2.a;
+UPDATE v2_invoker a1,v2_definer a2 SET a1.a=50 WHERE a1.a=a2.a;
+UPDATE v2_definer a1,t1 a2 SET a1.a=50 WHERE a1.a=a2.a;
+UPDATE v2_definer a1,t2 a2 SET a1.a=50 WHERE a1.a=a2.a;
+UPDATE v2_definer a1,t3 a2 SET a1.a=50 WHERE a1.a=a2.a;
+UPDATE v2_definer a1,v1_invoker a2 SET a1.a=50 WHERE a1.a=a2.a;
+UPDATE v2_definer a1,v1_definer a2 SET a1.a=50 WHERE a1.a=a2.a;
+UPDATE v2_definer a1,v2_invoker a2 SET a1.a=50 WHERE a1.a=a2.a;
+UPDATE v2_definer a1,v2_definer a2 SET a1.a=50 WHERE a1.a=a2.a;
+
+--connection user
+
+# All queries with t1 should fail
+--error ER_ACCESS_DENIED_ERROR
+UPDATE t1 a1,t1 a2 SET a1.a=50 WHERE a1.a=a2.a;
+--error ER_ACCESS_DENIED_ERROR
+UPDATE t1 a1,t2 a2 SET a1.a=50 WHERE a1.a=a2.a;
+--error ER_ACCESS_DENIED_ERROR
+UPDATE t1 a1,t3 a2 SET a1.a=50 WHERE a1.a=a2.a;
+--error ER_ACCESS_DENIED_ERROR
+UPDATE t1 a1,v1_invoker a2 SET a1.a=50 WHERE a1.a=a2.a;
+--error ER_ACCESS_DENIED_ERROR
+UPDATE t1 a1,v1_definer a2 SET a1.a=50 WHERE a1.a=a2.a;
+--error ER_ACCESS_DENIED_ERROR
+UPDATE t1 a1,v2_invoker a2 SET a1.a=50 WHERE a1.a=a2.a;
+--error ER_ACCESS_DENIED_ERROR
+UPDATE t1 a1,v2_definer a2 SET a1.a=50 WHERE a1.a=a2.a;
+
+# All queries with t2 should fail
+--error ER_ACCESS_DENIED_ERROR
+UPDATE t2 a1,t1 a2 SET a1.a=50 WHERE a1.a=a2.a;
+--error ER_ACCESS_DENIED_ERROR
+UPDATE t2 a1,t2 a2 SET a1.a=50 WHERE a1.a=a2.a;
+--error ER_ACCESS_DENIED_ERROR
+UPDATE t2 a1,t3 a2 SET a1.a=50 WHERE a1.a=a2.a;
+--error ER_ACCESS_DENIED_ERROR
+UPDATE t2 a1,v1_invoker a2 SET a1.a=50 WHERE a1.a=a2.a;
+--error ER_ACCESS_DENIED_ERROR
+UPDATE t2 a1,v1_definer a2 SET a1.a=50 WHERE a1.a=a2.a;
+--error ER_ACCESS_DENIED_ERROR
+UPDATE t2 a1,v2_invoker a2 SET a1.a=50 WHERE a1.a=a2.a;
+--error ER_ACCESS_DENIED_ERROR
+UPDATE t2 a1,v2_definer a2 SET a1.a=50 WHERE a1.a=a2.a;
+
+# t3 does not need FILE_ALC
+--error ER_ACCESS_DENIED_ERROR
+UPDATE t3 a1,t1 a2 SET a1.a=50 WHERE a1.a=a2.a;
+--error ER_ACCESS_DENIED_ERROR
+UPDATE t3 a1,t2 a2 SET a1.a=50 WHERE a1.a=a2.a;
+# This is OK:
+UPDATE t3 a1,t3 a2 SET a1.a=50 WHERE a1.a=a2.a;
+--error ER_ACCESS_DENIED_ERROR
+UPDATE t3 a1,v1_invoker a2 SET a1.a=50 WHERE a1.a=a2.a;
+# This is OK:
+UPDATE t3 a1,v1_definer a2 SET a1.a=50 WHERE a1.a=a2.a;
+--error ER_ACCESS_DENIED_ERROR
+UPDATE t3 a1,v2_invoker a2 SET a1.a=50 WHERE a1.a=a2.a;
+# This is OK:
+UPDATE t3 a1,v2_definer a2 SET a1.a=50 WHERE a1.a=a2.a;
+
+# All queries with v1_invoker should fail
+--error ER_ACCESS_DENIED_ERROR
+UPDATE v1_invoker a1,t1 a2 SET a1.a=50 WHERE a1.a=a2.a;
+--error ER_ACCESS_DENIED_ERROR
+UPDATE v1_invoker a1,t2 a2 SET a1.a=50 WHERE a1.a=a2.a;
+--error ER_ACCESS_DENIED_ERROR
+UPDATE v1_invoker a1,t3 a2 SET a1.a=50 WHERE a1.a=a2.a;
+--error ER_ACCESS_DENIED_ERROR
+UPDATE v1_invoker a1,v1_invoker a2 SET a1.a=50 WHERE a1.a=a2.a;
+--error ER_ACCESS_DENIED_ERROR
+UPDATE v1_invoker a1,v1_definer a2 SET a1.a=50 WHERE a1.a=a2.a;
+--error ER_ACCESS_DENIED_ERROR
+UPDATE v1_invoker a1,v2_invoker a2 SET a1.a=50 WHERE a1.a=a2.a;
+--error ER_ACCESS_DENIED_ERROR
+UPDATE v1_invoker a1,v2_definer a2 SET a1.a=50 WHERE a1.a=a2.a;
+
+# v1_definer does not need FILE_ACL from the invoker
+--error ER_ACCESS_DENIED_ERROR
+UPDATE v1_definer a1,t1 a2 SET a1.a=50 WHERE a1.a=a2.a;
+--error ER_ACCESS_DENIED_ERROR
+UPDATE v1_definer a1,t2 a2 SET a1.a=50 WHERE a1.a=a2.a;
+UPDATE v1_definer a1,t3 a2 SET a1.a=50 WHERE a1.a=a2.a;
+--error ER_ACCESS_DENIED_ERROR
+UPDATE v1_definer a1,v1_invoker a2 SET a1.a=50 WHERE a1.a=a2.a;
+UPDATE v1_definer a1,v1_definer a2 SET a1.a=50 WHERE a1.a=a2.a;
+--error ER_ACCESS_DENIED_ERROR
+UPDATE v1_definer a1,v2_invoker a2 SET a1.a=50 WHERE a1.a=a2.a;
+UPDATE v1_definer a1,v2_definer a2 SET a1.a=50 WHERE a1.a=a2.a;
+
+# All queries with v2_invoker should fail
+--error ER_ACCESS_DENIED_ERROR
+UPDATE v2_invoker a1,t1 a2 SET a1.a=50 WHERE a1.a=a2.a;
+--error ER_ACCESS_DENIED_ERROR
+UPDATE v2_invoker a1,t2 a2 SET a1.a=50 WHERE a1.a=a2.a;
+--error ER_ACCESS_DENIED_ERROR
+UPDATE v2_invoker a1,t3 a2 SET a1.a=50 WHERE a1.a=a2.a;
+--error ER_ACCESS_DENIED_ERROR
+UPDATE v2_invoker a1,v1_invoker a2 SET a1.a=50 WHERE a1.a=a2.a;
+--error ER_ACCESS_DENIED_ERROR
+UPDATE v2_invoker a1,v1_definer a2 SET a1.a=50 WHERE a1.a=a2.a;
+--error ER_ACCESS_DENIED_ERROR
+UPDATE v2_invoker a1,v2_invoker a2 SET a1.a=50 WHERE a1.a=a2.a;
+--error ER_ACCESS_DENIED_ERROR
+UPDATE v2_invoker a1,v2_definer a2 SET a1.a=50 WHERE a1.a=a2.a;
+
+# v2_definer does not need FILE_ACL from the invoker
+--error ER_ACCESS_DENIED_ERROR
+UPDATE v2_definer a1,t1 a2 SET a1.a=50 WHERE a1.a=a2.a;
+--error ER_ACCESS_DENIED_ERROR
+UPDATE v2_definer a1,t2 a2 SET a1.a=50 WHERE a1.a=a2.a;
+UPDATE v2_definer a1,t3 a2 SET a1.a=50 WHERE a1.a=a2.a;
+--error ER_ACCESS_DENIED_ERROR
+UPDATE v2_definer a1,v1_invoker a2 SET a1.a=50 WHERE a1.a=a2.a;
+UPDATE v2_definer a1,v1_definer a2 SET a1.a=50 WHERE a1.a=a2.a;
+--error ER_ACCESS_DENIED_ERROR
+UPDATE v2_definer a1,v2_invoker a2 SET a1.a=50 WHERE a1.a=a2.a;
+UPDATE v2_definer a1,v2_definer a2 SET a1.a=50 WHERE a1.a=a2.a;
+
+--connection default
+DROP VIEW v1_invoker, v1_definer, v2_invoker, v2_definer;
+DROP TABLE t1, t2, t3;
+--remove_file $MYSQLD_DATADIR/test/t1.fix
+--remove_file $MYSQLD_DATADIR/test/t2.fix
+
+
+--echo # Testing SQLCOM_DELETE_MULTI
+--connection default
+# t1 and t2 require FILE_ACL, t3 does not
+CREATE TABLE t1 (a INT) ENGINE=CONNECT TABLE_TYPE=fix FILE_NAME='t1.fix';
+CREATE TABLE t2 (a INT) ENGINE=CONNECT TABLE_TYPE=fix FILE_NAME='t2.fix';
+CREATE TABLE t3 (a INT);
+INSERT INTO t1 VALUES (10);
+INSERT INTO t2 VALUES (20);
+INSERT INTO t3 VALUES (30);
+CREATE SQL SECURITY INVOKER VIEW v1_invoker AS SELECT * FROM t1;
+CREATE SQL SECURITY DEFINER VIEW v1_definer AS SELECT * FROM t1;
+CREATE SQL SECURITY INVOKER VIEW v2_invoker AS SELECT * FROM t2;
+CREATE SQL SECURITY DEFINER VIEW v2_definer AS SELECT * FROM t2;
+DELETE a1 FROM t1 a1,t1 a2 WHERE a1.a=a2.a;
+DELETE a1 FROM t1 a1,t2 a2 WHERE a1.a=a2.a;
+DELETE a1 FROM t1 a1,t3 a2 WHERE a1.a=a2.a;
+DELETE a1 FROM t1 a1,v1_invoker a2 WHERE a1.a=a2.a;
+DELETE a1 FROM t1 a1,v1_definer a2 WHERE a1.a=a2.a;
+DELETE a1 FROM t1 a1,v2_invoker a2 WHERE a1.a=a2.a;
+DELETE a1 FROM t1 a1,v2_definer a2 WHERE a1.a=a2.a;
+DELETE a1 FROM t2 a1,t1 a2 WHERE a1.a=a2.a;
+DELETE a1 FROM t2 a1,t2 a2 WHERE a1.a=a2.a;
+DELETE a1 FROM t2 a1,t3 a2 WHERE a1.a=a2.a;
+DELETE a1 FROM t2 a1,v1_invoker a2 WHERE a1.a=a2.a;
+DELETE a1 FROM t2 a1,v1_definer a2 WHERE a1.a=a2.a;
+DELETE a1 FROM t2 a1,v2_invoker a2 WHERE a1.a=a2.a;
+DELETE a1 FROM t2 a1,v2_definer a2 WHERE a1.a=a2.a;
+DELETE a1 FROM t3 a1,t1 a2 WHERE a1.a=a2.a;
+DELETE a1 FROM t3 a1,t2 a2 WHERE a1.a=a2.a;
+DELETE a1 FROM t3 a1,t3 a2 WHERE a1.a=a2.a;
+DELETE a1 FROM t3 a1,v1_invoker a2 WHERE a1.a=a2.a;
+DELETE a1 FROM t3 a1,v1_definer a2 WHERE a1.a=a2.a;
+DELETE a1 FROM t3 a1,v2_invoker a2 WHERE a1.a=a2.a;
+DELETE a1 FROM t3 a1,v2_definer a2 WHERE a1.a=a2.a;
+DELETE a1 FROM v1_invoker a1,t1 a2 WHERE a1.a=a2.a;
+DELETE a1 FROM v1_invoker a1,t2 a2 WHERE a1.a=a2.a;
+DELETE a1 FROM v1_invoker a1,t3 a2 WHERE a1.a=a2.a;
+DELETE a1 FROM v1_invoker a1,v1_invoker a2 WHERE a1.a=a2.a;
+DELETE a1 FROM v1_invoker a1,v1_definer a2 WHERE a1.a=a2.a;
+DELETE a1 FROM v1_invoker a1,v2_invoker a2 WHERE a1.a=a2.a;
+DELETE a1 FROM v1_invoker a1,v2_definer a2 WHERE a1.a=a2.a;
+DELETE a1 FROM v1_definer a1,t1 a2 WHERE a1.a=a2.a;
+DELETE a1 FROM v1_definer a1,t2 a2 WHERE a1.a=a2.a;
+DELETE a1 FROM v1_definer a1,t3 a2 WHERE a1.a=a2.a;
+DELETE a1 FROM v1_definer a1,v1_invoker a2 WHERE a1.a=a2.a;
+DELETE a1 FROM v1_definer a1,v1_definer a2 WHERE a1.a=a2.a;
+DELETE a1 FROM v1_definer a1,v2_invoker a2 WHERE a1.a=a2.a;
+DELETE a1 FROM v1_definer a1,v2_definer a2 WHERE a1.a=a2.a;
+DELETE a1 FROM v2_invoker a1,t1 a2 WHERE a1.a=a2.a;
+DELETE a1 FROM v2_invoker a1,t2 a2 WHERE a1.a=a2.a;
+DELETE a1 FROM v2_invoker a1,t3 a2 WHERE a1.a=a2.a;
+DELETE a1 FROM v2_invoker a1,v1_invoker a2 WHERE a1.a=a2.a;
+DELETE a1 FROM v2_invoker a1,v1_definer a2 WHERE a1.a=a2.a;
+DELETE a1 FROM v2_invoker a1,v2_invoker a2 WHERE a1.a=a2.a;
+DELETE a1 FROM v2_invoker a1,v2_definer a2 WHERE a1.a=a2.a;
+DELETE a1 FROM v2_definer a1,t1 a2 WHERE a1.a=a2.a;
+DELETE a1 FROM v2_definer a1,t2 a2 WHERE a1.a=a2.a;
+DELETE a1 FROM v2_definer a1,t3 a2 WHERE a1.a=a2.a;
+DELETE a1 FROM v2_definer a1,v1_invoker a2 WHERE a1.a=a2.a;
+DELETE a1 FROM v2_definer a1,v1_definer a2 WHERE a1.a=a2.a;
+DELETE a1 FROM v2_definer a1,v2_invoker a2 WHERE a1.a=a2.a;
+DELETE a1 FROM v2_definer a1,v2_definer a2 WHERE a1.a=a2.a;
+
+--connection user
+
+# All queries with t1 should fail
+--error ER_ACCESS_DENIED_ERROR
+DELETE a1 FROM t1 a1,t1 a2 WHERE a1.a=a2.a;
+--error ER_ACCESS_DENIED_ERROR
+DELETE a1 FROM t1 a1,t2 a2 WHERE a1.a=a2.a;
+--error ER_ACCESS_DENIED_ERROR
+DELETE a1 FROM t1 a1,t3 a2 WHERE a1.a=a2.a;
+--error ER_ACCESS_DENIED_ERROR
+DELETE a1 FROM t1 a1,v1_invoker a2 WHERE a1.a=a2.a;
+--error ER_ACCESS_DENIED_ERROR
+DELETE a1 FROM t1 a1,v1_definer a2 WHERE a1.a=a2.a;
+--error ER_ACCESS_DENIED_ERROR
+DELETE a1 FROM t1 a1,v2_invoker a2 WHERE a1.a=a2.a;
+--error ER_ACCESS_DENIED_ERROR
+DELETE a1 FROM t1 a1,v2_definer a2 WHERE a1.a=a2.a;
+
+# All queries with t2 should fail
+--error ER_ACCESS_DENIED_ERROR
+DELETE a1 FROM t2 a1,t1 a2 WHERE a1.a=a2.a;
+--error ER_ACCESS_DENIED_ERROR
+DELETE a1 FROM t2 a1,t2 a2 WHERE a1.a=a2.a;
+--error ER_ACCESS_DENIED_ERROR
+DELETE a1 FROM t2 a1,t3 a2 WHERE a1.a=a2.a;
+--error ER_ACCESS_DENIED_ERROR
+DELETE a1 FROM t2 a1,v1_invoker a2 WHERE a1.a=a2.a;
+--error ER_ACCESS_DENIED_ERROR
+DELETE a1 FROM t2 a1,v1_definer a2 WHERE a1.a=a2.a;
+--error ER_ACCESS_DENIED_ERROR
+DELETE a1 FROM t2 a1,v2_invoker a2 WHERE a1.a=a2.a;
+--error ER_ACCESS_DENIED_ERROR
+DELETE a1 FROM t2 a1,v2_definer a2 WHERE a1.a=a2.a;
+
+# t3 does not need FILE_ALC
+--error ER_ACCESS_DENIED_ERROR
+DELETE a1 FROM t3 a1,t1 a2 WHERE a1.a=a2.a;
+--error ER_ACCESS_DENIED_ERROR
+DELETE a1 FROM t3 a1,t2 a2 WHERE a1.a=a2.a;
+# This is OK:
+DELETE a1 FROM t3 a1,t3 a2 WHERE a1.a=a2.a;
+--error ER_ACCESS_DENIED_ERROR
+DELETE a1 FROM t3 a1,v1_invoker a2 WHERE a1.a=a2.a;
+# This is OK:
+DELETE a1 FROM t3 a1,v1_definer a2 WHERE a1.a=a2.a;
+--error ER_ACCESS_DENIED_ERROR
+DELETE a1 FROM t3 a1,v2_invoker a2 WHERE a1.a=a2.a;
+# This is OK:
+DELETE a1 FROM t3 a1,v2_definer a2 WHERE a1.a=a2.a;
+
+# All queries with v1_invoker should fail
+--error ER_ACCESS_DENIED_ERROR
+DELETE a1 FROM v1_invoker a1,t1 a2 WHERE a1.a=a2.a;
+--error ER_ACCESS_DENIED_ERROR
+DELETE a1 FROM v1_invoker a1,t2 a2 WHERE a1.a=a2.a;
+--error ER_ACCESS_DENIED_ERROR
+DELETE a1 FROM v1_invoker a1,t3 a2 WHERE a1.a=a2.a;
+--error ER_ACCESS_DENIED_ERROR
+DELETE a1 FROM v1_invoker a1,v1_invoker a2 WHERE a1.a=a2.a;
+--error ER_ACCESS_DENIED_ERROR
+DELETE a1 FROM v1_invoker a1,v1_definer a2 WHERE a1.a=a2.a;
+--error ER_ACCESS_DENIED_ERROR
+DELETE a1 FROM v1_invoker a1,v2_invoker a2 WHERE a1.a=a2.a;
+--error ER_ACCESS_DENIED_ERROR
+DELETE a1 FROM v1_invoker a1,v2_definer a2 WHERE a1.a=a2.a;
+
+# v1_definer does not need FILE_ACL from the invoker
+--error ER_ACCESS_DENIED_ERROR
+DELETE a1 FROM v1_definer a1,t1 a2 WHERE a1.a=a2.a;
+--error ER_ACCESS_DENIED_ERROR
+DELETE a1 FROM v1_definer a1,t2 a2 WHERE a1.a=a2.a;
+DELETE a1 FROM v1_definer a1,t3 a2 WHERE a1.a=a2.a;
+--error ER_ACCESS_DENIED_ERROR
+DELETE a1 FROM v1_definer a1,v1_invoker a2 WHERE a1.a=a2.a;
+DELETE a1 FROM v1_definer a1,v1_definer a2 WHERE a1.a=a2.a;
+--error ER_ACCESS_DENIED_ERROR
+DELETE a1 FROM v1_definer a1,v2_invoker a2 WHERE a1.a=a2.a;
+DELETE a1 FROM v1_definer a1,v2_definer a2 WHERE a1.a=a2.a;
+
+# All queries with v2_invoker should fail
+--error ER_ACCESS_DENIED_ERROR
+DELETE a1 FROM v2_invoker a1,t1 a2 WHERE a1.a=a2.a;
+--error ER_ACCESS_DENIED_ERROR
+DELETE a1 FROM v2_invoker a1,t2 a2 WHERE a1.a=a2.a;
+--error ER_ACCESS_DENIED_ERROR
+DELETE a1 FROM v2_invoker a1,t3 a2 WHERE a1.a=a2.a;
+--error ER_ACCESS_DENIED_ERROR
+DELETE a1 FROM v2_invoker a1,v1_invoker a2 WHERE a1.a=a2.a;
+--error ER_ACCESS_DENIED_ERROR
+DELETE a1 FROM v2_invoker a1,v1_definer a2 WHERE a1.a=a2.a;
+--error ER_ACCESS_DENIED_ERROR
+DELETE a1 FROM v2_invoker a1,v2_invoker a2 WHERE a1.a=a2.a;
+--error ER_ACCESS_DENIED_ERROR
+DELETE a1 FROM v2_invoker a1,v2_definer a2 WHERE a1.a=a2.a;
+
+# v2_definer does not need FILE_ACL from the invoker
+--error ER_ACCESS_DENIED_ERROR
+DELETE a1 FROM v2_definer a1,t1 a2 WHERE a1.a=a2.a;
+--error ER_ACCESS_DENIED_ERROR
+DELETE a1 FROM v2_definer a1,t2 a2 WHERE a1.a=a2.a;
+DELETE a1 FROM v2_definer a1,t3 a2 WHERE a1.a=a2.a;
+--error ER_ACCESS_DENIED_ERROR
+DELETE a1 FROM v2_definer a1,v1_invoker a2 WHERE a1.a=a2.a;
+DELETE a1 FROM v2_definer a1,v1_definer a2 WHERE a1.a=a2.a;
+--error ER_ACCESS_DENIED_ERROR
+DELETE a1 FROM v2_definer a1,v2_invoker a2 WHERE a1.a=a2.a;
+DELETE a1 FROM v2_definer a1,v2_definer a2 WHERE a1.a=a2.a;
+
+--connection default
+DROP VIEW v1_invoker, v1_definer, v2_invoker, v2_definer;
+DROP TABLE t1, t2, t3;
+--remove_file $MYSQLD_DATADIR/test/t1.fix
+--remove_file $MYSQLD_DATADIR/test/t2.fix
+
+
+--echo # Testing SQLCOM_CREATE_VIEW
+--connection default
+CREATE TABLE t1 (a INT) ENGINE=CONNECT TABLE_TYPE=fix FILE_NAME='t1.fix';
+INSERT INTO t1 VALUES (10);
+CREATE SQL SECURITY INVOKER VIEW v1_invoker AS SELECT * FROM t1;
+CREATE SQL SECURITY DEFINER VIEW v1_definer AS SELECT * FROM t1;
+CREATE VIEW v2 AS SELECT * FROM v1_invoker;
+DROP VIEW v2;
+CREATE VIEW v2 AS SELECT * FROM v1_definer;
+DROP VIEW v2;
+--connection user
+--error ER_ACCESS_DENIED_ERROR
+CREATE VIEW v2 AS SELECT * FROM t1;
+--error ER_ACCESS_DENIED_ERROR
+CREATE VIEW v2 AS SELECT * FROM v1_invoker;
+CREATE VIEW v2 AS SELECT * FROM v1_definer;
+DROP VIEW v2;
+--connection default
+DROP VIEW v1_invoker, v1_definer;
+DROP TABLE t1;
+--remove_file $MYSQLD_DATADIR/test/t1.fix
+
+
+--echo # Testing SQLCOM_INSERT_SELECT
+--connection default
+CREATE TABLE t1 (a INT) ENGINE=CONNECT TABLE_TYPE=fix FILE_NAME='t1.fix';
+INSERT INTO t1 VALUES (10);
+CREATE SQL SECURITY INVOKER VIEW v1_invoker AS SELECT * FROM t1;
+CREATE SQL SECURITY DEFINER VIEW v1_definer AS SELECT * FROM t1;
+INSERT INTO t1 SELECT * FROM t1 WHERE a=20;
+INSERT INTO t1 SELECT * FROM v1_invoker WHERE a=20;
+INSERT INTO t1 SELECT * FROM v1_definer WHERE a=20;
+INSERT INTO v1_invoker SELECT * FROM t1 WHERE a=20;
+INSERT INTO v1_invoker SELECT * FROM v1_invoker WHERE a=20;
+INSERT INTO v1_invoker SELECT * FROM v1_definer WHERE a=20;
+INSERT INTO v1_definer SELECT * FROM t1 WHERE a=20;
+INSERT INTO v1_definer SELECT * FROM v1_invoker WHERE a=20;
+INSERT INTO v1_definer SELECT * FROM v1_definer WHERE a=20;
+--connection user
+--error ER_ACCESS_DENIED_ERROR
+INSERT INTO t1 SELECT * FROM t1 WHERE a=20;
+--error ER_ACCESS_DENIED_ERROR
+INSERT INTO t1 SELECT * FROM v1_invoker WHERE a=20;
+--error ER_ACCESS_DENIED_ERROR
+INSERT INTO t1 SELECT * FROM v1_definer WHERE a=20;
+--error ER_ACCESS_DENIED_ERROR
+INSERT INTO v1_invoker SELECT * FROM t1 WHERE a=20;
+--error ER_ACCESS_DENIED_ERROR
+INSERT INTO v1_invoker SELECT * FROM v1_invoker WHERE a=20;
+--error ER_ACCESS_DENIED_ERROR
+INSERT INTO v1_invoker SELECT * FROM v1_definer WHERE a=20;
+--error ER_ACCESS_DENIED_ERROR
+INSERT INTO v1_definer SELECT * FROM t1 WHERE a=20;
+--error ER_ACCESS_DENIED_ERROR
+INSERT INTO v1_definer SELECT * FROM v1_invoker WHERE a=20;
+# This is OK:
+INSERT INTO v1_definer SELECT * FROM v1_definer WHERE a=20;
+--connection default
+DROP VIEW v1_invoker, v1_definer;
+DROP TABLE t1;
+--remove_file $MYSQLD_DATADIR/test/t1.fix
+
+
+--echo # Testing SQLCOM_REPLACE_SELECT
+# REPLACE is not supported by CONNECT
+# so we're testing ER_NOT_ALLOWED_COMMAND vs ER_ACCESS_DENIED_ERROR here
+--connection default
+CREATE TABLE t1 (a INT) ENGINE=CONNECT TABLE_TYPE=fix FILE_NAME='t1.fix';
+INSERT INTO t1 VALUES (10);
+CREATE SQL SECURITY INVOKER VIEW v1_invoker AS SELECT * FROM t1;
+CREATE SQL SECURITY DEFINER VIEW v1_definer AS SELECT * FROM t1;
+--error ER_NOT_ALLOWED_COMMAND
+REPLACE INTO t1 SELECT * FROM t1 WHERE a=20;
+--error ER_NOT_ALLOWED_COMMAND
+REPLACE INTO t1 SELECT * FROM v1_invoker WHERE a=20;
+--error ER_NOT_ALLOWED_COMMAND
+REPLACE INTO t1 SELECT * FROM v1_definer WHERE a=20;
+--error ER_NOT_ALLOWED_COMMAND
+REPLACE INTO v1_invoker SELECT * FROM t1 WHERE a=20;
+--error ER_NOT_ALLOWED_COMMAND
+REPLACE INTO v1_invoker SELECT * FROM v1_invoker WHERE a=20;
+--error ER_NOT_ALLOWED_COMMAND
+REPLACE INTO v1_invoker SELECT * FROM v1_definer WHERE a=20;
+--error ER_NOT_ALLOWED_COMMAND
+REPLACE INTO v1_definer SELECT * FROM t1 WHERE a=20;
+--error ER_NOT_ALLOWED_COMMAND
+REPLACE INTO v1_definer SELECT * FROM v1_invoker WHERE a=20;
+--error ER_NOT_ALLOWED_COMMAND
+REPLACE INTO v1_definer SELECT * FROM v1_definer WHERE a=20;
+
+--connection user
+--error ER_ACCESS_DENIED_ERROR
+REPLACE INTO t1 SELECT * FROM t1 WHERE a=20;
+--error ER_ACCESS_DENIED_ERROR
+REPLACE INTO t1 SELECT * FROM v1_invoker WHERE a=20;
+--error ER_ACCESS_DENIED_ERROR
+REPLACE INTO t1 SELECT * FROM v1_definer WHERE a=20;
+--error ER_ACCESS_DENIED_ERROR
+REPLACE INTO v1_invoker SELECT * FROM t1 WHERE a=20;
+--error ER_ACCESS_DENIED_ERROR
+REPLACE INTO v1_invoker SELECT * FROM v1_invoker WHERE a=20;
+--error ER_ACCESS_DENIED_ERROR
+REPLACE INTO v1_invoker SELECT * FROM v1_definer WHERE a=20;
+--error ER_NOT_ALLOWED_COMMAND
+REPLACE INTO v1_definer SELECT * FROM t1 WHERE a=20;
+--error ER_NOT_ALLOWED_COMMAND
+REPLACE INTO v1_definer SELECT * FROM v1_invoker WHERE a=20;
+--error ER_NOT_ALLOWED_COMMAND
+REPLACE INTO v1_definer SELECT * FROM v1_definer WHERE a=20;
+--connection default
+DROP VIEW v1_invoker, v1_definer;
+DROP TABLE t1;
+--remove_file $MYSQLD_DATADIR/test/t1.fix
+
+
+--echo # Testing SQLCOM_RENAME_TABLE
+--connection default
+CREATE TABLE t1 (a INT) ENGINE=CONNECT TABLE_TYPE=fix FILE_NAME='t1.fix';
+INSERT INTO t1 VALUES (10);
+RENAME TABLE t1 TO t2;
+SHOW CREATE TABLE t2;
+RENAME TABLE t2 TO t1;
+--connection user
+# TODO: Perhaps FILE_ACL is needed for RENAME. Discuss with Oliver.
+--error ER_ACCESS_DENIED_ERROR
+RENAME TABLE t1 TO t2;
+--connection default
+DROP TABLE t1;
+--remove_file $MYSQLD_DATADIR/test/t1.fix
+
+
+--echo # Testing SQLCOM_ALTER_TABLE (for ALTER..RENAME)
+--connection default
+CREATE TABLE t1 (a INT) ENGINE=CONNECT TABLE_TYPE=fix FILE_NAME='t1.fix';
+INSERT INTO t1 VALUES (10);
+ALTER TABLE t1 RENAME TO t2;
+SHOW CREATE TABLE t2;
+ALTER TABLE t2 RENAME TO t1;
+--connection user
+# TODO: Perhaps FILE_ACL is not needed for ALTER..RENAME. Discuss with Olivier.
+--error ER_ACCESS_DENIED_ERROR
+ALTER TABLE t1 RENAME TO t2;
+--connection default
+DROP TABLE t1;
+--remove_file $MYSQLD_DATADIR/test/t1.fix
+
+
+--echo # Testing SQLCOM_ALTER_TABLE (changing ENGINE to non-CONNECT)
+--connection default
+CREATE TABLE t1 (a INT) ENGINE=CONNECT TABLE_TYPE=fix FILE_NAME='t1.fix';
+INSERT INTO t1 VALUES (10);
+ALTER TABLE t1 ENGINE=MyISAM;
+DROP TABLE t1;
+--remove_file $MYSQLD_DATADIR/test/t1.fix
+CREATE TABLE t1 (a INT) ENGINE=CONNECT TABLE_TYPE=fix FILE_NAME='t1.fix';
+INSERT INTO t1 VALUES (10);
+--connection user
+--error ER_ACCESS_DENIED_ERROR
+ALTER TABLE t1 ENGINE=MyISAM;
+--connection default
+DROP TABLE t1;
+--remove_file $MYSQLD_DATADIR/test/t1.fix
+
+
+--echo # Testing SQLCOM_ALTER_TABLE (changing ENGINE to CONNECT)
+--connection default
+CREATE TABLE t1 (a INT) ENGINE=MyISAM;
+INSERT INTO t1 VALUES (10);
+SELECT * FROM t1;
+# This should succeed, as 't1.fix' does not exists.
+ALTER TABLE t1 ENGINE=CONNECT TABLE_TYPE=fix FILE_NAME='t1.fix';
+DROP TABLE t1;
+CREATE TABLE t1 (a INT) ENGINE=MyISAM;
+INSERT INTO t1 VALUES (10);
+--connection user
+--error ER_ACCESS_DENIED_ERROR
+ALTER TABLE t1 ENGINE=CONNECT TABLE_TYPE=fix FILE_NAME='t1.fix';
+--connection default
+DROP TABLE t1;
+
+
+--echo # Testing SQLCOM_OPTIMIZE
+--connection default
+CREATE TABLE t1 (a INT NOT NULL, KEY(a)) ENGINE=CONNECT TABLE_TYPE=fix FILE_NAME='t1.fix';
+INSERT INTO t1 VALUES (10);
+OPTIMIZE TABLE t1;
+--connection user
+# This command succeeds, but reports "Access denied" in the "Msg_text" column.
+OPTIMIZE TABLE t1;
+--connection default
+DROP TABLE t1;
+--remove_file $MYSQLD_DATADIR/test/t1.fix
+
+
+--echo # Testing SQLCOM_ALTER_TABLE (adding columns)
+--connection default
+CREATE TABLE t1 (a INT) ENGINE=CONNECT TABLE_TYPE=fix FILE_NAME='t1.fix';
+INSERT INTO t1 VALUES (10);
+ALTER TABLE t1 ADD b INT;
+--connection user
+--error ER_ACCESS_DENIED_ERROR
+ALTER TABLE t1 ADD c INT;
+--connection default
+DROP TABLE t1;
+--remove_file $MYSQLD_DATADIR/test/t1.fix
+
+
+--echo # Testing SQLCOM_ALTER_TABLE (removing columns)
+--connection default
+CREATE TABLE t1 (a INT,b INT,c INT) ENGINE=CONNECT TABLE_TYPE=fix FILE_NAME='t1.fix';
+INSERT INTO t1 VALUES (10,10,10);
+ALTER TABLE t1 DROP b;
+--connection user
+--error ER_ACCESS_DENIED_ERROR
+ALTER TABLE t1 DROP c;
+--connection default
+DROP TABLE t1;
+--remove_file $MYSQLD_DATADIR/test/t1.fix
+
+
+--echo # Testing SQLCOM_ALTER_TABLE (adding keys)
+--connection default
+CREATE TABLE t1 (a INT NOT NULL,b INT NOT NULL) ENGINE=CONNECT TABLE_TYPE=fix FILE_NAME='t1.fix';
+INSERT INTO t1 VALUES (10,10);
+ALTER TABLE t1 ADD KEY(a);
+--connection user
+--error ER_ACCESS_DENIED_ERROR
+ALTER TABLE t1 ADD KEY(b);
+--connection default
+DROP TABLE t1;
+--remove_file $MYSQLD_DATADIR/test/t1.fix
+--remove_file $MYSQLD_DATADIR/test/t1.fnx
+
+
+--echo # Testing SQLCOM_ALTER_TABLE (removing keys)
+--connection default
+CREATE TABLE t1 (a INT NOT NULL,b INT NOT NULL, KEY a(a), KEY b(b)) ENGINE=CONNECT TABLE_TYPE=fix FILE_NAME='t1.fix';
+INSERT INTO t1 VALUES (10,10);
+ALTER TABLE t1 DROP KEY a;
+--connection user
+--error ER_ACCESS_DENIED_ERROR
+ALTER TABLE t1 DROP KEY b;
+--connection default
+DROP TABLE t1;
+--remove_file $MYSQLD_DATADIR/test/t1.fix
+--remove_file $MYSQLD_DATADIR/test/t1.fnx
+
+
+--echo # Testing SQLCOM_CREATE_INDEX and SQLCOM_DROP_INDEX
+--connection default
+CREATE TABLE t1 (a INT NOT NULL,b INT NOT NULL) ENGINE=CONNECT TABLE_TYPE=fix FILE_NAME='t1.fix';
+INSERT INTO t1 VALUES (10,10);
+CREATE INDEX a ON t1 (a);
+DROP INDEX a ON t1;
+CREATE INDEX a ON t1 (a);
+--connection user
+--error ER_ACCESS_DENIED_ERROR
+CREATE INDEX b ON t1 (b);
+--error ER_ACCESS_DENIED_ERROR
+DROP INDEX a ON t1;
+--connection default
+DROP TABLE t1;
+--remove_file $MYSQLD_DATADIR/test/t1.fix
+--remove_file $MYSQLD_DATADIR/test/t1.fnx
+
+
+#SQLCOM_ALTER_TABLE (change engine to CONNECT):
+
+
+
+DROP USER user@localhost;
diff --git a/storage/connect/mysql-test/connect/t/ini_grant.test b/storage/connect/mysql-test/connect/t/ini_grant.test
index 3067864..ebc7a80 100644
--- a/storage/connect/mysql-test/connect/t/ini_grant.test
+++ b/storage/connect/mysql-test/connect/t/ini_grant.test
@@ -54,7 +54,7 @@ CREATE VIEW v1 AS SELECT * FROM t1;
--echo # Testing a VIEW created with FILE privileges but accessed with no FILE
--connection default
SELECT user();
-CREATE VIEW v1 AS SELECT * FROM t1;
+CREATE SQL SECURITY INVOKER VIEW v1 AS SELECT * FROM t1;
--connection user
SELECT user();
--error ER_ACCESS_DENIED_ERROR
diff --git a/storage/connect/mysql-test/connect/t/mysql_grant.test b/storage/connect/mysql-test/connect/t/mysql_grant.test
index 7c75103..05337ef 100644
--- a/storage/connect/mysql-test/connect/t/mysql_grant.test
+++ b/storage/connect/mysql-test/connect/t/mysql_grant.test
@@ -54,7 +54,7 @@ CREATE VIEW v1 AS SELECT * FROM t1;
--echo # Testing a VIEW created with FILE privileges but accessed with no FILE
--connection default
SELECT user();
-CREATE VIEW v1 AS SELECT * FROM t1;
+CREATE SQL SECURITY INVOKER VIEW v1 AS SELECT * FROM t1;
--connection user
SELECT user();
--error ER_ACCESS_DENIED_ERROR
diff --git a/storage/connect/mysql-test/connect/t/odbc_sqlite3_grant.test b/storage/connect/mysql-test/connect/t/odbc_sqlite3_grant.test
index 7664a44..887385a 100644
--- a/storage/connect/mysql-test/connect/t/odbc_sqlite3_grant.test
+++ b/storage/connect/mysql-test/connect/t/odbc_sqlite3_grant.test
@@ -56,9 +56,10 @@ ALTER TABLE t1 READONLY=1;
CREATE VIEW v1 AS SELECT * FROM t1;
--echo # Testing a VIEW created with FILE privileges but accessed with no FILE
+--echo # using SQL SECIRITY INVOKER
--connection default
SELECT user();
-CREATE VIEW v1 AS SELECT * FROM t1;
+CREATE SQL SECURITY INVOKER VIEW v1 AS SELECT * FROM t1;
--connection user
SELECT user();
--error ER_ACCESS_DENIED_ERROR
@@ -70,6 +71,17 @@ UPDATE v1 SET a=123;
--error ER_ACCESS_DENIED_ERROR
DELETE FROM v1;
+--echo # Testing a VIEW created with FILE privileges but accessed with no FILE
+--echo # using SQL SECIRITY DEFINER
+--connection default
+DROP VIEW v1;
+SELECT user();
+CREATE SQL SECURITY DEFINER VIEW v1 AS SELECT * FROM t1;
+--connection user
+SELECT user();
+SELECT * FROM v1 WHERE a='test1';
+
+
--disconnect user
--connection default
SELECT user();
Follow ups
References
