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/connect/mysql-test | |
| 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/connect/mysql-test')
12 files changed, 1599 insertions, 15 deletions
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(); |