maria-developers team mailing list archive
-
maria-developers team
-
Mailing list archive
-
Message #08200
Re: PLEASE REVIEW: (MDEV-7574) Security definer views don't work with CONNECT ODBC tables
Hi Sergei,
On 02/19/2015 07:12 PM, Sergei Golubchik wrote:
Hi, Alexander!
On Feb 17, Alexander Barkov wrote:
From what I understood, FILE_ACL is written (among the other
privileges) into thd->security_ctx.privilege in
TABLE_LIST::prepare_security(). In case of a DEFINER view,
thd->security_ctx.privilege is filled exactly with the definer
privileges, and to the invoker privileges otherwise.
So inside ha_connect::check_privileges() the fact that there is
FILE_ACL in thd->security_ctx.privilege means that
TABLE_LIST::prepare_security() was previously called and FILE_ACL is
set to DEFINER or INVOKER, according to the view definition. This is
exactly what we need.
Agree, looks good so far :)
I'm not sure about the opposite: if there is no FILE_ACL in
thd->security_ctx.privilege, what does it mean? Does it mean that
there is no FILE_ACL for the effective user? Or can it also mean that
TABLE_LIST::prepare_security() was not called?
As far as I can see, TABLE_LIST::prepare_security() is always called for
a view that was successfully opened. That is, no FILE_ACL bit means that
there is no FILE privilege.
=== modified file 'storage/connect/ha_connect.cc'
--- storage/connect/ha_connect.cc 2015-01-20 00:21:56 +0000
+++ storage/connect/ha_connect.cc 2015-02-17 13:46:34 +0000
@@ -3865,6 +3865,8 @@ bool ha_connect::check_privileges(THD *t
case TAB_MAC:
case TAB_WMI:
case TAB_OEM:
+ if (table && (table->grant.privilege & FILE_ACL))
+ return false;
return check_access(thd, FILE_ACL, db, NULL, NULL, 0, 0);
I don't like that you use both approaches to check the privileges. This
just looks wrong. It should rather be
* if it's called from ::external_lock() - use table->grant.privilege.
* otherwise (::create() or ::delete_or_rename_table()) - don't use
table->grant.privilege, only use check_access().
Something like
if (called_from_external_lock)
return table->grant.privilege & FILE_ACL; // respect view's definer
else
return check_access(thd, FILE_ACL, db, NULL, NULL, 0, 0);
Thanks for the suggestion!
Please find a new version attached. It now uses
thd_sql_command() and lock_type to decide whether
to use table->grant.privilege or check_access().
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
With this change "mtr --embedded" passes all tests,
including those I added in this patch.
Possibly, a there is a better way to do this.
Please suggest.
Thanks.
Regards,
Sergei
=== 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
/*
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);
@@ -3978,7 +3992,25 @@ bool ha_connect::check_privileges(THD *t
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()
+ return check_access(thd, FILE_ACL, db, NULL, NULL, 0, 0);
+ }
// This is temporary until a solution is found
case TAB_TBL:
@@ -4019,6 +4051,15 @@ bool ha_connect::IsSameIndex(PIXDEF xp1,
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)
{
@@ -4079,9 +4120,7 @@ MODE ha_connect::CheckMode(PGLOBAL g, TH
// } // 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
@@ -4095,7 +4134,7 @@ MODE ha_connect::CheckMode(PGLOBAL g, TH
case SQLCOM_LOAD:
case SQLCOM_INSERT_SELECT:
// case SQLCOM_REPLACE:
-// case SQLCOM_REPLACE_SELECT:
+ case SQLCOM_REPLACE_SELECT: // REPLACE t1 SELECT * FROM connect_table
case SQLCOM_DELETE:
case SQLCOM_DELETE_MULTI:
case SQLCOM_TRUNCATE:
@@ -4128,9 +4167,7 @@ MODE ha_connect::CheckMode(PGLOBAL g, TH
// } // 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
@@ -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;
+ 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;
@@ -4363,7 +4461,7 @@ int ha_connect::external_lock(THD *thd,
DBUG_ASSERT(table && table->s);
- if (check_privileges(thd, options, table->s->db.str)) {
+ if (check_privileges_external_lock(g, thd, options, lock_type)) {
strcpy(g->Message, "This operation requires the FILE privilege");
htrc("%s\n", g->Message);
DBUG_RETURN(HA_ERR_INTERNAL_ERROR);
@@ -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;
=== modified file 'storage/connect/ha_connect.h'
--- storage/connect/ha_connect.h 2015-01-09 22:36:50 +0000
+++ storage/connect/ha_connect.h 2015-02-24 07:31:05 +0000
@@ -530,8 +530,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, int lock_type);
MODE CheckMode(PGLOBAL g, THD *thd, MODE newmode, bool *chk, bool *cras);
char *GetDBfromName(const char *name);
=== modified file 'storage/connect/mysql-test/connect/r/grant.result'
--- storage/connect/mysql-test/connect/r/grant.result 2013-05-21 14:29:10 +0000
+++ storage/connect/mysql-test/connect/r/grant.result 2015-02-24 10:17:30 +0000
@@ -43,10 +43,79 @@ ERROR 28000: Access denied for user 'use
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 SECURITY DEFINER
SELECT user();
user()
root@localhost
-CREATE VIEW v1 AS SELECT * FROM t1;
+CREATE SQL SECURITY DEFINER VIEW v1 AS SELECT * FROM t1;
+SELECT user();
+user()
+user@localhost
+SELECT fname, ftype FROM v1 ORDER BY fname, ftype;
+fname ftype
+t1 .frm
+v1 .frm
+INSERT INTO v1 (fname, ftype) VALUES ('aaa','bin');
+ERROR HY000: Got error 174 'COLBLK SetBuffer: undefined Access Method' from CONNECT
+REPLACE INTO v1 (fname, ftype) VALUES ('aaa','bin');
+ERROR 42000: CONNECT Unsupported command
+UPDATE v1 SET fname='aaa';
+ERROR HY000: Got error 174 'COLBLK SetBuffer: undefined Access Method' from CONNECT
+DELETE FROM v1;
+ERROR HY000: Got error 122 'DIR tables are read/only' from CONNECT
+# Testing LOAD DATA for a VIEW with SQL SECURITY DEFINER
+LOAD DATA LOCAL INFILE 'MTR_SUITE_DIR/std_data/boys.txt' INTO TABLE v1;;
+ERROR HY000: Got error 174 'COLBLK SetBuffer: undefined Access Method' from CONNECT
+LOAD DATA INFILE 'test.txt' INTO TABLE v1;
+ERROR 28000: Access denied for user 'user'@'localhost' (using password: NO)
+# Testing multiple-table statements
+CREATE TABLE t2 ENGINE=MyISAM AS SELECT fname, ftype FROM v1;
+SELECT * FROM t2 ORDER BY fname, ftype;
+fname ftype
+t1 .frm
+t2 .frm
+t2 .MYD
+t2 .MYI
+v1 .frm
+DELETE FROM t2;
+ALTER TABLE t2 ADD PRIMARY KEY (fname, ftype);
+INSERT IGNORE INTO t2 SELECT fname, ftype FROM v1;
+REPLACE INTO t2 SELECT fname, ftype FROM v1;
+SELECT fname,ftype FROM t2 ORDER BY fname, ftype;
+fname ftype
+t1 .frm
+t2 .frm
+t2 .MYD
+t2 .MYI
+v1 .frm
+ALTER TABLE t2 ADD cnt INT NOT NULL;
+UPDATE t2,v1 SET t2.cnt=1 WHERE t2.fname=v1.fname;
+SELECT fname,ftype FROM t2 ORDER BY fname, ftype;
+fname ftype
+t1 .frm
+t2 .frm
+t2 .MYD
+t2 .MYI
+v1 .frm
+DELETE t2 FROM t2,v1 WHERE t2.fname=v1.fname AND t2.ftype=v1.ftype;
+SELECT fname,ftype FROM v1 ORDER BY fname,ftype;
+fname ftype
+t1 .frm
+t2 .frm
+t2 .MYD
+t2 .MYI
+v1 .frm
+DROP TABLE t2;
+DROP VIEW v1;
+#
+# End of tests with SQL SECURITY DEFINER
+#
+# Testing a VIEW created with FILE privileges but accessed with no FILE
+# Using SQL SECURITY INVOKER
+SELECT user();
+user()
+root@localhost
+CREATE SQL SECURITY INVOKER VIEW v1 AS SELECT * FROM t1;
SELECT user();
user()
user@localhost
@@ -58,6 +127,31 @@ UPDATE v1 SET path=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 LOAD DATA for a VIEW with SQL SECURITY INVOKER
+LOAD DATA LOCAL INFILE 'MTR_SUITE_DIR/std_data/boys.txt' INTO TABLE v1;;
+ERROR 28000: Access denied for user 'user'@'localhost' (using password: NO)
+LOAD DATA INFILE 'test.txt' INTO TABLE v1;
+ERROR 28000: Access denied for user 'user'@'localhost' (using password: NO)
+# Testing multiple-table statements
+CREATE TABLE t2 AS SELECT fname, ftype FROM v1;
+ERROR 28000: Access denied for user 'user'@'localhost' (using password: NO)
+CREATE TABLE t2 (fname VARCHAR(256) NOT NULL, ftype CHAR(4) NOT NULL) ENGINE=MyISAM;
+INSERT INTO t2 VALUES ('test','bin');
+INSERT IGNORE INTO t2 SELECT fname, ftype FROM v1;
+ERROR 28000: Access denied for user 'user'@'localhost' (using password: NO)
+INSERT IGNORE INTO t2 SELECT fname, ftype FROM v1;
+ERROR 28000: Access denied for user 'user'@'localhost' (using password: NO)
+REPLACE INTO t2 SELECT fname, ftype FROM v1;
+ERROR 28000: Access denied for user 'user'@'localhost' (using password: NO)
+ALTER TABLE t2 ADD cnt INT NOT NULL;
+UPDATE t2,v1 SET t2.cnt=1 WHERE t2.fname=v1.fname;
+ERROR 28000: Access denied for user 'user'@'localhost' (using password: NO)
+DELETE t2 FROM t2,v1 WHERE t2.fname=v1.fname AND t2.ftype=v1.ftype;
+ERROR 28000: Access denied for user 'user'@'localhost' (using password: NO)
+DROP TABLE t2;
+#
+# End of tests with SQL SECURITY INVOKER
+#
SELECT user();
user()
root@localhost
=== modified file 'storage/connect/mysql-test/connect/r/mysql_grant.result'
--- storage/connect/mysql-test/connect/r/mysql_grant.result 2013-05-21 14:29:10 +0000
+++ storage/connect/mysql-test/connect/r/mysql_grant.result 2015-02-17 13:40:33 +0000
@@ -40,7 +40,7 @@ ERROR 28000: Access denied for user 'use
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
=== modified file 'storage/connect/mysql-test/connect/r/odbc_sqlite3_grant.result'
--- storage/connect/mysql-test/connect/r/odbc_sqlite3_grant.result 2013-11-06 17:22:09 +0000
+++ storage/connect/mysql-test/connect/r/odbc_sqlite3_grant.result 2015-02-24 10:30:25 +0000
@@ -49,10 +49,11 @@ ERROR 28000: Access denied for user 'use
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
=== modified file 'storage/connect/mysql-test/connect/t/grant.test'
--- storage/connect/mysql-test/connect/t/grant.test 2013-05-21 14:29:10 +0000
+++ storage/connect/mysql-test/connect/t/grant.test 2015-02-24 10:19:01 +0000
@@ -47,9 +47,65 @@ 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 SECURITY DEFINER
--connection default
SELECT user();
-CREATE VIEW v1 AS SELECT * FROM t1;
+CREATE SQL SECURITY DEFINER VIEW v1 AS SELECT * FROM t1;
+--connection user
+SELECT user();
+SELECT fname, ftype FROM v1 ORDER BY fname, ftype;
+
+# The following commands successfully pass access control but then fail
+# because the underlying table does not support data change commands.
+# The important thing is that the commands don't fail on ER_ACCESS_DENIED_ERROR
+
+--error ER_GET_ERRMSG
+INSERT INTO v1 (fname, ftype) VALUES ('aaa','bin');
+--error ER_NOT_ALLOWED_COMMAND
+REPLACE INTO v1 (fname, ftype) VALUES ('aaa','bin');
+--error ER_GET_ERRMSG
+UPDATE v1 SET fname='aaa';
+--error ER_GET_ERRMSG
+DELETE FROM v1;
+
+--echo # Testing LOAD DATA for a VIEW with SQL SECURITY DEFINER
+--replace_result $MTR_SUITE_DIR MTR_SUITE_DIR
+--error ER_GET_ERRMSG
+--eval LOAD DATA LOCAL INFILE '$MTR_SUITE_DIR/std_data/boys.txt' INTO TABLE v1;
+
+# Non-LOCAL LOAD DATA still fails on ER_ACCESS_DENIED_ERROR, even though
+# the view DEFINER has FILE privilege to access the underlying table data.
+# The reason is that the invoker does not have FILE privilege
+# to access to load the specified server-side *file*.
+--error ER_ACCESS_DENIED_ERROR
+LOAD DATA INFILE 'test.txt' INTO TABLE v1;
+
+--echo # Testing multiple-table statements
+
+CREATE TABLE t2 ENGINE=MyISAM AS SELECT fname, ftype FROM v1;
+SELECT * FROM t2 ORDER BY fname, ftype;
+DELETE FROM t2;
+ALTER TABLE t2 ADD PRIMARY KEY (fname, ftype);
+INSERT IGNORE INTO t2 SELECT fname, ftype FROM v1;
+REPLACE INTO t2 SELECT fname, ftype FROM v1;
+SELECT fname,ftype FROM t2 ORDER BY fname, ftype;
+ALTER TABLE t2 ADD cnt INT NOT NULL;
+UPDATE t2,v1 SET t2.cnt=1 WHERE t2.fname=v1.fname;
+SELECT fname,ftype FROM t2 ORDER BY fname, ftype;
+DELETE t2 FROM t2,v1 WHERE t2.fname=v1.fname AND t2.ftype=v1.ftype;
+SELECT fname,ftype FROM v1 ORDER BY fname,ftype;
+DROP TABLE t2;
+--connection default
+DROP VIEW v1;
+--echo #
+--echo # End of tests with SQL SECURITY DEFINER
+--echo #
+
+--echo # Testing a VIEW created with FILE privileges but accessed with no FILE
+--echo # Using SQL SECURITY INVOKER
+--connection default
+SELECT user();
+CREATE SQL SECURITY INVOKER VIEW v1 AS SELECT * FROM t1;
--connection user
SELECT user();
--error ER_ACCESS_DENIED_ERROR
@@ -61,6 +117,36 @@ UPDATE v1 SET path=123;
--error ER_ACCESS_DENIED_ERROR
DELETE FROM v1;
+--echo # Testing LOAD DATA for a VIEW with SQL SECURITY INVOKER
+--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;
+--error ER_ACCESS_DENIED_ERROR
+LOAD DATA INFILE 'test.txt' INTO TABLE v1;
+
+--echo # Testing multiple-table statements
+
+--error ER_ACCESS_DENIED_ERROR
+CREATE TABLE t2 AS SELECT fname, ftype FROM v1;
+CREATE TABLE t2 (fname VARCHAR(256) NOT NULL, ftype CHAR(4) NOT NULL) ENGINE=MyISAM;
+INSERT INTO t2 VALUES ('test','bin');
+--error ER_ACCESS_DENIED_ERROR
+INSERT IGNORE INTO t2 SELECT fname, ftype FROM v1;
+--error ER_ACCESS_DENIED_ERROR
+INSERT IGNORE INTO t2 SELECT fname, ftype FROM v1;
+--error ER_ACCESS_DENIED_ERROR
+REPLACE INTO t2 SELECT fname, ftype FROM v1;
+ALTER TABLE t2 ADD cnt INT NOT NULL;
+--error ER_ACCESS_DENIED_ERROR
+UPDATE t2,v1 SET t2.cnt=1 WHERE t2.fname=v1.fname;
+--error ER_ACCESS_DENIED_ERROR
+DELETE t2 FROM t2,v1 WHERE t2.fname=v1.fname AND t2.ftype=v1.ftype;
+DROP TABLE t2;
+--echo #
+--echo # End of tests with SQL SECURITY INVOKER
+--echo #
+
+
--disconnect user
--connection default
SELECT user();
=== modified file 'storage/connect/mysql-test/connect/t/mysql_grant.test'
--- storage/connect/mysql-test/connect/t/mysql_grant.test 2013-10-27 09:37:12 +0000
+++ storage/connect/mysql-test/connect/t/mysql_grant.test 2015-02-17 13:40:21 +0000
@@ -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
=== modified file 'storage/connect/mysql-test/connect/t/odbc_sqlite3_grant.test'
--- storage/connect/mysql-test/connect/t/odbc_sqlite3_grant.test 2013-05-24 11:21:06 +0000
+++ storage/connect/mysql-test/connect/t/odbc_sqlite3_grant.test 2015-02-24 10:30:03 +0000
@@ -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
