1############################################################################### 2# The test ensures that permission checks are applied correctly to 3# ANALYZE INSERT/REPLACE/UPDATE/DELETE/SELECT (I/R/U/D/S further in the test) 4# when it's executed on a table or on a view. 5# ANALYZE <query> should require both permissions needed to execute the <query>, 6# and permissions needed to acquire query plan (to execute EXPLAIN <query>). 7# Thus, additionally execution of the query and EXPLAIN is checked. 8# See MDEV-406, MDEV-6382 9# 10# Disclaimer: the goal of this test is to check permissions for ANALYZE against 11# permissions for the query and EXPLAIN. 12# The expected result for queries and EXPLAINs is mostly empirical. 13# In many cases the current behavior is obscure and questionable, 14# but unless it is obviously wrong, the expected result is adjusted 15# to match it. 16# In cases when the behavior is really wrong, the adjustments 17# come with comments which point at the issues in the bug tracker. 18# Search for 'MDEV' to find all of them. 19################################################################################ 20 21--source include/not_embedded.inc 22--source include/default_optimizer_switch.inc 23 24# Save the initial number of concurrent sessions 25--source include/count_sessions.inc 26 27set GLOBAL sql_mode=""; 28set LOCAL sql_mode=""; 29 30CREATE DATABASE privtest_db; 31 32# t1 is the main base table on which we'll perform DML 33# v1 is an "inner" view which selects from the base table 34# v2 is an "outer" view which selects from the inner view 35# t2 is an additional table for queries like INSERT .. SELECT 36# and multi-table UPDATEs/DELETEs 37 38CREATE TABLE privtest_db.t1 (a INT, b VARCHAR(3)) ENGINE=MyISAM; 39CREATE TABLE privtest_db.t2 (a INT, b VARCHAR(3)) ENGINE=MyISAM; 40CREATE VIEW privtest_db.v1 AS SELECT a, b FROM privtest_db.t1 WHERE b IS NOT NULL; 41CREATE VIEW privtest_db.v2 AS SELECT * FROM privtest_db.v1 WHERE a > 0; 42 43INSERT INTO privtest_db.t2 VALUES (1,'foo'), (2,'bar'), (3,'qux'); 44 45GRANT SELECT ON privtest_db.t2 TO 'privtest'@'localhost'; 46 47connect(con1,localhost,privtest,,privtest_db); 48--source include/default_optimizer_switch.inc 49 50--echo 51--echo ######################################################################### 52--echo # Underlying table permission tests 53--echo # (we modify permissions on the base table, keeping ALL on views) 54--echo ######################################################################### 55--echo 56 57connection default; 58GRANT ALL ON privtest_db.v1 TO 'privtest'@'localhost'; 59GRANT ALL ON privtest_db.v2 TO 'privtest'@'localhost'; 60connection con1; 61 62--echo 63--echo #======================================================================== 64--echo # Test: No permissions on the table 65--echo #======================================================================== 66--echo 67 68--echo #------------------------------------------------------------------------ 69--echo # I/R/U/D/S on the table 70--echo # Expectation: Cannot run query, EXPLAIN, ANALYZE on the table 71--echo # because the query itself cannot be executed 72--echo #------------------------------------------------------------------------ 73 74--error ER_TABLEACCESS_DENIED_ERROR 75INSERT INTO t1 (a) VALUES (10); 76--error ER_TABLEACCESS_DENIED_ERROR 77EXPLAIN INSERT INTO t1 (a) VALUES (10); 78--error ER_TABLEACCESS_DENIED_ERROR 79ANALYZE INSERT INTO t1 (a) VALUES (10); 80 81--error ER_TABLEACCESS_DENIED_ERROR 82INSERT INTO t1 SELECT * FROM t2; 83--error ER_TABLEACCESS_DENIED_ERROR 84EXPLAIN INSERT INTO t1 SELECT * FROM t2; 85--error ER_TABLEACCESS_DENIED_ERROR 86ANALYZE INSERT INTO t1 SELECT * FROM t2; 87 88--error ER_TABLEACCESS_DENIED_ERROR 89REPLACE INTO t1 (a) VALUES (10); 90--error ER_TABLEACCESS_DENIED_ERROR 91EXPLAIN REPLACE INTO t1 (a) VALUES (10); 92--error ER_TABLEACCESS_DENIED_ERROR 93ANALYZE REPLACE INTO t1 (a) VALUES (10); 94 95--error ER_TABLEACCESS_DENIED_ERROR 96REPLACE INTO t1 SELECT * FROM t2; 97--error ER_TABLEACCESS_DENIED_ERROR 98EXPLAIN REPLACE INTO t1 SELECT * FROM t2; 99--error ER_TABLEACCESS_DENIED_ERROR 100ANALYZE REPLACE INTO t1 SELECT * FROM t2; 101 102--error ER_TABLEACCESS_DENIED_ERROR 103UPDATE t1 SET a = 10; 104--error ER_TABLEACCESS_DENIED_ERROR 105EXPLAIN UPDATE t1 SET a = 10; 106--error ER_TABLEACCESS_DENIED_ERROR 107ANALYZE UPDATE t1 SET a = 10; 108 109--error ER_TABLEACCESS_DENIED_ERROR 110UPDATE t1 SET a = a + 1; 111--error ER_TABLEACCESS_DENIED_ERROR 112EXPLAIN UPDATE t1 SET a = a + 1; 113--error ER_TABLEACCESS_DENIED_ERROR 114ANALYZE UPDATE t1 SET a = a + 1; 115 116--error ER_TABLEACCESS_DENIED_ERROR 117UPDATE t1, t2 SET t1.a = t1.a + 1 WHERE t1.a = t2.a; 118--error ER_TABLEACCESS_DENIED_ERROR 119EXPLAIN UPDATE t1, t2 SET t1.a = t1.a + 1 WHERE t1.a = t2.a; 120--error ER_TABLEACCESS_DENIED_ERROR 121ANALYZE UPDATE t1, t2 SET t1.a = t1.a + 1 WHERE t1.a = t2.a; 122 123--error ER_TABLEACCESS_DENIED_ERROR 124DELETE FROM t1; 125--error ER_TABLEACCESS_DENIED_ERROR 126EXPLAIN DELETE FROM t1; 127--error ER_TABLEACCESS_DENIED_ERROR 128ANALYZE DELETE FROM t1; 129 130--error ER_TABLEACCESS_DENIED_ERROR 131DELETE FROM t1 WHERE a = 10; 132--error ER_TABLEACCESS_DENIED_ERROR 133EXPLAIN DELETE FROM t1 WHERE a = 10; 134--error ER_TABLEACCESS_DENIED_ERROR 135ANALYZE DELETE FROM t1 WHERE a = 10; 136 137--error ER_TABLEACCESS_DENIED_ERROR 138DELETE FROM t1 USING t1, t2; 139--error ER_TABLEACCESS_DENIED_ERROR 140EXPLAIN DELETE FROM t1 USING t1, t2; 141--error ER_TABLEACCESS_DENIED_ERROR 142ANALYZE DELETE FROM t1 USING t1, t2; 143 144--error ER_TABLEACCESS_DENIED_ERROR 145DELETE FROM t1 USING t1, t2 WHERE t1.a = t2.a; 146--error ER_TABLEACCESS_DENIED_ERROR 147EXPLAIN DELETE FROM t1 USING t1, t2 WHERE t1.a = t2.a; 148--error ER_TABLEACCESS_DENIED_ERROR 149ANALYZE DELETE FROM t1 USING t1, t2 WHERE t1.a = t2.a; 150 151--error ER_TABLEACCESS_DENIED_ERROR 152SELECT * FROM t1; 153--error ER_TABLEACCESS_DENIED_ERROR 154EXPLAIN SELECT * FROM t1; 155--error ER_TABLEACCESS_DENIED_ERROR 156ANALYZE SELECT * FROM t1; 157 158--error ER_TABLEACCESS_DENIED_ERROR 159SELECT * FROM t1 WHERE a = 10; 160--error ER_TABLEACCESS_DENIED_ERROR 161EXPLAIN SELECT * FROM t1 WHERE a = 10; 162--error ER_TABLEACCESS_DENIED_ERROR 163ANALYZE SELECT * FROM t1 WHERE a = 10; 164 165--error ER_TABLEACCESS_DENIED_ERROR 166SELECT * FROM t1 WHERE a IN ( SELECT a FROM t2 ); 167--error ER_TABLEACCESS_DENIED_ERROR 168EXPLAIN SELECT * FROM t1 WHERE a IN ( SELECT a FROM t2 ); 169--error ER_TABLEACCESS_DENIED_ERROR 170ANALYZE SELECT * FROM t1 WHERE a IN ( SELECT a FROM t2 ); 171 172--echo #------------------------------------------------------------------------ 173--echo # I/R/U/D/S on the inner view 174--echo # Expectation: Can run the queries, but not EXPLAIN or ANALYZE 175--echo # because the query plan cannot be shown 176--echo # (it would have revealed the structure of the table) 177--echo #------------------------------------------------------------------------ 178 179INSERT INTO v1 (a) VALUES (10); 180--error ER_VIEW_NO_EXPLAIN 181EXPLAIN INSERT INTO v1 (a) VALUES (10); 182--error ER_VIEW_NO_EXPLAIN 183ANALYZE INSERT INTO v1 (a) VALUES (10); 184 185INSERT INTO v1 SELECT * FROM t2; 186--error ER_VIEW_NO_EXPLAIN 187EXPLAIN INSERT INTO v1 SELECT * FROM t2; 188--error ER_VIEW_NO_EXPLAIN 189ANALYZE INSERT INTO v1 SELECT * FROM t2; 190 191REPLACE INTO v1 (a) VALUES (10); 192--error ER_VIEW_NO_EXPLAIN 193EXPLAIN REPLACE INTO v1 (a) VALUES (10); 194--error ER_VIEW_NO_EXPLAIN 195ANALYZE REPLACE INTO v1 (a) VALUES (10); 196 197REPLACE INTO v1 SELECT * FROM t2; 198--error ER_VIEW_NO_EXPLAIN 199EXPLAIN REPLACE INTO v1 SELECT * FROM t2; 200--error ER_VIEW_NO_EXPLAIN 201ANALYZE REPLACE INTO v1 SELECT * FROM t2; 202 203UPDATE v1 SET a = 10; 204--error ER_VIEW_NO_EXPLAIN 205EXPLAIN UPDATE v1 SET a = 10; 206--error ER_VIEW_NO_EXPLAIN 207ANALYZE UPDATE v1 SET a = 10; 208 209UPDATE v1 SET a = a + 1; 210--error ER_VIEW_NO_EXPLAIN 211EXPLAIN UPDATE v1 SET a = a + 1; 212--error ER_VIEW_NO_EXPLAIN 213ANALYZE UPDATE v1 SET a = a + 1; 214 215UPDATE v1, t2 SET v1.a = v1.a + 1 WHERE v1.a = t2.a; 216--error ER_VIEW_NO_EXPLAIN 217EXPLAIN UPDATE v1, t2 SET v1.a = v1.a + 1 WHERE v1.a = t2.a; 218--error ER_VIEW_NO_EXPLAIN 219ANALYZE UPDATE v1, t2 SET v1.a = v1.a + 1 WHERE v1.a = t2.a; 220 221DELETE FROM v1; 222--error ER_VIEW_NO_EXPLAIN 223EXPLAIN DELETE FROM v1; 224--error ER_VIEW_NO_EXPLAIN 225ANALYZE DELETE FROM v1; 226 227DELETE FROM v1 WHERE a = 10; 228--error ER_VIEW_NO_EXPLAIN 229EXPLAIN DELETE FROM v1 WHERE a = 10; 230--error ER_VIEW_NO_EXPLAIN 231ANALYZE DELETE FROM v1 WHERE a = 10; 232 233DELETE FROM v1 USING v1, t2; 234--error ER_VIEW_NO_EXPLAIN 235EXPLAIN DELETE FROM v1 USING v1, t2; 236--error ER_VIEW_NO_EXPLAIN 237ANALYZE DELETE FROM v1 USING v1, t2; 238 239DELETE FROM v1 USING v1, t2 WHERE v1.a = t2.a; 240--error ER_VIEW_NO_EXPLAIN 241EXPLAIN DELETE FROM v1 USING v1, t2 WHERE v1.a = t2.a; 242--error ER_VIEW_NO_EXPLAIN 243ANALYZE DELETE FROM v1 USING v1, t2 WHERE v1.a = t2.a; 244 245SELECT * FROM v1; 246--error ER_VIEW_NO_EXPLAIN 247EXPLAIN SELECT * FROM v1; 248--error ER_VIEW_NO_EXPLAIN 249ANALYZE SELECT * FROM v1; 250 251SELECT * FROM v1 WHERE a = 10; 252--error ER_VIEW_NO_EXPLAIN 253EXPLAIN SELECT * FROM v1 WHERE a = 10; 254--error ER_VIEW_NO_EXPLAIN 255ANALYZE SELECT * FROM v1 WHERE a = 10; 256 257SELECT * FROM v1 WHERE a IN ( SELECT a FROM t2 ); 258--error ER_VIEW_NO_EXPLAIN 259EXPLAIN SELECT * FROM v1 WHERE a IN ( SELECT a FROM t2 ); 260--error ER_VIEW_NO_EXPLAIN 261ANALYZE SELECT * FROM v1 WHERE a IN ( SELECT a FROM t2 ); 262 263--echo #------------------------------------------------------------------------ 264--echo # I/R/U/D/S on the outer view 265--echo # Expectation: Can run the queries, but not EXPLAIN or ANALYZE 266--echo # because the query plan cannot be shown 267--echo # (it would have revealed the structure of the table) 268--echo #------------------------------------------------------------------------ 269 270 271INSERT INTO v2 (a) VALUES (10); 272--error ER_VIEW_NO_EXPLAIN 273EXPLAIN INSERT INTO v2 (a) VALUES (10); 274--error ER_VIEW_NO_EXPLAIN 275ANALYZE INSERT INTO v2 (a) VALUES (10); 276 277INSERT INTO v2 SELECT * FROM t2; 278--error ER_VIEW_NO_EXPLAIN 279EXPLAIN INSERT INTO v2 SELECT * FROM t2; 280--error ER_VIEW_NO_EXPLAIN 281ANALYZE INSERT INTO v2 SELECT * FROM t2; 282 283REPLACE INTO v2 (a) VALUES (10); 284--error ER_VIEW_NO_EXPLAIN 285EXPLAIN REPLACE INTO v2 (a) VALUES (10); 286--error ER_VIEW_NO_EXPLAIN 287ANALYZE REPLACE INTO v2 (a) VALUES (10); 288 289REPLACE INTO v2 SELECT * FROM t2; 290--error ER_VIEW_NO_EXPLAIN 291EXPLAIN REPLACE INTO v2 SELECT * FROM t2; 292--error ER_VIEW_NO_EXPLAIN 293ANALYZE REPLACE INTO v2 SELECT * FROM t2; 294 295UPDATE v2 SET a = 10; 296--error ER_VIEW_NO_EXPLAIN 297EXPLAIN UPDATE v2 SET a = 10; 298--error ER_VIEW_NO_EXPLAIN 299ANALYZE UPDATE v2 SET a = 10; 300 301UPDATE v2 SET a = a + 1; 302--error ER_VIEW_NO_EXPLAIN 303EXPLAIN UPDATE v2 SET a = a + 1; 304--error ER_VIEW_NO_EXPLAIN 305ANALYZE UPDATE v2 SET a = a + 1; 306 307UPDATE v2, t2 SET v2.a = v2.a + 1 WHERE v2.a = t2.a; 308--error ER_VIEW_NO_EXPLAIN 309EXPLAIN UPDATE v2, t2 SET v2.a = v2.a + 1 WHERE v2.a = t2.a; 310--error ER_VIEW_NO_EXPLAIN 311ANALYZE UPDATE v2, t2 SET v2.a = v2.a + 1 WHERE v2.a = t2.a; 312 313DELETE FROM v2; 314--error ER_VIEW_NO_EXPLAIN 315EXPLAIN DELETE FROM v2; 316--error ER_VIEW_NO_EXPLAIN 317ANALYZE DELETE FROM v2; 318 319DELETE FROM v2 WHERE a = 10; 320--error ER_VIEW_NO_EXPLAIN 321EXPLAIN DELETE FROM v2 WHERE a = 10; 322--error ER_VIEW_NO_EXPLAIN 323ANALYZE DELETE FROM v2 WHERE a = 10; 324 325DELETE FROM v2 USING v2, t2; 326--error ER_VIEW_NO_EXPLAIN 327EXPLAIN DELETE FROM v2 USING v2, t2; 328--error ER_VIEW_NO_EXPLAIN 329ANALYZE DELETE FROM v2 USING v2, t2; 330 331DELETE FROM v2 USING v2, t2 WHERE v2.a = t2.a; 332--error ER_VIEW_NO_EXPLAIN 333EXPLAIN DELETE FROM v2 USING v2, t2 WHERE v2.a = t2.a; 334--error ER_VIEW_NO_EXPLAIN 335ANALYZE DELETE FROM v2 USING v2, t2 WHERE v2.a = t2.a; 336 337SELECT * FROM v2; 338--error ER_VIEW_NO_EXPLAIN 339EXPLAIN SELECT * FROM v2; 340--error ER_VIEW_NO_EXPLAIN 341ANALYZE SELECT * FROM v2; 342 343SELECT * FROM v2 WHERE a = 10; 344--error ER_VIEW_NO_EXPLAIN 345EXPLAIN SELECT * FROM v2 WHERE a = 10; 346--error ER_VIEW_NO_EXPLAIN 347ANALYZE SELECT * FROM v2 WHERE a = 10; 348 349SELECT * FROM v2 WHERE a IN ( SELECT a FROM t2 ); 350--error ER_VIEW_NO_EXPLAIN 351EXPLAIN SELECT * FROM v2 WHERE a IN ( SELECT a FROM t2 ); 352--error ER_VIEW_NO_EXPLAIN 353ANALYZE SELECT * FROM v2 WHERE a IN ( SELECT a FROM t2 ); 354 355--echo 356--echo #======================================================================== 357--echo # Test: Grant SELECT on the table 358--echo #======================================================================== 359--echo 360 361connection default; 362GRANT SELECT ON privtest_db.t1 TO 'privtest'@'localhost'; 363connection con1; 364 365--echo #------------------------------------------------------------------------ 366--echo # I/R/U/D/S on the table 367--echo # Expectation: Can only run SELECT, EXPLAIN SELECT, ANALYZE SELECT 368--echo #------------------------------------------------------------------------ 369 370--error ER_TABLEACCESS_DENIED_ERROR 371INSERT INTO t1 (a) VALUES (10); 372--error ER_TABLEACCESS_DENIED_ERROR 373EXPLAIN INSERT INTO t1 (a) VALUES (10); 374--error ER_TABLEACCESS_DENIED_ERROR 375ANALYZE INSERT INTO t1 (a) VALUES (10); 376 377--error ER_TABLEACCESS_DENIED_ERROR 378INSERT INTO t1 SELECT * FROM t2; 379--error ER_TABLEACCESS_DENIED_ERROR 380EXPLAIN INSERT INTO t1 SELECT * FROM t2; 381--error ER_TABLEACCESS_DENIED_ERROR 382ANALYZE INSERT INTO t1 SELECT * FROM t2; 383 384--error ER_TABLEACCESS_DENIED_ERROR 385REPLACE INTO t1 (a) VALUES (10); 386--error ER_TABLEACCESS_DENIED_ERROR 387EXPLAIN REPLACE INTO t1 (a) VALUES (10); 388--error ER_TABLEACCESS_DENIED_ERROR 389ANALYZE REPLACE INTO t1 (a) VALUES (10); 390 391--error ER_TABLEACCESS_DENIED_ERROR 392REPLACE INTO t1 SELECT * FROM t2; 393--error ER_TABLEACCESS_DENIED_ERROR 394EXPLAIN REPLACE INTO t1 SELECT * FROM t2; 395--error ER_TABLEACCESS_DENIED_ERROR 396ANALYZE REPLACE INTO t1 SELECT * FROM t2; 397 398--error ER_TABLEACCESS_DENIED_ERROR 399UPDATE t1 SET a = 10; 400--error ER_TABLEACCESS_DENIED_ERROR 401EXPLAIN UPDATE t1 SET a = 10; 402--error ER_TABLEACCESS_DENIED_ERROR 403ANALYZE UPDATE t1 SET a = 10; 404 405--error ER_TABLEACCESS_DENIED_ERROR 406UPDATE t1 SET a = a + 1; 407--error ER_TABLEACCESS_DENIED_ERROR 408EXPLAIN UPDATE t1 SET a = a + 1; 409--error ER_TABLEACCESS_DENIED_ERROR 410ANALYZE UPDATE t1 SET a = a + 1; 411 412--error ER_TABLEACCESS_DENIED_ERROR 413UPDATE t1, t2 SET t1.a = t1.a + 1 WHERE t1.a = t2.a; 414--error ER_TABLEACCESS_DENIED_ERROR 415EXPLAIN UPDATE t1, t2 SET t1.a = t1.a + 1 WHERE t1.a = t2.a; 416--error ER_TABLEACCESS_DENIED_ERROR 417ANALYZE UPDATE t1, t2 SET t1.a = t1.a + 1 WHERE t1.a = t2.a; 418 419--error ER_TABLEACCESS_DENIED_ERROR 420DELETE FROM t1; 421--error ER_TABLEACCESS_DENIED_ERROR 422EXPLAIN DELETE FROM t1; 423--error ER_TABLEACCESS_DENIED_ERROR 424ANALYZE DELETE FROM t1; 425 426--error ER_TABLEACCESS_DENIED_ERROR 427DELETE FROM t1 WHERE a = 10; 428--error ER_TABLEACCESS_DENIED_ERROR 429EXPLAIN DELETE FROM t1 WHERE a = 10; 430--error ER_TABLEACCESS_DENIED_ERROR 431ANALYZE DELETE FROM t1 WHERE a = 10; 432 433--error ER_TABLEACCESS_DENIED_ERROR 434DELETE FROM t1 USING t1, t2; 435--error ER_TABLEACCESS_DENIED_ERROR 436EXPLAIN DELETE FROM t1 USING t1, t2; 437--error ER_TABLEACCESS_DENIED_ERROR 438ANALYZE DELETE FROM t1 USING t1, t2; 439 440--error ER_TABLEACCESS_DENIED_ERROR 441DELETE FROM t1 USING t1, t2 WHERE t1.a = t2.a; 442--error ER_TABLEACCESS_DENIED_ERROR 443EXPLAIN DELETE FROM t1 USING t1, t2 WHERE t1.a = t2.a; 444--error ER_TABLEACCESS_DENIED_ERROR 445ANALYZE DELETE FROM t1 USING t1, t2 WHERE t1.a = t2.a; 446 447SELECT * FROM t1; 448EXPLAIN SELECT * FROM t1; 449ANALYZE SELECT * FROM t1; 450 451SELECT * FROM t1 WHERE a = 10; 452EXPLAIN SELECT * FROM t1 WHERE a = 10; 453ANALYZE SELECT * FROM t1 WHERE a = 10; 454 455SELECT * FROM t1 WHERE a IN ( SELECT a FROM t2 ); 456EXPLAIN SELECT * FROM t1 WHERE a IN ( SELECT a FROM t2 ); 457ANALYZE SELECT * FROM t1 WHERE a IN ( SELECT a FROM t2 ); 458 459 460--echo #------------------------------------------------------------------------ 461--echo # I/R/U/D/S on the inner view 462--echo # Expectation: Can run everything 463--echo #------------------------------------------------------------------------ 464 465INSERT INTO v1 (a) VALUES (10); 466EXPLAIN INSERT INTO v1 (a) VALUES (10); 467ANALYZE INSERT INTO v1 (a) VALUES (10); 468 469INSERT INTO v1 SELECT * FROM t2; 470EXPLAIN INSERT INTO v1 SELECT * FROM t2; 471ANALYZE INSERT INTO v1 SELECT * FROM t2; 472 473REPLACE INTO v1 (a) VALUES (10); 474EXPLAIN REPLACE INTO v1 (a) VALUES (10); 475ANALYZE REPLACE INTO v1 (a) VALUES (10); 476 477REPLACE INTO v1 SELECT * FROM t2; 478EXPLAIN REPLACE INTO v1 SELECT * FROM t2; 479ANALYZE REPLACE INTO v1 SELECT * FROM t2; 480 481UPDATE v1 SET a = 10; 482EXPLAIN UPDATE v1 SET a = 10; 483ANALYZE UPDATE v1 SET a = 10; 484 485UPDATE v1 SET a = a + 1; 486EXPLAIN UPDATE v1 SET a = a + 1; 487ANALYZE UPDATE v1 SET a = a + 1; 488 489UPDATE v1, t2 SET v1.a = v1.a + 1 WHERE v1.a = t2.a; 490EXPLAIN UPDATE v1, t2 SET v1.a = v1.a + 1 WHERE v1.a = t2.a; 491ANALYZE UPDATE v1, t2 SET v1.a = v1.a + 1 WHERE v1.a = t2.a; 492 493DELETE FROM v1; 494EXPLAIN DELETE FROM v1; 495ANALYZE DELETE FROM v1; 496 497DELETE FROM v1 WHERE a = 10; 498EXPLAIN DELETE FROM v1 WHERE a = 10; 499ANALYZE DELETE FROM v1 WHERE a = 10; 500 501DELETE FROM v1 USING v1, t2; 502EXPLAIN DELETE FROM v1 USING v1, t2; 503ANALYZE DELETE FROM v1 USING v1, t2; 504 505DELETE FROM v1 USING v1, t2 WHERE v1.a = t2.a; 506EXPLAIN DELETE FROM v1 USING v1, t2 WHERE v1.a = t2.a; 507ANALYZE DELETE FROM v1 USING v1, t2 WHERE v1.a = t2.a; 508 509SELECT * FROM v1; 510EXPLAIN SELECT * FROM v1; 511ANALYZE SELECT * FROM v1; 512 513SELECT * FROM v1 WHERE a = 10; 514EXPLAIN SELECT * FROM v1 WHERE a = 10; 515ANALYZE SELECT * FROM v1 WHERE a = 10; 516 517SELECT * FROM v1 WHERE a IN ( SELECT a FROM t2 ); 518EXPLAIN SELECT * FROM v1 WHERE a IN ( SELECT a FROM t2 ); 519ANALYZE SELECT * FROM v1 WHERE a IN ( SELECT a FROM t2 ); 520 521 522--echo #------------------------------------------------------------------------ 523--echo # I/R/U/D/S on the outer view 524--echo # Expectation: Can run everything 525--echo #------------------------------------------------------------------------ 526 527INSERT INTO v2 (a) VALUES (10); 528EXPLAIN INSERT INTO v2 (a) VALUES (10); 529ANALYZE INSERT INTO v2 (a) VALUES (10); 530 531INSERT INTO v2 SELECT * FROM t2; 532EXPLAIN INSERT INTO v2 SELECT * FROM t2; 533ANALYZE INSERT INTO v2 SELECT * FROM t2; 534 535REPLACE INTO v2 (a) VALUES (10); 536EXPLAIN REPLACE INTO v2 (a) VALUES (10); 537ANALYZE REPLACE INTO v2 (a) VALUES (10); 538 539REPLACE INTO v2 SELECT * FROM t2; 540EXPLAIN REPLACE INTO v2 SELECT * FROM t2; 541ANALYZE REPLACE INTO v2 SELECT * FROM t2; 542 543UPDATE v2 SET a = 10; 544EXPLAIN UPDATE v2 SET a = 10; 545ANALYZE UPDATE v2 SET a = 10; 546 547UPDATE v2 SET a = a + 1; 548EXPLAIN UPDATE v2 SET a = a + 1; 549ANALYZE UPDATE v2 SET a = a + 1; 550 551UPDATE v2, t2 SET v2.a = v2.a + 1 WHERE v2.a = t2.a; 552EXPLAIN UPDATE v2, t2 SET v2.a = v2.a + 1 WHERE v2.a = t2.a; 553ANALYZE UPDATE v2, t2 SET v2.a = v2.a + 1 WHERE v2.a = t2.a; 554 555DELETE FROM v2; 556EXPLAIN DELETE FROM v2; 557ANALYZE DELETE FROM v2; 558 559DELETE FROM v2 WHERE a = 10; 560EXPLAIN DELETE FROM v2 WHERE a = 10; 561ANALYZE DELETE FROM v2 WHERE a = 10; 562 563DELETE FROM v2 USING v2, t2; 564# Commented due to MDEV-7034 (assertion failure) 565# EXPLAIN DELETE FROM v2 USING v2, t2; 566ANALYZE DELETE FROM v2 USING v2, t2; 567 568DELETE FROM v2 USING v2, t2 WHERE v2.a = t2.a; 569# Commented due to MDEV-7034 (assertion failure) 570# EXPLAIN DELETE FROM v2 USING v2, t2 WHERE v2.a = t2.a; 571ANALYZE DELETE FROM v2 USING v2, t2 WHERE v2.a = t2.a; 572 573SELECT * FROM v2; 574EXPLAIN SELECT * FROM v2; 575ANALYZE SELECT * FROM v2; 576 577SELECT * FROM v2 WHERE a = 10; 578EXPLAIN SELECT * FROM v2 WHERE a = 10; 579ANALYZE SELECT * FROM v2 WHERE a = 10; 580 581SELECT * FROM v2 WHERE a IN ( SELECT a FROM t2 ); 582EXPLAIN SELECT * FROM v2 WHERE a IN ( SELECT a FROM t2 ); 583ANALYZE SELECT * FROM v2 WHERE a IN ( SELECT a FROM t2 ); 584 585--echo 586--echo #======================================================================== 587--echo # Test: Grant INSERT on the table 588--echo #======================================================================== 589--echo 590 591connection default; 592REVOKE ALL PRIVILEGES ON privtest_db.t1 FROM 'privtest'@'localhost'; 593GRANT INSERT ON privtest_db.t1 TO 'privtest'@'localhost'; 594connection con1; 595 596--echo #------------------------------------------------------------------------ 597--echo # I/R/U/D/S on the table 598--echo # Expectation: Can only run INSERT, EXPLAIN INSERT, ANALYZE INSERT 599--echo #------------------------------------------------------------------------ 600 601INSERT INTO t1 (a) VALUES (10); 602EXPLAIN INSERT INTO t1 (a) VALUES (10); 603ANALYZE INSERT INTO t1 (a) VALUES (10); 604 605INSERT INTO t1 SELECT * FROM t2; 606EXPLAIN INSERT INTO t1 SELECT * FROM t2; 607ANALYZE INSERT INTO t1 SELECT * FROM t2; 608 609--error ER_TABLEACCESS_DENIED_ERROR 610REPLACE INTO t1 (a) VALUES (10); 611--error ER_TABLEACCESS_DENIED_ERROR 612EXPLAIN REPLACE INTO t1 (a) VALUES (10); 613--error ER_TABLEACCESS_DENIED_ERROR 614ANALYZE REPLACE INTO t1 (a) VALUES (10); 615 616--error ER_TABLEACCESS_DENIED_ERROR 617REPLACE INTO t1 SELECT * FROM t2; 618--error ER_TABLEACCESS_DENIED_ERROR 619EXPLAIN REPLACE INTO t1 SELECT * FROM t2; 620--error ER_TABLEACCESS_DENIED_ERROR 621ANALYZE REPLACE INTO t1 SELECT * FROM t2; 622 623--error ER_TABLEACCESS_DENIED_ERROR 624UPDATE t1 SET a = 10; 625--error ER_TABLEACCESS_DENIED_ERROR 626EXPLAIN UPDATE t1 SET a = 10; 627--error ER_TABLEACCESS_DENIED_ERROR 628ANALYZE UPDATE t1 SET a = 10; 629 630--error ER_TABLEACCESS_DENIED_ERROR 631UPDATE t1 SET a = a + 1; 632--error ER_TABLEACCESS_DENIED_ERROR 633EXPLAIN UPDATE t1 SET a = a + 1; 634--error ER_TABLEACCESS_DENIED_ERROR 635ANALYZE UPDATE t1 SET a = a + 1; 636 637--error ER_TABLEACCESS_DENIED_ERROR 638UPDATE t1, t2 SET t1.a = t1.a + 1 WHERE t1.a = t2.a; 639--error ER_TABLEACCESS_DENIED_ERROR 640EXPLAIN UPDATE t1, t2 SET t1.a = t1.a + 1 WHERE t1.a = t2.a; 641--error ER_TABLEACCESS_DENIED_ERROR 642ANALYZE UPDATE t1, t2 SET t1.a = t1.a + 1 WHERE t1.a = t2.a; 643 644--error ER_TABLEACCESS_DENIED_ERROR 645DELETE FROM t1; 646--error ER_TABLEACCESS_DENIED_ERROR 647EXPLAIN DELETE FROM t1; 648--error ER_TABLEACCESS_DENIED_ERROR 649ANALYZE DELETE FROM t1; 650 651--error ER_TABLEACCESS_DENIED_ERROR 652DELETE FROM t1 WHERE a = 10; 653--error ER_TABLEACCESS_DENIED_ERROR 654EXPLAIN DELETE FROM t1 WHERE a = 10; 655--error ER_TABLEACCESS_DENIED_ERROR 656ANALYZE DELETE FROM t1 WHERE a = 10; 657 658--error ER_TABLEACCESS_DENIED_ERROR 659DELETE FROM t1 USING t1, t2; 660--error ER_TABLEACCESS_DENIED_ERROR 661EXPLAIN DELETE FROM t1 USING t1, t2; 662--error ER_TABLEACCESS_DENIED_ERROR 663ANALYZE DELETE FROM t1 USING t1, t2; 664 665--error ER_TABLEACCESS_DENIED_ERROR 666DELETE FROM t1 USING t1, t2 WHERE t1.a = t2.a; 667--error ER_TABLEACCESS_DENIED_ERROR 668EXPLAIN DELETE FROM t1 USING t1, t2 WHERE t1.a = t2.a; 669--error ER_TABLEACCESS_DENIED_ERROR 670ANALYZE DELETE FROM t1 USING t1, t2 WHERE t1.a = t2.a; 671 672--error ER_TABLEACCESS_DENIED_ERROR 673SELECT * FROM t1; 674--error ER_TABLEACCESS_DENIED_ERROR 675EXPLAIN SELECT * FROM t1; 676--error ER_TABLEACCESS_DENIED_ERROR 677ANALYZE SELECT * FROM t1; 678 679--error ER_TABLEACCESS_DENIED_ERROR 680SELECT * FROM t1 WHERE a = 10; 681--error ER_TABLEACCESS_DENIED_ERROR 682EXPLAIN SELECT * FROM t1 WHERE a = 10; 683--error ER_TABLEACCESS_DENIED_ERROR 684ANALYZE SELECT * FROM t1 WHERE a = 10; 685 686--error ER_TABLEACCESS_DENIED_ERROR 687SELECT * FROM t1 WHERE a IN ( SELECT a FROM t2 ); 688--error ER_TABLEACCESS_DENIED_ERROR 689EXPLAIN SELECT * FROM t1 WHERE a IN ( SELECT a FROM t2 ); 690--error ER_TABLEACCESS_DENIED_ERROR 691ANALYZE SELECT * FROM t1 WHERE a IN ( SELECT a FROM t2 ); 692 693--echo #------------------------------------------------------------------------ 694--echo # I/R/U/D/S on the inner view 695--echo # Expectation: Can run the queries, but not EXPLAIN or ANALYZE 696--echo # because the query plan cannot be shown 697--echo # (it would have revealed the structure of the table) 698--echo #------------------------------------------------------------------------ 699 700INSERT INTO v1 (a) VALUES (10); 701--error ER_VIEW_NO_EXPLAIN 702EXPLAIN INSERT INTO v1 (a) VALUES (10); 703--error ER_VIEW_NO_EXPLAIN 704ANALYZE INSERT INTO v1 (a) VALUES (10); 705 706INSERT INTO v1 SELECT * FROM t2; 707--error ER_VIEW_NO_EXPLAIN 708EXPLAIN INSERT INTO v1 SELECT * FROM t2; 709--error ER_VIEW_NO_EXPLAIN 710ANALYZE INSERT INTO v1 SELECT * FROM t2; 711 712REPLACE INTO v1 (a) VALUES (10); 713--error ER_VIEW_NO_EXPLAIN 714EXPLAIN REPLACE INTO v1 (a) VALUES (10); 715--error ER_VIEW_NO_EXPLAIN 716ANALYZE REPLACE INTO v1 (a) VALUES (10); 717 718REPLACE INTO v1 SELECT * FROM t2; 719--error ER_VIEW_NO_EXPLAIN 720EXPLAIN REPLACE INTO v1 SELECT * FROM t2; 721--error ER_VIEW_NO_EXPLAIN 722ANALYZE REPLACE INTO v1 SELECT * FROM t2; 723 724UPDATE v1 SET a = 10; 725--error ER_VIEW_NO_EXPLAIN 726EXPLAIN UPDATE v1 SET a = 10; 727--error ER_VIEW_NO_EXPLAIN 728ANALYZE UPDATE v1 SET a = 10; 729 730UPDATE v1 SET a = a + 1; 731--error ER_VIEW_NO_EXPLAIN 732EXPLAIN UPDATE v1 SET a = a + 1; 733--error ER_VIEW_NO_EXPLAIN 734ANALYZE UPDATE v1 SET a = a + 1; 735 736UPDATE v1, t2 SET v1.a = v1.a + 1 WHERE v1.a = t2.a; 737--error ER_VIEW_NO_EXPLAIN 738EXPLAIN UPDATE v1, t2 SET v1.a = v1.a + 1 WHERE v1.a = t2.a; 739--error ER_VIEW_NO_EXPLAIN 740ANALYZE UPDATE v1, t2 SET v1.a = v1.a + 1 WHERE v1.a = t2.a; 741 742DELETE FROM v1; 743--error ER_VIEW_NO_EXPLAIN 744EXPLAIN DELETE FROM v1; 745--error ER_VIEW_NO_EXPLAIN 746ANALYZE DELETE FROM v1; 747 748DELETE FROM v1 WHERE a = 10; 749--error ER_VIEW_NO_EXPLAIN 750EXPLAIN DELETE FROM v1 WHERE a = 10; 751--error ER_VIEW_NO_EXPLAIN 752ANALYZE DELETE FROM v1 WHERE a = 10; 753 754DELETE FROM v1 USING v1, t2; 755--error ER_VIEW_NO_EXPLAIN 756EXPLAIN DELETE FROM v1 USING v1, t2; 757--error ER_VIEW_NO_EXPLAIN 758ANALYZE DELETE FROM v1 USING v1, t2; 759 760DELETE FROM v1 USING v1, t2 WHERE v1.a = t2.a; 761--error ER_VIEW_NO_EXPLAIN 762EXPLAIN DELETE FROM v1 USING v1, t2 WHERE v1.a = t2.a; 763--error ER_VIEW_NO_EXPLAIN 764ANALYZE DELETE FROM v1 USING v1, t2 WHERE v1.a = t2.a; 765 766SELECT * FROM v1; 767--error ER_VIEW_NO_EXPLAIN 768EXPLAIN SELECT * FROM v1; 769--error ER_VIEW_NO_EXPLAIN 770ANALYZE SELECT * FROM v1; 771 772SELECT * FROM v1 WHERE a = 10; 773--error ER_VIEW_NO_EXPLAIN 774EXPLAIN SELECT * FROM v1 WHERE a = 10; 775--error ER_VIEW_NO_EXPLAIN 776ANALYZE SELECT * FROM v1 WHERE a = 10; 777 778SELECT * FROM v1 WHERE a IN ( SELECT a FROM t2 ); 779--error ER_VIEW_NO_EXPLAIN 780EXPLAIN SELECT * FROM v1 WHERE a IN ( SELECT a FROM t2 ); 781--error ER_VIEW_NO_EXPLAIN 782ANALYZE SELECT * FROM v1 WHERE a IN ( SELECT a FROM t2 ); 783 784 785--echo #------------------------------------------------------------------------ 786--echo # I/R/U/D/S on the outer view 787--echo # Expectation: Can run the queries, but not EXPLAIN or ANALYZE 788--echo # because the query plan cannot be shown 789--echo # (it would have revealed the structure of the table) 790--echo #------------------------------------------------------------------------ 791 792 793INSERT INTO v2 (a) VALUES (10); 794--error ER_VIEW_NO_EXPLAIN 795EXPLAIN INSERT INTO v2 (a) VALUES (10); 796--error ER_VIEW_NO_EXPLAIN 797ANALYZE INSERT INTO v2 (a) VALUES (10); 798 799INSERT INTO v2 SELECT * FROM t2; 800--error ER_VIEW_NO_EXPLAIN 801EXPLAIN INSERT INTO v2 SELECT * FROM t2; 802--error ER_VIEW_NO_EXPLAIN 803ANALYZE INSERT INTO v2 SELECT * FROM t2; 804 805REPLACE INTO v2 (a) VALUES (10); 806--error ER_VIEW_NO_EXPLAIN 807EXPLAIN REPLACE INTO v2 (a) VALUES (10); 808--error ER_VIEW_NO_EXPLAIN 809ANALYZE REPLACE INTO v2 (a) VALUES (10); 810 811REPLACE INTO v2 SELECT * FROM t2; 812--error ER_VIEW_NO_EXPLAIN 813EXPLAIN REPLACE INTO v2 SELECT * FROM t2; 814--error ER_VIEW_NO_EXPLAIN 815ANALYZE REPLACE INTO v2 SELECT * FROM t2; 816 817UPDATE v2 SET a = 10; 818--error ER_VIEW_NO_EXPLAIN 819EXPLAIN UPDATE v2 SET a = 10; 820--error ER_VIEW_NO_EXPLAIN 821ANALYZE UPDATE v2 SET a = 10; 822 823UPDATE v2 SET a = a + 1; 824--error ER_VIEW_NO_EXPLAIN 825EXPLAIN UPDATE v2 SET a = a + 1; 826--error ER_VIEW_NO_EXPLAIN 827ANALYZE UPDATE v2 SET a = a + 1; 828 829UPDATE v2, t2 SET v2.a = v2.a + 1 WHERE v2.a = t2.a; 830--error ER_VIEW_NO_EXPLAIN 831EXPLAIN UPDATE v2, t2 SET v2.a = v2.a + 1 WHERE v2.a = t2.a; 832--error ER_VIEW_NO_EXPLAIN 833ANALYZE UPDATE v2, t2 SET v2.a = v2.a + 1 WHERE v2.a = t2.a; 834 835DELETE FROM v2; 836--error ER_VIEW_NO_EXPLAIN 837EXPLAIN DELETE FROM v2; 838--error ER_VIEW_NO_EXPLAIN 839ANALYZE DELETE FROM v2; 840 841DELETE FROM v2 WHERE a = 10; 842--error ER_VIEW_NO_EXPLAIN 843EXPLAIN DELETE FROM v2 WHERE a = 10; 844--error ER_VIEW_NO_EXPLAIN 845ANALYZE DELETE FROM v2 WHERE a = 10; 846 847DELETE FROM v2 USING v2, t2; 848--error ER_VIEW_NO_EXPLAIN 849EXPLAIN DELETE FROM v2 USING v2, t2; 850--error ER_VIEW_NO_EXPLAIN 851ANALYZE DELETE FROM v2 USING v2, t2; 852 853DELETE FROM v2 USING v2, t2 WHERE v2.a = t2.a; 854--error ER_VIEW_NO_EXPLAIN 855EXPLAIN DELETE FROM v2 USING v2, t2 WHERE v2.a = t2.a; 856--error ER_VIEW_NO_EXPLAIN 857ANALYZE DELETE FROM v2 USING v2, t2 WHERE v2.a = t2.a; 858 859SELECT * FROM v2; 860--error ER_VIEW_NO_EXPLAIN 861EXPLAIN SELECT * FROM v2; 862--error ER_VIEW_NO_EXPLAIN 863ANALYZE SELECT * FROM v2; 864 865SELECT * FROM v2 WHERE a = 10; 866--error ER_VIEW_NO_EXPLAIN 867EXPLAIN SELECT * FROM v2 WHERE a = 10; 868--error ER_VIEW_NO_EXPLAIN 869ANALYZE SELECT * FROM v2 WHERE a = 10; 870 871SELECT * FROM v2 WHERE a IN ( SELECT a FROM t2 ); 872--error ER_VIEW_NO_EXPLAIN 873EXPLAIN SELECT * FROM v2 WHERE a IN ( SELECT a FROM t2 ); 874--error ER_VIEW_NO_EXPLAIN 875ANALYZE SELECT * FROM v2 WHERE a IN ( SELECT a FROM t2 ); 876 877 878--echo 879--echo #======================================================================== 880--echo # Test: Grant UPDATE on the table 881--echo #======================================================================== 882--echo 883 884connection default; 885REVOKE ALL PRIVILEGES ON privtest_db.t1 FROM 'privtest'@'localhost'; 886GRANT UPDATE ON privtest_db.t1 TO 'privtest'@'localhost'; 887connection con1; 888 889--echo #------------------------------------------------------------------------ 890--echo # I/R/U/D/S on the table 891--echo # Expectation: Can only run UPDATE, EXPLAIN UPDATE, ANALYZE UPDATE 892--echo # when the UPDATE does not read any columns. UPDATEs which 893--echo # read columns fail with ER_COLUMNACCESS_DENIED_ERROR 894--echo #------------------------------------------------------------------------ 895 896--error ER_TABLEACCESS_DENIED_ERROR 897INSERT INTO t1 (a) VALUES (10); 898--error ER_TABLEACCESS_DENIED_ERROR 899EXPLAIN INSERT INTO t1 (a) VALUES (10); 900--error ER_TABLEACCESS_DENIED_ERROR 901ANALYZE INSERT INTO t1 (a) VALUES (10); 902 903--error ER_TABLEACCESS_DENIED_ERROR 904ANALYZE INSERT INTO t1 SELECT * FROM t2; 905--error ER_TABLEACCESS_DENIED_ERROR 906ANALYZE INSERT INTO t1 SELECT * FROM t2; 907--error ER_TABLEACCESS_DENIED_ERROR 908ANALYZE INSERT INTO t1 SELECT * FROM t2; 909 910--error ER_TABLEACCESS_DENIED_ERROR 911REPLACE INTO t1 (a) VALUES (10); 912--error ER_TABLEACCESS_DENIED_ERROR 913EXPLAIN REPLACE INTO t1 (a) VALUES (10); 914--error ER_TABLEACCESS_DENIED_ERROR 915ANALYZE REPLACE INTO t1 (a) VALUES (10); 916 917--error ER_TABLEACCESS_DENIED_ERROR 918REPLACE INTO t1 SELECT * FROM t2; 919--error ER_TABLEACCESS_DENIED_ERROR 920EXPLAIN REPLACE INTO t1 SELECT * FROM t2; 921--error ER_TABLEACCESS_DENIED_ERROR 922ANALYZE REPLACE INTO t1 SELECT * FROM t2; 923 924UPDATE t1 SET a = 10; 925EXPLAIN UPDATE t1 SET a = 10; 926ANALYZE UPDATE t1 SET a = 10; 927 928--error ER_COLUMNACCESS_DENIED_ERROR 929UPDATE t1 SET a = a + 1; 930--error ER_COLUMNACCESS_DENIED_ERROR 931EXPLAIN UPDATE t1 SET a = a + 1; 932--error ER_COLUMNACCESS_DENIED_ERROR 933ANALYZE UPDATE t1 SET a = a + 1; 934 935--error ER_COLUMNACCESS_DENIED_ERROR 936UPDATE t1, t2 SET t1.a = t1.a + 1 WHERE t1.a = t2.a; 937--error ER_COLUMNACCESS_DENIED_ERROR 938EXPLAIN UPDATE t1, t2 SET t1.a = t1.a + 1 WHERE t1.a = t2.a; 939--error ER_COLUMNACCESS_DENIED_ERROR 940ANALYZE UPDATE t1, t2 SET t1.a = t1.a + 1 WHERE t1.a = t2.a; 941 942--error ER_TABLEACCESS_DENIED_ERROR 943DELETE FROM t1; 944--error ER_TABLEACCESS_DENIED_ERROR 945EXPLAIN DELETE FROM t1; 946--error ER_TABLEACCESS_DENIED_ERROR 947ANALYZE DELETE FROM t1; 948 949--error ER_TABLEACCESS_DENIED_ERROR 950DELETE FROM t1 WHERE a = 10; 951--error ER_TABLEACCESS_DENIED_ERROR 952EXPLAIN DELETE FROM t1 WHERE a = 10; 953--error ER_TABLEACCESS_DENIED_ERROR 954ANALYZE DELETE FROM t1 WHERE a = 10; 955 956--error ER_TABLEACCESS_DENIED_ERROR 957DELETE FROM t1 USING t1, t2; 958--error ER_TABLEACCESS_DENIED_ERROR 959EXPLAIN DELETE FROM t1 USING t1, t2; 960--error ER_TABLEACCESS_DENIED_ERROR 961ANALYZE DELETE FROM t1 USING t1, t2; 962 963--error ER_TABLEACCESS_DENIED_ERROR 964DELETE FROM t1 USING t1, t2 WHERE t1.a = t2.a; 965--error ER_TABLEACCESS_DENIED_ERROR 966EXPLAIN DELETE FROM t1 USING t1, t2 WHERE t1.a = t2.a; 967--error ER_TABLEACCESS_DENIED_ERROR 968ANALYZE DELETE FROM t1 USING t1, t2 WHERE t1.a = t2.a; 969 970--error ER_TABLEACCESS_DENIED_ERROR 971SELECT * FROM t1; 972--error ER_TABLEACCESS_DENIED_ERROR 973EXPLAIN SELECT * FROM t1; 974--error ER_TABLEACCESS_DENIED_ERROR 975ANALYZE SELECT * FROM t1; 976 977--error ER_TABLEACCESS_DENIED_ERROR 978SELECT * FROM t1 WHERE a = 10; 979--error ER_TABLEACCESS_DENIED_ERROR 980EXPLAIN SELECT * FROM t1 WHERE a = 10; 981--error ER_TABLEACCESS_DENIED_ERROR 982ANALYZE SELECT * FROM t1 WHERE a = 10; 983 984--error ER_TABLEACCESS_DENIED_ERROR 985SELECT * FROM t1 WHERE a IN ( SELECT a FROM t2 ); 986--error ER_TABLEACCESS_DENIED_ERROR 987EXPLAIN SELECT * FROM t1 WHERE a IN ( SELECT a FROM t2 ); 988--error ER_TABLEACCESS_DENIED_ERROR 989ANALYZE SELECT * FROM t1 WHERE a IN ( SELECT a FROM t2 ); 990 991--echo #------------------------------------------------------------------------ 992--echo # I/R/U/D/S on the inner view 993--echo # Expectation: Can run the queries, but not EXPLAIN or ANALYZE 994--echo # because the query plan cannot be shown 995--echo # (it would have revealed the structure of the table) 996--echo #------------------------------------------------------------------------ 997 998INSERT INTO v1 (a) VALUES (10); 999--error ER_VIEW_NO_EXPLAIN 1000EXPLAIN INSERT INTO v1 (a) VALUES (10); 1001--error ER_VIEW_NO_EXPLAIN 1002ANALYZE INSERT INTO v1 (a) VALUES (10); 1003 1004INSERT INTO v1 SELECT * FROM t2; 1005--error ER_VIEW_NO_EXPLAIN 1006EXPLAIN INSERT INTO v1 SELECT * FROM t2; 1007--error ER_VIEW_NO_EXPLAIN 1008ANALYZE INSERT INTO v1 SELECT * FROM t2; 1009 1010REPLACE INTO v1 (a) VALUES (10); 1011--error ER_VIEW_NO_EXPLAIN 1012EXPLAIN REPLACE INTO v1 (a) VALUES (10); 1013--error ER_VIEW_NO_EXPLAIN 1014ANALYZE REPLACE INTO v1 (a) VALUES (10); 1015 1016REPLACE INTO v1 SELECT * FROM t2; 1017--error ER_VIEW_NO_EXPLAIN 1018EXPLAIN REPLACE INTO v1 SELECT * FROM t2; 1019--error ER_VIEW_NO_EXPLAIN 1020ANALYZE REPLACE INTO v1 SELECT * FROM t2; 1021 1022UPDATE v1 SET a = 10; 1023--error ER_VIEW_NO_EXPLAIN 1024EXPLAIN UPDATE v1 SET a = 10; 1025--error ER_VIEW_NO_EXPLAIN 1026ANALYZE UPDATE v1 SET a = 10; 1027 1028UPDATE v1 SET a = a + 1; 1029--error ER_VIEW_NO_EXPLAIN 1030EXPLAIN UPDATE v1 SET a = a + 1; 1031--error ER_VIEW_NO_EXPLAIN 1032ANALYZE UPDATE v1 SET a = a + 1; 1033 1034UPDATE v1, t2 SET v1.a = v1.a + 1 WHERE v1.a = t2.a; 1035--error ER_VIEW_NO_EXPLAIN 1036EXPLAIN UPDATE v1, t2 SET v1.a = v1.a + 1 WHERE v1.a = t2.a; 1037--error ER_VIEW_NO_EXPLAIN 1038ANALYZE UPDATE v1, t2 SET v1.a = v1.a + 1 WHERE v1.a = t2.a; 1039 1040DELETE FROM v1; 1041--error ER_VIEW_NO_EXPLAIN 1042EXPLAIN DELETE FROM v1; 1043--error ER_VIEW_NO_EXPLAIN 1044ANALYZE DELETE FROM v1; 1045 1046DELETE FROM v1 WHERE a = 10; 1047--error ER_VIEW_NO_EXPLAIN 1048EXPLAIN DELETE FROM v1 WHERE a = 10; 1049--error ER_VIEW_NO_EXPLAIN 1050ANALYZE DELETE FROM v1 WHERE a = 10; 1051 1052DELETE FROM v1 USING v1, t2; 1053--error ER_VIEW_NO_EXPLAIN 1054EXPLAIN DELETE FROM v1 USING v1, t2; 1055--error ER_VIEW_NO_EXPLAIN 1056ANALYZE DELETE FROM v1 USING v1, t2; 1057 1058DELETE FROM v1 USING v1, t2 WHERE v1.a = t2.a; 1059--error ER_VIEW_NO_EXPLAIN 1060EXPLAIN DELETE FROM v1 USING v1, t2 WHERE v1.a = t2.a; 1061--error ER_VIEW_NO_EXPLAIN 1062ANALYZE DELETE FROM v1 USING v1, t2 WHERE v1.a = t2.a; 1063 1064SELECT * FROM v1; 1065--error ER_VIEW_NO_EXPLAIN 1066EXPLAIN SELECT * FROM v1; 1067--error ER_VIEW_NO_EXPLAIN 1068ANALYZE SELECT * FROM v1; 1069 1070SELECT * FROM v1 WHERE a = 10; 1071--error ER_VIEW_NO_EXPLAIN 1072EXPLAIN SELECT * FROM v1 WHERE a = 10; 1073--error ER_VIEW_NO_EXPLAIN 1074ANALYZE SELECT * FROM v1 WHERE a = 10; 1075 1076SELECT * FROM v1 WHERE a IN ( SELECT a FROM t2 ); 1077--error ER_VIEW_NO_EXPLAIN 1078EXPLAIN SELECT * FROM v1 WHERE a IN ( SELECT a FROM t2 ); 1079--error ER_VIEW_NO_EXPLAIN 1080ANALYZE SELECT * FROM v1 WHERE a IN ( SELECT a FROM t2 ); 1081 1082 1083--echo #------------------------------------------------------------------------ 1084--echo # I/R/U/D/S on the outer view 1085--echo # Expectation: Can run the queries, but not EXPLAIN or ANALYZE 1086--echo # because the query plan cannot be shown 1087--echo # (it would have revealed the structure of the table) 1088--echo #------------------------------------------------------------------------ 1089 1090 1091INSERT INTO v2 (a) VALUES (10); 1092--error ER_VIEW_NO_EXPLAIN 1093EXPLAIN INSERT INTO v2 (a) VALUES (10); 1094--error ER_VIEW_NO_EXPLAIN 1095ANALYZE INSERT INTO v2 (a) VALUES (10); 1096 1097INSERT INTO v2 SELECT * FROM t2; 1098--error ER_VIEW_NO_EXPLAIN 1099EXPLAIN INSERT INTO v2 SELECT * FROM t2; 1100--error ER_VIEW_NO_EXPLAIN 1101ANALYZE INSERT INTO v2 SELECT * FROM t2; 1102 1103REPLACE INTO v2 (a) VALUES (10); 1104--error ER_VIEW_NO_EXPLAIN 1105EXPLAIN REPLACE INTO v2 (a) VALUES (10); 1106--error ER_VIEW_NO_EXPLAIN 1107ANALYZE REPLACE INTO v2 (a) VALUES (10); 1108 1109REPLACE INTO v2 SELECT * FROM t2; 1110--error ER_VIEW_NO_EXPLAIN 1111EXPLAIN REPLACE INTO v2 SELECT * FROM t2; 1112--error ER_VIEW_NO_EXPLAIN 1113ANALYZE REPLACE INTO v2 SELECT * FROM t2; 1114 1115UPDATE v2 SET a = 10; 1116--error ER_VIEW_NO_EXPLAIN 1117EXPLAIN UPDATE v2 SET a = 10; 1118--error ER_VIEW_NO_EXPLAIN 1119ANALYZE UPDATE v2 SET a = 10; 1120 1121UPDATE v2 SET a = a + 1; 1122--error ER_VIEW_NO_EXPLAIN 1123EXPLAIN UPDATE v2 SET a = a + 1; 1124--error ER_VIEW_NO_EXPLAIN 1125ANALYZE UPDATE v2 SET a = a + 1; 1126 1127UPDATE v2, t2 SET v2.a = v2.a + 1 WHERE v2.a = t2.a; 1128--error ER_VIEW_NO_EXPLAIN 1129EXPLAIN UPDATE v2, t2 SET v2.a = v2.a + 1 WHERE v2.a = t2.a; 1130--error ER_VIEW_NO_EXPLAIN 1131ANALYZE UPDATE v2, t2 SET v2.a = v2.a + 1 WHERE v2.a = t2.a; 1132 1133DELETE FROM v2; 1134--error ER_VIEW_NO_EXPLAIN 1135EXPLAIN DELETE FROM v2; 1136--error ER_VIEW_NO_EXPLAIN 1137ANALYZE DELETE FROM v2; 1138 1139DELETE FROM v2 WHERE a = 10; 1140--error ER_VIEW_NO_EXPLAIN 1141EXPLAIN DELETE FROM v2 WHERE a = 10; 1142--error ER_VIEW_NO_EXPLAIN 1143ANALYZE DELETE FROM v2 WHERE a = 10; 1144 1145DELETE FROM v2 USING v2, t2; 1146--error ER_VIEW_NO_EXPLAIN 1147EXPLAIN DELETE FROM v2 USING v2, t2; 1148--error ER_VIEW_NO_EXPLAIN 1149ANALYZE DELETE FROM v2 USING v2, t2; 1150 1151DELETE FROM v2 USING v2, t2 WHERE v2.a = t2.a; 1152--error ER_VIEW_NO_EXPLAIN 1153EXPLAIN DELETE FROM v2 USING v2, t2 WHERE v2.a = t2.a; 1154--error ER_VIEW_NO_EXPLAIN 1155ANALYZE DELETE FROM v2 USING v2, t2 WHERE v2.a = t2.a; 1156 1157SELECT * FROM v2; 1158--error ER_VIEW_NO_EXPLAIN 1159EXPLAIN SELECT * FROM v2; 1160--error ER_VIEW_NO_EXPLAIN 1161ANALYZE SELECT * FROM v2; 1162 1163SELECT * FROM v2 WHERE a = 10; 1164--error ER_VIEW_NO_EXPLAIN 1165EXPLAIN SELECT * FROM v2 WHERE a = 10; 1166--error ER_VIEW_NO_EXPLAIN 1167ANALYZE SELECT * FROM v2 WHERE a = 10; 1168 1169SELECT * FROM v2 WHERE a IN ( SELECT a FROM t2 ); 1170--error ER_VIEW_NO_EXPLAIN 1171EXPLAIN SELECT * FROM v2 WHERE a IN ( SELECT a FROM t2 ); 1172--error ER_VIEW_NO_EXPLAIN 1173ANALYZE SELECT * FROM v2 WHERE a IN ( SELECT a FROM t2 ); 1174 1175 1176--echo 1177--echo #======================================================================== 1178--echo # Test: Grant DELETE on the table 1179--echo #======================================================================== 1180--echo 1181 1182connection default; 1183REVOKE ALL PRIVILEGES ON privtest_db.t1 FROM 'privtest'@'localhost'; 1184GRANT DELETE ON privtest_db.t1 TO 'privtest'@'localhost'; 1185connection con1; 1186 1187--echo #------------------------------------------------------------------------ 1188--echo # I/R/U/D/S on the table 1189--echo # Expectation: Can only run DELETE, EXPLAIN DELETE, ANALYZE DELETE 1190--echo # when the DELETE does not read any columns. DELETEs which 1191--echo # read columns fail with ER_COLUMNACCESS_DENIED_ERROR 1192--echo #------------------------------------------------------------------------ 1193--echo # Note: ANALYZE DELETE FROM t1 USING t1, t2 ... fails due to MDEV-7043 1194 1195--error ER_TABLEACCESS_DENIED_ERROR 1196INSERT INTO t1 (a) VALUES (10); 1197--error ER_TABLEACCESS_DENIED_ERROR 1198EXPLAIN INSERT INTO t1 (a) VALUES (10); 1199--error ER_TABLEACCESS_DENIED_ERROR 1200ANALYZE INSERT INTO t1 (a) VALUES (10); 1201 1202--error ER_TABLEACCESS_DENIED_ERROR 1203INSERT INTO t1 SELECT * FROM t2; 1204--error ER_TABLEACCESS_DENIED_ERROR 1205EXPLAIN INSERT INTO t1 SELECT * FROM t2; 1206--error ER_TABLEACCESS_DENIED_ERROR 1207ANALYZE INSERT INTO t1 SELECT * FROM t2; 1208 1209--error ER_TABLEACCESS_DENIED_ERROR 1210REPLACE INTO t1 (a) VALUES (10); 1211--error ER_TABLEACCESS_DENIED_ERROR 1212EXPLAIN REPLACE INTO t1 (a) VALUES (10); 1213--error ER_TABLEACCESS_DENIED_ERROR 1214ANALYZE REPLACE INTO t1 (a) VALUES (10); 1215 1216--error ER_TABLEACCESS_DENIED_ERROR 1217REPLACE INTO t1 SELECT * FROM t2; 1218--error ER_TABLEACCESS_DENIED_ERROR 1219EXPLAIN REPLACE INTO t1 SELECT * FROM t2; 1220--error ER_TABLEACCESS_DENIED_ERROR 1221ANALYZE REPLACE INTO t1 SELECT * FROM t2; 1222 1223--error ER_TABLEACCESS_DENIED_ERROR 1224UPDATE t1 SET a = 10; 1225--error ER_TABLEACCESS_DENIED_ERROR 1226EXPLAIN UPDATE t1 SET a = 10; 1227--error ER_TABLEACCESS_DENIED_ERROR 1228ANALYZE UPDATE t1 SET a = 10; 1229 1230--error ER_TABLEACCESS_DENIED_ERROR 1231UPDATE t1 SET a = a + 1; 1232--error ER_TABLEACCESS_DENIED_ERROR 1233EXPLAIN UPDATE t1 SET a = a + 1; 1234--error ER_TABLEACCESS_DENIED_ERROR 1235ANALYZE UPDATE t1 SET a = a + 1; 1236 1237--error ER_TABLEACCESS_DENIED_ERROR 1238UPDATE t1, t2 SET t1.a = t1.a + 1 WHERE t1.a = t2.a; 1239--error ER_TABLEACCESS_DENIED_ERROR 1240EXPLAIN UPDATE t1, t2 SET t1.a = t1.a + 1 WHERE t1.a = t2.a; 1241--error ER_TABLEACCESS_DENIED_ERROR 1242ANALYZE UPDATE t1, t2 SET t1.a = t1.a + 1 WHERE t1.a = t2.a; 1243 1244DELETE FROM t1; 1245EXPLAIN DELETE FROM t1; 1246ANALYZE DELETE FROM t1; 1247 1248--error ER_COLUMNACCESS_DENIED_ERROR 1249DELETE FROM t1 WHERE a = 10; 1250--error ER_COLUMNACCESS_DENIED_ERROR 1251EXPLAIN DELETE FROM t1 WHERE a = 10; 1252--error ER_COLUMNACCESS_DENIED_ERROR 1253ANALYZE DELETE FROM t1 WHERE a = 10; 1254 1255# Unexpected errors due to MDEV-7043 (expected all three to succeed) 1256--error ER_TABLEACCESS_DENIED_ERROR 1257DELETE FROM t1 USING t1, t2; 1258--error ER_TABLEACCESS_DENIED_ERROR 1259EXPLAIN DELETE FROM t1 USING t1, t2; 1260--error ER_TABLEACCESS_DENIED_ERROR 1261ANALYZE DELETE FROM t1 USING t1, t2; 1262 1263# Unexpected error code due to MDEV-7043 1264#--error ER_COLUMNACCESS_DENIED_ERROR 1265--error ER_TABLEACCESS_DENIED_ERROR 1266DELETE FROM t1 USING t1, t2 WHERE t1.a = t2.a; 1267--error ER_TABLEACCESS_DENIED_ERROR 1268EXPLAIN DELETE FROM t1 USING t1, t2 WHERE t1.a = t2.a; 1269--error ER_TABLEACCESS_DENIED_ERROR 1270ANALYZE DELETE FROM t1 USING t1, t2 WHERE t1.a = t2.a; 1271 1272--error ER_TABLEACCESS_DENIED_ERROR 1273SELECT * FROM t1; 1274--error ER_TABLEACCESS_DENIED_ERROR 1275EXPLAIN SELECT * FROM t1; 1276--error ER_TABLEACCESS_DENIED_ERROR 1277ANALYZE SELECT * FROM t1; 1278 1279--error ER_TABLEACCESS_DENIED_ERROR 1280SELECT * FROM t1 WHERE a = 10; 1281--error ER_TABLEACCESS_DENIED_ERROR 1282EXPLAIN SELECT * FROM t1 WHERE a = 10; 1283--error ER_TABLEACCESS_DENIED_ERROR 1284ANALYZE SELECT * FROM t1 WHERE a = 10; 1285 1286--error ER_TABLEACCESS_DENIED_ERROR 1287SELECT * FROM t1 WHERE a IN ( SELECT a FROM t2 ); 1288--error ER_TABLEACCESS_DENIED_ERROR 1289EXPLAIN SELECT * FROM t1 WHERE a IN ( SELECT a FROM t2 ); 1290--error ER_TABLEACCESS_DENIED_ERROR 1291ANALYZE SELECT * FROM t1 WHERE a IN ( SELECT a FROM t2 ); 1292 1293--echo #------------------------------------------------------------------------ 1294--echo # I/R/U/D/S on the inner view 1295--echo # Expectation: Can run the queries, but not EXPLAIN or ANALYZE 1296--echo # because the query plan cannot be shown 1297--echo # (it would have revealed the structure of the table) 1298--echo #------------------------------------------------------------------------ 1299 1300 1301INSERT INTO v1 (a) VALUES (10); 1302--error ER_VIEW_NO_EXPLAIN 1303EXPLAIN INSERT INTO v1 (a) VALUES (10); 1304--error ER_VIEW_NO_EXPLAIN 1305ANALYZE INSERT INTO v1 (a) VALUES (10); 1306 1307INSERT INTO v1 SELECT * FROM t2; 1308--error ER_VIEW_NO_EXPLAIN 1309EXPLAIN INSERT INTO v1 SELECT * FROM t2; 1310--error ER_VIEW_NO_EXPLAIN 1311ANALYZE INSERT INTO v1 SELECT * FROM t2; 1312 1313REPLACE INTO v1 (a) VALUES (10); 1314--error ER_VIEW_NO_EXPLAIN 1315EXPLAIN REPLACE INTO v1 (a) VALUES (10); 1316--error ER_VIEW_NO_EXPLAIN 1317ANALYZE REPLACE INTO v1 (a) VALUES (10); 1318 1319REPLACE INTO v1 SELECT * FROM t2; 1320--error ER_VIEW_NO_EXPLAIN 1321EXPLAIN REPLACE INTO v1 SELECT * FROM t2; 1322--error ER_VIEW_NO_EXPLAIN 1323ANALYZE REPLACE INTO v1 SELECT * FROM t2; 1324 1325UPDATE v1 SET a = 10; 1326--error ER_VIEW_NO_EXPLAIN 1327EXPLAIN UPDATE v1 SET a = 10; 1328--error ER_VIEW_NO_EXPLAIN 1329ANALYZE UPDATE v1 SET a = 10; 1330 1331UPDATE v1 SET a = a + 1; 1332--error ER_VIEW_NO_EXPLAIN 1333EXPLAIN UPDATE v1 SET a = a + 1; 1334--error ER_VIEW_NO_EXPLAIN 1335ANALYZE UPDATE v1 SET a = a + 1; 1336 1337UPDATE v1, t2 SET v1.a = v1.a + 1 WHERE v1.a = t2.a; 1338--error ER_VIEW_NO_EXPLAIN 1339EXPLAIN UPDATE v1, t2 SET v1.a = v1.a + 1 WHERE v1.a = t2.a; 1340--error ER_VIEW_NO_EXPLAIN 1341ANALYZE UPDATE v1, t2 SET v1.a = v1.a + 1 WHERE v1.a = t2.a; 1342 1343DELETE FROM v1; 1344--error ER_VIEW_NO_EXPLAIN 1345EXPLAIN DELETE FROM v1; 1346--error ER_VIEW_NO_EXPLAIN 1347ANALYZE DELETE FROM v1; 1348 1349DELETE FROM v1 WHERE a = 10; 1350--error ER_VIEW_NO_EXPLAIN 1351EXPLAIN DELETE FROM v1 WHERE a = 10; 1352--error ER_VIEW_NO_EXPLAIN 1353ANALYZE DELETE FROM v1 WHERE a = 10; 1354 1355DELETE FROM v1 USING v1, t2; 1356--error ER_VIEW_NO_EXPLAIN 1357EXPLAIN DELETE FROM v1 USING v1, t2; 1358--error ER_VIEW_NO_EXPLAIN 1359ANALYZE DELETE FROM v1 USING v1, t2; 1360 1361DELETE FROM v1 USING v1, t2 WHERE v1.a = t2.a; 1362--error ER_VIEW_NO_EXPLAIN 1363EXPLAIN DELETE FROM v1 USING v1, t2 WHERE v1.a = t2.a; 1364--error ER_VIEW_NO_EXPLAIN 1365ANALYZE DELETE FROM v1 USING v1, t2 WHERE v1.a = t2.a; 1366 1367SELECT * FROM v1; 1368--error ER_VIEW_NO_EXPLAIN 1369EXPLAIN SELECT * FROM v1; 1370--error ER_VIEW_NO_EXPLAIN 1371ANALYZE SELECT * FROM v1; 1372 1373SELECT * FROM v1 WHERE a = 10; 1374--error ER_VIEW_NO_EXPLAIN 1375EXPLAIN SELECT * FROM v1 WHERE a = 10; 1376--error ER_VIEW_NO_EXPLAIN 1377ANALYZE SELECT * FROM v1 WHERE a = 10; 1378 1379SELECT * FROM v1 WHERE a IN ( SELECT a FROM t2 ); 1380--error ER_VIEW_NO_EXPLAIN 1381EXPLAIN SELECT * FROM v1 WHERE a IN ( SELECT a FROM t2 ); 1382--error ER_VIEW_NO_EXPLAIN 1383ANALYZE SELECT * FROM v1 WHERE a IN ( SELECT a FROM t2 ); 1384 1385 1386--echo #------------------------------------------------------------------------ 1387--echo # I/R/U/D/S on the outer view 1388--echo # Expectation: Can run the queries, but not EXPLAIN or ANALYZE 1389--echo # because the query plan cannot be shown 1390--echo # (it would have revealed the structure of the table) 1391--echo #------------------------------------------------------------------------ 1392 1393 1394INSERT INTO v2 (a) VALUES (10); 1395--error ER_VIEW_NO_EXPLAIN 1396EXPLAIN INSERT INTO v2 (a) VALUES (10); 1397--error ER_VIEW_NO_EXPLAIN 1398ANALYZE INSERT INTO v2 (a) VALUES (10); 1399 1400INSERT INTO v2 SELECT * FROM t2; 1401--error ER_VIEW_NO_EXPLAIN 1402EXPLAIN INSERT INTO v2 SELECT * FROM t2; 1403--error ER_VIEW_NO_EXPLAIN 1404ANALYZE INSERT INTO v2 SELECT * FROM t2; 1405 1406REPLACE INTO v2 (a) VALUES (10); 1407--error ER_VIEW_NO_EXPLAIN 1408EXPLAIN REPLACE INTO v2 (a) VALUES (10); 1409--error ER_VIEW_NO_EXPLAIN 1410ANALYZE REPLACE INTO v2 (a) VALUES (10); 1411 1412REPLACE INTO v2 SELECT * FROM t2; 1413--error ER_VIEW_NO_EXPLAIN 1414EXPLAIN REPLACE INTO v2 SELECT * FROM t2; 1415--error ER_VIEW_NO_EXPLAIN 1416ANALYZE REPLACE INTO v2 SELECT * FROM t2; 1417 1418UPDATE v2 SET a = 10; 1419--error ER_VIEW_NO_EXPLAIN 1420EXPLAIN UPDATE v2 SET a = 10; 1421--error ER_VIEW_NO_EXPLAIN 1422ANALYZE UPDATE v2 SET a = 10; 1423 1424UPDATE v2 SET a = a + 1; 1425--error ER_VIEW_NO_EXPLAIN 1426EXPLAIN UPDATE v2 SET a = a + 1; 1427--error ER_VIEW_NO_EXPLAIN 1428ANALYZE UPDATE v2 SET a = a + 1; 1429 1430UPDATE v2, t2 SET v2.a = v2.a + 1 WHERE v2.a = t2.a; 1431--error ER_VIEW_NO_EXPLAIN 1432EXPLAIN UPDATE v2, t2 SET v2.a = v2.a + 1 WHERE v2.a = t2.a; 1433--error ER_VIEW_NO_EXPLAIN 1434ANALYZE UPDATE v2, t2 SET v2.a = v2.a + 1 WHERE v2.a = t2.a; 1435 1436DELETE FROM v2; 1437--error ER_VIEW_NO_EXPLAIN 1438EXPLAIN DELETE FROM v2; 1439--error ER_VIEW_NO_EXPLAIN 1440ANALYZE DELETE FROM v2; 1441 1442DELETE FROM v2 WHERE a = 10; 1443--error ER_VIEW_NO_EXPLAIN 1444EXPLAIN DELETE FROM v2 WHERE a = 10; 1445--error ER_VIEW_NO_EXPLAIN 1446ANALYZE DELETE FROM v2 WHERE a = 10; 1447 1448DELETE FROM v2 USING v2, t2; 1449--error ER_VIEW_NO_EXPLAIN 1450EXPLAIN DELETE FROM v2 USING v2, t2; 1451--error ER_VIEW_NO_EXPLAIN 1452ANALYZE DELETE FROM v2 USING v2, t2; 1453 1454DELETE FROM v2 USING v2, t2 WHERE v2.a = t2.a; 1455--error ER_VIEW_NO_EXPLAIN 1456EXPLAIN DELETE FROM v2 USING v2, t2 WHERE v2.a = t2.a; 1457--error ER_VIEW_NO_EXPLAIN 1458ANALYZE DELETE FROM v2 USING v2, t2 WHERE v2.a = t2.a; 1459 1460SELECT * FROM v2; 1461--error ER_VIEW_NO_EXPLAIN 1462EXPLAIN SELECT * FROM v2; 1463--error ER_VIEW_NO_EXPLAIN 1464ANALYZE SELECT * FROM v2; 1465 1466SELECT * FROM v2 WHERE a = 10; 1467--error ER_VIEW_NO_EXPLAIN 1468EXPLAIN SELECT * FROM v2 WHERE a = 10; 1469--error ER_VIEW_NO_EXPLAIN 1470ANALYZE SELECT * FROM v2 WHERE a = 10; 1471 1472SELECT * FROM v2 WHERE a IN ( SELECT a FROM t2 ); 1473--error ER_VIEW_NO_EXPLAIN 1474EXPLAIN SELECT * FROM v2 WHERE a IN ( SELECT a FROM t2 ); 1475--error ER_VIEW_NO_EXPLAIN 1476ANALYZE SELECT * FROM v2 WHERE a IN ( SELECT a FROM t2 ); 1477 1478 1479--echo 1480--echo #======================================================================== 1481--echo # Test: Grant DELETE, SELECT(a) on the table 1482--echo #======================================================================== 1483--echo 1484 1485connection default; 1486REVOKE ALL PRIVILEGES ON privtest_db.t1 FROM 'privtest'@'localhost'; 1487GRANT SELECT(a), DELETE ON privtest_db.t1 TO 'privtest'@'localhost'; 1488connection con1; 1489 1490--echo #------------------------------------------------------------------------ 1491--echo # I/R/U/D/S on the table 1492--echo # Expectation: Can run DELETE, EXPLAIN DELETE, ANALYZE DELETE 1493--echo #------------------------------------------------------------------------ 1494 1495 1496--error ER_TABLEACCESS_DENIED_ERROR 1497INSERT INTO t1 (a) VALUES (10); 1498--error ER_TABLEACCESS_DENIED_ERROR 1499EXPLAIN INSERT INTO t1 (a) VALUES (10); 1500--error ER_TABLEACCESS_DENIED_ERROR 1501ANALYZE INSERT INTO t1 (a) VALUES (10); 1502 1503--error ER_TABLEACCESS_DENIED_ERROR 1504INSERT INTO t1 SELECT * FROM t2; 1505--error ER_TABLEACCESS_DENIED_ERROR 1506EXPLAIN INSERT INTO t1 SELECT * FROM t2; 1507--error ER_TABLEACCESS_DENIED_ERROR 1508ANALYZE INSERT INTO t1 SELECT * FROM t2; 1509 1510--error ER_TABLEACCESS_DENIED_ERROR 1511REPLACE INTO t1 (a) VALUES (10); 1512--error ER_TABLEACCESS_DENIED_ERROR 1513EXPLAIN REPLACE INTO t1 (a) VALUES (10); 1514--error ER_TABLEACCESS_DENIED_ERROR 1515ANALYZE REPLACE INTO t1 (a) VALUES (10); 1516 1517--error ER_TABLEACCESS_DENIED_ERROR 1518REPLACE INTO t1 SELECT * FROM t2; 1519--error ER_TABLEACCESS_DENIED_ERROR 1520EXPLAIN REPLACE INTO t1 SELECT * FROM t2; 1521--error ER_TABLEACCESS_DENIED_ERROR 1522ANALYZE REPLACE INTO t1 SELECT * FROM t2; 1523 1524--error ER_TABLEACCESS_DENIED_ERROR 1525UPDATE t1 SET a = 10; 1526--error ER_TABLEACCESS_DENIED_ERROR 1527EXPLAIN UPDATE t1 SET a = 10; 1528--error ER_TABLEACCESS_DENIED_ERROR 1529ANALYZE UPDATE t1 SET a = 10; 1530 1531--error ER_TABLEACCESS_DENIED_ERROR 1532UPDATE t1 SET a = a + 1; 1533--error ER_TABLEACCESS_DENIED_ERROR 1534EXPLAIN UPDATE t1 SET a = a + 1; 1535--error ER_TABLEACCESS_DENIED_ERROR 1536ANALYZE UPDATE t1 SET a = a + 1; 1537 1538--error ER_TABLEACCESS_DENIED_ERROR 1539UPDATE t1, t2 SET t1.a = t1.a + 1 WHERE t1.a = t2.a; 1540--error ER_TABLEACCESS_DENIED_ERROR 1541EXPLAIN UPDATE t1, t2 SET t1.a = t1.a + 1 WHERE t1.a = t2.a; 1542--error ER_TABLEACCESS_DENIED_ERROR 1543ANALYZE UPDATE t1, t2 SET t1.a = t1.a + 1 WHERE t1.a = t2.a; 1544 1545DELETE FROM t1; 1546EXPLAIN DELETE FROM t1; 1547ANALYZE DELETE FROM t1; 1548 1549DELETE FROM t1 WHERE a = 10; 1550EXPLAIN DELETE FROM t1 WHERE a = 10; 1551ANALYZE DELETE FROM t1 WHERE a = 10; 1552 1553DELETE FROM t1 USING t1, t2; 1554EXPLAIN DELETE FROM t1 USING t1, t2; 1555ANALYZE DELETE FROM t1 USING t1, t2; 1556 1557DELETE FROM t1 USING t1, t2 WHERE t1.a = t2.a; 1558EXPLAIN DELETE FROM t1 USING t1, t2 WHERE t1.a = t2.a; 1559ANALYZE DELETE FROM t1 USING t1, t2 WHERE t1.a = t2.a; 1560 1561--error ER_TABLEACCESS_DENIED_ERROR 1562SELECT * FROM t1; 1563--error ER_TABLEACCESS_DENIED_ERROR 1564EXPLAIN SELECT * FROM t1; 1565--error ER_TABLEACCESS_DENIED_ERROR 1566ANALYZE SELECT * FROM t1; 1567 1568--error ER_TABLEACCESS_DENIED_ERROR 1569SELECT * FROM t1 WHERE a = 10; 1570--error ER_TABLEACCESS_DENIED_ERROR 1571EXPLAIN SELECT * FROM t1 WHERE a = 10; 1572--error ER_TABLEACCESS_DENIED_ERROR 1573ANALYZE SELECT * FROM t1 WHERE a = 10; 1574 1575--error ER_TABLEACCESS_DENIED_ERROR 1576SELECT * FROM t1 WHERE a IN ( SELECT a FROM t2 ); 1577--error ER_TABLEACCESS_DENIED_ERROR 1578EXPLAIN SELECT * FROM t1 WHERE a IN ( SELECT a FROM t2 ); 1579--error ER_TABLEACCESS_DENIED_ERROR 1580ANALYZE SELECT * FROM t1 WHERE a IN ( SELECT a FROM t2 ); 1581 1582--echo #------------------------------------------------------------------------ 1583--echo # I/R/U/D/S on the inner view 1584--echo # Expectation: Can run everything: SELECT access to the column `a` 1585--echo # in the underlying table is enough to show EXPLAIN 1586--echo # (that's how it works now) 1587--echo #------------------------------------------------------------------------ 1588 1589 1590INSERT INTO v1 (a) VALUES (10); 1591EXPLAIN INSERT INTO v1 (a) VALUES (10); 1592ANALYZE INSERT INTO v1 (a) VALUES (10); 1593 1594INSERT INTO v1 SELECT * FROM t2; 1595EXPLAIN INSERT INTO v1 SELECT * FROM t2; 1596ANALYZE INSERT INTO v1 SELECT * FROM t2; 1597 1598REPLACE INTO v1 (a) VALUES (10); 1599EXPLAIN REPLACE INTO v1 (a) VALUES (10); 1600ANALYZE REPLACE INTO v1 (a) VALUES (10); 1601 1602REPLACE INTO v1 SELECT * FROM t2; 1603EXPLAIN REPLACE INTO v1 SELECT * FROM t2; 1604ANALYZE REPLACE INTO v1 SELECT * FROM t2; 1605 1606UPDATE v1 SET a = 10; 1607EXPLAIN UPDATE v1 SET a = 10; 1608ANALYZE UPDATE v1 SET a = 10; 1609 1610UPDATE v1 SET a = a + 1; 1611EXPLAIN UPDATE v1 SET a = a + 1; 1612ANALYZE UPDATE v1 SET a = a + 1; 1613 1614UPDATE v1, t2 SET v1.a = v1.a + 1 WHERE v1.a = t2.a; 1615EXPLAIN UPDATE v1, t2 SET v1.a = v1.a + 1 WHERE v1.a = t2.a; 1616ANALYZE UPDATE v1, t2 SET v1.a = v1.a + 1 WHERE v1.a = t2.a; 1617 1618DELETE FROM v1; 1619EXPLAIN DELETE FROM v1; 1620ANALYZE DELETE FROM v1; 1621 1622DELETE FROM v1 WHERE a = 10; 1623EXPLAIN DELETE FROM v1 WHERE a = 10; 1624ANALYZE DELETE FROM v1 WHERE a = 10; 1625 1626DELETE FROM v1 USING v1, t2; 1627EXPLAIN DELETE FROM v1 USING v1, t2; 1628ANALYZE DELETE FROM v1 USING v1, t2; 1629 1630DELETE FROM v1 USING v1, t2 WHERE v1.a = t2.a; 1631EXPLAIN DELETE FROM v1 USING v1, t2 WHERE v1.a = t2.a; 1632ANALYZE DELETE FROM v1 USING v1, t2 WHERE v1.a = t2.a; 1633 1634SELECT * FROM v1; 1635EXPLAIN SELECT * FROM v1; 1636ANALYZE SELECT * FROM v1; 1637 1638SELECT * FROM v1 WHERE a = 10; 1639EXPLAIN SELECT * FROM v1 WHERE a = 10; 1640ANALYZE SELECT * FROM v1 WHERE a = 10; 1641 1642SELECT * FROM v1 WHERE a IN ( SELECT a FROM t2 ); 1643EXPLAIN SELECT * FROM v1 WHERE a IN ( SELECT a FROM t2 ); 1644ANALYZE SELECT * FROM v1 WHERE a IN ( SELECT a FROM t2 ); 1645 1646 1647--echo #------------------------------------------------------------------------ 1648--echo # I/R/U/D/S on the outer view 1649--echo # Expectation: Can run everything: SELECT access to the column `a` 1650--echo # in the underlying table is enough to show EXPLAIN 1651--echo # (that's how it works now) 1652--echo #------------------------------------------------------------------------ 1653 1654 1655INSERT INTO v2 (a) VALUES (10); 1656EXPLAIN INSERT INTO v2 (a) VALUES (10); 1657ANALYZE INSERT INTO v2 (a) VALUES (10); 1658 1659INSERT INTO v2 SELECT * FROM t2; 1660EXPLAIN INSERT INTO v2 SELECT * FROM t2; 1661ANALYZE INSERT INTO v2 SELECT * FROM t2; 1662 1663REPLACE INTO v2 (a) VALUES (10); 1664EXPLAIN REPLACE INTO v2 (a) VALUES (10); 1665ANALYZE REPLACE INTO v2 (a) VALUES (10); 1666 1667REPLACE INTO v2 SELECT * FROM t2; 1668EXPLAIN REPLACE INTO v2 SELECT * FROM t2; 1669ANALYZE REPLACE INTO v2 SELECT * FROM t2; 1670 1671UPDATE v2 SET a = 10; 1672EXPLAIN UPDATE v2 SET a = 10; 1673ANALYZE UPDATE v2 SET a = 10; 1674 1675UPDATE v2 SET a = a + 1; 1676EXPLAIN UPDATE v2 SET a = a + 1; 1677ANALYZE UPDATE v2 SET a = a + 1; 1678 1679UPDATE v2, t2 SET v2.a = v2.a + 1 WHERE v2.a = t2.a; 1680EXPLAIN UPDATE v2, t2 SET v2.a = v2.a + 1 WHERE v2.a = t2.a; 1681ANALYZE UPDATE v2, t2 SET v2.a = v2.a + 1 WHERE v2.a = t2.a; 1682 1683DELETE FROM v2; 1684EXPLAIN DELETE FROM v2; 1685ANALYZE DELETE FROM v2; 1686 1687DELETE FROM v2 WHERE a = 10; 1688EXPLAIN DELETE FROM v2 WHERE a = 10; 1689ANALYZE DELETE FROM v2 WHERE a = 10; 1690 1691DELETE FROM v2 USING v2, t2; 1692EXPLAIN DELETE FROM v2 USING v2, t2; 1693ANALYZE DELETE FROM v2 USING v2, t2; 1694 1695DELETE FROM v2 USING v2, t2 WHERE v2.a = t2.a; 1696EXPLAIN DELETE FROM v2 USING v2, t2 WHERE v2.a = t2.a; 1697ANALYZE DELETE FROM v2 USING v2, t2 WHERE v2.a = t2.a; 1698 1699SELECT * FROM v2; 1700EXPLAIN SELECT * FROM v2; 1701ANALYZE SELECT * FROM v2; 1702 1703SELECT * FROM v2 WHERE a = 10; 1704EXPLAIN SELECT * FROM v2 WHERE a = 10; 1705ANALYZE SELECT * FROM v2 WHERE a = 10; 1706 1707SELECT * FROM v2 WHERE a IN ( SELECT a FROM t2 ); 1708EXPLAIN SELECT * FROM v2 WHERE a IN ( SELECT a FROM t2 ); 1709ANALYZE SELECT * FROM v2 WHERE a IN ( SELECT a FROM t2 ); 1710 1711--echo 1712--echo #======================================================================== 1713--echo # Test: Grant SELECT, INSERT, UPDATE, DELETE on the table 1714--echo #======================================================================== 1715--echo 1716 1717 1718connection default; 1719REVOKE ALL PRIVILEGES ON privtest_db.t1 FROM 'privtest'@'localhost'; 1720GRANT SELECT, INSERT, UPDATE, DELETE ON privtest_db.t1 TO 'privtest'@'localhost'; 1721connection con1; 1722 1723 1724--echo #------------------------------------------------------------------------ 1725--echo # I/R/U/D/S on the table 1726--echo # Expectation: Can run everything 1727--echo #------------------------------------------------------------------------ 1728 1729INSERT INTO t1 (a) VALUES (10); 1730EXPLAIN INSERT INTO t1 (a) VALUES (10); 1731ANALYZE INSERT INTO t1 (a) VALUES (10); 1732 1733INSERT INTO t1 SELECT * FROM t2; 1734EXPLAIN INSERT INTO t1 SELECT * FROM t2; 1735ANALYZE INSERT INTO t1 SELECT * FROM t2; 1736 1737REPLACE INTO t1 (a) VALUES (10); 1738EXPLAIN REPLACE INTO t1 (a) VALUES (10); 1739ANALYZE REPLACE INTO t1 (a) VALUES (10); 1740 1741REPLACE INTO t1 SELECT * FROM t2; 1742EXPLAIN REPLACE INTO t1 SELECT * FROM t2; 1743ANALYZE REPLACE INTO t1 SELECT * FROM t2; 1744 1745UPDATE t1 SET a = 10; 1746EXPLAIN UPDATE t1 SET a = 10; 1747ANALYZE UPDATE t1 SET a = 10; 1748 1749UPDATE t1 SET a = a + 1; 1750EXPLAIN UPDATE t1 SET a = a + 1; 1751ANALYZE UPDATE t1 SET a = a + 1; 1752 1753UPDATE t1, t2 SET t1.a = t1.a + 1 WHERE t1.a = t2.a; 1754EXPLAIN UPDATE t1, t2 SET t1.a = t1.a + 1 WHERE t1.a = t2.a; 1755ANALYZE UPDATE t1, t2 SET t1.a = t1.a + 1 WHERE t1.a = t2.a; 1756 1757DELETE FROM t1; 1758EXPLAIN DELETE FROM t1; 1759ANALYZE DELETE FROM t1; 1760 1761DELETE FROM t1 WHERE a = 10; 1762EXPLAIN DELETE FROM t1 WHERE a = 10; 1763ANALYZE DELETE FROM t1 WHERE a = 10; 1764 1765DELETE FROM t1 USING t1, t2; 1766EXPLAIN DELETE FROM t1 USING t1, t2; 1767ANALYZE DELETE FROM t1 USING t1, t2; 1768 1769DELETE FROM t1 USING t1, t2 WHERE t1.a = t2.a; 1770EXPLAIN DELETE FROM t1 USING t1, t2 WHERE t1.a = t2.a; 1771ANALYZE DELETE FROM t1 USING t1, t2 WHERE t1.a = t2.a; 1772 1773SELECT * FROM t1; 1774EXPLAIN SELECT * FROM t1; 1775ANALYZE SELECT * FROM t1; 1776 1777SELECT * FROM t1 WHERE a = 10; 1778EXPLAIN SELECT * FROM t1 WHERE a = 10; 1779ANALYZE SELECT * FROM t1 WHERE a = 10; 1780 1781SELECT * FROM t1 WHERE a IN ( SELECT a FROM t2 ); 1782EXPLAIN SELECT * FROM t1 WHERE a IN ( SELECT a FROM t2 ); 1783ANALYZE SELECT * FROM t1 WHERE a IN ( SELECT a FROM t2 ); 1784 1785 1786--echo #------------------------------------------------------------------------ 1787--echo # I/R/U/D/S on the inner view 1788--echo # Expectation: Can run everything 1789--echo #------------------------------------------------------------------------ 1790 1791INSERT INTO v1 (a) VALUES (10); 1792EXPLAIN INSERT INTO v1 (a) VALUES (10); 1793ANALYZE INSERT INTO v1 (a) VALUES (10); 1794 1795INSERT INTO v1 SELECT * FROM t2; 1796EXPLAIN INSERT INTO v1 SELECT * FROM t2; 1797ANALYZE INSERT INTO v1 SELECT * FROM t2; 1798 1799REPLACE INTO v1 (a) VALUES (10); 1800EXPLAIN REPLACE INTO v1 (a) VALUES (10); 1801ANALYZE REPLACE INTO v1 (a) VALUES (10); 1802 1803REPLACE INTO v1 SELECT * FROM t2; 1804EXPLAIN REPLACE INTO v1 SELECT * FROM t2; 1805ANALYZE REPLACE INTO v1 SELECT * FROM t2; 1806 1807UPDATE v1 SET a = 10; 1808EXPLAIN UPDATE v1 SET a = 10; 1809ANALYZE UPDATE v1 SET a = 10; 1810 1811UPDATE v1 SET a = a + 1; 1812EXPLAIN UPDATE v1 SET a = a + 1; 1813ANALYZE UPDATE v1 SET a = a + 1; 1814 1815UPDATE v1, t2 SET v1.a = v1.a + 1 WHERE v1.a = t2.a; 1816EXPLAIN UPDATE v1, t2 SET v1.a = v1.a + 1 WHERE v1.a = t2.a; 1817ANALYZE UPDATE v1, t2 SET v1.a = v1.a + 1 WHERE v1.a = t2.a; 1818 1819DELETE FROM v1; 1820EXPLAIN DELETE FROM v1; 1821ANALYZE DELETE FROM v1; 1822 1823DELETE FROM v1 WHERE a = 10; 1824EXPLAIN DELETE FROM v1 WHERE a = 10; 1825ANALYZE DELETE FROM v1 WHERE a = 10; 1826 1827DELETE FROM v1 USING v1, t2; 1828EXPLAIN DELETE FROM v1 USING v1, t2; 1829ANALYZE DELETE FROM v1 USING v1, t2; 1830 1831DELETE FROM v1 USING v1, t2 WHERE v1.a = t2.a; 1832EXPLAIN DELETE FROM v1 USING v1, t2 WHERE v1.a = t2.a; 1833ANALYZE DELETE FROM v1 USING v1, t2 WHERE v1.a = t2.a; 1834 1835SELECT * FROM v1; 1836EXPLAIN SELECT * FROM v1; 1837ANALYZE SELECT * FROM v1; 1838 1839SELECT * FROM v1 WHERE a = 10; 1840EXPLAIN SELECT * FROM v1 WHERE a = 10; 1841ANALYZE SELECT * FROM v1 WHERE a = 10; 1842 1843SELECT * FROM v1 WHERE a IN ( SELECT a FROM t2 ); 1844EXPLAIN SELECT * FROM v1 WHERE a IN ( SELECT a FROM t2 ); 1845ANALYZE SELECT * FROM v1 WHERE a IN ( SELECT a FROM t2 ); 1846 1847--echo #------------------------------------------------------------------------ 1848--echo # I/R/U/D/S on the outer view 1849--echo # Expectation: Can run everything 1850--echo #------------------------------------------------------------------------ 1851--echo # Note: some queries are commented due to MDEV-7034 1852 1853INSERT INTO v2 (a) VALUES (10); 1854EXPLAIN INSERT INTO v2 (a) VALUES (10); 1855ANALYZE INSERT INTO v2 (a) VALUES (10); 1856 1857INSERT INTO v2 SELECT * FROM t2; 1858EXPLAIN INSERT INTO v2 SELECT * FROM t2; 1859ANALYZE INSERT INTO v2 SELECT * FROM t2; 1860 1861REPLACE INTO v2 (a) VALUES (10); 1862EXPLAIN REPLACE INTO v2 (a) VALUES (10); 1863ANALYZE REPLACE INTO v2 (a) VALUES (10); 1864 1865REPLACE INTO v2 SELECT * FROM t2; 1866EXPLAIN REPLACE INTO v2 SELECT * FROM t2; 1867ANALYZE REPLACE INTO v2 SELECT * FROM t2; 1868 1869UPDATE v2 SET a = 10; 1870EXPLAIN UPDATE v2 SET a = 10; 1871ANALYZE UPDATE v2 SET a = 10; 1872 1873UPDATE v2 SET a = a + 1; 1874EXPLAIN UPDATE v2 SET a = a + 1; 1875ANALYZE UPDATE v2 SET a = a + 1; 1876 1877UPDATE v2, t2 SET v2.a = v2.a + 1 WHERE v2.a = t2.a; 1878EXPLAIN UPDATE v2, t2 SET v2.a = v2.a + 1 WHERE v2.a = t2.a; 1879ANALYZE UPDATE v2, t2 SET v2.a = v2.a + 1 WHERE v2.a = t2.a; 1880 1881DELETE FROM v2; 1882EXPLAIN DELETE FROM v2; 1883ANALYZE DELETE FROM v2; 1884 1885DELETE FROM v2 WHERE a = 10; 1886EXPLAIN DELETE FROM v2 WHERE a = 10; 1887ANALYZE DELETE FROM v2 WHERE a = 10; 1888 1889DELETE FROM v2 USING v2, t2; 1890# Commented due to MDEV-7034 1891# EXPLAIN DELETE FROM v2 USING v2, t2; 1892ANALYZE DELETE FROM v2 USING v2, t2; 1893 1894DELETE FROM v2 USING v2, t2 WHERE v2.a = t2.a; 1895# Commented due to MDEV-7034 1896# EXPLAIN DELETE FROM v2 USING v2, t2 WHERE v2.a = t2.a; 1897ANALYZE DELETE FROM v2 USING v2, t2 WHERE v2.a = t2.a; 1898 1899SELECT * FROM v2; 1900EXPLAIN SELECT * FROM v2; 1901ANALYZE SELECT * FROM v2; 1902 1903SELECT * FROM v2 WHERE a = 10; 1904EXPLAIN SELECT * FROM v2 WHERE a = 10; 1905ANALYZE SELECT * FROM v2 WHERE a = 10; 1906 1907SELECT * FROM v2 WHERE a IN ( SELECT a FROM t2 ); 1908EXPLAIN SELECT * FROM v2 WHERE a IN ( SELECT a FROM t2 ); 1909ANALYZE SELECT * FROM v2 WHERE a IN ( SELECT a FROM t2 ); 1910 1911 1912--echo 1913--echo ######################################################################### 1914--echo # Inner view permission tests 1915--echo # (we modify permissions on the inner view, keeping ALL the rest) 1916--echo ######################################################################### 1917--echo 1918 1919 1920--echo 1921--echo #======================================================================== 1922--echo # Test: No permissions on the inner view 1923--echo #======================================================================== 1924--echo 1925 1926connection default; 1927GRANT ALL ON privtest_db.t1 TO 'privtest'@'localhost'; 1928GRANT ALL ON privtest_db.v2 TO 'privtest'@'localhost'; 1929REVOKE ALL PRIVILEGES ON privtest_db.v1 FROM 'privtest'@'localhost'; 1930connection con1; 1931 1932 1933--echo #------------------------------------------------------------------------ 1934--echo # I/R/U/D/S on the inner view 1935--echo # Expectation: Cannot run anything 1936--echo #------------------------------------------------------------------------ 1937 1938 1939--error ER_TABLEACCESS_DENIED_ERROR 1940INSERT INTO v1 (a) VALUES (10); 1941--error ER_TABLEACCESS_DENIED_ERROR 1942EXPLAIN INSERT INTO v1 (a) VALUES (10); 1943--error ER_TABLEACCESS_DENIED_ERROR 1944ANALYZE INSERT INTO v1 (a) VALUES (10); 1945 1946--error ER_TABLEACCESS_DENIED_ERROR 1947INSERT INTO v1 SELECT * FROM t2; 1948--error ER_TABLEACCESS_DENIED_ERROR 1949EXPLAIN INSERT INTO v1 SELECT * FROM t2; 1950--error ER_TABLEACCESS_DENIED_ERROR 1951ANALYZE INSERT INTO v1 SELECT * FROM t2; 1952 1953--error ER_TABLEACCESS_DENIED_ERROR 1954REPLACE INTO v1 (a) VALUES (10); 1955--error ER_TABLEACCESS_DENIED_ERROR 1956EXPLAIN REPLACE INTO v1 (a) VALUES (10); 1957--error ER_TABLEACCESS_DENIED_ERROR 1958ANALYZE REPLACE INTO v1 (a) VALUES (10); 1959 1960--error ER_TABLEACCESS_DENIED_ERROR 1961REPLACE INTO v1 SELECT * FROM t2; 1962--error ER_TABLEACCESS_DENIED_ERROR 1963EXPLAIN REPLACE INTO v1 SELECT * FROM t2; 1964--error ER_TABLEACCESS_DENIED_ERROR 1965ANALYZE REPLACE INTO v1 SELECT * FROM t2; 1966 1967--error ER_TABLEACCESS_DENIED_ERROR 1968UPDATE v1 SET a = 10; 1969--error ER_TABLEACCESS_DENIED_ERROR 1970EXPLAIN UPDATE v1 SET a = 10; 1971--error ER_TABLEACCESS_DENIED_ERROR 1972ANALYZE UPDATE v1 SET a = 10; 1973 1974--error ER_TABLEACCESS_DENIED_ERROR 1975UPDATE v1 SET a = a + 1; 1976--error ER_TABLEACCESS_DENIED_ERROR 1977EXPLAIN UPDATE v1 SET a = a + 1; 1978--error ER_TABLEACCESS_DENIED_ERROR 1979ANALYZE UPDATE v1 SET a = a + 1; 1980 1981--error ER_TABLEACCESS_DENIED_ERROR 1982UPDATE v1, t2 SET v1.a = v1.a + 1 WHERE v1.a = t2.a; 1983--error ER_TABLEACCESS_DENIED_ERROR 1984EXPLAIN UPDATE v1, t2 SET v1.a = v1.a + 1 WHERE v1.a = t2.a; 1985--error ER_TABLEACCESS_DENIED_ERROR 1986ANALYZE UPDATE v1, t2 SET v1.a = v1.a + 1 WHERE v1.a = t2.a; 1987 1988--error ER_TABLEACCESS_DENIED_ERROR 1989DELETE FROM v1; 1990--error ER_TABLEACCESS_DENIED_ERROR 1991EXPLAIN DELETE FROM v1; 1992--error ER_TABLEACCESS_DENIED_ERROR 1993ANALYZE DELETE FROM v1; 1994 1995--error ER_TABLEACCESS_DENIED_ERROR 1996DELETE FROM v1 WHERE a = 10; 1997--error ER_TABLEACCESS_DENIED_ERROR 1998EXPLAIN DELETE FROM v1 WHERE a = 10; 1999--error ER_TABLEACCESS_DENIED_ERROR 2000ANALYZE DELETE FROM v1 WHERE a = 10; 2001 2002--error ER_TABLEACCESS_DENIED_ERROR 2003DELETE FROM v1 USING v1, t2; 2004--error ER_TABLEACCESS_DENIED_ERROR 2005EXPLAIN DELETE FROM v1 USING v1, t2; 2006--error ER_TABLEACCESS_DENIED_ERROR 2007ANALYZE DELETE FROM v1 USING v1, t2; 2008 2009--error ER_TABLEACCESS_DENIED_ERROR 2010DELETE FROM v1 USING v1, t2 WHERE v1.a = t2.a; 2011--error ER_TABLEACCESS_DENIED_ERROR 2012EXPLAIN DELETE FROM v1 USING v1, t2 WHERE v1.a = t2.a; 2013--error ER_TABLEACCESS_DENIED_ERROR 2014ANALYZE DELETE FROM v1 USING v1, t2 WHERE v1.a = t2.a; 2015 2016--error ER_TABLEACCESS_DENIED_ERROR 2017SELECT * FROM v1; 2018--error ER_TABLEACCESS_DENIED_ERROR 2019EXPLAIN SELECT * FROM v1; 2020--error ER_TABLEACCESS_DENIED_ERROR 2021ANALYZE SELECT * FROM v1; 2022 2023--error ER_TABLEACCESS_DENIED_ERROR 2024SELECT * FROM v1 WHERE a = 10; 2025--error ER_TABLEACCESS_DENIED_ERROR 2026EXPLAIN SELECT * FROM v1 WHERE a = 10; 2027--error ER_TABLEACCESS_DENIED_ERROR 2028ANALYZE SELECT * FROM v1 WHERE a = 10; 2029 2030--error ER_TABLEACCESS_DENIED_ERROR 2031SELECT * FROM v1 WHERE a IN ( SELECT a FROM t2 ); 2032--error ER_TABLEACCESS_DENIED_ERROR 2033EXPLAIN SELECT * FROM v1 WHERE a IN ( SELECT a FROM t2 ); 2034--error ER_TABLEACCESS_DENIED_ERROR 2035ANALYZE SELECT * FROM v1 WHERE a IN ( SELECT a FROM t2 ); 2036 2037 2038--echo #------------------------------------------------------------------------ 2039--echo # I/R/U/D/S on the outer view 2040--echo # Expectation: Can run the queries, but not EXPLAIN or ANALYZE 2041--echo # because the query plan cannot be shown 2042--echo # (it could have revealed the structure of the inner view) 2043--echo #------------------------------------------------------------------------ 2044 2045 2046INSERT INTO v2 (a) VALUES (10); 2047--error ER_VIEW_NO_EXPLAIN 2048EXPLAIN INSERT INTO v2 (a) VALUES (10); 2049--error ER_VIEW_NO_EXPLAIN 2050ANALYZE INSERT INTO v2 (a) VALUES (10); 2051 2052INSERT INTO v2 SELECT * FROM t2; 2053--error ER_VIEW_NO_EXPLAIN 2054EXPLAIN INSERT INTO v2 SELECT * FROM t2; 2055--error ER_VIEW_NO_EXPLAIN 2056ANALYZE INSERT INTO v2 SELECT * FROM t2; 2057 2058REPLACE INTO v2 (a) VALUES (10); 2059--error ER_VIEW_NO_EXPLAIN 2060EXPLAIN REPLACE INTO v2 (a) VALUES (10); 2061--error ER_VIEW_NO_EXPLAIN 2062ANALYZE REPLACE INTO v2 (a) VALUES (10); 2063 2064REPLACE INTO v2 SELECT * FROM t2; 2065--error ER_VIEW_NO_EXPLAIN 2066EXPLAIN REPLACE INTO v2 SELECT * FROM t2; 2067--error ER_VIEW_NO_EXPLAIN 2068ANALYZE REPLACE INTO v2 SELECT * FROM t2; 2069 2070UPDATE v2 SET a = 10; 2071--error ER_VIEW_NO_EXPLAIN 2072EXPLAIN UPDATE v2 SET a = 10; 2073--error ER_VIEW_NO_EXPLAIN 2074ANALYZE UPDATE v2 SET a = 10; 2075 2076UPDATE v2 SET a = a + 1; 2077--error ER_VIEW_NO_EXPLAIN 2078EXPLAIN UPDATE v2 SET a = a + 1; 2079--error ER_VIEW_NO_EXPLAIN 2080ANALYZE UPDATE v2 SET a = a + 1; 2081 2082UPDATE v2, t2 SET v2.a = v2.a + 1 WHERE v2.a = t2.a; 2083--error ER_VIEW_NO_EXPLAIN 2084EXPLAIN UPDATE v2, t2 SET v2.a = v2.a + 1 WHERE v2.a = t2.a; 2085--error ER_VIEW_NO_EXPLAIN 2086ANALYZE UPDATE v2, t2 SET v2.a = v2.a + 1 WHERE v2.a = t2.a; 2087 2088DELETE FROM v2; 2089--error ER_VIEW_NO_EXPLAIN 2090EXPLAIN DELETE FROM v2; 2091--error ER_VIEW_NO_EXPLAIN 2092ANALYZE DELETE FROM v2; 2093 2094DELETE FROM v2 WHERE a = 10; 2095--error ER_VIEW_NO_EXPLAIN 2096EXPLAIN DELETE FROM v2 WHERE a = 10; 2097--error ER_VIEW_NO_EXPLAIN 2098ANALYZE DELETE FROM v2 WHERE a = 10; 2099 2100DELETE FROM v2 USING v2, t2; 2101--error ER_VIEW_NO_EXPLAIN 2102EXPLAIN DELETE FROM v2 USING v2, t2; 2103--error ER_VIEW_NO_EXPLAIN 2104ANALYZE DELETE FROM v2 USING v2, t2; 2105 2106DELETE FROM v2 USING v2, t2 WHERE v2.a = t2.a; 2107--error ER_VIEW_NO_EXPLAIN 2108EXPLAIN DELETE FROM v2 USING v2, t2 WHERE v2.a = t2.a; 2109--error ER_VIEW_NO_EXPLAIN 2110ANALYZE DELETE FROM v2 USING v2, t2 WHERE v2.a = t2.a; 2111 2112SELECT * FROM v2; 2113--error ER_VIEW_NO_EXPLAIN 2114EXPLAIN SELECT * FROM v2; 2115--error ER_VIEW_NO_EXPLAIN 2116ANALYZE SELECT * FROM v2; 2117 2118SELECT * FROM v2 WHERE a = 10; 2119--error ER_VIEW_NO_EXPLAIN 2120EXPLAIN SELECT * FROM v2 WHERE a = 10; 2121--error ER_VIEW_NO_EXPLAIN 2122ANALYZE SELECT * FROM v2 WHERE a = 10; 2123 2124SELECT * FROM v2 WHERE a IN ( SELECT a FROM t2 ); 2125--error ER_VIEW_NO_EXPLAIN 2126EXPLAIN SELECT * FROM v2 WHERE a IN ( SELECT a FROM t2 ); 2127--error ER_VIEW_NO_EXPLAIN 2128ANALYZE SELECT * FROM v2 WHERE a IN ( SELECT a FROM t2 ); 2129 2130 2131--echo 2132--echo #======================================================================== 2133--echo # Test: Grant SHOW VIEW on the inner view 2134--echo #======================================================================== 2135--echo 2136 2137connection default; 2138GRANT SHOW VIEW ON privtest_db.v1 TO 'privtest'@'localhost'; 2139connection con1; 2140 2141--echo #------------------------------------------------------------------------ 2142--echo # I/R/U/D/S on the inner view 2143--echo # Expectation: Cannot run anything 2144--echo #------------------------------------------------------------------------ 2145 2146--error ER_TABLEACCESS_DENIED_ERROR 2147INSERT INTO v1 (a) VALUES (10); 2148--error ER_TABLEACCESS_DENIED_ERROR 2149EXPLAIN INSERT INTO v1 (a) VALUES (10); 2150--error ER_TABLEACCESS_DENIED_ERROR 2151ANALYZE INSERT INTO v1 (a) VALUES (10); 2152 2153--error ER_TABLEACCESS_DENIED_ERROR 2154INSERT INTO v1 SELECT * FROM t2; 2155--error ER_TABLEACCESS_DENIED_ERROR 2156EXPLAIN INSERT INTO v1 SELECT * FROM t2; 2157--error ER_TABLEACCESS_DENIED_ERROR 2158ANALYZE INSERT INTO v1 SELECT * FROM t2; 2159 2160--error ER_TABLEACCESS_DENIED_ERROR 2161REPLACE INTO v1 (a) VALUES (10); 2162--error ER_TABLEACCESS_DENIED_ERROR 2163EXPLAIN REPLACE INTO v1 (a) VALUES (10); 2164--error ER_TABLEACCESS_DENIED_ERROR 2165ANALYZE REPLACE INTO v1 (a) VALUES (10); 2166 2167--error ER_TABLEACCESS_DENIED_ERROR 2168REPLACE INTO v1 SELECT * FROM t2; 2169--error ER_TABLEACCESS_DENIED_ERROR 2170EXPLAIN REPLACE INTO v1 SELECT * FROM t2; 2171--error ER_TABLEACCESS_DENIED_ERROR 2172ANALYZE REPLACE INTO v1 SELECT * FROM t2; 2173 2174--error ER_TABLEACCESS_DENIED_ERROR 2175UPDATE v1 SET a = 10; 2176--error ER_TABLEACCESS_DENIED_ERROR 2177EXPLAIN UPDATE v1 SET a = 10; 2178--error ER_TABLEACCESS_DENIED_ERROR 2179ANALYZE UPDATE v1 SET a = 10; 2180 2181--error ER_TABLEACCESS_DENIED_ERROR 2182UPDATE v1 SET a = a + 1; 2183--error ER_TABLEACCESS_DENIED_ERROR 2184EXPLAIN UPDATE v1 SET a = a + 1; 2185--error ER_TABLEACCESS_DENIED_ERROR 2186ANALYZE UPDATE v1 SET a = a + 1; 2187 2188--error ER_TABLEACCESS_DENIED_ERROR 2189UPDATE v1, t2 SET v1.a = v1.a + 1 WHERE v1.a = t2.a; 2190--error ER_TABLEACCESS_DENIED_ERROR 2191EXPLAIN UPDATE v1, t2 SET v1.a = v1.a + 1 WHERE v1.a = t2.a; 2192--error ER_TABLEACCESS_DENIED_ERROR 2193ANALYZE UPDATE v1, t2 SET v1.a = v1.a + 1 WHERE v1.a = t2.a; 2194 2195--error ER_TABLEACCESS_DENIED_ERROR 2196DELETE FROM v1; 2197--error ER_TABLEACCESS_DENIED_ERROR 2198EXPLAIN DELETE FROM v1; 2199--error ER_TABLEACCESS_DENIED_ERROR 2200ANALYZE DELETE FROM v1; 2201 2202--error ER_TABLEACCESS_DENIED_ERROR 2203DELETE FROM v1 WHERE a = 10; 2204--error ER_TABLEACCESS_DENIED_ERROR 2205EXPLAIN DELETE FROM v1 WHERE a = 10; 2206--error ER_TABLEACCESS_DENIED_ERROR 2207ANALYZE DELETE FROM v1 WHERE a = 10; 2208 2209--error ER_TABLEACCESS_DENIED_ERROR 2210DELETE FROM v1 USING v1, t2; 2211--error ER_TABLEACCESS_DENIED_ERROR 2212EXPLAIN DELETE FROM v1 USING v1, t2; 2213--error ER_TABLEACCESS_DENIED_ERROR 2214ANALYZE DELETE FROM v1 USING v1, t2; 2215 2216--error ER_TABLEACCESS_DENIED_ERROR 2217DELETE FROM v1 USING v1, t2 WHERE v1.a = t2.a; 2218--error ER_TABLEACCESS_DENIED_ERROR 2219EXPLAIN DELETE FROM v1 USING v1, t2 WHERE v1.a = t2.a; 2220--error ER_TABLEACCESS_DENIED_ERROR 2221ANALYZE DELETE FROM v1 USING v1, t2 WHERE v1.a = t2.a; 2222 2223--error ER_TABLEACCESS_DENIED_ERROR 2224SELECT * FROM v1; 2225--error ER_TABLEACCESS_DENIED_ERROR 2226EXPLAIN SELECT * FROM v1; 2227--error ER_TABLEACCESS_DENIED_ERROR 2228ANALYZE SELECT * FROM v1; 2229 2230--error ER_TABLEACCESS_DENIED_ERROR 2231SELECT * FROM v1 WHERE a = 10; 2232--error ER_TABLEACCESS_DENIED_ERROR 2233EXPLAIN SELECT * FROM v1 WHERE a = 10; 2234--error ER_TABLEACCESS_DENIED_ERROR 2235ANALYZE SELECT * FROM v1 WHERE a = 10; 2236 2237--error ER_TABLEACCESS_DENIED_ERROR 2238SELECT * FROM v1 WHERE a IN ( SELECT a FROM t2 ); 2239--error ER_TABLEACCESS_DENIED_ERROR 2240EXPLAIN SELECT * FROM v1 WHERE a IN ( SELECT a FROM t2 ); 2241--error ER_TABLEACCESS_DENIED_ERROR 2242ANALYZE SELECT * FROM v1 WHERE a IN ( SELECT a FROM t2 ); 2243 2244 2245--echo #------------------------------------------------------------------------ 2246--echo # I/R/U/D/S on the outer view 2247--echo # Expectation: Can run the queries, but not EXPLAIN or ANALYZE 2248--echo # because the query plan cannot be shown 2249--echo # (that's how it works now) 2250--echo #------------------------------------------------------------------------ 2251 2252 2253INSERT INTO v2 (a) VALUES (10); 2254--error ER_VIEW_NO_EXPLAIN 2255EXPLAIN INSERT INTO v2 (a) VALUES (10); 2256--error ER_VIEW_NO_EXPLAIN 2257ANALYZE INSERT INTO v2 (a) VALUES (10); 2258 2259INSERT INTO v2 SELECT * FROM t2; 2260--error ER_VIEW_NO_EXPLAIN 2261EXPLAIN INSERT INTO v2 SELECT * FROM t2; 2262--error ER_VIEW_NO_EXPLAIN 2263ANALYZE INSERT INTO v2 SELECT * FROM t2; 2264 2265REPLACE INTO v2 (a) VALUES (10); 2266--error ER_VIEW_NO_EXPLAIN 2267EXPLAIN REPLACE INTO v2 (a) VALUES (10); 2268--error ER_VIEW_NO_EXPLAIN 2269ANALYZE REPLACE INTO v2 (a) VALUES (10); 2270 2271REPLACE INTO v2 SELECT * FROM t2; 2272--error ER_VIEW_NO_EXPLAIN 2273EXPLAIN REPLACE INTO v2 SELECT * FROM t2; 2274--error ER_VIEW_NO_EXPLAIN 2275ANALYZE REPLACE INTO v2 SELECT * FROM t2; 2276 2277UPDATE v2 SET a = 10; 2278--error ER_VIEW_NO_EXPLAIN 2279EXPLAIN UPDATE v2 SET a = 10; 2280--error ER_VIEW_NO_EXPLAIN 2281ANALYZE UPDATE v2 SET a = 10; 2282 2283UPDATE v2 SET a = a + 1; 2284--error ER_VIEW_NO_EXPLAIN 2285EXPLAIN UPDATE v2 SET a = a + 1; 2286--error ER_VIEW_NO_EXPLAIN 2287ANALYZE UPDATE v2 SET a = a + 1; 2288 2289UPDATE v2, t2 SET v2.a = v2.a + 1 WHERE v2.a = t2.a; 2290--error ER_VIEW_NO_EXPLAIN 2291EXPLAIN UPDATE v2, t2 SET v2.a = v2.a + 1 WHERE v2.a = t2.a; 2292--error ER_VIEW_NO_EXPLAIN 2293ANALYZE UPDATE v2, t2 SET v2.a = v2.a + 1 WHERE v2.a = t2.a; 2294 2295DELETE FROM v2; 2296--error ER_VIEW_NO_EXPLAIN 2297EXPLAIN DELETE FROM v2; 2298--error ER_VIEW_NO_EXPLAIN 2299ANALYZE DELETE FROM v2; 2300 2301DELETE FROM v2 WHERE a = 10; 2302--error ER_VIEW_NO_EXPLAIN 2303EXPLAIN DELETE FROM v2 WHERE a = 10; 2304--error ER_VIEW_NO_EXPLAIN 2305ANALYZE DELETE FROM v2 WHERE a = 10; 2306 2307DELETE FROM v2 USING v2, t2; 2308--error ER_VIEW_NO_EXPLAIN 2309EXPLAIN DELETE FROM v2 USING v2, t2; 2310--error ER_VIEW_NO_EXPLAIN 2311ANALYZE DELETE FROM v2 USING v2, t2; 2312 2313DELETE FROM v2 USING v2, t2 WHERE v2.a = t2.a; 2314--error ER_VIEW_NO_EXPLAIN 2315EXPLAIN DELETE FROM v2 USING v2, t2 WHERE v2.a = t2.a; 2316--error ER_VIEW_NO_EXPLAIN 2317ANALYZE DELETE FROM v2 USING v2, t2 WHERE v2.a = t2.a; 2318 2319SELECT * FROM v2; 2320--error ER_VIEW_NO_EXPLAIN 2321EXPLAIN SELECT * FROM v2; 2322--error ER_VIEW_NO_EXPLAIN 2323ANALYZE SELECT * FROM v2; 2324 2325SELECT * FROM v2 WHERE a = 10; 2326--error ER_VIEW_NO_EXPLAIN 2327EXPLAIN SELECT * FROM v2 WHERE a = 10; 2328--error ER_VIEW_NO_EXPLAIN 2329ANALYZE SELECT * FROM v2 WHERE a = 10; 2330 2331SELECT * FROM v2 WHERE a IN ( SELECT a FROM t2 ); 2332--error ER_VIEW_NO_EXPLAIN 2333EXPLAIN SELECT * FROM v2 WHERE a IN ( SELECT a FROM t2 ); 2334--error ER_VIEW_NO_EXPLAIN 2335ANALYZE SELECT * FROM v2 WHERE a IN ( SELECT a FROM t2 ); 2336 2337 2338--echo 2339--echo #======================================================================== 2340--echo # Test: Grant SELECT on the inner view 2341--echo #======================================================================== 2342--echo 2343 2344connection default; 2345REVOKE ALL PRIVILEGES ON privtest_db.v1 FROM 'privtest'@'localhost'; 2346GRANT SELECT ON privtest_db.v1 TO 'privtest'@'localhost'; 2347connection con1; 2348 2349 2350--echo #------------------------------------------------------------------------ 2351--echo # I/R/U/D/S on the inner view 2352--echo # Expectation: Can run SELECT, but not EXPLAIN SELECT or ANALYZE SELECT 2353--echo # because the query plan cannot be shown 2354--echo # (it could have revealed the structure of the view) 2355--echo #------------------------------------------------------------------------ 2356 2357 2358--error ER_TABLEACCESS_DENIED_ERROR 2359INSERT INTO v1 (a) VALUES (10); 2360--error ER_TABLEACCESS_DENIED_ERROR 2361EXPLAIN INSERT INTO v1 (a) VALUES (10); 2362--error ER_TABLEACCESS_DENIED_ERROR 2363ANALYZE INSERT INTO v1 (a) VALUES (10); 2364 2365--error ER_TABLEACCESS_DENIED_ERROR 2366INSERT INTO v1 SELECT * FROM t2; 2367--error ER_TABLEACCESS_DENIED_ERROR 2368EXPLAIN INSERT INTO v1 SELECT * FROM t2; 2369--error ER_TABLEACCESS_DENIED_ERROR 2370ANALYZE INSERT INTO v1 SELECT * FROM t2; 2371 2372--error ER_TABLEACCESS_DENIED_ERROR 2373REPLACE INTO v1 (a) VALUES (10); 2374--error ER_TABLEACCESS_DENIED_ERROR 2375EXPLAIN REPLACE INTO v1 (a) VALUES (10); 2376--error ER_TABLEACCESS_DENIED_ERROR 2377ANALYZE REPLACE INTO v1 (a) VALUES (10); 2378 2379--error ER_TABLEACCESS_DENIED_ERROR 2380REPLACE INTO v1 SELECT * FROM t2; 2381--error ER_TABLEACCESS_DENIED_ERROR 2382EXPLAIN REPLACE INTO v1 SELECT * FROM t2; 2383--error ER_TABLEACCESS_DENIED_ERROR 2384ANALYZE REPLACE INTO v1 SELECT * FROM t2; 2385 2386--error ER_TABLEACCESS_DENIED_ERROR 2387UPDATE v1 SET a = 10; 2388--error ER_TABLEACCESS_DENIED_ERROR 2389EXPLAIN UPDATE v1 SET a = 10; 2390--error ER_TABLEACCESS_DENIED_ERROR 2391ANALYZE UPDATE v1 SET a = 10; 2392 2393--error ER_TABLEACCESS_DENIED_ERROR 2394UPDATE v1 SET a = a + 1; 2395--error ER_TABLEACCESS_DENIED_ERROR 2396EXPLAIN UPDATE v1 SET a = a + 1; 2397--error ER_TABLEACCESS_DENIED_ERROR 2398ANALYZE UPDATE v1 SET a = a + 1; 2399 2400--error ER_TABLEACCESS_DENIED_ERROR 2401UPDATE v1, t2 SET v1.a = v1.a + 1 WHERE v1.a = t2.a; 2402# Strange error code due to MDEV-7033 2403# --error ER_TABLEACCESS_DENIED_ERROR 2404--error ER_VIEW_NO_EXPLAIN 2405EXPLAIN UPDATE v1, t2 SET v1.a = v1.a + 1 WHERE v1.a = t2.a; 2406--error ER_VIEW_NO_EXPLAIN 2407ANALYZE UPDATE v1, t2 SET v1.a = v1.a + 1 WHERE v1.a = t2.a; 2408 2409--error ER_TABLEACCESS_DENIED_ERROR 2410DELETE FROM v1; 2411--error ER_TABLEACCESS_DENIED_ERROR 2412EXPLAIN DELETE FROM v1; 2413--error ER_TABLEACCESS_DENIED_ERROR 2414ANALYZE DELETE FROM v1; 2415 2416--error ER_TABLEACCESS_DENIED_ERROR 2417DELETE FROM v1 WHERE a = 10; 2418--error ER_TABLEACCESS_DENIED_ERROR 2419EXPLAIN DELETE FROM v1 WHERE a = 10; 2420--error ER_TABLEACCESS_DENIED_ERROR 2421ANALYZE DELETE FROM v1 WHERE a = 10; 2422 2423--error ER_TABLEACCESS_DENIED_ERROR 2424DELETE FROM v1 USING v1, t2; 2425--error ER_TABLEACCESS_DENIED_ERROR 2426EXPLAIN DELETE FROM v1 USING v1, t2; 2427--error ER_TABLEACCESS_DENIED_ERROR 2428ANALYZE DELETE FROM v1 USING v1, t2; 2429 2430--error ER_TABLEACCESS_DENIED_ERROR 2431DELETE FROM v1 USING v1, t2 WHERE v1.a = t2.a; 2432--error ER_TABLEACCESS_DENIED_ERROR 2433EXPLAIN DELETE FROM v1 USING v1, t2 WHERE v1.a = t2.a; 2434--error ER_TABLEACCESS_DENIED_ERROR 2435ANALYZE DELETE FROM v1 USING v1, t2 WHERE v1.a = t2.a; 2436 2437SELECT * FROM v1; 2438--error ER_VIEW_NO_EXPLAIN 2439EXPLAIN SELECT * FROM v1; 2440--error ER_VIEW_NO_EXPLAIN 2441ANALYZE SELECT * FROM v1; 2442 2443SELECT * FROM v1 WHERE a = 10; 2444--error ER_VIEW_NO_EXPLAIN 2445EXPLAIN SELECT * FROM v1 WHERE a = 10; 2446--error ER_VIEW_NO_EXPLAIN 2447ANALYZE SELECT * FROM v1 WHERE a = 10; 2448 2449SELECT * FROM v1 WHERE a IN ( SELECT a FROM t2 ); 2450--error ER_VIEW_NO_EXPLAIN 2451EXPLAIN SELECT * FROM v1 WHERE a IN ( SELECT a FROM t2 ); 2452--error ER_VIEW_NO_EXPLAIN 2453ANALYZE SELECT * FROM v1 WHERE a IN ( SELECT a FROM t2 ); 2454 2455 2456--echo #------------------------------------------------------------------------ 2457--echo # I/R/U/D/S on the outer view 2458--echo # Expectation: Can run the queries, but not EXPLAIN or ANALYZE 2459--echo # because the query plan cannot be shown 2460--echo # (it could have revealed the structure of the inner view) 2461--echo #------------------------------------------------------------------------ 2462 2463 2464INSERT INTO v2 (a) VALUES (10); 2465--error ER_VIEW_NO_EXPLAIN 2466EXPLAIN INSERT INTO v2 (a) VALUES (10); 2467--error ER_VIEW_NO_EXPLAIN 2468ANALYZE INSERT INTO v2 (a) VALUES (10); 2469 2470INSERT INTO v2 SELECT * FROM t2; 2471--error ER_VIEW_NO_EXPLAIN 2472EXPLAIN INSERT INTO v2 SELECT * FROM t2; 2473--error ER_VIEW_NO_EXPLAIN 2474ANALYZE INSERT INTO v2 SELECT * FROM t2; 2475 2476REPLACE INTO v2 (a) VALUES (10); 2477--error ER_VIEW_NO_EXPLAIN 2478EXPLAIN REPLACE INTO v2 (a) VALUES (10); 2479--error ER_VIEW_NO_EXPLAIN 2480ANALYZE REPLACE INTO v2 (a) VALUES (10); 2481 2482REPLACE INTO v2 SELECT * FROM t2; 2483--error ER_VIEW_NO_EXPLAIN 2484EXPLAIN REPLACE INTO v2 SELECT * FROM t2; 2485--error ER_VIEW_NO_EXPLAIN 2486ANALYZE REPLACE INTO v2 SELECT * FROM t2; 2487 2488UPDATE v2 SET a = 10; 2489--error ER_VIEW_NO_EXPLAIN 2490EXPLAIN UPDATE v2 SET a = 10; 2491--error ER_VIEW_NO_EXPLAIN 2492ANALYZE UPDATE v2 SET a = 10; 2493 2494UPDATE v2 SET a = a + 1; 2495--error ER_VIEW_NO_EXPLAIN 2496EXPLAIN UPDATE v2 SET a = a + 1; 2497--error ER_VIEW_NO_EXPLAIN 2498ANALYZE UPDATE v2 SET a = a + 1; 2499 2500UPDATE v2, t2 SET v2.a = v2.a + 1 WHERE v2.a = t2.a; 2501--error ER_VIEW_NO_EXPLAIN 2502EXPLAIN UPDATE v2, t2 SET v2.a = v2.a + 1 WHERE v2.a = t2.a; 2503--error ER_VIEW_NO_EXPLAIN 2504ANALYZE UPDATE v2, t2 SET v2.a = v2.a + 1 WHERE v2.a = t2.a; 2505 2506DELETE FROM v2; 2507--error ER_VIEW_NO_EXPLAIN 2508EXPLAIN DELETE FROM v2; 2509--error ER_VIEW_NO_EXPLAIN 2510ANALYZE DELETE FROM v2; 2511 2512DELETE FROM v2 WHERE a = 10; 2513--error ER_VIEW_NO_EXPLAIN 2514EXPLAIN DELETE FROM v2 WHERE a = 10; 2515--error ER_VIEW_NO_EXPLAIN 2516ANALYZE DELETE FROM v2 WHERE a = 10; 2517 2518DELETE FROM v2 USING v2, t2; 2519--error ER_VIEW_NO_EXPLAIN 2520EXPLAIN DELETE FROM v2 USING v2, t2; 2521--error ER_VIEW_NO_EXPLAIN 2522ANALYZE DELETE FROM v2 USING v2, t2; 2523 2524DELETE FROM v2 USING v2, t2 WHERE v2.a = t2.a; 2525--error ER_VIEW_NO_EXPLAIN 2526EXPLAIN DELETE FROM v2 USING v2, t2 WHERE v2.a = t2.a; 2527--error ER_VIEW_NO_EXPLAIN 2528ANALYZE DELETE FROM v2 USING v2, t2 WHERE v2.a = t2.a; 2529 2530SELECT * FROM v2; 2531--error ER_VIEW_NO_EXPLAIN 2532EXPLAIN SELECT * FROM v2; 2533--error ER_VIEW_NO_EXPLAIN 2534ANALYZE SELECT * FROM v2; 2535 2536SELECT * FROM v2 WHERE a = 10; 2537--error ER_VIEW_NO_EXPLAIN 2538EXPLAIN SELECT * FROM v2 WHERE a = 10; 2539--error ER_VIEW_NO_EXPLAIN 2540ANALYZE SELECT * FROM v2 WHERE a = 10; 2541 2542SELECT * FROM v2 WHERE a IN ( SELECT a FROM t2 ); 2543--error ER_VIEW_NO_EXPLAIN 2544EXPLAIN SELECT * FROM v2 WHERE a IN ( SELECT a FROM t2 ); 2545--error ER_VIEW_NO_EXPLAIN 2546ANALYZE SELECT * FROM v2 WHERE a IN ( SELECT a FROM t2 ); 2547 2548--echo 2549--echo #======================================================================== 2550--echo # Test: Grant SELECT, SHOW VIEW on the inner view 2551--echo #======================================================================== 2552--echo 2553 2554--echo 2555--echo #------------------------------------------------------------------------ 2556--echo # Test: SELECT + SHOW VIEW privileges allow ANALYZE SELECT for the inner 2557--echo # view, and ANALYZE <anything> for the outer view 2558--echo #------------------------------------------------------------------------ 2559--echo 2560 2561connection default; 2562REVOKE ALL PRIVILEGES ON privtest_db.v1 FROM 'privtest'@'localhost'; 2563GRANT SELECT, SHOW VIEW ON privtest_db.v1 TO 'privtest'@'localhost'; 2564connection con1; 2565 2566--echo #------------------------------------------------------------------------ 2567--echo # I/R/U/D/S on the inner view 2568--echo # Expectation: Can run SELECT, EXPLAIN SELECT, ANALYZE SELECT 2569--echo #------------------------------------------------------------------------ 2570 2571--error ER_TABLEACCESS_DENIED_ERROR 2572INSERT INTO v1 (a) VALUES (10); 2573--error ER_TABLEACCESS_DENIED_ERROR 2574EXPLAIN INSERT INTO v1 (a) VALUES (10); 2575--error ER_TABLEACCESS_DENIED_ERROR 2576ANALYZE INSERT INTO v1 (a) VALUES (10); 2577 2578--error ER_TABLEACCESS_DENIED_ERROR 2579INSERT INTO v1 SELECT * FROM t2; 2580--error ER_TABLEACCESS_DENIED_ERROR 2581EXPLAIN INSERT INTO v1 SELECT * FROM t2; 2582--error ER_TABLEACCESS_DENIED_ERROR 2583ANALYZE INSERT INTO v1 SELECT * FROM t2; 2584 2585--error ER_TABLEACCESS_DENIED_ERROR 2586REPLACE INTO v1 (a) VALUES (10); 2587--error ER_TABLEACCESS_DENIED_ERROR 2588EXPLAIN REPLACE INTO v1 (a) VALUES (10); 2589--error ER_TABLEACCESS_DENIED_ERROR 2590ANALYZE REPLACE INTO v1 (a) VALUES (10); 2591 2592--error ER_TABLEACCESS_DENIED_ERROR 2593REPLACE INTO v1 SELECT * FROM t2; 2594--error ER_TABLEACCESS_DENIED_ERROR 2595EXPLAIN REPLACE INTO v1 SELECT * FROM t2; 2596--error ER_TABLEACCESS_DENIED_ERROR 2597ANALYZE REPLACE INTO v1 SELECT * FROM t2; 2598 2599--error ER_TABLEACCESS_DENIED_ERROR 2600UPDATE v1 SET a = 10; 2601--error ER_TABLEACCESS_DENIED_ERROR 2602EXPLAIN UPDATE v1 SET a = 10; 2603--error ER_TABLEACCESS_DENIED_ERROR 2604ANALYZE UPDATE v1 SET a = 10; 2605 2606--error ER_TABLEACCESS_DENIED_ERROR 2607UPDATE v1 SET a = a + 1; 2608--error ER_TABLEACCESS_DENIED_ERROR 2609EXPLAIN UPDATE v1 SET a = a + 1; 2610--error ER_TABLEACCESS_DENIED_ERROR 2611ANALYZE UPDATE v1 SET a = a + 1; 2612 2613--error ER_TABLEACCESS_DENIED_ERROR 2614UPDATE v1, t2 SET v1.a = v1.a + 1 WHERE v1.a = t2.a; 2615--error ER_TABLEACCESS_DENIED_ERROR 2616EXPLAIN UPDATE v1, t2 SET v1.a = v1.a + 1 WHERE v1.a = t2.a; 2617--error ER_TABLEACCESS_DENIED_ERROR 2618ANALYZE UPDATE v1, t2 SET v1.a = v1.a + 1 WHERE v1.a = t2.a; 2619 2620--error ER_TABLEACCESS_DENIED_ERROR 2621DELETE FROM v1; 2622--error ER_TABLEACCESS_DENIED_ERROR 2623EXPLAIN DELETE FROM v1; 2624--error ER_TABLEACCESS_DENIED_ERROR 2625ANALYZE DELETE FROM v1; 2626 2627--error ER_TABLEACCESS_DENIED_ERROR 2628DELETE FROM v1 WHERE a = 10; 2629--error ER_TABLEACCESS_DENIED_ERROR 2630EXPLAIN DELETE FROM v1 WHERE a = 10; 2631--error ER_TABLEACCESS_DENIED_ERROR 2632ANALYZE DELETE FROM v1 WHERE a = 10; 2633 2634--error ER_TABLEACCESS_DENIED_ERROR 2635DELETE FROM v1 USING v1, t2; 2636--error ER_TABLEACCESS_DENIED_ERROR 2637EXPLAIN DELETE FROM v1 USING v1, t2; 2638--error ER_TABLEACCESS_DENIED_ERROR 2639ANALYZE DELETE FROM v1 USING v1, t2; 2640 2641--error ER_TABLEACCESS_DENIED_ERROR 2642DELETE FROM v1 USING v1, t2 WHERE v1.a = t2.a; 2643--error ER_TABLEACCESS_DENIED_ERROR 2644EXPLAIN DELETE FROM v1 USING v1, t2 WHERE v1.a = t2.a; 2645--error ER_TABLEACCESS_DENIED_ERROR 2646ANALYZE DELETE FROM v1 USING v1, t2 WHERE v1.a = t2.a; 2647 2648SELECT * FROM v1; 2649EXPLAIN SELECT * FROM v1; 2650ANALYZE SELECT * FROM v1; 2651 2652SELECT * FROM v1 WHERE a = 10; 2653EXPLAIN SELECT * FROM v1 WHERE a = 10; 2654ANALYZE SELECT * FROM v1 WHERE a = 10; 2655 2656SELECT * FROM v1 WHERE a IN ( SELECT a FROM t2 ); 2657EXPLAIN SELECT * FROM v1 WHERE a IN ( SELECT a FROM t2 ); 2658ANALYZE SELECT * FROM v1 WHERE a IN ( SELECT a FROM t2 ); 2659 2660 2661--echo #------------------------------------------------------------------------ 2662--echo # I/R/U/D/S on the outer view 2663--echo # Expectation: Can run everything 2664--echo #------------------------------------------------------------------------ 2665 2666INSERT INTO v2 (a) VALUES (10); 2667EXPLAIN INSERT INTO v2 (a) VALUES (10); 2668ANALYZE INSERT INTO v2 (a) VALUES (10); 2669 2670INSERT INTO v2 SELECT * FROM t2; 2671EXPLAIN INSERT INTO v2 SELECT * FROM t2; 2672ANALYZE INSERT INTO v2 SELECT * FROM t2; 2673 2674REPLACE INTO v2 (a) VALUES (10); 2675EXPLAIN REPLACE INTO v2 (a) VALUES (10); 2676ANALYZE REPLACE INTO v2 (a) VALUES (10); 2677 2678REPLACE INTO v2 SELECT * FROM t2; 2679EXPLAIN REPLACE INTO v2 SELECT * FROM t2; 2680ANALYZE REPLACE INTO v2 SELECT * FROM t2; 2681 2682UPDATE v2 SET a = 10; 2683EXPLAIN UPDATE v2 SET a = 10; 2684ANALYZE UPDATE v2 SET a = 10; 2685 2686UPDATE v2 SET a = a + 1; 2687EXPLAIN UPDATE v2 SET a = a + 1; 2688ANALYZE UPDATE v2 SET a = a + 1; 2689 2690UPDATE v2, t2 SET v2.a = v2.a + 1 WHERE v2.a = t2.a; 2691EXPLAIN UPDATE v2, t2 SET v2.a = v2.a + 1 WHERE v2.a = t2.a; 2692ANALYZE UPDATE v2, t2 SET v2.a = v2.a + 1 WHERE v2.a = t2.a; 2693 2694DELETE FROM v2; 2695EXPLAIN DELETE FROM v2; 2696ANALYZE DELETE FROM v2; 2697 2698DELETE FROM v2 WHERE a = 10; 2699EXPLAIN DELETE FROM v2 WHERE a = 10; 2700ANALYZE DELETE FROM v2 WHERE a = 10; 2701 2702DELETE FROM v2 USING v2, t2; 2703# Commented due to MDEV-7034 2704# EXPLAIN DELETE FROM v2 USING v2, t2; 2705ANALYZE DELETE FROM v2 USING v2, t2; 2706 2707DELETE FROM v2 USING v2, t2 WHERE v2.a = t2.a; 2708# Commented due to MDEV-7034 2709# EXPLAIN DELETE FROM v2 USING v2, t2 WHERE v2.a = t2.a; 2710ANALYZE DELETE FROM v2 USING v2, t2 WHERE v2.a = t2.a; 2711 2712SELECT * FROM v2; 2713EXPLAIN SELECT * FROM v2; 2714ANALYZE SELECT * FROM v2; 2715 2716SELECT * FROM v2 WHERE a = 10; 2717EXPLAIN SELECT * FROM v2 WHERE a = 10; 2718ANALYZE SELECT * FROM v2 WHERE a = 10; 2719 2720SELECT * FROM v2 WHERE a IN ( SELECT a FROM t2 ); 2721EXPLAIN SELECT * FROM v2 WHERE a IN ( SELECT a FROM t2 ); 2722ANALYZE SELECT * FROM v2 WHERE a IN ( SELECT a FROM t2 ); 2723 2724 2725--echo 2726--echo #======================================================================== 2727--echo # Test: Grant INSERT on the inner view 2728--echo #======================================================================== 2729--echo 2730 2731connection default; 2732REVOKE ALL PRIVILEGES ON privtest_db.v1 FROM 'privtest'@'localhost'; 2733GRANT INSERT ON privtest_db.v1 TO 'privtest'@'localhost'; 2734connection con1; 2735 2736 2737--echo #------------------------------------------------------------------------ 2738--echo # I/R/U/D/S on the inner view 2739--echo # Expectation: Can run INSERT, but not EXPLAIN INSERT or ANALYZE INSERT 2740--echo # because the query plan cannot be shown 2741--echo # (it could have revealed the structure of the view) 2742--echo #------------------------------------------------------------------------ 2743 2744 2745INSERT INTO v1 (a) VALUES (10); 2746--error ER_VIEW_NO_EXPLAIN 2747EXPLAIN INSERT INTO v1 (a) VALUES (10); 2748--error ER_VIEW_NO_EXPLAIN 2749ANALYZE INSERT INTO v1 (a) VALUES (10); 2750 2751INSERT INTO v1 SELECT * FROM t2; 2752--error ER_VIEW_NO_EXPLAIN 2753EXPLAIN INSERT INTO v1 SELECT * FROM t2; 2754--error ER_VIEW_NO_EXPLAIN 2755ANALYZE INSERT INTO v1 SELECT * FROM t2; 2756 2757--error ER_TABLEACCESS_DENIED_ERROR 2758REPLACE INTO v1 (a) VALUES (10); 2759--error ER_TABLEACCESS_DENIED_ERROR 2760EXPLAIN REPLACE INTO v1 (a) VALUES (10); 2761--error ER_TABLEACCESS_DENIED_ERROR 2762ANALYZE REPLACE INTO v1 (a) VALUES (10); 2763 2764--error ER_TABLEACCESS_DENIED_ERROR 2765REPLACE INTO v1 SELECT * FROM t2; 2766--error ER_TABLEACCESS_DENIED_ERROR 2767EXPLAIN REPLACE INTO v1 SELECT * FROM t2; 2768--error ER_TABLEACCESS_DENIED_ERROR 2769ANALYZE REPLACE INTO v1 SELECT * FROM t2; 2770 2771--error ER_TABLEACCESS_DENIED_ERROR 2772UPDATE v1 SET a = 10; 2773--error ER_TABLEACCESS_DENIED_ERROR 2774EXPLAIN UPDATE v1 SET a = 10; 2775--error ER_TABLEACCESS_DENIED_ERROR 2776ANALYZE UPDATE v1 SET a = 10; 2777 2778--error ER_TABLEACCESS_DENIED_ERROR 2779UPDATE v1 SET a = a + 1; 2780--error ER_TABLEACCESS_DENIED_ERROR 2781EXPLAIN UPDATE v1 SET a = a + 1; 2782--error ER_TABLEACCESS_DENIED_ERROR 2783ANALYZE UPDATE v1 SET a = a + 1; 2784 2785--error ER_TABLEACCESS_DENIED_ERROR 2786UPDATE v1, t2 SET v1.a = v1.a + 1 WHERE v1.a = t2.a; 2787--error ER_TABLEACCESS_DENIED_ERROR 2788EXPLAIN UPDATE v1, t2 SET v1.a = v1.a + 1 WHERE v1.a = t2.a; 2789--error ER_TABLEACCESS_DENIED_ERROR 2790ANALYZE UPDATE v1, t2 SET v1.a = v1.a + 1 WHERE v1.a = t2.a; 2791 2792--error ER_TABLEACCESS_DENIED_ERROR 2793DELETE FROM v1; 2794--error ER_TABLEACCESS_DENIED_ERROR 2795EXPLAIN DELETE FROM v1; 2796--error ER_TABLEACCESS_DENIED_ERROR 2797ANALYZE DELETE FROM v1; 2798 2799--error ER_TABLEACCESS_DENIED_ERROR 2800DELETE FROM v1 WHERE a = 10; 2801--error ER_TABLEACCESS_DENIED_ERROR 2802EXPLAIN DELETE FROM v1 WHERE a = 10; 2803--error ER_TABLEACCESS_DENIED_ERROR 2804ANALYZE DELETE FROM v1 WHERE a = 10; 2805 2806--error ER_TABLEACCESS_DENIED_ERROR 2807DELETE FROM v1 USING v1, t2; 2808--error ER_TABLEACCESS_DENIED_ERROR 2809EXPLAIN DELETE FROM v1 USING v1, t2; 2810--error ER_TABLEACCESS_DENIED_ERROR 2811ANALYZE DELETE FROM v1 USING v1, t2; 2812 2813--error ER_TABLEACCESS_DENIED_ERROR 2814DELETE FROM v1 USING v1, t2 WHERE v1.a = t2.a; 2815--error ER_TABLEACCESS_DENIED_ERROR 2816EXPLAIN DELETE FROM v1 USING v1, t2 WHERE v1.a = t2.a; 2817--error ER_TABLEACCESS_DENIED_ERROR 2818ANALYZE DELETE FROM v1 USING v1, t2 WHERE v1.a = t2.a; 2819 2820--error ER_TABLEACCESS_DENIED_ERROR 2821SELECT * FROM v1; 2822--error ER_TABLEACCESS_DENIED_ERROR 2823EXPLAIN SELECT * FROM v1; 2824--error ER_TABLEACCESS_DENIED_ERROR 2825ANALYZE SELECT * FROM v1; 2826 2827--error ER_TABLEACCESS_DENIED_ERROR 2828SELECT * FROM v1 WHERE a = 10; 2829--error ER_TABLEACCESS_DENIED_ERROR 2830EXPLAIN SELECT * FROM v1 WHERE a = 10; 2831--error ER_TABLEACCESS_DENIED_ERROR 2832ANALYZE SELECT * FROM v1 WHERE a = 10; 2833 2834--error ER_TABLEACCESS_DENIED_ERROR 2835SELECT * FROM v1 WHERE a IN ( SELECT a FROM t2 ); 2836--error ER_TABLEACCESS_DENIED_ERROR 2837EXPLAIN SELECT * FROM v1 WHERE a IN ( SELECT a FROM t2 ); 2838--error ER_TABLEACCESS_DENIED_ERROR 2839ANALYZE SELECT * FROM v1 WHERE a IN ( SELECT a FROM t2 ); 2840 2841 2842--echo #------------------------------------------------------------------------ 2843--echo # I/R/U/D/S on the outer view 2844--echo # Expectation: Can run the queries, but not EXPLAIN or ANALYZE 2845--echo # because the query plan cannot be shown 2846--echo # (it could have revealed the structure of the inner view) 2847--echo #------------------------------------------------------------------------ 2848 2849 2850INSERT INTO v2 (a) VALUES (10); 2851--error ER_VIEW_NO_EXPLAIN 2852EXPLAIN INSERT INTO v2 (a) VALUES (10); 2853--error ER_VIEW_NO_EXPLAIN 2854ANALYZE INSERT INTO v2 (a) VALUES (10); 2855 2856INSERT INTO v2 SELECT * FROM t2; 2857--error ER_VIEW_NO_EXPLAIN 2858EXPLAIN INSERT INTO v2 SELECT * FROM t2; 2859--error ER_VIEW_NO_EXPLAIN 2860ANALYZE INSERT INTO v2 SELECT * FROM t2; 2861 2862REPLACE INTO v2 (a) VALUES (10); 2863--error ER_VIEW_NO_EXPLAIN 2864EXPLAIN REPLACE INTO v2 (a) VALUES (10); 2865--error ER_VIEW_NO_EXPLAIN 2866ANALYZE REPLACE INTO v2 (a) VALUES (10); 2867 2868REPLACE INTO v2 SELECT * FROM t2; 2869--error ER_VIEW_NO_EXPLAIN 2870EXPLAIN REPLACE INTO v2 SELECT * FROM t2; 2871--error ER_VIEW_NO_EXPLAIN 2872ANALYZE REPLACE INTO v2 SELECT * FROM t2; 2873 2874UPDATE v2 SET a = 10; 2875--error ER_VIEW_NO_EXPLAIN 2876EXPLAIN UPDATE v2 SET a = 10; 2877--error ER_VIEW_NO_EXPLAIN 2878ANALYZE UPDATE v2 SET a = 10; 2879 2880UPDATE v2 SET a = a + 1; 2881--error ER_VIEW_NO_EXPLAIN 2882EXPLAIN UPDATE v2 SET a = a + 1; 2883--error ER_VIEW_NO_EXPLAIN 2884ANALYZE UPDATE v2 SET a = a + 1; 2885 2886UPDATE v2, t2 SET v2.a = v2.a + 1 WHERE v2.a = t2.a; 2887--error ER_VIEW_NO_EXPLAIN 2888EXPLAIN UPDATE v2, t2 SET v2.a = v2.a + 1 WHERE v2.a = t2.a; 2889--error ER_VIEW_NO_EXPLAIN 2890ANALYZE UPDATE v2, t2 SET v2.a = v2.a + 1 WHERE v2.a = t2.a; 2891 2892DELETE FROM v2; 2893--error ER_VIEW_NO_EXPLAIN 2894EXPLAIN DELETE FROM v2; 2895--error ER_VIEW_NO_EXPLAIN 2896ANALYZE DELETE FROM v2; 2897 2898DELETE FROM v2 WHERE a = 10; 2899--error ER_VIEW_NO_EXPLAIN 2900EXPLAIN DELETE FROM v2 WHERE a = 10; 2901--error ER_VIEW_NO_EXPLAIN 2902ANALYZE DELETE FROM v2 WHERE a = 10; 2903 2904DELETE FROM v2 USING v2, t2; 2905--error ER_VIEW_NO_EXPLAIN 2906EXPLAIN DELETE FROM v2 USING v2, t2; 2907--error ER_VIEW_NO_EXPLAIN 2908ANALYZE DELETE FROM v2 USING v2, t2; 2909 2910DELETE FROM v2 USING v2, t2 WHERE v2.a = t2.a; 2911--error ER_VIEW_NO_EXPLAIN 2912EXPLAIN DELETE FROM v2 USING v2, t2 WHERE v2.a = t2.a; 2913--error ER_VIEW_NO_EXPLAIN 2914ANALYZE DELETE FROM v2 USING v2, t2 WHERE v2.a = t2.a; 2915 2916SELECT * FROM v2; 2917--error ER_VIEW_NO_EXPLAIN 2918EXPLAIN SELECT * FROM v2; 2919--error ER_VIEW_NO_EXPLAIN 2920ANALYZE SELECT * FROM v2; 2921 2922SELECT * FROM v2 WHERE a = 10; 2923--error ER_VIEW_NO_EXPLAIN 2924EXPLAIN SELECT * FROM v2 WHERE a = 10; 2925--error ER_VIEW_NO_EXPLAIN 2926ANALYZE SELECT * FROM v2 WHERE a = 10; 2927 2928SELECT * FROM v2 WHERE a IN ( SELECT a FROM t2 ); 2929--error ER_VIEW_NO_EXPLAIN 2930EXPLAIN SELECT * FROM v2 WHERE a IN ( SELECT a FROM t2 ); 2931--error ER_VIEW_NO_EXPLAIN 2932ANALYZE SELECT * FROM v2 WHERE a IN ( SELECT a FROM t2 ); 2933 2934 2935--echo 2936--echo #======================================================================== 2937--echo # Test: Grant UPDATE on the inner view 2938--echo #======================================================================== 2939--echo 2940 2941connection default; 2942REVOKE ALL PRIVILEGES ON privtest_db.v1 FROM 'privtest'@'localhost'; 2943GRANT UPDATE ON privtest_db.v1 TO 'privtest'@'localhost'; 2944connection con1; 2945 2946 2947--echo #------------------------------------------------------------------------ 2948--echo # I/R/U/D/S on the inner view 2949--echo # Expectation: Can run UPDATE which does not read any columns, but not 2950--echo # but not EXPLAIN UPDATE or ANALYZE UPDATE 2951--echo # because the query plan cannot be shown 2952--echo # (it could have revealed the structure of the view). 2953--echo #------------------------------------------------------------------------ 2954 2955 2956--error ER_TABLEACCESS_DENIED_ERROR 2957INSERT INTO v1 (a) VALUES (10); 2958--error ER_TABLEACCESS_DENIED_ERROR 2959EXPLAIN INSERT INTO v1 (a) VALUES (10); 2960--error ER_TABLEACCESS_DENIED_ERROR 2961ANALYZE INSERT INTO v1 (a) VALUES (10); 2962 2963--error ER_TABLEACCESS_DENIED_ERROR 2964INSERT INTO v1 SELECT * FROM t2; 2965--error ER_TABLEACCESS_DENIED_ERROR 2966EXPLAIN INSERT INTO v1 SELECT * FROM t2; 2967--error ER_TABLEACCESS_DENIED_ERROR 2968ANALYZE INSERT INTO v1 SELECT * FROM t2; 2969 2970--error ER_TABLEACCESS_DENIED_ERROR 2971REPLACE INTO v1 (a) VALUES (10); 2972--error ER_TABLEACCESS_DENIED_ERROR 2973EXPLAIN REPLACE INTO v1 (a) VALUES (10); 2974--error ER_TABLEACCESS_DENIED_ERROR 2975ANALYZE REPLACE INTO v1 (a) VALUES (10); 2976 2977--error ER_TABLEACCESS_DENIED_ERROR 2978REPLACE INTO v1 SELECT * FROM t2; 2979--error ER_TABLEACCESS_DENIED_ERROR 2980EXPLAIN REPLACE INTO v1 SELECT * FROM t2; 2981--error ER_TABLEACCESS_DENIED_ERROR 2982ANALYZE REPLACE INTO v1 SELECT * FROM t2; 2983 2984UPDATE v1 SET a = 10; 2985--error ER_VIEW_NO_EXPLAIN 2986EXPLAIN UPDATE v1 SET a = 10; 2987--error ER_VIEW_NO_EXPLAIN 2988ANALYZE UPDATE v1 SET a = 10; 2989 2990# Wrong result due to MDEV-7042 2991#--error ER_COLUMNACCESS_DENIED_ERROR 2992UPDATE v1 SET a = a + 1; 2993# Strange error code due to MDEV-7042 2994#--error ER_COLUMNACCESS_DENIED_ERROR 2995--error ER_VIEW_NO_EXPLAIN 2996EXPLAIN UPDATE v1 SET a = a + 1; 2997--error ER_VIEW_NO_EXPLAIN 2998ANALYZE UPDATE v1 SET a = a + 1; 2999 3000--error ER_COLUMNACCESS_DENIED_ERROR 3001UPDATE v1, t2 SET v1.a = v1.a + 1 WHERE v1.a = t2.a; 3002# Strange error code due to MDEV-7033, MDEV-7042 3003#--error ER_COLUMNACCESS_DENIED_ERROR 3004--error ER_VIEW_NO_EXPLAIN 3005EXPLAIN UPDATE v1, t2 SET v1.a = v1.a + 1 WHERE v1.a = t2.a; 3006--error ER_VIEW_NO_EXPLAIN 3007ANALYZE UPDATE v1, t2 SET v1.a = v1.a + 1 WHERE v1.a = t2.a; 3008 3009--error ER_TABLEACCESS_DENIED_ERROR 3010DELETE FROM v1; 3011--error ER_TABLEACCESS_DENIED_ERROR 3012EXPLAIN DELETE FROM v1; 3013--error ER_TABLEACCESS_DENIED_ERROR 3014ANALYZE DELETE FROM v1; 3015 3016--error ER_TABLEACCESS_DENIED_ERROR 3017DELETE FROM v1 WHERE a = 10; 3018--error ER_TABLEACCESS_DENIED_ERROR 3019EXPLAIN DELETE FROM v1 WHERE a = 10; 3020--error ER_TABLEACCESS_DENIED_ERROR 3021ANALYZE DELETE FROM v1 WHERE a = 10; 3022 3023--error ER_TABLEACCESS_DENIED_ERROR 3024DELETE FROM v1 USING v1, t2; 3025--error ER_TABLEACCESS_DENIED_ERROR 3026EXPLAIN DELETE FROM v1 USING v1, t2; 3027--error ER_TABLEACCESS_DENIED_ERROR 3028ANALYZE DELETE FROM v1 USING v1, t2; 3029 3030--error ER_TABLEACCESS_DENIED_ERROR 3031DELETE FROM v1 USING v1, t2 WHERE v1.a = t2.a; 3032--error ER_TABLEACCESS_DENIED_ERROR 3033EXPLAIN DELETE FROM v1 USING v1, t2 WHERE v1.a = t2.a; 3034--error ER_TABLEACCESS_DENIED_ERROR 3035ANALYZE DELETE FROM v1 USING v1, t2 WHERE v1.a = t2.a; 3036 3037--error ER_TABLEACCESS_DENIED_ERROR 3038SELECT * FROM v1; 3039--error ER_TABLEACCESS_DENIED_ERROR 3040EXPLAIN SELECT * FROM v1; 3041--error ER_TABLEACCESS_DENIED_ERROR 3042ANALYZE SELECT * FROM v1; 3043 3044--error ER_TABLEACCESS_DENIED_ERROR 3045SELECT * FROM v1 WHERE a = 10; 3046--error ER_TABLEACCESS_DENIED_ERROR 3047EXPLAIN SELECT * FROM v1 WHERE a = 10; 3048--error ER_TABLEACCESS_DENIED_ERROR 3049ANALYZE SELECT * FROM v1 WHERE a = 10; 3050 3051--error ER_TABLEACCESS_DENIED_ERROR 3052SELECT * FROM v1 WHERE a IN ( SELECT a FROM t2 ); 3053--error ER_TABLEACCESS_DENIED_ERROR 3054EXPLAIN SELECT * FROM v1 WHERE a IN ( SELECT a FROM t2 ); 3055--error ER_TABLEACCESS_DENIED_ERROR 3056ANALYZE SELECT * FROM v1 WHERE a IN ( SELECT a FROM t2 ); 3057 3058 3059--echo #------------------------------------------------------------------------ 3060--echo # I/R/U/D/S on the outer view 3061--echo # Expectation: Can run the queries, but not EXPLAIN or ANALYZE 3062--echo # because the query plan cannot be shown 3063--echo # (it could have revealed the structure of the inner view) 3064--echo #------------------------------------------------------------------------ 3065 3066 3067INSERT INTO v2 (a) VALUES (10); 3068--error ER_VIEW_NO_EXPLAIN 3069EXPLAIN INSERT INTO v2 (a) VALUES (10); 3070--error ER_VIEW_NO_EXPLAIN 3071ANALYZE INSERT INTO v2 (a) VALUES (10); 3072 3073INSERT INTO v2 SELECT * FROM t2; 3074--error ER_VIEW_NO_EXPLAIN 3075EXPLAIN INSERT INTO v2 SELECT * FROM t2; 3076--error ER_VIEW_NO_EXPLAIN 3077ANALYZE INSERT INTO v2 SELECT * FROM t2; 3078 3079REPLACE INTO v2 (a) VALUES (10); 3080--error ER_VIEW_NO_EXPLAIN 3081EXPLAIN REPLACE INTO v2 (a) VALUES (10); 3082--error ER_VIEW_NO_EXPLAIN 3083ANALYZE REPLACE INTO v2 (a) VALUES (10); 3084 3085REPLACE INTO v2 SELECT * FROM t2; 3086--error ER_VIEW_NO_EXPLAIN 3087EXPLAIN REPLACE INTO v2 SELECT * FROM t2; 3088--error ER_VIEW_NO_EXPLAIN 3089ANALYZE REPLACE INTO v2 SELECT * FROM t2; 3090 3091UPDATE v2 SET a = 10; 3092--error ER_VIEW_NO_EXPLAIN 3093EXPLAIN UPDATE v2 SET a = 10; 3094--error ER_VIEW_NO_EXPLAIN 3095ANALYZE UPDATE v2 SET a = 10; 3096 3097UPDATE v2 SET a = a + 1; 3098--error ER_VIEW_NO_EXPLAIN 3099EXPLAIN UPDATE v2 SET a = a + 1; 3100--error ER_VIEW_NO_EXPLAIN 3101ANALYZE UPDATE v2 SET a = a + 1; 3102 3103UPDATE v2, t2 SET v2.a = v2.a + 1 WHERE v2.a = t2.a; 3104--error ER_VIEW_NO_EXPLAIN 3105EXPLAIN UPDATE v2, t2 SET v2.a = v2.a + 1 WHERE v2.a = t2.a; 3106--error ER_VIEW_NO_EXPLAIN 3107ANALYZE UPDATE v2, t2 SET v2.a = v2.a + 1 WHERE v2.a = t2.a; 3108 3109DELETE FROM v2; 3110--error ER_VIEW_NO_EXPLAIN 3111EXPLAIN DELETE FROM v2; 3112--error ER_VIEW_NO_EXPLAIN 3113ANALYZE DELETE FROM v2; 3114 3115DELETE FROM v2 WHERE a = 10; 3116--error ER_VIEW_NO_EXPLAIN 3117EXPLAIN DELETE FROM v2 WHERE a = 10; 3118--error ER_VIEW_NO_EXPLAIN 3119ANALYZE DELETE FROM v2 WHERE a = 10; 3120 3121DELETE FROM v2 USING v2, t2; 3122--error ER_VIEW_NO_EXPLAIN 3123EXPLAIN DELETE FROM v2 USING v2, t2; 3124--error ER_VIEW_NO_EXPLAIN 3125ANALYZE DELETE FROM v2 USING v2, t2; 3126 3127DELETE FROM v2 USING v2, t2 WHERE v2.a = t2.a; 3128--error ER_VIEW_NO_EXPLAIN 3129EXPLAIN DELETE FROM v2 USING v2, t2 WHERE v2.a = t2.a; 3130--error ER_VIEW_NO_EXPLAIN 3131ANALYZE DELETE FROM v2 USING v2, t2 WHERE v2.a = t2.a; 3132 3133SELECT * FROM v2; 3134--error ER_VIEW_NO_EXPLAIN 3135EXPLAIN SELECT * FROM v2; 3136--error ER_VIEW_NO_EXPLAIN 3137ANALYZE SELECT * FROM v2; 3138 3139SELECT * FROM v2 WHERE a = 10; 3140--error ER_VIEW_NO_EXPLAIN 3141EXPLAIN SELECT * FROM v2 WHERE a = 10; 3142--error ER_VIEW_NO_EXPLAIN 3143ANALYZE SELECT * FROM v2 WHERE a = 10; 3144 3145SELECT * FROM v2 WHERE a IN ( SELECT a FROM t2 ); 3146--error ER_VIEW_NO_EXPLAIN 3147EXPLAIN SELECT * FROM v2 WHERE a IN ( SELECT a FROM t2 ); 3148--error ER_VIEW_NO_EXPLAIN 3149ANALYZE SELECT * FROM v2 WHERE a IN ( SELECT a FROM t2 ); 3150 3151 3152--echo 3153--echo #======================================================================== 3154--echo # Test: Grant UPDATE, SELECT(a) on the inner view 3155--echo #======================================================================== 3156--echo 3157 3158connection default; 3159REVOKE ALL PRIVILEGES ON privtest_db.v1 FROM 'privtest'@'localhost'; 3160GRANT SELECT(a), UPDATE ON privtest_db.v1 TO 'privtest'@'localhost'; 3161connection con1; 3162 3163 3164--echo #------------------------------------------------------------------------ 3165--echo # I/R/U/D/S on the inner view 3166--echo # Expectation: Can run UPDATE, but not EXPLAIN or ANALYZE for it 3167--echo # because the query plan cannot be shown 3168--echo # (it could have revealed the structure of the view) 3169--echo #------------------------------------------------------------------------ 3170 3171 3172--error ER_TABLEACCESS_DENIED_ERROR 3173INSERT INTO v1 (a) VALUES (10); 3174--error ER_TABLEACCESS_DENIED_ERROR 3175EXPLAIN INSERT INTO v1 (a) VALUES (10); 3176--error ER_TABLEACCESS_DENIED_ERROR 3177ANALYZE INSERT INTO v1 (a) VALUES (10); 3178 3179--error ER_TABLEACCESS_DENIED_ERROR 3180INSERT INTO v1 SELECT * FROM t2; 3181--error ER_TABLEACCESS_DENIED_ERROR 3182EXPLAIN INSERT INTO v1 SELECT * FROM t2; 3183--error ER_TABLEACCESS_DENIED_ERROR 3184ANALYZE INSERT INTO v1 SELECT * FROM t2; 3185 3186 3187--error ER_TABLEACCESS_DENIED_ERROR 3188REPLACE INTO v1 (a) VALUES (10); 3189--error ER_TABLEACCESS_DENIED_ERROR 3190EXPLAIN REPLACE INTO v1 (a) VALUES (10); 3191--error ER_TABLEACCESS_DENIED_ERROR 3192ANALYZE REPLACE INTO v1 (a) VALUES (10); 3193 3194 3195--error ER_TABLEACCESS_DENIED_ERROR 3196REPLACE INTO v1 SELECT * FROM t2; 3197--error ER_TABLEACCESS_DENIED_ERROR 3198EXPLAIN REPLACE INTO v1 SELECT * FROM t2; 3199--error ER_TABLEACCESS_DENIED_ERROR 3200ANALYZE REPLACE INTO v1 SELECT * FROM t2; 3201 3202UPDATE v1 SET a = 10; 3203--error ER_VIEW_NO_EXPLAIN 3204EXPLAIN UPDATE v1 SET a = 10; 3205--error ER_VIEW_NO_EXPLAIN 3206ANALYZE UPDATE v1 SET a = 10; 3207 3208UPDATE v1 SET a = a + 1; 3209--error ER_VIEW_NO_EXPLAIN 3210EXPLAIN UPDATE v1 SET a = a + 1; 3211--error ER_VIEW_NO_EXPLAIN 3212ANALYZE UPDATE v1 SET a = a + 1; 3213 3214UPDATE v1, t2 SET v1.a = v1.a + 1 WHERE v1.a = t2.a; 3215--error ER_VIEW_NO_EXPLAIN 3216EXPLAIN UPDATE v1, t2 SET v1.a = v1.a + 1 WHERE v1.a = t2.a; 3217--error ER_VIEW_NO_EXPLAIN 3218ANALYZE UPDATE v1, t2 SET v1.a = v1.a + 1 WHERE v1.a = t2.a; 3219 3220--error ER_TABLEACCESS_DENIED_ERROR 3221DELETE FROM v1; 3222--error ER_TABLEACCESS_DENIED_ERROR 3223EXPLAIN DELETE FROM v1; 3224--error ER_TABLEACCESS_DENIED_ERROR 3225ANALYZE DELETE FROM v1; 3226 3227--error ER_TABLEACCESS_DENIED_ERROR 3228DELETE FROM v1 WHERE a = 10; 3229--error ER_TABLEACCESS_DENIED_ERROR 3230EXPLAIN DELETE FROM v1 WHERE a = 10; 3231--error ER_TABLEACCESS_DENIED_ERROR 3232ANALYZE DELETE FROM v1 WHERE a = 10; 3233 3234--error ER_TABLEACCESS_DENIED_ERROR 3235DELETE FROM v1 USING v1, t2; 3236--error ER_TABLEACCESS_DENIED_ERROR 3237EXPLAIN DELETE FROM v1 USING v1, t2; 3238--error ER_TABLEACCESS_DENIED_ERROR 3239ANALYZE DELETE FROM v1 USING v1, t2; 3240 3241--error ER_TABLEACCESS_DENIED_ERROR 3242DELETE FROM v1 USING v1, t2 WHERE v1.a = t2.a; 3243--error ER_TABLEACCESS_DENIED_ERROR 3244EXPLAIN DELETE FROM v1 USING v1, t2 WHERE v1.a = t2.a; 3245--error ER_TABLEACCESS_DENIED_ERROR 3246ANALYZE DELETE FROM v1 USING v1, t2 WHERE v1.a = t2.a; 3247 3248--error ER_TABLEACCESS_DENIED_ERROR 3249SELECT * FROM v1; 3250--error ER_VIEW_NO_EXPLAIN 3251EXPLAIN SELECT * FROM v1; 3252--error ER_VIEW_NO_EXPLAIN 3253ANALYZE SELECT * FROM v1; 3254 3255--error ER_TABLEACCESS_DENIED_ERROR 3256SELECT * FROM v1 WHERE a = 10; 3257--error ER_VIEW_NO_EXPLAIN 3258EXPLAIN SELECT * FROM v1 WHERE a = 10; 3259--error ER_VIEW_NO_EXPLAIN 3260ANALYZE SELECT * FROM v1 WHERE a = 10; 3261 3262--error ER_TABLEACCESS_DENIED_ERROR 3263SELECT * FROM v1 WHERE a IN ( SELECT a FROM t2 ); 3264--error ER_VIEW_NO_EXPLAIN 3265EXPLAIN SELECT * FROM v1 WHERE a IN ( SELECT a FROM t2 ); 3266--error ER_VIEW_NO_EXPLAIN 3267ANALYZE SELECT * FROM v1 WHERE a IN ( SELECT a FROM t2 ); 3268 3269 3270--echo #------------------------------------------------------------------------ 3271--echo # I/R/U/D/S on the outer view 3272--echo # Expectation: Can run the queries, but not EXPLAIN or ANALYZE 3273--echo # because the query plan cannot be shown 3274--echo # (it could have revealed the structure of the inner view) 3275--echo #------------------------------------------------------------------------ 3276 3277 3278INSERT INTO v2 (a) VALUES (10); 3279--error ER_VIEW_NO_EXPLAIN 3280EXPLAIN INSERT INTO v2 (a) VALUES (10); 3281--error ER_VIEW_NO_EXPLAIN 3282ANALYZE INSERT INTO v2 (a) VALUES (10); 3283 3284INSERT INTO v2 SELECT * FROM t2; 3285--error ER_VIEW_NO_EXPLAIN 3286EXPLAIN INSERT INTO v2 SELECT * FROM t2; 3287--error ER_VIEW_NO_EXPLAIN 3288ANALYZE INSERT INTO v2 SELECT * FROM t2; 3289 3290REPLACE INTO v2 (a) VALUES (10); 3291--error ER_VIEW_NO_EXPLAIN 3292EXPLAIN REPLACE INTO v2 (a) VALUES (10); 3293--error ER_VIEW_NO_EXPLAIN 3294ANALYZE REPLACE INTO v2 (a) VALUES (10); 3295 3296REPLACE INTO v2 SELECT * FROM t2; 3297--error ER_VIEW_NO_EXPLAIN 3298EXPLAIN REPLACE INTO v2 SELECT * FROM t2; 3299--error ER_VIEW_NO_EXPLAIN 3300ANALYZE REPLACE INTO v2 SELECT * FROM t2; 3301 3302UPDATE v2 SET a = 10; 3303--error ER_VIEW_NO_EXPLAIN 3304EXPLAIN UPDATE v2 SET a = 10; 3305--error ER_VIEW_NO_EXPLAIN 3306ANALYZE UPDATE v2 SET a = 10; 3307 3308UPDATE v2 SET a = a + 1; 3309--error ER_VIEW_NO_EXPLAIN 3310EXPLAIN UPDATE v2 SET a = a + 1; 3311--error ER_VIEW_NO_EXPLAIN 3312ANALYZE UPDATE v2 SET a = a + 1; 3313 3314UPDATE v2, t2 SET v2.a = v2.a + 1 WHERE v2.a = t2.a; 3315--error ER_VIEW_NO_EXPLAIN 3316EXPLAIN UPDATE v2, t2 SET v2.a = v2.a + 1 WHERE v2.a = t2.a; 3317--error ER_VIEW_NO_EXPLAIN 3318ANALYZE UPDATE v2, t2 SET v2.a = v2.a + 1 WHERE v2.a = t2.a; 3319 3320DELETE FROM v2; 3321--error ER_VIEW_NO_EXPLAIN 3322EXPLAIN DELETE FROM v2; 3323--error ER_VIEW_NO_EXPLAIN 3324ANALYZE DELETE FROM v2; 3325 3326DELETE FROM v2 WHERE a = 10; 3327--error ER_VIEW_NO_EXPLAIN 3328EXPLAIN DELETE FROM v2 WHERE a = 10; 3329--error ER_VIEW_NO_EXPLAIN 3330ANALYZE DELETE FROM v2 WHERE a = 10; 3331 3332DELETE FROM v2 USING v2, t2; 3333--error ER_VIEW_NO_EXPLAIN 3334EXPLAIN DELETE FROM v2 USING v2, t2; 3335--error ER_VIEW_NO_EXPLAIN 3336ANALYZE DELETE FROM v2 USING v2, t2; 3337 3338DELETE FROM v2 USING v2, t2 WHERE v2.a = t2.a; 3339--error ER_VIEW_NO_EXPLAIN 3340EXPLAIN DELETE FROM v2 USING v2, t2 WHERE v2.a = t2.a; 3341--error ER_VIEW_NO_EXPLAIN 3342ANALYZE DELETE FROM v2 USING v2, t2 WHERE v2.a = t2.a; 3343 3344SELECT * FROM v2; 3345--error ER_VIEW_NO_EXPLAIN 3346EXPLAIN SELECT * FROM v2; 3347--error ER_VIEW_NO_EXPLAIN 3348ANALYZE SELECT * FROM v2; 3349 3350SELECT * FROM v2 WHERE a = 10; 3351--error ER_VIEW_NO_EXPLAIN 3352EXPLAIN SELECT * FROM v2 WHERE a = 10; 3353--error ER_VIEW_NO_EXPLAIN 3354ANALYZE SELECT * FROM v2 WHERE a = 10; 3355 3356SELECT * FROM v2 WHERE a IN ( SELECT a FROM t2 ); 3357--error ER_VIEW_NO_EXPLAIN 3358EXPLAIN SELECT * FROM v2 WHERE a IN ( SELECT a FROM t2 ); 3359--error ER_VIEW_NO_EXPLAIN 3360ANALYZE SELECT * FROM v2 WHERE a IN ( SELECT a FROM t2 ); 3361 3362--echo 3363--echo #======================================================================== 3364--echo # Test: Grant SELECT, UPDATE on the inner view 3365--echo #======================================================================== 3366--echo 3367 3368connection default; 3369REVOKE ALL PRIVILEGES ON privtest_db.v1 FROM 'privtest'@'localhost'; 3370GRANT SELECT, UPDATE ON privtest_db.v1 TO 'privtest'@'localhost'; 3371connection con1; 3372 3373 3374--echo #------------------------------------------------------------------------ 3375--echo # I/R/U/D/S on the inner view 3376--echo # Expectation: Can run the UPDATE and SELECT queries, but not EXPLAIN 3377--echo # or ANALYZE because the query plan cannot be shown 3378--echo # (it could have revealed the structure of the view) 3379--echo #------------------------------------------------------------------------ 3380 3381 3382--error ER_TABLEACCESS_DENIED_ERROR 3383INSERT INTO v1 (a) VALUES (10); 3384--error ER_TABLEACCESS_DENIED_ERROR 3385EXPLAIN INSERT INTO v1 (a) VALUES (10); 3386--error ER_TABLEACCESS_DENIED_ERROR 3387ANALYZE INSERT INTO v1 (a) VALUES (10); 3388 3389--error ER_TABLEACCESS_DENIED_ERROR 3390INSERT INTO v1 SELECT * FROM t2; 3391--error ER_TABLEACCESS_DENIED_ERROR 3392EXPLAIN INSERT INTO v1 SELECT * FROM t2; 3393--error ER_TABLEACCESS_DENIED_ERROR 3394ANALYZE INSERT INTO v1 SELECT * FROM t2; 3395 3396--error ER_TABLEACCESS_DENIED_ERROR 3397REPLACE INTO v1 (a) VALUES (10); 3398--error ER_TABLEACCESS_DENIED_ERROR 3399EXPLAIN REPLACE INTO v1 (a) VALUES (10); 3400--error ER_TABLEACCESS_DENIED_ERROR 3401ANALYZE REPLACE INTO v1 (a) VALUES (10); 3402 3403--error ER_TABLEACCESS_DENIED_ERROR 3404REPLACE INTO v1 SELECT * FROM t2; 3405--error ER_TABLEACCESS_DENIED_ERROR 3406EXPLAIN REPLACE INTO v1 SELECT * FROM t2; 3407--error ER_TABLEACCESS_DENIED_ERROR 3408ANALYZE REPLACE INTO v1 SELECT * FROM t2; 3409 3410UPDATE v1 SET a = 10; 3411--error ER_VIEW_NO_EXPLAIN 3412EXPLAIN UPDATE v1 SET a = 10; 3413--error ER_VIEW_NO_EXPLAIN 3414ANALYZE UPDATE v1 SET a = 10; 3415 3416UPDATE v1 SET a = a + 1; 3417--error ER_VIEW_NO_EXPLAIN 3418EXPLAIN UPDATE v1 SET a = a + 1; 3419--error ER_VIEW_NO_EXPLAIN 3420ANALYZE UPDATE v1 SET a = a + 1; 3421 3422UPDATE v1, t2 SET v1.a = v1.a + 1 WHERE v1.a = t2.a; 3423--error ER_VIEW_NO_EXPLAIN 3424EXPLAIN UPDATE v1, t2 SET v1.a = v1.a + 1 WHERE v1.a = t2.a; 3425--error ER_VIEW_NO_EXPLAIN 3426ANALYZE UPDATE v1, t2 SET v1.a = v1.a + 1 WHERE v1.a = t2.a; 3427 3428--error ER_TABLEACCESS_DENIED_ERROR 3429DELETE FROM v1; 3430--error ER_TABLEACCESS_DENIED_ERROR 3431EXPLAIN DELETE FROM v1; 3432--error ER_TABLEACCESS_DENIED_ERROR 3433ANALYZE DELETE FROM v1; 3434 3435--error ER_TABLEACCESS_DENIED_ERROR 3436DELETE FROM v1 WHERE a = 10; 3437--error ER_TABLEACCESS_DENIED_ERROR 3438EXPLAIN DELETE FROM v1 WHERE a = 10; 3439--error ER_TABLEACCESS_DENIED_ERROR 3440ANALYZE DELETE FROM v1 WHERE a = 10; 3441 3442--error ER_TABLEACCESS_DENIED_ERROR 3443DELETE FROM v1 USING v1, t2; 3444--error ER_TABLEACCESS_DENIED_ERROR 3445EXPLAIN DELETE FROM v1 USING v1, t2; 3446--error ER_TABLEACCESS_DENIED_ERROR 3447ANALYZE DELETE FROM v1 USING v1, t2; 3448 3449--error ER_TABLEACCESS_DENIED_ERROR 3450DELETE FROM v1 USING v1, t2 WHERE v1.a = t2.a; 3451--error ER_TABLEACCESS_DENIED_ERROR 3452EXPLAIN DELETE FROM v1 USING v1, t2 WHERE v1.a = t2.a; 3453--error ER_TABLEACCESS_DENIED_ERROR 3454ANALYZE DELETE FROM v1 USING v1, t2 WHERE v1.a = t2.a; 3455 3456SELECT * FROM v1; 3457--error ER_VIEW_NO_EXPLAIN 3458EXPLAIN SELECT * FROM v1; 3459--error ER_VIEW_NO_EXPLAIN 3460ANALYZE SELECT * FROM v1; 3461 3462SELECT * FROM v1 WHERE a = 10; 3463--error ER_VIEW_NO_EXPLAIN 3464EXPLAIN SELECT * FROM v1 WHERE a = 10; 3465--error ER_VIEW_NO_EXPLAIN 3466ANALYZE SELECT * FROM v1 WHERE a = 10; 3467 3468SELECT * FROM v1 WHERE a IN ( SELECT a FROM t2 ); 3469--error ER_VIEW_NO_EXPLAIN 3470EXPLAIN SELECT * FROM v1 WHERE a IN ( SELECT a FROM t2 ); 3471--error ER_VIEW_NO_EXPLAIN 3472ANALYZE SELECT * FROM v1 WHERE a IN ( SELECT a FROM t2 ); 3473 3474 3475--echo #------------------------------------------------------------------------ 3476--echo # I/R/U/D/S on the outer view 3477--echo # Expectation: Can run the queries, but not EXPLAIN or ANALYZE 3478--echo # because the query plan cannot be shown 3479--echo # (it could have revealed the structure of the inner view) 3480--echo #------------------------------------------------------------------------ 3481 3482 3483INSERT INTO v2 (a) VALUES (10); 3484--error ER_VIEW_NO_EXPLAIN 3485EXPLAIN INSERT INTO v2 (a) VALUES (10); 3486--error ER_VIEW_NO_EXPLAIN 3487ANALYZE INSERT INTO v2 (a) VALUES (10); 3488 3489INSERT INTO v2 SELECT * FROM t2; 3490--error ER_VIEW_NO_EXPLAIN 3491EXPLAIN INSERT INTO v2 SELECT * FROM t2; 3492--error ER_VIEW_NO_EXPLAIN 3493ANALYZE INSERT INTO v2 SELECT * FROM t2; 3494 3495REPLACE INTO v2 (a) VALUES (10); 3496--error ER_VIEW_NO_EXPLAIN 3497EXPLAIN REPLACE INTO v2 (a) VALUES (10); 3498--error ER_VIEW_NO_EXPLAIN 3499ANALYZE REPLACE INTO v2 (a) VALUES (10); 3500 3501REPLACE INTO v2 SELECT * FROM t2; 3502--error ER_VIEW_NO_EXPLAIN 3503EXPLAIN REPLACE INTO v2 SELECT * FROM t2; 3504--error ER_VIEW_NO_EXPLAIN 3505ANALYZE REPLACE INTO v2 SELECT * FROM t2; 3506 3507UPDATE v2 SET a = 10; 3508--error ER_VIEW_NO_EXPLAIN 3509EXPLAIN UPDATE v2 SET a = 10; 3510--error ER_VIEW_NO_EXPLAIN 3511ANALYZE UPDATE v2 SET a = 10; 3512 3513UPDATE v2 SET a = a + 1; 3514--error ER_VIEW_NO_EXPLAIN 3515EXPLAIN UPDATE v2 SET a = a + 1; 3516--error ER_VIEW_NO_EXPLAIN 3517ANALYZE UPDATE v2 SET a = a + 1; 3518 3519UPDATE v2, t2 SET v2.a = v2.a + 1 WHERE v2.a = t2.a; 3520--error ER_VIEW_NO_EXPLAIN 3521EXPLAIN UPDATE v2, t2 SET v2.a = v2.a + 1 WHERE v2.a = t2.a; 3522--error ER_VIEW_NO_EXPLAIN 3523ANALYZE UPDATE v2, t2 SET v2.a = v2.a + 1 WHERE v2.a = t2.a; 3524 3525DELETE FROM v2; 3526--error ER_VIEW_NO_EXPLAIN 3527EXPLAIN DELETE FROM v2; 3528--error ER_VIEW_NO_EXPLAIN 3529ANALYZE DELETE FROM v2; 3530 3531DELETE FROM v2 WHERE a = 10; 3532--error ER_VIEW_NO_EXPLAIN 3533EXPLAIN DELETE FROM v2 WHERE a = 10; 3534--error ER_VIEW_NO_EXPLAIN 3535ANALYZE DELETE FROM v2 WHERE a = 10; 3536 3537DELETE FROM v2 USING v2, t2; 3538--error ER_VIEW_NO_EXPLAIN 3539EXPLAIN DELETE FROM v2 USING v2, t2; 3540--error ER_VIEW_NO_EXPLAIN 3541ANALYZE DELETE FROM v2 USING v2, t2; 3542 3543DELETE FROM v2 USING v2, t2 WHERE v2.a = t2.a; 3544--error ER_VIEW_NO_EXPLAIN 3545EXPLAIN DELETE FROM v2 USING v2, t2 WHERE v2.a = t2.a; 3546--error ER_VIEW_NO_EXPLAIN 3547ANALYZE DELETE FROM v2 USING v2, t2 WHERE v2.a = t2.a; 3548 3549SELECT * FROM v2; 3550--error ER_VIEW_NO_EXPLAIN 3551EXPLAIN SELECT * FROM v2; 3552--error ER_VIEW_NO_EXPLAIN 3553ANALYZE SELECT * FROM v2; 3554 3555SELECT * FROM v2 WHERE a = 10; 3556--error ER_VIEW_NO_EXPLAIN 3557EXPLAIN SELECT * FROM v2 WHERE a = 10; 3558--error ER_VIEW_NO_EXPLAIN 3559ANALYZE SELECT * FROM v2 WHERE a = 10; 3560 3561SELECT * FROM v2 WHERE a IN ( SELECT a FROM t2 ); 3562--error ER_VIEW_NO_EXPLAIN 3563EXPLAIN SELECT * FROM v2 WHERE a IN ( SELECT a FROM t2 ); 3564--error ER_VIEW_NO_EXPLAIN 3565ANALYZE SELECT * FROM v2 WHERE a IN ( SELECT a FROM t2 ); 3566 3567 3568--echo 3569--echo #======================================================================== 3570--echo # Test: Grant DELETE on the inner view 3571--echo #======================================================================== 3572--echo 3573 3574connection default; 3575REVOKE ALL PRIVILEGES ON privtest_db.v1 FROM 'privtest'@'localhost'; 3576GRANT DELETE ON privtest_db.v1 TO 'privtest'@'localhost'; 3577connection con1; 3578 3579 3580--echo #------------------------------------------------------------------------ 3581--echo # I/R/U/D/S on the inner view 3582--echo # Expectation: Can run DELETE which does not read any columns, 3583--echo # but not EXPLAIN DELETE or ANALYZE DELETE 3584--echo # because the query plan cannot be shown 3585--echo # (it could have revealed the structure of the view) 3586--echo #------------------------------------------------------------------------ 3587 3588 3589--error ER_TABLEACCESS_DENIED_ERROR 3590INSERT INTO v1 (a) VALUES (10); 3591--error ER_TABLEACCESS_DENIED_ERROR 3592EXPLAIN INSERT INTO v1 (a) VALUES (10); 3593--error ER_TABLEACCESS_DENIED_ERROR 3594ANALYZE INSERT INTO v1 (a) VALUES (10); 3595 3596--error ER_TABLEACCESS_DENIED_ERROR 3597INSERT INTO v1 SELECT * FROM t2; 3598--error ER_TABLEACCESS_DENIED_ERROR 3599EXPLAIN INSERT INTO v1 SELECT * FROM t2; 3600--error ER_TABLEACCESS_DENIED_ERROR 3601ANALYZE INSERT INTO v1 SELECT * FROM t2; 3602 3603--error ER_TABLEACCESS_DENIED_ERROR 3604REPLACE INTO v1 (a) VALUES (10); 3605--error ER_TABLEACCESS_DENIED_ERROR 3606EXPLAIN REPLACE INTO v1 (a) VALUES (10); 3607--error ER_TABLEACCESS_DENIED_ERROR 3608ANALYZE REPLACE INTO v1 (a) VALUES (10); 3609 3610--error ER_TABLEACCESS_DENIED_ERROR 3611REPLACE INTO v1 SELECT * FROM t2; 3612--error ER_TABLEACCESS_DENIED_ERROR 3613EXPLAIN REPLACE INTO v1 SELECT * FROM t2; 3614--error ER_TABLEACCESS_DENIED_ERROR 3615ANALYZE REPLACE INTO v1 SELECT * FROM t2; 3616 3617--error ER_TABLEACCESS_DENIED_ERROR 3618UPDATE v1 SET a = 10; 3619--error ER_TABLEACCESS_DENIED_ERROR 3620EXPLAIN UPDATE v1 SET a = 10; 3621--error ER_TABLEACCESS_DENIED_ERROR 3622ANALYZE UPDATE v1 SET a = 10; 3623 3624--error ER_TABLEACCESS_DENIED_ERROR 3625UPDATE v1 SET a = a + 1; 3626--error ER_TABLEACCESS_DENIED_ERROR 3627EXPLAIN UPDATE v1 SET a = a + 1; 3628--error ER_TABLEACCESS_DENIED_ERROR 3629ANALYZE UPDATE v1 SET a = a + 1; 3630 3631--error ER_TABLEACCESS_DENIED_ERROR 3632UPDATE v1, t2 SET v1.a = v1.a + 1 WHERE v1.a = t2.a; 3633--error ER_TABLEACCESS_DENIED_ERROR 3634EXPLAIN UPDATE v1, t2 SET v1.a = v1.a + 1 WHERE v1.a = t2.a; 3635--error ER_TABLEACCESS_DENIED_ERROR 3636ANALYZE UPDATE v1, t2 SET v1.a = v1.a + 1 WHERE v1.a = t2.a; 3637 3638DELETE FROM v1; 3639--error ER_VIEW_NO_EXPLAIN 3640EXPLAIN DELETE FROM v1; 3641--error ER_VIEW_NO_EXPLAIN 3642ANALYZE DELETE FROM v1; 3643 3644--error ER_COLUMNACCESS_DENIED_ERROR 3645DELETE FROM v1 WHERE a = 10; 3646# Strange error code due to MDEV-7033 3647#--error ER_COLUMNACCESS_DENIED_ERROR 3648--error ER_VIEW_NO_EXPLAIN 3649EXPLAIN DELETE FROM v1 WHERE a = 10; 3650--error ER_VIEW_NO_EXPLAIN 3651ANALYZE DELETE FROM v1 WHERE a = 10; 3652 3653# Unexpected error due to MDEV-7043 3654--error ER_TABLEACCESS_DENIED_ERROR 3655DELETE FROM v1 USING v1, t2; 3656# Unexpected error code due to MDEV-7043 3657--error ER_TABLEACCESS_DENIED_ERROR 3658EXPLAIN DELETE FROM v1 USING v1, t2; 3659# Unexpected error code due to MDEV-7043 3660--error ER_TABLEACCESS_DENIED_ERROR 3661ANALYZE DELETE FROM v1 USING v1, t2; 3662 3663--error ER_TABLEACCESS_DENIED_ERROR 3664DELETE FROM v1 USING v1, t2 WHERE v1.a = t2.a; 3665--error ER_TABLEACCESS_DENIED_ERROR 3666EXPLAIN DELETE FROM v1 USING v1, t2 WHERE v1.a = t2.a; 3667--error ER_TABLEACCESS_DENIED_ERROR 3668ANALYZE DELETE FROM v1 USING v1, t2 WHERE v1.a = t2.a; 3669 3670--error ER_TABLEACCESS_DENIED_ERROR 3671SELECT * FROM v1; 3672--error ER_TABLEACCESS_DENIED_ERROR 3673EXPLAIN SELECT * FROM v1; 3674--error ER_TABLEACCESS_DENIED_ERROR 3675ANALYZE SELECT * FROM v1; 3676 3677--error ER_TABLEACCESS_DENIED_ERROR 3678SELECT * FROM v1 WHERE a = 10; 3679--error ER_TABLEACCESS_DENIED_ERROR 3680EXPLAIN SELECT * FROM v1 WHERE a = 10; 3681--error ER_TABLEACCESS_DENIED_ERROR 3682ANALYZE SELECT * FROM v1 WHERE a = 10; 3683 3684--error ER_TABLEACCESS_DENIED_ERROR 3685SELECT * FROM v1 WHERE a IN ( SELECT a FROM t2 ); 3686--error ER_TABLEACCESS_DENIED_ERROR 3687EXPLAIN SELECT * FROM v1 WHERE a IN ( SELECT a FROM t2 ); 3688--error ER_TABLEACCESS_DENIED_ERROR 3689ANALYZE SELECT * FROM v1 WHERE a IN ( SELECT a FROM t2 ); 3690 3691 3692--echo #------------------------------------------------------------------------ 3693--echo # I/R/U/D/S on the outer view 3694--echo # Expectation: Can run the queries, but not EXPLAIN or ANALYZE 3695--echo # because the query plan cannot be shown 3696--echo # (it could have revealed the structure of the inner view) 3697--echo #------------------------------------------------------------------------ 3698 3699 3700INSERT INTO v2 (a) VALUES (10); 3701--error ER_VIEW_NO_EXPLAIN 3702EXPLAIN INSERT INTO v2 (a) VALUES (10); 3703--error ER_VIEW_NO_EXPLAIN 3704ANALYZE INSERT INTO v2 (a) VALUES (10); 3705 3706INSERT INTO v2 SELECT * FROM t2; 3707--error ER_VIEW_NO_EXPLAIN 3708EXPLAIN INSERT INTO v2 SELECT * FROM t2; 3709--error ER_VIEW_NO_EXPLAIN 3710ANALYZE INSERT INTO v2 SELECT * FROM t2; 3711 3712REPLACE INTO v2 (a) VALUES (10); 3713--error ER_VIEW_NO_EXPLAIN 3714EXPLAIN REPLACE INTO v2 (a) VALUES (10); 3715--error ER_VIEW_NO_EXPLAIN 3716ANALYZE REPLACE INTO v2 (a) VALUES (10); 3717 3718REPLACE INTO v2 SELECT * FROM t2; 3719--error ER_VIEW_NO_EXPLAIN 3720EXPLAIN REPLACE INTO v2 SELECT * FROM t2; 3721--error ER_VIEW_NO_EXPLAIN 3722ANALYZE REPLACE INTO v2 SELECT * FROM t2; 3723 3724UPDATE v2 SET a = 10; 3725--error ER_VIEW_NO_EXPLAIN 3726EXPLAIN UPDATE v2 SET a = 10; 3727--error ER_VIEW_NO_EXPLAIN 3728ANALYZE UPDATE v2 SET a = 10; 3729 3730UPDATE v2 SET a = a + 1; 3731--error ER_VIEW_NO_EXPLAIN 3732EXPLAIN UPDATE v2 SET a = a + 1; 3733--error ER_VIEW_NO_EXPLAIN 3734ANALYZE UPDATE v2 SET a = a + 1; 3735 3736UPDATE v2, t2 SET v2.a = v2.a + 1 WHERE v2.a = t2.a; 3737--error ER_VIEW_NO_EXPLAIN 3738EXPLAIN UPDATE v2, t2 SET v2.a = v2.a + 1 WHERE v2.a = t2.a; 3739--error ER_VIEW_NO_EXPLAIN 3740ANALYZE UPDATE v2, t2 SET v2.a = v2.a + 1 WHERE v2.a = t2.a; 3741 3742DELETE FROM v2; 3743--error ER_VIEW_NO_EXPLAIN 3744EXPLAIN DELETE FROM v2; 3745--error ER_VIEW_NO_EXPLAIN 3746ANALYZE DELETE FROM v2; 3747 3748DELETE FROM v2 WHERE a = 10; 3749--error ER_VIEW_NO_EXPLAIN 3750EXPLAIN DELETE FROM v2 WHERE a = 10; 3751--error ER_VIEW_NO_EXPLAIN 3752ANALYZE DELETE FROM v2 WHERE a = 10; 3753 3754DELETE FROM v2 USING v2, t2; 3755--error ER_VIEW_NO_EXPLAIN 3756EXPLAIN DELETE FROM v2 USING v2, t2; 3757--error ER_VIEW_NO_EXPLAIN 3758ANALYZE DELETE FROM v2 USING v2, t2; 3759 3760DELETE FROM v2 USING v2, t2 WHERE v2.a = t2.a; 3761--error ER_VIEW_NO_EXPLAIN 3762EXPLAIN DELETE FROM v2 USING v2, t2 WHERE v2.a = t2.a; 3763--error ER_VIEW_NO_EXPLAIN 3764ANALYZE DELETE FROM v2 USING v2, t2 WHERE v2.a = t2.a; 3765 3766SELECT * FROM v2; 3767--error ER_VIEW_NO_EXPLAIN 3768EXPLAIN SELECT * FROM v2; 3769--error ER_VIEW_NO_EXPLAIN 3770ANALYZE SELECT * FROM v2; 3771 3772SELECT * FROM v2 WHERE a = 10; 3773--error ER_VIEW_NO_EXPLAIN 3774EXPLAIN SELECT * FROM v2 WHERE a = 10; 3775--error ER_VIEW_NO_EXPLAIN 3776ANALYZE SELECT * FROM v2 WHERE a = 10; 3777 3778SELECT * FROM v2 WHERE a IN ( SELECT a FROM t2 ); 3779--error ER_VIEW_NO_EXPLAIN 3780EXPLAIN SELECT * FROM v2 WHERE a IN ( SELECT a FROM t2 ); 3781--error ER_VIEW_NO_EXPLAIN 3782ANALYZE SELECT * FROM v2 WHERE a IN ( SELECT a FROM t2 ); 3783 3784 3785--echo 3786--echo #======================================================================== 3787--echo # Test: Grant DELETE, SHOW VIEW on the inner view 3788--echo #======================================================================== 3789--echo 3790 3791connection default; 3792REVOKE ALL PRIVILEGES ON privtest_db.v1 FROM 'privtest'@'localhost'; 3793GRANT SHOW VIEW, DELETE ON privtest_db.v1 TO 'privtest'@'localhost'; 3794connection con1; 3795 3796 3797--echo #------------------------------------------------------------------------ 3798--echo # I/R/U/D/S on the inner view 3799--echo # Expectation: Can run DELETE, EXPLAIN DELETE, UPDATE DELETE 3800--echo # which don't read any columns 3801--echo #------------------------------------------------------------------------ 3802 3803--error ER_TABLEACCESS_DENIED_ERROR 3804INSERT INTO v1 (a) VALUES (10); 3805--error ER_TABLEACCESS_DENIED_ERROR 3806EXPLAIN INSERT INTO v1 (a) VALUES (10); 3807--error ER_TABLEACCESS_DENIED_ERROR 3808ANALYZE INSERT INTO v1 (a) VALUES (10); 3809 3810--error ER_TABLEACCESS_DENIED_ERROR 3811INSERT INTO v1 SELECT * FROM t2; 3812--error ER_TABLEACCESS_DENIED_ERROR 3813EXPLAIN INSERT INTO v1 SELECT * FROM t2; 3814--error ER_TABLEACCESS_DENIED_ERROR 3815ANALYZE INSERT INTO v1 SELECT * FROM t2; 3816 3817--error ER_TABLEACCESS_DENIED_ERROR 3818REPLACE INTO v1 (a) VALUES (10); 3819--error ER_TABLEACCESS_DENIED_ERROR 3820EXPLAIN REPLACE INTO v1 (a) VALUES (10); 3821--error ER_TABLEACCESS_DENIED_ERROR 3822ANALYZE REPLACE INTO v1 (a) VALUES (10); 3823 3824--error ER_TABLEACCESS_DENIED_ERROR 3825REPLACE INTO v1 SELECT * FROM t2; 3826--error ER_TABLEACCESS_DENIED_ERROR 3827EXPLAIN REPLACE INTO v1 SELECT * FROM t2; 3828--error ER_TABLEACCESS_DENIED_ERROR 3829ANALYZE REPLACE INTO v1 SELECT * FROM t2; 3830 3831--error ER_TABLEACCESS_DENIED_ERROR 3832UPDATE v1 SET a = 10; 3833--error ER_TABLEACCESS_DENIED_ERROR 3834EXPLAIN UPDATE v1 SET a = 10; 3835--error ER_TABLEACCESS_DENIED_ERROR 3836ANALYZE UPDATE v1 SET a = 10; 3837 3838--error ER_TABLEACCESS_DENIED_ERROR 3839UPDATE v1 SET a = a + 1; 3840--error ER_TABLEACCESS_DENIED_ERROR 3841EXPLAIN UPDATE v1 SET a = a + 1; 3842--error ER_TABLEACCESS_DENIED_ERROR 3843ANALYZE UPDATE v1 SET a = a + 1; 3844 3845--error ER_TABLEACCESS_DENIED_ERROR 3846UPDATE v1, t2 SET v1.a = v1.a + 1 WHERE v1.a = t2.a; 3847--error ER_TABLEACCESS_DENIED_ERROR 3848EXPLAIN UPDATE v1, t2 SET v1.a = v1.a + 1 WHERE v1.a = t2.a; 3849--error ER_TABLEACCESS_DENIED_ERROR 3850ANALYZE UPDATE v1, t2 SET v1.a = v1.a + 1 WHERE v1.a = t2.a; 3851 3852DELETE FROM v1; 3853EXPLAIN DELETE FROM v1; 3854ANALYZE DELETE FROM v1; 3855 3856--error ER_COLUMNACCESS_DENIED_ERROR 3857DELETE FROM v1 WHERE a = 10; 3858--error ER_COLUMNACCESS_DENIED_ERROR 3859EXPLAIN DELETE FROM v1 WHERE a = 10; 3860--error ER_COLUMNACCESS_DENIED_ERROR 3861ANALYZE DELETE FROM v1 WHERE a = 10; 3862 3863# Unexpected error due to MDEV-7043 3864--error ER_TABLEACCESS_DENIED_ERROR 3865DELETE FROM v1 USING v1, t2; 3866# Unexpected error due to MDEV-7043 3867--error ER_TABLEACCESS_DENIED_ERROR 3868EXPLAIN DELETE FROM v1 USING v1, t2; 3869# Unexpected error due to MDEV-7043 3870--error ER_TABLEACCESS_DENIED_ERROR 3871ANALYZE DELETE FROM v1 USING v1, t2; 3872 3873# Unexpected error code due to MDEV-7043 3874--error ER_TABLEACCESS_DENIED_ERROR 3875EXPLAIN DELETE FROM v1 USING v1, t2 WHERE v1.a = t2.a; 3876# Unexpected error code due to MDEV-7043 3877--error ER_TABLEACCESS_DENIED_ERROR 3878EXPLAIN DELETE FROM v1 USING v1, t2 WHERE v1.a = t2.a; 3879# Unexpected error code due to MDEV-7043 3880--error ER_TABLEACCESS_DENIED_ERROR 3881ANALYZE DELETE FROM v1 USING v1, t2 WHERE v1.a = t2.a; 3882 3883--error ER_TABLEACCESS_DENIED_ERROR 3884SELECT * FROM v1; 3885--error ER_TABLEACCESS_DENIED_ERROR 3886EXPLAIN SELECT * FROM v1; 3887--error ER_TABLEACCESS_DENIED_ERROR 3888ANALYZE SELECT * FROM v1; 3889 3890--error ER_TABLEACCESS_DENIED_ERROR 3891SELECT * FROM v1 WHERE a = 10; 3892--error ER_TABLEACCESS_DENIED_ERROR 3893EXPLAIN SELECT * FROM v1 WHERE a = 10; 3894--error ER_TABLEACCESS_DENIED_ERROR 3895ANALYZE SELECT * FROM v1 WHERE a = 10; 3896 3897--error ER_TABLEACCESS_DENIED_ERROR 3898SELECT * FROM v1 WHERE a IN ( SELECT a FROM t2 ); 3899--error ER_TABLEACCESS_DENIED_ERROR 3900EXPLAIN SELECT * FROM v1 WHERE a IN ( SELECT a FROM t2 ); 3901--error ER_TABLEACCESS_DENIED_ERROR 3902ANALYZE SELECT * FROM v1 WHERE a IN ( SELECT a FROM t2 ); 3903 3904 3905--echo #------------------------------------------------------------------------ 3906--echo # I/R/U/D/S on the outer view 3907--echo # Expectation: Can run the queries, but not EXPLAIN or ANALYZE 3908--echo # because the query plan cannot be shown 3909--echo # (that's how it works now) 3910--echo #------------------------------------------------------------------------ 3911 3912 3913INSERT INTO v2 (a) VALUES (10); 3914--error ER_VIEW_NO_EXPLAIN 3915EXPLAIN INSERT INTO v2 (a) VALUES (10); 3916--error ER_VIEW_NO_EXPLAIN 3917ANALYZE INSERT INTO v2 (a) VALUES (10); 3918 3919INSERT INTO v2 SELECT * FROM t2; 3920--error ER_VIEW_NO_EXPLAIN 3921EXPLAIN INSERT INTO v2 SELECT * FROM t2; 3922--error ER_VIEW_NO_EXPLAIN 3923ANALYZE INSERT INTO v2 SELECT * FROM t2; 3924 3925REPLACE INTO v2 (a) VALUES (10); 3926--error ER_VIEW_NO_EXPLAIN 3927EXPLAIN REPLACE INTO v2 (a) VALUES (10); 3928--error ER_VIEW_NO_EXPLAIN 3929ANALYZE REPLACE INTO v2 (a) VALUES (10); 3930 3931REPLACE INTO v2 SELECT * FROM t2; 3932--error ER_VIEW_NO_EXPLAIN 3933EXPLAIN REPLACE INTO v2 SELECT * FROM t2; 3934--error ER_VIEW_NO_EXPLAIN 3935ANALYZE REPLACE INTO v2 SELECT * FROM t2; 3936 3937UPDATE v2 SET a = 10; 3938--error ER_VIEW_NO_EXPLAIN 3939EXPLAIN UPDATE v2 SET a = 10; 3940--error ER_VIEW_NO_EXPLAIN 3941ANALYZE UPDATE v2 SET a = 10; 3942 3943UPDATE v2 SET a = a + 1; 3944--error ER_VIEW_NO_EXPLAIN 3945EXPLAIN UPDATE v2 SET a = a + 1; 3946--error ER_VIEW_NO_EXPLAIN 3947ANALYZE UPDATE v2 SET a = a + 1; 3948 3949UPDATE v2, t2 SET v2.a = v2.a + 1 WHERE v2.a = t2.a; 3950--error ER_VIEW_NO_EXPLAIN 3951EXPLAIN UPDATE v2, t2 SET v2.a = v2.a + 1 WHERE v2.a = t2.a; 3952--error ER_VIEW_NO_EXPLAIN 3953ANALYZE UPDATE v2, t2 SET v2.a = v2.a + 1 WHERE v2.a = t2.a; 3954 3955DELETE FROM v2; 3956--error ER_VIEW_NO_EXPLAIN 3957EXPLAIN DELETE FROM v2; 3958--error ER_VIEW_NO_EXPLAIN 3959ANALYZE DELETE FROM v2; 3960 3961DELETE FROM v2 WHERE a = 10; 3962--error ER_VIEW_NO_EXPLAIN 3963EXPLAIN DELETE FROM v2 WHERE a = 10; 3964--error ER_VIEW_NO_EXPLAIN 3965ANALYZE DELETE FROM v2 WHERE a = 10; 3966 3967DELETE FROM v2 USING v2, t2; 3968--error ER_VIEW_NO_EXPLAIN 3969EXPLAIN DELETE FROM v2 USING v2, t2; 3970--error ER_VIEW_NO_EXPLAIN 3971ANALYZE DELETE FROM v2 USING v2, t2; 3972 3973DELETE FROM v2 USING v2, t2 WHERE v2.a = t2.a; 3974--error ER_VIEW_NO_EXPLAIN 3975EXPLAIN DELETE FROM v2 USING v2, t2 WHERE v2.a = t2.a; 3976--error ER_VIEW_NO_EXPLAIN 3977ANALYZE DELETE FROM v2 USING v2, t2 WHERE v2.a = t2.a; 3978 3979SELECT * FROM v2; 3980--error ER_VIEW_NO_EXPLAIN 3981EXPLAIN SELECT * FROM v2; 3982--error ER_VIEW_NO_EXPLAIN 3983ANALYZE SELECT * FROM v2; 3984 3985SELECT * FROM v2 WHERE a = 10; 3986--error ER_VIEW_NO_EXPLAIN 3987EXPLAIN SELECT * FROM v2 WHERE a = 10; 3988--error ER_VIEW_NO_EXPLAIN 3989ANALYZE SELECT * FROM v2 WHERE a = 10; 3990 3991SELECT * FROM v2 WHERE a IN ( SELECT a FROM t2 ); 3992--error ER_VIEW_NO_EXPLAIN 3993EXPLAIN SELECT * FROM v2 WHERE a IN ( SELECT a FROM t2 ); 3994--error ER_VIEW_NO_EXPLAIN 3995ANALYZE SELECT * FROM v2 WHERE a IN ( SELECT a FROM t2 ); 3996 3997--echo 3998--echo #======================================================================== 3999--echo # Test: Grant SELECT, INSERT, UPDATE, DELETE, SHOW VIEW 4000--echo #======================================================================== 4001--echo 4002 4003connection default; 4004REVOKE ALL PRIVILEGES ON privtest_db.v1 FROM 'privtest'@'localhost'; 4005GRANT SELECT, INSERT, UPDATE, DELETE, SHOW VIEW ON privtest_db.v1 TO 'privtest'@'localhost'; 4006connection con1; 4007 4008 4009--echo #------------------------------------------------------------------------ 4010--echo # I/R/U/D/S on the inner view 4011--echo # Expectation: Can run everything 4012--echo #------------------------------------------------------------------------ 4013 4014INSERT INTO v1 (a) VALUES (10); 4015EXPLAIN INSERT INTO v1 (a) VALUES (10); 4016ANALYZE INSERT INTO v1 (a) VALUES (10); 4017 4018INSERT INTO v1 SELECT * FROM t2; 4019EXPLAIN INSERT INTO v1 SELECT * FROM t2; 4020ANALYZE INSERT INTO v1 SELECT * FROM t2; 4021 4022REPLACE INTO v1 (a) VALUES (10); 4023EXPLAIN REPLACE INTO v1 (a) VALUES (10); 4024ANALYZE REPLACE INTO v1 (a) VALUES (10); 4025 4026REPLACE INTO v1 SELECT * FROM t2; 4027EXPLAIN REPLACE INTO v1 SELECT * FROM t2; 4028ANALYZE REPLACE INTO v1 SELECT * FROM t2; 4029 4030UPDATE v1 SET a = 10; 4031EXPLAIN UPDATE v1 SET a = 10; 4032ANALYZE UPDATE v1 SET a = 10; 4033 4034UPDATE v1 SET a = a + 1; 4035EXPLAIN UPDATE v1 SET a = a + 1; 4036ANALYZE UPDATE v1 SET a = a + 1; 4037 4038UPDATE v1, t2 SET v1.a = v1.a + 1 WHERE v1.a = t2.a; 4039EXPLAIN UPDATE v1, t2 SET v1.a = v1.a + 1 WHERE v1.a = t2.a; 4040ANALYZE UPDATE v1, t2 SET v1.a = v1.a + 1 WHERE v1.a = t2.a; 4041 4042DELETE FROM v1; 4043EXPLAIN DELETE FROM v1; 4044ANALYZE DELETE FROM v1; 4045 4046DELETE FROM v1 WHERE a = 10; 4047EXPLAIN DELETE FROM v1 WHERE a = 10; 4048ANALYZE DELETE FROM v1 WHERE a = 10; 4049 4050DELETE FROM v1 USING v1, t2; 4051EXPLAIN DELETE FROM v1 USING v1, t2; 4052ANALYZE DELETE FROM v1 USING v1, t2; 4053 4054DELETE FROM v1 USING v1, t2 WHERE v1.a = t2.a; 4055EXPLAIN DELETE FROM v1 USING v1, t2 WHERE v1.a = t2.a; 4056ANALYZE DELETE FROM v1 USING v1, t2 WHERE v1.a = t2.a; 4057 4058SELECT * FROM v1; 4059EXPLAIN SELECT * FROM v1; 4060ANALYZE SELECT * FROM v1; 4061 4062SELECT * FROM v1 WHERE a = 10; 4063EXPLAIN SELECT * FROM v1 WHERE a = 10; 4064ANALYZE SELECT * FROM v1 WHERE a = 10; 4065 4066SELECT * FROM v1 WHERE a IN ( SELECT a FROM t2 ); 4067EXPLAIN SELECT * FROM v1 WHERE a IN ( SELECT a FROM t2 ); 4068ANALYZE SELECT * FROM v1 WHERE a IN ( SELECT a FROM t2 ); 4069 4070 4071--echo #------------------------------------------------------------------------ 4072--echo # I/R/U/D/S on the outer view 4073--echo # Expectation: Can run everything 4074--echo #------------------------------------------------------------------------ 4075 4076INSERT INTO v2 (a) VALUES (10); 4077EXPLAIN INSERT INTO v2 (a) VALUES (10); 4078ANALYZE INSERT INTO v2 (a) VALUES (10); 4079 4080INSERT INTO v2 SELECT * FROM t2; 4081EXPLAIN INSERT INTO v2 SELECT * FROM t2; 4082ANALYZE INSERT INTO v2 SELECT * FROM t2; 4083 4084REPLACE INTO v2 (a) VALUES (10); 4085EXPLAIN REPLACE INTO v2 (a) VALUES (10); 4086ANALYZE REPLACE INTO v2 (a) VALUES (10); 4087 4088REPLACE INTO v2 SELECT * FROM t2; 4089EXPLAIN REPLACE INTO v2 SELECT * FROM t2; 4090ANALYZE REPLACE INTO v2 SELECT * FROM t2; 4091 4092UPDATE v2 SET a = 10; 4093EXPLAIN UPDATE v2 SET a = 10; 4094ANALYZE UPDATE v2 SET a = 10; 4095 4096UPDATE v2 SET a = a + 1; 4097EXPLAIN UPDATE v2 SET a = a + 1; 4098ANALYZE UPDATE v2 SET a = a + 1; 4099 4100UPDATE v2, t2 SET v2.a = v2.a + 1 WHERE v2.a = t2.a; 4101EXPLAIN UPDATE v2, t2 SET v2.a = v2.a + 1 WHERE v2.a = t2.a; 4102ANALYZE UPDATE v2, t2 SET v2.a = v2.a + 1 WHERE v2.a = t2.a; 4103 4104DELETE FROM v2; 4105EXPLAIN DELETE FROM v2; 4106ANALYZE DELETE FROM v2; 4107 4108DELETE FROM v2 WHERE a = 10; 4109EXPLAIN DELETE FROM v2 WHERE a = 10; 4110ANALYZE DELETE FROM v2 WHERE a = 10; 4111 4112DELETE FROM v2 USING v2, t2; 4113# Commented due to MDEV-7034 4114# EXPLAIN DELETE FROM v2 USING v2, t2; 4115ANALYZE DELETE FROM v2 USING v2, t2; 4116 4117DELETE FROM v2 USING v2, t2 WHERE v2.a = t2.a; 4118# Commented due to MDEV-7034 4119# EXPLAIN DELETE FROM v2 USING v2, t2 WHERE v2.a = t2.a; 4120ANALYZE DELETE FROM v2 USING v2, t2 WHERE v2.a = t2.a; 4121 4122SELECT * FROM v2; 4123EXPLAIN SELECT * FROM v2; 4124ANALYZE SELECT * FROM v2; 4125 4126SELECT * FROM v2 WHERE a = 10; 4127EXPLAIN SELECT * FROM v2 WHERE a = 10; 4128ANALYZE SELECT * FROM v2 WHERE a = 10; 4129 4130SELECT * FROM v2 WHERE a IN ( SELECT a FROM t2 ); 4131EXPLAIN SELECT * FROM v2 WHERE a IN ( SELECT a FROM t2 ); 4132ANALYZE SELECT * FROM v2 WHERE a IN ( SELECT a FROM t2 ); 4133 4134 4135 4136--echo 4137--echo ######################################################################### 4138--echo # Outer view permission tests 4139--echo # (we modify permissions on the outer view, keeping ALL the rest) 4140--echo ######################################################################### 4141--echo 4142 4143--echo 4144--echo #======================================================================== 4145--echo # Test: No permissions on the outer view 4146--echo #======================================================================== 4147--echo 4148 4149connection default; 4150GRANT ALL ON privtest_db.t1 TO 'privtest'@'localhost'; 4151GRANT ALL ON privtest_db.v1 TO 'privtest'@'localhost'; 4152REVOKE ALL PRIVILEGES ON privtest_db.v2 FROM 'privtest'@'localhost'; 4153connection con1; 4154 4155 4156--echo #------------------------------------------------------------------------ 4157--echo # I/R/U/D/S on the outer view 4158--echo # Expectation: Cannot run anything 4159--echo #------------------------------------------------------------------------ 4160 4161--error ER_TABLEACCESS_DENIED_ERROR 4162INSERT INTO v2 (a) VALUES (10); 4163--error ER_TABLEACCESS_DENIED_ERROR 4164EXPLAIN INSERT INTO v2 (a) VALUES (10); 4165--error ER_TABLEACCESS_DENIED_ERROR 4166ANALYZE INSERT INTO v2 (a) VALUES (10); 4167 4168--error ER_TABLEACCESS_DENIED_ERROR 4169INSERT INTO v2 SELECT * FROM t2; 4170--error ER_TABLEACCESS_DENIED_ERROR 4171EXPLAIN INSERT INTO v2 SELECT * FROM t2; 4172--error ER_TABLEACCESS_DENIED_ERROR 4173ANALYZE INSERT INTO v2 SELECT * FROM t2; 4174 4175--error ER_TABLEACCESS_DENIED_ERROR 4176REPLACE INTO v2 (a) VALUES (10); 4177--error ER_TABLEACCESS_DENIED_ERROR 4178EXPLAIN REPLACE INTO v2 (a) VALUES (10); 4179--error ER_TABLEACCESS_DENIED_ERROR 4180ANALYZE REPLACE INTO v2 (a) VALUES (10); 4181 4182--error ER_TABLEACCESS_DENIED_ERROR 4183REPLACE INTO v2 SELECT * FROM t2; 4184--error ER_TABLEACCESS_DENIED_ERROR 4185EXPLAIN REPLACE INTO v2 SELECT * FROM t2; 4186--error ER_TABLEACCESS_DENIED_ERROR 4187ANALYZE REPLACE INTO v2 SELECT * FROM t2; 4188 4189--error ER_TABLEACCESS_DENIED_ERROR 4190UPDATE v2 SET a = 10; 4191--error ER_TABLEACCESS_DENIED_ERROR 4192EXPLAIN UPDATE v2 SET a = 10; 4193--error ER_TABLEACCESS_DENIED_ERROR 4194ANALYZE UPDATE v2 SET a = 10; 4195 4196--error ER_TABLEACCESS_DENIED_ERROR 4197UPDATE v2 SET a = a + 1; 4198--error ER_TABLEACCESS_DENIED_ERROR 4199EXPLAIN UPDATE v2 SET a = a + 1; 4200--error ER_TABLEACCESS_DENIED_ERROR 4201ANALYZE UPDATE v2 SET a = a + 1; 4202 4203--error ER_TABLEACCESS_DENIED_ERROR 4204UPDATE v2, t2 SET v2.a = v2.a + 1 WHERE v2.a = t2.a; 4205--error ER_TABLEACCESS_DENIED_ERROR 4206EXPLAIN UPDATE v2, t2 SET v2.a = v2.a + 1 WHERE v2.a = t2.a; 4207--error ER_TABLEACCESS_DENIED_ERROR 4208ANALYZE UPDATE v2, t2 SET v2.a = v2.a + 1 WHERE v2.a = t2.a; 4209 4210--error ER_TABLEACCESS_DENIED_ERROR 4211DELETE FROM v2; 4212--error ER_TABLEACCESS_DENIED_ERROR 4213EXPLAIN DELETE FROM v2; 4214--error ER_TABLEACCESS_DENIED_ERROR 4215ANALYZE DELETE FROM v2; 4216 4217--error ER_TABLEACCESS_DENIED_ERROR 4218DELETE FROM v2 WHERE a = 10; 4219--error ER_TABLEACCESS_DENIED_ERROR 4220EXPLAIN DELETE FROM v2 WHERE a = 10; 4221--error ER_TABLEACCESS_DENIED_ERROR 4222ANALYZE DELETE FROM v2 WHERE a = 10; 4223 4224--error ER_TABLEACCESS_DENIED_ERROR 4225DELETE FROM v2 USING v2, t2; 4226--error ER_TABLEACCESS_DENIED_ERROR 4227EXPLAIN DELETE FROM v2 USING v2, t2; 4228--error ER_TABLEACCESS_DENIED_ERROR 4229ANALYZE DELETE FROM v2 USING v2, t2; 4230 4231--error ER_TABLEACCESS_DENIED_ERROR 4232DELETE FROM v2 USING v2, t2 WHERE v2.a = t2.a; 4233--error ER_TABLEACCESS_DENIED_ERROR 4234EXPLAIN DELETE FROM v2 USING v2, t2 WHERE v2.a = t2.a; 4235--error ER_TABLEACCESS_DENIED_ERROR 4236ANALYZE DELETE FROM v2 USING v2, t2 WHERE v2.a = t2.a; 4237 4238--error ER_TABLEACCESS_DENIED_ERROR 4239SELECT * FROM v2; 4240--error ER_TABLEACCESS_DENIED_ERROR 4241EXPLAIN SELECT * FROM v2; 4242--error ER_TABLEACCESS_DENIED_ERROR 4243ANALYZE SELECT * FROM v2; 4244 4245--error ER_TABLEACCESS_DENIED_ERROR 4246SELECT * FROM v2 WHERE a = 10; 4247--error ER_TABLEACCESS_DENIED_ERROR 4248EXPLAIN SELECT * FROM v2 WHERE a = 10; 4249--error ER_TABLEACCESS_DENIED_ERROR 4250ANALYZE SELECT * FROM v2 WHERE a = 10; 4251 4252--error ER_TABLEACCESS_DENIED_ERROR 4253SELECT * FROM v2 WHERE a IN ( SELECT a FROM t2 ); 4254--error ER_TABLEACCESS_DENIED_ERROR 4255EXPLAIN SELECT * FROM v2 WHERE a IN ( SELECT a FROM t2 ); 4256--error ER_TABLEACCESS_DENIED_ERROR 4257ANALYZE SELECT * FROM v2 WHERE a IN ( SELECT a FROM t2 ); 4258 4259 4260--echo 4261--echo #======================================================================== 4262--echo # Test: Grant SHOW VIEW on the outer view 4263--echo #======================================================================== 4264--echo 4265 4266connection default; 4267GRANT SHOW VIEW ON privtest_db.v2 TO 'privtest'@'localhost'; 4268connection con1; 4269 4270 4271--echo #------------------------------------------------------------------------ 4272--echo # I/R/U/D/S on the outer view 4273--echo # Expectation: Cannot run anything 4274--echo #------------------------------------------------------------------------ 4275 4276--error ER_TABLEACCESS_DENIED_ERROR 4277INSERT INTO v2 (a) VALUES (10); 4278--error ER_TABLEACCESS_DENIED_ERROR 4279EXPLAIN INSERT INTO v2 (a) VALUES (10); 4280--error ER_TABLEACCESS_DENIED_ERROR 4281ANALYZE INSERT INTO v2 (a) VALUES (10); 4282 4283--error ER_TABLEACCESS_DENIED_ERROR 4284INSERT INTO v2 SELECT * FROM t2; 4285--error ER_TABLEACCESS_DENIED_ERROR 4286EXPLAIN INSERT INTO v2 SELECT * FROM t2; 4287--error ER_TABLEACCESS_DENIED_ERROR 4288ANALYZE INSERT INTO v2 SELECT * FROM t2; 4289 4290--error ER_TABLEACCESS_DENIED_ERROR 4291REPLACE INTO v2 (a) VALUES (10); 4292--error ER_TABLEACCESS_DENIED_ERROR 4293EXPLAIN REPLACE INTO v2 (a) VALUES (10); 4294--error ER_TABLEACCESS_DENIED_ERROR 4295ANALYZE REPLACE INTO v2 (a) VALUES (10); 4296 4297--error ER_TABLEACCESS_DENIED_ERROR 4298REPLACE INTO v2 SELECT * FROM t2; 4299--error ER_TABLEACCESS_DENIED_ERROR 4300EXPLAIN REPLACE INTO v2 SELECT * FROM t2; 4301--error ER_TABLEACCESS_DENIED_ERROR 4302ANALYZE REPLACE INTO v2 SELECT * FROM t2; 4303 4304--error ER_TABLEACCESS_DENIED_ERROR 4305UPDATE v2 SET a = 10; 4306--error ER_TABLEACCESS_DENIED_ERROR 4307EXPLAIN UPDATE v2 SET a = 10; 4308--error ER_TABLEACCESS_DENIED_ERROR 4309ANALYZE UPDATE v2 SET a = 10; 4310 4311--error ER_TABLEACCESS_DENIED_ERROR 4312UPDATE v2 SET a = a + 1; 4313--error ER_TABLEACCESS_DENIED_ERROR 4314EXPLAIN UPDATE v2 SET a = a + 1; 4315--error ER_TABLEACCESS_DENIED_ERROR 4316ANALYZE UPDATE v2 SET a = a + 1; 4317 4318--error ER_TABLEACCESS_DENIED_ERROR 4319UPDATE v2, t2 SET v2.a = v2.a + 1 WHERE v2.a = t2.a; 4320--error ER_TABLEACCESS_DENIED_ERROR 4321EXPLAIN UPDATE v2, t2 SET v2.a = v2.a + 1 WHERE v2.a = t2.a; 4322--error ER_TABLEACCESS_DENIED_ERROR 4323ANALYZE UPDATE v2, t2 SET v2.a = v2.a + 1 WHERE v2.a = t2.a; 4324 4325--error ER_TABLEACCESS_DENIED_ERROR 4326DELETE FROM v2; 4327--error ER_TABLEACCESS_DENIED_ERROR 4328EXPLAIN DELETE FROM v2; 4329--error ER_TABLEACCESS_DENIED_ERROR 4330ANALYZE DELETE FROM v2; 4331 4332--error ER_TABLEACCESS_DENIED_ERROR 4333DELETE FROM v2 WHERE a = 10; 4334--error ER_TABLEACCESS_DENIED_ERROR 4335EXPLAIN DELETE FROM v2 WHERE a = 10; 4336--error ER_TABLEACCESS_DENIED_ERROR 4337ANALYZE DELETE FROM v2 WHERE a = 10; 4338 4339--error ER_TABLEACCESS_DENIED_ERROR 4340DELETE FROM v2 USING v2, t2; 4341--error ER_TABLEACCESS_DENIED_ERROR 4342EXPLAIN DELETE FROM v2 USING v2, t2; 4343--error ER_TABLEACCESS_DENIED_ERROR 4344ANALYZE DELETE FROM v2 USING v2, t2; 4345 4346--error ER_TABLEACCESS_DENIED_ERROR 4347DELETE FROM v2 USING v2, t2 WHERE v2.a = t2.a; 4348--error ER_TABLEACCESS_DENIED_ERROR 4349EXPLAIN DELETE FROM v2 USING v2, t2 WHERE v2.a = t2.a; 4350--error ER_TABLEACCESS_DENIED_ERROR 4351ANALYZE DELETE FROM v2 USING v2, t2 WHERE v2.a = t2.a; 4352 4353--error ER_TABLEACCESS_DENIED_ERROR 4354SELECT * FROM v2; 4355--error ER_TABLEACCESS_DENIED_ERROR 4356EXPLAIN SELECT * FROM v2; 4357--error ER_TABLEACCESS_DENIED_ERROR 4358ANALYZE SELECT * FROM v2; 4359 4360--error ER_TABLEACCESS_DENIED_ERROR 4361SELECT * FROM v2 WHERE a = 10; 4362--error ER_TABLEACCESS_DENIED_ERROR 4363EXPLAIN SELECT * FROM v2 WHERE a = 10; 4364--error ER_TABLEACCESS_DENIED_ERROR 4365ANALYZE SELECT * FROM v2 WHERE a = 10; 4366 4367--error ER_TABLEACCESS_DENIED_ERROR 4368SELECT * FROM v2 WHERE a IN ( SELECT a FROM t2 ); 4369--error ER_TABLEACCESS_DENIED_ERROR 4370EXPLAIN SELECT * FROM v2 WHERE a IN ( SELECT a FROM t2 ); 4371--error ER_TABLEACCESS_DENIED_ERROR 4372ANALYZE SELECT * FROM v2 WHERE a IN ( SELECT a FROM t2 ); 4373 4374--echo 4375--echo #======================================================================== 4376--echo # Test: Grant SHOW VIEW, SELECT(a) on the outer view 4377--echo #======================================================================== 4378--echo 4379 4380connection default; 4381REVOKE ALL PRIVILEGES ON privtest_db.v2 FROM 'privtest'@'localhost'; 4382GRANT SELECT(a), SHOW VIEW ON privtest_db.v2 TO 'privtest'@'localhost'; 4383connection con1; 4384 4385 4386--echo #------------------------------------------------------------------------ 4387--echo # I/R/U/D/S on the outer view 4388--echo # Expectation: Can run SELECT, EXPLAIN SELECT and ANALYZE SELECT 4389--echo # when only `a` column is involved 4390--echo #------------------------------------------------------------------------ 4391 4392 4393--error ER_TABLEACCESS_DENIED_ERROR 4394INSERT INTO v2 (a) VALUES (10); 4395--error ER_TABLEACCESS_DENIED_ERROR 4396EXPLAIN INSERT INTO v2 (a) VALUES (10); 4397--error ER_TABLEACCESS_DENIED_ERROR 4398ANALYZE INSERT INTO v2 (a) VALUES (10); 4399 4400--error ER_TABLEACCESS_DENIED_ERROR 4401INSERT INTO v2 SELECT * FROM t2; 4402--error ER_TABLEACCESS_DENIED_ERROR 4403EXPLAIN INSERT INTO v2 SELECT * FROM t2; 4404--error ER_TABLEACCESS_DENIED_ERROR 4405ANALYZE INSERT INTO v2 SELECT * FROM t2; 4406 4407--error ER_TABLEACCESS_DENIED_ERROR 4408REPLACE INTO v2 (a) VALUES (10); 4409--error ER_TABLEACCESS_DENIED_ERROR 4410EXPLAIN REPLACE INTO v2 (a) VALUES (10); 4411--error ER_TABLEACCESS_DENIED_ERROR 4412ANALYZE REPLACE INTO v2 (a) VALUES (10); 4413 4414--error ER_TABLEACCESS_DENIED_ERROR 4415REPLACE INTO v2 SELECT * FROM t2; 4416--error ER_TABLEACCESS_DENIED_ERROR 4417EXPLAIN REPLACE INTO v2 SELECT * FROM t2; 4418--error ER_TABLEACCESS_DENIED_ERROR 4419ANALYZE REPLACE INTO v2 SELECT * FROM t2; 4420 4421--error ER_TABLEACCESS_DENIED_ERROR 4422UPDATE v2 SET a = 10; 4423--error ER_TABLEACCESS_DENIED_ERROR 4424EXPLAIN UPDATE v2 SET a = 10; 4425--error ER_TABLEACCESS_DENIED_ERROR 4426ANALYZE UPDATE v2 SET a = 10; 4427 4428--error ER_TABLEACCESS_DENIED_ERROR 4429UPDATE v2 SET a = a + 1; 4430--error ER_TABLEACCESS_DENIED_ERROR 4431EXPLAIN UPDATE v2 SET a = a + 1; 4432--error ER_TABLEACCESS_DENIED_ERROR 4433ANALYZE UPDATE v2 SET a = a + 1; 4434 4435--error ER_TABLEACCESS_DENIED_ERROR 4436UPDATE v2, t2 SET v2.a = v2.a + 1 WHERE v2.a = t2.a; 4437--error ER_TABLEACCESS_DENIED_ERROR 4438EXPLAIN UPDATE v2, t2 SET v2.a = v2.a + 1 WHERE v2.a = t2.a; 4439--error ER_TABLEACCESS_DENIED_ERROR 4440ANALYZE UPDATE v2, t2 SET v2.a = v2.a + 1 WHERE v2.a = t2.a; 4441 4442--error ER_TABLEACCESS_DENIED_ERROR 4443DELETE FROM v2; 4444--error ER_TABLEACCESS_DENIED_ERROR 4445EXPLAIN DELETE FROM v2; 4446--error ER_TABLEACCESS_DENIED_ERROR 4447ANALYZE DELETE FROM v2; 4448 4449--error ER_TABLEACCESS_DENIED_ERROR 4450DELETE FROM v2 WHERE a = 10; 4451--error ER_TABLEACCESS_DENIED_ERROR 4452EXPLAIN DELETE FROM v2 WHERE a = 10; 4453--error ER_TABLEACCESS_DENIED_ERROR 4454ANALYZE DELETE FROM v2 WHERE a = 10; 4455 4456--error ER_TABLEACCESS_DENIED_ERROR 4457DELETE FROM v2 USING v2, t2; 4458--error ER_TABLEACCESS_DENIED_ERROR 4459EXPLAIN DELETE FROM v2 USING v2, t2; 4460--error ER_TABLEACCESS_DENIED_ERROR 4461ANALYZE DELETE FROM v2 USING v2, t2; 4462 4463--error ER_TABLEACCESS_DENIED_ERROR 4464DELETE FROM v2 USING v2, t2 WHERE v2.a = t2.a; 4465--error ER_TABLEACCESS_DENIED_ERROR 4466EXPLAIN DELETE FROM v2 USING v2, t2 WHERE v2.a = t2.a; 4467--error ER_TABLEACCESS_DENIED_ERROR 4468ANALYZE DELETE FROM v2 USING v2, t2 WHERE v2.a = t2.a; 4469 4470SELECT a FROM v2; 4471EXPLAIN SELECT a FROM v2; 4472ANALYZE SELECT a FROM v2; 4473 4474--error ER_TABLEACCESS_DENIED_ERROR 4475SELECT * FROM v2; 4476--error ER_TABLEACCESS_DENIED_ERROR 4477EXPLAIN SELECT * FROM v2; 4478--error ER_TABLEACCESS_DENIED_ERROR 4479ANALYZE SELECT * FROM v2; 4480 4481--error ER_TABLEACCESS_DENIED_ERROR 4482SELECT * FROM v2 WHERE a = 10; 4483--error ER_TABLEACCESS_DENIED_ERROR 4484EXPLAIN SELECT * FROM v2 WHERE a = 10; 4485--error ER_TABLEACCESS_DENIED_ERROR 4486ANALYZE SELECT * FROM v2 WHERE a = 10; 4487 4488SELECT a FROM v2 WHERE a = 10; 4489EXPLAIN SELECT a FROM v2 WHERE a = 10; 4490ANALYZE SELECT a FROM v2 WHERE a = 10; 4491 4492--error ER_TABLEACCESS_DENIED_ERROR 4493SELECT * FROM v2 WHERE a IN ( SELECT a FROM t2 ); 4494--error ER_TABLEACCESS_DENIED_ERROR 4495EXPLAIN SELECT * FROM v2 WHERE a IN ( SELECT a FROM t2 ); 4496--error ER_TABLEACCESS_DENIED_ERROR 4497ANALYZE SELECT * FROM v2 WHERE a IN ( SELECT a FROM t2 ); 4498 4499--echo 4500--echo #======================================================================== 4501--echo # Test: Grant SELECT on the outer view 4502--echo #======================================================================== 4503--echo 4504 4505connection default; 4506REVOKE ALL PRIVILEGES ON privtest_db.v2 FROM 'privtest'@'localhost'; 4507GRANT SELECT ON privtest_db.v2 TO 'privtest'@'localhost'; 4508connection con1; 4509 4510--echo #------------------------------------------------------------------------ 4511--echo # I/R/U/D/S on the inner view 4512--echo # Expectation: Can run SELECT, but not EXPLAIN SELECT or ANALYZE SELECT 4513--echo # because the query plan cannot be shown 4514--echo # (it could have revealed the structure of the view) 4515--echo #------------------------------------------------------------------------ 4516 4517 4518--error ER_TABLEACCESS_DENIED_ERROR 4519INSERT INTO v2 (a) VALUES (10); 4520--error ER_TABLEACCESS_DENIED_ERROR 4521EXPLAIN INSERT INTO v2 (a) VALUES (10); 4522--error ER_TABLEACCESS_DENIED_ERROR 4523ANALYZE INSERT INTO v2 (a) VALUES (10); 4524 4525--error ER_TABLEACCESS_DENIED_ERROR 4526INSERT INTO v2 SELECT * FROM t2; 4527--error ER_TABLEACCESS_DENIED_ERROR 4528EXPLAIN INSERT INTO v2 SELECT * FROM t2; 4529--error ER_TABLEACCESS_DENIED_ERROR 4530ANALYZE INSERT INTO v2 SELECT * FROM t2; 4531 4532--error ER_TABLEACCESS_DENIED_ERROR 4533REPLACE INTO v2 (a) VALUES (10); 4534--error ER_TABLEACCESS_DENIED_ERROR 4535EXPLAIN REPLACE INTO v2 (a) VALUES (10); 4536--error ER_TABLEACCESS_DENIED_ERROR 4537ANALYZE REPLACE INTO v2 (a) VALUES (10); 4538 4539--error ER_TABLEACCESS_DENIED_ERROR 4540REPLACE INTO v2 SELECT * FROM t2; 4541--error ER_TABLEACCESS_DENIED_ERROR 4542EXPLAIN REPLACE INTO v2 SELECT * FROM t2; 4543--error ER_TABLEACCESS_DENIED_ERROR 4544ANALYZE REPLACE INTO v2 SELECT * FROM t2; 4545 4546--error ER_TABLEACCESS_DENIED_ERROR 4547UPDATE v2 SET a = 10; 4548--error ER_TABLEACCESS_DENIED_ERROR 4549EXPLAIN UPDATE v2 SET a = 10; 4550--error ER_TABLEACCESS_DENIED_ERROR 4551ANALYZE UPDATE v2 SET a = 10; 4552 4553--error ER_TABLEACCESS_DENIED_ERROR 4554UPDATE v2 SET a = a + 1; 4555--error ER_TABLEACCESS_DENIED_ERROR 4556EXPLAIN UPDATE v2 SET a = a + 1; 4557--error ER_TABLEACCESS_DENIED_ERROR 4558ANALYZE UPDATE v2 SET a = a + 1; 4559 4560--error ER_TABLEACCESS_DENIED_ERROR 4561UPDATE v2, t2 SET v2.a = v2.a + 1 WHERE v2.a = t2.a; 4562# Strange error code due to MDEV-7033, MDEV-7042 4563# --error ER_TABLEACCESS_DENIED_ERROR 4564--error ER_VIEW_NO_EXPLAIN 4565EXPLAIN UPDATE v2, t2 SET v2.a = v2.a + 1 WHERE v2.a = t2.a; 4566--error ER_VIEW_NO_EXPLAIN 4567ANALYZE UPDATE v2, t2 SET v2.a = v2.a + 1 WHERE v2.a = t2.a; 4568 4569--error ER_TABLEACCESS_DENIED_ERROR 4570DELETE FROM v2; 4571--error ER_TABLEACCESS_DENIED_ERROR 4572EXPLAIN DELETE FROM v2; 4573--error ER_TABLEACCESS_DENIED_ERROR 4574ANALYZE DELETE FROM v2; 4575 4576--error ER_TABLEACCESS_DENIED_ERROR 4577DELETE FROM v2 WHERE a = 10; 4578--error ER_TABLEACCESS_DENIED_ERROR 4579EXPLAIN DELETE FROM v2 WHERE a = 10; 4580--error ER_TABLEACCESS_DENIED_ERROR 4581ANALYZE DELETE FROM v2 WHERE a = 10; 4582 4583--error ER_TABLEACCESS_DENIED_ERROR 4584DELETE FROM v2 USING v2, t2; 4585--error ER_TABLEACCESS_DENIED_ERROR 4586EXPLAIN DELETE FROM v2 USING v2, t2; 4587--error ER_TABLEACCESS_DENIED_ERROR 4588ANALYZE DELETE FROM v2 USING v2, t2; 4589 4590--error ER_TABLEACCESS_DENIED_ERROR 4591DELETE FROM v2 USING v2, t2 WHERE v2.a = t2.a; 4592--error ER_TABLEACCESS_DENIED_ERROR 4593EXPLAIN DELETE FROM v2 USING v2, t2 WHERE v2.a = t2.a; 4594--error ER_TABLEACCESS_DENIED_ERROR 4595ANALYZE DELETE FROM v2 USING v2, t2 WHERE v2.a = t2.a; 4596 4597SELECT * FROM v2; 4598--error ER_VIEW_NO_EXPLAIN 4599EXPLAIN SELECT * FROM v2; 4600--error ER_VIEW_NO_EXPLAIN 4601ANALYZE SELECT * FROM v2; 4602 4603SELECT * FROM v2 WHERE a = 10; 4604--error ER_VIEW_NO_EXPLAIN 4605EXPLAIN SELECT * FROM v2 WHERE a = 10; 4606--error ER_VIEW_NO_EXPLAIN 4607ANALYZE SELECT * FROM v2 WHERE a = 10; 4608 4609SELECT * FROM v2 WHERE a IN ( SELECT a FROM t2 ); 4610--error ER_VIEW_NO_EXPLAIN 4611EXPLAIN SELECT * FROM v2 WHERE a IN ( SELECT a FROM t2 ); 4612--error ER_VIEW_NO_EXPLAIN 4613ANALYZE SELECT * FROM v2 WHERE a IN ( SELECT a FROM t2 ); 4614 4615 4616--echo 4617--echo #======================================================================== 4618--echo # Test: Grant SHOW VIEW, SELECT on the outer view 4619--echo #======================================================================== 4620--echo 4621 4622connection default; 4623REVOKE ALL PRIVILEGES ON privtest_db.v2 FROM 'privtest'@'localhost'; 4624GRANT SELECT, SHOW VIEW ON privtest_db.v2 TO 'privtest'@'localhost'; 4625connection con1; 4626 4627 4628--echo #------------------------------------------------------------------------ 4629--echo # I/R/U/D/S on the inner view 4630--echo # Expectation: Can run SELECT, EXPLAIN SELECT, ANALYZE SELECT 4631--echo #------------------------------------------------------------------------ 4632 4633--error ER_TABLEACCESS_DENIED_ERROR 4634INSERT INTO v2 (a) VALUES (10); 4635--error ER_TABLEACCESS_DENIED_ERROR 4636EXPLAIN INSERT INTO v2 (a) VALUES (10); 4637--error ER_TABLEACCESS_DENIED_ERROR 4638ANALYZE INSERT INTO v2 (a) VALUES (10); 4639 4640--error ER_TABLEACCESS_DENIED_ERROR 4641INSERT INTO v2 SELECT * FROM t2; 4642--error ER_TABLEACCESS_DENIED_ERROR 4643EXPLAIN INSERT INTO v2 SELECT * FROM t2; 4644--error ER_TABLEACCESS_DENIED_ERROR 4645ANALYZE INSERT INTO v2 SELECT * FROM t2; 4646 4647--error ER_TABLEACCESS_DENIED_ERROR 4648REPLACE INTO v2 (a) VALUES (10); 4649--error ER_TABLEACCESS_DENIED_ERROR 4650EXPLAIN REPLACE INTO v2 (a) VALUES (10); 4651--error ER_TABLEACCESS_DENIED_ERROR 4652ANALYZE REPLACE INTO v2 (a) VALUES (10); 4653 4654--error ER_TABLEACCESS_DENIED_ERROR 4655REPLACE INTO v2 SELECT * FROM t2; 4656--error ER_TABLEACCESS_DENIED_ERROR 4657EXPLAIN REPLACE INTO v2 SELECT * FROM t2; 4658--error ER_TABLEACCESS_DENIED_ERROR 4659ANALYZE REPLACE INTO v2 SELECT * FROM t2; 4660 4661--error ER_TABLEACCESS_DENIED_ERROR 4662UPDATE v2 SET a = 10; 4663--error ER_TABLEACCESS_DENIED_ERROR 4664EXPLAIN UPDATE v2 SET a = 10; 4665--error ER_TABLEACCESS_DENIED_ERROR 4666ANALYZE UPDATE v2 SET a = 10; 4667 4668--error ER_TABLEACCESS_DENIED_ERROR 4669UPDATE v2 SET a = a + 1; 4670--error ER_TABLEACCESS_DENIED_ERROR 4671EXPLAIN UPDATE v2 SET a = a + 1; 4672--error ER_TABLEACCESS_DENIED_ERROR 4673ANALYZE UPDATE v2 SET a = a + 1; 4674 4675--error ER_TABLEACCESS_DENIED_ERROR 4676UPDATE v2, t2 SET v2.a = v2.a + 1 WHERE v2.a = t2.a; 4677--error ER_TABLEACCESS_DENIED_ERROR 4678EXPLAIN UPDATE v2, t2 SET v2.a = v2.a + 1 WHERE v2.a = t2.a; 4679--error ER_TABLEACCESS_DENIED_ERROR 4680ANALYZE UPDATE v2, t2 SET v2.a = v2.a + 1 WHERE v2.a = t2.a; 4681 4682--error ER_TABLEACCESS_DENIED_ERROR 4683DELETE FROM v2; 4684--error ER_TABLEACCESS_DENIED_ERROR 4685EXPLAIN DELETE FROM v2; 4686--error ER_TABLEACCESS_DENIED_ERROR 4687ANALYZE DELETE FROM v2; 4688 4689--error ER_TABLEACCESS_DENIED_ERROR 4690DELETE FROM v2 WHERE a = 10; 4691--error ER_TABLEACCESS_DENIED_ERROR 4692EXPLAIN DELETE FROM v2 WHERE a = 10; 4693--error ER_TABLEACCESS_DENIED_ERROR 4694ANALYZE DELETE FROM v2 WHERE a = 10; 4695 4696--error ER_TABLEACCESS_DENIED_ERROR 4697DELETE FROM v2 USING v2, t2; 4698--error ER_TABLEACCESS_DENIED_ERROR 4699EXPLAIN DELETE FROM v2 USING v2, t2; 4700--error ER_TABLEACCESS_DENIED_ERROR 4701ANALYZE DELETE FROM v2 USING v2, t2; 4702 4703--error ER_TABLEACCESS_DENIED_ERROR 4704DELETE FROM v2 USING v2, t2 WHERE v2.a = t2.a; 4705--error ER_TABLEACCESS_DENIED_ERROR 4706EXPLAIN DELETE FROM v2 USING v2, t2 WHERE v2.a = t2.a; 4707--error ER_TABLEACCESS_DENIED_ERROR 4708ANALYZE DELETE FROM v2 USING v2, t2 WHERE v2.a = t2.a; 4709 4710SELECT * FROM v2; 4711EXPLAIN SELECT * FROM v2; 4712ANALYZE SELECT * FROM v2; 4713 4714SELECT * FROM v2 WHERE a = 10; 4715EXPLAIN SELECT * FROM v2 WHERE a = 10; 4716ANALYZE SELECT * FROM v2 WHERE a = 10; 4717 4718SELECT * FROM v2 WHERE a IN ( SELECT a FROM t2 ); 4719EXPLAIN SELECT * FROM v2 WHERE a IN ( SELECT a FROM t2 ); 4720ANALYZE SELECT * FROM v2 WHERE a IN ( SELECT a FROM t2 ); 4721 4722--echo 4723--echo #======================================================================== 4724--echo # Test: Grant INSERT on the outer view 4725--echo #======================================================================== 4726--echo 4727--echo 4728 4729connection default; 4730REVOKE ALL PRIVILEGES ON privtest_db.v2 FROM 'privtest'@'localhost'; 4731GRANT INSERT ON privtest_db.v2 TO 'privtest'@'localhost'; 4732connection con1; 4733 4734--echo #------------------------------------------------------------------------ 4735--echo # I/R/U/D/S on the outer view 4736--echo # Expectation: Can run INSERT, but not EXPLAIN INSERT or ANALYZE INSERT 4737--echo # because the query plan cannot be shown 4738--echo # (it could have revealed the structure of the view) 4739--echo #------------------------------------------------------------------------ 4740 4741 4742INSERT INTO v2 (a) VALUES (10); 4743--error ER_VIEW_NO_EXPLAIN 4744EXPLAIN INSERT INTO v2 (a) VALUES (10); 4745--error ER_VIEW_NO_EXPLAIN 4746ANALYZE INSERT INTO v2 (a) VALUES (10); 4747 4748INSERT INTO v2 SELECT * FROM t2; 4749--error ER_VIEW_NO_EXPLAIN 4750EXPLAIN INSERT INTO v2 SELECT * FROM t2; 4751--error ER_VIEW_NO_EXPLAIN 4752ANALYZE INSERT INTO v2 SELECT * FROM t2; 4753 4754--error ER_TABLEACCESS_DENIED_ERROR 4755REPLACE INTO v2 (a) VALUES (10); 4756--error ER_TABLEACCESS_DENIED_ERROR 4757EXPLAIN REPLACE INTO v2 (a) VALUES (10); 4758--error ER_TABLEACCESS_DENIED_ERROR 4759ANALYZE REPLACE INTO v2 (a) VALUES (10); 4760 4761--error ER_TABLEACCESS_DENIED_ERROR 4762REPLACE INTO v2 SELECT * FROM t2; 4763--error ER_TABLEACCESS_DENIED_ERROR 4764EXPLAIN REPLACE INTO v2 SELECT * FROM t2; 4765--error ER_TABLEACCESS_DENIED_ERROR 4766ANALYZE REPLACE INTO v2 SELECT * FROM t2; 4767 4768--error ER_TABLEACCESS_DENIED_ERROR 4769UPDATE v2 SET a = 10; 4770--error ER_TABLEACCESS_DENIED_ERROR 4771EXPLAIN UPDATE v2 SET a = 10; 4772--error ER_TABLEACCESS_DENIED_ERROR 4773ANALYZE UPDATE v2 SET a = 10; 4774 4775--error ER_TABLEACCESS_DENIED_ERROR 4776UPDATE v2 SET a = a + 1; 4777--error ER_TABLEACCESS_DENIED_ERROR 4778EXPLAIN UPDATE v2 SET a = a + 1; 4779--error ER_TABLEACCESS_DENIED_ERROR 4780ANALYZE UPDATE v2 SET a = a + 1; 4781 4782--error ER_TABLEACCESS_DENIED_ERROR 4783UPDATE v2, t2 SET v2.a = v2.a + 1 WHERE v2.a = t2.a; 4784--error ER_TABLEACCESS_DENIED_ERROR 4785EXPLAIN UPDATE v2, t2 SET v2.a = v2.a + 1 WHERE v2.a = t2.a; 4786--error ER_TABLEACCESS_DENIED_ERROR 4787ANALYZE UPDATE v2, t2 SET v2.a = v2.a + 1 WHERE v2.a = t2.a; 4788 4789--error ER_TABLEACCESS_DENIED_ERROR 4790DELETE FROM v2; 4791--error ER_TABLEACCESS_DENIED_ERROR 4792EXPLAIN DELETE FROM v2; 4793--error ER_TABLEACCESS_DENIED_ERROR 4794ANALYZE DELETE FROM v2; 4795 4796--error ER_TABLEACCESS_DENIED_ERROR 4797DELETE FROM v2 WHERE a = 10; 4798--error ER_TABLEACCESS_DENIED_ERROR 4799EXPLAIN DELETE FROM v2 WHERE a = 10; 4800--error ER_TABLEACCESS_DENIED_ERROR 4801ANALYZE DELETE FROM v2 WHERE a = 10; 4802 4803--error ER_TABLEACCESS_DENIED_ERROR 4804DELETE FROM v2 USING v2, t2; 4805--error ER_TABLEACCESS_DENIED_ERROR 4806EXPLAIN DELETE FROM v2 USING v2, t2; 4807--error ER_TABLEACCESS_DENIED_ERROR 4808ANALYZE DELETE FROM v2 USING v2, t2; 4809 4810--error ER_TABLEACCESS_DENIED_ERROR 4811DELETE FROM v2 USING v2, t2 WHERE v2.a = t2.a; 4812--error ER_TABLEACCESS_DENIED_ERROR 4813EXPLAIN DELETE FROM v2 USING v2, t2 WHERE v2.a = t2.a; 4814--error ER_TABLEACCESS_DENIED_ERROR 4815ANALYZE DELETE FROM v2 USING v2, t2 WHERE v2.a = t2.a; 4816 4817--error ER_TABLEACCESS_DENIED_ERROR 4818SELECT * FROM v2; 4819--error ER_TABLEACCESS_DENIED_ERROR 4820EXPLAIN SELECT * FROM v2; 4821--error ER_TABLEACCESS_DENIED_ERROR 4822ANALYZE SELECT * FROM v2; 4823 4824--error ER_TABLEACCESS_DENIED_ERROR 4825SELECT * FROM v2 WHERE a = 10; 4826--error ER_TABLEACCESS_DENIED_ERROR 4827EXPLAIN SELECT * FROM v2 WHERE a = 10; 4828--error ER_TABLEACCESS_DENIED_ERROR 4829ANALYZE SELECT * FROM v2 WHERE a = 10; 4830 4831--error ER_TABLEACCESS_DENIED_ERROR 4832SELECT * FROM v2 WHERE a IN ( SELECT a FROM t2 ); 4833--error ER_TABLEACCESS_DENIED_ERROR 4834EXPLAIN SELECT * FROM v2 WHERE a IN ( SELECT a FROM t2 ); 4835--error ER_TABLEACCESS_DENIED_ERROR 4836ANALYZE SELECT * FROM v2 WHERE a IN ( SELECT a FROM t2 ); 4837 4838 4839--echo 4840--echo #======================================================================== 4841--echo # Test: Grant UPDATE on the outer view 4842--echo #======================================================================== 4843--echo 4844--echo 4845 4846connection default; 4847REVOKE ALL PRIVILEGES ON privtest_db.v2 FROM 'privtest'@'localhost'; 4848GRANT UPDATE ON privtest_db.v2 TO 'privtest'@'localhost'; 4849connection con1; 4850 4851--echo #------------------------------------------------------------------------ 4852--echo # I/R/U/D/S on the outer view 4853--echo # Expectation: Can run UPDATE which does not read any columns, 4854--echo # but not EXPLAIN UPDATE or ANALYZE UPDATE 4855--echo # because the query plan cannot be shown 4856--echo # (it could have revealed the structure of the view) 4857--echo #------------------------------------------------------------------------ 4858 4859 4860--error ER_TABLEACCESS_DENIED_ERROR 4861INSERT INTO v2 (a) VALUES (10); 4862--error ER_TABLEACCESS_DENIED_ERROR 4863EXPLAIN INSERT INTO v2 (a) VALUES (10); 4864--error ER_TABLEACCESS_DENIED_ERROR 4865ANALYZE INSERT INTO v2 (a) VALUES (10); 4866 4867--error ER_TABLEACCESS_DENIED_ERROR 4868INSERT INTO v2 SELECT * FROM t2; 4869--error ER_TABLEACCESS_DENIED_ERROR 4870EXPLAIN INSERT INTO v2 SELECT * FROM t2; 4871--error ER_TABLEACCESS_DENIED_ERROR 4872ANALYZE INSERT INTO v2 SELECT * FROM t2; 4873 4874--error ER_TABLEACCESS_DENIED_ERROR 4875REPLACE INTO v2 (a) VALUES (10); 4876--error ER_TABLEACCESS_DENIED_ERROR 4877EXPLAIN REPLACE INTO v2 (a) VALUES (10); 4878--error ER_TABLEACCESS_DENIED_ERROR 4879ANALYZE REPLACE INTO v2 (a) VALUES (10); 4880 4881--error ER_TABLEACCESS_DENIED_ERROR 4882REPLACE INTO v2 SELECT * FROM t2; 4883--error ER_TABLEACCESS_DENIED_ERROR 4884EXPLAIN REPLACE INTO v2 SELECT * FROM t2; 4885--error ER_TABLEACCESS_DENIED_ERROR 4886ANALYZE REPLACE INTO v2 SELECT * FROM t2; 4887 4888UPDATE v2 SET a = 10; 4889--error ER_VIEW_NO_EXPLAIN 4890EXPLAIN UPDATE v2 SET a = 10; 4891--error ER_VIEW_NO_EXPLAIN 4892ANALYZE UPDATE v2 SET a = 10; 4893 4894# Wrong result due to MDEV-7042 4895# --error ER_COLUMNACCESS_DENIED_ERROR 4896UPDATE v2 SET a = a + 1; 4897# Strange error code due to MDEV-7042 4898#--error ER_COLUMNACCESS_DENIED_ERROR 4899--error ER_VIEW_NO_EXPLAIN 4900EXPLAIN UPDATE v2 SET a = a + 1; 4901--error ER_VIEW_NO_EXPLAIN 4902ANALYZE UPDATE v2 SET a = a + 1; 4903 4904--error ER_COLUMNACCESS_DENIED_ERROR 4905UPDATE v2, t2 SET v2.a = v2.a + 1 WHERE v2.a = t2.a; 4906# Strange error code due to MDEV-7042 4907# --error ER_COLUMNACCESS_DENIED_ERROR 4908--error ER_VIEW_NO_EXPLAIN 4909EXPLAIN UPDATE v2, t2 SET v2.a = v2.a + 1 WHERE v2.a = t2.a; 4910--error ER_VIEW_NO_EXPLAIN 4911ANALYZE UPDATE v2, t2 SET v2.a = v2.a + 1 WHERE v2.a = t2.a; 4912 4913--error ER_TABLEACCESS_DENIED_ERROR 4914DELETE FROM v2; 4915--error ER_TABLEACCESS_DENIED_ERROR 4916EXPLAIN DELETE FROM v2; 4917--error ER_TABLEACCESS_DENIED_ERROR 4918ANALYZE DELETE FROM v2; 4919 4920--error ER_TABLEACCESS_DENIED_ERROR 4921DELETE FROM v2 WHERE a = 10; 4922--error ER_TABLEACCESS_DENIED_ERROR 4923EXPLAIN DELETE FROM v2 WHERE a = 10; 4924--error ER_TABLEACCESS_DENIED_ERROR 4925ANALYZE DELETE FROM v2 WHERE a = 10; 4926 4927--error ER_TABLEACCESS_DENIED_ERROR 4928DELETE FROM v2 USING v2, t2; 4929--error ER_TABLEACCESS_DENIED_ERROR 4930EXPLAIN DELETE FROM v2 USING v2, t2; 4931--error ER_TABLEACCESS_DENIED_ERROR 4932ANALYZE DELETE FROM v2 USING v2, t2; 4933 4934--error ER_TABLEACCESS_DENIED_ERROR 4935DELETE FROM v2 USING v2, t2 WHERE v2.a = t2.a; 4936--error ER_TABLEACCESS_DENIED_ERROR 4937EXPLAIN DELETE FROM v2 USING v2, t2 WHERE v2.a = t2.a; 4938--error ER_TABLEACCESS_DENIED_ERROR 4939ANALYZE DELETE FROM v2 USING v2, t2 WHERE v2.a = t2.a; 4940 4941--error ER_TABLEACCESS_DENIED_ERROR 4942SELECT * FROM v2; 4943--error ER_TABLEACCESS_DENIED_ERROR 4944EXPLAIN SELECT * FROM v2; 4945--error ER_TABLEACCESS_DENIED_ERROR 4946ANALYZE SELECT * FROM v2; 4947 4948--error ER_TABLEACCESS_DENIED_ERROR 4949SELECT * FROM v2 WHERE a = 10; 4950--error ER_TABLEACCESS_DENIED_ERROR 4951EXPLAIN SELECT * FROM v2 WHERE a = 10; 4952--error ER_TABLEACCESS_DENIED_ERROR 4953ANALYZE SELECT * FROM v2 WHERE a = 10; 4954 4955--error ER_TABLEACCESS_DENIED_ERROR 4956SELECT * FROM v2 WHERE a IN ( SELECT a FROM t2 ); 4957--error ER_TABLEACCESS_DENIED_ERROR 4958EXPLAIN SELECT * FROM v2 WHERE a IN ( SELECT a FROM t2 ); 4959--error ER_TABLEACCESS_DENIED_ERROR 4960ANALYZE SELECT * FROM v2 WHERE a IN ( SELECT a FROM t2 ); 4961 4962--echo 4963--echo #======================================================================== 4964--echo # Test: Grant UPDATE, SHOW VIEW on the outer view 4965--echo #======================================================================== 4966--echo 4967 4968connection default; 4969REVOKE ALL PRIVILEGES ON privtest_db.v2 FROM 'privtest'@'localhost'; 4970GRANT SHOW VIEW, UPDATE ON privtest_db.v2 TO 'privtest'@'localhost'; 4971connection con1; 4972 4973 4974--echo #------------------------------------------------------------------------ 4975--echo # I/R/U/D/S on the inner view 4976--echo # Expectation: Can run UPDATE, EXPLAIN UPDATE, ANALYZE UPDATE 4977--echo # which do not read any columns 4978--echo #------------------------------------------------------------------------ 4979 4980--error ER_TABLEACCESS_DENIED_ERROR 4981INSERT INTO v2 (a) VALUES (10); 4982--error ER_TABLEACCESS_DENIED_ERROR 4983EXPLAIN INSERT INTO v2 (a) VALUES (10); 4984--error ER_TABLEACCESS_DENIED_ERROR 4985ANALYZE INSERT INTO v2 (a) VALUES (10); 4986 4987--error ER_TABLEACCESS_DENIED_ERROR 4988INSERT INTO v2 SELECT * FROM t2; 4989--error ER_TABLEACCESS_DENIED_ERROR 4990EXPLAIN INSERT INTO v2 SELECT * FROM t2; 4991--error ER_TABLEACCESS_DENIED_ERROR 4992ANALYZE INSERT INTO v2 SELECT * FROM t2; 4993 4994--error ER_TABLEACCESS_DENIED_ERROR 4995REPLACE INTO v2 (a) VALUES (10); 4996--error ER_TABLEACCESS_DENIED_ERROR 4997EXPLAIN REPLACE INTO v2 (a) VALUES (10); 4998--error ER_TABLEACCESS_DENIED_ERROR 4999ANALYZE REPLACE INTO v2 (a) VALUES (10); 5000 5001--error ER_TABLEACCESS_DENIED_ERROR 5002REPLACE INTO v2 SELECT * FROM t2; 5003--error ER_TABLEACCESS_DENIED_ERROR 5004EXPLAIN REPLACE INTO v2 SELECT * FROM t2; 5005--error ER_TABLEACCESS_DENIED_ERROR 5006ANALYZE REPLACE INTO v2 SELECT * FROM t2; 5007 5008UPDATE v2 SET a = 10; 5009EXPLAIN UPDATE v2 SET a = 10; 5010ANALYZE UPDATE v2 SET a = 10; 5011 5012# Wrong result due to MDEV-7042 5013# --error ER_COLUMNACCESS_DENIED_ERROR 5014UPDATE v2 SET a = a + 1; 5015# Wrong result due to MDEV-7042 5016# --error ER_COLUMNACCESS_DENIED_ERROR 5017EXPLAIN UPDATE v2 SET a = a + 1; 5018# Wrong result due to MDEV-7042 5019# --error ER_COLUMNACCESS_DENIED_ERROR 5020ANALYZE UPDATE v2 SET a = a + 1; 5021 5022--error ER_COLUMNACCESS_DENIED_ERROR 5023UPDATE v2, t2 SET v2.a = v2.a + 1 WHERE v2.a = t2.a; 5024--error ER_COLUMNACCESS_DENIED_ERROR 5025EXPLAIN UPDATE v2, t2 SET v2.a = v2.a + 1 WHERE v2.a = t2.a; 5026--error ER_COLUMNACCESS_DENIED_ERROR 5027ANALYZE UPDATE v2, t2 SET v2.a = v2.a + 1 WHERE v2.a = t2.a; 5028 5029--error ER_TABLEACCESS_DENIED_ERROR 5030DELETE FROM v2; 5031--error ER_TABLEACCESS_DENIED_ERROR 5032EXPLAIN DELETE FROM v2; 5033--error ER_TABLEACCESS_DENIED_ERROR 5034ANALYZE DELETE FROM v2; 5035 5036--error ER_TABLEACCESS_DENIED_ERROR 5037DELETE FROM v2 WHERE a = 10; 5038--error ER_TABLEACCESS_DENIED_ERROR 5039EXPLAIN DELETE FROM v2 WHERE a = 10; 5040--error ER_TABLEACCESS_DENIED_ERROR 5041ANALYZE DELETE FROM v2 WHERE a = 10; 5042 5043--error ER_TABLEACCESS_DENIED_ERROR 5044DELETE FROM v2 USING v2, t2; 5045--error ER_TABLEACCESS_DENIED_ERROR 5046EXPLAIN DELETE FROM v2 USING v2, t2; 5047--error ER_TABLEACCESS_DENIED_ERROR 5048ANALYZE DELETE FROM v2 USING v2, t2; 5049 5050--error ER_TABLEACCESS_DENIED_ERROR 5051DELETE FROM v2 USING v2, t2 WHERE v2.a = t2.a; 5052--error ER_TABLEACCESS_DENIED_ERROR 5053EXPLAIN DELETE FROM v2 USING v2, t2 WHERE v2.a = t2.a; 5054--error ER_TABLEACCESS_DENIED_ERROR 5055ANALYZE DELETE FROM v2 USING v2, t2 WHERE v2.a = t2.a; 5056 5057--error ER_TABLEACCESS_DENIED_ERROR 5058SELECT * FROM v2; 5059--error ER_TABLEACCESS_DENIED_ERROR 5060EXPLAIN SELECT * FROM v2; 5061--error ER_TABLEACCESS_DENIED_ERROR 5062ANALYZE SELECT * FROM v2; 5063 5064--error ER_TABLEACCESS_DENIED_ERROR 5065SELECT * FROM v2 WHERE a = 10; 5066--error ER_TABLEACCESS_DENIED_ERROR 5067EXPLAIN SELECT * FROM v2 WHERE a = 10; 5068--error ER_TABLEACCESS_DENIED_ERROR 5069ANALYZE SELECT * FROM v2 WHERE a = 10; 5070 5071--error ER_TABLEACCESS_DENIED_ERROR 5072SELECT * FROM v2 WHERE a IN ( SELECT a FROM t2 ); 5073--error ER_TABLEACCESS_DENIED_ERROR 5074EXPLAIN SELECT * FROM v2 WHERE a IN ( SELECT a FROM t2 ); 5075--error ER_TABLEACCESS_DENIED_ERROR 5076ANALYZE SELECT * FROM v2 WHERE a IN ( SELECT a FROM t2 ); 5077 5078--echo 5079--echo #======================================================================== 5080--echo # Test: Grant DELETE on the outer view 5081--echo #======================================================================== 5082--echo 5083 5084connection default; 5085REVOKE ALL PRIVILEGES ON privtest_db.v2 FROM 'privtest'@'localhost'; 5086GRANT DELETE ON privtest_db.v2 TO 'privtest'@'localhost'; 5087connection con1; 5088 5089--echo #------------------------------------------------------------------------ 5090--echo # I/R/U/D/S on the outer view 5091--echo # Expectation: Can run DELETE which does not read any columns, 5092--echo # but not EXPLAIN DELETE or ANALYZE DELETE 5093--echo # because the query plan cannot be shown 5094--echo # (it could have revealed the structure of the view) 5095--echo #------------------------------------------------------------------------ 5096 5097 5098--error ER_TABLEACCESS_DENIED_ERROR 5099INSERT INTO v2 (a) VALUES (10); 5100--error ER_TABLEACCESS_DENIED_ERROR 5101EXPLAIN INSERT INTO v2 (a) VALUES (10); 5102--error ER_TABLEACCESS_DENIED_ERROR 5103ANALYZE INSERT INTO v2 (a) VALUES (10); 5104 5105--error ER_TABLEACCESS_DENIED_ERROR 5106INSERT INTO v2 SELECT * FROM t2; 5107--error ER_TABLEACCESS_DENIED_ERROR 5108EXPLAIN INSERT INTO v2 SELECT * FROM t2; 5109--error ER_TABLEACCESS_DENIED_ERROR 5110ANALYZE INSERT INTO v2 SELECT * FROM t2; 5111 5112--error ER_TABLEACCESS_DENIED_ERROR 5113REPLACE INTO v2 (a) VALUES (10); 5114--error ER_TABLEACCESS_DENIED_ERROR 5115EXPLAIN REPLACE INTO v2 (a) VALUES (10); 5116--error ER_TABLEACCESS_DENIED_ERROR 5117ANALYZE REPLACE INTO v2 (a) VALUES (10); 5118 5119--error ER_TABLEACCESS_DENIED_ERROR 5120REPLACE INTO v2 SELECT * FROM t2; 5121--error ER_TABLEACCESS_DENIED_ERROR 5122EXPLAIN REPLACE INTO v2 SELECT * FROM t2; 5123--error ER_TABLEACCESS_DENIED_ERROR 5124ANALYZE REPLACE INTO v2 SELECT * FROM t2; 5125 5126--error ER_TABLEACCESS_DENIED_ERROR 5127UPDATE v2 SET a = 10; 5128--error ER_TABLEACCESS_DENIED_ERROR 5129EXPLAIN UPDATE v2 SET a = 10; 5130--error ER_TABLEACCESS_DENIED_ERROR 5131ANALYZE UPDATE v2 SET a = 10; 5132 5133--error ER_TABLEACCESS_DENIED_ERROR 5134UPDATE v2 SET a = a + 1; 5135--error ER_TABLEACCESS_DENIED_ERROR 5136EXPLAIN UPDATE v2 SET a = a + 1; 5137--error ER_TABLEACCESS_DENIED_ERROR 5138ANALYZE UPDATE v2 SET a = a + 1; 5139 5140--error ER_TABLEACCESS_DENIED_ERROR 5141UPDATE v2, t2 SET v2.a = v2.a + 1 WHERE v2.a = t2.a; 5142--error ER_TABLEACCESS_DENIED_ERROR 5143EXPLAIN UPDATE v2, t2 SET v2.a = v2.a + 1 WHERE v2.a = t2.a; 5144--error ER_TABLEACCESS_DENIED_ERROR 5145ANALYZE UPDATE v2, t2 SET v2.a = v2.a + 1 WHERE v2.a = t2.a; 5146 5147DELETE FROM v2; 5148--error ER_VIEW_NO_EXPLAIN 5149EXPLAIN DELETE FROM v2; 5150--error ER_VIEW_NO_EXPLAIN 5151ANALYZE DELETE FROM v2; 5152 5153--error ER_COLUMNACCESS_DENIED_ERROR 5154DELETE FROM v2 WHERE a = 10; 5155# Strange error code due to MDEV-7042, MDEV-7033 5156# --error ER_COLUMNACCESS_DENIED_ERROR 5157--error ER_VIEW_NO_EXPLAIN 5158EXPLAIN DELETE FROM v2 WHERE a = 10; 5159--error ER_VIEW_NO_EXPLAIN 5160ANALYZE DELETE FROM v2 WHERE a = 10; 5161 5162# Unexpected error due to MDEV-7043 5163--error ER_TABLEACCESS_DENIED_ERROR 5164DELETE FROM v2 USING v2, t2; 5165# Unexpected error code due to MDEV-7043 5166# --error ER_VIEW_NO_EXPLAIN 5167--error ER_TABLEACCESS_DENIED_ERROR 5168EXPLAIN DELETE FROM v2 USING v2, t2; 5169# Unexpected error code due to MDEV-7043 5170--error ER_TABLEACCESS_DENIED_ERROR 5171ANALYZE DELETE FROM v2 USING v2, t2; 5172 5173# Unexpected error due to MDEV-7043 5174--error ER_TABLEACCESS_DENIED_ERROR 5175DELETE FROM v2 USING v2, t2 WHERE v2.a = t2.a; 5176# Unexpected error code due to MDEV-7043 5177# --error ER_VIEW_NO_EXPLAIN 5178--error ER_TABLEACCESS_DENIED_ERROR 5179EXPLAIN DELETE FROM v2 USING v2, t2 WHERE v2.a = t2.a; 5180# Unexpected error code due to MDEV-7043 5181--error ER_TABLEACCESS_DENIED_ERROR 5182ANALYZE DELETE FROM v2 USING v2, t2 WHERE v2.a = t2.a; 5183 5184--error ER_TABLEACCESS_DENIED_ERROR 5185SELECT * FROM v2; 5186--error ER_TABLEACCESS_DENIED_ERROR 5187EXPLAIN SELECT * FROM v2; 5188--error ER_TABLEACCESS_DENIED_ERROR 5189ANALYZE SELECT * FROM v2; 5190 5191--error ER_TABLEACCESS_DENIED_ERROR 5192SELECT * FROM v2 WHERE a = 10; 5193--error ER_TABLEACCESS_DENIED_ERROR 5194EXPLAIN SELECT * FROM v2 WHERE a = 10; 5195--error ER_TABLEACCESS_DENIED_ERROR 5196ANALYZE SELECT * FROM v2 WHERE a = 10; 5197 5198--error ER_TABLEACCESS_DENIED_ERROR 5199SELECT * FROM v2 WHERE a IN ( SELECT a FROM t2 ); 5200--error ER_TABLEACCESS_DENIED_ERROR 5201EXPLAIN SELECT * FROM v2 WHERE a IN ( SELECT a FROM t2 ); 5202--error ER_TABLEACCESS_DENIED_ERROR 5203ANALYZE SELECT * FROM v2 WHERE a IN ( SELECT a FROM t2 ); 5204 5205--echo 5206--echo #======================================================================== 5207--echo # Test: Grant DELETE, SELECT on the outer view 5208--echo #======================================================================== 5209--echo 5210 5211connection default; 5212REVOKE ALL PRIVILEGES ON privtest_db.v2 FROM 'privtest'@'localhost'; 5213GRANT SELECT, DELETE ON privtest_db.v2 TO 'privtest'@'localhost'; 5214connection con1; 5215 5216 5217--echo #------------------------------------------------------------------------ 5218--echo # I/R/U/D/S on the outer view 5219--echo # Expectation: Can run DELETE and SELECT, but not EXPLAIN or ANALYZE 5220--echo # for them because the query plan cannot be shown 5221--echo # (it could have revealed the structure of the view) 5222--echo #------------------------------------------------------------------------ 5223 5224--error ER_TABLEACCESS_DENIED_ERROR 5225INSERT INTO v2 (a) VALUES (10); 5226--error ER_TABLEACCESS_DENIED_ERROR 5227EXPLAIN INSERT INTO v2 (a) VALUES (10); 5228--error ER_TABLEACCESS_DENIED_ERROR 5229ANALYZE INSERT INTO v2 (a) VALUES (10); 5230 5231--error ER_TABLEACCESS_DENIED_ERROR 5232INSERT INTO v2 SELECT * FROM t2; 5233--error ER_TABLEACCESS_DENIED_ERROR 5234EXPLAIN INSERT INTO v2 SELECT * FROM t2; 5235--error ER_TABLEACCESS_DENIED_ERROR 5236ANALYZE INSERT INTO v2 SELECT * FROM t2; 5237 5238--error ER_TABLEACCESS_DENIED_ERROR 5239REPLACE INTO v2 (a) VALUES (10); 5240--error ER_TABLEACCESS_DENIED_ERROR 5241EXPLAIN REPLACE INTO v2 (a) VALUES (10); 5242--error ER_TABLEACCESS_DENIED_ERROR 5243ANALYZE REPLACE INTO v2 (a) VALUES (10); 5244 5245--error ER_TABLEACCESS_DENIED_ERROR 5246REPLACE INTO v2 SELECT * FROM t2; 5247--error ER_TABLEACCESS_DENIED_ERROR 5248EXPLAIN REPLACE INTO v2 SELECT * FROM t2; 5249--error ER_TABLEACCESS_DENIED_ERROR 5250ANALYZE REPLACE INTO v2 SELECT * FROM t2; 5251 5252--error ER_TABLEACCESS_DENIED_ERROR 5253UPDATE v2 SET a = 10; 5254--error ER_TABLEACCESS_DENIED_ERROR 5255EXPLAIN UPDATE v2 SET a = 10; 5256--error ER_TABLEACCESS_DENIED_ERROR 5257ANALYZE UPDATE v2 SET a = 10; 5258 5259--error ER_TABLEACCESS_DENIED_ERROR 5260UPDATE v2 SET a = a + 1; 5261--error ER_TABLEACCESS_DENIED_ERROR 5262EXPLAIN UPDATE v2 SET a = a + 1; 5263--error ER_TABLEACCESS_DENIED_ERROR 5264ANALYZE UPDATE v2 SET a = a + 1; 5265 5266--error ER_TABLEACCESS_DENIED_ERROR 5267UPDATE v2, t2 SET v2.a = v2.a + 1 WHERE v2.a = t2.a; 5268# Strange error code due to MDEV-7033 5269--error ER_VIEW_NO_EXPLAIN 5270EXPLAIN UPDATE v2, t2 SET v2.a = v2.a + 1 WHERE v2.a = t2.a; 5271--error ER_VIEW_NO_EXPLAIN 5272ANALYZE UPDATE v2, t2 SET v2.a = v2.a + 1 WHERE v2.a = t2.a; 5273 5274DELETE FROM v2; 5275--error ER_VIEW_NO_EXPLAIN 5276EXPLAIN DELETE FROM v2; 5277--error ER_VIEW_NO_EXPLAIN 5278ANALYZE DELETE FROM v2; 5279 5280DELETE FROM v2 WHERE a = 10; 5281--error ER_VIEW_NO_EXPLAIN 5282EXPLAIN DELETE FROM v2 WHERE a = 10; 5283--error ER_VIEW_NO_EXPLAIN 5284ANALYZE DELETE FROM v2 WHERE a = 10; 5285 5286DELETE FROM v2 USING v2, t2; 5287--error ER_VIEW_NO_EXPLAIN 5288EXPLAIN DELETE FROM v2 USING v2, t2; 5289--error ER_VIEW_NO_EXPLAIN 5290ANALYZE DELETE FROM v2 USING v2, t2; 5291 5292DELETE FROM v2 USING v2, t2 WHERE v2.a = t2.a; 5293--error ER_VIEW_NO_EXPLAIN 5294EXPLAIN DELETE FROM v2 USING v2, t2 WHERE v2.a = t2.a; 5295--error ER_VIEW_NO_EXPLAIN 5296ANALYZE DELETE FROM v2 USING v2, t2 WHERE v2.a = t2.a; 5297 5298SELECT * FROM v2; 5299--error ER_VIEW_NO_EXPLAIN 5300EXPLAIN SELECT * FROM v2; 5301--error ER_VIEW_NO_EXPLAIN 5302ANALYZE SELECT * FROM v2; 5303 5304SELECT * FROM v2 WHERE a = 10; 5305--error ER_VIEW_NO_EXPLAIN 5306EXPLAIN SELECT * FROM v2 WHERE a = 10; 5307--error ER_VIEW_NO_EXPLAIN 5308ANALYZE SELECT * FROM v2 WHERE a = 10; 5309 5310SELECT * FROM v2 WHERE a IN ( SELECT a FROM t2 ); 5311--error ER_VIEW_NO_EXPLAIN 5312EXPLAIN SELECT * FROM v2 WHERE a IN ( SELECT a FROM t2 ); 5313--error ER_VIEW_NO_EXPLAIN 5314ANALYZE SELECT * FROM v2 WHERE a IN ( SELECT a FROM t2 ); 5315 5316--echo 5317--echo #======================================================================== 5318--echo # Test: Grant SELECT, INSERT, UPDATE, DELETE, SHOW VIEW on the outer view 5319--echo #======================================================================== 5320--echo 5321 5322connection default; 5323REVOKE ALL PRIVILEGES ON privtest_db.v2 FROM 'privtest'@'localhost'; 5324GRANT SELECT, INSERT, UPDATE, DELETE, SHOW VIEW ON privtest_db.v2 TO 'privtest'@'localhost'; 5325connection con1; 5326 5327 5328--echo #------------------------------------------------------------------------ 5329--echo # I/R/U/D/S on the outer view 5330--echo # Expectation: Can run everything 5331--echo #------------------------------------------------------------------------ 5332 5333INSERT INTO v2 (a) VALUES (10); 5334EXPLAIN INSERT INTO v2 (a) VALUES (10); 5335ANALYZE INSERT INTO v2 (a) VALUES (10); 5336 5337INSERT INTO v2 SELECT * FROM t2; 5338# Commented due to MDEV-7034 5339EXPLAIN INSERT INTO v2 SELECT * FROM t2; 5340ANALYZE INSERT INTO v2 SELECT * FROM t2; 5341 5342REPLACE INTO v2 (a) VALUES (10); 5343EXPLAIN REPLACE INTO v2 (a) VALUES (10); 5344ANALYZE REPLACE INTO v2 (a) VALUES (10); 5345 5346REPLACE INTO v2 SELECT * FROM t2; 5347EXPLAIN REPLACE INTO v2 SELECT * FROM t2; 5348ANALYZE REPLACE INTO v2 SELECT * FROM t2; 5349 5350UPDATE v2 SET a = 10; 5351EXPLAIN UPDATE v2 SET a = 10; 5352ANALYZE UPDATE v2 SET a = 10; 5353 5354UPDATE v2 SET a = a + 1; 5355EXPLAIN UPDATE v2 SET a = a + 1; 5356ANALYZE UPDATE v2 SET a = a + 1; 5357 5358UPDATE v2, t2 SET v2.a = v2.a + 1 WHERE v2.a = t2.a; 5359EXPLAIN UPDATE v2, t2 SET v2.a = v2.a + 1 WHERE v2.a = t2.a; 5360ANALYZE UPDATE v2, t2 SET v2.a = v2.a + 1 WHERE v2.a = t2.a; 5361 5362DELETE FROM v2; 5363EXPLAIN DELETE FROM v2; 5364ANALYZE DELETE FROM v2; 5365 5366DELETE FROM v2 WHERE a = 10; 5367EXPLAIN DELETE FROM v2 WHERE a = 10; 5368ANALYZE DELETE FROM v2 WHERE a = 10; 5369 5370DELETE FROM v2 USING v2, t2; 5371# Commented due to MDEV-7034 5372# EXPLAIN DELETE FROM v2 USING v2, t2; 5373ANALYZE DELETE FROM v2 USING v2, t2; 5374 5375DELETE FROM v2 USING v2, t2 WHERE v2.a = t2.a; 5376# Commented due to MDEV-7034 5377# EXPLAIN DELETE FROM v2 USING v2, t2 WHERE v2.a = t2.a; 5378ANALYZE DELETE FROM v2 USING v2, t2 WHERE v2.a = t2.a; 5379 5380SELECT * FROM v2; 5381EXPLAIN SELECT * FROM v2; 5382ANALYZE SELECT * FROM v2; 5383 5384SELECT * FROM v2 WHERE a = 10; 5385EXPLAIN SELECT * FROM v2 WHERE a = 10; 5386ANALYZE SELECT * FROM v2 WHERE a = 10; 5387 5388SELECT * FROM v2 WHERE a IN ( SELECT a FROM t2 ); 5389EXPLAIN SELECT * FROM v2 WHERE a IN ( SELECT a FROM t2 ); 5390ANALYZE SELECT * FROM v2 WHERE a IN ( SELECT a FROM t2 ); 5391 5392 5393################################################################################################ 5394disconnect con1; 5395connection default; 5396 5397DROP USER 'privtest'@localhost; 5398USE test; 5399DROP DATABASE privtest_db; 5400 5401set GLOBAL sql_mode=default; 5402--source include/wait_until_count_sessions.inc 5403 5404 5405