EVOLUTION-MANAGER

Edit File: analyze_stmt_privileges2.result

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;