#
# MDEV-7574 Security definer views don't work with CONNECT ODBC tables
#
CREATE USER user@localhost;
GRANT ALL PRIVILEGES ON *.* TO user@localhost;
REVOKE FILE ON *.* FROM user@localhost;
# 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;
a 10
SELECT * FROM v1_invoker;
a 10
SELECT * FROM v1_definer;
a 10
SELECT * FROM v1_baddefiner; ERROR42000: Access denied; you need (at least one of) the FILE privilege(s) for this operation
connect user,localhost,user,,;
SELECT * FROM t1; ERROR42000: Access denied; you need (at least one of) the FILE privilege(s) for this operation
SELECT * FROM v1_invoker; ERROR42000: Access denied; you need (at least one of) the FILE privilege(s) for this operation
SELECT * FROM v1_definer;
a 10
connection default;
DROP VIEW v1_invoker, v1_definer, v1_baddefiner;
DROP TABLE t1;
# 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;
UPDATE t1 SET a=21; ERROR42000: Access denied; you need (at least one of) the FILE privilege(s) for this operation
UPDATE v1_invoker SET a=22; ERROR42000: Access denied; you need (at least one of) the FILE privilege(s) for this operation
UPDATE v1_definer SET a=23;
connection default;
DROP VIEW v1_invoker, v1_definer;
DROP TABLE t1;
# 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;
INSERT INTO t1 VALUES (21); ERROR42000: Access denied; you need (at least one of) the FILE privilege(s) for this operation
INSERT INTO v1_invoker VALUES (22); ERROR42000: Access denied; you need (at least one of) the FILE privilege(s) for this operation
INSERT INTO v1_definer VALUES (23);
connection default;
DROP VIEW v1_invoker, v1_definer;
DROP TABLE t1;
# Testing SQLCOM_REPLACE
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; REPLACE INTO t1 VALUES (11); ERROR42000: CONNECT Unsupported command REPLACE INTO v1_invoker VALUES (12); ERROR42000: CONNECT Unsupported command REPLACE INTO v1_definer VALUES (13); ERROR42000: CONNECT Unsupported command
connection user; REPLACE INTO t1 VALUES (21); ERROR42000: Access denied; you need (at least one of) the FILE privilege(s) for this operation REPLACE INTO v1_invoker VALUES (22); ERROR42000: Access denied; you need (at least one of) the FILE privilege(s) for this operation REPLACE INTO v1_definer VALUES (23); ERROR42000: CONNECT Unsupported command
connection default;
DROP VIEW v1_invoker, v1_definer;
DROP TABLE t1;
# 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;
DELETE FROM t1 WHERE a=21; ERROR42000: Access denied; you need (at least one of) the FILE privilege(s) for this operation
DELETE FROM v1_invoker WHERE a=22; ERROR42000: Access denied; you need (at least one of) the FILE privilege(s) for this operation
DELETE FROM v1_definer WHERE a=23;
connection default;
DROP VIEW v1_invoker, v1_definer;
DROP TABLE t1;
# 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;
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;
connection user;
LOAD DATA LOCAL INFILE 'MTR_SUITE_DIR/std_data/boys.txt' INTO TABLE t1; ERROR42000: Access denied; you need (at least one of) the FILE privilege(s) for this operation
LOAD DATA LOCAL INFILE 'MTR_SUITE_DIR/std_data/boys.txt' INTO TABLE v1_invoker; ERROR42000: Access denied; you need (at least one of) the FILE privilege(s) for this operation
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;
# 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;
TRUNCATE TABLE t1; ERROR42000: Access denied; you need (at least one of) the FILE privilege(s) for this operation
connection default;
DROP TABLE t1;
# 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;
DROP TABLE t1; ERROR42000: Access denied; you need (at least one of) the FILE privilege(s) for this operation
connection default;
DROP TABLE t1;
# Testing SQLCOM_DROP_VIEW
# 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;
# Testing SQLCOM_CREATE_TABLE
connection user;
CREATE TABLE t1 (a INT) ENGINE=CONNECT TABLE_TYPE=fix FILE_NAME='t1.fix'; ERROR42000: Access denied; you need (at least one of) the FILE privilege(s) for this operation
connection default;
# 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;
LOCK TABLE t1 READ; ERROR42000: Access denied; you need (at least one of) the FILE privilege(s) for this operation
LOCK TABLE t1 WRITE; ERROR42000: Access denied; you need (at least one of) the FILE privilege(s) for this operation
LOCK TABLE v1_invoker READ; ERROR42000: Access denied; you need (at least one of) the FILE privilege(s) for this operation
LOCK TABLE v1_invoker WRITE; ERROR42000: Access denied; you need (at least one of) the FILE privilege(s) for this operation
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;
# Testing SQLCOM_UPDATE_MULTI
connection default;
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;
UPDATE t1 a1,t1 a2 SET a1.a=50 WHERE a1.a=a2.a; ERROR42000: Access denied; you need (at least one of) the FILE privilege(s) for this operation
UPDATE t1 a1,t2 a2 SET a1.a=50 WHERE a1.a=a2.a; ERROR42000: Access denied; you need (at least one of) the FILE privilege(s) for this operation
UPDATE t1 a1,t3 a2 SET a1.a=50 WHERE a1.a=a2.a; ERROR42000: Access denied; you need (at least one of) the FILE privilege(s) for this operation
UPDATE t1 a1,v1_invoker a2 SET a1.a=50 WHERE a1.a=a2.a; ERROR42000: Access denied; you need (at least one of) the FILE privilege(s) for this operation
UPDATE t1 a1,v1_definer a2 SET a1.a=50 WHERE a1.a=a2.a; ERROR42000: Access denied; you need (at least one of) the FILE privilege(s) for this operation
UPDATE t1 a1,v2_invoker a2 SET a1.a=50 WHERE a1.a=a2.a; ERROR42000: Access denied; you need (at least one of) the FILE privilege(s) for this operation
UPDATE t1 a1,v2_definer a2 SET a1.a=50 WHERE a1.a=a2.a; ERROR42000: Access denied; you need (at least one of) the FILE privilege(s) for this operation
UPDATE t2 a1,t1 a2 SET a1.a=50 WHERE a1.a=a2.a; ERROR42000: Access denied; you need (at least one of) the FILE privilege(s) for this operation
UPDATE t2 a1,t2 a2 SET a1.a=50 WHERE a1.a=a2.a; ERROR42000: Access denied; you need (at least one of) the FILE privilege(s) for this operation
UPDATE t2 a1,t3 a2 SET a1.a=50 WHERE a1.a=a2.a; ERROR42000: Access denied; you need (at least one of) the FILE privilege(s) for this operation
UPDATE t2 a1,v1_invoker a2 SET a1.a=50 WHERE a1.a=a2.a; ERROR42000: Access denied; you need (at least one of) the FILE privilege(s) for this operation
UPDATE t2 a1,v1_definer a2 SET a1.a=50 WHERE a1.a=a2.a; ERROR42000: Access denied; you need (at least one of) the FILE privilege(s) for this operation
UPDATE t2 a1,v2_invoker a2 SET a1.a=50 WHERE a1.a=a2.a; ERROR42000: Access denied; you need (at least one of) the FILE privilege(s) for this operation
UPDATE t2 a1,v2_definer a2 SET a1.a=50 WHERE a1.a=a2.a; ERROR42000: Access denied; you need (at least one of) the FILE privilege(s) for this operation
UPDATE t3 a1,t1 a2 SET a1.a=50 WHERE a1.a=a2.a; ERROR42000: Access denied; you need (at least one of) the FILE privilege(s) for this operation
UPDATE t3 a1,t2 a2 SET a1.a=50 WHERE a1.a=a2.a; ERROR42000: Access denied; you need (at least one of) the FILE privilege(s) for this operation
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; ERROR42000: Access denied; you need (at least one of) the FILE privilege(s) for this operation
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; ERROR42000: Access denied; you need (at least one of) the FILE privilege(s) for this operation
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; ERROR42000: Access denied; you need (at least one of) the FILE privilege(s) for this operation
UPDATE v1_invoker a1,t2 a2 SET a1.a=50 WHERE a1.a=a2.a; ERROR42000: Access denied; you need (at least one of) the FILE privilege(s) for this operation
UPDATE v1_invoker a1,t3 a2 SET a1.a=50 WHERE a1.a=a2.a; ERROR42000: Access denied; you need (at least one of) the FILE privilege(s) for this operation
UPDATE v1_invoker a1,v1_invoker a2 SET a1.a=50 WHERE a1.a=a2.a; ERROR42000: Access denied; you need (at least one of) the FILE privilege(s) for this operation
UPDATE v1_invoker a1,v1_definer a2 SET a1.a=50 WHERE a1.a=a2.a; ERROR42000: Access denied; you need (at least one of) the FILE privilege(s) for this operation
UPDATE v1_invoker a1,v2_invoker a2 SET a1.a=50 WHERE a1.a=a2.a; ERROR42000: Access denied; you need (at least one of) the FILE privilege(s) for this operation
UPDATE v1_invoker a1,v2_definer a2 SET a1.a=50 WHERE a1.a=a2.a; ERROR42000: Access denied; you need (at least one of) the FILE privilege(s) for this operation
UPDATE v1_definer a1,t1 a2 SET a1.a=50 WHERE a1.a=a2.a; ERROR42000: Access denied; you need (at least one of) the FILE privilege(s) for this operation
UPDATE v1_definer a1,t2 a2 SET a1.a=50 WHERE a1.a=a2.a; ERROR42000: Access denied; you need (at least one of) the FILE privilege(s) for this operation
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; ERROR42000: Access denied; you need (at least one of) the FILE privilege(s) for this operation
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; ERROR42000: Access denied; you need (at least one of) the FILE privilege(s) for this operation
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; ERROR42000: Access denied; you need (at least one of) the FILE privilege(s) for this operation
UPDATE v2_invoker a1,t2 a2 SET a1.a=50 WHERE a1.a=a2.a; ERROR42000: Access denied; you need (at least one of) the FILE privilege(s) for this operation
UPDATE v2_invoker a1,t3 a2 SET a1.a=50 WHERE a1.a=a2.a; ERROR42000: Access denied; you need (at least one of) the FILE privilege(s) for this operation
UPDATE v2_invoker a1,v1_invoker a2 SET a1.a=50 WHERE a1.a=a2.a; ERROR42000: Access denied; you need (at least one of) the FILE privilege(s) for this operation
UPDATE v2_invoker a1,v1_definer a2 SET a1.a=50 WHERE a1.a=a2.a; ERROR42000: Access denied; you need (at least one of) the FILE privilege(s) for this operation
UPDATE v2_invoker a1,v2_invoker a2 SET a1.a=50 WHERE a1.a=a2.a; ERROR42000: Access denied; you need (at least one of) the FILE privilege(s) for this operation
UPDATE v2_invoker a1,v2_definer a2 SET a1.a=50 WHERE a1.a=a2.a; ERROR42000: Access denied; you need (at least one of) the FILE privilege(s) for this operation
UPDATE v2_definer a1,t1 a2 SET a1.a=50 WHERE a1.a=a2.a; ERROR42000: Access denied; you need (at least one of) the FILE privilege(s) for this operation
UPDATE v2_definer a1,t2 a2 SET a1.a=50 WHERE a1.a=a2.a; ERROR42000: Access denied; you need (at least one of) the FILE privilege(s) for this operation
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; ERROR42000: Access denied; you need (at least one of) the FILE privilege(s) for this operation
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; ERROR42000: Access denied; you need (at least one of) the FILE privilege(s) for this operation
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;
# Testing SQLCOM_DELETE_MULTI
connection default;
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;
DELETE a1 FROM t1 a1,t1 a2 WHERE a1.a=a2.a; ERROR42000: Access denied; you need (at least one of) the FILE privilege(s) for this operation
DELETE a1 FROM t1 a1,t2 a2 WHERE a1.a=a2.a; ERROR42000: Access denied; you need (at least one of) the FILE privilege(s) for this operation
DELETE a1 FROM t1 a1,t3 a2 WHERE a1.a=a2.a; ERROR42000: Access denied; you need (at least one of) the FILE privilege(s) for this operation
DELETE a1 FROM t1 a1,v1_invoker a2 WHERE a1.a=a2.a; ERROR42000: Access denied; you need (at least one of) the FILE privilege(s) for this operation
DELETE a1 FROM t1 a1,v1_definer a2 WHERE a1.a=a2.a; ERROR42000: Access denied; you need (at least one of) the FILE privilege(s) for this operation
DELETE a1 FROM t1 a1,v2_invoker a2 WHERE a1.a=a2.a; ERROR42000: Access denied; you need (at least one of) the FILE privilege(s) for this operation
DELETE a1 FROM t1 a1,v2_definer a2 WHERE a1.a=a2.a; ERROR42000: Access denied; you need (at least one of) the FILE privilege(s) for this operation
DELETE a1 FROM t2 a1,t1 a2 WHERE a1.a=a2.a; ERROR42000: Access denied; you need (at least one of) the FILE privilege(s) for this operation
DELETE a1 FROM t2 a1,t2 a2 WHERE a1.a=a2.a; ERROR42000: Access denied; you need (at least one of) the FILE privilege(s) for this operation
DELETE a1 FROM t2 a1,t3 a2 WHERE a1.a=a2.a; ERROR42000: Access denied; you need (at least one of) the FILE privilege(s) for this operation
DELETE a1 FROM t2 a1,v1_invoker a2 WHERE a1.a=a2.a; ERROR42000: Access denied; you need (at least one of) the FILE privilege(s) for this operation
DELETE a1 FROM t2 a1,v1_definer a2 WHERE a1.a=a2.a; ERROR42000: Access denied; you need (at least one of) the FILE privilege(s) for this operation
DELETE a1 FROM t2 a1,v2_invoker a2 WHERE a1.a=a2.a; ERROR42000: Access denied; you need (at least one of) the FILE privilege(s) for this operation
DELETE a1 FROM t2 a1,v2_definer a2 WHERE a1.a=a2.a; ERROR42000: Access denied; you need (at least one of) the FILE privilege(s) for this operation
DELETE a1 FROM t3 a1,t1 a2 WHERE a1.a=a2.a; ERROR42000: Access denied; you need (at least one of) the FILE privilege(s) for this operation
DELETE a1 FROM t3 a1,t2 a2 WHERE a1.a=a2.a; ERROR42000: Access denied; you need (at least one of) the FILE privilege(s) for this operation
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; ERROR42000: Access denied; you need (at least one of) the FILE privilege(s) for this operation
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; ERROR42000: Access denied; you need (at least one of) the FILE privilege(s) for this operation
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; ERROR42000: Access denied; you need (at least one of) the FILE privilege(s) for this operation
DELETE a1 FROM v1_invoker a1,t2 a2 WHERE a1.a=a2.a; ERROR42000: Access denied; you need (at least one of) the FILE privilege(s) for this operation
DELETE a1 FROM v1_invoker a1,t3 a2 WHERE a1.a=a2.a; ERROR42000: Access denied; you need (at least one of) the FILE privilege(s) for this operation
DELETE a1 FROM v1_invoker a1,v1_invoker a2 WHERE a1.a=a2.a; ERROR42000: Access denied; you need (at least one of) the FILE privilege(s) for this operation
DELETE a1 FROM v1_invoker a1,v1_definer a2 WHERE a1.a=a2.a; ERROR42000: Access denied; you need (at least one of) the FILE privilege(s) for this operation
DELETE a1 FROM v1_invoker a1,v2_invoker a2 WHERE a1.a=a2.a; ERROR42000: Access denied; you need (at least one of) the FILE privilege(s) for this operation
DELETE a1 FROM v1_invoker a1,v2_definer a2 WHERE a1.a=a2.a; ERROR42000: Access denied; you need (at least one of) the FILE privilege(s) for this operation
DELETE a1 FROM v1_definer a1,t1 a2 WHERE a1.a=a2.a; ERROR42000: Access denied; you need (at least one of) the FILE privilege(s) for this operation
DELETE a1 FROM v1_definer a1,t2 a2 WHERE a1.a=a2.a; ERROR42000: Access denied; you need (at least one of) the FILE privilege(s) for this operation
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; ERROR42000: Access denied; you need (at least one of) the FILE privilege(s) for this operation
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; ERROR42000: Access denied; you need (at least one of) the FILE privilege(s) for this operation
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; ERROR42000: Access denied; you need (at least one of) the FILE privilege(s) for this operation
DELETE a1 FROM v2_invoker a1,t2 a2 WHERE a1.a=a2.a; ERROR42000: Access denied; you need (at least one of) the FILE privilege(s) for this operation
DELETE a1 FROM v2_invoker a1,t3 a2 WHERE a1.a=a2.a; ERROR42000: Access denied; you need (at least one of) the FILE privilege(s) for this operation
DELETE a1 FROM v2_invoker a1,v1_invoker a2 WHERE a1.a=a2.a; ERROR42000: Access denied; you need (at least one of) the FILE privilege(s) for this operation
DELETE a1 FROM v2_invoker a1,v1_definer a2 WHERE a1.a=a2.a; ERROR42000: Access denied; you need (at least one of) the FILE privilege(s) for this operation
DELETE a1 FROM v2_invoker a1,v2_invoker a2 WHERE a1.a=a2.a; ERROR42000: Access denied; you need (at least one of) the FILE privilege(s) for this operation
DELETE a1 FROM v2_invoker a1,v2_definer a2 WHERE a1.a=a2.a; ERROR42000: Access denied; you need (at least one of) the FILE privilege(s) for this operation
DELETE a1 FROM v2_definer a1,t1 a2 WHERE a1.a=a2.a; ERROR42000: Access denied; you need (at least one of) the FILE privilege(s) for this operation
DELETE a1 FROM v2_definer a1,t2 a2 WHERE a1.a=a2.a; ERROR42000: Access denied; you need (at least one of) the FILE privilege(s) for this operation
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; ERROR42000: Access denied; you need (at least one of) the FILE privilege(s) for this operation
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; ERROR42000: Access denied; you need (at least one of) the FILE privilege(s) for this operation
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;
# 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;
CREATE VIEW v2 AS SELECT * FROM t1; ERROR42000: Access denied; you need (at least one of) the FILE privilege(s) for this operation
CREATE VIEW v2 AS SELECT * FROM v1_invoker; ERROR42000: Access denied; you need (at least one of) the FILE privilege(s) for this operation
CREATE VIEW v2 AS SELECT * FROM v1_definer;
DROP VIEW v2;
connection default;
DROP VIEW v1_invoker, v1_definer;
DROP TABLE t1;
# 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;
INSERT INTO t1 SELECT * FROM t1 WHERE a=20; ERROR42000: Access denied; you need (at least one of) the FILE privilege(s) for this operation
INSERT INTO t1 SELECT * FROM v1_invoker WHERE a=20; ERROR42000: Access denied; you need (at least one of) the FILE privilege(s) for this operation
INSERT INTO t1 SELECT * FROM v1_definer WHERE a=20; ERROR42000: Access denied; you need (at least one of) the FILE privilege(s) for this operation
INSERT INTO v1_invoker SELECT * FROM t1 WHERE a=20; ERROR42000: Access denied; you need (at least one of) the FILE privilege(s) for this operation
INSERT INTO v1_invoker SELECT * FROM v1_invoker WHERE a=20; ERROR42000: Access denied; you need (at least one of) the FILE privilege(s) for this operation
INSERT INTO v1_invoker SELECT * FROM v1_definer WHERE a=20; ERROR42000: Access denied; you need (at least one of) the FILE privilege(s) for this operation
INSERT INTO v1_definer SELECT * FROM t1 WHERE a=20; ERROR42000: Access denied; you need (at least one of) the FILE privilege(s) for this operation
INSERT INTO v1_definer SELECT * FROM v1_invoker WHERE a=20; ERROR42000: Access denied; you need (at least one of) the FILE privilege(s) for this operation
INSERT INTO v1_definer SELECT * FROM v1_definer WHERE a=20;
connection default;
DROP VIEW v1_invoker, v1_definer;
DROP TABLE t1;
# Testing SQLCOM_REPLACE_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; REPLACE INTO t1 SELECT * FROM t1 WHERE a=20; ERROR42000: CONNECT Unsupported command REPLACE INTO t1 SELECT * FROM v1_invoker WHERE a=20; ERROR42000: CONNECT Unsupported command REPLACE INTO t1 SELECT * FROM v1_definer WHERE a=20; ERROR42000: CONNECT Unsupported command REPLACE INTO v1_invoker SELECT * FROM t1 WHERE a=20; ERROR42000: CONNECT Unsupported command REPLACE INTO v1_invoker SELECT * FROM v1_invoker WHERE a=20; ERROR42000: CONNECT Unsupported command REPLACE INTO v1_invoker SELECT * FROM v1_definer WHERE a=20; ERROR42000: CONNECT Unsupported command REPLACE INTO v1_definer SELECT * FROM t1 WHERE a=20; ERROR42000: CONNECT Unsupported command REPLACE INTO v1_definer SELECT * FROM v1_invoker WHERE a=20; ERROR42000: CONNECT Unsupported command REPLACE INTO v1_definer SELECT * FROM v1_definer WHERE a=20; ERROR42000: CONNECT Unsupported command
connection user; REPLACE INTO t1 SELECT * FROM t1 WHERE a=20; ERROR42000: Access denied; you need (at least one of) the FILE privilege(s) for this operation REPLACE INTO t1 SELECT * FROM v1_invoker WHERE a=20; ERROR42000: Access denied; you need (at least one of) the FILE privilege(s) for this operation REPLACE INTO t1 SELECT * FROM v1_definer WHERE a=20; ERROR42000: Access denied; you need (at least one of) the FILE privilege(s) for this operation REPLACE INTO v1_invoker SELECT * FROM t1 WHERE a=20; ERROR42000: Access denied; you need (at least one of) the FILE privilege(s) for this operation REPLACE INTO v1_invoker SELECT * FROM v1_invoker WHERE a=20; ERROR42000: Access denied; you need (at least one of) the FILE privilege(s) for this operation REPLACE INTO v1_invoker SELECT * FROM v1_definer WHERE a=20; ERROR42000: Access denied; you need (at least one of) the FILE privilege(s) for this operation REPLACE INTO v1_definer SELECT * FROM t1 WHERE a=20; ERROR42000: CONNECT Unsupported command REPLACE INTO v1_definer SELECT * FROM v1_invoker WHERE a=20; ERROR42000: CONNECT Unsupported command REPLACE INTO v1_definer SELECT * FROM v1_definer WHERE a=20; ERROR42000: CONNECT Unsupported command
connection default;
DROP VIEW v1_invoker, v1_definer;
DROP TABLE t1;
# 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;
Table Create Table
t2 CREATE TABLE `t2` (
`a` int(11) DEFAULT NULL
) ENGINE=CONNECT DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci `TABLE_TYPE`='fix' `FILE_NAME`='t1.fix'
RENAME TABLE t2 TO t1;
connection user;
RENAME TABLE t1 TO t2; ERROR42000: Access denied; you need (at least one of) the FILE privilege(s) for this operation
connection default;
DROP TABLE t1;
# 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;
Table Create Table
t2 CREATE TABLE `t2` (
`a` int(11) DEFAULT NULL
) ENGINE=CONNECT DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci `TABLE_TYPE`='fix' `FILE_NAME`='t1.fix'
ALTER TABLE t2 RENAME TO t1;
connection user;
ALTER TABLE t1 RENAME TO t2; ERROR42000: Access denied; you need (at least one of) the FILE privilege(s) for this operation
connection default;
DROP TABLE t1;
# 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;
CREATE TABLE t1 (a INT) ENGINE=CONNECT TABLE_TYPE=fix FILE_NAME='t1.fix';
INSERT INTO t1 VALUES (10);
connection user;
ALTER TABLE t1 ENGINE=MyISAM; ERROR42000: Access denied; you need (at least one of) the FILE privilege(s) for this operation
connection default;
DROP TABLE t1;
# 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;
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);
connection user;
ALTER TABLE t1 ENGINE=CONNECT TABLE_TYPE=fix FILE_NAME='t1.fix'; ERROR42000: Access denied; you need (at least one of) the FILE privilege(s) for this operation
connection default;
DROP TABLE t1;
# 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;
Table Op Msg_type Msg_text
test.t1 optimize status OK
connection user;
OPTIMIZE TABLE t1;
Table Op Msg_type Msg_text
test.t1 optimize Error Access denied; you need (at least one of) the FILE privilege(s) for this operation
test.t1 optimize Error Got error122'This operation requires the FILE privilege' from CONNECT
test.t1 optimize error Corrupt
connection default;
DROP TABLE t1;
# 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;
Warnings:
Warning 1105 This is an outward table, table data were not modified.
connection user;
ALTER TABLE t1 ADD c INT; ERROR42000: Access denied; you need (at least one of) the FILE privilege(s) for this operation
connection default;
DROP TABLE t1;
# 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;
Warnings:
Warning 1105 This is an outward table, table data were not modified.
connection user;
ALTER TABLE t1 DROP c; ERROR42000: Access denied; you need (at least one of) the FILE privilege(s) for this operation
connection default;
DROP TABLE t1;
# 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;
ALTER TABLE t1 ADD KEY(b); ERROR42000: Access denied; you need (at least one of) the FILE privilege(s) for this operation
connection default;
DROP TABLE t1;
# 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;
ALTER TABLE t1 DROP KEY b; ERROR42000: Access denied; you need (at least one of) the FILE privilege(s) for this operation
connection default;
DROP TABLE t1;
# 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;
CREATE INDEX b ON t1 (b); ERROR42000: Access denied; you need (at least one of) the FILE privilege(s) for this operation
DROP INDEX a ON t1; ERROR42000: Access denied; you need (at least one of) the FILE privilege(s) for this operation
connection default;
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(); ERROR42000: Access denied; you need (at least one of) the FILE privilege(s) for this operation
connection user;
CALL p_invoker(); ERROR42000: Access denied; you need (at least one of) the FILE privilege(s) for this operation
CALL p_definer();
disconnect user;
connection default;
DROP TABLE t1;
DROP PROCEDURE p_definer;
DROP PROCEDURE p_invoker;
DROP PROCEDURE p_baddefiner;
DROP USER user@localhost;
Messung V0.5 in Prozent
¤ Dauer der Verarbeitung: 0.15 Sekunden
(vorverarbeitet am 2026-10-08)
¤
Die Informationen auf dieser Webseite wurden
nach bestem Wissen sorgfältig zusammengestellt. Es wird jedoch weder Vollständigkeit, noch Richtigkeit,
noch Qualität der bereit gestellten Informationen zugesichert.
Bemerkung:
Die farbliche Syntaxdarstellung und die Messung sind noch experimentell.
