diff options
Diffstat (limited to 'storage/connect/mysql-test/connect/r')
8 files changed, 933 insertions, 10 deletions
diff --git a/storage/connect/mysql-test/connect/r/endian.result b/storage/connect/mysql-test/connect/r/endian.result new file mode 100644 index 00000000000..a4c81e43b6b --- /dev/null +++ b/storage/connect/mysql-test/connect/r/endian.result @@ -0,0 +1,105 @@ +SET time_zone='+00:00'; +# +# Testing little endian table +# +CREATE TABLE t1 +( +fig INT(4) NOT NULL FIELD_FORMAT='C', +name CHAR(10) NOT NULL, +birth DATE NOT NULL FIELD_FORMAT='L', +id CHAR(5) NOT NULL FIELD_FORMAT='L2', +salary DOUBLE(9,2) NOT NULL DEFAULT 0.00 FIELD_FORMAT='LF', +dept INT(4) NOT NULL FIELD_FORMAT='L2' +) ENGINE=CONNECT TABLE_TYPE=BIN BLOCK_SIZE=5 FILE_NAME='Testbal.dat'; +SELECT * FROM t1; +fig name birth id salary dept +5500 ARCHIBALD 1980-01-25 3789 4380.50 318 +123 OLIVER 1953-08-10 23456 3400.68 2158 +3123 FOO 2002-07-23 888 0.00 318 +INSERT INTO t1 VALUES (5555,'RONALD','1980-02-26','3333',4444.44,555); +SELECT * FROM t1; +fig name birth id salary dept +5500 ARCHIBALD 1980-01-25 3789 4380.50 318 +123 OLIVER 1953-08-10 23456 3400.68 2158 +3123 FOO 2002-07-23 888 0.00 318 +5555 RONALD 1980-02-26 3333 4444.44 555 +DROP TABLE t1; +CREATE TABLE t1 +( +fig INT(4) NOT NULL FIELD_FORMAT='C', +name CHAR(10) NOT NULL, +birth DATE NOT NULL, +id CHAR(5) NOT NULL FIELD_FORMAT='S', +salary DOUBLE(9,2) NOT NULL DEFAULT 0.00 FIELD_FORMAT='F', +dept INT(4) NOT NULL FIELD_FORMAT='S' +) ENGINE=CONNECT TABLE_TYPE=BIN BLOCK_SIZE=5 FILE_NAME='Testbal.dat' OPTION_LIST='Endian=Little'; +SELECT * FROM t1; +fig name birth id salary dept +5500 ARCHIBALD 1980-01-25 3789 4380.50 318 +123 OLIVER 1953-08-10 23456 3400.68 2158 +3123 FOO 2002-07-23 888 0.00 318 +5555 RONALD 1980-02-26 3333 4444.44 555 +# +# Testing big endian table +# +CREATE TABLE t2 ( +fig INT(4) NOT NULL FIELD_FORMAT='C', +name CHAR(10) NOT NULL, +birth DATE NOT NULL FIELD_FORMAT='B', +id CHAR(5) NOT NULL FIELD_FORMAT='BS', +salary DOUBLE(9,2) NOT NULL DEFAULT 0.00 FIELD_FORMAT='BF', +dept INT(4) NOT NULL FIELD_FORMAT='B2' +) ENGINE=CONNECT TABLE_TYPE=BIN BLOCK_SIZE=5 FILE_NAME='Testbal.bin'; +INSERT INTO t2 SELECT * FROM t1; +SELECT * FROM t2; +fig name birth id salary dept +5500 ARCHIBALD 1980-01-25 3789 4380.50 318 +123 OLIVER 1953-08-10 23456 3400.68 2158 +3123 FOO 2002-07-23 888 0.00 318 +5555 RONALD 1980-02-26 3333 4444.44 555 +DROP TABLE t2; +CREATE TABLE t2 ( +fig INT(4) NOT NULL FIELD_FORMAT='C', +name CHAR(10) NOT NULL, +birth DATE NOT NULL, +id CHAR(5) NOT NULL FIELD_FORMAT='S', +salary DOUBLE(9,2) NOT NULL DEFAULT 0.00 FIELD_FORMAT='F', +dept INT(4) NOT NULL FIELD_FORMAT='2' +) ENGINE=CONNECT TABLE_TYPE=BIN BLOCK_SIZE=5 FILE_NAME='Testbal.bin' OPTION_LIST='Endian=Big'; +SELECT * FROM t2; +fig name birth id salary dept +5500 ARCHIBALD 1980-01-25 3789 4380.50 318 +123 OLIVER 1953-08-10 23456 3400.68 2158 +3123 FOO 2002-07-23 888 0.00 318 +5555 RONALD 1980-02-26 3333 4444.44 555 +DROP TABLE t2; +CREATE TABLE t2 ( +fig CHAR(4) NOT NULL, +name CHAR(10) NOT NULL, +birth DATE NOT NULL, +id SMALLINT(5) NOT NULL, +salary DOUBLE(9,2) NOT NULL DEFAULT 0.00 FIELD_FORMAT='F', +dept SMALLINT(4) NOT NULL +) ENGINE=CONNECT TABLE_TYPE=BIN BLOCK_SIZE=5 FILE_NAME='Testbal.bin' OPTION_LIST='Endian=Big'; +SELECT * FROM t2; +fig name birth id salary dept +5500 ARCHIBALD 1980-01-25 3789 4380.50 318 +123 OLIVER 1953-08-10 23456 3400.68 2158 +3123 FOO 2002-07-23 888 0.00 318 +5555 RONALD 1980-02-26 3333 4444.44 555 +DROP TABLE t2; +CREATE TABLE t2 ( +fig INT(4) NOT NULL FIELD_FORMAT='C', +name CHAR(10) NOT NULL, +birth DATE NOT NULL FIELD_FORMAT='B', +id CHAR(5) NOT NULL FIELD_FORMAT='BS', +salary DOUBLE(9,2) NOT NULL DEFAULT 0.00 FIELD_FORMAT='BF', +dept SMALLINT(4) NOT NULL FIELD_FORMAT='B' +) ENGINE=CONNECT TABLE_TYPE=BIN BLOCK_SIZE=5 FILE_NAME='Testbal.bin'; +SELECT * FROM t2; +fig name birth id salary dept +5500 ARCHIBALD 1980-01-25 3789 4380.50 318 +123 OLIVER 1953-08-10 23456 3400.68 2158 +3123 FOO 2002-07-23 888 0.00 318 +5555 RONALD 1980-02-26 3333 4444.44 555 +DROP TABLE t1, t2; 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/mysql_index.result b/storage/connect/mysql-test/connect/r/mysql_index.result new file mode 100644 index 00000000000..4ebf10802ae --- /dev/null +++ b/storage/connect/mysql-test/connect/r/mysql_index.result @@ -0,0 +1,114 @@ +# +# Make remote table +# +CREATE TABLE t1 ( +id int(11) NOT NULL, +msg char(100) DEFAULT NULL, +PRIMARY KEY (id) +) ENGINE=MyISAM DEFAULT CHARSET=latin1; +INSERT INTO t1 VALUES(1,'Un'),(3,'Trois'),(5,'Cinq'); +INSERT INTO t1 VALUES(2,'Two'),(4,'Four'),(6,'Six'); +SELECT * FROM t1; +id msg +1 Un +3 Trois +5 Cinq +2 Two +4 Four +6 Six +# +# Make local MYSQL table with indexed id column +# +CREATE TABLE t2 ( +id int(11) NOT NULL, +msg char(100) DEFAULT NULL, +PRIMARY KEY (id) +) ENGINE=CONNECT DEFAULT CHARSET=latin1 TABLE_TYPE=MYSQL TABNAME=t1; +# +# Testing SELECT, etc. +# +SELECT * FROM t2; +id msg +1 Un +3 Trois +5 Cinq +2 Two +4 Four +6 Six +SELECT * FROM t2 WHERE id = 3; +id msg +3 Trois +SELECT * FROM t2 WHERE id IN (2,4); +id msg +2 Two +4 Four +SELECT * FROM t2 WHERE id IN (2,4) AND msg = 'Two'; +id msg +2 Two +SELECT * FROM t2 WHERE id > 3; +id msg +5 Cinq +4 Four +6 Six +SELECT * FROM t2 WHERE id >= 3; +id msg +3 Trois +5 Cinq +4 Four +6 Six +SELECT * FROM t2 WHERE id < 3; +id msg +1 Un +2 Two +SELECT * FROM t2 WHERE id < 3 OR id > 4; +id msg +1 Un +2 Two +5 Cinq +6 Six +SELECT * FROM t2 WHERE id <= 3; +id msg +1 Un +2 Two +3 Trois +SELECT * FROM t2 WHERE id BETWEEN 3 AND 5; +id msg +3 Trois +4 Four +5 Cinq +SELECT * FROM t2 WHERE id > 2 AND id < 6; +id msg +3 Trois +4 Four +5 Cinq +SELECT * FROM t2 ORDER BY id; +id msg +1 Un +2 Two +3 Trois +4 Four +5 Cinq +6 Six +UPDATE t2 SET msg = 'Five' WHERE id = 5; +Warnings: +Note 1105 t1: 1 affected rows +SELECT * FROM t2; +id msg +1 Un +3 Trois +5 Five +2 Two +4 Four +6 Six +DELETE FROM t2 WHERE id = 4; +Warnings: +Note 1105 t1: 1 affected rows +SELECT * FROM t2; +id msg +1 Un +3 Trois +5 Five +2 Two +6 Six +DROP TABLE t2; +DROP TABLE t1; 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 |