diff options
Diffstat (limited to 'mysql-test/main/analyze_stmt_privileges2.result')
| -rw-r--r-- | mysql-test/main/analyze_stmt_privileges2.result | 5237 |
1 files changed, 5237 insertions, 0 deletions
diff --git a/mysql-test/main/analyze_stmt_privileges2.result b/mysql-test/main/analyze_stmt_privileges2.result new file mode 100644 index 00000000000..cf38810b598 --- /dev/null +++ b/mysql-test/main/analyze_stmt_privileges2.result @@ -0,0 +1,5237 @@ +set GLOBAL sql_mode=""; +set LOCAL sql_mode=""; +CREATE DATABASE privtest_db; +CREATE TABLE privtest_db.t1 (a INT, b VARCHAR(3)) ENGINE=MyISAM; +CREATE TABLE privtest_db.t2 (a INT, b VARCHAR(3)) ENGINE=MyISAM; +CREATE VIEW privtest_db.v1 AS SELECT a, b FROM privtest_db.t1 WHERE b IS NOT NULL; +CREATE VIEW privtest_db.v2 AS SELECT * FROM privtest_db.v1 WHERE a > 0; +INSERT INTO privtest_db.t2 VALUES (1,'foo'), (2,'bar'), (3,'qux'); +GRANT SELECT ON privtest_db.t2 TO 'privtest'@'localhost'; +connect con1,localhost,privtest,,privtest_db; + +######################################################################### +# Underlying table permission tests +# (we modify permissions on the base table, keeping ALL on views) +######################################################################### + +connection default; +GRANT ALL ON privtest_db.v1 TO 'privtest'@'localhost'; +GRANT ALL ON privtest_db.v2 TO 'privtest'@'localhost'; +connection con1; + +#======================================================================== +# Test: No permissions on the table +#======================================================================== + +#------------------------------------------------------------------------ +# I/R/U/D/S on the table +# Expectation: Cannot run query, EXPLAIN, ANALYZE on the table +# because the query itself cannot be executed +#------------------------------------------------------------------------ +INSERT INTO t1 (a) VALUES (10); +ERROR 42000: INSERT command denied to user 'privtest'@'localhost' for table 't1' +EXPLAIN INSERT INTO t1 (a) VALUES (10); +ERROR 42000: INSERT command denied to user 'privtest'@'localhost' for table 't1' +ANALYZE INSERT INTO t1 (a) VALUES (10); +ERROR 42000: INSERT command denied to user 'privtest'@'localhost' for table 't1' +INSERT INTO t1 SELECT * FROM t2; +ERROR 42000: INSERT command denied to user 'privtest'@'localhost' for table 't1' +EXPLAIN INSERT INTO t1 SELECT * FROM t2; +ERROR 42000: INSERT command denied to user 'privtest'@'localhost' for table 't1' +ANALYZE INSERT INTO t1 SELECT * FROM t2; +ERROR 42000: INSERT command denied to user 'privtest'@'localhost' for table 't1' +REPLACE INTO t1 (a) VALUES (10); +ERROR 42000: INSERT, DELETE command denied to user 'privtest'@'localhost' for table 't1' +EXPLAIN REPLACE INTO t1 (a) VALUES (10); +ERROR 42000: INSERT, DELETE command denied to user 'privtest'@'localhost' for table 't1' +ANALYZE REPLACE INTO t1 (a) VALUES (10); +ERROR 42000: INSERT, DELETE command denied to user 'privtest'@'localhost' for table 't1' +REPLACE INTO t1 SELECT * FROM t2; +ERROR 42000: INSERT, DELETE command denied to user 'privtest'@'localhost' for table 't1' +EXPLAIN REPLACE INTO t1 SELECT * FROM t2; +ERROR 42000: INSERT, DELETE command denied to user 'privtest'@'localhost' for table 't1' +ANALYZE REPLACE INTO t1 SELECT * FROM t2; +ERROR 42000: INSERT, DELETE command denied to user 'privtest'@'localhost' for table 't1' +UPDATE t1 SET a = 10; +ERROR 42000: UPDATE command denied to user 'privtest'@'localhost' for table 't1' +EXPLAIN UPDATE t1 SET a = 10; +ERROR 42000: UPDATE command denied to user 'privtest'@'localhost' for table 't1' +ANALYZE UPDATE t1 SET a = 10; +ERROR 42000: UPDATE command denied to user 'privtest'@'localhost' for table 't1' +UPDATE t1 SET a = a + 1; +ERROR 42000: UPDATE command denied to user 'privtest'@'localhost' for table 't1' +EXPLAIN UPDATE t1 SET a = a + 1; +ERROR 42000: UPDATE command denied to user 'privtest'@'localhost' for table 't1' +ANALYZE UPDATE t1 SET a = a + 1; +ERROR 42000: UPDATE command denied to user 'privtest'@'localhost' for table 't1' +UPDATE t1, t2 SET t1.a = t1.a + 1 WHERE t1.a = t2.a; +ERROR 42000: SELECT command denied to user 'privtest'@'localhost' for table 't1' +EXPLAIN UPDATE t1, t2 SET t1.a = t1.a + 1 WHERE t1.a = t2.a; +ERROR 42000: SELECT command denied to user 'privtest'@'localhost' for table 't1' +ANALYZE UPDATE t1, t2 SET t1.a = t1.a + 1 WHERE t1.a = t2.a; +ERROR 42000: SELECT command denied to user 'privtest'@'localhost' for table 't1' +DELETE FROM t1; +ERROR 42000: DELETE command denied to user 'privtest'@'localhost' for table 't1' +EXPLAIN DELETE FROM t1; +ERROR 42000: DELETE command denied to user 'privtest'@'localhost' for table 't1' +ANALYZE DELETE FROM t1; +ERROR 42000: DELETE command denied to user 'privtest'@'localhost' for table 't1' +DELETE FROM t1 WHERE a = 10; +ERROR 42000: DELETE command denied to user 'privtest'@'localhost' for table 't1' +EXPLAIN DELETE FROM t1 WHERE a = 10; +ERROR 42000: DELETE command denied to user 'privtest'@'localhost' for table 't1' +ANALYZE DELETE FROM t1 WHERE a = 10; +ERROR 42000: DELETE command denied to user 'privtest'@'localhost' for table 't1' +DELETE FROM t1 USING t1, t2; +ERROR 42000: SELECT command denied to user 'privtest'@'localhost' for table 't1' +EXPLAIN DELETE FROM t1 USING t1, t2; +ERROR 42000: SELECT command denied to user 'privtest'@'localhost' for table 't1' +ANALYZE DELETE FROM t1 USING t1, t2; +ERROR 42000: SELECT command denied to user 'privtest'@'localhost' for table 't1' +DELETE FROM t1 USING t1, t2 WHERE t1.a = t2.a; +ERROR 42000: SELECT command denied to user 'privtest'@'localhost' for table 't1' +EXPLAIN DELETE FROM t1 USING t1, t2 WHERE t1.a = t2.a; +ERROR 42000: SELECT command denied to user 'privtest'@'localhost' for table 't1' +ANALYZE DELETE FROM t1 USING t1, t2 WHERE t1.a = t2.a; +ERROR 42000: SELECT command denied to user 'privtest'@'localhost' for table 't1' +SELECT * FROM t1; +ERROR 42000: SELECT command denied to user 'privtest'@'localhost' for table 't1' +EXPLAIN SELECT * FROM t1; +ERROR 42000: SELECT command denied to user 'privtest'@'localhost' for table 't1' +ANALYZE SELECT * FROM t1; +ERROR 42000: SELECT command denied to user 'privtest'@'localhost' for table 't1' +SELECT * FROM t1 WHERE a = 10; +ERROR 42000: SELECT command denied to user 'privtest'@'localhost' for table 't1' +EXPLAIN SELECT * FROM t1 WHERE a = 10; +ERROR 42000: SELECT command denied to user 'privtest'@'localhost' for table 't1' +ANALYZE SELECT * FROM t1 WHERE a = 10; +ERROR 42000: SELECT command denied to user 'privtest'@'localhost' for table 't1' +SELECT * FROM t1 WHERE a IN ( SELECT a FROM t2 ); +ERROR 42000: SELECT command denied to user 'privtest'@'localhost' for table 't1' +EXPLAIN SELECT * FROM t1 WHERE a IN ( SELECT a FROM t2 ); +ERROR 42000: SELECT command denied to user 'privtest'@'localhost' for table 't1' +ANALYZE SELECT * FROM t1 WHERE a IN ( SELECT a FROM t2 ); +ERROR 42000: SELECT command denied to user 'privtest'@'localhost' for table 't1' +#------------------------------------------------------------------------ +# I/R/U/D/S on the inner view +# Expectation: Can run the queries, but not EXPLAIN or ANALYZE +# because the query plan cannot be shown +# (it would have revealed the structure of the table) +#------------------------------------------------------------------------ +INSERT INTO v1 (a) VALUES (10); +EXPLAIN INSERT INTO v1 (a) VALUES (10); +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +ANALYZE INSERT INTO v1 (a) VALUES (10); +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +INSERT INTO v1 SELECT * FROM t2; +EXPLAIN INSERT INTO v1 SELECT * FROM t2; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +ANALYZE INSERT INTO v1 SELECT * FROM t2; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +REPLACE INTO v1 (a) VALUES (10); +EXPLAIN REPLACE INTO v1 (a) VALUES (10); +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +ANALYZE REPLACE INTO v1 (a) VALUES (10); +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +REPLACE INTO v1 SELECT * FROM t2; +EXPLAIN REPLACE INTO v1 SELECT * FROM t2; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +ANALYZE REPLACE INTO v1 SELECT * FROM t2; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +UPDATE v1 SET a = 10; +EXPLAIN UPDATE v1 SET a = 10; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +ANALYZE UPDATE v1 SET a = 10; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +UPDATE v1 SET a = a + 1; +EXPLAIN UPDATE v1 SET a = a + 1; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +ANALYZE UPDATE v1 SET a = a + 1; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +UPDATE v1, t2 SET v1.a = v1.a + 1 WHERE v1.a = t2.a; +EXPLAIN UPDATE v1, t2 SET v1.a = v1.a + 1 WHERE v1.a = t2.a; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +ANALYZE UPDATE v1, t2 SET v1.a = v1.a + 1 WHERE v1.a = t2.a; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +DELETE FROM v1; +EXPLAIN DELETE FROM v1; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +ANALYZE DELETE FROM v1; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +DELETE FROM v1 WHERE a = 10; +EXPLAIN DELETE FROM v1 WHERE a = 10; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +ANALYZE DELETE FROM v1 WHERE a = 10; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +DELETE FROM v1 USING v1, t2; +EXPLAIN DELETE FROM v1 USING v1, t2; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +ANALYZE DELETE FROM v1 USING v1, t2; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +DELETE FROM v1 USING v1, t2 WHERE v1.a = t2.a; +EXPLAIN DELETE FROM v1 USING v1, t2 WHERE v1.a = t2.a; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +ANALYZE DELETE FROM v1 USING v1, t2 WHERE v1.a = t2.a; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +SELECT * FROM v1; +a b +EXPLAIN SELECT * FROM v1; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +ANALYZE SELECT * FROM v1; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +SELECT * FROM v1 WHERE a = 10; +a b +EXPLAIN SELECT * FROM v1 WHERE a = 10; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +ANALYZE SELECT * FROM v1 WHERE a = 10; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +SELECT * FROM v1 WHERE a IN ( SELECT a FROM t2 ); +a b +EXPLAIN SELECT * FROM v1 WHERE a IN ( SELECT a FROM t2 ); +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +ANALYZE SELECT * FROM v1 WHERE a IN ( SELECT a FROM t2 ); +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +#------------------------------------------------------------------------ +# I/R/U/D/S on the outer view +# Expectation: Can run the queries, but not EXPLAIN or ANALYZE +# because the query plan cannot be shown +# (it would have revealed the structure of the table) +#------------------------------------------------------------------------ +INSERT INTO v2 (a) VALUES (10); +EXPLAIN INSERT INTO v2 (a) VALUES (10); +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +ANALYZE INSERT INTO v2 (a) VALUES (10); +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +INSERT INTO v2 SELECT * FROM t2; +EXPLAIN INSERT INTO v2 SELECT * FROM t2; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +ANALYZE INSERT INTO v2 SELECT * FROM t2; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +REPLACE INTO v2 (a) VALUES (10); +EXPLAIN REPLACE INTO v2 (a) VALUES (10); +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +ANALYZE REPLACE INTO v2 (a) VALUES (10); +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +REPLACE INTO v2 SELECT * FROM t2; +EXPLAIN REPLACE INTO v2 SELECT * FROM t2; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +ANALYZE REPLACE INTO v2 SELECT * FROM t2; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +UPDATE v2 SET a = 10; +EXPLAIN UPDATE v2 SET a = 10; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +ANALYZE UPDATE v2 SET a = 10; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +UPDATE v2 SET a = a + 1; +EXPLAIN UPDATE v2 SET a = a + 1; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +ANALYZE UPDATE v2 SET a = a + 1; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +UPDATE v2, t2 SET v2.a = v2.a + 1 WHERE v2.a = t2.a; +EXPLAIN UPDATE v2, t2 SET v2.a = v2.a + 1 WHERE v2.a = t2.a; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +ANALYZE UPDATE v2, t2 SET v2.a = v2.a + 1 WHERE v2.a = t2.a; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +DELETE FROM v2; +EXPLAIN DELETE FROM v2; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +ANALYZE DELETE FROM v2; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +DELETE FROM v2 WHERE a = 10; +EXPLAIN DELETE FROM v2 WHERE a = 10; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +ANALYZE DELETE FROM v2 WHERE a = 10; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +DELETE FROM v2 USING v2, t2; +EXPLAIN DELETE FROM v2 USING v2, t2; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +ANALYZE DELETE FROM v2 USING v2, t2; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +DELETE FROM v2 USING v2, t2 WHERE v2.a = t2.a; +EXPLAIN DELETE FROM v2 USING v2, t2 WHERE v2.a = t2.a; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +ANALYZE DELETE FROM v2 USING v2, t2 WHERE v2.a = t2.a; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +SELECT * FROM v2; +a b +EXPLAIN SELECT * FROM v2; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +ANALYZE SELECT * FROM v2; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +SELECT * FROM v2 WHERE a = 10; +a b +EXPLAIN SELECT * FROM v2 WHERE a = 10; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +ANALYZE SELECT * FROM v2 WHERE a = 10; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +SELECT * FROM v2 WHERE a IN ( SELECT a FROM t2 ); +a b +EXPLAIN SELECT * FROM v2 WHERE a IN ( SELECT a FROM t2 ); +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +ANALYZE SELECT * FROM v2 WHERE a IN ( SELECT a FROM t2 ); +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table + +#======================================================================== +# Test: Grant SELECT on the table +#======================================================================== + +connection default; +GRANT SELECT ON privtest_db.t1 TO 'privtest'@'localhost'; +connection con1; +#------------------------------------------------------------------------ +# I/R/U/D/S on the table +# Expectation: Can only run SELECT, EXPLAIN SELECT, ANALYZE SELECT +#------------------------------------------------------------------------ +INSERT INTO t1 (a) VALUES (10); +ERROR 42000: INSERT command denied to user 'privtest'@'localhost' for table 't1' +EXPLAIN INSERT INTO t1 (a) VALUES (10); +ERROR 42000: INSERT command denied to user 'privtest'@'localhost' for table 't1' +ANALYZE INSERT INTO t1 (a) VALUES (10); +ERROR 42000: INSERT command denied to user 'privtest'@'localhost' for table 't1' +INSERT INTO t1 SELECT * FROM t2; +ERROR 42000: INSERT command denied to user 'privtest'@'localhost' for table 't1' +EXPLAIN INSERT INTO t1 SELECT * FROM t2; +ERROR 42000: INSERT command denied to user 'privtest'@'localhost' for table 't1' +ANALYZE INSERT INTO t1 SELECT * FROM t2; +ERROR 42000: INSERT command denied to user 'privtest'@'localhost' for table 't1' +REPLACE INTO t1 (a) VALUES (10); +ERROR 42000: INSERT, DELETE command denied to user 'privtest'@'localhost' for table 't1' +EXPLAIN REPLACE INTO t1 (a) VALUES (10); +ERROR 42000: INSERT, DELETE command denied to user 'privtest'@'localhost' for table 't1' +ANALYZE REPLACE INTO t1 (a) VALUES (10); +ERROR 42000: INSERT, DELETE command denied to user 'privtest'@'localhost' for table 't1' +REPLACE INTO t1 SELECT * FROM t2; +ERROR 42000: INSERT, DELETE command denied to user 'privtest'@'localhost' for table 't1' +EXPLAIN REPLACE INTO t1 SELECT * FROM t2; +ERROR 42000: INSERT, DELETE command denied to user 'privtest'@'localhost' for table 't1' +ANALYZE REPLACE INTO t1 SELECT * FROM t2; +ERROR 42000: INSERT, DELETE command denied to user 'privtest'@'localhost' for table 't1' +UPDATE t1 SET a = 10; +ERROR 42000: UPDATE command denied to user 'privtest'@'localhost' for table 't1' +EXPLAIN UPDATE t1 SET a = 10; +ERROR 42000: UPDATE command denied to user 'privtest'@'localhost' for table 't1' +ANALYZE UPDATE t1 SET a = 10; +ERROR 42000: UPDATE command denied to user 'privtest'@'localhost' for table 't1' +UPDATE t1 SET a = a + 1; +ERROR 42000: UPDATE command denied to user 'privtest'@'localhost' for table 't1' +EXPLAIN UPDATE t1 SET a = a + 1; +ERROR 42000: UPDATE command denied to user 'privtest'@'localhost' for table 't1' +ANALYZE UPDATE t1 SET a = a + 1; +ERROR 42000: UPDATE command denied to user 'privtest'@'localhost' for table 't1' +UPDATE t1, t2 SET t1.a = t1.a + 1 WHERE t1.a = t2.a; +ERROR 42000: UPDATE command denied to user 'privtest'@'localhost' for table 't1' +EXPLAIN UPDATE t1, t2 SET t1.a = t1.a + 1 WHERE t1.a = t2.a; +ERROR 42000: UPDATE command denied to user 'privtest'@'localhost' for table 't1' +ANALYZE UPDATE t1, t2 SET t1.a = t1.a + 1 WHERE t1.a = t2.a; +ERROR 42000: UPDATE command denied to user 'privtest'@'localhost' for table 't1' +DELETE FROM t1; +ERROR 42000: DELETE command denied to user 'privtest'@'localhost' for table 't1' +EXPLAIN DELETE FROM t1; +ERROR 42000: DELETE command denied to user 'privtest'@'localhost' for table 't1' +ANALYZE DELETE FROM t1; +ERROR 42000: DELETE command denied to user 'privtest'@'localhost' for table 't1' +DELETE FROM t1 WHERE a = 10; +ERROR 42000: DELETE command denied to user 'privtest'@'localhost' for table 't1' +EXPLAIN DELETE FROM t1 WHERE a = 10; +ERROR 42000: DELETE command denied to user 'privtest'@'localhost' for table 't1' +ANALYZE DELETE FROM t1 WHERE a = 10; +ERROR 42000: DELETE command denied to user 'privtest'@'localhost' for table 't1' +DELETE FROM t1 USING t1, t2; +ERROR 42000: DELETE command denied to user 'privtest'@'localhost' for table 't1' +EXPLAIN DELETE FROM t1 USING t1, t2; +ERROR 42000: DELETE command denied to user 'privtest'@'localhost' for table 't1' +ANALYZE DELETE FROM t1 USING t1, t2; +ERROR 42000: DELETE command denied to user 'privtest'@'localhost' for table 't1' +DELETE FROM t1 USING t1, t2 WHERE t1.a = t2.a; +ERROR 42000: DELETE command denied to user 'privtest'@'localhost' for table 't1' +EXPLAIN DELETE FROM t1 USING t1, t2 WHERE t1.a = t2.a; +ERROR 42000: DELETE command denied to user 'privtest'@'localhost' for table 't1' +ANALYZE DELETE FROM t1 USING t1, t2 WHERE t1.a = t2.a; +ERROR 42000: DELETE command denied to user 'privtest'@'localhost' for table 't1' +SELECT * FROM t1; +a b +10 NULL +10 NULL +10 NULL +10 NULL +EXPLAIN SELECT * FROM t1; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 4 +ANALYZE SELECT * FROM t1; +id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 4 4.00 100.00 100.00 +SELECT * FROM t1 WHERE a = 10; +a b +10 NULL +10 NULL +10 NULL +10 NULL +EXPLAIN SELECT * FROM t1 WHERE a = 10; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 4 Using where +ANALYZE SELECT * FROM t1 WHERE a = 10; +id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 4 4.00 100.00 100.00 Using where +SELECT * FROM t1 WHERE a IN ( SELECT a FROM t2 ); +a b +EXPLAIN SELECT * FROM t1 WHERE a IN ( SELECT a FROM t2 ); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 3 +1 PRIMARY t1 ALL NULL NULL NULL NULL 4 Using where; Using join buffer (flat, BNL join) +2 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 +ANALYZE SELECT * FROM t1 WHERE a IN ( SELECT a FROM t2 ); +id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra +1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 3 3.00 100.00 100.00 +1 PRIMARY t1 ALL NULL NULL NULL NULL 4 4.00 100.00 0.00 Using where; Using join buffer (flat, BNL join) +2 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 3.00 100.00 100.00 +#------------------------------------------------------------------------ +# I/R/U/D/S on the inner view +# Expectation: Can run everything +#------------------------------------------------------------------------ +INSERT INTO v1 (a) VALUES (10); +EXPLAIN INSERT INTO v1 (a) VALUES (10); +id select_type table type possible_keys key key_len ref rows Extra +1 INSERT t1 ALL NULL NULL NULL NULL NULL NULL +ANALYZE INSERT INTO v1 (a) VALUES (10); +id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra +1 INSERT t1 ALL NULL NULL NULL NULL NULL NULL 100.00 100.00 NULL +INSERT INTO v1 SELECT * FROM t2; +EXPLAIN INSERT INTO v1 SELECT * FROM t2; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t2 ALL NULL NULL NULL NULL 3 +ANALYZE INSERT INTO v1 SELECT * FROM t2; +id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra +1 SIMPLE t2 ALL NULL NULL NULL NULL 3 3.00 100.00 100.00 +REPLACE INTO v1 (a) VALUES (10); +EXPLAIN REPLACE INTO v1 (a) VALUES (10); +id select_type table type possible_keys key key_len ref rows Extra +1 INSERT t1 ALL NULL NULL NULL NULL NULL NULL +ANALYZE REPLACE INTO v1 (a) VALUES (10); +id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra +1 INSERT t1 ALL NULL NULL NULL NULL NULL NULL 100.00 100.00 NULL +REPLACE INTO v1 SELECT * FROM t2; +EXPLAIN REPLACE INTO v1 SELECT * FROM t2; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t2 ALL NULL NULL NULL NULL 3 +ANALYZE REPLACE INTO v1 SELECT * FROM t2; +id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra +1 SIMPLE t2 ALL NULL NULL NULL NULL 3 3.00 100.00 100.00 +UPDATE v1 SET a = 10; +EXPLAIN UPDATE v1 SET a = 10; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 20 Using where +ANALYZE UPDATE v1 SET a = 10; +id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 20 20.00 100.00 60.00 Using where +UPDATE v1 SET a = a + 1; +EXPLAIN UPDATE v1 SET a = a + 1; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 20 Using where +ANALYZE UPDATE v1 SET a = a + 1; +id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 20 20.00 100.00 60.00 Using where +UPDATE v1, t2 SET v1.a = v1.a + 1 WHERE v1.a = t2.a; +EXPLAIN UPDATE v1, t2 SET v1.a = v1.a + 1 WHERE v1.a = t2.a; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t2 ALL NULL NULL NULL NULL 3 +1 SIMPLE t1 ALL NULL NULL NULL NULL 20 Using where +ANALYZE UPDATE v1, t2 SET v1.a = v1.a + 1 WHERE v1.a = t2.a; +id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra +1 SIMPLE t2 ALL NULL NULL NULL NULL 3 3.00 100.00 100.00 +1 SIMPLE t1 ALL NULL NULL NULL NULL 20 20.00 100.00 0.00 Using where +DELETE FROM v1; +EXPLAIN DELETE FROM v1; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 8 Using where +ANALYZE DELETE FROM v1; +id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 8 8.00 100.00 0.00 Using where +DELETE FROM v1 WHERE a = 10; +EXPLAIN DELETE FROM v1 WHERE a = 10; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 8 Using where +ANALYZE DELETE FROM v1 WHERE a = 10; +id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 8 8.00 100.00 0.00 Using where +DELETE FROM v1 USING v1, t2; +EXPLAIN DELETE FROM v1 USING v1, t2; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t2 ALL NULL NULL NULL NULL 3 +1 SIMPLE t1 ALL NULL NULL NULL NULL 8 Using where +ANALYZE DELETE FROM v1 USING v1, t2; +id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra +1 SIMPLE t2 ALL NULL NULL NULL NULL 3 3.00 100.00 100.00 +1 SIMPLE t1 ALL NULL NULL NULL NULL 8 8.00 100.00 0.00 Using where +DELETE FROM v1 USING v1, t2 WHERE v1.a = t2.a; +EXPLAIN DELETE FROM v1 USING v1, t2 WHERE v1.a = t2.a; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t2 ALL NULL NULL NULL NULL 3 +1 SIMPLE t1 ALL NULL NULL NULL NULL 8 Using where +ANALYZE DELETE FROM v1 USING v1, t2 WHERE v1.a = t2.a; +id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra +1 SIMPLE t2 ALL NULL NULL NULL NULL 3 3.00 100.00 100.00 +1 SIMPLE t1 ALL NULL NULL NULL NULL 8 8.00 100.00 0.00 Using where +SELECT * FROM v1; +a b +EXPLAIN SELECT * FROM v1; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 8 Using where +ANALYZE SELECT * FROM v1; +id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 8 8.00 100.00 0.00 Using where +SELECT * FROM v1 WHERE a = 10; +a b +EXPLAIN SELECT * FROM v1 WHERE a = 10; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 8 Using where +ANALYZE SELECT * FROM v1 WHERE a = 10; +id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 8 8.00 100.00 0.00 Using where +SELECT * FROM v1 WHERE a IN ( SELECT a FROM t2 ); +a b +EXPLAIN SELECT * FROM v1 WHERE a IN ( SELECT a FROM t2 ); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 3 +1 PRIMARY t1 ALL NULL NULL NULL NULL 8 Using where; Using join buffer (flat, BNL join) +2 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 +ANALYZE SELECT * FROM v1 WHERE a IN ( SELECT a FROM t2 ); +id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra +1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 3 3.00 100.00 100.00 +1 PRIMARY t1 ALL NULL NULL NULL NULL 8 8.00 100.00 0.00 Using where; Using join buffer (flat, BNL join) +2 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 3.00 100.00 100.00 +#------------------------------------------------------------------------ +# I/R/U/D/S on the outer view +# Expectation: Can run everything +#------------------------------------------------------------------------ +INSERT INTO v2 (a) VALUES (10); +EXPLAIN INSERT INTO v2 (a) VALUES (10); +id select_type table type possible_keys key key_len ref rows Extra +1 INSERT t1 ALL NULL NULL NULL NULL NULL NULL +ANALYZE INSERT INTO v2 (a) VALUES (10); +id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra +1 INSERT t1 ALL NULL NULL NULL NULL NULL NULL 100.00 100.00 NULL +INSERT INTO v2 SELECT * FROM t2; +EXPLAIN INSERT INTO v2 SELECT * FROM t2; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t2 ALL NULL NULL NULL NULL 3 +ANALYZE INSERT INTO v2 SELECT * FROM t2; +id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra +1 SIMPLE t2 ALL NULL NULL NULL NULL 3 3.00 100.00 100.00 +REPLACE INTO v2 (a) VALUES (10); +EXPLAIN REPLACE INTO v2 (a) VALUES (10); +id select_type table type possible_keys key key_len ref rows Extra +1 INSERT t1 ALL NULL NULL NULL NULL NULL NULL +ANALYZE REPLACE INTO v2 (a) VALUES (10); +id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra +1 INSERT t1 ALL NULL NULL NULL NULL NULL NULL 100.00 100.00 NULL +REPLACE INTO v2 SELECT * FROM t2; +EXPLAIN REPLACE INTO v2 SELECT * FROM t2; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t2 ALL NULL NULL NULL NULL 3 +ANALYZE REPLACE INTO v2 SELECT * FROM t2; +id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra +1 SIMPLE t2 ALL NULL NULL NULL NULL 3 3.00 100.00 100.00 +UPDATE v2 SET a = 10; +EXPLAIN UPDATE v2 SET a = 10; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 24 Using where +ANALYZE UPDATE v2 SET a = 10; +id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 24 24.00 100.00 50.00 Using where +UPDATE v2 SET a = a + 1; +EXPLAIN UPDATE v2 SET a = a + 1; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 24 Using where +ANALYZE UPDATE v2 SET a = a + 1; +id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 24 24.00 100.00 50.00 Using where +UPDATE v2, t2 SET v2.a = v2.a + 1 WHERE v2.a = t2.a; +EXPLAIN UPDATE v2, t2 SET v2.a = v2.a + 1 WHERE v2.a = t2.a; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t2 ALL NULL NULL NULL NULL 3 Using where +1 SIMPLE t1 ALL NULL NULL NULL NULL 24 Using where +ANALYZE UPDATE v2, t2 SET v2.a = v2.a + 1 WHERE v2.a = t2.a; +id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra +1 SIMPLE t2 ALL NULL NULL NULL NULL 3 3.00 100.00 100.00 Using where +1 SIMPLE t1 ALL NULL NULL NULL NULL 24 24.00 100.00 0.00 Using where +DELETE FROM v2; +EXPLAIN DELETE FROM v2; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 12 Using where +ANALYZE DELETE FROM v2; +id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 12 12.00 100.00 0.00 Using where +DELETE FROM v2 WHERE a = 10; +EXPLAIN DELETE FROM v2 WHERE a = 10; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 12 Using where +ANALYZE DELETE FROM v2 WHERE a = 10; +id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 12 12.00 100.00 0.00 Using where +DELETE FROM v2 USING v2, t2; +ANALYZE DELETE FROM v2 USING v2, t2; +id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra +1 SIMPLE t2 ALL NULL NULL NULL NULL 3 3.00 100.00 100.00 +1 SIMPLE t1 ALL NULL NULL NULL NULL 12 12.00 100.00 0.00 Using where +DELETE FROM v2 USING v2, t2 WHERE v2.a = t2.a; +ANALYZE DELETE FROM v2 USING v2, t2 WHERE v2.a = t2.a; +id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra +1 SIMPLE t2 ALL NULL NULL NULL NULL 3 3.00 100.00 100.00 Using where +1 SIMPLE t1 ALL NULL NULL NULL NULL 12 12.00 100.00 0.00 Using where +SELECT * FROM v2; +a b +EXPLAIN SELECT * FROM v2; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 12 Using where +ANALYZE SELECT * FROM v2; +id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 12 12.00 100.00 0.00 Using where +SELECT * FROM v2 WHERE a = 10; +a b +EXPLAIN SELECT * FROM v2 WHERE a = 10; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 12 Using where +ANALYZE SELECT * FROM v2 WHERE a = 10; +id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 12 12.00 100.00 0.00 Using where +SELECT * FROM v2 WHERE a IN ( SELECT a FROM t2 ); +a b +EXPLAIN SELECT * FROM v2 WHERE a IN ( SELECT a FROM t2 ); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 3 +1 PRIMARY t1 ALL NULL NULL NULL NULL 12 Using where; Using join buffer (flat, BNL join) +2 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 Using where +ANALYZE SELECT * FROM v2 WHERE a IN ( SELECT a FROM t2 ); +id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra +1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 3 3.00 100.00 100.00 +1 PRIMARY t1 ALL NULL NULL NULL NULL 12 12.00 100.00 0.00 Using where; Using join buffer (flat, BNL join) +2 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 3.00 100.00 100.00 Using where + +#======================================================================== +# Test: Grant INSERT on the table +#======================================================================== + +connection default; +REVOKE ALL PRIVILEGES ON privtest_db.t1 FROM 'privtest'@'localhost'; +GRANT INSERT ON privtest_db.t1 TO 'privtest'@'localhost'; +connection con1; +#------------------------------------------------------------------------ +# I/R/U/D/S on the table +# Expectation: Can only run INSERT, EXPLAIN INSERT, ANALYZE INSERT +#------------------------------------------------------------------------ +INSERT INTO t1 (a) VALUES (10); +EXPLAIN INSERT INTO t1 (a) VALUES (10); +id select_type table type possible_keys key key_len ref rows Extra +1 INSERT t1 ALL NULL NULL NULL NULL NULL NULL +ANALYZE INSERT INTO t1 (a) VALUES (10); +id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra +1 INSERT t1 ALL NULL NULL NULL NULL NULL NULL 100.00 100.00 NULL +INSERT INTO t1 SELECT * FROM t2; +EXPLAIN INSERT INTO t1 SELECT * FROM t2; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t2 ALL NULL NULL NULL NULL 3 +ANALYZE INSERT INTO t1 SELECT * FROM t2; +id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra +1 SIMPLE t2 ALL NULL NULL NULL NULL 3 3.00 100.00 100.00 +REPLACE INTO t1 (a) VALUES (10); +ERROR 42000: DELETE command denied to user 'privtest'@'localhost' for table 't1' +EXPLAIN REPLACE INTO t1 (a) VALUES (10); +ERROR 42000: DELETE command denied to user 'privtest'@'localhost' for table 't1' +ANALYZE REPLACE INTO t1 (a) VALUES (10); +ERROR 42000: DELETE command denied to user 'privtest'@'localhost' for table 't1' +REPLACE INTO t1 SELECT * FROM t2; +ERROR 42000: DELETE command denied to user 'privtest'@'localhost' for table 't1' +EXPLAIN REPLACE INTO t1 SELECT * FROM t2; +ERROR 42000: DELETE command denied to user 'privtest'@'localhost' for table 't1' +ANALYZE REPLACE INTO t1 SELECT * FROM t2; +ERROR 42000: DELETE command denied to user 'privtest'@'localhost' for table 't1' +UPDATE t1 SET a = 10; +ERROR 42000: UPDATE command denied to user 'privtest'@'localhost' for table 't1' +EXPLAIN UPDATE t1 SET a = 10; +ERROR 42000: UPDATE command denied to user 'privtest'@'localhost' for table 't1' +ANALYZE UPDATE t1 SET a = 10; +ERROR 42000: UPDATE command denied to user 'privtest'@'localhost' for table 't1' +UPDATE t1 SET a = a + 1; +ERROR 42000: UPDATE command denied to user 'privtest'@'localhost' for table 't1' +EXPLAIN UPDATE t1 SET a = a + 1; +ERROR 42000: UPDATE command denied to user 'privtest'@'localhost' for table 't1' +ANALYZE UPDATE t1 SET a = a + 1; +ERROR 42000: UPDATE command denied to user 'privtest'@'localhost' for table 't1' +UPDATE t1, t2 SET t1.a = t1.a + 1 WHERE t1.a = t2.a; +ERROR 42000: SELECT command denied to user 'privtest'@'localhost' for table 't1' +EXPLAIN UPDATE t1, t2 SET t1.a = t1.a + 1 WHERE t1.a = t2.a; +ERROR 42000: SELECT command denied to user 'privtest'@'localhost' for table 't1' +ANALYZE UPDATE t1, t2 SET t1.a = t1.a + 1 WHERE t1.a = t2.a; +ERROR 42000: SELECT command denied to user 'privtest'@'localhost' for table 't1' +DELETE FROM t1; +ERROR 42000: DELETE command denied to user 'privtest'@'localhost' for table 't1' +EXPLAIN DELETE FROM t1; +ERROR 42000: DELETE command denied to user 'privtest'@'localhost' for table 't1' +ANALYZE DELETE FROM t1; +ERROR 42000: DELETE command denied to user 'privtest'@'localhost' for table 't1' +DELETE FROM t1 WHERE a = 10; +ERROR 42000: DELETE command denied to user 'privtest'@'localhost' for table 't1' +EXPLAIN DELETE FROM t1 WHERE a = 10; +ERROR 42000: DELETE command denied to user 'privtest'@'localhost' for table 't1' +ANALYZE DELETE FROM t1 WHERE a = 10; +ERROR 42000: DELETE command denied to user 'privtest'@'localhost' for table 't1' +DELETE FROM t1 USING t1, t2; +ERROR 42000: SELECT command denied to user 'privtest'@'localhost' for table 't1' +EXPLAIN DELETE FROM t1 USING t1, t2; +ERROR 42000: SELECT command denied to user 'privtest'@'localhost' for table 't1' +ANALYZE DELETE FROM t1 USING t1, t2; +ERROR 42000: SELECT command denied to user 'privtest'@'localhost' for table 't1' +DELETE FROM t1 USING t1, t2 WHERE t1.a = t2.a; +ERROR 42000: SELECT command denied to user 'privtest'@'localhost' for table 't1' +EXPLAIN DELETE FROM t1 USING t1, t2 WHERE t1.a = t2.a; +ERROR 42000: SELECT command denied to user 'privtest'@'localhost' for table 't1' +ANALYZE DELETE FROM t1 USING t1, t2 WHERE t1.a = t2.a; +ERROR 42000: SELECT command denied to user 'privtest'@'localhost' for table 't1' +SELECT * FROM t1; +ERROR 42000: SELECT command denied to user 'privtest'@'localhost' for table 't1' +EXPLAIN SELECT * FROM t1; +ERROR 42000: SELECT command denied to user 'privtest'@'localhost' for table 't1' +ANALYZE SELECT * FROM t1; +ERROR 42000: SELECT command denied to user 'privtest'@'localhost' for table 't1' +SELECT * FROM t1 WHERE a = 10; +ERROR 42000: SELECT command denied to user 'privtest'@'localhost' for table 't1' +EXPLAIN SELECT * FROM t1 WHERE a = 10; +ERROR 42000: SELECT command denied to user 'privtest'@'localhost' for table 't1' +ANALYZE SELECT * FROM t1 WHERE a = 10; +ERROR 42000: SELECT command denied to user 'privtest'@'localhost' for table 't1' +SELECT * FROM t1 WHERE a IN ( SELECT a FROM t2 ); +ERROR 42000: SELECT command denied to user 'privtest'@'localhost' for table 't1' +EXPLAIN SELECT * FROM t1 WHERE a IN ( SELECT a FROM t2 ); +ERROR 42000: SELECT command denied to user 'privtest'@'localhost' for table 't1' +ANALYZE SELECT * FROM t1 WHERE a IN ( SELECT a FROM t2 ); +ERROR 42000: SELECT command denied to user 'privtest'@'localhost' for table 't1' +#------------------------------------------------------------------------ +# I/R/U/D/S on the inner view +# Expectation: Can run the queries, but not EXPLAIN or ANALYZE +# because the query plan cannot be shown +# (it would have revealed the structure of the table) +#------------------------------------------------------------------------ +INSERT INTO v1 (a) VALUES (10); +EXPLAIN INSERT INTO v1 (a) VALUES (10); +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +ANALYZE INSERT INTO v1 (a) VALUES (10); +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +INSERT INTO v1 SELECT * FROM t2; +EXPLAIN INSERT INTO v1 SELECT * FROM t2; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +ANALYZE INSERT INTO v1 SELECT * FROM t2; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +REPLACE INTO v1 (a) VALUES (10); +EXPLAIN REPLACE INTO v1 (a) VALUES (10); +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +ANALYZE REPLACE INTO v1 (a) VALUES (10); +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +REPLACE INTO v1 SELECT * FROM t2; +EXPLAIN REPLACE INTO v1 SELECT * FROM t2; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +ANALYZE REPLACE INTO v1 SELECT * FROM t2; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +UPDATE v1 SET a = 10; +EXPLAIN UPDATE v1 SET a = 10; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +ANALYZE UPDATE v1 SET a = 10; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +UPDATE v1 SET a = a + 1; +EXPLAIN UPDATE v1 SET a = a + 1; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +ANALYZE UPDATE v1 SET a = a + 1; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +UPDATE v1, t2 SET v1.a = v1.a + 1 WHERE v1.a = t2.a; +EXPLAIN UPDATE v1, t2 SET v1.a = v1.a + 1 WHERE v1.a = t2.a; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +ANALYZE UPDATE v1, t2 SET v1.a = v1.a + 1 WHERE v1.a = t2.a; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +DELETE FROM v1; +EXPLAIN DELETE FROM v1; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +ANALYZE DELETE FROM v1; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +DELETE FROM v1 WHERE a = 10; +EXPLAIN DELETE FROM v1 WHERE a = 10; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +ANALYZE DELETE FROM v1 WHERE a = 10; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +DELETE FROM v1 USING v1, t2; +EXPLAIN DELETE FROM v1 USING v1, t2; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +ANALYZE DELETE FROM v1 USING v1, t2; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +DELETE FROM v1 USING v1, t2 WHERE v1.a = t2.a; +EXPLAIN DELETE FROM v1 USING v1, t2 WHERE v1.a = t2.a; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +ANALYZE DELETE FROM v1 USING v1, t2 WHERE v1.a = t2.a; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +SELECT * FROM v1; +a b +EXPLAIN SELECT * FROM v1; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +ANALYZE SELECT * FROM v1; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +SELECT * FROM v1 WHERE a = 10; +a b +EXPLAIN SELECT * FROM v1 WHERE a = 10; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +ANALYZE SELECT * FROM v1 WHERE a = 10; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +SELECT * FROM v1 WHERE a IN ( SELECT a FROM t2 ); +a b +EXPLAIN SELECT * FROM v1 WHERE a IN ( SELECT a FROM t2 ); +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +ANALYZE SELECT * FROM v1 WHERE a IN ( SELECT a FROM t2 ); +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +#------------------------------------------------------------------------ +# I/R/U/D/S on the outer view +# Expectation: Can run the queries, but not EXPLAIN or ANALYZE +# because the query plan cannot be shown +# (it would have revealed the structure of the table) +#------------------------------------------------------------------------ +INSERT INTO v2 (a) VALUES (10); +EXPLAIN INSERT INTO v2 (a) VALUES (10); +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +ANALYZE INSERT INTO v2 (a) VALUES (10); +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +INSERT INTO v2 SELECT * FROM t2; +EXPLAIN INSERT INTO v2 SELECT * FROM t2; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +ANALYZE INSERT INTO v2 SELECT * FROM t2; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +REPLACE INTO v2 (a) VALUES (10); +EXPLAIN REPLACE INTO v2 (a) VALUES (10); +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +ANALYZE REPLACE INTO v2 (a) VALUES (10); +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +REPLACE INTO v2 SELECT * FROM t2; +EXPLAIN REPLACE INTO v2 SELECT * FROM t2; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +ANALYZE REPLACE INTO v2 SELECT * FROM t2; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +UPDATE v2 SET a = 10; +EXPLAIN UPDATE v2 SET a = 10; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +ANALYZE UPDATE v2 SET a = 10; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +UPDATE v2 SET a = a + 1; +EXPLAIN UPDATE v2 SET a = a + 1; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +ANALYZE UPDATE v2 SET a = a + 1; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +UPDATE v2, t2 SET v2.a = v2.a + 1 WHERE v2.a = t2.a; +EXPLAIN UPDATE v2, t2 SET v2.a = v2.a + 1 WHERE v2.a = t2.a; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +ANALYZE UPDATE v2, t2 SET v2.a = v2.a + 1 WHERE v2.a = t2.a; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +DELETE FROM v2; +EXPLAIN DELETE FROM v2; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +ANALYZE DELETE FROM v2; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +DELETE FROM v2 WHERE a = 10; +EXPLAIN DELETE FROM v2 WHERE a = 10; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +ANALYZE DELETE FROM v2 WHERE a = 10; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +DELETE FROM v2 USING v2, t2; +EXPLAIN DELETE FROM v2 USING v2, t2; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +ANALYZE DELETE FROM v2 USING v2, t2; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +DELETE FROM v2 USING v2, t2 WHERE v2.a = t2.a; +EXPLAIN DELETE FROM v2 USING v2, t2 WHERE v2.a = t2.a; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +ANALYZE DELETE FROM v2 USING v2, t2 WHERE v2.a = t2.a; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +SELECT * FROM v2; +a b +EXPLAIN SELECT * FROM v2; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +ANALYZE SELECT * FROM v2; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +SELECT * FROM v2 WHERE a = 10; +a b +EXPLAIN SELECT * FROM v2 WHERE a = 10; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +ANALYZE SELECT * FROM v2 WHERE a = 10; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +SELECT * FROM v2 WHERE a IN ( SELECT a FROM t2 ); +a b +EXPLAIN SELECT * FROM v2 WHERE a IN ( SELECT a FROM t2 ); +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +ANALYZE SELECT * FROM v2 WHERE a IN ( SELECT a FROM t2 ); +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table + +#======================================================================== +# Test: Grant UPDATE on the table +#======================================================================== + +connection default; +REVOKE ALL PRIVILEGES ON privtest_db.t1 FROM 'privtest'@'localhost'; +GRANT UPDATE ON privtest_db.t1 TO 'privtest'@'localhost'; +connection con1; +#------------------------------------------------------------------------ +# I/R/U/D/S on the table +# Expectation: Can only run UPDATE, EXPLAIN UPDATE, ANALYZE UPDATE +# when the UPDATE does not read any columns. UPDATEs which +# read columns fail with ER_COLUMNACCESS_DENIED_ERROR +#------------------------------------------------------------------------ +INSERT INTO t1 (a) VALUES (10); +ERROR 42000: INSERT command denied to user 'privtest'@'localhost' for table 't1' +EXPLAIN INSERT INTO t1 (a) VALUES (10); +ERROR 42000: INSERT command denied to user 'privtest'@'localhost' for table 't1' +ANALYZE INSERT INTO t1 (a) VALUES (10); +ERROR 42000: INSERT command denied to user 'privtest'@'localhost' for table 't1' +ANALYZE INSERT INTO t1 SELECT * FROM t2; +ERROR 42000: INSERT command denied to user 'privtest'@'localhost' for table 't1' +ANALYZE INSERT INTO t1 SELECT * FROM t2; +ERROR 42000: INSERT command denied to user 'privtest'@'localhost' for table 't1' +ANALYZE INSERT INTO t1 SELECT * FROM t2; +ERROR 42000: INSERT command denied to user 'privtest'@'localhost' for table 't1' +REPLACE INTO t1 (a) VALUES (10); +ERROR 42000: INSERT, DELETE command denied to user 'privtest'@'localhost' for table 't1' +EXPLAIN REPLACE INTO t1 (a) VALUES (10); +ERROR 42000: INSERT, DELETE command denied to user 'privtest'@'localhost' for table 't1' +ANALYZE REPLACE INTO t1 (a) VALUES (10); +ERROR 42000: INSERT, DELETE command denied to user 'privtest'@'localhost' for table 't1' +REPLACE INTO t1 SELECT * FROM t2; +ERROR 42000: INSERT, DELETE command denied to user 'privtest'@'localhost' for table 't1' +EXPLAIN REPLACE INTO t1 SELECT * FROM t2; +ERROR 42000: INSERT, DELETE command denied to user 'privtest'@'localhost' for table 't1' +ANALYZE REPLACE INTO t1 SELECT * FROM t2; +ERROR 42000: INSERT, DELETE command denied to user 'privtest'@'localhost' for table 't1' +UPDATE t1 SET a = 10; +EXPLAIN UPDATE t1 SET a = 10; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 18 +ANALYZE UPDATE t1 SET a = 10; +id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 18 18.00 100.00 100.00 +UPDATE t1 SET a = a + 1; +ERROR 42000: SELECT command denied to user 'privtest'@'localhost' for column 'a' in table 't1' +EXPLAIN UPDATE t1 SET a = a + 1; +ERROR 42000: SELECT command denied to user 'privtest'@'localhost' for column 'a' in table 't1' +ANALYZE UPDATE t1 SET a = a + 1; +ERROR 42000: SELECT command denied to user 'privtest'@'localhost' for column 'a' in table 't1' +UPDATE t1, t2 SET t1.a = t1.a + 1 WHERE t1.a = t2.a; +ERROR 42000: SELECT command denied to user 'privtest'@'localhost' for column 'a' in table 't1' +EXPLAIN UPDATE t1, t2 SET t1.a = t1.a + 1 WHERE t1.a = t2.a; +ERROR 42000: SELECT command denied to user 'privtest'@'localhost' for column 'a' in table 't1' +ANALYZE UPDATE t1, t2 SET t1.a = t1.a + 1 WHERE t1.a = t2.a; +ERROR 42000: SELECT command denied to user 'privtest'@'localhost' for column 'a' in table 't1' +DELETE FROM t1; +ERROR 42000: DELETE command denied to user 'privtest'@'localhost' for table 't1' +EXPLAIN DELETE FROM t1; +ERROR 42000: DELETE command denied to user 'privtest'@'localhost' for table 't1' +ANALYZE DELETE FROM t1; +ERROR 42000: DELETE command denied to user 'privtest'@'localhost' for table 't1' +DELETE FROM t1 WHERE a = 10; +ERROR 42000: DELETE command denied to user 'privtest'@'localhost' for table 't1' +EXPLAIN DELETE FROM t1 WHERE a = 10; +ERROR 42000: DELETE command denied to user 'privtest'@'localhost' for table 't1' +ANALYZE DELETE FROM t1 WHERE a = 10; +ERROR 42000: DELETE command denied to user 'privtest'@'localhost' for table 't1' +DELETE FROM t1 USING t1, t2; +ERROR 42000: SELECT command denied to user 'privtest'@'localhost' for table 't1' +EXPLAIN DELETE FROM t1 USING t1, t2; +ERROR 42000: SELECT command denied to user 'privtest'@'localhost' for table 't1' +ANALYZE DELETE FROM t1 USING t1, t2; +ERROR 42000: SELECT command denied to user 'privtest'@'localhost' for table 't1' +DELETE FROM t1 USING t1, t2 WHERE t1.a = t2.a; +ERROR 42000: SELECT command denied to user 'privtest'@'localhost' for table 't1' +EXPLAIN DELETE FROM t1 USING t1, t2 WHERE t1.a = t2.a; +ERROR 42000: SELECT command denied to user 'privtest'@'localhost' for table 't1' +ANALYZE DELETE FROM t1 USING t1, t2 WHERE t1.a = t2.a; +ERROR 42000: SELECT command denied to user 'privtest'@'localhost' for table 't1' +SELECT * FROM t1; +ERROR 42000: SELECT command denied to user 'privtest'@'localhost' for table 't1' +EXPLAIN SELECT * FROM t1; +ERROR 42000: SELECT command denied to user 'privtest'@'localhost' for table 't1' +ANALYZE SELECT * FROM t1; +ERROR 42000: SELECT command denied to user 'privtest'@'localhost' for table 't1' +SELECT * FROM t1 WHERE a = 10; +ERROR 42000: SELECT command denied to user 'privtest'@'localhost' for table 't1' +EXPLAIN SELECT * FROM t1 WHERE a = 10; +ERROR 42000: SELECT command denied to user 'privtest'@'localhost' for table 't1' +ANALYZE SELECT * FROM t1 WHERE a = 10; +ERROR 42000: SELECT command denied to user 'privtest'@'localhost' for table 't1' +SELECT * FROM t1 WHERE a IN ( SELECT a FROM t2 ); +ERROR 42000: SELECT command denied to user 'privtest'@'localhost' for table 't1' +EXPLAIN SELECT * FROM t1 WHERE a IN ( SELECT a FROM t2 ); +ERROR 42000: SELECT command denied to user 'privtest'@'localhost' for table 't1' +ANALYZE SELECT * FROM t1 WHERE a IN ( SELECT a FROM t2 ); +ERROR 42000: SELECT command denied to user 'privtest'@'localhost' for table 't1' +#------------------------------------------------------------------------ +# I/R/U/D/S on the inner view +# Expectation: Can run the queries, but not EXPLAIN or ANALYZE +# because the query plan cannot be shown +# (it would have revealed the structure of the table) +#------------------------------------------------------------------------ +INSERT INTO v1 (a) VALUES (10); +EXPLAIN INSERT INTO v1 (a) VALUES (10); +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +ANALYZE INSERT INTO v1 (a) VALUES (10); +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +INSERT INTO v1 SELECT * FROM t2; +EXPLAIN INSERT INTO v1 SELECT * FROM t2; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +ANALYZE INSERT INTO v1 SELECT * FROM t2; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +REPLACE INTO v1 (a) VALUES (10); +EXPLAIN REPLACE INTO v1 (a) VALUES (10); +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +ANALYZE REPLACE INTO v1 (a) VALUES (10); +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +REPLACE INTO v1 SELECT * FROM t2; +EXPLAIN REPLACE INTO v1 SELECT * FROM t2; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +ANALYZE REPLACE INTO v1 SELECT * FROM t2; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +UPDATE v1 SET a = 10; +EXPLAIN UPDATE v1 SET a = 10; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +ANALYZE UPDATE v1 SET a = 10; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +UPDATE v1 SET a = a + 1; +EXPLAIN UPDATE v1 SET a = a + 1; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +ANALYZE UPDATE v1 SET a = a + 1; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +UPDATE v1, t2 SET v1.a = v1.a + 1 WHERE v1.a = t2.a; +EXPLAIN UPDATE v1, t2 SET v1.a = v1.a + 1 WHERE v1.a = t2.a; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +ANALYZE UPDATE v1, t2 SET v1.a = v1.a + 1 WHERE v1.a = t2.a; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +DELETE FROM v1; +EXPLAIN DELETE FROM v1; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +ANALYZE DELETE FROM v1; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +DELETE FROM v1 WHERE a = 10; +EXPLAIN DELETE FROM v1 WHERE a = 10; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +ANALYZE DELETE FROM v1 WHERE a = 10; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +DELETE FROM v1 USING v1, t2; +EXPLAIN DELETE FROM v1 USING v1, t2; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +ANALYZE DELETE FROM v1 USING v1, t2; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +DELETE FROM v1 USING v1, t2 WHERE v1.a = t2.a; +EXPLAIN DELETE FROM v1 USING v1, t2 WHERE v1.a = t2.a; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +ANALYZE DELETE FROM v1 USING v1, t2 WHERE v1.a = t2.a; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +SELECT * FROM v1; +a b +EXPLAIN SELECT * FROM v1; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +ANALYZE SELECT * FROM v1; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +SELECT * FROM v1 WHERE a = 10; +a b +EXPLAIN SELECT * FROM v1 WHERE a = 10; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +ANALYZE SELECT * FROM v1 WHERE a = 10; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +SELECT * FROM v1 WHERE a IN ( SELECT a FROM t2 ); +a b +EXPLAIN SELECT * FROM v1 WHERE a IN ( SELECT a FROM t2 ); +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +ANALYZE SELECT * FROM v1 WHERE a IN ( SELECT a FROM t2 ); +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +#------------------------------------------------------------------------ +# I/R/U/D/S on the outer view +# Expectation: Can run the queries, but not EXPLAIN or ANALYZE +# because the query plan cannot be shown +# (it would have revealed the structure of the table) +#------------------------------------------------------------------------ +INSERT INTO v2 (a) VALUES (10); +EXPLAIN INSERT INTO v2 (a) VALUES (10); +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +ANALYZE INSERT INTO v2 (a) VALUES (10); +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +INSERT INTO v2 SELECT * FROM t2; +EXPLAIN INSERT INTO v2 SELECT * FROM t2; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +ANALYZE INSERT INTO v2 SELECT * FROM t2; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +REPLACE INTO v2 (a) VALUES (10); +EXPLAIN REPLACE INTO v2 (a) VALUES (10); +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +ANALYZE REPLACE INTO v2 (a) VALUES (10); +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +REPLACE INTO v2 SELECT * FROM t2; +EXPLAIN REPLACE INTO v2 SELECT * FROM t2; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +ANALYZE REPLACE INTO v2 SELECT * FROM t2; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +UPDATE v2 SET a = 10; +EXPLAIN UPDATE v2 SET a = 10; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +ANALYZE UPDATE v2 SET a = 10; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +UPDATE v2 SET a = a + 1; +EXPLAIN UPDATE v2 SET a = a + 1; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +ANALYZE UPDATE v2 SET a = a + 1; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +UPDATE v2, t2 SET v2.a = v2.a + 1 WHERE v2.a = t2.a; +EXPLAIN UPDATE v2, t2 SET v2.a = v2.a + 1 WHERE v2.a = t2.a; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +ANALYZE UPDATE v2, t2 SET v2.a = v2.a + 1 WHERE v2.a = t2.a; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +DELETE FROM v2; +EXPLAIN DELETE FROM v2; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +ANALYZE DELETE FROM v2; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +DELETE FROM v2 WHERE a = 10; +EXPLAIN DELETE FROM v2 WHERE a = 10; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +ANALYZE DELETE FROM v2 WHERE a = 10; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +DELETE FROM v2 USING v2, t2; +EXPLAIN DELETE FROM v2 USING v2, t2; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +ANALYZE DELETE FROM v2 USING v2, t2; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +DELETE FROM v2 USING v2, t2 WHERE v2.a = t2.a; +EXPLAIN DELETE FROM v2 USING v2, t2 WHERE v2.a = t2.a; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +ANALYZE DELETE FROM v2 USING v2, t2 WHERE v2.a = t2.a; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +SELECT * FROM v2; +a b +EXPLAIN SELECT * FROM v2; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +ANALYZE SELECT * FROM v2; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +SELECT * FROM v2 WHERE a = 10; +a b +EXPLAIN SELECT * FROM v2 WHERE a = 10; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +ANALYZE SELECT * FROM v2 WHERE a = 10; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +SELECT * FROM v2 WHERE a IN ( SELECT a FROM t2 ); +a b +EXPLAIN SELECT * FROM v2 WHERE a IN ( SELECT a FROM t2 ); +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +ANALYZE SELECT * FROM v2 WHERE a IN ( SELECT a FROM t2 ); +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table + +#======================================================================== +# Test: Grant DELETE on the table +#======================================================================== + +connection default; +REVOKE ALL PRIVILEGES ON privtest_db.t1 FROM 'privtest'@'localhost'; +GRANT DELETE ON privtest_db.t1 TO 'privtest'@'localhost'; +connection con1; +#------------------------------------------------------------------------ +# I/R/U/D/S on the table +# Expectation: Can only run DELETE, EXPLAIN DELETE, ANALYZE DELETE +# when the DELETE does not read any columns. DELETEs which +# read columns fail with ER_COLUMNACCESS_DENIED_ERROR +#------------------------------------------------------------------------ +# Note: ANALYZE DELETE FROM t1 USING t1, t2 ... fails due to MDEV-7043 +INSERT INTO t1 (a) VALUES (10); +ERROR 42000: INSERT command denied to user 'privtest'@'localhost' for table 't1' +EXPLAIN INSERT INTO t1 (a) VALUES (10); +ERROR 42000: INSERT command denied to user 'privtest'@'localhost' for table 't1' +ANALYZE INSERT INTO t1 (a) VALUES (10); +ERROR 42000: INSERT command denied to user 'privtest'@'localhost' for table 't1' +INSERT INTO t1 SELECT * FROM t2; +ERROR 42000: INSERT command denied to user 'privtest'@'localhost' for table 't1' +EXPLAIN INSERT INTO t1 SELECT * FROM t2; +ERROR 42000: INSERT command denied to user 'privtest'@'localhost' for table 't1' +ANALYZE INSERT INTO t1 SELECT * FROM t2; +ERROR 42000: INSERT command denied to user 'privtest'@'localhost' for table 't1' +REPLACE INTO t1 (a) VALUES (10); +ERROR 42000: INSERT command denied to user 'privtest'@'localhost' for table 't1' +EXPLAIN REPLACE INTO t1 (a) VALUES (10); +ERROR 42000: INSERT command denied to user 'privtest'@'localhost' for table 't1' +ANALYZE REPLACE INTO t1 (a) VALUES (10); +ERROR 42000: INSERT command denied to user 'privtest'@'localhost' for table 't1' +REPLACE INTO t1 SELECT * FROM t2; +ERROR 42000: INSERT command denied to user 'privtest'@'localhost' for table 't1' +EXPLAIN REPLACE INTO t1 SELECT * FROM t2; +ERROR 42000: INSERT command denied to user 'privtest'@'localhost' for table 't1' +ANALYZE REPLACE INTO t1 SELECT * FROM t2; +ERROR 42000: INSERT command denied to user 'privtest'@'localhost' for table 't1' +UPDATE t1 SET a = 10; +ERROR 42000: UPDATE command denied to user 'privtest'@'localhost' for table 't1' +EXPLAIN UPDATE t1 SET a = 10; +ERROR 42000: UPDATE command denied to user 'privtest'@'localhost' for table 't1' +ANALYZE UPDATE t1 SET a = 10; +ERROR 42000: UPDATE command denied to user 'privtest'@'localhost' for table 't1' +UPDATE t1 SET a = a + 1; +ERROR 42000: UPDATE command denied to user 'privtest'@'localhost' for table 't1' +EXPLAIN UPDATE t1 SET a = a + 1; +ERROR 42000: UPDATE command denied to user 'privtest'@'localhost' for table 't1' +ANALYZE UPDATE t1 SET a = a + 1; +ERROR 42000: UPDATE command denied to user 'privtest'@'localhost' for table 't1' +UPDATE t1, t2 SET t1.a = t1.a + 1 WHERE t1.a = t2.a; +ERROR 42000: SELECT command denied to user 'privtest'@'localhost' for table 't1' +EXPLAIN UPDATE t1, t2 SET t1.a = t1.a + 1 WHERE t1.a = t2.a; +ERROR 42000: SELECT command denied to user 'privtest'@'localhost' for table 't1' +ANALYZE UPDATE t1, t2 SET t1.a = t1.a + 1 WHERE t1.a = t2.a; +ERROR 42000: SELECT command denied to user 'privtest'@'localhost' for table 't1' +DELETE FROM t1; +EXPLAIN DELETE FROM t1; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL 0 Deleting all rows +ANALYZE DELETE FROM t1; +id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL 0 NULL NULL NULL Deleting all rows +DELETE FROM t1 WHERE a = 10; +ERROR 42000: SELECT command denied to user 'privtest'@'localhost' for column 'a' in table 't1' +EXPLAIN DELETE FROM t1 WHERE a = 10; +ERROR 42000: SELECT command denied to user 'privtest'@'localhost' for column 'a' in table 't1' +ANALYZE DELETE FROM t1 WHERE a = 10; +ERROR 42000: SELECT command denied to user 'privtest'@'localhost' for column 'a' in table 't1' +DELETE FROM t1 USING t1, t2; +ERROR 42000: SELECT command denied to user 'privtest'@'localhost' for table 't1' +EXPLAIN DELETE FROM t1 USING t1, t2; +ERROR 42000: SELECT command denied to user 'privtest'@'localhost' for table 't1' +ANALYZE DELETE FROM t1 USING t1, t2; +ERROR 42000: SELECT command denied to user 'privtest'@'localhost' for table 't1' +DELETE FROM t1 USING t1, t2 WHERE t1.a = t2.a; +ERROR 42000: SELECT command denied to user 'privtest'@'localhost' for table 't1' +EXPLAIN DELETE FROM t1 USING t1, t2 WHERE t1.a = t2.a; +ERROR 42000: SELECT command denied to user 'privtest'@'localhost' for table 't1' +ANALYZE DELETE FROM t1 USING t1, t2 WHERE t1.a = t2.a; +ERROR 42000: SELECT command denied to user 'privtest'@'localhost' for table 't1' +SELECT * FROM t1; +ERROR 42000: SELECT command denied to user 'privtest'@'localhost' for table 't1' +EXPLAIN SELECT * FROM t1; +ERROR 42000: SELECT command denied to user 'privtest'@'localhost' for table 't1' +ANALYZE SELECT * FROM t1; +ERROR 42000: SELECT command denied to user 'privtest'@'localhost' for table 't1' +SELECT * FROM t1 WHERE a = 10; +ERROR 42000: SELECT command denied to user 'privtest'@'localhost' for table 't1' +EXPLAIN SELECT * FROM t1 WHERE a = 10; +ERROR 42000: SELECT command denied to user 'privtest'@'localhost' for table 't1' +ANALYZE SELECT * FROM t1 WHERE a = 10; +ERROR 42000: SELECT command denied to user 'privtest'@'localhost' for table 't1' +SELECT * FROM t1 WHERE a IN ( SELECT a FROM t2 ); +ERROR 42000: SELECT command denied to user 'privtest'@'localhost' for table 't1' +EXPLAIN SELECT * FROM t1 WHERE a IN ( SELECT a FROM t2 ); +ERROR 42000: SELECT command denied to user 'privtest'@'localhost' for table 't1' +ANALYZE SELECT * FROM t1 WHERE a IN ( SELECT a FROM t2 ); +ERROR 42000: SELECT command denied to user 'privtest'@'localhost' for table 't1' +#------------------------------------------------------------------------ +# I/R/U/D/S on the inner view +# Expectation: Can run the queries, but not EXPLAIN or ANALYZE +# because the query plan cannot be shown +# (it would have revealed the structure of the table) +#------------------------------------------------------------------------ +INSERT INTO v1 (a) VALUES (10); +EXPLAIN INSERT INTO v1 (a) VALUES (10); +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +ANALYZE INSERT INTO v1 (a) VALUES (10); +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +INSERT INTO v1 SELECT * FROM t2; +EXPLAIN INSERT INTO v1 SELECT * FROM t2; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +ANALYZE INSERT INTO v1 SELECT * FROM t2; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +REPLACE INTO v1 (a) VALUES (10); +EXPLAIN REPLACE INTO v1 (a) VALUES (10); +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +ANALYZE REPLACE INTO v1 (a) VALUES (10); +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +REPLACE INTO v1 SELECT * FROM t2; +EXPLAIN REPLACE INTO v1 SELECT * FROM t2; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +ANALYZE REPLACE INTO v1 SELECT * FROM t2; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +UPDATE v1 SET a = 10; +EXPLAIN UPDATE v1 SET a = 10; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +ANALYZE UPDATE v1 SET a = 10; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +UPDATE v1 SET a = a + 1; +EXPLAIN UPDATE v1 SET a = a + 1; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +ANALYZE UPDATE v1 SET a = a + 1; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +UPDATE v1, t2 SET v1.a = v1.a + 1 WHERE v1.a = t2.a; +EXPLAIN UPDATE v1, t2 SET v1.a = v1.a + 1 WHERE v1.a = t2.a; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +ANALYZE UPDATE v1, t2 SET v1.a = v1.a + 1 WHERE v1.a = t2.a; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +DELETE FROM v1; +EXPLAIN DELETE FROM v1; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +ANALYZE DELETE FROM v1; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +DELETE FROM v1 WHERE a = 10; +EXPLAIN DELETE FROM v1 WHERE a = 10; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +ANALYZE DELETE FROM v1 WHERE a = 10; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +DELETE FROM v1 USING v1, t2; +EXPLAIN DELETE FROM v1 USING v1, t2; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +ANALYZE DELETE FROM v1 USING v1, t2; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +DELETE FROM v1 USING v1, t2 WHERE v1.a = t2.a; +EXPLAIN DELETE FROM v1 USING v1, t2 WHERE v1.a = t2.a; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +ANALYZE DELETE FROM v1 USING v1, t2 WHERE v1.a = t2.a; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +SELECT * FROM v1; +a b +EXPLAIN SELECT * FROM v1; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +ANALYZE SELECT * FROM v1; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +SELECT * FROM v1 WHERE a = 10; +a b +EXPLAIN SELECT * FROM v1 WHERE a = 10; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +ANALYZE SELECT * FROM v1 WHERE a = 10; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +SELECT * FROM v1 WHERE a IN ( SELECT a FROM t2 ); +a b +EXPLAIN SELECT * FROM v1 WHERE a IN ( SELECT a FROM t2 ); +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +ANALYZE SELECT * FROM v1 WHERE a IN ( SELECT a FROM t2 ); +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +#------------------------------------------------------------------------ +# I/R/U/D/S on the outer view +# Expectation: Can run the queries, but not EXPLAIN or ANALYZE +# because the query plan cannot be shown +# (it would have revealed the structure of the table) +#------------------------------------------------------------------------ +INSERT INTO v2 (a) VALUES (10); +EXPLAIN INSERT INTO v2 (a) VALUES (10); +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +ANALYZE INSERT INTO v2 (a) VALUES (10); +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +INSERT INTO v2 SELECT * FROM t2; +EXPLAIN INSERT INTO v2 SELECT * FROM t2; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +ANALYZE INSERT INTO v2 SELECT * FROM t2; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +REPLACE INTO v2 (a) VALUES (10); +EXPLAIN REPLACE INTO v2 (a) VALUES (10); +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +ANALYZE REPLACE INTO v2 (a) VALUES (10); +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +REPLACE INTO v2 SELECT * FROM t2; +EXPLAIN REPLACE INTO v2 SELECT * FROM t2; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +ANALYZE REPLACE INTO v2 SELECT * FROM t2; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +UPDATE v2 SET a = 10; +EXPLAIN UPDATE v2 SET a = 10; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +ANALYZE UPDATE v2 SET a = 10; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +UPDATE v2 SET a = a + 1; +EXPLAIN UPDATE v2 SET a = a + 1; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +ANALYZE UPDATE v2 SET a = a + 1; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +UPDATE v2, t2 SET v2.a = v2.a + 1 WHERE v2.a = t2.a; +EXPLAIN UPDATE v2, t2 SET v2.a = v2.a + 1 WHERE v2.a = t2.a; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +ANALYZE UPDATE v2, t2 SET v2.a = v2.a + 1 WHERE v2.a = t2.a; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +DELETE FROM v2; +EXPLAIN DELETE FROM v2; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +ANALYZE DELETE FROM v2; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +DELETE FROM v2 WHERE a = 10; +EXPLAIN DELETE FROM v2 WHERE a = 10; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +ANALYZE DELETE FROM v2 WHERE a = 10; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +DELETE FROM v2 USING v2, t2; +EXPLAIN DELETE FROM v2 USING v2, t2; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +ANALYZE DELETE FROM v2 USING v2, t2; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +DELETE FROM v2 USING v2, t2 WHERE v2.a = t2.a; +EXPLAIN DELETE FROM v2 USING v2, t2 WHERE v2.a = t2.a; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +ANALYZE DELETE FROM v2 USING v2, t2 WHERE v2.a = t2.a; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +SELECT * FROM v2; +a b +EXPLAIN SELECT * FROM v2; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +ANALYZE SELECT * FROM v2; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +SELECT * FROM v2 WHERE a = 10; +a b +EXPLAIN SELECT * FROM v2 WHERE a = 10; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +ANALYZE SELECT * FROM v2 WHERE a = 10; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +SELECT * FROM v2 WHERE a IN ( SELECT a FROM t2 ); +a b +EXPLAIN SELECT * FROM v2 WHERE a IN ( SELECT a FROM t2 ); +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +ANALYZE SELECT * FROM v2 WHERE a IN ( SELECT a FROM t2 ); +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table + +#======================================================================== +# Test: Grant DELETE, SELECT(a) on the table +#======================================================================== + +connection default; +REVOKE ALL PRIVILEGES ON privtest_db.t1 FROM 'privtest'@'localhost'; +GRANT SELECT(a), DELETE ON privtest_db.t1 TO 'privtest'@'localhost'; +connection con1; +#------------------------------------------------------------------------ +# I/R/U/D/S on the table +# Expectation: Can run DELETE, EXPLAIN DELETE, ANALYZE DELETE +#------------------------------------------------------------------------ +INSERT INTO t1 (a) VALUES (10); +ERROR 42000: INSERT command denied to user 'privtest'@'localhost' for table 't1' +EXPLAIN INSERT INTO t1 (a) VALUES (10); +ERROR 42000: INSERT command denied to user 'privtest'@'localhost' for table 't1' +ANALYZE INSERT INTO t1 (a) VALUES (10); +ERROR 42000: INSERT command denied to user 'privtest'@'localhost' for table 't1' +INSERT INTO t1 SELECT * FROM t2; +ERROR 42000: INSERT command denied to user 'privtest'@'localhost' for table 't1' +EXPLAIN INSERT INTO t1 SELECT * FROM t2; +ERROR 42000: INSERT command denied to user 'privtest'@'localhost' for table 't1' +ANALYZE INSERT INTO t1 SELECT * FROM t2; +ERROR 42000: INSERT command denied to user 'privtest'@'localhost' for table 't1' +REPLACE INTO t1 (a) VALUES (10); +ERROR 42000: INSERT command denied to user 'privtest'@'localhost' for table 't1' +EXPLAIN REPLACE INTO t1 (a) VALUES (10); +ERROR 42000: INSERT command denied to user 'privtest'@'localhost' for table 't1' +ANALYZE REPLACE INTO t1 (a) VALUES (10); +ERROR 42000: INSERT command denied to user 'privtest'@'localhost' for table 't1' +REPLACE INTO t1 SELECT * FROM t2; +ERROR 42000: INSERT command denied to user 'privtest'@'localhost' for table 't1' +EXPLAIN REPLACE INTO t1 SELECT * FROM t2; +ERROR 42000: INSERT command denied to user 'privtest'@'localhost' for table 't1' +ANALYZE REPLACE INTO t1 SELECT * FROM t2; +ERROR 42000: INSERT command denied to user 'privtest'@'localhost' for table 't1' +UPDATE t1 SET a = 10; +ERROR 42000: UPDATE command denied to user 'privtest'@'localhost' for table 't1' +EXPLAIN UPDATE t1 SET a = 10; +ERROR 42000: UPDATE command denied to user 'privtest'@'localhost' for table 't1' +ANALYZE UPDATE t1 SET a = 10; +ERROR 42000: UPDATE command denied to user 'privtest'@'localhost' for table 't1' +UPDATE t1 SET a = a + 1; +ERROR 42000: UPDATE command denied to user 'privtest'@'localhost' for table 't1' +EXPLAIN UPDATE t1 SET a = a + 1; +ERROR 42000: UPDATE command denied to user 'privtest'@'localhost' for table 't1' +ANALYZE UPDATE t1 SET a = a + 1; +ERROR 42000: UPDATE command denied to user 'privtest'@'localhost' for table 't1' +UPDATE t1, t2 SET t1.a = t1.a + 1 WHERE t1.a = t2.a; +ERROR 42000: UPDATE command denied to user 'privtest'@'localhost' for table 't1' +EXPLAIN UPDATE t1, t2 SET t1.a = t1.a + 1 WHERE t1.a = t2.a; +ERROR 42000: UPDATE command denied to user 'privtest'@'localhost' for table 't1' +ANALYZE UPDATE t1, t2 SET t1.a = t1.a + 1 WHERE t1.a = t2.a; +ERROR 42000: UPDATE command denied to user 'privtest'@'localhost' for table 't1' +DELETE FROM t1; +EXPLAIN DELETE FROM t1; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL 0 Deleting all rows +ANALYZE DELETE FROM t1; +id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL 0 NULL NULL NULL Deleting all rows +DELETE FROM t1 WHERE a = 10; +EXPLAIN DELETE FROM t1 WHERE a = 10; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 1 Using where +ANALYZE DELETE FROM t1 WHERE a = 10; +id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 1 0.00 100.00 100.00 Using where +DELETE FROM t1 USING t1, t2; +EXPLAIN DELETE FROM t1 USING t1, t2; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 system NULL NULL NULL NULL 0 Const row not found +1 SIMPLE t2 ALL NULL NULL NULL NULL 3 +ANALYZE DELETE FROM t1 USING t1, t2; +id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL no matching row in const table +DELETE FROM t1 USING t1, t2 WHERE t1.a = t2.a; +EXPLAIN DELETE FROM t1 USING t1, t2 WHERE t1.a = t2.a; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +ANALYZE DELETE FROM t1 USING t1, t2 WHERE t1.a = t2.a; +id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL no matching row in const table +SELECT * FROM t1; +ERROR 42000: SELECT command denied to user 'privtest'@'localhost' for table 't1' +EXPLAIN SELECT * FROM t1; +ERROR 42000: SELECT command denied to user 'privtest'@'localhost' for table 't1' +ANALYZE SELECT * FROM t1; +ERROR 42000: SELECT command denied to user 'privtest'@'localhost' for table 't1' +SELECT * FROM t1 WHERE a = 10; +ERROR 42000: SELECT command denied to user 'privtest'@'localhost' for table 't1' +EXPLAIN SELECT * FROM t1 WHERE a = 10; +ERROR 42000: SELECT command denied to user 'privtest'@'localhost' for table 't1' +ANALYZE SELECT * FROM t1 WHERE a = 10; +ERROR 42000: SELECT command denied to user 'privtest'@'localhost' for table 't1' +SELECT * FROM t1 WHERE a IN ( SELECT a FROM t2 ); +ERROR 42000: SELECT command denied to user 'privtest'@'localhost' for table 't1' +EXPLAIN SELECT * FROM t1 WHERE a IN ( SELECT a FROM t2 ); +ERROR 42000: SELECT command denied to user 'privtest'@'localhost' for table 't1' +ANALYZE SELECT * FROM t1 WHERE a IN ( SELECT a FROM t2 ); +ERROR 42000: SELECT command denied to user 'privtest'@'localhost' for table 't1' +#------------------------------------------------------------------------ +# I/R/U/D/S on the inner view +# Expectation: Can run everything: SELECT access to the column `a` +# in the underlying table is enough to show EXPLAIN +# (that's how it works now) +#------------------------------------------------------------------------ +INSERT INTO v1 (a) VALUES (10); +EXPLAIN INSERT INTO v1 (a) VALUES (10); +id select_type table type possible_keys key key_len ref rows Extra +1 INSERT t1 ALL NULL NULL NULL NULL NULL NULL +ANALYZE INSERT INTO v1 (a) VALUES (10); +id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra +1 INSERT t1 ALL NULL NULL NULL NULL NULL NULL 100.00 100.00 NULL +INSERT INTO v1 SELECT * FROM t2; +EXPLAIN INSERT INTO v1 SELECT * FROM t2; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t2 ALL NULL NULL NULL NULL 3 +ANALYZE INSERT INTO v1 SELECT * FROM t2; +id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra +1 SIMPLE t2 ALL NULL NULL NULL NULL 3 3.00 100.00 100.00 +REPLACE INTO v1 (a) VALUES (10); +EXPLAIN REPLACE INTO v1 (a) VALUES (10); +id select_type table type possible_keys key key_len ref rows Extra +1 INSERT t1 ALL NULL NULL NULL NULL NULL NULL +ANALYZE REPLACE INTO v1 (a) VALUES (10); +id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra +1 INSERT t1 ALL NULL NULL NULL NULL NULL NULL 100.00 100.00 NULL +REPLACE INTO v1 SELECT * FROM t2; +EXPLAIN REPLACE INTO v1 SELECT * FROM t2; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t2 ALL NULL NULL NULL NULL 3 +ANALYZE REPLACE INTO v1 SELECT * FROM t2; +id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra +1 SIMPLE t2 ALL NULL NULL NULL NULL 3 3.00 100.00 100.00 +UPDATE v1 SET a = 10; +EXPLAIN UPDATE v1 SET a = 10; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 16 Using where +ANALYZE UPDATE v1 SET a = 10; +id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 16 16.00 100.00 75.00 Using where +UPDATE v1 SET a = a + 1; +EXPLAIN UPDATE v1 SET a = a + 1; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 16 Using where +ANALYZE UPDATE v1 SET a = a + 1; +id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 16 16.00 100.00 75.00 Using where +UPDATE v1, t2 SET v1.a = v1.a + 1 WHERE v1.a = t2.a; +EXPLAIN UPDATE v1, t2 SET v1.a = v1.a + 1 WHERE v1.a = t2.a; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t2 ALL NULL NULL NULL NULL 3 +1 SIMPLE t1 ALL NULL NULL NULL NULL 16 Using where +ANALYZE UPDATE v1, t2 SET v1.a = v1.a + 1 WHERE v1.a = t2.a; +id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra +1 SIMPLE t2 ALL NULL NULL NULL NULL 3 3.00 100.00 100.00 +1 SIMPLE t1 ALL NULL NULL NULL NULL 16 16.00 100.00 0.00 Using where +DELETE FROM v1; +EXPLAIN DELETE FROM v1; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 4 Using where +ANALYZE DELETE FROM v1; +id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 4 4.00 100.00 0.00 Using where +DELETE FROM v1 WHERE a = 10; +EXPLAIN DELETE FROM v1 WHERE a = 10; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 4 Using where +ANALYZE DELETE FROM v1 WHERE a = 10; +id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 4 4.00 100.00 0.00 Using where +DELETE FROM v1 USING v1, t2; +EXPLAIN DELETE FROM v1 USING v1, t2; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t2 ALL NULL NULL NULL NULL 3 +1 SIMPLE t1 ALL NULL NULL NULL NULL 4 Using where +ANALYZE DELETE FROM v1 USING v1, t2; +id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra +1 SIMPLE t2 ALL NULL NULL NULL NULL 3 3.00 100.00 100.00 +1 SIMPLE t1 ALL NULL NULL NULL NULL 4 4.00 100.00 0.00 Using where +DELETE FROM v1 USING v1, t2 WHERE v1.a = t2.a; +EXPLAIN DELETE FROM v1 USING v1, t2 WHERE v1.a = t2.a; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t2 ALL NULL NULL NULL NULL 3 +1 SIMPLE t1 ALL NULL NULL NULL NULL 4 Using where +ANALYZE DELETE FROM v1 USING v1, t2 WHERE v1.a = t2.a; +id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra +1 SIMPLE t2 ALL NULL NULL NULL NULL 3 3.00 100.00 100.00 +1 SIMPLE t1 ALL NULL NULL NULL NULL 4 4.00 100.00 0.00 Using where +SELECT * FROM v1; +a b +EXPLAIN SELECT * FROM v1; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 4 Using where +ANALYZE SELECT * FROM v1; +id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 4 4.00 100.00 0.00 Using where +SELECT * FROM v1 WHERE a = 10; +a b +EXPLAIN SELECT * FROM v1 WHERE a = 10; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 4 Using where +ANALYZE SELECT * FROM v1 WHERE a = 10; +id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 4 4.00 100.00 0.00 Using where +SELECT * FROM v1 WHERE a IN ( SELECT a FROM t2 ); +a b +EXPLAIN SELECT * FROM v1 WHERE a IN ( SELECT a FROM t2 ); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 3 +1 PRIMARY t1 ALL NULL NULL NULL NULL 4 Using where; Using join buffer (flat, BNL join) +2 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 +ANALYZE SELECT * FROM v1 WHERE a IN ( SELECT a FROM t2 ); +id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra +1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 3 3.00 100.00 100.00 +1 PRIMARY t1 ALL NULL NULL NULL NULL 4 4.00 100.00 0.00 Using where; Using join buffer (flat, BNL join) +2 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 3.00 100.00 100.00 +#------------------------------------------------------------------------ +# I/R/U/D/S on the outer view +# Expectation: Can run everything: SELECT access to the column `a` +# in the underlying table is enough to show EXPLAIN +# (that's how it works now) +#------------------------------------------------------------------------ +INSERT INTO v2 (a) VALUES (10); +EXPLAIN INSERT INTO v2 (a) VALUES (10); +id select_type table type possible_keys key key_len ref rows Extra +1 INSERT t1 ALL NULL NULL NULL NULL NULL NULL +ANALYZE INSERT INTO v2 (a) VALUES (10); +id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra +1 INSERT t1 ALL NULL NULL NULL NULL NULL NULL 100.00 100.00 NULL +INSERT INTO v2 SELECT * FROM t2; +EXPLAIN INSERT INTO v2 SELECT * FROM t2; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t2 ALL NULL NULL NULL NULL 3 +ANALYZE INSERT INTO v2 SELECT * FROM t2; +id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra +1 SIMPLE t2 ALL NULL NULL NULL NULL 3 3.00 100.00 100.00 +REPLACE INTO v2 (a) VALUES (10); +EXPLAIN REPLACE INTO v2 (a) VALUES (10); +id select_type table type possible_keys key key_len ref rows Extra +1 INSERT t1 ALL NULL NULL NULL NULL NULL NULL +ANALYZE REPLACE INTO v2 (a) VALUES (10); +id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra +1 INSERT t1 ALL NULL NULL NULL NULL NULL NULL 100.00 100.00 NULL +REPLACE INTO v2 SELECT * FROM t2; +EXPLAIN REPLACE INTO v2 SELECT * FROM t2; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t2 ALL NULL NULL NULL NULL 3 +ANALYZE REPLACE INTO v2 SELECT * FROM t2; +id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra +1 SIMPLE t2 ALL NULL NULL NULL NULL 3 3.00 100.00 100.00 +UPDATE v2 SET a = 10; +EXPLAIN UPDATE v2 SET a = 10; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 20 Using where +ANALYZE UPDATE v2 SET a = 10; +id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 20 20.00 100.00 60.00 Using where +UPDATE v2 SET a = a + 1; +EXPLAIN UPDATE v2 SET a = a + 1; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 20 Using where +ANALYZE UPDATE v2 SET a = a + 1; +id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 20 20.00 100.00 60.00 Using where +UPDATE v2, t2 SET v2.a = v2.a + 1 WHERE v2.a = t2.a; +EXPLAIN UPDATE v2, t2 SET v2.a = v2.a + 1 WHERE v2.a = t2.a; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t2 ALL NULL NULL NULL NULL 3 Using where +1 SIMPLE t1 ALL NULL NULL NULL NULL 20 Using where +ANALYZE UPDATE v2, t2 SET v2.a = v2.a + 1 WHERE v2.a = t2.a; +id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra +1 SIMPLE t2 ALL NULL NULL NULL NULL 3 3.00 100.00 100.00 Using where +1 SIMPLE t1 ALL NULL NULL NULL NULL 20 20.00 100.00 0.00 Using where +DELETE FROM v2; +EXPLAIN DELETE FROM v2; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 8 Using where +ANALYZE DELETE FROM v2; +id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 8 8.00 100.00 0.00 Using where +DELETE FROM v2 WHERE a = 10; +EXPLAIN DELETE FROM v2 WHERE a = 10; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 8 Using where +ANALYZE DELETE FROM v2 WHERE a = 10; +id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 8 8.00 100.00 0.00 Using where +DELETE FROM v2 USING v2, t2; +EXPLAIN DELETE FROM v2 USING v2, t2; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t2 ALL NULL NULL NULL NULL 3 +1 SIMPLE t1 ALL NULL NULL NULL NULL 8 Using where +ANALYZE DELETE FROM v2 USING v2, t2; +id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra +1 SIMPLE t2 ALL NULL NULL NULL NULL 3 3.00 100.00 100.00 +1 SIMPLE t1 ALL NULL NULL NULL NULL 8 8.00 100.00 0.00 Using where +DELETE FROM v2 USING v2, t2 WHERE v2.a = t2.a; +EXPLAIN DELETE FROM v2 USING v2, t2 WHERE v2.a = t2.a; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t2 ALL NULL NULL NULL NULL 3 Using where +1 SIMPLE t1 ALL NULL NULL NULL NULL 8 Using where +ANALYZE DELETE FROM v2 USING v2, t2 WHERE v2.a = t2.a; +id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra +1 SIMPLE t2 ALL NULL NULL NULL NULL 3 3.00 100.00 100.00 Using where +1 SIMPLE t1 ALL NULL NULL NULL NULL 8 8.00 100.00 0.00 Using where +SELECT * FROM v2; +a b +EXPLAIN SELECT * FROM v2; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 8 Using where +ANALYZE SELECT * FROM v2; +id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 8 8.00 100.00 0.00 Using where +SELECT * FROM v2 WHERE a = 10; +a b +EXPLAIN SELECT * FROM v2 WHERE a = 10; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 8 Using where +ANALYZE SELECT * FROM v2 WHERE a = 10; +id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 8 8.00 100.00 0.00 Using where +SELECT * FROM v2 WHERE a IN ( SELECT a FROM t2 ); +a b +EXPLAIN SELECT * FROM v2 WHERE a IN ( SELECT a FROM t2 ); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 3 +1 PRIMARY t1 ALL NULL NULL NULL NULL 8 Using where; Using join buffer (flat, BNL join) +2 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 Using where +ANALYZE SELECT * FROM v2 WHERE a IN ( SELECT a FROM t2 ); +id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra +1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 3 3.00 100.00 100.00 +1 PRIMARY t1 ALL NULL NULL NULL NULL 8 8.00 100.00 0.00 Using where; Using join buffer (flat, BNL join) +2 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 3.00 100.00 100.00 Using where + +#======================================================================== +# Test: Grant SELECT, INSERT, UPDATE, DELETE on the table +#======================================================================== + +connection default; +REVOKE ALL PRIVILEGES ON privtest_db.t1 FROM 'privtest'@'localhost'; +GRANT SELECT, INSERT, UPDATE, DELETE ON privtest_db.t1 TO 'privtest'@'localhost'; +connection con1; +#------------------------------------------------------------------------ +# I/R/U/D/S on the table +# Expectation: Can run everything +#------------------------------------------------------------------------ +INSERT INTO t1 (a) VALUES (10); +EXPLAIN INSERT INTO t1 (a) VALUES (10); +id select_type table type possible_keys key key_len ref rows Extra +1 INSERT t1 ALL NULL NULL NULL NULL NULL NULL +ANALYZE INSERT INTO t1 (a) VALUES (10); +id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra +1 INSERT t1 ALL NULL NULL NULL NULL NULL NULL 100.00 100.00 NULL +INSERT INTO t1 SELECT * FROM t2; +EXPLAIN INSERT INTO t1 SELECT * FROM t2; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t2 ALL NULL NULL NULL NULL 3 +ANALYZE INSERT INTO t1 SELECT * FROM t2; +id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra +1 SIMPLE t2 ALL NULL NULL NULL NULL 3 3.00 100.00 100.00 +REPLACE INTO t1 (a) VALUES (10); +EXPLAIN REPLACE INTO t1 (a) VALUES (10); +id select_type table type possible_keys key key_len ref rows Extra +1 INSERT t1 ALL NULL NULL NULL NULL NULL NULL +ANALYZE REPLACE INTO t1 (a) VALUES (10); +id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra +1 INSERT t1 ALL NULL NULL NULL NULL NULL NULL 100.00 100.00 NULL +REPLACE INTO t1 SELECT * FROM t2; +EXPLAIN REPLACE INTO t1 SELECT * FROM t2; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t2 ALL NULL NULL NULL NULL 3 +ANALYZE REPLACE INTO t1 SELECT * FROM t2; +id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra +1 SIMPLE t2 ALL NULL NULL NULL NULL 3 3.00 100.00 100.00 +UPDATE t1 SET a = 10; +EXPLAIN UPDATE t1 SET a = 10; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 24 +ANALYZE UPDATE t1 SET a = 10; +id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 24 24.00 100.00 100.00 +UPDATE t1 SET a = a + 1; +EXPLAIN UPDATE t1 SET a = a + 1; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 24 +ANALYZE UPDATE t1 SET a = a + 1; +id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 24 24.00 100.00 100.00 +UPDATE t1, t2 SET t1.a = t1.a + 1 WHERE t1.a = t2.a; +EXPLAIN UPDATE t1, t2 SET t1.a = t1.a + 1 WHERE t1.a = t2.a; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t2 ALL NULL NULL NULL NULL 3 +1 SIMPLE t1 ALL NULL NULL NULL NULL 24 Using where +ANALYZE UPDATE t1, t2 SET t1.a = t1.a + 1 WHERE t1.a = t2.a; +id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra +1 SIMPLE t2 ALL NULL NULL NULL NULL 3 3.00 100.00 100.00 +1 SIMPLE t1 ALL NULL NULL NULL NULL 24 24.00 100.00 0.00 Using where +DELETE FROM t1; +EXPLAIN DELETE FROM t1; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL 0 Deleting all rows +ANALYZE DELETE FROM t1; +id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL 0 NULL NULL NULL Deleting all rows +DELETE FROM t1 WHERE a = 10; +EXPLAIN DELETE FROM t1 WHERE a = 10; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 1 Using where +ANALYZE DELETE FROM t1 WHERE a = 10; +id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 1 0.00 100.00 100.00 Using where +DELETE FROM t1 USING t1, t2; +EXPLAIN DELETE FROM t1 USING t1, t2; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 system NULL NULL NULL NULL 0 Const row not found +1 SIMPLE t2 ALL NULL NULL NULL NULL 3 +ANALYZE DELETE FROM t1 USING t1, t2; +id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL no matching row in const table +DELETE FROM t1 USING t1, t2 WHERE t1.a = t2.a; +EXPLAIN DELETE FROM t1 USING t1, t2 WHERE t1.a = t2.a; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +ANALYZE DELETE FROM t1 USING t1, t2 WHERE t1.a = t2.a; +id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL no matching row in const table +SELECT * FROM t1; +a b +EXPLAIN SELECT * FROM t1; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 system NULL NULL NULL NULL 0 Const row not found +ANALYZE SELECT * FROM t1; +id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL no matching row in const table +SELECT * FROM t1 WHERE a = 10; +a b +EXPLAIN SELECT * FROM t1 WHERE a = 10; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +ANALYZE SELECT * FROM t1 WHERE a = 10; +id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL no matching row in const table +SELECT * FROM t1 WHERE a IN ( SELECT a FROM t2 ); +a b +EXPLAIN SELECT * FROM t1 WHERE a IN ( SELECT a FROM t2 ); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +ANALYZE SELECT * FROM t1 WHERE a IN ( SELECT a FROM t2 ); +id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL no matching row in const table +#------------------------------------------------------------------------ +# I/R/U/D/S on the inner view +# Expectation: Can run everything +#------------------------------------------------------------------------ +INSERT INTO v1 (a) VALUES (10); +EXPLAIN INSERT INTO v1 (a) VALUES (10); +id select_type table type possible_keys key key_len ref rows Extra +1 INSERT t1 ALL NULL NULL NULL NULL NULL NULL +ANALYZE INSERT INTO v1 (a) VALUES (10); +id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra +1 INSERT t1 ALL NULL NULL NULL NULL NULL NULL 100.00 100.00 NULL +INSERT INTO v1 SELECT * FROM t2; +EXPLAIN INSERT INTO v1 SELECT * FROM t2; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t2 ALL NULL NULL NULL NULL 3 +ANALYZE INSERT INTO v1 SELECT * FROM t2; +id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra +1 SIMPLE t2 ALL NULL NULL NULL NULL 3 3.00 100.00 100.00 +REPLACE INTO v1 (a) VALUES (10); +EXPLAIN REPLACE INTO v1 (a) VALUES (10); +id select_type table type possible_keys key key_len ref rows Extra +1 INSERT t1 ALL NULL NULL NULL NULL NULL NULL +ANALYZE REPLACE INTO v1 (a) VALUES (10); +id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra +1 INSERT t1 ALL NULL NULL NULL NULL NULL NULL 100.00 100.00 NULL +REPLACE INTO v1 SELECT * FROM t2; +EXPLAIN REPLACE INTO v1 SELECT * FROM t2; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t2 ALL NULL NULL NULL NULL 3 +ANALYZE REPLACE INTO v1 SELECT * FROM t2; +id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra +1 SIMPLE t2 ALL NULL NULL NULL NULL 3 3.00 100.00 100.00 +UPDATE v1 SET a = 10; +EXPLAIN UPDATE v1 SET a = 10; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 16 Using where +ANALYZE UPDATE v1 SET a = 10; +id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 16 16.00 100.00 75.00 Using where +UPDATE v1 SET a = a + 1; +EXPLAIN UPDATE v1 SET a = a + 1; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 16 Using where +ANALYZE UPDATE v1 SET a = a + 1; +id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 16 16.00 100.00 75.00 Using where +UPDATE v1, t2 SET v1.a = v1.a + 1 WHERE v1.a = t2.a; +EXPLAIN UPDATE v1, t2 SET v1.a = v1.a + 1 WHERE v1.a = t2.a; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t2 ALL NULL NULL NULL NULL 3 +1 SIMPLE t1 ALL NULL NULL NULL NULL 16 Using where +ANALYZE UPDATE v1, t2 SET v1.a = v1.a + 1 WHERE v1.a = t2.a; +id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra +1 SIMPLE t2 ALL NULL NULL NULL NULL 3 3.00 100.00 100.00 +1 SIMPLE t1 ALL NULL NULL NULL NULL 16 16.00 100.00 0.00 Using where +DELETE FROM v1; +EXPLAIN DELETE FROM v1; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 4 Using where +ANALYZE DELETE FROM v1; +id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 4 4.00 100.00 0.00 Using where +DELETE FROM v1 WHERE a = 10; +EXPLAIN DELETE FROM v1 WHERE a = 10; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 4 Using where +ANALYZE DELETE FROM v1 WHERE a = 10; +id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 4 4.00 100.00 0.00 Using where +DELETE FROM v1 USING v1, t2; +EXPLAIN DELETE FROM v1 USING v1, t2; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t2 ALL NULL NULL NULL NULL 3 +1 SIMPLE t1 ALL NULL NULL NULL NULL 4 Using where +ANALYZE DELETE FROM v1 USING v1, t2; +id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra +1 SIMPLE t2 ALL NULL NULL NULL NULL 3 3.00 100.00 100.00 +1 SIMPLE t1 ALL NULL NULL NULL NULL 4 4.00 100.00 0.00 Using where +DELETE FROM v1 USING v1, t2 WHERE v1.a = t2.a; +EXPLAIN DELETE FROM v1 USING v1, t2 WHERE v1.a = t2.a; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t2 ALL NULL NULL NULL NULL 3 +1 SIMPLE t1 ALL NULL NULL NULL NULL 4 Using where +ANALYZE DELETE FROM v1 USING v1, t2 WHERE v1.a = t2.a; +id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra +1 SIMPLE t2 ALL NULL NULL NULL NULL 3 3.00 100.00 100.00 +1 SIMPLE t1 ALL NULL NULL NULL NULL 4 4.00 100.00 0.00 Using where +SELECT * FROM v1; +a b +EXPLAIN SELECT * FROM v1; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 4 Using where +ANALYZE SELECT * FROM v1; +id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 4 4.00 100.00 0.00 Using where +SELECT * FROM v1 WHERE a = 10; +a b +EXPLAIN SELECT * FROM v1 WHERE a = 10; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 4 Using where +ANALYZE SELECT * FROM v1 WHERE a = 10; +id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 4 4.00 100.00 0.00 Using where +SELECT * FROM v1 WHERE a IN ( SELECT a FROM t2 ); +a b +EXPLAIN SELECT * FROM v1 WHERE a IN ( SELECT a FROM t2 ); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 3 +1 PRIMARY t1 ALL NULL NULL NULL NULL 4 Using where; Using join buffer (flat, BNL join) +2 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 +ANALYZE SELECT * FROM v1 WHERE a IN ( SELECT a FROM t2 ); +id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra +1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 3 3.00 100.00 100.00 +1 PRIMARY t1 ALL NULL NULL NULL NULL 4 4.00 100.00 0.00 Using where; Using join buffer (flat, BNL join) +2 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 3.00 100.00 100.00 +#------------------------------------------------------------------------ +# I/R/U/D/S on the outer view +# Expectation: Can run everything +#------------------------------------------------------------------------ +# Note: some queries are commented due to MDEV-7034 +INSERT INTO v2 (a) VALUES (10); +EXPLAIN INSERT INTO v2 (a) VALUES (10); +id select_type table type possible_keys key key_len ref rows Extra +1 INSERT t1 ALL NULL NULL NULL NULL NULL NULL +ANALYZE INSERT INTO v2 (a) VALUES (10); +id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra +1 INSERT t1 ALL NULL NULL NULL NULL NULL NULL 100.00 100.00 NULL +INSERT INTO v2 SELECT * FROM t2; +EXPLAIN INSERT INTO v2 SELECT * FROM t2; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t2 ALL NULL NULL NULL NULL 3 +ANALYZE INSERT INTO v2 SELECT * FROM t2; +id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra +1 SIMPLE t2 ALL NULL NULL NULL NULL 3 3.00 100.00 100.00 +REPLACE INTO v2 (a) VALUES (10); +EXPLAIN REPLACE INTO v2 (a) VALUES (10); +id select_type table type possible_keys key key_len ref rows Extra +1 INSERT t1 ALL NULL NULL NULL NULL NULL NULL +ANALYZE REPLACE INTO v2 (a) VALUES (10); +id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra +1 INSERT t1 ALL NULL NULL NULL NULL NULL NULL 100.00 100.00 NULL +REPLACE INTO v2 SELECT * FROM t2; +EXPLAIN REPLACE INTO v2 SELECT * FROM t2; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t2 ALL NULL NULL NULL NULL 3 +ANALYZE REPLACE INTO v2 SELECT * FROM t2; +id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra +1 SIMPLE t2 ALL NULL NULL NULL NULL 3 3.00 100.00 100.00 +UPDATE v2 SET a = 10; +EXPLAIN UPDATE v2 SET a = 10; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 20 Using where +ANALYZE UPDATE v2 SET a = 10; +id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 20 20.00 100.00 60.00 Using where +UPDATE v2 SET a = a + 1; +EXPLAIN UPDATE v2 SET a = a + 1; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 20 Using where +ANALYZE UPDATE v2 SET a = a + 1; +id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 20 20.00 100.00 60.00 Using where +UPDATE v2, t2 SET v2.a = v2.a + 1 WHERE v2.a = t2.a; +EXPLAIN UPDATE v2, t2 SET v2.a = v2.a + 1 WHERE v2.a = t2.a; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t2 ALL NULL NULL NULL NULL 3 Using where +1 SIMPLE t1 ALL NULL NULL NULL NULL 20 Using where +ANALYZE UPDATE v2, t2 SET v2.a = v2.a + 1 WHERE v2.a = t2.a; +id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra +1 SIMPLE t2 ALL NULL NULL NULL NULL 3 3.00 100.00 100.00 Using where +1 SIMPLE t1 ALL NULL NULL NULL NULL 20 20.00 100.00 0.00 Using where +DELETE FROM v2; +EXPLAIN DELETE FROM v2; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 8 Using where +ANALYZE DELETE FROM v2; +id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 8 8.00 100.00 0.00 Using where +DELETE FROM v2 WHERE a = 10; +EXPLAIN DELETE FROM v2 WHERE a = 10; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 8 Using where +ANALYZE DELETE FROM v2 WHERE a = 10; +id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 8 8.00 100.00 0.00 Using where +DELETE FROM v2 USING v2, t2; +ANALYZE DELETE FROM v2 USING v2, t2; +id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra +1 SIMPLE t2 ALL NULL NULL NULL NULL 3 3.00 100.00 100.00 +1 SIMPLE t1 ALL NULL NULL NULL NULL 8 8.00 100.00 0.00 Using where +DELETE FROM v2 USING v2, t2 WHERE v2.a = t2.a; +ANALYZE DELETE FROM v2 USING v2, t2 WHERE v2.a = t2.a; +id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra +1 SIMPLE t2 ALL NULL NULL NULL NULL 3 3.00 100.00 100.00 Using where +1 SIMPLE t1 ALL NULL NULL NULL NULL 8 8.00 100.00 0.00 Using where +SELECT * FROM v2; +a b +EXPLAIN SELECT * FROM v2; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 8 Using where +ANALYZE SELECT * FROM v2; +id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 8 8.00 100.00 0.00 Using where +SELECT * FROM v2 WHERE a = 10; +a b +EXPLAIN SELECT * FROM v2 WHERE a = 10; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 8 Using where +ANALYZE SELECT * FROM v2 WHERE a = 10; +id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 8 8.00 100.00 0.00 Using where +SELECT * FROM v2 WHERE a IN ( SELECT a FROM t2 ); +a b +EXPLAIN SELECT * FROM v2 WHERE a IN ( SELECT a FROM t2 ); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 3 +1 PRIMARY t1 ALL NULL NULL NULL NULL 8 Using where; Using join buffer (flat, BNL join) +2 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 Using where +ANALYZE SELECT * FROM v2 WHERE a IN ( SELECT a FROM t2 ); +id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra +1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 3 3.00 100.00 100.00 +1 PRIMARY t1 ALL NULL NULL NULL NULL 8 8.00 100.00 0.00 Using where; Using join buffer (flat, BNL join) +2 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 3.00 100.00 100.00 Using where + +######################################################################### +# Inner view permission tests +# (we modify permissions on the inner view, keeping ALL the rest) +######################################################################### + + +#======================================================================== +# Test: No permissions on the inner view +#======================================================================== + +connection default; +GRANT ALL ON privtest_db.t1 TO 'privtest'@'localhost'; +GRANT ALL ON privtest_db.v2 TO 'privtest'@'localhost'; +REVOKE ALL PRIVILEGES ON privtest_db.v1 FROM 'privtest'@'localhost'; +connection con1; +#------------------------------------------------------------------------ +# I/R/U/D/S on the inner view +# Expectation: Cannot run anything +#------------------------------------------------------------------------ +INSERT INTO v1 (a) VALUES (10); +ERROR 42000: INSERT command denied to user 'privtest'@'localhost' for table 'v1' +EXPLAIN INSERT INTO v1 (a) VALUES (10); +ERROR 42000: INSERT command denied to user 'privtest'@'localhost' for table 'v1' +ANALYZE INSERT INTO v1 (a) VALUES (10); +ERROR 42000: INSERT command denied to user 'privtest'@'localhost' for table 'v1' +INSERT INTO v1 SELECT * FROM t2; +ERROR 42000: INSERT command denied to user 'privtest'@'localhost' for table 'v1' +EXPLAIN INSERT INTO v1 SELECT * FROM t2; +ERROR 42000: INSERT command denied to user 'privtest'@'localhost' for table 'v1' +ANALYZE INSERT INTO v1 SELECT * FROM t2; +ERROR 42000: INSERT command denied to user 'privtest'@'localhost' for table 'v1' +REPLACE INTO v1 (a) VALUES (10); +ERROR 42000: INSERT, DELETE command denied to user 'privtest'@'localhost' for table 'v1' +EXPLAIN REPLACE INTO v1 (a) VALUES (10); +ERROR 42000: INSERT, DELETE command denied to user 'privtest'@'localhost' for table 'v1' +ANALYZE REPLACE INTO v1 (a) VALUES (10); +ERROR 42000: INSERT, DELETE command denied to user 'privtest'@'localhost' for table 'v1' +REPLACE INTO v1 SELECT * FROM t2; +ERROR 42000: INSERT, DELETE command denied to user 'privtest'@'localhost' for table 'v1' +EXPLAIN REPLACE INTO v1 SELECT * FROM t2; +ERROR 42000: INSERT, DELETE command denied to user 'privtest'@'localhost' for table 'v1' +ANALYZE REPLACE INTO v1 SELECT * FROM t2; +ERROR 42000: INSERT, DELETE command denied to user 'privtest'@'localhost' for table 'v1' +UPDATE v1 SET a = 10; +ERROR 42000: UPDATE command denied to user 'privtest'@'localhost' for table 'v1' +EXPLAIN UPDATE v1 SET a = 10; +ERROR 42000: UPDATE command denied to user 'privtest'@'localhost' for table 'v1' +ANALYZE UPDATE v1 SET a = 10; +ERROR 42000: UPDATE command denied to user 'privtest'@'localhost' for table 'v1' +UPDATE v1 SET a = a + 1; +ERROR 42000: UPDATE command denied to user 'privtest'@'localhost' for table 'v1' +EXPLAIN UPDATE v1 SET a = a + 1; +ERROR 42000: UPDATE command denied to user 'privtest'@'localhost' for table 'v1' +ANALYZE UPDATE v1 SET a = a + 1; +ERROR 42000: UPDATE command denied to user 'privtest'@'localhost' for table 'v1' +UPDATE v1, t2 SET v1.a = v1.a + 1 WHERE v1.a = t2.a; +ERROR 42000: SELECT command denied to user 'privtest'@'localhost' for table 'v1' +EXPLAIN UPDATE v1, t2 SET v1.a = v1.a + 1 WHERE v1.a = t2.a; +ERROR 42000: SELECT command denied to user 'privtest'@'localhost' for table 'v1' +ANALYZE UPDATE v1, t2 SET v1.a = v1.a + 1 WHERE v1.a = t2.a; +ERROR 42000: SELECT command denied to user 'privtest'@'localhost' for table 'v1' +DELETE FROM v1; +ERROR 42000: DELETE command denied to user 'privtest'@'localhost' for table 'v1' +EXPLAIN DELETE FROM v1; +ERROR 42000: DELETE command denied to user 'privtest'@'localhost' for table 'v1' +ANALYZE DELETE FROM v1; +ERROR 42000: DELETE command denied to user 'privtest'@'localhost' for table 'v1' +DELETE FROM v1 WHERE a = 10; +ERROR 42000: DELETE command denied to user 'privtest'@'localhost' for table 'v1' +EXPLAIN DELETE FROM v1 WHERE a = 10; +ERROR 42000: DELETE command denied to user 'privtest'@'localhost' for table 'v1' +ANALYZE DELETE FROM v1 WHERE a = 10; +ERROR 42000: DELETE command denied to user 'privtest'@'localhost' for table 'v1' +DELETE FROM v1 USING v1, t2; +ERROR 42000: SELECT command denied to user 'privtest'@'localhost' for table 'v1' +EXPLAIN DELETE FROM v1 USING v1, t2; +ERROR 42000: SELECT command denied to user 'privtest'@'localhost' for table 'v1' +ANALYZE DELETE FROM v1 USING v1, t2; +ERROR 42000: SELECT command denied to user 'privtest'@'localhost' for table 'v1' +DELETE FROM v1 USING v1, t2 WHERE v1.a = t2.a; +ERROR 42000: SELECT command denied to user 'privtest'@'localhost' for table 'v1' +EXPLAIN DELETE FROM v1 USING v1, t2 WHERE v1.a = t2.a; +ERROR 42000: SELECT command denied to user 'privtest'@'localhost' for table 'v1' +ANALYZE DELETE FROM v1 USING v1, t2 WHERE v1.a = t2.a; +ERROR 42000: SELECT command denied to user 'privtest'@'localhost' for table 'v1' +SELECT * FROM v1; +ERROR 42000: SELECT command denied to user 'privtest'@'localhost' for table 'v1' +EXPLAIN SELECT * FROM v1; +ERROR 42000: SELECT command denied to user 'privtest'@'localhost' for table 'v1' +ANALYZE SELECT * FROM v1; +ERROR 42000: SELECT command denied to user 'privtest'@'localhost' for table 'v1' +SELECT * FROM v1 WHERE a = 10; +ERROR 42000: SELECT command denied to user 'privtest'@'localhost' for table 'v1' +EXPLAIN SELECT * FROM v1 WHERE a = 10; +ERROR 42000: SELECT command denied to user 'privtest'@'localhost' for table 'v1' +ANALYZE SELECT * FROM v1 WHERE a = 10; +ERROR 42000: SELECT command denied to user 'privtest'@'localhost' for table 'v1' +SELECT * FROM v1 WHERE a IN ( SELECT a FROM t2 ); +ERROR 42000: SELECT command denied to user 'privtest'@'localhost' for table 'v1' +EXPLAIN SELECT * FROM v1 WHERE a IN ( SELECT a FROM t2 ); +ERROR 42000: SELECT command denied to user 'privtest'@'localhost' for table 'v1' +ANALYZE SELECT * FROM v1 WHERE a IN ( SELECT a FROM t2 ); +ERROR 42000: SELECT command denied to user 'privtest'@'localhost' for table 'v1' +#------------------------------------------------------------------------ +# I/R/U/D/S on the outer view +# Expectation: Can run the queries, but not EXPLAIN or ANALYZE +# because the query plan cannot be shown +# (it could have revealed the structure of the inner view) +#------------------------------------------------------------------------ +INSERT INTO v2 (a) VALUES (10); +EXPLAIN INSERT INTO v2 (a) VALUES (10); +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +ANALYZE INSERT INTO v2 (a) VALUES (10); +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +INSERT INTO v2 SELECT * FROM t2; +EXPLAIN INSERT INTO v2 SELECT * FROM t2; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +ANALYZE INSERT INTO v2 SELECT * FROM t2; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +REPLACE INTO v2 (a) VALUES (10); +EXPLAIN REPLACE INTO v2 (a) VALUES (10); +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +ANALYZE REPLACE INTO v2 (a) VALUES (10); +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +REPLACE INTO v2 SELECT * FROM t2; +EXPLAIN REPLACE INTO v2 SELECT * FROM t2; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +ANALYZE REPLACE INTO v2 SELECT * FROM t2; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +UPDATE v2 SET a = 10; +EXPLAIN UPDATE v2 SET a = 10; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +ANALYZE UPDATE v2 SET a = 10; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +UPDATE v2 SET a = a + 1; +EXPLAIN UPDATE v2 SET a = a + 1; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +ANALYZE UPDATE v2 SET a = a + 1; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +UPDATE v2, t2 SET v2.a = v2.a + 1 WHERE v2.a = t2.a; +EXPLAIN UPDATE v2, t2 SET v2.a = v2.a + 1 WHERE v2.a = t2.a; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +ANALYZE UPDATE v2, t2 SET v2.a = v2.a + 1 WHERE v2.a = t2.a; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +DELETE FROM v2; +EXPLAIN DELETE FROM v2; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +ANALYZE DELETE FROM v2; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +DELETE FROM v2 WHERE a = 10; +EXPLAIN DELETE FROM v2 WHERE a = 10; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +ANALYZE DELETE FROM v2 WHERE a = 10; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +DELETE FROM v2 USING v2, t2; +EXPLAIN DELETE FROM v2 USING v2, t2; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +ANALYZE DELETE FROM v2 USING v2, t2; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +DELETE FROM v2 USING v2, t2 WHERE v2.a = t2.a; +EXPLAIN DELETE FROM v2 USING v2, t2 WHERE v2.a = t2.a; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +ANALYZE DELETE FROM v2 USING v2, t2 WHERE v2.a = t2.a; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +SELECT * FROM v2; +a b +EXPLAIN SELECT * FROM v2; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +ANALYZE SELECT * FROM v2; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +SELECT * FROM v2 WHERE a = 10; +a b +EXPLAIN SELECT * FROM v2 WHERE a = 10; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +ANALYZE SELECT * FROM v2 WHERE a = 10; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +SELECT * FROM v2 WHERE a IN ( SELECT a FROM t2 ); +a b +EXPLAIN SELECT * FROM v2 WHERE a IN ( SELECT a FROM t2 ); +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +ANALYZE SELECT * FROM v2 WHERE a IN ( SELECT a FROM t2 ); +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table + +#======================================================================== +# Test: Grant SHOW VIEW on the inner view +#======================================================================== + +connection default; +GRANT SHOW VIEW ON privtest_db.v1 TO 'privtest'@'localhost'; +connection con1; +#------------------------------------------------------------------------ +# I/R/U/D/S on the inner view +# Expectation: Cannot run anything +#------------------------------------------------------------------------ +INSERT INTO v1 (a) VALUES (10); +ERROR 42000: INSERT command denied to user 'privtest'@'localhost' for table 'v1' +EXPLAIN INSERT INTO v1 (a) VALUES (10); +ERROR 42000: INSERT command denied to user 'privtest'@'localhost' for table 'v1' +ANALYZE INSERT INTO v1 (a) VALUES (10); +ERROR 42000: INSERT command denied to user 'privtest'@'localhost' for table 'v1' +INSERT INTO v1 SELECT * FROM t2; +ERROR 42000: INSERT command denied to user 'privtest'@'localhost' for table 'v1' +EXPLAIN INSERT INTO v1 SELECT * FROM t2; +ERROR 42000: INSERT command denied to user 'privtest'@'localhost' for table 'v1' +ANALYZE INSERT INTO v1 SELECT * FROM t2; +ERROR 42000: INSERT command denied to user 'privtest'@'localhost' for table 'v1' +REPLACE INTO v1 (a) VALUES (10); +ERROR 42000: INSERT, DELETE command denied to user 'privtest'@'localhost' for table 'v1' +EXPLAIN REPLACE INTO v1 (a) VALUES (10); +ERROR 42000: INSERT, DELETE command denied to user 'privtest'@'localhost' for table 'v1' +ANALYZE REPLACE INTO v1 (a) VALUES (10); +ERROR 42000: INSERT, DELETE command denied to user 'privtest'@'localhost' for table 'v1' +REPLACE INTO v1 SELECT * FROM t2; +ERROR 42000: INSERT, DELETE command denied to user 'privtest'@'localhost' for table 'v1' +EXPLAIN REPLACE INTO v1 SELECT * FROM t2; +ERROR 42000: INSERT, DELETE command denied to user 'privtest'@'localhost' for table 'v1' +ANALYZE REPLACE INTO v1 SELECT * FROM t2; +ERROR 42000: INSERT, DELETE command denied to user 'privtest'@'localhost' for table 'v1' +UPDATE v1 SET a = 10; +ERROR 42000: UPDATE command denied to user 'privtest'@'localhost' for table 'v1' +EXPLAIN UPDATE v1 SET a = 10; +ERROR 42000: UPDATE command denied to user 'privtest'@'localhost' for table 'v1' +ANALYZE UPDATE v1 SET a = 10; +ERROR 42000: UPDATE command denied to user 'privtest'@'localhost' for table 'v1' +UPDATE v1 SET a = a + 1; +ERROR 42000: UPDATE command denied to user 'privtest'@'localhost' for table 'v1' +EXPLAIN UPDATE v1 SET a = a + 1; +ERROR 42000: UPDATE command denied to user 'privtest'@'localhost' for table 'v1' +ANALYZE UPDATE v1 SET a = a + 1; +ERROR 42000: UPDATE command denied to user 'privtest'@'localhost' for table 'v1' +UPDATE v1, t2 SET v1.a = v1.a + 1 WHERE v1.a = t2.a; +ERROR 42000: SELECT command denied to user 'privtest'@'localhost' for table 'v1' +EXPLAIN UPDATE v1, t2 SET v1.a = v1.a + 1 WHERE v1.a = t2.a; +ERROR 42000: SELECT command denied to user 'privtest'@'localhost' for table 'v1' +ANALYZE UPDATE v1, t2 SET v1.a = v1.a + 1 WHERE v1.a = t2.a; +ERROR 42000: SELECT command denied to user 'privtest'@'localhost' for table 'v1' +DELETE FROM v1; +ERROR 42000: DELETE command denied to user 'privtest'@'localhost' for table 'v1' +EXPLAIN DELETE FROM v1; +ERROR 42000: DELETE command denied to user 'privtest'@'localhost' for table 'v1' +ANALYZE DELETE FROM v1; +ERROR 42000: DELETE command denied to user 'privtest'@'localhost' for table 'v1' +DELETE FROM v1 WHERE a = 10; +ERROR 42000: DELETE command denied to user 'privtest'@'localhost' for table 'v1' +EXPLAIN DELETE FROM v1 WHERE a = 10; +ERROR 42000: DELETE command denied to user 'privtest'@'localhost' for table 'v1' +ANALYZE DELETE FROM v1 WHERE a = 10; +ERROR 42000: DELETE command denied to user 'privtest'@'localhost' for table 'v1' +DELETE FROM v1 USING v1, t2; +ERROR 42000: SELECT command denied to user 'privtest'@'localhost' for table 'v1' +EXPLAIN DELETE FROM v1 USING v1, t2; +ERROR 42000: SELECT command denied to user 'privtest'@'localhost' for table 'v1' +ANALYZE DELETE FROM v1 USING v1, t2; +ERROR 42000: SELECT command denied to user 'privtest'@'localhost' for table 'v1' +DELETE FROM v1 USING v1, t2 WHERE v1.a = t2.a; +ERROR 42000: SELECT command denied to user 'privtest'@'localhost' for table 'v1' +EXPLAIN DELETE FROM v1 USING v1, t2 WHERE v1.a = t2.a; +ERROR 42000: SELECT command denied to user 'privtest'@'localhost' for table 'v1' +ANALYZE DELETE FROM v1 USING v1, t2 WHERE v1.a = t2.a; +ERROR 42000: SELECT command denied to user 'privtest'@'localhost' for table 'v1' +SELECT * FROM v1; +ERROR 42000: SELECT command denied to user 'privtest'@'localhost' for table 'v1' +EXPLAIN SELECT * FROM v1; +ERROR 42000: SELECT command denied to user 'privtest'@'localhost' for table 'v1' +ANALYZE SELECT * FROM v1; +ERROR 42000: SELECT command denied to user 'privtest'@'localhost' for table 'v1' +SELECT * FROM v1 WHERE a = 10; +ERROR 42000: SELECT command denied to user 'privtest'@'localhost' for table 'v1' +EXPLAIN SELECT * FROM v1 WHERE a = 10; +ERROR 42000: SELECT command denied to user 'privtest'@'localhost' for table 'v1' +ANALYZE SELECT * FROM v1 WHERE a = 10; +ERROR 42000: SELECT command denied to user 'privtest'@'localhost' for table 'v1' +SELECT * FROM v1 WHERE a IN ( SELECT a FROM t2 ); +ERROR 42000: SELECT command denied to user 'privtest'@'localhost' for table 'v1' +EXPLAIN SELECT * FROM v1 WHERE a IN ( SELECT a FROM t2 ); +ERROR 42000: SELECT command denied to user 'privtest'@'localhost' for table 'v1' +ANALYZE SELECT * FROM v1 WHERE a IN ( SELECT a FROM t2 ); +ERROR 42000: SELECT command denied to user 'privtest'@'localhost' for table 'v1' +#------------------------------------------------------------------------ +# I/R/U/D/S on the outer view +# Expectation: Can run the queries, but not EXPLAIN or ANALYZE +# because the query plan cannot be shown +# (that's how it works now) +#------------------------------------------------------------------------ +INSERT INTO v2 (a) VALUES (10); +EXPLAIN INSERT INTO v2 (a) VALUES (10); +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +ANALYZE INSERT INTO v2 (a) VALUES (10); +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +INSERT INTO v2 SELECT * FROM t2; +EXPLAIN INSERT INTO v2 SELECT * FROM t2; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +ANALYZE INSERT INTO v2 SELECT * FROM t2; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +REPLACE INTO v2 (a) VALUES (10); +EXPLAIN REPLACE INTO v2 (a) VALUES (10); +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +ANALYZE REPLACE INTO v2 (a) VALUES (10); +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +REPLACE INTO v2 SELECT * FROM t2; +EXPLAIN REPLACE INTO v2 SELECT * FROM t2; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +ANALYZE REPLACE INTO v2 SELECT * FROM t2; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +UPDATE v2 SET a = 10; +EXPLAIN UPDATE v2 SET a = 10; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +ANALYZE UPDATE v2 SET a = 10; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +UPDATE v2 SET a = a + 1; +EXPLAIN UPDATE v2 SET a = a + 1; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +ANALYZE UPDATE v2 SET a = a + 1; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +UPDATE v2, t2 SET v2.a = v2.a + 1 WHERE v2.a = t2.a; +EXPLAIN UPDATE v2, t2 SET v2.a = v2.a + 1 WHERE v2.a = t2.a; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +ANALYZE UPDATE v2, t2 SET v2.a = v2.a + 1 WHERE v2.a = t2.a; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +DELETE FROM v2; +EXPLAIN DELETE FROM v2; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +ANALYZE DELETE FROM v2; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +DELETE FROM v2 WHERE a = 10; +EXPLAIN DELETE FROM v2 WHERE a = 10; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +ANALYZE DELETE FROM v2 WHERE a = 10; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +DELETE FROM v2 USING v2, t2; +EXPLAIN DELETE FROM v2 USING v2, t2; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +ANALYZE DELETE FROM v2 USING v2, t2; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +DELETE FROM v2 USING v2, t2 WHERE v2.a = t2.a; +EXPLAIN DELETE FROM v2 USING v2, t2 WHERE v2.a = t2.a; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +ANALYZE DELETE FROM v2 USING v2, t2 WHERE v2.a = t2.a; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +SELECT * FROM v2; +a b +EXPLAIN SELECT * FROM v2; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +ANALYZE SELECT * FROM v2; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +SELECT * FROM v2 WHERE a = 10; +a b +EXPLAIN SELECT * FROM v2 WHERE a = 10; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +ANALYZE SELECT * FROM v2 WHERE a = 10; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +SELECT * FROM v2 WHERE a IN ( SELECT a FROM t2 ); +a b +EXPLAIN SELECT * FROM v2 WHERE a IN ( SELECT a FROM t2 ); +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +ANALYZE SELECT * FROM v2 WHERE a IN ( SELECT a FROM t2 ); +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table + +#======================================================================== +# Test: Grant SELECT on the inner view +#======================================================================== + +connection default; +REVOKE ALL PRIVILEGES ON privtest_db.v1 FROM 'privtest'@'localhost'; +GRANT SELECT ON privtest_db.v1 TO 'privtest'@'localhost'; +connection con1; +#------------------------------------------------------------------------ +# I/R/U/D/S on the inner view +# Expectation: Can run SELECT, but not EXPLAIN SELECT or ANALYZE SELECT +# because the query plan cannot be shown +# (it could have revealed the structure of the view) +#------------------------------------------------------------------------ +INSERT INTO v1 (a) VALUES (10); +ERROR 42000: INSERT command denied to user 'privtest'@'localhost' for table 'v1' +EXPLAIN INSERT INTO v1 (a) VALUES (10); +ERROR 42000: INSERT command denied to user 'privtest'@'localhost' for table 'v1' +ANALYZE INSERT INTO v1 (a) VALUES (10); +ERROR 42000: INSERT command denied to user 'privtest'@'localhost' for table 'v1' +INSERT INTO v1 SELECT * FROM t2; +ERROR 42000: INSERT command denied to user 'privtest'@'localhost' for table 'v1' +EXPLAIN INSERT INTO v1 SELECT * FROM t2; +ERROR 42000: INSERT command denied to user 'privtest'@'localhost' for table 'v1' +ANALYZE INSERT INTO v1 SELECT * FROM t2; +ERROR 42000: INSERT command denied to user 'privtest'@'localhost' for table 'v1' +REPLACE INTO v1 (a) VALUES (10); +ERROR 42000: INSERT, DELETE command denied to user 'privtest'@'localhost' for table 'v1' +EXPLAIN REPLACE INTO v1 (a) VALUES (10); +ERROR 42000: INSERT, DELETE command denied to user 'privtest'@'localhost' for table 'v1' +ANALYZE REPLACE INTO v1 (a) VALUES (10); +ERROR 42000: INSERT, DELETE command denied to user 'privtest'@'localhost' for table 'v1' +REPLACE INTO v1 SELECT * FROM t2; +ERROR 42000: INSERT, DELETE command denied to user 'privtest'@'localhost' for table 'v1' +EXPLAIN REPLACE INTO v1 SELECT * FROM t2; +ERROR 42000: INSERT, DELETE command denied to user 'privtest'@'localhost' for table 'v1' +ANALYZE REPLACE INTO v1 SELECT * FROM t2; +ERROR 42000: INSERT, DELETE command denied to user 'privtest'@'localhost' for table 'v1' +UPDATE v1 SET a = 10; +ERROR 42000: UPDATE command denied to user 'privtest'@'localhost' for table 'v1' +EXPLAIN UPDATE v1 SET a = 10; +ERROR 42000: UPDATE command denied to user 'privtest'@'localhost' for table 'v1' +ANALYZE UPDATE v1 SET a = 10; +ERROR 42000: UPDATE command denied to user 'privtest'@'localhost' for table 'v1' +UPDATE v1 SET a = a + 1; +ERROR 42000: UPDATE command denied to user 'privtest'@'localhost' for table 'v1' +EXPLAIN UPDATE v1 SET a = a + 1; +ERROR 42000: UPDATE command denied to user 'privtest'@'localhost' for table 'v1' +ANALYZE UPDATE v1 SET a = a + 1; +ERROR 42000: UPDATE command denied to user 'privtest'@'localhost' for table 'v1' +UPDATE v1, t2 SET v1.a = v1.a + 1 WHERE v1.a = t2.a; +ERROR 42000: UPDATE command denied to user 'privtest'@'localhost' for table 'v1' +EXPLAIN UPDATE v1, t2 SET v1.a = v1.a + 1 WHERE v1.a = t2.a; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +ANALYZE UPDATE v1, t2 SET v1.a = v1.a + 1 WHERE v1.a = t2.a; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +DELETE FROM v1; +ERROR 42000: DELETE command denied to user 'privtest'@'localhost' for table 'v1' +EXPLAIN DELETE FROM v1; +ERROR 42000: DELETE command denied to user 'privtest'@'localhost' for table 'v1' +ANALYZE DELETE FROM v1; +ERROR 42000: DELETE command denied to user 'privtest'@'localhost' for table 'v1' +DELETE FROM v1 WHERE a = 10; +ERROR 42000: DELETE command denied to user 'privtest'@'localhost' for table 'v1' +EXPLAIN DELETE FROM v1 WHERE a = 10; +ERROR 42000: DELETE command denied to user 'privtest'@'localhost' for table 'v1' +ANALYZE DELETE FROM v1 WHERE a = 10; +ERROR 42000: DELETE command denied to user 'privtest'@'localhost' for table 'v1' +DELETE FROM v1 USING v1, t2; +ERROR 42000: DELETE command denied to user 'privtest'@'localhost' for table 'v1' +EXPLAIN DELETE FROM v1 USING v1, t2; +ERROR 42000: DELETE command denied to user 'privtest'@'localhost' for table 'v1' +ANALYZE DELETE FROM v1 USING v1, t2; +ERROR 42000: DELETE command denied to user 'privtest'@'localhost' for table 'v1' +DELETE FROM v1 USING v1, t2 WHERE v1.a = t2.a; +ERROR 42000: DELETE command denied to user 'privtest'@'localhost' for table 'v1' +EXPLAIN DELETE FROM v1 USING v1, t2 WHERE v1.a = t2.a; +ERROR 42000: DELETE command denied to user 'privtest'@'localhost' for table 'v1' +ANALYZE DELETE FROM v1 USING v1, t2 WHERE v1.a = t2.a; +ERROR 42000: DELETE command denied to user 'privtest'@'localhost' for table 'v1' +SELECT * FROM v1; +a b +EXPLAIN SELECT * FROM v1; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +ANALYZE SELECT * FROM v1; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +SELECT * FROM v1 WHERE a = 10; +a b +EXPLAIN SELECT * FROM v1 WHERE a = 10; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +ANALYZE SELECT * FROM v1 WHERE a = 10; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +SELECT * FROM v1 WHERE a IN ( SELECT a FROM t2 ); +a b +EXPLAIN SELECT * FROM v1 WHERE a IN ( SELECT a FROM t2 ); +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +ANALYZE SELECT * FROM v1 WHERE a IN ( SELECT a FROM t2 ); +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +#------------------------------------------------------------------------ +# I/R/U/D/S on the outer view +# Expectation: Can run the queries, but not EXPLAIN or ANALYZE +# because the query plan cannot be shown +# (it could have revealed the structure of the inner view) +#------------------------------------------------------------------------ +INSERT INTO v2 (a) VALUES (10); +EXPLAIN INSERT INTO v2 (a) VALUES (10); +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +ANALYZE INSERT INTO v2 (a) VALUES (10); +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +INSERT INTO v2 SELECT * FROM t2; +EXPLAIN INSERT INTO v2 SELECT * FROM t2; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +ANALYZE INSERT INTO v2 SELECT * FROM t2; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +REPLACE INTO v2 (a) VALUES (10); +EXPLAIN REPLACE INTO v2 (a) VALUES (10); +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +ANALYZE REPLACE INTO v2 (a) VALUES (10); +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +REPLACE INTO v2 SELECT * FROM t2; +EXPLAIN REPLACE INTO v2 SELECT * FROM t2; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +ANALYZE REPLACE INTO v2 SELECT * FROM t2; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +UPDATE v2 SET a = 10; +EXPLAIN UPDATE v2 SET a = 10; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +ANALYZE UPDATE v2 SET a = 10; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +UPDATE v2 SET a = a + 1; +EXPLAIN UPDATE v2 SET a = a + 1; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +ANALYZE UPDATE v2 SET a = a + 1; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +UPDATE v2, t2 SET v2.a = v2.a + 1 WHERE v2.a = t2.a; +EXPLAIN UPDATE v2, t2 SET v2.a = v2.a + 1 WHERE v2.a = t2.a; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +ANALYZE UPDATE v2, t2 SET v2.a = v2.a + 1 WHERE v2.a = t2.a; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +DELETE FROM v2; +EXPLAIN DELETE FROM v2; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +ANALYZE DELETE FROM v2; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +DELETE FROM v2 WHERE a = 10; +EXPLAIN DELETE FROM v2 WHERE a = 10; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +ANALYZE DELETE FROM v2 WHERE a = 10; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +DELETE FROM v2 USING v2, t2; +EXPLAIN DELETE FROM v2 USING v2, t2; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +ANALYZE DELETE FROM v2 USING v2, t2; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +DELETE FROM v2 USING v2, t2 WHERE v2.a = t2.a; +EXPLAIN DELETE FROM v2 USING v2, t2 WHERE v2.a = t2.a; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +ANALYZE DELETE FROM v2 USING v2, t2 WHERE v2.a = t2.a; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +SELECT * FROM v2; +a b +EXPLAIN SELECT * FROM v2; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +ANALYZE SELECT * FROM v2; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +SELECT * FROM v2 WHERE a = 10; +a b +EXPLAIN SELECT * FROM v2 WHERE a = 10; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +ANALYZE SELECT * FROM v2 WHERE a = 10; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +SELECT * FROM v2 WHERE a IN ( SELECT a FROM t2 ); +a b +EXPLAIN SELECT * FROM v2 WHERE a IN ( SELECT a FROM t2 ); +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +ANALYZE SELECT * FROM v2 WHERE a IN ( SELECT a FROM t2 ); +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table + +#======================================================================== +# Test: Grant SELECT, SHOW VIEW on the inner view +#======================================================================== + + +#------------------------------------------------------------------------ +# Test: SELECT + SHOW VIEW privileges allow ANALYZE SELECT for the inner +# view, and ANALYZE <anything> for the outer view +#------------------------------------------------------------------------ + +connection default; +REVOKE ALL PRIVILEGES ON privtest_db.v1 FROM 'privtest'@'localhost'; +GRANT SELECT, SHOW VIEW ON privtest_db.v1 TO 'privtest'@'localhost'; +connection con1; +#------------------------------------------------------------------------ +# I/R/U/D/S on the inner view +# Expectation: Can run SELECT, EXPLAIN SELECT, ANALYZE SELECT +#------------------------------------------------------------------------ +INSERT INTO v1 (a) VALUES (10); +ERROR 42000: INSERT command denied to user 'privtest'@'localhost' for table 'v1' +EXPLAIN INSERT INTO v1 (a) VALUES (10); +ERROR 42000: INSERT command denied to user 'privtest'@'localhost' for table 'v1' +ANALYZE INSERT INTO v1 (a) VALUES (10); +ERROR 42000: INSERT command denied to user 'privtest'@'localhost' for table 'v1' +INSERT INTO v1 SELECT * FROM t2; +ERROR 42000: INSERT command denied to user 'privtest'@'localhost' for table 'v1' +EXPLAIN INSERT INTO v1 SELECT * FROM t2; +ERROR 42000: INSERT command denied to user 'privtest'@'localhost' for table 'v1' +ANALYZE INSERT INTO v1 SELECT * FROM t2; +ERROR 42000: INSERT command denied to user 'privtest'@'localhost' for table 'v1' +REPLACE INTO v1 (a) VALUES (10); +ERROR 42000: INSERT, DELETE command denied to user 'privtest'@'localhost' for table 'v1' +EXPLAIN REPLACE INTO v1 (a) VALUES (10); +ERROR 42000: INSERT, DELETE command denied to user 'privtest'@'localhost' for table 'v1' +ANALYZE REPLACE INTO v1 (a) VALUES (10); +ERROR 42000: INSERT, DELETE command denied to user 'privtest'@'localhost' for table 'v1' +REPLACE INTO v1 SELECT * FROM t2; +ERROR 42000: INSERT, DELETE command denied to user 'privtest'@'localhost' for table 'v1' +EXPLAIN REPLACE INTO v1 SELECT * FROM t2; +ERROR 42000: INSERT, DELETE command denied to user 'privtest'@'localhost' for table 'v1' +ANALYZE REPLACE INTO v1 SELECT * FROM t2; +ERROR 42000: INSERT, DELETE command denied to user 'privtest'@'localhost' for table 'v1' +UPDATE v1 SET a = 10; +ERROR 42000: UPDATE command denied to user 'privtest'@'localhost' for table 'v1' +EXPLAIN UPDATE v1 SET a = 10; +ERROR 42000: UPDATE command denied to user 'privtest'@'localhost' for table 'v1' +ANALYZE UPDATE v1 SET a = 10; +ERROR 42000: UPDATE command denied to user 'privtest'@'localhost' for table 'v1' +UPDATE v1 SET a = a + 1; +ERROR 42000: UPDATE command denied to user 'privtest'@'localhost' for table 'v1' +EXPLAIN UPDATE v1 SET a = a + 1; +ERROR 42000: UPDATE command denied to user 'privtest'@'localhost' for table 'v1' +ANALYZE UPDATE v1 SET a = a + 1; +ERROR 42000: UPDATE command denied to user 'privtest'@'localhost' for table 'v1' +UPDATE v1, t2 SET v1.a = v1.a + 1 WHERE v1.a = t2.a; +ERROR 42000: UPDATE command denied to user 'privtest'@'localhost' for table 'v1' +EXPLAIN UPDATE v1, t2 SET v1.a = v1.a + 1 WHERE v1.a = t2.a; +ERROR 42000: UPDATE command denied to user 'privtest'@'localhost' for table 'v1' +ANALYZE UPDATE v1, t2 SET v1.a = v1.a + 1 WHERE v1.a = t2.a; +ERROR 42000: UPDATE command denied to user 'privtest'@'localhost' for table 'v1' +DELETE FROM v1; +ERROR 42000: DELETE command denied to user 'privtest'@'localhost' for table 'v1' +EXPLAIN DELETE FROM v1; +ERROR 42000: DELETE command denied to user 'privtest'@'localhost' for table 'v1' +ANALYZE DELETE FROM v1; +ERROR 42000: DELETE command denied to user 'privtest'@'localhost' for table 'v1' +DELETE FROM v1 WHERE a = 10; +ERROR 42000: DELETE command denied to user 'privtest'@'localhost' for table 'v1' +EXPLAIN DELETE FROM v1 WHERE a = 10; +ERROR 42000: DELETE command denied to user 'privtest'@'localhost' for table 'v1' +ANALYZE DELETE FROM v1 WHERE a = 10; +ERROR 42000: DELETE command denied to user 'privtest'@'localhost' for table 'v1' +DELETE FROM v1 USING v1, t2; +ERROR 42000: DELETE command denied to user 'privtest'@'localhost' for table 'v1' +EXPLAIN DELETE FROM v1 USING v1, t2; +ERROR 42000: DELETE command denied to user 'privtest'@'localhost' for table 'v1' +ANALYZE DELETE FROM v1 USING v1, t2; +ERROR 42000: DELETE command denied to user 'privtest'@'localhost' for table 'v1' +DELETE FROM v1 USING v1, t2 WHERE v1.a = t2.a; +ERROR 42000: DELETE command denied to user 'privtest'@'localhost' for table 'v1' +EXPLAIN DELETE FROM v1 USING v1, t2 WHERE v1.a = t2.a; +ERROR 42000: DELETE command denied to user 'privtest'@'localhost' for table 'v1' +ANALYZE DELETE FROM v1 USING v1, t2 WHERE v1.a = t2.a; +ERROR 42000: DELETE command denied to user 'privtest'@'localhost' for table 'v1' +SELECT * FROM v1; +a b +EXPLAIN SELECT * FROM v1; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 14 Using where +ANALYZE SELECT * FROM v1; +id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 14 14.00 100.00 0.00 Using where +SELECT * FROM v1 WHERE a = 10; +a b +EXPLAIN SELECT * FROM v1 WHERE a = 10; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 14 Using where +ANALYZE SELECT * FROM v1 WHERE a = 10; +id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 14 14.00 100.00 0.00 Using where +SELECT * FROM v1 WHERE a IN ( SELECT a FROM t2 ); +a b +EXPLAIN SELECT * FROM v1 WHERE a IN ( SELECT a FROM t2 ); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 3 +1 PRIMARY t1 ALL NULL NULL NULL NULL 14 Using where; Using join buffer (flat, BNL join) +2 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 +ANALYZE SELECT * FROM v1 WHERE a IN ( SELECT a FROM t2 ); +id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra +1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 3 3.00 100.00 100.00 +1 PRIMARY t1 ALL NULL NULL NULL NULL 14 14.00 100.00 0.00 Using where; Using join buffer (flat, BNL join) +2 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 3.00 100.00 100.00 +#------------------------------------------------------------------------ +# I/R/U/D/S on the outer view +# Expectation: Can run everything +#------------------------------------------------------------------------ +INSERT INTO v2 (a) VALUES (10); +EXPLAIN INSERT INTO v2 (a) VALUES (10); +id select_type table type possible_keys key key_len ref rows Extra +1 INSERT t1 ALL NULL NULL NULL NULL NULL NULL +ANALYZE INSERT INTO v2 (a) VALUES (10); +id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra +1 INSERT t1 ALL NULL NULL NULL NULL NULL NULL 100.00 100.00 NULL +INSERT INTO v2 SELECT * FROM t2; +EXPLAIN INSERT INTO v2 SELECT * FROM t2; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t2 ALL NULL NULL NULL NULL 3 +ANALYZE INSERT INTO v2 SELECT * FROM t2; +id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra +1 SIMPLE t2 ALL NULL NULL NULL NULL 3 3.00 100.00 100.00 +REPLACE INTO v2 (a) VALUES (10); +EXPLAIN REPLACE INTO v2 (a) VALUES (10); +id select_type table type possible_keys key key_len ref rows Extra +1 INSERT t1 ALL NULL NULL NULL NULL NULL NULL +ANALYZE REPLACE INTO v2 (a) VALUES (10); +id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra +1 INSERT t1 ALL NULL NULL NULL NULL NULL NULL 100.00 100.00 NULL +REPLACE INTO v2 SELECT * FROM t2; +EXPLAIN REPLACE INTO v2 SELECT * FROM t2; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t2 ALL NULL NULL NULL NULL 3 +ANALYZE REPLACE INTO v2 SELECT * FROM t2; +id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra +1 SIMPLE t2 ALL NULL NULL NULL NULL 3 3.00 100.00 100.00 +UPDATE v2 SET a = 10; +EXPLAIN UPDATE v2 SET a = 10; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 30 Using where +ANALYZE UPDATE v2 SET a = 10; +id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 30 30.00 100.00 40.00 Using where +UPDATE v2 SET a = a + 1; +EXPLAIN UPDATE v2 SET a = a + 1; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 30 Using where +ANALYZE UPDATE v2 SET a = a + 1; +id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 30 30.00 100.00 40.00 Using where +UPDATE v2, t2 SET v2.a = v2.a + 1 WHERE v2.a = t2.a; +EXPLAIN UPDATE v2, t2 SET v2.a = v2.a + 1 WHERE v2.a = t2.a; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t2 ALL NULL NULL NULL NULL 3 Using where +1 SIMPLE t1 ALL NULL NULL NULL NULL 30 Using where +ANALYZE UPDATE v2, t2 SET v2.a = v2.a + 1 WHERE v2.a = t2.a; +id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra +1 SIMPLE t2 ALL NULL NULL NULL NULL 3 3.00 100.00 100.00 Using where +1 SIMPLE t1 ALL NULL NULL NULL NULL 30 30.00 100.00 0.00 Using where +DELETE FROM v2; +EXPLAIN DELETE FROM v2; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 18 Using where +ANALYZE DELETE FROM v2; +id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 18 18.00 100.00 0.00 Using where +DELETE FROM v2 WHERE a = 10; +EXPLAIN DELETE FROM v2 WHERE a = 10; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 18 Using where +ANALYZE DELETE FROM v2 WHERE a = 10; +id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 18 18.00 100.00 0.00 Using where +DELETE FROM v2 USING v2, t2; +ANALYZE DELETE FROM v2 USING v2, t2; +id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra +1 SIMPLE t2 ALL NULL NULL NULL NULL 3 3.00 100.00 100.00 +1 SIMPLE t1 ALL NULL NULL NULL NULL 18 18.00 100.00 0.00 Using where +DELETE FROM v2 USING v2, t2 WHERE v2.a = t2.a; +ANALYZE DELETE FROM v2 USING v2, t2 WHERE v2.a = t2.a; +id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra +1 SIMPLE t2 ALL NULL NULL NULL NULL 3 3.00 100.00 100.00 Using where +1 SIMPLE t1 ALL NULL NULL NULL NULL 18 18.00 100.00 0.00 Using where +SELECT * FROM v2; +a b +EXPLAIN SELECT * FROM v2; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 18 Using where +ANALYZE SELECT * FROM v2; +id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 18 18.00 100.00 0.00 Using where +SELECT * FROM v2 WHERE a = 10; +a b +EXPLAIN SELECT * FROM v2 WHERE a = 10; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 18 Using where +ANALYZE SELECT * FROM v2 WHERE a = 10; +id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 18 18.00 100.00 0.00 Using where +SELECT * FROM v2 WHERE a IN ( SELECT a FROM t2 ); +a b +EXPLAIN SELECT * FROM v2 WHERE a IN ( SELECT a FROM t2 ); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 3 +1 PRIMARY t1 ALL NULL NULL NULL NULL 18 Using where; Using join buffer (flat, BNL join) +2 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 Using where +ANALYZE SELECT * FROM v2 WHERE a IN ( SELECT a FROM t2 ); +id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra +1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 3 3.00 100.00 100.00 +1 PRIMARY t1 ALL NULL NULL NULL NULL 18 18.00 100.00 0.00 Using where; Using join buffer (flat, BNL join) +2 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 3.00 100.00 100.00 Using where + +#======================================================================== +# Test: Grant INSERT on the inner view +#======================================================================== + +connection default; +REVOKE ALL PRIVILEGES ON privtest_db.v1 FROM 'privtest'@'localhost'; +GRANT INSERT ON privtest_db.v1 TO 'privtest'@'localhost'; +connection con1; +#------------------------------------------------------------------------ +# I/R/U/D/S on the inner view +# Expectation: Can run INSERT, but not EXPLAIN INSERT or ANALYZE INSERT +# because the query plan cannot be shown +# (it could have revealed the structure of the view) +#------------------------------------------------------------------------ +INSERT INTO v1 (a) VALUES (10); +EXPLAIN INSERT INTO v1 (a) VALUES (10); +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +ANALYZE INSERT INTO v1 (a) VALUES (10); +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +INSERT INTO v1 SELECT * FROM t2; +EXPLAIN INSERT INTO v1 SELECT * FROM t2; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +ANALYZE INSERT INTO v1 SELECT * FROM t2; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +REPLACE INTO v1 (a) VALUES (10); +ERROR 42000: DELETE command denied to user 'privtest'@'localhost' for table 'v1' +EXPLAIN REPLACE INTO v1 (a) VALUES (10); +ERROR 42000: DELETE command denied to user 'privtest'@'localhost' for table 'v1' +ANALYZE REPLACE INTO v1 (a) VALUES (10); +ERROR 42000: DELETE command denied to user 'privtest'@'localhost' for table 'v1' +REPLACE INTO v1 SELECT * FROM t2; +ERROR 42000: DELETE command denied to user 'privtest'@'localhost' for table 'v1' +EXPLAIN REPLACE INTO v1 SELECT * FROM t2; +ERROR 42000: DELETE command denied to user 'privtest'@'localhost' for table 'v1' +ANALYZE REPLACE INTO v1 SELECT * FROM t2; +ERROR 42000: DELETE command denied to user 'privtest'@'localhost' for table 'v1' +UPDATE v1 SET a = 10; +ERROR 42000: UPDATE command denied to user 'privtest'@'localhost' for table 'v1' +EXPLAIN UPDATE v1 SET a = 10; +ERROR 42000: UPDATE command denied to user 'privtest'@'localhost' for table 'v1' +ANALYZE UPDATE v1 SET a = 10; +ERROR 42000: UPDATE command denied to user 'privtest'@'localhost' for table 'v1' +UPDATE v1 SET a = a + 1; +ERROR 42000: UPDATE command denied to user 'privtest'@'localhost' for table 'v1' +EXPLAIN UPDATE v1 SET a = a + 1; +ERROR 42000: UPDATE command denied to user 'privtest'@'localhost' for table 'v1' +ANALYZE UPDATE v1 SET a = a + 1; +ERROR 42000: UPDATE command denied to user 'privtest'@'localhost' for table 'v1' +UPDATE v1, t2 SET v1.a = v1.a + 1 WHERE v1.a = t2.a; +ERROR 42000: SELECT command denied to user 'privtest'@'localhost' for table 'v1' +EXPLAIN UPDATE v1, t2 SET v1.a = v1.a + 1 WHERE v1.a = t2.a; +ERROR 42000: SELECT command denied to user 'privtest'@'localhost' for table 'v1' +ANALYZE UPDATE v1, t2 SET v1.a = v1.a + 1 WHERE v1.a = t2.a; +ERROR 42000: SELECT command denied to user 'privtest'@'localhost' for table 'v1' +DELETE FROM v1; +ERROR 42000: DELETE command denied to user 'privtest'@'localhost' for table 'v1' +EXPLAIN DELETE FROM v1; +ERROR 42000: DELETE command denied to user 'privtest'@'localhost' for table 'v1' +ANALYZE DELETE FROM v1; +ERROR 42000: DELETE command denied to user 'privtest'@'localhost' for table 'v1' +DELETE FROM v1 WHERE a = 10; +ERROR 42000: DELETE command denied to user 'privtest'@'localhost' for table 'v1' +EXPLAIN DELETE FROM v1 WHERE a = 10; +ERROR 42000: DELETE command denied to user 'privtest'@'localhost' for table 'v1' +ANALYZE DELETE FROM v1 WHERE a = 10; +ERROR 42000: DELETE command denied to user 'privtest'@'localhost' for table 'v1' +DELETE FROM v1 USING v1, t2; +ERROR 42000: SELECT command denied to user 'privtest'@'localhost' for table 'v1' +EXPLAIN DELETE FROM v1 USING v1, t2; +ERROR 42000: SELECT command denied to user 'privtest'@'localhost' for table 'v1' +ANALYZE DELETE FROM v1 USING v1, t2; +ERROR 42000: SELECT command denied to user 'privtest'@'localhost' for table 'v1' +DELETE FROM v1 USING v1, t2 WHERE v1.a = t2.a; +ERROR 42000: SELECT command denied to user 'privtest'@'localhost' for table 'v1' +EXPLAIN DELETE FROM v1 USING v1, t2 WHERE v1.a = t2.a; +ERROR 42000: SELECT command denied to user 'privtest'@'localhost' for table 'v1' +ANALYZE DELETE FROM v1 USING v1, t2 WHERE v1.a = t2.a; +ERROR 42000: SELECT command denied to user 'privtest'@'localhost' for table 'v1' +SELECT * FROM v1; +ERROR 42000: SELECT command denied to user 'privtest'@'localhost' for table 'v1' +EXPLAIN SELECT * FROM v1; +ERROR 42000: SELECT command denied to user 'privtest'@'localhost' for table 'v1' +ANALYZE SELECT * FROM v1; +ERROR 42000: SELECT command denied to user 'privtest'@'localhost' for table 'v1' +SELECT * FROM v1 WHERE a = 10; +ERROR 42000: SELECT command denied to user 'privtest'@'localhost' for table 'v1' +EXPLAIN SELECT * FROM v1 WHERE a = 10; +ERROR 42000: SELECT command denied to user 'privtest'@'localhost' for table 'v1' +ANALYZE SELECT * FROM v1 WHERE a = 10; +ERROR 42000: SELECT command denied to user 'privtest'@'localhost' for table 'v1' +SELECT * FROM v1 WHERE a IN ( SELECT a FROM t2 ); +ERROR 42000: SELECT command denied to user 'privtest'@'localhost' for table 'v1' +EXPLAIN SELECT * FROM v1 WHERE a IN ( SELECT a FROM t2 ); +ERROR 42000: SELECT command denied to user 'privtest'@'localhost' for table 'v1' +ANALYZE SELECT * FROM v1 WHERE a IN ( SELECT a FROM t2 ); +ERROR 42000: SELECT command denied to user 'privtest'@'localhost' for table 'v1' +#------------------------------------------------------------------------ +# I/R/U/D/S on the outer view +# Expectation: Can run the queries, but not EXPLAIN or ANALYZE +# because the query plan cannot be shown +# (it could have revealed the structure of the inner view) +#------------------------------------------------------------------------ +INSERT INTO v2 (a) VALUES (10); +EXPLAIN INSERT INTO v2 (a) VALUES (10); +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +ANALYZE INSERT INTO v2 (a) VALUES (10); +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +INSERT INTO v2 SELECT * FROM t2; +EXPLAIN INSERT INTO v2 SELECT * FROM t2; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +ANALYZE INSERT INTO v2 SELECT * FROM t2; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +REPLACE INTO v2 (a) VALUES (10); +EXPLAIN REPLACE INTO v2 (a) VALUES (10); +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +ANALYZE REPLACE INTO v2 (a) VALUES (10); +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +REPLACE INTO v2 SELECT * FROM t2; +EXPLAIN REPLACE INTO v2 SELECT * FROM t2; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +ANALYZE REPLACE INTO v2 SELECT * FROM t2; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +UPDATE v2 SET a = 10; +EXPLAIN UPDATE v2 SET a = 10; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +ANALYZE UPDATE v2 SET a = 10; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +UPDATE v2 SET a = a + 1; +EXPLAIN UPDATE v2 SET a = a + 1; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +ANALYZE UPDATE v2 SET a = a + 1; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +UPDATE v2, t2 SET v2.a = v2.a + 1 WHERE v2.a = t2.a; +EXPLAIN UPDATE v2, t2 SET v2.a = v2.a + 1 WHERE v2.a = t2.a; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +ANALYZE UPDATE v2, t2 SET v2.a = v2.a + 1 WHERE v2.a = t2.a; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +DELETE FROM v2; +EXPLAIN DELETE FROM v2; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +ANALYZE DELETE FROM v2; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +DELETE FROM v2 WHERE a = 10; +EXPLAIN DELETE FROM v2 WHERE a = 10; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +ANALYZE DELETE FROM v2 WHERE a = 10; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +DELETE FROM v2 USING v2, t2; +EXPLAIN DELETE FROM v2 USING v2, t2; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +ANALYZE DELETE FROM v2 USING v2, t2; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +DELETE FROM v2 USING v2, t2 WHERE v2.a = t2.a; +EXPLAIN DELETE FROM v2 USING v2, t2 WHERE v2.a = t2.a; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +ANALYZE DELETE FROM v2 USING v2, t2 WHERE v2.a = t2.a; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +SELECT * FROM v2; +a b +EXPLAIN SELECT * FROM v2; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +ANALYZE SELECT * FROM v2; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +SELECT * FROM v2 WHERE a = 10; +a b +EXPLAIN SELECT * FROM v2 WHERE a = 10; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +ANALYZE SELECT * FROM v2 WHERE a = 10; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +SELECT * FROM v2 WHERE a IN ( SELECT a FROM t2 ); +a b +EXPLAIN SELECT * FROM v2 WHERE a IN ( SELECT a FROM t2 ); +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +ANALYZE SELECT * FROM v2 WHERE a IN ( SELECT a FROM t2 ); +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table + +#======================================================================== +# Test: Grant UPDATE on the inner view +#======================================================================== + +connection default; +REVOKE ALL PRIVILEGES ON privtest_db.v1 FROM 'privtest'@'localhost'; +GRANT UPDATE ON privtest_db.v1 TO 'privtest'@'localhost'; +connection con1; +#------------------------------------------------------------------------ +# I/R/U/D/S on the inner view +# Expectation: Can run UPDATE which does not read any columns, but not +# but not EXPLAIN UPDATE or ANALYZE UPDATE +# because the query plan cannot be shown +# (it could have revealed the structure of the view). +#------------------------------------------------------------------------ +INSERT INTO v1 (a) VALUES (10); +ERROR 42000: INSERT command denied to user 'privtest'@'localhost' for table 'v1' +EXPLAIN INSERT INTO v1 (a) VALUES (10); +ERROR 42000: INSERT command denied to user 'privtest'@'localhost' for table 'v1' +ANALYZE INSERT INTO v1 (a) VALUES (10); +ERROR 42000: INSERT command denied to user 'privtest'@'localhost' for table 'v1' +INSERT INTO v1 SELECT * FROM t2; +ERROR 42000: INSERT command denied to user 'privtest'@'localhost' for table 'v1' +EXPLAIN INSERT INTO v1 SELECT * FROM t2; +ERROR 42000: INSERT command denied to user 'privtest'@'localhost' for table 'v1' +ANALYZE INSERT INTO v1 SELECT * FROM t2; +ERROR 42000: INSERT command denied to user 'privtest'@'localhost' for table 'v1' +REPLACE INTO v1 (a) VALUES (10); +ERROR 42000: INSERT, DELETE command denied to user 'privtest'@'localhost' for table 'v1' +EXPLAIN REPLACE INTO v1 (a) VALUES (10); +ERROR 42000: INSERT, DELETE command denied to user 'privtest'@'localhost' for table 'v1' +ANALYZE REPLACE INTO v1 (a) VALUES (10); +ERROR 42000: INSERT, DELETE command denied to user 'privtest'@'localhost' for table 'v1' +REPLACE INTO v1 SELECT * FROM t2; +ERROR 42000: INSERT, DELETE command denied to user 'privtest'@'localhost' for table 'v1' +EXPLAIN REPLACE INTO v1 SELECT * FROM t2; +ERROR 42000: INSERT, DELETE command denied to user 'privtest'@'localhost' for table 'v1' +ANALYZE REPLACE INTO v1 SELECT * FROM t2; +ERROR 42000: INSERT, DELETE command denied to user 'privtest'@'localhost' for table 'v1' +UPDATE v1 SET a = 10; +EXPLAIN UPDATE v1 SET a = 10; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +ANALYZE UPDATE v1 SET a = 10; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +UPDATE v1 SET a = a + 1; +EXPLAIN UPDATE v1 SET a = a + 1; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +ANALYZE UPDATE v1 SET a = a + 1; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +UPDATE v1, t2 SET v1.a = v1.a + 1 WHERE v1.a = t2.a; +ERROR 42000: SELECT command denied to user 'privtest'@'localhost' for column 'a' in table 'v1' +EXPLAIN UPDATE v1, t2 SET v1.a = v1.a + 1 WHERE v1.a = t2.a; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +ANALYZE UPDATE v1, t2 SET v1.a = v1.a + 1 WHERE v1.a = t2.a; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +DELETE FROM v1; +ERROR 42000: DELETE command denied to user 'privtest'@'localhost' for table 'v1' +EXPLAIN DELETE FROM v1; +ERROR 42000: DELETE command denied to user 'privtest'@'localhost' for table 'v1' +ANALYZE DELETE FROM v1; +ERROR 42000: DELETE command denied to user 'privtest'@'localhost' for table 'v1' +DELETE FROM v1 WHERE a = 10; +ERROR 42000: DELETE command denied to user 'privtest'@'localhost' for table 'v1' +EXPLAIN DELETE FROM v1 WHERE a = 10; +ERROR 42000: DELETE command denied to user 'privtest'@'localhost' for table 'v1' +ANALYZE DELETE FROM v1 WHERE a = 10; +ERROR 42000: DELETE command denied to user 'privtest'@'localhost' for table 'v1' +DELETE FROM v1 USING v1, t2; +ERROR 42000: SELECT command denied to user 'privtest'@'localhost' for table 'v1' +EXPLAIN DELETE FROM v1 USING v1, t2; +ERROR 42000: SELECT command denied to user 'privtest'@'localhost' for table 'v1' +ANALYZE DELETE FROM v1 USING v1, t2; +ERROR 42000: SELECT command denied to user 'privtest'@'localhost' for table 'v1' +DELETE FROM v1 USING v1, t2 WHERE v1.a = t2.a; +ERROR 42000: SELECT command denied to user 'privtest'@'localhost' for table 'v1' +EXPLAIN DELETE FROM v1 USING v1, t2 WHERE v1.a = t2.a; +ERROR 42000: SELECT command denied to user 'privtest'@'localhost' for table 'v1' +ANALYZE DELETE FROM v1 USING v1, t2 WHERE v1.a = t2.a; +ERROR 42000: SELECT command denied to user 'privtest'@'localhost' for table 'v1' +SELECT * FROM v1; +ERROR 42000: SELECT command denied to user 'privtest'@'localhost' for table 'v1' +EXPLAIN SELECT * FROM v1; +ERROR 42000: SELECT command denied to user 'privtest'@'localhost' for table 'v1' +ANALYZE SELECT * FROM v1; +ERROR 42000: SELECT command denied to user 'privtest'@'localhost' for table 'v1' +SELECT * FROM v1 WHERE a = 10; +ERROR 42000: SELECT command denied to user 'privtest'@'localhost' for table 'v1' +EXPLAIN SELECT * FROM v1 WHERE a = 10; +ERROR 42000: SELECT command denied to user 'privtest'@'localhost' for table 'v1' +ANALYZE SELECT * FROM v1 WHERE a = 10; +ERROR 42000: SELECT command denied to user 'privtest'@'localhost' for table 'v1' +SELECT * FROM v1 WHERE a IN ( SELECT a FROM t2 ); +ERROR 42000: SELECT command denied to user 'privtest'@'localhost' for table 'v1' +EXPLAIN SELECT * FROM v1 WHERE a IN ( SELECT a FROM t2 ); +ERROR 42000: SELECT command denied to user 'privtest'@'localhost' for table 'v1' +ANALYZE SELECT * FROM v1 WHERE a IN ( SELECT a FROM t2 ); +ERROR 42000: SELECT command denied to user 'privtest'@'localhost' for table 'v1' +#------------------------------------------------------------------------ +# I/R/U/D/S on the outer view +# Expectation: Can run the queries, but not EXPLAIN or ANALYZE +# because the query plan cannot be shown +# (it could have revealed the structure of the inner view) +#------------------------------------------------------------------------ +INSERT INTO v2 (a) VALUES (10); +EXPLAIN INSERT INTO v2 (a) VALUES (10); +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +ANALYZE INSERT INTO v2 (a) VALUES (10); +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +INSERT INTO v2 SELECT * FROM t2; +EXPLAIN INSERT INTO v2 SELECT * FROM t2; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +ANALYZE INSERT INTO v2 SELECT * FROM t2; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +REPLACE INTO v2 (a) VALUES (10); +EXPLAIN REPLACE INTO v2 (a) VALUES (10); +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +ANALYZE REPLACE INTO v2 (a) VALUES (10); +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +REPLACE INTO v2 SELECT * FROM t2; +EXPLAIN REPLACE INTO v2 SELECT * FROM t2; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +ANALYZE REPLACE INTO v2 SELECT * FROM t2; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +UPDATE v2 SET a = 10; +EXPLAIN UPDATE v2 SET a = 10; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +ANALYZE UPDATE v2 SET a = 10; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +UPDATE v2 SET a = a + 1; +EXPLAIN UPDATE v2 SET a = a + 1; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +ANALYZE UPDATE v2 SET a = a + 1; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +UPDATE v2, t2 SET v2.a = v2.a + 1 WHERE v2.a = t2.a; +EXPLAIN UPDATE v2, t2 SET v2.a = v2.a + 1 WHERE v2.a = t2.a; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +ANALYZE UPDATE v2, t2 SET v2.a = v2.a + 1 WHERE v2.a = t2.a; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +DELETE FROM v2; +EXPLAIN DELETE FROM v2; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +ANALYZE DELETE FROM v2; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +DELETE FROM v2 WHERE a = 10; +EXPLAIN DELETE FROM v2 WHERE a = 10; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +ANALYZE DELETE FROM v2 WHERE a = 10; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +DELETE FROM v2 USING v2, t2; +EXPLAIN DELETE FROM v2 USING v2, t2; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +ANALYZE DELETE FROM v2 USING v2, t2; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +DELETE FROM v2 USING v2, t2 WHERE v2.a = t2.a; +EXPLAIN DELETE FROM v2 USING v2, t2 WHERE v2.a = t2.a; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +ANALYZE DELETE FROM v2 USING v2, t2 WHERE v2.a = t2.a; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +SELECT * FROM v2; +a b +EXPLAIN SELECT * FROM v2; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +ANALYZE SELECT * FROM v2; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +SELECT * FROM v2 WHERE a = 10; +a b +EXPLAIN SELECT * FROM v2 WHERE a = 10; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +ANALYZE SELECT * FROM v2 WHERE a = 10; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +SELECT * FROM v2 WHERE a IN ( SELECT a FROM t2 ); +a b +EXPLAIN SELECT * FROM v2 WHERE a IN ( SELECT a FROM t2 ); +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +ANALYZE SELECT * FROM v2 WHERE a IN ( SELECT a FROM t2 ); +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table + +#======================================================================== +# Test: Grant UPDATE, SELECT(a) on the inner view +#======================================================================== + +connection default; +REVOKE ALL PRIVILEGES ON privtest_db.v1 FROM 'privtest'@'localhost'; +GRANT SELECT(a), UPDATE ON privtest_db.v1 TO 'privtest'@'localhost'; +connection con1; +#------------------------------------------------------------------------ +# I/R/U/D/S on the inner view +# Expectation: Can run UPDATE, but not EXPLAIN or ANALYZE for it +# because the query plan cannot be shown +# (it could have revealed the structure of the view) +#------------------------------------------------------------------------ +INSERT INTO v1 (a) VALUES (10); +ERROR 42000: INSERT command denied to user 'privtest'@'localhost' for table 'v1' +EXPLAIN INSERT INTO v1 (a) VALUES (10); +ERROR 42000: INSERT command denied to user 'privtest'@'localhost' for table 'v1' +ANALYZE INSERT INTO v1 (a) VALUES (10); +ERROR 42000: INSERT command denied to user 'privtest'@'localhost' for table 'v1' +INSERT INTO v1 SELECT * FROM t2; +ERROR 42000: INSERT command denied to user 'privtest'@'localhost' for table 'v1' +EXPLAIN INSERT INTO v1 SELECT * FROM t2; +ERROR 42000: INSERT command denied to user 'privtest'@'localhost' for table 'v1' +ANALYZE INSERT INTO v1 SELECT * FROM t2; +ERROR 42000: INSERT command denied to user 'privtest'@'localhost' for table 'v1' +REPLACE INTO v1 (a) VALUES (10); +ERROR 42000: INSERT, DELETE command denied to user 'privtest'@'localhost' for table 'v1' +EXPLAIN REPLACE INTO v1 (a) VALUES (10); +ERROR 42000: INSERT, DELETE command denied to user 'privtest'@'localhost' for table 'v1' +ANALYZE REPLACE INTO v1 (a) VALUES (10); +ERROR 42000: INSERT, DELETE command denied to user 'privtest'@'localhost' for table 'v1' +REPLACE INTO v1 SELECT * FROM t2; +ERROR 42000: INSERT, DELETE command denied to user 'privtest'@'localhost' for table 'v1' +EXPLAIN REPLACE INTO v1 SELECT * FROM t2; +ERROR 42000: INSERT, DELETE command denied to user 'privtest'@'localhost' for table 'v1' +ANALYZE REPLACE INTO v1 SELECT * FROM t2; +ERROR 42000: INSERT, DELETE command denied to user 'privtest'@'localhost' for table 'v1' +UPDATE v1 SET a = 10; +EXPLAIN UPDATE v1 SET a = 10; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +ANALYZE UPDATE v1 SET a = 10; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +UPDATE v1 SET a = a + 1; +EXPLAIN UPDATE v1 SET a = a + 1; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +ANALYZE UPDATE v1 SET a = a + 1; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +UPDATE v1, t2 SET v1.a = v1.a + 1 WHERE v1.a = t2.a; +EXPLAIN UPDATE v1, t2 SET v1.a = v1.a + 1 WHERE v1.a = t2.a; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +ANALYZE UPDATE v1, t2 SET v1.a = v1.a + 1 WHERE v1.a = t2.a; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +DELETE FROM v1; +ERROR 42000: DELETE command denied to user 'privtest'@'localhost' for table 'v1' +EXPLAIN DELETE FROM v1; +ERROR 42000: DELETE command denied to user 'privtest'@'localhost' for table 'v1' +ANALYZE DELETE FROM v1; +ERROR 42000: DELETE command denied to user 'privtest'@'localhost' for table 'v1' +DELETE FROM v1 WHERE a = 10; +ERROR 42000: DELETE command denied to user 'privtest'@'localhost' for table 'v1' +EXPLAIN DELETE FROM v1 WHERE a = 10; +ERROR 42000: DELETE command denied to user 'privtest'@'localhost' for table 'v1' +ANALYZE DELETE FROM v1 WHERE a = 10; +ERROR 42000: DELETE command denied to user 'privtest'@'localhost' for table 'v1' +DELETE FROM v1 USING v1, t2; +ERROR 42000: DELETE command denied to user 'privtest'@'localhost' for table 'v1' +EXPLAIN DELETE FROM v1 USING v1, t2; +ERROR 42000: DELETE command denied to user 'privtest'@'localhost' for table 'v1' +ANALYZE DELETE FROM v1 USING v1, t2; +ERROR 42000: DELETE command denied to user 'privtest'@'localhost' for table 'v1' +DELETE FROM v1 USING v1, t2 WHERE v1.a = t2.a; +ERROR 42000: DELETE command denied to user 'privtest'@'localhost' for table 'v1' +EXPLAIN DELETE FROM v1 USING v1, t2 WHERE v1.a = t2.a; +ERROR 42000: DELETE command denied to user 'privtest'@'localhost' for table 'v1' +ANALYZE DELETE FROM v1 USING v1, t2 WHERE v1.a = t2.a; +ERROR 42000: DELETE command denied to user 'privtest'@'localhost' for table 'v1' +SELECT * FROM v1; +ERROR 42000: SELECT command denied to user 'privtest'@'localhost' for table 'v1' +EXPLAIN SELECT * FROM v1; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +ANALYZE SELECT * FROM v1; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +SELECT * FROM v1 WHERE a = 10; +ERROR 42000: SELECT command denied to user 'privtest'@'localhost' for table 'v1' +EXPLAIN SELECT * FROM v1 WHERE a = 10; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +ANALYZE SELECT * FROM v1 WHERE a = 10; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +SELECT * FROM v1 WHERE a IN ( SELECT a FROM t2 ); +ERROR 42000: SELECT command denied to user 'privtest'@'localhost' for table 'v1' +EXPLAIN SELECT * FROM v1 WHERE a IN ( SELECT a FROM t2 ); +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +ANALYZE SELECT * FROM v1 WHERE a IN ( SELECT a FROM t2 ); +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +#------------------------------------------------------------------------ +# I/R/U/D/S on the outer view +# Expectation: Can run the queries, but not EXPLAIN or ANALYZE +# because the query plan cannot be shown +# (it could have revealed the structure of the inner view) +#------------------------------------------------------------------------ +INSERT INTO v2 (a) VALUES (10); +EXPLAIN INSERT INTO v2 (a) VALUES (10); +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +ANALYZE INSERT INTO v2 (a) VALUES (10); +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +INSERT INTO v2 SELECT * FROM t2; +EXPLAIN INSERT INTO v2 SELECT * FROM t2; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +ANALYZE INSERT INTO v2 SELECT * FROM t2; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +REPLACE INTO v2 (a) VALUES (10); +EXPLAIN REPLACE INTO v2 (a) VALUES (10); +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +ANALYZE REPLACE INTO v2 (a) VALUES (10); +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +REPLACE INTO v2 SELECT * FROM t2; +EXPLAIN REPLACE INTO v2 SELECT * FROM t2; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +ANALYZE REPLACE INTO v2 SELECT * FROM t2; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +UPDATE v2 SET a = 10; +EXPLAIN UPDATE v2 SET a = 10; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +ANALYZE UPDATE v2 SET a = 10; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +UPDATE v2 SET a = a + 1; +EXPLAIN UPDATE v2 SET a = a + 1; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +ANALYZE UPDATE v2 SET a = a + 1; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +UPDATE v2, t2 SET v2.a = v2.a + 1 WHERE v2.a = t2.a; +EXPLAIN UPDATE v2, t2 SET v2.a = v2.a + 1 WHERE v2.a = t2.a; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +ANALYZE UPDATE v2, t2 SET v2.a = v2.a + 1 WHERE v2.a = t2.a; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +DELETE FROM v2; +EXPLAIN DELETE FROM v2; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +ANALYZE DELETE FROM v2; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +DELETE FROM v2 WHERE a = 10; +EXPLAIN DELETE FROM v2 WHERE a = 10; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +ANALYZE DELETE FROM v2 WHERE a = 10; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +DELETE FROM v2 USING v2, t2; +EXPLAIN DELETE FROM v2 USING v2, t2; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +ANALYZE DELETE FROM v2 USING v2, t2; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +DELETE FROM v2 USING v2, t2 WHERE v2.a = t2.a; +EXPLAIN DELETE FROM v2 USING v2, t2 WHERE v2.a = t2.a; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +ANALYZE DELETE FROM v2 USING v2, t2 WHERE v2.a = t2.a; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +SELECT * FROM v2; +a b +EXPLAIN SELECT * FROM v2; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +ANALYZE SELECT * FROM v2; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +SELECT * FROM v2 WHERE a = 10; +a b +EXPLAIN SELECT * FROM v2 WHERE a = 10; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +ANALYZE SELECT * FROM v2 WHERE a = 10; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +SELECT * FROM v2 WHERE a IN ( SELECT a FROM t2 ); +a b +EXPLAIN SELECT * FROM v2 WHERE a IN ( SELECT a FROM t2 ); +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +ANALYZE SELECT * FROM v2 WHERE a IN ( SELECT a FROM t2 ); +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table + +#======================================================================== +# Test: Grant SELECT, UPDATE on the inner view +#======================================================================== + +connection default; +REVOKE ALL PRIVILEGES ON privtest_db.v1 FROM 'privtest'@'localhost'; +GRANT SELECT, UPDATE ON privtest_db.v1 TO 'privtest'@'localhost'; +connection con1; +#------------------------------------------------------------------------ +# I/R/U/D/S on the inner view +# Expectation: Can run the UPDATE and SELECT queries, but not EXPLAIN +# or ANALYZE because the query plan cannot be shown +# (it could have revealed the structure of the view) +#------------------------------------------------------------------------ +INSERT INTO v1 (a) VALUES (10); +ERROR 42000: INSERT command denied to user 'privtest'@'localhost' for table 'v1' +EXPLAIN INSERT INTO v1 (a) VALUES (10); +ERROR 42000: INSERT command denied to user 'privtest'@'localhost' for table 'v1' +ANALYZE INSERT INTO v1 (a) VALUES (10); +ERROR 42000: INSERT command denied to user 'privtest'@'localhost' for table 'v1' +INSERT INTO v1 SELECT * FROM t2; +ERROR 42000: INSERT command denied to user 'privtest'@'localhost' for table 'v1' +EXPLAIN INSERT INTO v1 SELECT * FROM t2; +ERROR 42000: INSERT command denied to user 'privtest'@'localhost' for table 'v1' +ANALYZE INSERT INTO v1 SELECT * FROM t2; +ERROR 42000: INSERT command denied to user 'privtest'@'localhost' for table 'v1' +REPLACE INTO v1 (a) VALUES (10); +ERROR 42000: INSERT, DELETE command denied to user 'privtest'@'localhost' for table 'v1' +EXPLAIN REPLACE INTO v1 (a) VALUES (10); +ERROR 42000: INSERT, DELETE command denied to user 'privtest'@'localhost' for table 'v1' +ANALYZE REPLACE INTO v1 (a) VALUES (10); +ERROR 42000: INSERT, DELETE command denied to user 'privtest'@'localhost' for table 'v1' +REPLACE INTO v1 SELECT * FROM t2; +ERROR 42000: INSERT, DELETE command denied to user 'privtest'@'localhost' for table 'v1' +EXPLAIN REPLACE INTO v1 SELECT * FROM t2; +ERROR 42000: INSERT, DELETE command denied to user 'privtest'@'localhost' for table 'v1' +ANALYZE REPLACE INTO v1 SELECT * FROM t2; +ERROR 42000: INSERT, DELETE command denied to user 'privtest'@'localhost' for table 'v1' +UPDATE v1 SET a = 10; +EXPLAIN UPDATE v1 SET a = 10; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +ANALYZE UPDATE v1 SET a = 10; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +UPDATE v1 SET a = a + 1; +EXPLAIN UPDATE v1 SET a = a + 1; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +ANALYZE UPDATE v1 SET a = a + 1; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +UPDATE v1, t2 SET v1.a = v1.a + 1 WHERE v1.a = t2.a; +EXPLAIN UPDATE v1, t2 SET v1.a = v1.a + 1 WHERE v1.a = t2.a; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +ANALYZE UPDATE v1, t2 SET v1.a = v1.a + 1 WHERE v1.a = t2.a; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +DELETE FROM v1; +ERROR 42000: DELETE command denied to user 'privtest'@'localhost' for table 'v1' +EXPLAIN DELETE FROM v1; +ERROR 42000: DELETE command denied to user 'privtest'@'localhost' for table 'v1' +ANALYZE DELETE FROM v1; +ERROR 42000: DELETE command denied to user 'privtest'@'localhost' for table 'v1' +DELETE FROM v1 WHERE a = 10; +ERROR 42000: DELETE command denied to user 'privtest'@'localhost' for table 'v1' +EXPLAIN DELETE FROM v1 WHERE a = 10; +ERROR 42000: DELETE command denied to user 'privtest'@'localhost' for table 'v1' +ANALYZE DELETE FROM v1 WHERE a = 10; +ERROR 42000: DELETE command denied to user 'privtest'@'localhost' for table 'v1' +DELETE FROM v1 USING v1, t2; +ERROR 42000: DELETE command denied to user 'privtest'@'localhost' for table 'v1' +EXPLAIN DELETE FROM v1 USING v1, t2; +ERROR 42000: DELETE command denied to user 'privtest'@'localhost' for table 'v1' +ANALYZE DELETE FROM v1 USING v1, t2; +ERROR 42000: DELETE command denied to user 'privtest'@'localhost' for table 'v1' +DELETE FROM v1 USING v1, t2 WHERE v1.a = t2.a; +ERROR 42000: DELETE command denied to user 'privtest'@'localhost' for table 'v1' +EXPLAIN DELETE FROM v1 USING v1, t2 WHERE v1.a = t2.a; +ERROR 42000: DELETE command denied to user 'privtest'@'localhost' for table 'v1' +ANALYZE DELETE FROM v1 USING v1, t2 WHERE v1.a = t2.a; +ERROR 42000: DELETE command denied to user 'privtest'@'localhost' for table 'v1' +SELECT * FROM v1; +a b +EXPLAIN SELECT * FROM v1; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +ANALYZE SELECT * FROM v1; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +SELECT * FROM v1 WHERE a = 10; +a b +EXPLAIN SELECT * FROM v1 WHERE a = 10; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +ANALYZE SELECT * FROM v1 WHERE a = 10; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +SELECT * FROM v1 WHERE a IN ( SELECT a FROM t2 ); +a b +EXPLAIN SELECT * FROM v1 WHERE a IN ( SELECT a FROM t2 ); +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +ANALYZE SELECT * FROM v1 WHERE a IN ( SELECT a FROM t2 ); +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +#------------------------------------------------------------------------ +# I/R/U/D/S on the outer view +# Expectation: Can run the queries, but not EXPLAIN or ANALYZE +# because the query plan cannot be shown +# (it could have revealed the structure of the inner view) +#------------------------------------------------------------------------ +INSERT INTO v2 (a) VALUES (10); +EXPLAIN INSERT INTO v2 (a) VALUES (10); +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +ANALYZE INSERT INTO v2 (a) VALUES (10); +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +INSERT INTO v2 SELECT * FROM t2; +EXPLAIN INSERT INTO v2 SELECT * FROM t2; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +ANALYZE INSERT INTO v2 SELECT * FROM t2; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +REPLACE INTO v2 (a) VALUES (10); +EXPLAIN REPLACE INTO v2 (a) VALUES (10); +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +ANALYZE REPLACE INTO v2 (a) VALUES (10); +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +REPLACE INTO v2 SELECT * FROM t2; +EXPLAIN REPLACE INTO v2 SELECT * FROM t2; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +ANALYZE REPLACE INTO v2 SELECT * FROM t2; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +UPDATE v2 SET a = 10; +EXPLAIN UPDATE v2 SET a = 10; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +ANALYZE UPDATE v2 SET a = 10; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +UPDATE v2 SET a = a + 1; +EXPLAIN UPDATE v2 SET a = a + 1; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +ANALYZE UPDATE v2 SET a = a + 1; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +UPDATE v2, t2 SET v2.a = v2.a + 1 WHERE v2.a = t2.a; +EXPLAIN UPDATE v2, t2 SET v2.a = v2.a + 1 WHERE v2.a = t2.a; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +ANALYZE UPDATE v2, t2 SET v2.a = v2.a + 1 WHERE v2.a = t2.a; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +DELETE FROM v2; +EXPLAIN DELETE FROM v2; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +ANALYZE DELETE FROM v2; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +DELETE FROM v2 WHERE a = 10; +EXPLAIN DELETE FROM v2 WHERE a = 10; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +ANALYZE DELETE FROM v2 WHERE a = 10; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +DELETE FROM v2 USING v2, t2; +EXPLAIN DELETE FROM v2 USING v2, t2; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +ANALYZE DELETE FROM v2 USING v2, t2; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +DELETE FROM v2 USING v2, t2 WHERE v2.a = t2.a; +EXPLAIN DELETE FROM v2 USING v2, t2 WHERE v2.a = t2.a; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +ANALYZE DELETE FROM v2 USING v2, t2 WHERE v2.a = t2.a; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +SELECT * FROM v2; +a b +EXPLAIN SELECT * FROM v2; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +ANALYZE SELECT * FROM v2; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +SELECT * FROM v2 WHERE a = 10; +a b +EXPLAIN SELECT * FROM v2 WHERE a = 10; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +ANALYZE SELECT * FROM v2 WHERE a = 10; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +SELECT * FROM v2 WHERE a IN ( SELECT a FROM t2 ); +a b +EXPLAIN SELECT * FROM v2 WHERE a IN ( SELECT a FROM t2 ); +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +ANALYZE SELECT * FROM v2 WHERE a IN ( SELECT a FROM t2 ); +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table + +#======================================================================== +# Test: Grant DELETE on the inner view +#======================================================================== + +connection default; +REVOKE ALL PRIVILEGES ON privtest_db.v1 FROM 'privtest'@'localhost'; +GRANT DELETE ON privtest_db.v1 TO 'privtest'@'localhost'; +connection con1; +#------------------------------------------------------------------------ +# I/R/U/D/S on the inner view +# Expectation: Can run DELETE which does not read any columns, +# but not EXPLAIN DELETE or ANALYZE DELETE +# because the query plan cannot be shown +# (it could have revealed the structure of the view) +#------------------------------------------------------------------------ +INSERT INTO v1 (a) VALUES (10); +ERROR 42000: INSERT command denied to user 'privtest'@'localhost' for table 'v1' +EXPLAIN INSERT INTO v1 (a) VALUES (10); +ERROR 42000: INSERT command denied to user 'privtest'@'localhost' for table 'v1' +ANALYZE INSERT INTO v1 (a) VALUES (10); +ERROR 42000: INSERT command denied to user 'privtest'@'localhost' for table 'v1' +INSERT INTO v1 SELECT * FROM t2; +ERROR 42000: INSERT command denied to user 'privtest'@'localhost' for table 'v1' +EXPLAIN INSERT INTO v1 SELECT * FROM t2; +ERROR 42000: INSERT command denied to user 'privtest'@'localhost' for table 'v1' +ANALYZE INSERT INTO v1 SELECT * FROM t2; +ERROR 42000: INSERT command denied to user 'privtest'@'localhost' for table 'v1' +REPLACE INTO v1 (a) VALUES (10); +ERROR 42000: INSERT command denied to user 'privtest'@'localhost' for table 'v1' +EXPLAIN REPLACE INTO v1 (a) VALUES (10); +ERROR 42000: INSERT command denied to user 'privtest'@'localhost' for table 'v1' +ANALYZE REPLACE INTO v1 (a) VALUES (10); +ERROR 42000: INSERT command denied to user 'privtest'@'localhost' for table 'v1' +REPLACE INTO v1 SELECT * FROM t2; +ERROR 42000: INSERT command denied to user 'privtest'@'localhost' for table 'v1' +EXPLAIN REPLACE INTO v1 SELECT * FROM t2; +ERROR 42000: INSERT command denied to user 'privtest'@'localhost' for table 'v1' +ANALYZE REPLACE INTO v1 SELECT * FROM t2; +ERROR 42000: INSERT command denied to user 'privtest'@'localhost' for table 'v1' +UPDATE v1 SET a = 10; +ERROR 42000: UPDATE command denied to user 'privtest'@'localhost' for table 'v1' +EXPLAIN UPDATE v1 SET a = 10; +ERROR 42000: UPDATE command denied to user 'privtest'@'localhost' for table 'v1' +ANALYZE UPDATE v1 SET a = 10; +ERROR 42000: UPDATE command denied to user 'privtest'@'localhost' for table 'v1' +UPDATE v1 SET a = a + 1; +ERROR 42000: UPDATE command denied to user 'privtest'@'localhost' for table 'v1' +EXPLAIN UPDATE v1 SET a = a + 1; +ERROR 42000: UPDATE command denied to user 'privtest'@'localhost' for table 'v1' +ANALYZE UPDATE v1 SET a = a + 1; +ERROR 42000: UPDATE command denied to user 'privtest'@'localhost' for table 'v1' +UPDATE v1, t2 SET v1.a = v1.a + 1 WHERE v1.a = t2.a; +ERROR 42000: SELECT command denied to user 'privtest'@'localhost' for table 'v1' +EXPLAIN UPDATE v1, t2 SET v1.a = v1.a + 1 WHERE v1.a = t2.a; +ERROR 42000: SELECT command denied to user 'privtest'@'localhost' for table 'v1' +ANALYZE UPDATE v1, t2 SET v1.a = v1.a + 1 WHERE v1.a = t2.a; +ERROR 42000: SELECT command denied to user 'privtest'@'localhost' for table 'v1' +DELETE FROM v1; +EXPLAIN DELETE FROM v1; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +ANALYZE DELETE FROM v1; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +DELETE FROM v1 WHERE a = 10; +ERROR 42000: SELECT command denied to user 'privtest'@'localhost' for column 'a' in table 'v1' +EXPLAIN DELETE FROM v1 WHERE a = 10; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +ANALYZE DELETE FROM v1 WHERE a = 10; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +DELETE FROM v1 USING v1, t2; +ERROR 42000: SELECT command denied to user 'privtest'@'localhost' for table 'v1' +EXPLAIN DELETE FROM v1 USING v1, t2; +ERROR 42000: SELECT command denied to user 'privtest'@'localhost' for table 'v1' +ANALYZE DELETE FROM v1 USING v1, t2; +ERROR 42000: SELECT command denied to user 'privtest'@'localhost' for table 'v1' +DELETE FROM v1 USING v1, t2 WHERE v1.a = t2.a; +ERROR 42000: SELECT command denied to user 'privtest'@'localhost' for table 'v1' +EXPLAIN DELETE FROM v1 USING v1, t2 WHERE v1.a = t2.a; +ERROR 42000: SELECT command denied to user 'privtest'@'localhost' for table 'v1' +ANALYZE DELETE FROM v1 USING v1, t2 WHERE v1.a = t2.a; +ERROR 42000: SELECT command denied to user 'privtest'@'localhost' for table 'v1' +SELECT * FROM v1; +ERROR 42000: SELECT command denied to user 'privtest'@'localhost' for table 'v1' +EXPLAIN SELECT * FROM v1; +ERROR 42000: SELECT command denied to user 'privtest'@'localhost' for table 'v1' +ANALYZE SELECT * FROM v1; +ERROR 42000: SELECT command denied to user 'privtest'@'localhost' for table 'v1' +SELECT * FROM v1 WHERE a = 10; +ERROR 42000: SELECT command denied to user 'privtest'@'localhost' for table 'v1' +EXPLAIN SELECT * FROM v1 WHERE a = 10; +ERROR 42000: SELECT command denied to user 'privtest'@'localhost' for table 'v1' +ANALYZE SELECT * FROM v1 WHERE a = 10; +ERROR 42000: SELECT command denied to user 'privtest'@'localhost' for table 'v1' +SELECT * FROM v1 WHERE a IN ( SELECT a FROM t2 ); +ERROR 42000: SELECT command denied to user 'privtest'@'localhost' for table 'v1' +EXPLAIN SELECT * FROM v1 WHERE a IN ( SELECT a FROM t2 ); +ERROR 42000: SELECT command denied to user 'privtest'@'localhost' for table 'v1' +ANALYZE SELECT * FROM v1 WHERE a IN ( SELECT a FROM t2 ); +ERROR 42000: SELECT command denied to user 'privtest'@'localhost' for table 'v1' +#------------------------------------------------------------------------ +# I/R/U/D/S on the outer view +# Expectation: Can run the queries, but not EXPLAIN or ANALYZE +# because the query plan cannot be shown +# (it could have revealed the structure of the inner view) +#------------------------------------------------------------------------ +INSERT INTO v2 (a) VALUES (10); +EXPLAIN INSERT INTO v2 (a) VALUES (10); +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +ANALYZE INSERT INTO v2 (a) VALUES (10); +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +INSERT INTO v2 SELECT * FROM t2; +EXPLAIN INSERT INTO v2 SELECT * FROM t2; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +ANALYZE INSERT INTO v2 SELECT * FROM t2; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +REPLACE INTO v2 (a) VALUES (10); +EXPLAIN REPLACE INTO v2 (a) VALUES (10); +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +ANALYZE REPLACE INTO v2 (a) VALUES (10); +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +REPLACE INTO v2 SELECT * FROM t2; +EXPLAIN REPLACE INTO v2 SELECT * FROM t2; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +ANALYZE REPLACE INTO v2 SELECT * FROM t2; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +UPDATE v2 SET a = 10; +EXPLAIN UPDATE v2 SET a = 10; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +ANALYZE UPDATE v2 SET a = 10; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +UPDATE v2 SET a = a + 1; +EXPLAIN UPDATE v2 SET a = a + 1; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +ANALYZE UPDATE v2 SET a = a + 1; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +UPDATE v2, t2 SET v2.a = v2.a + 1 WHERE v2.a = t2.a; +EXPLAIN UPDATE v2, t2 SET v2.a = v2.a + 1 WHERE v2.a = t2.a; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +ANALYZE UPDATE v2, t2 SET v2.a = v2.a + 1 WHERE v2.a = t2.a; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +DELETE FROM v2; +EXPLAIN DELETE FROM v2; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +ANALYZE DELETE FROM v2; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +DELETE FROM v2 WHERE a = 10; +EXPLAIN DELETE FROM v2 WHERE a = 10; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +ANALYZE DELETE FROM v2 WHERE a = 10; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +DELETE FROM v2 USING v2, t2; +EXPLAIN DELETE FROM v2 USING v2, t2; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +ANALYZE DELETE FROM v2 USING v2, t2; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +DELETE FROM v2 USING v2, t2 WHERE v2.a = t2.a; +EXPLAIN DELETE FROM v2 USING v2, t2 WHERE v2.a = t2.a; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +ANALYZE DELETE FROM v2 USING v2, t2 WHERE v2.a = t2.a; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +SELECT * FROM v2; +a b +EXPLAIN SELECT * FROM v2; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +ANALYZE SELECT * FROM v2; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +SELECT * FROM v2 WHERE a = 10; +a b +EXPLAIN SELECT * FROM v2 WHERE a = 10; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +ANALYZE SELECT * FROM v2 WHERE a = 10; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +SELECT * FROM v2 WHERE a IN ( SELECT a FROM t2 ); +a b +EXPLAIN SELECT * FROM v2 WHERE a IN ( SELECT a FROM t2 ); +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +ANALYZE SELECT * FROM v2 WHERE a IN ( SELECT a FROM t2 ); +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table + +#======================================================================== +# Test: Grant DELETE, SHOW VIEW on the inner view +#======================================================================== + +connection default; +REVOKE ALL PRIVILEGES ON privtest_db.v1 FROM 'privtest'@'localhost'; +GRANT SHOW VIEW, DELETE ON privtest_db.v1 TO 'privtest'@'localhost'; +connection con1; +#------------------------------------------------------------------------ +# I/R/U/D/S on the inner view +# Expectation: Can run DELETE, EXPLAIN DELETE, UPDATE DELETE +# which don't read any columns +#------------------------------------------------------------------------ +INSERT INTO v1 (a) VALUES (10); +ERROR 42000: INSERT command denied to user 'privtest'@'localhost' for table 'v1' +EXPLAIN INSERT INTO v1 (a) VALUES (10); +ERROR 42000: INSERT command denied to user 'privtest'@'localhost' for table 'v1' +ANALYZE INSERT INTO v1 (a) VALUES (10); +ERROR 42000: INSERT command denied to user 'privtest'@'localhost' for table 'v1' +INSERT INTO v1 SELECT * FROM t2; +ERROR 42000: INSERT command denied to user 'privtest'@'localhost' for table 'v1' +EXPLAIN INSERT INTO v1 SELECT * FROM t2; +ERROR 42000: INSERT command denied to user 'privtest'@'localhost' for table 'v1' +ANALYZE INSERT INTO v1 SELECT * FROM t2; +ERROR 42000: INSERT command denied to user 'privtest'@'localhost' for table 'v1' +REPLACE INTO v1 (a) VALUES (10); +ERROR 42000: INSERT command denied to user 'privtest'@'localhost' for table 'v1' +EXPLAIN REPLACE INTO v1 (a) VALUES (10); +ERROR 42000: INSERT command denied to user 'privtest'@'localhost' for table 'v1' +ANALYZE REPLACE INTO v1 (a) VALUES (10); +ERROR 42000: INSERT command denied to user 'privtest'@'localhost' for table 'v1' +REPLACE INTO v1 SELECT * FROM t2; +ERROR 42000: INSERT command denied to user 'privtest'@'localhost' for table 'v1' +EXPLAIN REPLACE INTO v1 SELECT * FROM t2; +ERROR 42000: INSERT command denied to user 'privtest'@'localhost' for table 'v1' +ANALYZE REPLACE INTO v1 SELECT * FROM t2; +ERROR 42000: INSERT command denied to user 'privtest'@'localhost' for table 'v1' +UPDATE v1 SET a = 10; +ERROR 42000: UPDATE command denied to user 'privtest'@'localhost' for table 'v1' +EXPLAIN UPDATE v1 SET a = 10; +ERROR 42000: UPDATE command denied to user 'privtest'@'localhost' for table 'v1' +ANALYZE UPDATE v1 SET a = 10; +ERROR 42000: UPDATE command denied to user 'privtest'@'localhost' for table 'v1' +UPDATE v1 SET a = a + 1; +ERROR 42000: UPDATE command denied to user 'privtest'@'localhost' for table 'v1' +EXPLAIN UPDATE v1 SET a = a + 1; +ERROR 42000: UPDATE command denied to user 'privtest'@'localhost' for table 'v1' +ANALYZE UPDATE v1 SET a = a + 1; +ERROR 42000: UPDATE command denied to user 'privtest'@'localhost' for table 'v1' +UPDATE v1, t2 SET v1.a = v1.a + 1 WHERE v1.a = t2.a; +ERROR 42000: SELECT command denied to user 'privtest'@'localhost' for table 'v1' +EXPLAIN UPDATE v1, t2 SET v1.a = v1.a + 1 WHERE v1.a = t2.a; +ERROR 42000: SELECT command denied to user 'privtest'@'localhost' for table 'v1' +ANALYZE UPDATE v1, t2 SET v1.a = v1.a + 1 WHERE v1.a = t2.a; +ERROR 42000: SELECT command denied to user 'privtest'@'localhost' for table 'v1' +DELETE FROM v1; +EXPLAIN DELETE FROM v1; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 29 Using where +ANALYZE DELETE FROM v1; +id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 29 29.00 100.00 0.00 Using where +DELETE FROM v1 WHERE a = 10; +ERROR 42000: SELECT command denied to user 'privtest'@'localhost' for column 'a' in table 'v1' +EXPLAIN DELETE FROM v1 WHERE a = 10; +ERROR 42000: SELECT command denied to user 'privtest'@'localhost' for column 'a' in table 'v1' +ANALYZE DELETE FROM v1 WHERE a = 10; +ERROR 42000: SELECT command denied to user 'privtest'@'localhost' for column 'a' in table 'v1' +DELETE FROM v1 USING v1, t2; +ERROR 42000: SELECT command denied to user 'privtest'@'localhost' for table 'v1' +EXPLAIN DELETE FROM v1 USING v1, t2; +ERROR 42000: SELECT command denied to user 'privtest'@'localhost' for table 'v1' +ANALYZE DELETE FROM v1 USING v1, t2; +ERROR 42000: SELECT command denied to user 'privtest'@'localhost' for table 'v1' +EXPLAIN DELETE FROM v1 USING v1, t2 WHERE v1.a = t2.a; +ERROR 42000: SELECT command denied to user 'privtest'@'localhost' for table 'v1' +EXPLAIN DELETE FROM v1 USING v1, t2 WHERE v1.a = t2.a; +ERROR 42000: SELECT command denied to user 'privtest'@'localhost' for table 'v1' +ANALYZE DELETE FROM v1 USING v1, t2 WHERE v1.a = t2.a; +ERROR 42000: SELECT command denied to user 'privtest'@'localhost' for table 'v1' +SELECT * FROM v1; +ERROR 42000: SELECT command denied to user 'privtest'@'localhost' for table 'v1' +EXPLAIN SELECT * FROM v1; +ERROR 42000: SELECT command denied to user 'privtest'@'localhost' for table 'v1' +ANALYZE SELECT * FROM v1; +ERROR 42000: SELECT command denied to user 'privtest'@'localhost' for table 'v1' +SELECT * FROM v1 WHERE a = 10; +ERROR 42000: SELECT command denied to user 'privtest'@'localhost' for table 'v1' +EXPLAIN SELECT * FROM v1 WHERE a = 10; +ERROR 42000: SELECT command denied to user 'privtest'@'localhost' for table 'v1' +ANALYZE SELECT * FROM v1 WHERE a = 10; +ERROR 42000: SELECT command denied to user 'privtest'@'localhost' for table 'v1' +SELECT * FROM v1 WHERE a IN ( SELECT a FROM t2 ); +ERROR 42000: SELECT command denied to user 'privtest'@'localhost' for table 'v1' +EXPLAIN SELECT * FROM v1 WHERE a IN ( SELECT a FROM t2 ); +ERROR 42000: SELECT command denied to user 'privtest'@'localhost' for table 'v1' +ANALYZE SELECT * FROM v1 WHERE a IN ( SELECT a FROM t2 ); +ERROR 42000: SELECT command denied to user 'privtest'@'localhost' for table 'v1' +#------------------------------------------------------------------------ +# I/R/U/D/S on the outer view +# Expectation: Can run the queries, but not EXPLAIN or ANALYZE +# because the query plan cannot be shown +# (that's how it works now) +#------------------------------------------------------------------------ +INSERT INTO v2 (a) VALUES (10); +EXPLAIN INSERT INTO v2 (a) VALUES (10); +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +ANALYZE INSERT INTO v2 (a) VALUES (10); +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +INSERT INTO v2 SELECT * FROM t2; +EXPLAIN INSERT INTO v2 SELECT * FROM t2; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +ANALYZE INSERT INTO v2 SELECT * FROM t2; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +REPLACE INTO v2 (a) VALUES (10); +EXPLAIN REPLACE INTO v2 (a) VALUES (10); +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +ANALYZE REPLACE INTO v2 (a) VALUES (10); +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +REPLACE INTO v2 SELECT * FROM t2; +EXPLAIN REPLACE INTO v2 SELECT * FROM t2; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +ANALYZE REPLACE INTO v2 SELECT * FROM t2; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +UPDATE v2 SET a = 10; +EXPLAIN UPDATE v2 SET a = 10; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +ANALYZE UPDATE v2 SET a = 10; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +UPDATE v2 SET a = a + 1; +EXPLAIN UPDATE v2 SET a = a + 1; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +ANALYZE UPDATE v2 SET a = a + 1; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +UPDATE v2, t2 SET v2.a = v2.a + 1 WHERE v2.a = t2.a; +EXPLAIN UPDATE v2, t2 SET v2.a = v2.a + 1 WHERE v2.a = t2.a; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +ANALYZE UPDATE v2, t2 SET v2.a = v2.a + 1 WHERE v2.a = t2.a; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +DELETE FROM v2; +EXPLAIN DELETE FROM v2; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +ANALYZE DELETE FROM v2; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +DELETE FROM v2 WHERE a = 10; +EXPLAIN DELETE FROM v2 WHERE a = 10; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +ANALYZE DELETE FROM v2 WHERE a = 10; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +DELETE FROM v2 USING v2, t2; +EXPLAIN DELETE FROM v2 USING v2, t2; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +ANALYZE DELETE FROM v2 USING v2, t2; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +DELETE FROM v2 USING v2, t2 WHERE v2.a = t2.a; +EXPLAIN DELETE FROM v2 USING v2, t2 WHERE v2.a = t2.a; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +ANALYZE DELETE FROM v2 USING v2, t2 WHERE v2.a = t2.a; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +SELECT * FROM v2; +a b +EXPLAIN SELECT * FROM v2; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +ANALYZE SELECT * FROM v2; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +SELECT * FROM v2 WHERE a = 10; +a b +EXPLAIN SELECT * FROM v2 WHERE a = 10; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +ANALYZE SELECT * FROM v2 WHERE a = 10; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +SELECT * FROM v2 WHERE a IN ( SELECT a FROM t2 ); +a b +EXPLAIN SELECT * FROM v2 WHERE a IN ( SELECT a FROM t2 ); +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +ANALYZE SELECT * FROM v2 WHERE a IN ( SELECT a FROM t2 ); +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table + +#======================================================================== +# Test: Grant SELECT, INSERT, UPDATE, DELETE, SHOW VIEW +#======================================================================== + +connection default; +REVOKE ALL PRIVILEGES ON privtest_db.v1 FROM 'privtest'@'localhost'; +GRANT SELECT, INSERT, UPDATE, DELETE, SHOW VIEW ON privtest_db.v1 TO 'privtest'@'localhost'; +connection con1; +#------------------------------------------------------------------------ +# I/R/U/D/S on the inner view +# Expectation: Can run everything +#------------------------------------------------------------------------ +INSERT INTO v1 (a) VALUES (10); +EXPLAIN INSERT INTO v1 (a) VALUES (10); +id select_type table type possible_keys key key_len ref rows Extra +1 INSERT t1 ALL NULL NULL NULL NULL NULL NULL +ANALYZE INSERT INTO v1 (a) VALUES (10); +id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra +1 INSERT t1 ALL NULL NULL NULL NULL NULL NULL 100.00 100.00 NULL +INSERT INTO v1 SELECT * FROM t2; +EXPLAIN INSERT INTO v1 SELECT * FROM t2; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t2 ALL NULL NULL NULL NULL 3 +ANALYZE INSERT INTO v1 SELECT * FROM t2; +id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra +1 SIMPLE t2 ALL NULL NULL NULL NULL 3 3.00 100.00 100.00 +REPLACE INTO v1 (a) VALUES (10); +EXPLAIN REPLACE INTO v1 (a) VALUES (10); +id select_type table type possible_keys key key_len ref rows Extra +1 INSERT t1 ALL NULL NULL NULL NULL NULL NULL +ANALYZE REPLACE INTO v1 (a) VALUES (10); +id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra +1 INSERT t1 ALL NULL NULL NULL NULL NULL NULL 100.00 100.00 NULL +REPLACE INTO v1 SELECT * FROM t2; +EXPLAIN REPLACE INTO v1 SELECT * FROM t2; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t2 ALL NULL NULL NULL NULL 3 +ANALYZE REPLACE INTO v1 SELECT * FROM t2; +id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra +1 SIMPLE t2 ALL NULL NULL NULL NULL 3 3.00 100.00 100.00 +UPDATE v1 SET a = 10; +EXPLAIN UPDATE v1 SET a = 10; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 47 Using where +ANALYZE UPDATE v1 SET a = 10; +id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 47 47.00 100.00 25.53 Using where +UPDATE v1 SET a = a + 1; +EXPLAIN UPDATE v1 SET a = a + 1; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 47 Using where +ANALYZE UPDATE v1 SET a = a + 1; +id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 47 47.00 100.00 25.53 Using where +UPDATE v1, t2 SET v1.a = v1.a + 1 WHERE v1.a = t2.a; +EXPLAIN UPDATE v1, t2 SET v1.a = v1.a + 1 WHERE v1.a = t2.a; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t2 ALL NULL NULL NULL NULL 3 +1 SIMPLE t1 ALL NULL NULL NULL NULL 47 Using where +ANALYZE UPDATE v1, t2 SET v1.a = v1.a + 1 WHERE v1.a = t2.a; +id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra +1 SIMPLE t2 ALL NULL NULL NULL NULL 3 3.00 100.00 100.00 +1 SIMPLE t1 ALL NULL NULL NULL NULL 47 47.00 100.00 0.00 Using where +DELETE FROM v1; +EXPLAIN DELETE FROM v1; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 35 Using where +ANALYZE DELETE FROM v1; +id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 35 35.00 100.00 0.00 Using where +DELETE FROM v1 WHERE a = 10; +EXPLAIN DELETE FROM v1 WHERE a = 10; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 35 Using where +ANALYZE DELETE FROM v1 WHERE a = 10; +id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 35 35.00 100.00 0.00 Using where +DELETE FROM v1 USING v1, t2; +EXPLAIN DELETE FROM v1 USING v1, t2; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t2 ALL NULL NULL NULL NULL 3 +1 SIMPLE t1 ALL NULL NULL NULL NULL 35 Using where +ANALYZE DELETE FROM v1 USING v1, t2; +id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra +1 SIMPLE t2 ALL NULL NULL NULL NULL 3 3.00 100.00 100.00 +1 SIMPLE t1 ALL NULL NULL NULL NULL 35 35.00 100.00 0.00 Using where +DELETE FROM v1 USING v1, t2 WHERE v1.a = t2.a; +EXPLAIN DELETE FROM v1 USING v1, t2 WHERE v1.a = t2.a; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t2 ALL NULL NULL NULL NULL 3 +1 SIMPLE t1 ALL NULL NULL NULL NULL 35 Using where +ANALYZE DELETE FROM v1 USING v1, t2 WHERE v1.a = t2.a; +id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra +1 SIMPLE t2 ALL NULL NULL NULL NULL 3 3.00 100.00 100.00 +1 SIMPLE t1 ALL NULL NULL NULL NULL 35 35.00 100.00 0.00 Using where +SELECT * FROM v1; +a b +EXPLAIN SELECT * FROM v1; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 35 Using where +ANALYZE SELECT * FROM v1; +id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 35 35.00 100.00 0.00 Using where +SELECT * FROM v1 WHERE a = 10; +a b +EXPLAIN SELECT * FROM v1 WHERE a = 10; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 35 Using where +ANALYZE SELECT * FROM v1 WHERE a = 10; +id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 35 35.00 100.00 0.00 Using where +SELECT * FROM v1 WHERE a IN ( SELECT a FROM t2 ); +a b +EXPLAIN SELECT * FROM v1 WHERE a IN ( SELECT a FROM t2 ); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 3 +1 PRIMARY t1 ALL NULL NULL NULL NULL 35 Using where; Using join buffer (flat, BNL join) +2 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 +ANALYZE SELECT * FROM v1 WHERE a IN ( SELECT a FROM t2 ); +id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra +1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 3 3.00 100.00 100.00 +1 PRIMARY t1 ALL NULL NULL NULL NULL 35 35.00 100.00 0.00 Using where; Using join buffer (flat, BNL join) +2 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 3.00 100.00 100.00 +#------------------------------------------------------------------------ +# I/R/U/D/S on the outer view +# Expectation: Can run everything +#------------------------------------------------------------------------ +INSERT INTO v2 (a) VALUES (10); +EXPLAIN INSERT INTO v2 (a) VALUES (10); +id select_type table type possible_keys key key_len ref rows Extra +1 INSERT t1 ALL NULL NULL NULL NULL NULL NULL +ANALYZE INSERT INTO v2 (a) VALUES (10); +id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra +1 INSERT t1 ALL NULL NULL NULL NULL NULL NULL 100.00 100.00 NULL +INSERT INTO v2 SELECT * FROM t2; +EXPLAIN INSERT INTO v2 SELECT * FROM t2; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t2 ALL NULL NULL NULL NULL 3 +ANALYZE INSERT INTO v2 SELECT * FROM t2; +id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra +1 SIMPLE t2 ALL NULL NULL NULL NULL 3 3.00 100.00 100.00 +REPLACE INTO v2 (a) VALUES (10); +EXPLAIN REPLACE INTO v2 (a) VALUES (10); +id select_type table type possible_keys key key_len ref rows Extra +1 INSERT t1 ALL NULL NULL NULL NULL NULL NULL +ANALYZE REPLACE INTO v2 (a) VALUES (10); +id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra +1 INSERT t1 ALL NULL NULL NULL NULL NULL NULL 100.00 100.00 NULL +REPLACE INTO v2 SELECT * FROM t2; +EXPLAIN REPLACE INTO v2 SELECT * FROM t2; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t2 ALL NULL NULL NULL NULL 3 +ANALYZE REPLACE INTO v2 SELECT * FROM t2; +id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra +1 SIMPLE t2 ALL NULL NULL NULL NULL 3 3.00 100.00 100.00 +UPDATE v2 SET a = 10; +EXPLAIN UPDATE v2 SET a = 10; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 51 Using where +ANALYZE UPDATE v2 SET a = 10; +id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 51 51.00 100.00 23.53 Using where +UPDATE v2 SET a = a + 1; +EXPLAIN UPDATE v2 SET a = a + 1; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 51 Using where +ANALYZE UPDATE v2 SET a = a + 1; +id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 51 51.00 100.00 23.53 Using where +UPDATE v2, t2 SET v2.a = v2.a + 1 WHERE v2.a = t2.a; +EXPLAIN UPDATE v2, t2 SET v2.a = v2.a + 1 WHERE v2.a = t2.a; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t2 ALL NULL NULL NULL NULL 3 Using where +1 SIMPLE t1 ALL NULL NULL NULL NULL 51 Using where +ANALYZE UPDATE v2, t2 SET v2.a = v2.a + 1 WHERE v2.a = t2.a; +id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra +1 SIMPLE t2 ALL NULL NULL NULL NULL 3 3.00 100.00 100.00 Using where +1 SIMPLE t1 ALL NULL NULL NULL NULL 51 51.00 100.00 0.00 Using where +DELETE FROM v2; +EXPLAIN DELETE FROM v2; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 39 Using where +ANALYZE DELETE FROM v2; +id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 39 39.00 100.00 0.00 Using where +DELETE FROM v2 WHERE a = 10; +EXPLAIN DELETE FROM v2 WHERE a = 10; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 39 Using where +ANALYZE DELETE FROM v2 WHERE a = 10; +id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 39 39.00 100.00 0.00 Using where +DELETE FROM v2 USING v2, t2; +ANALYZE DELETE FROM v2 USING v2, t2; +id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra +1 SIMPLE t2 ALL NULL NULL NULL NULL 3 3.00 100.00 100.00 +1 SIMPLE t1 ALL NULL NULL NULL NULL 39 39.00 100.00 0.00 Using where +DELETE FROM v2 USING v2, t2 WHERE v2.a = t2.a; +ANALYZE DELETE FROM v2 USING v2, t2 WHERE v2.a = t2.a; +id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra +1 SIMPLE t2 ALL NULL NULL NULL NULL 3 3.00 100.00 100.00 Using where +1 SIMPLE t1 ALL NULL NULL NULL NULL 39 39.00 100.00 0.00 Using where +SELECT * FROM v2; +a b +EXPLAIN SELECT * FROM v2; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 39 Using where +ANALYZE SELECT * FROM v2; +id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 39 39.00 100.00 0.00 Using where +SELECT * FROM v2 WHERE a = 10; +a b +EXPLAIN SELECT * FROM v2 WHERE a = 10; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 39 Using where +ANALYZE SELECT * FROM v2 WHERE a = 10; +id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 39 39.00 100.00 0.00 Using where +SELECT * FROM v2 WHERE a IN ( SELECT a FROM t2 ); +a b +EXPLAIN SELECT * FROM v2 WHERE a IN ( SELECT a FROM t2 ); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 3 +1 PRIMARY t1 ALL NULL NULL NULL NULL 39 Using where; Using join buffer (flat, BNL join) +2 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 Using where +ANALYZE SELECT * FROM v2 WHERE a IN ( SELECT a FROM t2 ); +id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra +1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 3 3.00 100.00 100.00 +1 PRIMARY t1 ALL NULL NULL NULL NULL 39 39.00 100.00 0.00 Using where; Using join buffer (flat, BNL join) +2 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 3.00 100.00 100.00 Using where + +######################################################################### +# Outer view permission tests +# (we modify permissions on the outer view, keeping ALL the rest) +######################################################################### + + +#======================================================================== +# Test: No permissions on the outer view +#======================================================================== + +connection default; +GRANT ALL ON privtest_db.t1 TO 'privtest'@'localhost'; +GRANT ALL ON privtest_db.v1 TO 'privtest'@'localhost'; +REVOKE ALL PRIVILEGES ON privtest_db.v2 FROM 'privtest'@'localhost'; +connection con1; +#------------------------------------------------------------------------ +# I/R/U/D/S on the outer view +# Expectation: Cannot run anything +#------------------------------------------------------------------------ +INSERT INTO v2 (a) VALUES (10); +ERROR 42000: INSERT command denied to user 'privtest'@'localhost' for table 'v2' +EXPLAIN INSERT INTO v2 (a) VALUES (10); +ERROR 42000: INSERT command denied to user 'privtest'@'localhost' for table 'v2' +ANALYZE INSERT INTO v2 (a) VALUES (10); +ERROR 42000: INSERT command denied to user 'privtest'@'localhost' for table 'v2' +INSERT INTO v2 SELECT * FROM t2; +ERROR 42000: INSERT command denied to user 'privtest'@'localhost' for table 'v2' +EXPLAIN INSERT INTO v2 SELECT * FROM t2; +ERROR 42000: INSERT command denied to user 'privtest'@'localhost' for table 'v2' +ANALYZE INSERT INTO v2 SELECT * FROM t2; +ERROR 42000: INSERT command denied to user 'privtest'@'localhost' for table 'v2' +REPLACE INTO v2 (a) VALUES (10); +ERROR 42000: INSERT, DELETE command denied to user 'privtest'@'localhost' for table 'v2' +EXPLAIN REPLACE INTO v2 (a) VALUES (10); +ERROR 42000: INSERT, DELETE command denied to user 'privtest'@'localhost' for table 'v2' +ANALYZE REPLACE INTO v2 (a) VALUES (10); +ERROR 42000: INSERT, DELETE command denied to user 'privtest'@'localhost' for table 'v2' +REPLACE INTO v2 SELECT * FROM t2; +ERROR 42000: INSERT, DELETE command denied to user 'privtest'@'localhost' for table 'v2' +EXPLAIN REPLACE INTO v2 SELECT * FROM t2; +ERROR 42000: INSERT, DELETE command denied to user 'privtest'@'localhost' for table 'v2' +ANALYZE REPLACE INTO v2 SELECT * FROM t2; +ERROR 42000: INSERT, DELETE command denied to user 'privtest'@'localhost' for table 'v2' +UPDATE v2 SET a = 10; +ERROR 42000: UPDATE command denied to user 'privtest'@'localhost' for table 'v2' +EXPLAIN UPDATE v2 SET a = 10; +ERROR 42000: UPDATE command denied to user 'privtest'@'localhost' for table 'v2' +ANALYZE UPDATE v2 SET a = 10; +ERROR 42000: UPDATE command denied to user 'privtest'@'localhost' for table 'v2' +UPDATE v2 SET a = a + 1; +ERROR 42000: UPDATE command denied to user 'privtest'@'localhost' for table 'v2' +EXPLAIN UPDATE v2 SET a = a + 1; +ERROR 42000: UPDATE command denied to user 'privtest'@'localhost' for table 'v2' +ANALYZE UPDATE v2 SET a = a + 1; +ERROR 42000: UPDATE command denied to user 'privtest'@'localhost' for table 'v2' +UPDATE v2, t2 SET v2.a = v2.a + 1 WHERE v2.a = t2.a; +ERROR 42000: SELECT command denied to user 'privtest'@'localhost' for table 'v2' +EXPLAIN UPDATE v2, t2 SET v2.a = v2.a + 1 WHERE v2.a = t2.a; +ERROR 42000: SELECT command denied to user 'privtest'@'localhost' for table 'v2' +ANALYZE UPDATE v2, t2 SET v2.a = v2.a + 1 WHERE v2.a = t2.a; +ERROR 42000: SELECT command denied to user 'privtest'@'localhost' for table 'v2' +DELETE FROM v2; +ERROR 42000: DELETE command denied to user 'privtest'@'localhost' for table 'v2' +EXPLAIN DELETE FROM v2; +ERROR 42000: DELETE command denied to user 'privtest'@'localhost' for table 'v2' +ANALYZE DELETE FROM v2; +ERROR 42000: DELETE command denied to user 'privtest'@'localhost' for table 'v2' +DELETE FROM v2 WHERE a = 10; +ERROR 42000: DELETE command denied to user 'privtest'@'localhost' for table 'v2' +EXPLAIN DELETE FROM v2 WHERE a = 10; +ERROR 42000: DELETE command denied to user 'privtest'@'localhost' for table 'v2' +ANALYZE DELETE FROM v2 WHERE a = 10; +ERROR 42000: DELETE command denied to user 'privtest'@'localhost' for table 'v2' +DELETE FROM v2 USING v2, t2; +ERROR 42000: SELECT command denied to user 'privtest'@'localhost' for table 'v2' +EXPLAIN DELETE FROM v2 USING v2, t2; +ERROR 42000: SELECT command denied to user 'privtest'@'localhost' for table 'v2' +ANALYZE DELETE FROM v2 USING v2, t2; +ERROR 42000: SELECT command denied to user 'privtest'@'localhost' for table 'v2' +DELETE FROM v2 USING v2, t2 WHERE v2.a = t2.a; +ERROR 42000: SELECT command denied to user 'privtest'@'localhost' for table 'v2' +EXPLAIN DELETE FROM v2 USING v2, t2 WHERE v2.a = t2.a; +ERROR 42000: SELECT command denied to user 'privtest'@'localhost' for table 'v2' +ANALYZE DELETE FROM v2 USING v2, t2 WHERE v2.a = t2.a; +ERROR 42000: SELECT command denied to user 'privtest'@'localhost' for table 'v2' +SELECT * FROM v2; +ERROR 42000: SELECT command denied to user 'privtest'@'localhost' for table 'v2' +EXPLAIN SELECT * FROM v2; +ERROR 42000: SELECT command denied to user 'privtest'@'localhost' for table 'v2' +ANALYZE SELECT * FROM v2; +ERROR 42000: SELECT command denied to user 'privtest'@'localhost' for table 'v2' +SELECT * FROM v2 WHERE a = 10; +ERROR 42000: SELECT command denied to user 'privtest'@'localhost' for table 'v2' +EXPLAIN SELECT * FROM v2 WHERE a = 10; +ERROR 42000: SELECT command denied to user 'privtest'@'localhost' for table 'v2' +ANALYZE SELECT * FROM v2 WHERE a = 10; +ERROR 42000: SELECT command denied to user 'privtest'@'localhost' for table 'v2' +SELECT * FROM v2 WHERE a IN ( SELECT a FROM t2 ); +ERROR 42000: SELECT command denied to user 'privtest'@'localhost' for table 'v2' +EXPLAIN SELECT * FROM v2 WHERE a IN ( SELECT a FROM t2 ); +ERROR 42000: SELECT command denied to user 'privtest'@'localhost' for table 'v2' +ANALYZE SELECT * FROM v2 WHERE a IN ( SELECT a FROM t2 ); +ERROR 42000: SELECT command denied to user 'privtest'@'localhost' for table 'v2' + +#======================================================================== +# Test: Grant SHOW VIEW on the outer view +#======================================================================== + +connection default; +GRANT SHOW VIEW ON privtest_db.v2 TO 'privtest'@'localhost'; +connection con1; +#------------------------------------------------------------------------ +# I/R/U/D/S on the outer view +# Expectation: Cannot run anything +#------------------------------------------------------------------------ +INSERT INTO v2 (a) VALUES (10); +ERROR 42000: INSERT command denied to user 'privtest'@'localhost' for table 'v2' +EXPLAIN INSERT INTO v2 (a) VALUES (10); +ERROR 42000: INSERT command denied to user 'privtest'@'localhost' for table 'v2' +ANALYZE INSERT INTO v2 (a) VALUES (10); +ERROR 42000: INSERT command denied to user 'privtest'@'localhost' for table 'v2' +INSERT INTO v2 SELECT * FROM t2; +ERROR 42000: INSERT command denied to user 'privtest'@'localhost' for table 'v2' +EXPLAIN INSERT INTO v2 SELECT * FROM t2; +ERROR 42000: INSERT command denied to user 'privtest'@'localhost' for table 'v2' +ANALYZE INSERT INTO v2 SELECT * FROM t2; +ERROR 42000: INSERT command denied to user 'privtest'@'localhost' for table 'v2' +REPLACE INTO v2 (a) VALUES (10); +ERROR 42000: INSERT, DELETE command denied to user 'privtest'@'localhost' for table 'v2' +EXPLAIN REPLACE INTO v2 (a) VALUES (10); +ERROR 42000: INSERT, DELETE command denied to user 'privtest'@'localhost' for table 'v2' +ANALYZE REPLACE INTO v2 (a) VALUES (10); +ERROR 42000: INSERT, DELETE command denied to user 'privtest'@'localhost' for table 'v2' +REPLACE INTO v2 SELECT * FROM t2; +ERROR 42000: INSERT, DELETE command denied to user 'privtest'@'localhost' for table 'v2' +EXPLAIN REPLACE INTO v2 SELECT * FROM t2; +ERROR 42000: INSERT, DELETE command denied to user 'privtest'@'localhost' for table 'v2' +ANALYZE REPLACE INTO v2 SELECT * FROM t2; +ERROR 42000: INSERT, DELETE command denied to user 'privtest'@'localhost' for table 'v2' +UPDATE v2 SET a = 10; +ERROR 42000: UPDATE command denied to user 'privtest'@'localhost' for table 'v2' +EXPLAIN UPDATE v2 SET a = 10; +ERROR 42000: UPDATE command denied to user 'privtest'@'localhost' for table 'v2' +ANALYZE UPDATE v2 SET a = 10; +ERROR 42000: UPDATE command denied to user 'privtest'@'localhost' for table 'v2' +UPDATE v2 SET a = a + 1; +ERROR 42000: UPDATE command denied to user 'privtest'@'localhost' for table 'v2' +EXPLAIN UPDATE v2 SET a = a + 1; +ERROR 42000: UPDATE command denied to user 'privtest'@'localhost' for table 'v2' +ANALYZE UPDATE v2 SET a = a + 1; +ERROR 42000: UPDATE command denied to user 'privtest'@'localhost' for table 'v2' +UPDATE v2, t2 SET v2.a = v2.a + 1 WHERE v2.a = t2.a; +ERROR 42000: SELECT command denied to user 'privtest'@'localhost' for table 'v2' +EXPLAIN UPDATE v2, t2 SET v2.a = v2.a + 1 WHERE v2.a = t2.a; +ERROR 42000: SELECT command denied to user 'privtest'@'localhost' for table 'v2' +ANALYZE UPDATE v2, t2 SET v2.a = v2.a + 1 WHERE v2.a = t2.a; +ERROR 42000: SELECT command denied to user 'privtest'@'localhost' for table 'v2' +DELETE FROM v2; +ERROR 42000: DELETE command denied to user 'privtest'@'localhost' for table 'v2' +EXPLAIN DELETE FROM v2; +ERROR 42000: DELETE command denied to user 'privtest'@'localhost' for table 'v2' +ANALYZE DELETE FROM v2; +ERROR 42000: DELETE command denied to user 'privtest'@'localhost' for table 'v2' +DELETE FROM v2 WHERE a = 10; +ERROR 42000: DELETE command denied to user 'privtest'@'localhost' for table 'v2' +EXPLAIN DELETE FROM v2 WHERE a = 10; +ERROR 42000: DELETE command denied to user 'privtest'@'localhost' for table 'v2' +ANALYZE DELETE FROM v2 WHERE a = 10; +ERROR 42000: DELETE command denied to user 'privtest'@'localhost' for table 'v2' +DELETE FROM v2 USING v2, t2; +ERROR 42000: SELECT command denied to user 'privtest'@'localhost' for table 'v2' +EXPLAIN DELETE FROM v2 USING v2, t2; +ERROR 42000: SELECT command denied to user 'privtest'@'localhost' for table 'v2' +ANALYZE DELETE FROM v2 USING v2, t2; +ERROR 42000: SELECT command denied to user 'privtest'@'localhost' for table 'v2' +DELETE FROM v2 USING v2, t2 WHERE v2.a = t2.a; +ERROR 42000: SELECT command denied to user 'privtest'@'localhost' for table 'v2' +EXPLAIN DELETE FROM v2 USING v2, t2 WHERE v2.a = t2.a; +ERROR 42000: SELECT command denied to user 'privtest'@'localhost' for table 'v2' +ANALYZE DELETE FROM v2 USING v2, t2 WHERE v2.a = t2.a; +ERROR 42000: SELECT command denied to user 'privtest'@'localhost' for table 'v2' +SELECT * FROM v2; +ERROR 42000: SELECT command denied to user 'privtest'@'localhost' for table 'v2' +EXPLAIN SELECT * FROM v2; +ERROR 42000: SELECT command denied to user 'privtest'@'localhost' for table 'v2' +ANALYZE SELECT * FROM v2; +ERROR 42000: SELECT command denied to user 'privtest'@'localhost' for table 'v2' +SELECT * FROM v2 WHERE a = 10; +ERROR 42000: SELECT command denied to user 'privtest'@'localhost' for table 'v2' +EXPLAIN SELECT * FROM v2 WHERE a = 10; +ERROR 42000: SELECT command denied to user 'privtest'@'localhost' for table 'v2' +ANALYZE SELECT * FROM v2 WHERE a = 10; +ERROR 42000: SELECT command denied to user 'privtest'@'localhost' for table 'v2' +SELECT * FROM v2 WHERE a IN ( SELECT a FROM t2 ); +ERROR 42000: SELECT command denied to user 'privtest'@'localhost' for table 'v2' +EXPLAIN SELECT * FROM v2 WHERE a IN ( SELECT a FROM t2 ); +ERROR 42000: SELECT command denied to user 'privtest'@'localhost' for table 'v2' +ANALYZE SELECT * FROM v2 WHERE a IN ( SELECT a FROM t2 ); +ERROR 42000: SELECT command denied to user 'privtest'@'localhost' for table 'v2' + +#======================================================================== +# Test: Grant SHOW VIEW, SELECT(a) on the outer view +#======================================================================== + +connection default; +REVOKE ALL PRIVILEGES ON privtest_db.v2 FROM 'privtest'@'localhost'; +GRANT SELECT(a), SHOW VIEW ON privtest_db.v2 TO 'privtest'@'localhost'; +connection con1; +#------------------------------------------------------------------------ +# I/R/U/D/S on the outer view +# Expectation: Can run SELECT, EXPLAIN SELECT and ANALYZE SELECT +# when only `a` column is involved +#------------------------------------------------------------------------ +INSERT INTO v2 (a) VALUES (10); +ERROR 42000: INSERT command denied to user 'privtest'@'localhost' for table 'v2' +EXPLAIN INSERT INTO v2 (a) VALUES (10); +ERROR 42000: INSERT command denied to user 'privtest'@'localhost' for table 'v2' +ANALYZE INSERT INTO v2 (a) VALUES (10); +ERROR 42000: INSERT command denied to user 'privtest'@'localhost' for table 'v2' +INSERT INTO v2 SELECT * FROM t2; +ERROR 42000: INSERT command denied to user 'privtest'@'localhost' for table 'v2' +EXPLAIN INSERT INTO v2 SELECT * FROM t2; +ERROR 42000: INSERT command denied to user 'privtest'@'localhost' for table 'v2' +ANALYZE INSERT INTO v2 SELECT * FROM t2; +ERROR 42000: INSERT command denied to user 'privtest'@'localhost' for table 'v2' +REPLACE INTO v2 (a) VALUES (10); +ERROR 42000: INSERT, DELETE command denied to user 'privtest'@'localhost' for table 'v2' +EXPLAIN REPLACE INTO v2 (a) VALUES (10); +ERROR 42000: INSERT, DELETE command denied to user 'privtest'@'localhost' for table 'v2' +ANALYZE REPLACE INTO v2 (a) VALUES (10); +ERROR 42000: INSERT, DELETE command denied to user 'privtest'@'localhost' for table 'v2' +REPLACE INTO v2 SELECT * FROM t2; +ERROR 42000: INSERT, DELETE command denied to user 'privtest'@'localhost' for table 'v2' +EXPLAIN REPLACE INTO v2 SELECT * FROM t2; +ERROR 42000: INSERT, DELETE command denied to user 'privtest'@'localhost' for table 'v2' +ANALYZE REPLACE INTO v2 SELECT * FROM t2; +ERROR 42000: INSERT, DELETE command denied to user 'privtest'@'localhost' for table 'v2' +UPDATE v2 SET a = 10; +ERROR 42000: UPDATE command denied to user 'privtest'@'localhost' for table 'v2' +EXPLAIN UPDATE v2 SET a = 10; +ERROR 42000: UPDATE command denied to user 'privtest'@'localhost' for table 'v2' +ANALYZE UPDATE v2 SET a = 10; +ERROR 42000: UPDATE command denied to user 'privtest'@'localhost' for table 'v2' +UPDATE v2 SET a = a + 1; +ERROR 42000: UPDATE command denied to user 'privtest'@'localhost' for table 'v2' +EXPLAIN UPDATE v2 SET a = a + 1; +ERROR 42000: UPDATE command denied to user 'privtest'@'localhost' for table 'v2' +ANALYZE UPDATE v2 SET a = a + 1; +ERROR 42000: UPDATE command denied to user 'privtest'@'localhost' for table 'v2' +UPDATE v2, t2 SET v2.a = v2.a + 1 WHERE v2.a = t2.a; +ERROR 42000: UPDATE command denied to user 'privtest'@'localhost' for table 'v2' +EXPLAIN UPDATE v2, t2 SET v2.a = v2.a + 1 WHERE v2.a = t2.a; +ERROR 42000: UPDATE command denied to user 'privtest'@'localhost' for table 'v2' +ANALYZE UPDATE v2, t2 SET v2.a = v2.a + 1 WHERE v2.a = t2.a; +ERROR 42000: UPDATE command denied to user 'privtest'@'localhost' for table 'v2' +DELETE FROM v2; +ERROR 42000: DELETE command denied to user 'privtest'@'localhost' for table 'v2' +EXPLAIN DELETE FROM v2; +ERROR 42000: DELETE command denied to user 'privtest'@'localhost' for table 'v2' +ANALYZE DELETE FROM v2; +ERROR 42000: DELETE command denied to user 'privtest'@'localhost' for table 'v2' +DELETE FROM v2 WHERE a = 10; +ERROR 42000: DELETE command denied to user 'privtest'@'localhost' for table 'v2' +EXPLAIN DELETE FROM v2 WHERE a = 10; +ERROR 42000: DELETE command denied to user 'privtest'@'localhost' for table 'v2' +ANALYZE DELETE FROM v2 WHERE a = 10; +ERROR 42000: DELETE command denied to user 'privtest'@'localhost' for table 'v2' +DELETE FROM v2 USING v2, t2; +ERROR 42000: DELETE command denied to user 'privtest'@'localhost' for table 'v2' +EXPLAIN DELETE FROM v2 USING v2, t2; +ERROR 42000: DELETE command denied to user 'privtest'@'localhost' for table 'v2' +ANALYZE DELETE FROM v2 USING v2, t2; +ERROR 42000: DELETE command denied to user 'privtest'@'localhost' for table 'v2' +DELETE FROM v2 USING v2, t2 WHERE v2.a = t2.a; +ERROR 42000: DELETE command denied to user 'privtest'@'localhost' for table 'v2' +EXPLAIN DELETE FROM v2 USING v2, t2 WHERE v2.a = t2.a; +ERROR 42000: DELETE command denied to user 'privtest'@'localhost' for table 'v2' +ANALYZE DELETE FROM v2 USING v2, t2 WHERE v2.a = t2.a; +ERROR 42000: DELETE command denied to user 'privtest'@'localhost' for table 'v2' +SELECT a FROM v2; +a +EXPLAIN SELECT a FROM v2; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 39 Using where +ANALYZE SELECT a FROM v2; +id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 39 39.00 100.00 0.00 Using where +SELECT * FROM v2; +ERROR 42000: SELECT command denied to user 'privtest'@'localhost' for table 'v2' +EXPLAIN SELECT * FROM v2; +ERROR 42000: SELECT command denied to user 'privtest'@'localhost' for table 'v2' +ANALYZE SELECT * FROM v2; +ERROR 42000: SELECT command denied to user 'privtest'@'localhost' for table 'v2' +SELECT * FROM v2 WHERE a = 10; +ERROR 42000: SELECT command denied to user 'privtest'@'localhost' for table 'v2' +EXPLAIN SELECT * FROM v2 WHERE a = 10; +ERROR 42000: SELECT command denied to user 'privtest'@'localhost' for table 'v2' +ANALYZE SELECT * FROM v2 WHERE a = 10; +ERROR 42000: SELECT command denied to user 'privtest'@'localhost' for table 'v2' +SELECT a FROM v2 WHERE a = 10; +a +EXPLAIN SELECT a FROM v2 WHERE a = 10; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 39 Using where +ANALYZE SELECT a FROM v2 WHERE a = 10; +id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 39 39.00 100.00 0.00 Using where +SELECT * FROM v2 WHERE a IN ( SELECT a FROM t2 ); +ERROR 42000: SELECT command denied to user 'privtest'@'localhost' for table 'v2' +EXPLAIN SELECT * FROM v2 WHERE a IN ( SELECT a FROM t2 ); +ERROR 42000: SELECT command denied to user 'privtest'@'localhost' for table 'v2' +ANALYZE SELECT * FROM v2 WHERE a IN ( SELECT a FROM t2 ); +ERROR 42000: SELECT command denied to user 'privtest'@'localhost' for table 'v2' + +#======================================================================== +# Test: Grant SELECT on the outer view +#======================================================================== + +connection default; +REVOKE ALL PRIVILEGES ON privtest_db.v2 FROM 'privtest'@'localhost'; +GRANT SELECT ON privtest_db.v2 TO 'privtest'@'localhost'; +connection con1; +#------------------------------------------------------------------------ +# I/R/U/D/S on the inner view +# Expectation: Can run SELECT, but not EXPLAIN SELECT or ANALYZE SELECT +# because the query plan cannot be shown +# (it could have revealed the structure of the view) +#------------------------------------------------------------------------ +INSERT INTO v2 (a) VALUES (10); +ERROR 42000: INSERT command denied to user 'privtest'@'localhost' for table 'v2' +EXPLAIN INSERT INTO v2 (a) VALUES (10); +ERROR 42000: INSERT command denied to user 'privtest'@'localhost' for table 'v2' +ANALYZE INSERT INTO v2 (a) VALUES (10); +ERROR 42000: INSERT command denied to user 'privtest'@'localhost' for table 'v2' +INSERT INTO v2 SELECT * FROM t2; +ERROR 42000: INSERT command denied to user 'privtest'@'localhost' for table 'v2' +EXPLAIN INSERT INTO v2 SELECT * FROM t2; +ERROR 42000: INSERT command denied to user 'privtest'@'localhost' for table 'v2' +ANALYZE INSERT INTO v2 SELECT * FROM t2; +ERROR 42000: INSERT command denied to user 'privtest'@'localhost' for table 'v2' +REPLACE INTO v2 (a) VALUES (10); +ERROR 42000: INSERT, DELETE command denied to user 'privtest'@'localhost' for table 'v2' +EXPLAIN REPLACE INTO v2 (a) VALUES (10); +ERROR 42000: INSERT, DELETE command denied to user 'privtest'@'localhost' for table 'v2' +ANALYZE REPLACE INTO v2 (a) VALUES (10); +ERROR 42000: INSERT, DELETE command denied to user 'privtest'@'localhost' for table 'v2' +REPLACE INTO v2 SELECT * FROM t2; +ERROR 42000: INSERT, DELETE command denied to user 'privtest'@'localhost' for table 'v2' +EXPLAIN REPLACE INTO v2 SELECT * FROM t2; +ERROR 42000: INSERT, DELETE command denied to user 'privtest'@'localhost' for table 'v2' +ANALYZE REPLACE INTO v2 SELECT * FROM t2; +ERROR 42000: INSERT, DELETE command denied to user 'privtest'@'localhost' for table 'v2' +UPDATE v2 SET a = 10; +ERROR 42000: UPDATE command denied to user 'privtest'@'localhost' for table 'v2' +EXPLAIN UPDATE v2 SET a = 10; +ERROR 42000: UPDATE command denied to user 'privtest'@'localhost' for table 'v2' +ANALYZE UPDATE v2 SET a = 10; +ERROR 42000: UPDATE command denied to user 'privtest'@'localhost' for table 'v2' +UPDATE v2 SET a = a + 1; +ERROR 42000: UPDATE command denied to user 'privtest'@'localhost' for table 'v2' +EXPLAIN UPDATE v2 SET a = a + 1; +ERROR 42000: UPDATE command denied to user 'privtest'@'localhost' for table 'v2' +ANALYZE UPDATE v2 SET a = a + 1; +ERROR 42000: UPDATE command denied to user 'privtest'@'localhost' for table 'v2' +UPDATE v2, t2 SET v2.a = v2.a + 1 WHERE v2.a = t2.a; +ERROR 42000: UPDATE command denied to user 'privtest'@'localhost' for table 'v2' +EXPLAIN UPDATE v2, t2 SET v2.a = v2.a + 1 WHERE v2.a = t2.a; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +ANALYZE UPDATE v2, t2 SET v2.a = v2.a + 1 WHERE v2.a = t2.a; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +DELETE FROM v2; +ERROR 42000: DELETE command denied to user 'privtest'@'localhost' for table 'v2' +EXPLAIN DELETE FROM v2; +ERROR 42000: DELETE command denied to user 'privtest'@'localhost' for table 'v2' +ANALYZE DELETE FROM v2; +ERROR 42000: DELETE command denied to user 'privtest'@'localhost' for table 'v2' +DELETE FROM v2 WHERE a = 10; +ERROR 42000: DELETE command denied to user 'privtest'@'localhost' for table 'v2' +EXPLAIN DELETE FROM v2 WHERE a = 10; +ERROR 42000: DELETE command denied to user 'privtest'@'localhost' for table 'v2' +ANALYZE DELETE FROM v2 WHERE a = 10; +ERROR 42000: DELETE command denied to user 'privtest'@'localhost' for table 'v2' +DELETE FROM v2 USING v2, t2; +ERROR 42000: DELETE command denied to user 'privtest'@'localhost' for table 'v2' +EXPLAIN DELETE FROM v2 USING v2, t2; +ERROR 42000: DELETE command denied to user 'privtest'@'localhost' for table 'v2' +ANALYZE DELETE FROM v2 USING v2, t2; +ERROR 42000: DELETE command denied to user 'privtest'@'localhost' for table 'v2' +DELETE FROM v2 USING v2, t2 WHERE v2.a = t2.a; +ERROR 42000: DELETE command denied to user 'privtest'@'localhost' for table 'v2' +EXPLAIN DELETE FROM v2 USING v2, t2 WHERE v2.a = t2.a; +ERROR 42000: DELETE command denied to user 'privtest'@'localhost' for table 'v2' +ANALYZE DELETE FROM v2 USING v2, t2 WHERE v2.a = t2.a; +ERROR 42000: DELETE command denied to user 'privtest'@'localhost' for table 'v2' +SELECT * FROM v2; +a b +EXPLAIN SELECT * FROM v2; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +ANALYZE SELECT * FROM v2; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +SELECT * FROM v2 WHERE a = 10; +a b +EXPLAIN SELECT * FROM v2 WHERE a = 10; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +ANALYZE SELECT * FROM v2 WHERE a = 10; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +SELECT * FROM v2 WHERE a IN ( SELECT a FROM t2 ); +a b +EXPLAIN SELECT * FROM v2 WHERE a IN ( SELECT a FROM t2 ); +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +ANALYZE SELECT * FROM v2 WHERE a IN ( SELECT a FROM t2 ); +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table + +#======================================================================== +# Test: Grant SHOW VIEW, SELECT on the outer view +#======================================================================== + +connection default; +REVOKE ALL PRIVILEGES ON privtest_db.v2 FROM 'privtest'@'localhost'; +GRANT SELECT, SHOW VIEW ON privtest_db.v2 TO 'privtest'@'localhost'; +connection con1; +#------------------------------------------------------------------------ +# I/R/U/D/S on the inner view +# Expectation: Can run SELECT, EXPLAIN SELECT, ANALYZE SELECT +#------------------------------------------------------------------------ +INSERT INTO v2 (a) VALUES (10); +ERROR 42000: INSERT command denied to user 'privtest'@'localhost' for table 'v2' +EXPLAIN INSERT INTO v2 (a) VALUES (10); +ERROR 42000: INSERT command denied to user 'privtest'@'localhost' for table 'v2' +ANALYZE INSERT INTO v2 (a) VALUES (10); +ERROR 42000: INSERT command denied to user 'privtest'@'localhost' for table 'v2' +INSERT INTO v2 SELECT * FROM t2; +ERROR 42000: INSERT command denied to user 'privtest'@'localhost' for table 'v2' +EXPLAIN INSERT INTO v2 SELECT * FROM t2; +ERROR 42000: INSERT command denied to user 'privtest'@'localhost' for table 'v2' +ANALYZE INSERT INTO v2 SELECT * FROM t2; +ERROR 42000: INSERT command denied to user 'privtest'@'localhost' for table 'v2' +REPLACE INTO v2 (a) VALUES (10); +ERROR 42000: INSERT, DELETE command denied to user 'privtest'@'localhost' for table 'v2' +EXPLAIN REPLACE INTO v2 (a) VALUES (10); +ERROR 42000: INSERT, DELETE command denied to user 'privtest'@'localhost' for table 'v2' +ANALYZE REPLACE INTO v2 (a) VALUES (10); +ERROR 42000: INSERT, DELETE command denied to user 'privtest'@'localhost' for table 'v2' +REPLACE INTO v2 SELECT * FROM t2; +ERROR 42000: INSERT, DELETE command denied to user 'privtest'@'localhost' for table 'v2' +EXPLAIN REPLACE INTO v2 SELECT * FROM t2; +ERROR 42000: INSERT, DELETE command denied to user 'privtest'@'localhost' for table 'v2' +ANALYZE REPLACE INTO v2 SELECT * FROM t2; +ERROR 42000: INSERT, DELETE command denied to user 'privtest'@'localhost' for table 'v2' +UPDATE v2 SET a = 10; +ERROR 42000: UPDATE command denied to user 'privtest'@'localhost' for table 'v2' +EXPLAIN UPDATE v2 SET a = 10; +ERROR 42000: UPDATE command denied to user 'privtest'@'localhost' for table 'v2' +ANALYZE UPDATE v2 SET a = 10; +ERROR 42000: UPDATE command denied to user 'privtest'@'localhost' for table 'v2' +UPDATE v2 SET a = a + 1; +ERROR 42000: UPDATE command denied to user 'privtest'@'localhost' for table 'v2' +EXPLAIN UPDATE v2 SET a = a + 1; +ERROR 42000: UPDATE command denied to user 'privtest'@'localhost' for table 'v2' +ANALYZE UPDATE v2 SET a = a + 1; +ERROR 42000: UPDATE command denied to user 'privtest'@'localhost' for table 'v2' +UPDATE v2, t2 SET v2.a = v2.a + 1 WHERE v2.a = t2.a; +ERROR 42000: UPDATE command denied to user 'privtest'@'localhost' for table 'v2' +EXPLAIN UPDATE v2, t2 SET v2.a = v2.a + 1 WHERE v2.a = t2.a; +ERROR 42000: UPDATE command denied to user 'privtest'@'localhost' for table 'v2' +ANALYZE UPDATE v2, t2 SET v2.a = v2.a + 1 WHERE v2.a = t2.a; +ERROR 42000: UPDATE command denied to user 'privtest'@'localhost' for table 'v2' +DELETE FROM v2; +ERROR 42000: DELETE command denied to user 'privtest'@'localhost' for table 'v2' +EXPLAIN DELETE FROM v2; +ERROR 42000: DELETE command denied to user 'privtest'@'localhost' for table 'v2' +ANALYZE DELETE FROM v2; +ERROR 42000: DELETE command denied to user 'privtest'@'localhost' for table 'v2' +DELETE FROM v2 WHERE a = 10; +ERROR 42000: DELETE command denied to user 'privtest'@'localhost' for table 'v2' +EXPLAIN DELETE FROM v2 WHERE a = 10; +ERROR 42000: DELETE command denied to user 'privtest'@'localhost' for table 'v2' +ANALYZE DELETE FROM v2 WHERE a = 10; +ERROR 42000: DELETE command denied to user 'privtest'@'localhost' for table 'v2' +DELETE FROM v2 USING v2, t2; +ERROR 42000: DELETE command denied to user 'privtest'@'localhost' for table 'v2' +EXPLAIN DELETE FROM v2 USING v2, t2; +ERROR 42000: DELETE command denied to user 'privtest'@'localhost' for table 'v2' +ANALYZE DELETE FROM v2 USING v2, t2; +ERROR 42000: DELETE command denied to user 'privtest'@'localhost' for table 'v2' +DELETE FROM v2 USING v2, t2 WHERE v2.a = t2.a; +ERROR 42000: DELETE command denied to user 'privtest'@'localhost' for table 'v2' +EXPLAIN DELETE FROM v2 USING v2, t2 WHERE v2.a = t2.a; +ERROR 42000: DELETE command denied to user 'privtest'@'localhost' for table 'v2' +ANALYZE DELETE FROM v2 USING v2, t2 WHERE v2.a = t2.a; +ERROR 42000: DELETE command denied to user 'privtest'@'localhost' for table 'v2' +SELECT * FROM v2; +a b +EXPLAIN SELECT * FROM v2; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 39 Using where +ANALYZE SELECT * FROM v2; +id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 39 39.00 100.00 0.00 Using where +SELECT * FROM v2 WHERE a = 10; +a b +EXPLAIN SELECT * FROM v2 WHERE a = 10; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 39 Using where +ANALYZE SELECT * FROM v2 WHERE a = 10; +id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 39 39.00 100.00 0.00 Using where +SELECT * FROM v2 WHERE a IN ( SELECT a FROM t2 ); +a b +EXPLAIN SELECT * FROM v2 WHERE a IN ( SELECT a FROM t2 ); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 3 +1 PRIMARY t1 ALL NULL NULL NULL NULL 39 Using where; Using join buffer (flat, BNL join) +2 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 Using where +ANALYZE SELECT * FROM v2 WHERE a IN ( SELECT a FROM t2 ); +id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra +1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 3 3.00 100.00 100.00 +1 PRIMARY t1 ALL NULL NULL NULL NULL 39 39.00 100.00 0.00 Using where; Using join buffer (flat, BNL join) +2 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 3.00 100.00 100.00 Using where + +#======================================================================== +# Test: Grant INSERT on the outer view +#======================================================================== + + +connection default; +REVOKE ALL PRIVILEGES ON privtest_db.v2 FROM 'privtest'@'localhost'; +GRANT INSERT ON privtest_db.v2 TO 'privtest'@'localhost'; +connection con1; +#------------------------------------------------------------------------ +# I/R/U/D/S on the outer view +# Expectation: Can run INSERT, but not EXPLAIN INSERT or ANALYZE INSERT +# because the query plan cannot be shown +# (it could have revealed the structure of the view) +#------------------------------------------------------------------------ +INSERT INTO v2 (a) VALUES (10); +EXPLAIN INSERT INTO v2 (a) VALUES (10); +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +ANALYZE INSERT INTO v2 (a) VALUES (10); +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +INSERT INTO v2 SELECT * FROM t2; +EXPLAIN INSERT INTO v2 SELECT * FROM t2; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +ANALYZE INSERT INTO v2 SELECT * FROM t2; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +REPLACE INTO v2 (a) VALUES (10); +ERROR 42000: DELETE command denied to user 'privtest'@'localhost' for table 'v2' +EXPLAIN REPLACE INTO v2 (a) VALUES (10); +ERROR 42000: DELETE command denied to user 'privtest'@'localhost' for table 'v2' +ANALYZE REPLACE INTO v2 (a) VALUES (10); +ERROR 42000: DELETE command denied to user 'privtest'@'localhost' for table 'v2' +REPLACE INTO v2 SELECT * FROM t2; +ERROR 42000: DELETE command denied to user 'privtest'@'localhost' for table 'v2' +EXPLAIN REPLACE INTO v2 SELECT * FROM t2; +ERROR 42000: DELETE command denied to user 'privtest'@'localhost' for table 'v2' +ANALYZE REPLACE INTO v2 SELECT * FROM t2; +ERROR 42000: DELETE command denied to user 'privtest'@'localhost' for table 'v2' +UPDATE v2 SET a = 10; +ERROR 42000: UPDATE command denied to user 'privtest'@'localhost' for table 'v2' +EXPLAIN UPDATE v2 SET a = 10; +ERROR 42000: UPDATE command denied to user 'privtest'@'localhost' for table 'v2' +ANALYZE UPDATE v2 SET a = 10; +ERROR 42000: UPDATE command denied to user 'privtest'@'localhost' for table 'v2' +UPDATE v2 SET a = a + 1; +ERROR 42000: UPDATE command denied to user 'privtest'@'localhost' for table 'v2' +EXPLAIN UPDATE v2 SET a = a + 1; +ERROR 42000: UPDATE command denied to user 'privtest'@'localhost' for table 'v2' +ANALYZE UPDATE v2 SET a = a + 1; +ERROR 42000: UPDATE command denied to user 'privtest'@'localhost' for table 'v2' +UPDATE v2, t2 SET v2.a = v2.a + 1 WHERE v2.a = t2.a; +ERROR 42000: SELECT command denied to user 'privtest'@'localhost' for table 'v2' +EXPLAIN UPDATE v2, t2 SET v2.a = v2.a + 1 WHERE v2.a = t2.a; +ERROR 42000: SELECT command denied to user 'privtest'@'localhost' for table 'v2' +ANALYZE UPDATE v2, t2 SET v2.a = v2.a + 1 WHERE v2.a = t2.a; +ERROR 42000: SELECT command denied to user 'privtest'@'localhost' for table 'v2' +DELETE FROM v2; +ERROR 42000: DELETE command denied to user 'privtest'@'localhost' for table 'v2' +EXPLAIN DELETE FROM v2; +ERROR 42000: DELETE command denied to user 'privtest'@'localhost' for table 'v2' +ANALYZE DELETE FROM v2; +ERROR 42000: DELETE command denied to user 'privtest'@'localhost' for table 'v2' +DELETE FROM v2 WHERE a = 10; +ERROR 42000: DELETE command denied to user 'privtest'@'localhost' for table 'v2' +EXPLAIN DELETE FROM v2 WHERE a = 10; +ERROR 42000: DELETE command denied to user 'privtest'@'localhost' for table 'v2' +ANALYZE DELETE FROM v2 WHERE a = 10; +ERROR 42000: DELETE command denied to user 'privtest'@'localhost' for table 'v2' +DELETE FROM v2 USING v2, t2; +ERROR 42000: SELECT command denied to user 'privtest'@'localhost' for table 'v2' +EXPLAIN DELETE FROM v2 USING v2, t2; +ERROR 42000: SELECT command denied to user 'privtest'@'localhost' for table 'v2' +ANALYZE DELETE FROM v2 USING v2, t2; +ERROR 42000: SELECT command denied to user 'privtest'@'localhost' for table 'v2' +DELETE FROM v2 USING v2, t2 WHERE v2.a = t2.a; +ERROR 42000: SELECT command denied to user 'privtest'@'localhost' for table 'v2' +EXPLAIN DELETE FROM v2 USING v2, t2 WHERE v2.a = t2.a; +ERROR 42000: SELECT command denied to user 'privtest'@'localhost' for table 'v2' +ANALYZE DELETE FROM v2 USING v2, t2 WHERE v2.a = t2.a; +ERROR 42000: SELECT command denied to user 'privtest'@'localhost' for table 'v2' +SELECT * FROM v2; +ERROR 42000: SELECT command denied to user 'privtest'@'localhost' for table 'v2' +EXPLAIN SELECT * FROM v2; +ERROR 42000: SELECT command denied to user 'privtest'@'localhost' for table 'v2' +ANALYZE SELECT * FROM v2; +ERROR 42000: SELECT command denied to user 'privtest'@'localhost' for table 'v2' +SELECT * FROM v2 WHERE a = 10; +ERROR 42000: SELECT command denied to user 'privtest'@'localhost' for table 'v2' +EXPLAIN SELECT * FROM v2 WHERE a = 10; +ERROR 42000: SELECT command denied to user 'privtest'@'localhost' for table 'v2' +ANALYZE SELECT * FROM v2 WHERE a = 10; +ERROR 42000: SELECT command denied to user 'privtest'@'localhost' for table 'v2' +SELECT * FROM v2 WHERE a IN ( SELECT a FROM t2 ); +ERROR 42000: SELECT command denied to user 'privtest'@'localhost' for table 'v2' +EXPLAIN SELECT * FROM v2 WHERE a IN ( SELECT a FROM t2 ); +ERROR 42000: SELECT command denied to user 'privtest'@'localhost' for table 'v2' +ANALYZE SELECT * FROM v2 WHERE a IN ( SELECT a FROM t2 ); +ERROR 42000: SELECT command denied to user 'privtest'@'localhost' for table 'v2' + +#======================================================================== +# Test: Grant UPDATE on the outer view +#======================================================================== + + +connection default; +REVOKE ALL PRIVILEGES ON privtest_db.v2 FROM 'privtest'@'localhost'; +GRANT UPDATE ON privtest_db.v2 TO 'privtest'@'localhost'; +connection con1; +#------------------------------------------------------------------------ +# I/R/U/D/S on the outer view +# Expectation: Can run UPDATE which does not read any columns, +# but not EXPLAIN UPDATE or ANALYZE UPDATE +# because the query plan cannot be shown +# (it could have revealed the structure of the view) +#------------------------------------------------------------------------ +INSERT INTO v2 (a) VALUES (10); +ERROR 42000: INSERT command denied to user 'privtest'@'localhost' for table 'v2' +EXPLAIN INSERT INTO v2 (a) VALUES (10); +ERROR 42000: INSERT command denied to user 'privtest'@'localhost' for table 'v2' +ANALYZE INSERT INTO v2 (a) VALUES (10); +ERROR 42000: INSERT command denied to user 'privtest'@'localhost' for table 'v2' +INSERT INTO v2 SELECT * FROM t2; +ERROR 42000: INSERT command denied to user 'privtest'@'localhost' for table 'v2' +EXPLAIN INSERT INTO v2 SELECT * FROM t2; +ERROR 42000: INSERT command denied to user 'privtest'@'localhost' for table 'v2' +ANALYZE INSERT INTO v2 SELECT * FROM t2; +ERROR 42000: INSERT command denied to user 'privtest'@'localhost' for table 'v2' +REPLACE INTO v2 (a) VALUES (10); +ERROR 42000: INSERT, DELETE command denied to user 'privtest'@'localhost' for table 'v2' +EXPLAIN REPLACE INTO v2 (a) VALUES (10); +ERROR 42000: INSERT, DELETE command denied to user 'privtest'@'localhost' for table 'v2' +ANALYZE REPLACE INTO v2 (a) VALUES (10); +ERROR 42000: INSERT, DELETE command denied to user 'privtest'@'localhost' for table 'v2' +REPLACE INTO v2 SELECT * FROM t2; +ERROR 42000: INSERT, DELETE command denied to user 'privtest'@'localhost' for table 'v2' +EXPLAIN REPLACE INTO v2 SELECT * FROM t2; +ERROR 42000: INSERT, DELETE command denied to user 'privtest'@'localhost' for table 'v2' +ANALYZE REPLACE INTO v2 SELECT * FROM t2; +ERROR 42000: INSERT, DELETE command denied to user 'privtest'@'localhost' for table 'v2' +UPDATE v2 SET a = 10; +EXPLAIN UPDATE v2 SET a = 10; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +ANALYZE UPDATE v2 SET a = 10; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +UPDATE v2 SET a = a + 1; +EXPLAIN UPDATE v2 SET a = a + 1; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +ANALYZE UPDATE v2 SET a = a + 1; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +UPDATE v2, t2 SET v2.a = v2.a + 1 WHERE v2.a = t2.a; +ERROR 42000: SELECT command denied to user 'privtest'@'localhost' for column 'a' in table 'v2' +EXPLAIN UPDATE v2, t2 SET v2.a = v2.a + 1 WHERE v2.a = t2.a; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +ANALYZE UPDATE v2, t2 SET v2.a = v2.a + 1 WHERE v2.a = t2.a; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +DELETE FROM v2; +ERROR 42000: DELETE command denied to user 'privtest'@'localhost' for table 'v2' +EXPLAIN DELETE FROM v2; +ERROR 42000: DELETE command denied to user 'privtest'@'localhost' for table 'v2' +ANALYZE DELETE FROM v2; +ERROR 42000: DELETE command denied to user 'privtest'@'localhost' for table 'v2' +DELETE FROM v2 WHERE a = 10; +ERROR 42000: DELETE command denied to user 'privtest'@'localhost' for table 'v2' +EXPLAIN DELETE FROM v2 WHERE a = 10; +ERROR 42000: DELETE command denied to user 'privtest'@'localhost' for table 'v2' +ANALYZE DELETE FROM v2 WHERE a = 10; +ERROR 42000: DELETE command denied to user 'privtest'@'localhost' for table 'v2' +DELETE FROM v2 USING v2, t2; +ERROR 42000: SELECT command denied to user 'privtest'@'localhost' for table 'v2' +EXPLAIN DELETE FROM v2 USING v2, t2; +ERROR 42000: SELECT command denied to user 'privtest'@'localhost' for table 'v2' +ANALYZE DELETE FROM v2 USING v2, t2; +ERROR 42000: SELECT command denied to user 'privtest'@'localhost' for table 'v2' +DELETE FROM v2 USING v2, t2 WHERE v2.a = t2.a; +ERROR 42000: SELECT command denied to user 'privtest'@'localhost' for table 'v2' +EXPLAIN DELETE FROM v2 USING v2, t2 WHERE v2.a = t2.a; +ERROR 42000: SELECT command denied to user 'privtest'@'localhost' for table 'v2' +ANALYZE DELETE FROM v2 USING v2, t2 WHERE v2.a = t2.a; +ERROR 42000: SELECT command denied to user 'privtest'@'localhost' for table 'v2' +SELECT * FROM v2; +ERROR 42000: SELECT command denied to user 'privtest'@'localhost' for table 'v2' +EXPLAIN SELECT * FROM v2; +ERROR 42000: SELECT command denied to user 'privtest'@'localhost' for table 'v2' +ANALYZE SELECT * FROM v2; +ERROR 42000: SELECT command denied to user 'privtest'@'localhost' for table 'v2' +SELECT * FROM v2 WHERE a = 10; +ERROR 42000: SELECT command denied to user 'privtest'@'localhost' for table 'v2' +EXPLAIN SELECT * FROM v2 WHERE a = 10; +ERROR 42000: SELECT command denied to user 'privtest'@'localhost' for table 'v2' +ANALYZE SELECT * FROM v2 WHERE a = 10; +ERROR 42000: SELECT command denied to user 'privtest'@'localhost' for table 'v2' +SELECT * FROM v2 WHERE a IN ( SELECT a FROM t2 ); +ERROR 42000: SELECT command denied to user 'privtest'@'localhost' for table 'v2' +EXPLAIN SELECT * FROM v2 WHERE a IN ( SELECT a FROM t2 ); +ERROR 42000: SELECT command denied to user 'privtest'@'localhost' for table 'v2' +ANALYZE SELECT * FROM v2 WHERE a IN ( SELECT a FROM t2 ); +ERROR 42000: SELECT command denied to user 'privtest'@'localhost' for table 'v2' + +#======================================================================== +# Test: Grant UPDATE, SHOW VIEW on the outer view +#======================================================================== + +connection default; +REVOKE ALL PRIVILEGES ON privtest_db.v2 FROM 'privtest'@'localhost'; +GRANT SHOW VIEW, UPDATE ON privtest_db.v2 TO 'privtest'@'localhost'; +connection con1; +#------------------------------------------------------------------------ +# I/R/U/D/S on the inner view +# Expectation: Can run UPDATE, EXPLAIN UPDATE, ANALYZE UPDATE +# which do not read any columns +#------------------------------------------------------------------------ +INSERT INTO v2 (a) VALUES (10); +ERROR 42000: INSERT command denied to user 'privtest'@'localhost' for table 'v2' +EXPLAIN INSERT INTO v2 (a) VALUES (10); +ERROR 42000: INSERT command denied to user 'privtest'@'localhost' for table 'v2' +ANALYZE INSERT INTO v2 (a) VALUES (10); +ERROR 42000: INSERT command denied to user 'privtest'@'localhost' for table 'v2' +INSERT INTO v2 SELECT * FROM t2; +ERROR 42000: INSERT command denied to user 'privtest'@'localhost' for table 'v2' +EXPLAIN INSERT INTO v2 SELECT * FROM t2; +ERROR 42000: INSERT command denied to user 'privtest'@'localhost' for table 'v2' +ANALYZE INSERT INTO v2 SELECT * FROM t2; +ERROR 42000: INSERT command denied to user 'privtest'@'localhost' for table 'v2' +REPLACE INTO v2 (a) VALUES (10); +ERROR 42000: INSERT, DELETE command denied to user 'privtest'@'localhost' for table 'v2' +EXPLAIN REPLACE INTO v2 (a) VALUES (10); +ERROR 42000: INSERT, DELETE command denied to user 'privtest'@'localhost' for table 'v2' +ANALYZE REPLACE INTO v2 (a) VALUES (10); +ERROR 42000: INSERT, DELETE command denied to user 'privtest'@'localhost' for table 'v2' +REPLACE INTO v2 SELECT * FROM t2; +ERROR 42000: INSERT, DELETE command denied to user 'privtest'@'localhost' for table 'v2' +EXPLAIN REPLACE INTO v2 SELECT * FROM t2; +ERROR 42000: INSERT, DELETE command denied to user 'privtest'@'localhost' for table 'v2' +ANALYZE REPLACE INTO v2 SELECT * FROM t2; +ERROR 42000: INSERT, DELETE command denied to user 'privtest'@'localhost' for table 'v2' +UPDATE v2 SET a = 10; +EXPLAIN UPDATE v2 SET a = 10; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 43 Using where +ANALYZE UPDATE v2 SET a = 10; +id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 43 43.00 100.00 6.98 Using where +UPDATE v2 SET a = a + 1; +EXPLAIN UPDATE v2 SET a = a + 1; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 43 Using where +ANALYZE UPDATE v2 SET a = a + 1; +id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 43 43.00 100.00 6.98 Using where +UPDATE v2, t2 SET v2.a = v2.a + 1 WHERE v2.a = t2.a; +ERROR 42000: SELECT command denied to user 'privtest'@'localhost' for column 'a' in table 'v2' +EXPLAIN UPDATE v2, t2 SET v2.a = v2.a + 1 WHERE v2.a = t2.a; +ERROR 42000: SELECT command denied to user 'privtest'@'localhost' for column 'a' in table 'v2' +ANALYZE UPDATE v2, t2 SET v2.a = v2.a + 1 WHERE v2.a = t2.a; +ERROR 42000: SELECT command denied to user 'privtest'@'localhost' for column 'a' in table 'v2' +DELETE FROM v2; +ERROR 42000: DELETE command denied to user 'privtest'@'localhost' for table 'v2' +EXPLAIN DELETE FROM v2; +ERROR 42000: DELETE command denied to user 'privtest'@'localhost' for table 'v2' +ANALYZE DELETE FROM v2; +ERROR 42000: DELETE command denied to user 'privtest'@'localhost' for table 'v2' +DELETE FROM v2 WHERE a = 10; +ERROR 42000: DELETE command denied to user 'privtest'@'localhost' for table 'v2' +EXPLAIN DELETE FROM v2 WHERE a = 10; +ERROR 42000: DELETE command denied to user 'privtest'@'localhost' for table 'v2' +ANALYZE DELETE FROM v2 WHERE a = 10; +ERROR 42000: DELETE command denied to user 'privtest'@'localhost' for table 'v2' +DELETE FROM v2 USING v2, t2; +ERROR 42000: SELECT command denied to user 'privtest'@'localhost' for table 'v2' +EXPLAIN DELETE FROM v2 USING v2, t2; +ERROR 42000: SELECT command denied to user 'privtest'@'localhost' for table 'v2' +ANALYZE DELETE FROM v2 USING v2, t2; +ERROR 42000: SELECT command denied to user 'privtest'@'localhost' for table 'v2' +DELETE FROM v2 USING v2, t2 WHERE v2.a = t2.a; +ERROR 42000: SELECT command denied to user 'privtest'@'localhost' for table 'v2' +EXPLAIN DELETE FROM v2 USING v2, t2 WHERE v2.a = t2.a; +ERROR 42000: SELECT command denied to user 'privtest'@'localhost' for table 'v2' +ANALYZE DELETE FROM v2 USING v2, t2 WHERE v2.a = t2.a; +ERROR 42000: SELECT command denied to user 'privtest'@'localhost' for table 'v2' +SELECT * FROM v2; +ERROR 42000: SELECT command denied to user 'privtest'@'localhost' for table 'v2' +EXPLAIN SELECT * FROM v2; +ERROR 42000: SELECT command denied to user 'privtest'@'localhost' for table 'v2' +ANALYZE SELECT * FROM v2; +ERROR 42000: SELECT command denied to user 'privtest'@'localhost' for table 'v2' +SELECT * FROM v2 WHERE a = 10; +ERROR 42000: SELECT command denied to user 'privtest'@'localhost' for table 'v2' +EXPLAIN SELECT * FROM v2 WHERE a = 10; +ERROR 42000: SELECT command denied to user 'privtest'@'localhost' for table 'v2' +ANALYZE SELECT * FROM v2 WHERE a = 10; +ERROR 42000: SELECT command denied to user 'privtest'@'localhost' for table 'v2' +SELECT * FROM v2 WHERE a IN ( SELECT a FROM t2 ); +ERROR 42000: SELECT command denied to user 'privtest'@'localhost' for table 'v2' +EXPLAIN SELECT * FROM v2 WHERE a IN ( SELECT a FROM t2 ); +ERROR 42000: SELECT command denied to user 'privtest'@'localhost' for table 'v2' +ANALYZE SELECT * FROM v2 WHERE a IN ( SELECT a FROM t2 ); +ERROR 42000: SELECT command denied to user 'privtest'@'localhost' for table 'v2' + +#======================================================================== +# Test: Grant DELETE on the outer view +#======================================================================== + +connection default; +REVOKE ALL PRIVILEGES ON privtest_db.v2 FROM 'privtest'@'localhost'; +GRANT DELETE ON privtest_db.v2 TO 'privtest'@'localhost'; +connection con1; +#------------------------------------------------------------------------ +# I/R/U/D/S on the outer view +# Expectation: Can run DELETE which does not read any columns, +# but not EXPLAIN DELETE or ANALYZE DELETE +# because the query plan cannot be shown +# (it could have revealed the structure of the view) +#------------------------------------------------------------------------ +INSERT INTO v2 (a) VALUES (10); +ERROR 42000: INSERT command denied to user 'privtest'@'localhost' for table 'v2' +EXPLAIN INSERT INTO v2 (a) VALUES (10); +ERROR 42000: INSERT command denied to user 'privtest'@'localhost' for table 'v2' +ANALYZE INSERT INTO v2 (a) VALUES (10); +ERROR 42000: INSERT command denied to user 'privtest'@'localhost' for table 'v2' +INSERT INTO v2 SELECT * FROM t2; +ERROR 42000: INSERT command denied to user 'privtest'@'localhost' for table 'v2' +EXPLAIN INSERT INTO v2 SELECT * FROM t2; +ERROR 42000: INSERT command denied to user 'privtest'@'localhost' for table 'v2' +ANALYZE INSERT INTO v2 SELECT * FROM t2; +ERROR 42000: INSERT command denied to user 'privtest'@'localhost' for table 'v2' +REPLACE INTO v2 (a) VALUES (10); +ERROR 42000: INSERT command denied to user 'privtest'@'localhost' for table 'v2' +EXPLAIN REPLACE INTO v2 (a) VALUES (10); +ERROR 42000: INSERT command denied to user 'privtest'@'localhost' for table 'v2' +ANALYZE REPLACE INTO v2 (a) VALUES (10); +ERROR 42000: INSERT command denied to user 'privtest'@'localhost' for table 'v2' +REPLACE INTO v2 SELECT * FROM t2; +ERROR 42000: INSERT command denied to user 'privtest'@'localhost' for table 'v2' +EXPLAIN REPLACE INTO v2 SELECT * FROM t2; +ERROR 42000: INSERT command denied to user 'privtest'@'localhost' for table 'v2' +ANALYZE REPLACE INTO v2 SELECT * FROM t2; +ERROR 42000: INSERT command denied to user 'privtest'@'localhost' for table 'v2' +UPDATE v2 SET a = 10; +ERROR 42000: UPDATE command denied to user 'privtest'@'localhost' for table 'v2' +EXPLAIN UPDATE v2 SET a = 10; +ERROR 42000: UPDATE command denied to user 'privtest'@'localhost' for table 'v2' +ANALYZE UPDATE v2 SET a = 10; +ERROR 42000: UPDATE command denied to user 'privtest'@'localhost' for table 'v2' +UPDATE v2 SET a = a + 1; +ERROR 42000: UPDATE command denied to user 'privtest'@'localhost' for table 'v2' +EXPLAIN UPDATE v2 SET a = a + 1; +ERROR 42000: UPDATE command denied to user 'privtest'@'localhost' for table 'v2' +ANALYZE UPDATE v2 SET a = a + 1; +ERROR 42000: UPDATE command denied to user 'privtest'@'localhost' for table 'v2' +UPDATE v2, t2 SET v2.a = v2.a + 1 WHERE v2.a = t2.a; +ERROR 42000: SELECT command denied to user 'privtest'@'localhost' for table 'v2' +EXPLAIN UPDATE v2, t2 SET v2.a = v2.a + 1 WHERE v2.a = t2.a; +ERROR 42000: SELECT command denied to user 'privtest'@'localhost' for table 'v2' +ANALYZE UPDATE v2, t2 SET v2.a = v2.a + 1 WHERE v2.a = t2.a; +ERROR 42000: SELECT command denied to user 'privtest'@'localhost' for table 'v2' +DELETE FROM v2; +EXPLAIN DELETE FROM v2; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +ANALYZE DELETE FROM v2; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +DELETE FROM v2 WHERE a = 10; +ERROR 42000: SELECT command denied to user 'privtest'@'localhost' for column 'a' in table 'v2' +EXPLAIN DELETE FROM v2 WHERE a = 10; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +ANALYZE DELETE FROM v2 WHERE a = 10; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +DELETE FROM v2 USING v2, t2; +ERROR 42000: SELECT command denied to user 'privtest'@'localhost' for table 'v2' +EXPLAIN DELETE FROM v2 USING v2, t2; +ERROR 42000: SELECT command denied to user 'privtest'@'localhost' for table 'v2' +ANALYZE DELETE FROM v2 USING v2, t2; +ERROR 42000: SELECT command denied to user 'privtest'@'localhost' for table 'v2' +DELETE FROM v2 USING v2, t2 WHERE v2.a = t2.a; +ERROR 42000: SELECT command denied to user 'privtest'@'localhost' for table 'v2' +EXPLAIN DELETE FROM v2 USING v2, t2 WHERE v2.a = t2.a; +ERROR 42000: SELECT command denied to user 'privtest'@'localhost' for table 'v2' +ANALYZE DELETE FROM v2 USING v2, t2 WHERE v2.a = t2.a; +ERROR 42000: SELECT command denied to user 'privtest'@'localhost' for table 'v2' +SELECT * FROM v2; +ERROR 42000: SELECT command denied to user 'privtest'@'localhost' for table 'v2' +EXPLAIN SELECT * FROM v2; +ERROR 42000: SELECT command denied to user 'privtest'@'localhost' for table 'v2' +ANALYZE SELECT * FROM v2; +ERROR 42000: SELECT command denied to user 'privtest'@'localhost' for table 'v2' +SELECT * FROM v2 WHERE a = 10; +ERROR 42000: SELECT command denied to user 'privtest'@'localhost' for table 'v2' +EXPLAIN SELECT * FROM v2 WHERE a = 10; +ERROR 42000: SELECT command denied to user 'privtest'@'localhost' for table 'v2' +ANALYZE SELECT * FROM v2 WHERE a = 10; +ERROR 42000: SELECT command denied to user 'privtest'@'localhost' for table 'v2' +SELECT * FROM v2 WHERE a IN ( SELECT a FROM t2 ); +ERROR 42000: SELECT command denied to user 'privtest'@'localhost' for table 'v2' +EXPLAIN SELECT * FROM v2 WHERE a IN ( SELECT a FROM t2 ); +ERROR 42000: SELECT command denied to user 'privtest'@'localhost' for table 'v2' +ANALYZE SELECT * FROM v2 WHERE a IN ( SELECT a FROM t2 ); +ERROR 42000: SELECT command denied to user 'privtest'@'localhost' for table 'v2' + +#======================================================================== +# Test: Grant DELETE, SELECT on the outer view +#======================================================================== + +connection default; +REVOKE ALL PRIVILEGES ON privtest_db.v2 FROM 'privtest'@'localhost'; +GRANT SELECT, DELETE ON privtest_db.v2 TO 'privtest'@'localhost'; +connection con1; +#------------------------------------------------------------------------ +# I/R/U/D/S on the outer view +# Expectation: Can run DELETE and SELECT, but not EXPLAIN or ANALYZE +# for them because the query plan cannot be shown +# (it could have revealed the structure of the view) +#------------------------------------------------------------------------ +INSERT INTO v2 (a) VALUES (10); +ERROR 42000: INSERT command denied to user 'privtest'@'localhost' for table 'v2' +EXPLAIN INSERT INTO v2 (a) VALUES (10); +ERROR 42000: INSERT command denied to user 'privtest'@'localhost' for table 'v2' +ANALYZE INSERT INTO v2 (a) VALUES (10); +ERROR 42000: INSERT command denied to user 'privtest'@'localhost' for table 'v2' +INSERT INTO v2 SELECT * FROM t2; +ERROR 42000: INSERT command denied to user 'privtest'@'localhost' for table 'v2' +EXPLAIN INSERT INTO v2 SELECT * FROM t2; +ERROR 42000: INSERT command denied to user 'privtest'@'localhost' for table 'v2' +ANALYZE INSERT INTO v2 SELECT * FROM t2; +ERROR 42000: INSERT command denied to user 'privtest'@'localhost' for table 'v2' +REPLACE INTO v2 (a) VALUES (10); +ERROR 42000: INSERT command denied to user 'privtest'@'localhost' for table 'v2' +EXPLAIN REPLACE INTO v2 (a) VALUES (10); +ERROR 42000: INSERT command denied to user 'privtest'@'localhost' for table 'v2' +ANALYZE REPLACE INTO v2 (a) VALUES (10); +ERROR 42000: INSERT command denied to user 'privtest'@'localhost' for table 'v2' +REPLACE INTO v2 SELECT * FROM t2; +ERROR 42000: INSERT command denied to user 'privtest'@'localhost' for table 'v2' +EXPLAIN REPLACE INTO v2 SELECT * FROM t2; +ERROR 42000: INSERT command denied to user 'privtest'@'localhost' for table 'v2' +ANALYZE REPLACE INTO v2 SELECT * FROM t2; +ERROR 42000: INSERT command denied to user 'privtest'@'localhost' for table 'v2' +UPDATE v2 SET a = 10; +ERROR 42000: UPDATE command denied to user 'privtest'@'localhost' for table 'v2' +EXPLAIN UPDATE v2 SET a = 10; +ERROR 42000: UPDATE command denied to user 'privtest'@'localhost' for table 'v2' +ANALYZE UPDATE v2 SET a = 10; +ERROR 42000: UPDATE command denied to user 'privtest'@'localhost' for table 'v2' +UPDATE v2 SET a = a + 1; +ERROR 42000: UPDATE command denied to user 'privtest'@'localhost' for table 'v2' +EXPLAIN UPDATE v2 SET a = a + 1; +ERROR 42000: UPDATE command denied to user 'privtest'@'localhost' for table 'v2' +ANALYZE UPDATE v2 SET a = a + 1; +ERROR 42000: UPDATE command denied to user 'privtest'@'localhost' for table 'v2' +UPDATE v2, t2 SET v2.a = v2.a + 1 WHERE v2.a = t2.a; +ERROR 42000: UPDATE command denied to user 'privtest'@'localhost' for table 'v2' +EXPLAIN UPDATE v2, t2 SET v2.a = v2.a + 1 WHERE v2.a = t2.a; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +ANALYZE UPDATE v2, t2 SET v2.a = v2.a + 1 WHERE v2.a = t2.a; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +DELETE FROM v2; +EXPLAIN DELETE FROM v2; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +ANALYZE DELETE FROM v2; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +DELETE FROM v2 WHERE a = 10; +EXPLAIN DELETE FROM v2 WHERE a = 10; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +ANALYZE DELETE FROM v2 WHERE a = 10; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +DELETE FROM v2 USING v2, t2; +EXPLAIN DELETE FROM v2 USING v2, t2; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +ANALYZE DELETE FROM v2 USING v2, t2; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +DELETE FROM v2 USING v2, t2 WHERE v2.a = t2.a; +EXPLAIN DELETE FROM v2 USING v2, t2 WHERE v2.a = t2.a; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +ANALYZE DELETE FROM v2 USING v2, t2 WHERE v2.a = t2.a; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +SELECT * FROM v2; +a b +EXPLAIN SELECT * FROM v2; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +ANALYZE SELECT * FROM v2; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +SELECT * FROM v2 WHERE a = 10; +a b +EXPLAIN SELECT * FROM v2 WHERE a = 10; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +ANALYZE SELECT * FROM v2 WHERE a = 10; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +SELECT * FROM v2 WHERE a IN ( SELECT a FROM t2 ); +a b +EXPLAIN SELECT * FROM v2 WHERE a IN ( SELECT a FROM t2 ); +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +ANALYZE SELECT * FROM v2 WHERE a IN ( SELECT a FROM t2 ); +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table + +#======================================================================== +# Test: Grant SELECT, INSERT, UPDATE, DELETE, SHOW VIEW on the outer view +#======================================================================== + +connection default; +REVOKE ALL PRIVILEGES ON privtest_db.v2 FROM 'privtest'@'localhost'; +GRANT SELECT, INSERT, UPDATE, DELETE, SHOW VIEW ON privtest_db.v2 TO 'privtest'@'localhost'; +connection con1; +#------------------------------------------------------------------------ +# I/R/U/D/S on the outer view +# Expectation: Can run everything +#------------------------------------------------------------------------ +INSERT INTO v2 (a) VALUES (10); +EXPLAIN INSERT INTO v2 (a) VALUES (10); +id select_type table type possible_keys key key_len ref rows Extra +1 INSERT t1 ALL NULL NULL NULL NULL NULL NULL +ANALYZE INSERT INTO v2 (a) VALUES (10); +id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra +1 INSERT t1 ALL NULL NULL NULL NULL NULL NULL 100.00 100.00 NULL +INSERT INTO v2 SELECT * FROM t2; +EXPLAIN INSERT INTO v2 SELECT * FROM t2; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t2 ALL NULL NULL NULL NULL 3 +ANALYZE INSERT INTO v2 SELECT * FROM t2; +id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra +1 SIMPLE t2 ALL NULL NULL NULL NULL 3 3.00 100.00 100.00 +REPLACE INTO v2 (a) VALUES (10); +EXPLAIN REPLACE INTO v2 (a) VALUES (10); +id select_type table type possible_keys key key_len ref rows Extra +1 INSERT t1 ALL NULL NULL NULL NULL NULL NULL +ANALYZE REPLACE INTO v2 (a) VALUES (10); +id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra +1 INSERT t1 ALL NULL NULL NULL NULL NULL NULL 100.00 100.00 NULL +REPLACE INTO v2 SELECT * FROM t2; +EXPLAIN REPLACE INTO v2 SELECT * FROM t2; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t2 ALL NULL NULL NULL NULL 3 +ANALYZE REPLACE INTO v2 SELECT * FROM t2; +id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra +1 SIMPLE t2 ALL NULL NULL NULL NULL 3 3.00 100.00 100.00 +UPDATE v2 SET a = 10; +EXPLAIN UPDATE v2 SET a = 10; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 56 Using where +ANALYZE UPDATE v2 SET a = 10; +id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 56 56.00 100.00 21.43 Using where +UPDATE v2 SET a = a + 1; +EXPLAIN UPDATE v2 SET a = a + 1; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 56 Using where +ANALYZE UPDATE v2 SET a = a + 1; +id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 56 56.00 100.00 21.43 Using where +UPDATE v2, t2 SET v2.a = v2.a + 1 WHERE v2.a = t2.a; +EXPLAIN UPDATE v2, t2 SET v2.a = v2.a + 1 WHERE v2.a = t2.a; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t2 ALL NULL NULL NULL NULL 3 Using where +1 SIMPLE t1 ALL NULL NULL NULL NULL 56 Using where +ANALYZE UPDATE v2, t2 SET v2.a = v2.a + 1 WHERE v2.a = t2.a; +id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra +1 SIMPLE t2 ALL NULL NULL NULL NULL 3 3.00 100.00 100.00 Using where +1 SIMPLE t1 ALL NULL NULL NULL NULL 56 56.00 100.00 0.00 Using where +DELETE FROM v2; +EXPLAIN DELETE FROM v2; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 44 Using where +ANALYZE DELETE FROM v2; +id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 44 44.00 100.00 0.00 Using where +DELETE FROM v2 WHERE a = 10; +EXPLAIN DELETE FROM v2 WHERE a = 10; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 44 Using where +ANALYZE DELETE FROM v2 WHERE a = 10; +id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 44 44.00 100.00 0.00 Using where +DELETE FROM v2 USING v2, t2; +ANALYZE DELETE FROM v2 USING v2, t2; +id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra +1 SIMPLE t2 ALL NULL NULL NULL NULL 3 3.00 100.00 100.00 +1 SIMPLE t1 ALL NULL NULL NULL NULL 44 44.00 100.00 0.00 Using where +DELETE FROM v2 USING v2, t2 WHERE v2.a = t2.a; +ANALYZE DELETE FROM v2 USING v2, t2 WHERE v2.a = t2.a; +id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra +1 SIMPLE t2 ALL NULL NULL NULL NULL 3 3.00 100.00 100.00 Using where +1 SIMPLE t1 ALL NULL NULL NULL NULL 44 44.00 100.00 0.00 Using where +SELECT * FROM v2; +a b +EXPLAIN SELECT * FROM v2; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 44 Using where +ANALYZE SELECT * FROM v2; +id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 44 44.00 100.00 0.00 Using where +SELECT * FROM v2 WHERE a = 10; +a b +EXPLAIN SELECT * FROM v2 WHERE a = 10; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 44 Using where +ANALYZE SELECT * FROM v2 WHERE a = 10; +id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 44 44.00 100.00 0.00 Using where +SELECT * FROM v2 WHERE a IN ( SELECT a FROM t2 ); +a b +EXPLAIN SELECT * FROM v2 WHERE a IN ( SELECT a FROM t2 ); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 3 +1 PRIMARY t1 ALL NULL NULL NULL NULL 44 Using where; Using join buffer (flat, BNL join) +2 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 Using where +ANALYZE SELECT * FROM v2 WHERE a IN ( SELECT a FROM t2 ); +id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra +1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 3 3.00 100.00 100.00 +1 PRIMARY t1 ALL NULL NULL NULL NULL 44 44.00 100.00 0.00 Using where; Using join buffer (flat, BNL join) +2 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 3.00 100.00 100.00 Using where +disconnect con1; +connection default; +DROP USER 'privtest'@localhost; +USE test; +DROP DATABASE privtest_db; +set GLOBAL sql_mode=default; |