diff options
| author | Sergei Golubchik <serg@mariadb.org> | 2015-07-26 00:03:34 +0200 |
|---|---|---|
| committer | Sergei Golubchik <serg@mariadb.org> | 2015-07-27 10:52:21 +0200 |
| commit | 40a6160f4f977d6d142bdb80f7a4f944c7747097 (patch) | |
| tree | f17e7c9254de70c1b75c84ad5883a51c42a594b6 /storage | |
| parent | 121f3e4c90f5cc081b10c38024279df57777295f (diff) | |
| download | mariadb-git-40a6160f4f977d6d142bdb80f7a4f944c7747097.tar.gz | |
MDEV-7574 Security definer views don't work with CONNECT ODBC tables
Instead of checking user's privileges with check_access(),
use the cached value in table->grant.privilege instead -
it is correctly set to the invoker or definer, depending
on SQL SECURITY mode.
Continue to use check_access() for DDLs when
table->grant.privilege may be not set (but these cases are
only possible on tables, never for views).
(patch originally by Alexander Barkov)
Diffstat (limited to 'storage')
13 files changed, 1620 insertions, 20 deletions
diff --git a/storage/connect/ha_connect.cc b/storage/connect/ha_connect.cc index c2fb6481cb0..27a71bc152e 100644 --- a/storage/connect/ha_connect.cc +++ b/storage/connect/ha_connect.cc @@ -4020,7 +4020,27 @@ 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); +#ifdef NO_EMBEDDED_ACCESS_CHECKS + return false; +#endif + /* + If table or table->mdl_ticket is NULL - it's a DLL, e.g. CREATE TABLE. + if the table has an MDL_EXCLUSIVE lock - it's a DDL too, e.g. the + insert step of CREATE ... SELECT. + + Otherwise it's a DML, the table was normally opened, locked, + privilege were already checked, and table->grant.privilege is set. + With SQL SECURITY DEFINER, table->grant.privilege has definer's privileges. + */ + if (!table || !table->mdl_ticket || table->mdl_ticket->get_type() == MDL_EXCLUSIVE) + return check_access(thd, FILE_ACL, db, NULL, NULL, 0, 0); + if (table->grant.privilege & FILE_ACL) + return false; + status_var_increment(thd->status_var.access_denied_errors); + my_error(access_denied_error_code(thd->password), MYF(0), + thd->security_ctx->priv_user, thd->security_ctx->priv_host, + (thd->password ? ER(ER_YES) : ER(ER_NO))); + return true; // This is temporary until a solution is found case TAB_TBL: @@ -6159,10 +6179,6 @@ 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)) - return true; - #if defined(__WIN__) s= "\\"; #else // !__WIN__ diff --git a/storage/connect/mysql-test/connect/r/grant.result b/storage/connect/mysql-test/connect/r/grant.result index ba5728703a5..9a0a549fd1f 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 00000000000..f4a78f9b891 --- /dev/null +++ b/storage/connect/mysql-test/connect/r/grant2.result @@ -0,0 +1,690 @@ +# +# 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; +CREATE DEFINER=user@localhost SQL SECURITY DEFINER VIEW v1_baddefiner AS SELECT * FROM t1; +SELECT * FROM t1; +a +10 +SELECT * FROM v1_invoker; +a +10 +SELECT * FROM v1_definer; +a +10 +SELECT * FROM v1_baddefiner; +ERROR 28000: Access denied for user 'root'@'localhost' (using password: NO) +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, v1_baddefiner; +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; +# Testing stored procedures +CREATE PROCEDURE p_definer() SQL SECURITY DEFINER +CREATE TABLE t1 (a INT) ENGINE=CONNECT TABLE_TYPE=fix FILE_NAME='t1.fix'; +CREATE PROCEDURE p_invoker() SQL SECURITY INVOKER +CREATE TABLE t1 (a INT) ENGINE=CONNECT TABLE_TYPE=fix FILE_NAME='t1.fix'; +CREATE DEFINER=user@localhost PROCEDURE p_baddefiner() SQL SECURITY DEFINER +CREATE TABLE t1 (a INT) ENGINE=CONNECT TABLE_TYPE=fix FILE_NAME='t1.fix'; +CALL p_definer(); +DROP TABLE t1; +CALL p_invoker(); +DROP TABLE t1; +CALL p_baddefiner(); +ERROR 28000: Access denied for user 'user'@'localhost' (using password: NO) +CALL p_invoker(); +ERROR 28000: Access denied for user 'user'@'localhost' (using password: NO) +CALL p_definer(); +DROP TABLE t1; +DROP PROCEDURE p_definer; +DROP PROCEDURE p_invoker; +DROP PROCEDURE p_baddefiner; +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 c3acf7c8dfc..190114254b0 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 f8d0ee6ad6f..554e6f47ba1 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 364f340eddf..06b4239bd69 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 ea38e57af86..f6dc725b259 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 7bb214dc9fd..8f605a7c1d3 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 909bb4117a1..31a596ce078 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 00000000000..4d136ec5f5d --- /dev/null +++ b/storage/connect/mysql-test/connect/t/grant2.test @@ -0,0 +1,868 @@ +-- 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; + +--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; +CREATE DEFINER=user@localhost SQL SECURITY DEFINER VIEW v1_baddefiner AS SELECT * FROM t1; +SELECT * FROM t1; +SELECT * FROM v1_invoker; +SELECT * FROM v1_definer; +--error ER_ACCESS_DENIED_ERROR +SELECT * FROM v1_baddefiner; + +--connect(user,localhost,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, v1_baddefiner; +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 + +--echo # Testing stored procedures +CREATE PROCEDURE p_definer() SQL SECURITY DEFINER + CREATE TABLE t1 (a INT) ENGINE=CONNECT TABLE_TYPE=fix FILE_NAME='t1.fix'; +CREATE PROCEDURE p_invoker() SQL SECURITY INVOKER + CREATE TABLE t1 (a INT) ENGINE=CONNECT TABLE_TYPE=fix FILE_NAME='t1.fix'; +CREATE DEFINER=user@localhost PROCEDURE p_baddefiner() SQL SECURITY DEFINER + CREATE TABLE t1 (a INT) ENGINE=CONNECT TABLE_TYPE=fix FILE_NAME='t1.fix'; + +CALL p_definer(); +DROP TABLE t1; +CALL p_invoker(); +DROP TABLE t1; +--error ER_ACCESS_DENIED_ERROR +CALL p_baddefiner(); + +--connection user +--error ER_ACCESS_DENIED_ERROR +CALL p_invoker(); +CALL p_definer(); + +--connection default +DROP TABLE t1; +DROP PROCEDURE p_definer; +DROP PROCEDURE p_invoker; +DROP PROCEDURE p_baddefiner; + +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 30678645692..ebc7a80b8f7 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 7c75103ed3b..05337efb3f2 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 7664a4473ba..887385af2dc 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(); |