Debian Codegraph @

mysql-test/suite/funcs_1/triggers/triggers_03.inc

raw
1 #### suite/funcs_1/triggers/triggers_03.inc
2 #======================================================================
3 #
4 # Trigger Tests
5 # (test case numbering refer to requirement document TP v1.1)
6 #======================================================================
7 # WL#4084: enable disabled parts. 2007-11-15, hhunger
8
9
10 set @orig_sql_mode_session= @@SESSION.sql_mode;
11 set @orig_sql_mode_global= @@GLOBAL.sql_mode;
12
13 USE test;
14 --source suite/funcs_1/include/tb3.inc
15
16
17
18 --disable_abort_on_error
19
20 ###########################################
21 ################ Section 3.5.3 ############
22 # Check for the global nature of Triggers #
23 ###########################################
24
25 # General setup to be used in all testcases of 3.5.3
26 let $message= Testcase 3.5.3:;
27 --source include/show_msg.inc
28
29 --disable_warnings
30 drop database if exists priv_db;
31 --enable_warnings
32 create database priv_db;
33 use priv_db;
34 --replace_result $engine_type <engine_to_be_used>
35 eval create table t1 (f1 char(20)) engine= $engine_type;
36
37 create User test_noprivs@localhost;
38 set password for test_noprivs@localhost = 'PWD';
39
40 create User test_yesprivs@localhost;
41 set password for test_yesprivs@localhost = 'PWD';
42
43 #Section 3.5.3.1 / 3.5.3.2
44 # Test case: Ensure TRIGGER privilege is required to create a trigger
45 #Section 3.5.3.3 / 3.5.3.4
46 # Test case: Ensure that root always has the TRIGGER privilege.
47 # OMR - No need to test this since SUPER priv is an existing one and not related
48 # or added for triggers (TP 2005-06-06)
49 #Section 3.5.3.5 / 3.5.3.6
50 # Test case: Ensure that the TRIGGER privilege is required to drop a trigger.
51 let $message= Testcase 3.5.3.2/6:;
52 --source include/show_msg.inc
53
54 revoke ALL PRIVILEGES, GRANT OPTION FROM test_noprivs@localhost;
55 grant ALL on *.* to test_noprivs@localhost;
56 revoke TRIGGER on *.* from test_noprivs@localhost;
57 show grants for test_noprivs@localhost;
58
59 revoke ALL PRIVILEGES, GRANT OPTION FROM test_yesprivs@localhost;
60 grant TRIGGER on *.* to test_yesprivs@localhost;
61 # Adding the minimal priv to be able to set to the db
62 grant SELECT on priv_db.t1 to test_yesprivs@localhost;
63 show grants for test_yesprivs@localhost;
64
65 connect (no_privs,localhost,test_noprivs,PWD,test,$MASTER_MYPORT,$MASTER_MYSOCK);
66 connect (yes_privs,localhost,test_yesprivs,PWD,test,$MASTER_MYPORT,$MASTER_MYSOCK);
67 connection default;
68
69 let $message= Testcase 3.5.3.2:;
70 --source include/show_msg.inc
71
72 connection no_privs;
73 select current_user;
74 use priv_db;
75
76 --error ER_TABLEACCESS_DENIED_ERROR
77 create trigger trg1_1 before INSERT on t1 for each row
78 set new.f1 = 'trig 3.5.3.2_1-no';
79
80 connection default;
81 use priv_db;
82 insert into t1 (f1) values ('insert 3.5.3.2-no');
83 select f1 from t1 order by f1;
84
85 connection yes_privs;
86 select current_user;
87 use priv_db;
88
89 create trigger trg1_2 before INSERT on t1 for each row
90 set new.f1 = 'trig 3.5.3.2_2-yes';
91
92 connection default;
93 select current_user;
94 use priv_db;
95
96 --error ER_COLUMNACCESS_DENIED_ERROR
97 insert into t1 (f1) values ('insert 3.5.3.2-yes');
98 select f1 from t1 order by f1;
99
100 grant UPDATE on priv_db.t1 to test_yesprivs@localhost;
101 insert into t1 (f1) values ('insert 3.5.3.2-yes');
102 select f1 from t1 order by f1;
103
104 let $message= Testcase 3.5.3.6:;
105 --source include/show_msg.inc
106
107 connection no_privs;
108 use priv_db;
109
110 --error ER_TABLEACCESS_DENIED_ERROR
111 drop trigger trg1_2;
112
113 connection default;
114 use priv_db;
115 insert into t1 (f1) values ('insert 3.5.3.6-yes');
116 select f1 from t1 order by f1;
117
118 connection yes_privs;
119 use priv_db;
120
121 drop trigger trg1_2;
122
123 connection default;
124 use priv_db;
125 insert into t1 (f1) values ('insert 3.5.3.6-no');
126 select f1 from t1 order by f1;
127
128 # Cleanup
129 --disable_warnings
130 connection default;
131 --error 0, ER_TRG_DOES_NOT_EXIST
132 drop trigger trg1_2;
133 disconnect no_privs;
134 disconnect yes_privs;
135 --enable_warnings
136
137
138 #Section 3.5.3.7
139 # Test case: Ensure that use of the construct "SET NEW. <column name> = <value>"
140 # fails at CREATE TRIGGER time, if the current user does not have the
141 # UPDATE privilege on the column specified
142
143 # --- 3.5.3.7a - Privs set on a global level
144 let $message=Testcase 3.5.3.7a:;
145 --source include/show_msg.inc
146
147 revoke ALL PRIVILEGES, GRANT OPTION FROM test_noprivs@localhost;
148 grant ALL on *.* to test_noprivs@localhost;
149 revoke UPDATE on *.* from test_noprivs@localhost;
150 show grants for test_noprivs@localhost;
151
152 revoke ALL PRIVILEGES, GRANT OPTION FROM test_yesprivs@localhost;
153 grant TRIGGER, UPDATE on *.* to test_yesprivs@localhost;
154 show grants for test_yesprivs@localhost;
155
156 connect (no_privs_424a,localhost,test_noprivs,PWD,test,$MASTER_MYPORT,$MASTER_MYSOCK);
157 connect (yes_privs_424a,localhost,test_yesprivs,PWD,test,$MASTER_MYPORT,$MASTER_MYSOCK);
158
159 connection no_privs_424a;
160 select current_user;
161 use priv_db;
162 show grants;
163 select f1 from t1 order by f1;
164
165 create trigger trg4a_1 before INSERT on t1 for each row
166 set new.f1 = 'trig 3.5.3.7-1a';
167
168 connection default;
169 --error ER_COLUMNACCESS_DENIED_ERROR
170 insert into t1 (f1) values ('insert 3.5.3.7-1a');
171 select f1 from t1 order by f1;
172 drop trigger trg4a_1;
173
174 connection yes_privs_424a;
175 use priv_db;
176 select current_user;
177 show grants;
178 create trigger trg4a_2 before INSERT on t1 for each row
179 set new.f1 = 'trig 3.5.3.7-2a';
180
181 connection default;
182
183 insert into t1 (f1) values ('insert 3.5.3.7-2b');
184 select f1 from t1 order by f1;
185
186 # Cleanup
187 --disable_warnings
188 drop trigger trg4a_2;
189 disconnect no_privs_424a;
190 disconnect yes_privs_424a;
191 --enable_warnings
192
193 # --- 3.5.3.7b - Privs set on a database level
194 let $message= Testcase 3.5.3.7b:;
195 --source include/show_msg.inc
196
197 revoke ALL PRIVILEGES, GRANT OPTION FROM test_noprivs@localhost;
198 grant TRIGGER on *.* to test_noprivs;
199 grant ALL on priv_db.* to test_noprivs@localhost;
200 revoke UPDATE on priv_db.* from test_noprivs@localhost;
201 show grants for test_noprivs;
202
203 revoke ALL PRIVILEGES, GRANT OPTION FROM test_yesprivs@localhost;
204 grant TRIGGER on *.* to test_yesprivs@localhost;
205 grant UPDATE on priv_db.* to test_yesprivs@localhost;
206 show grants for test_yesprivs@localhost;
207
208 connect (no_privs_424b,localhost,test_noprivs,PWD,test,$MASTER_MYPORT,$MASTER_MYSOCK);
209 connect (yes_privs_424b,localhost,test_yesprivs,PWD,test,$MASTER_MYPORT,$MASTER_MYSOCK);
210 connection default;
211
212 connection no_privs_424b;
213 show grants;
214 use priv_db;
215
216 create trigger trg4b_1 before UPDATE on t1 for each row
217 set new.f1 = 'trig 3.5.3.7-1b';
218
219 connection default;
220 insert into t1 (f1) values ('insert 3.5.3.7-1b');
221 select f1 from t1 order by f1;
222 update t1 set f1 = 'update 3.5.3.7-1b' where f1 = 'insert 3.5.3.7-1b';
223 select f1 from t1 order by f1;
224 drop trigger trg4b_1;
225
226 connection yes_privs_424b;
227 show grants;
228 use priv_db;
229 create trigger trg4b_2 before UPDATE on t1 for each row
230 set new.f1 = 'trig 3.5.3.7-2b';
231
232 connection default;
233
234 insert into t1 (f1) values ('insert 3.5.3.7-2b');
235 select f1 from t1 order by f1;
236 update t1 set f1 = 'update 3.5.3.7-2b' where f1 = 'insert 3.5.3.7-2b';
237 select f1 from t1 order by f1;
238 # Cleanup
239 --disable_warnings
240 drop trigger trg4b_2;
241 disconnect no_privs_424b;
242 disconnect yes_privs_424b;
243 --enable_warnings
244
245 # --- 3.5.3.7c - Privs set on a table level
246 let $message= Testcase 3.5.3.7c;
247 --source include/show_msg.inc
248
249 revoke ALL PRIVILEGES, GRANT OPTION FROM test_noprivs@localhost;
250 grant TRIGGER on *.* to test_noprivs@localhost;
251 grant ALL on priv_db.t1 to test_noprivs@localhost;
252 revoke UPDATE on priv_db.t1 from test_noprivs@localhost;
253 show grants for test_noprivs;
254
255 revoke ALL PRIVILEGES, GRANT OPTION FROM test_yesprivs@localhost;
256 grant TRIGGER on *.* to test_yesprivs@localhost;
257 grant UPDATE on priv_db.t1 to test_yesprivs@localhost;
258 show grants for test_yesprivs@localhost;
259
260 connect (no_privs_424c,localhost,test_noprivs,PWD,test,$MASTER_MYPORT,$MASTER_MYSOCK);
261 connect (yes_privs_424c,localhost,test_yesprivs,PWD,test,$MASTER_MYPORT,$MASTER_MYSOCK);
262 connection default;
263
264 connection no_privs_424c;
265 show grants;
266 use priv_db;
267
268 create trigger trg4c_1 before INSERT on t1 for each row
269 set new.f1 = 'trig 3.5.3.7-1c';
270
271 connection default;
272 insert into t1 (f1) values ('insert 3.5.3.7-1c');
273 select f1 from t1 order by f1;
274 drop trigger trg4c_1;
275
276 connection yes_privs_424c;
277 show grants;
278 use priv_db;
279 create trigger trg4c_2 before INSERT on t1 for each row
280 set new.f1 = 'trig 3.5.3.7-2c';
281
282 connection default;
283
284 insert into t1 (f1) values ('insert 3.5.3.7-2c');
285 select f1 from t1 order by f1;
286
287 # Cleanup
288 --disable_warnings
289 drop trigger trg4c_2;
290 disconnect no_privs_424c;
291 disconnect yes_privs_424c;
292 --enable_warnings
293
294 # --- 3.5.3.7d - Privs set on a column level
295 --disable_query_log
296 let $message= Testcase 3.5.3.7d:;
297 --enable_query_log
298 --source include/show_msg.inc
299
300 revoke ALL PRIVILEGES, GRANT OPTION FROM test_noprivs@localhost;
301 grant TRIGGER on *.* to test_noprivs@localhost;
302 # There is no ALL privs on the column level
303 grant SELECT (f1), INSERT (f1) on priv_db.t1 to test_noprivs@localhost;
304 show grants for test_noprivs;
305
306 revoke ALL PRIVILEGES, GRANT OPTION FROM test_yesprivs@localhost;
307 grant TRIGGER on *.* to test_yesprivs@localhost;
308 grant UPDATE (f1) on priv_db.t1 to test_yesprivs@localhost;
309 show grants for test_noprivs;
310
311 connect (no_privs_424d,localhost,test_noprivs,PWD,test,$MASTER_MYPORT,$MASTER_MYSOCK);
312 connect (yes_privs_424d,localhost,test_yesprivs,PWD,test,$MASTER_MYPORT,$MASTER_MYSOCK);
313 connection default;
314
315 connection no_privs_424d;
316 show grants;
317 use priv_db;
318 create trigger trg4d_1 before INSERT on t1 for each row
319 set new.f1 = 'trig 3.5.3.7-1d';
320
321 connection default;
322 insert into t1 (f1) values ('insert 3.5.3.7-1d');
323 select f1 from t1 order by f1;
324 drop trigger trg4d_1;
325
326 connection yes_privs_424d;
327 show grants;
328 use priv_db;
329 create trigger trg4d_2 before INSERT on t1 for each row
330 set new.f1 = 'trig 3.5.3.7-2d';
331
332 connection default;
333
334 insert into t1 (f1) values ('insert 3.5.3.7-2d');
335 select f1 from t1 order by f1;
336
337 # Cleanup
338 --disable_warnings
339 drop trigger trg4d_2;
340 disconnect no_privs_424d;
341 disconnect yes_privs_424d;
342 --enable_warnings
343
344 #Section 3.5.3.8
345 # Test case: Ensure that use of the construct "SET <target> = NEW. <Column name>" fails
346 # at CREATE TRIGGER time, if the current user does not have the SELECT privilege
347 # on the column specified.
348
349 # --- 3.5.3.8a - Privs set on a global level
350 let $message= Testcase 3.5.3.8a:;
351 --source include/show_msg.inc
352
353 revoke ALL PRIVILEGES, GRANT OPTION FROM test_noprivs@localhost;
354 grant ALL on *.* to test_noprivs@localhost;
355 revoke SELECT on *.* from test_noprivs@localhost;
356 show grants for test_noprivs@localhost;
357
358 revoke ALL PRIVILEGES, GRANT OPTION FROM test_yesprivs@localhost;
359 grant TRIGGER, SELECT on *.* to test_yesprivs@localhost;
360 show grants for test_yesprivs@localhost;
361
362 connect (no_privs_425a,localhost,test_noprivs,PWD,test,$MASTER_MYPORT,$MASTER_MYSOCK);
363 connect (yes_privs_425a,localhost,test_yesprivs,PWD,test,$MASTER_MYPORT,$MASTER_MYSOCK);
364 connection default;
365
366 connection no_privs_425a;
367 select current_user;
368 use priv_db;
369 show grants;
370
371 create trigger trg5a_1 before INSERT on t1 for each row
372 set @test_var = new.f1;
373
374 connection default;
375 set @test_var = 'before trig 3.5.3.8-1a';
376 select @test_var;
377 insert into t1 (f1) values ('insert 3.5.3.8-1a');
378 select @test_var;
379 drop trigger trg5a_1;
380
381 connection yes_privs_425a;
382 use priv_db;
383 select current_user;
384 show grants;
385 create trigger trg5a_2 before INSERT on t1 for each row
386 set @test_var= new.f1;
387
388 connection default;
389 set @test_var= 'before trig 3.5.3.8-2a';
390 select @test_var;
391
392 insert into t1 (f1) values ('insert 3.5.3.8-2a');
393 select @test_var;
394
395 # Cleanup
396 --disable_warnings
397 drop trigger trg5a_2;
398 disconnect no_privs_425a;
399 disconnect yes_privs_425a;
400 --enable_warnings
401
402 # --- 3.5.3.8b - Privs set on a database level
403 let $message= Testcase: 3.5.3.8b;
404 --source include/show_msg.inc
405
406 revoke ALL PRIVILEGES, GRANT OPTION FROM test_noprivs@localhost;
407 grant TRIGGER on *.* to test_noprivs@localhost;
408 grant ALL on priv_db.* to test_noprivs@localhost;
409 revoke SELECT on priv_db.* from test_noprivs@localhost;
410 show grants for test_noprivs@localhost;
411
412 revoke ALL PRIVILEGES, GRANT OPTION FROM test_yesprivs@localhost;
413 grant TRIGGER on *.* to test_yesprivs@localhost;
414 grant SELECT on priv_db.* to test_yesprivs@localhost;
415 show grants for test_yesprivs@localhost;
416
417 connect (no_privs_425b,localhost,test_noprivs,PWD,test,$MASTER_MYPORT,$MASTER_MYSOCK);
418 connect (yes_privs_425b,localhost,test_yesprivs,PWD,test,$MASTER_MYPORT,$MASTER_MYSOCK);
419 connection default;
420
421 connection no_privs_425b;
422 show grants;
423 use priv_db;
424
425 create trigger trg5b_1 before UPDATE on t1 for each row
426 set @test_var= new.f1;
427
428 connection default;
429 set @test_var= 'before trig 3.5.3.8-1b';
430 insert into t1 (f1) values ('insert 3.5.3.8-1b');
431 select @test_var;
432 update t1 set f1= 'update 3.5.3.8-1b' where f1 = 'insert 3.5.3.8-1b';
433 select @test_var;
434 drop trigger trg5b_1;
435
436 connection yes_privs_425b;
437 show grants;
438 use priv_db;
439 create trigger trg5b_2 before UPDATE on t1 for each row
440 set @test_var= new.f1;
441
442 connection default;
443 set @test_var= 'before trig 3.5.3.8-2b';
444 insert into t1 (f1) values ('insert 3.5.3.8-2b');
445 select @test_var;
446
447 update t1 set f1= 'update 3.5.3.8-2b' where f1 = 'insert 3.5.3.8-2b';
448 select @test_var;
449 # Cleanup
450 --disable_warnings
451 drop trigger trg5b_2;
452 disconnect no_privs_425b;
453 disconnect yes_privs_425b;
454 --enable_warnings
455
456 # --- 3.5.3.8c - Privs set on a table level
457 let $message= Testcase 3.5.3.8c:;
458 --source include/show_msg.inc
459
460 revoke ALL PRIVILEGES, GRANT OPTION FROM test_noprivs@localhost;
461 grant TRIGGER on *.* to test_noprivs@localhost;
462 grant ALL on priv_db.t1 to test_noprivs@localhost;
463 revoke SELECT on priv_db.t1 from test_noprivs@localhost;
464 show grants for test_noprivs@localhost;
465
466 revoke ALL PRIVILEGES, GRANT OPTION FROM test_yesprivs@localhost;
467 grant TRIGGER on *.* to test_yesprivs@localhost;
468 grant SELECT on priv_db.t1 to test_yesprivs@localhost;
469 show grants for test_yesprivs@localhost;
470
471 connect (no_privs_425c,localhost,test_noprivs,PWD,test,$MASTER_MYPORT,$MASTER_MYSOCK);
472 connect (yes_privs_425c,localhost,test_yesprivs,PWD,test,$MASTER_MYPORT,$MASTER_MYSOCK);
473 connection default;
474
475 connection no_privs_425c;
476 show grants;
477 use priv_db;
478
479 create trigger trg5c_1 before INSERT on t1 for each row
480 set @test_var= new.f1;
481
482 connection default;
483 set @test_var= 'before trig 3.5.3.8-1c';
484 insert into t1 (f1) values ('insert 3.5.3.8-1c');
485 select @test_var;
486 drop trigger trg5c_1;
487
488 connection yes_privs_425c;
489 show grants;
490 use priv_db;
491 create trigger trg5c_2 before INSERT on t1 for each row
492 set @test_var= new.f1;
493
494 connection default;
495 set @test_var='before trig 3.5.3.8-2c';
496
497 insert into t1 (f1) values ('insert 3.5.3.8-2c');
498 select @test_var;
499 # Cleanup
500 --disable_warnings
501 drop trigger trg5c_2;
502 disconnect no_privs_425c;
503 disconnect yes_privs_425c;
504 --enable_warnings
505
506 # --- 3.5.3.8d - Privs set on a column level
507 let $message=Testcase: 3.5.3.8d:;
508 --source include/show_msg.inc
509
510 revoke ALL PRIVILEGES, GRANT OPTION FROM test_noprivs@localhost;
511 grant TRIGGER on *.* to test_noprivs@localhost;
512 # There is no ALL prov on the column level
513 grant UPDATE (f1), INSERT (f1) on priv_db.t1 to test_noprivs@localhost;
514 show grants for test_noprivs@localhost;
515
516 revoke ALL PRIVILEGES, GRANT OPTION FROM test_yesprivs@localhost;
517 grant TRIGGER on *.* to test_yesprivs@localhost;
518 grant SELECT (f1) on priv_db.t1 to test_yesprivs@localhost;
519 show grants for test_noprivs@localhost;
520
521 connect (no_privs_425d,localhost,test_noprivs,PWD,test,$MASTER_MYPORT,$MASTER_MYSOCK);
522 connect (yes_privs_425d,localhost,test_yesprivs,PWD,test,$MASTER_MYPORT,$MASTER_MYSOCK);
523 connection default;
524
525 connection no_privs_425d;
526 show grants;
527 use priv_db;
528 create trigger trg5d_1 before INSERT on t1 for each row
529 set @test_var= new.f1;
530
531 connection default;
532 set @test_var='before trig 3.5.3.8-1d';
533 insert into t1 (f1) values ('insert 3.5.3.8-1d');
534 select @test_var;
535 drop trigger trg5d_1;
536
537 connection yes_privs_425d;
538 show grants;
539 use priv_db;
540 create trigger trg5d_2 before INSERT on t1 for each row
541 set @test_var= new.f1;
542
543 connection default;
544 set @test_var='before trig 3.5.3.8-2d';
545
546 insert into t1 (f1) values ('insert 3.5.3.8-2d');
547 select @test_var;
548
549 # Cleanup 3.5.3.8
550 --disable_warnings
551 drop trigger trg5d_2;
552 --enable_warnings
553
554 # --- 3.5.3.x to test for trigger definer privs in the case of trigger
555 # actions (insert/update/delete/select) performed on other
556 # tables.
557 let $message=Testcase: 3.5.3.x:;
558 --source include/show_msg.inc
559
560 use priv_db;
561 --disable_warnings
562 drop table if exists t1;
563 drop table if exists t2;
564 --enable_warnings
565
566 --replace_result $engine_type <engine_to_be_used>
567 eval create table t1 (f1 int) engine= $engine_type;
568 --replace_result $engine_type <engine_to_be_used>
569 eval create table t2 (f2 int) engine= $engine_type;
570
571 revoke ALL PRIVILEGES, GRANT OPTION FROM test_yesprivs@localhost;
572 grant TRIGGER on *.* to test_yesprivs@localhost;
573 grant SELECT, UPDATE on priv_db.t1 to test_yesprivs@localhost;
574 grant SELECT on priv_db.t2 to test_yesprivs@localhost;
575 show grants for test_yesprivs@localhost;
576
577 connect (yes_353x,localhost,test_yesprivs,PWD,test,$MASTER_MYPORT,$MASTER_MYSOCK);
578
579 connection yes_353x;
580 select current_user;
581 use priv_db;
582
583 create trigger trg1 before insert on t1 for each row
584 insert into t2 values (new.f1);
585
586 connection default;
587 use priv_db;
588 insert into t1 (f1) values (4);
589 revoke SELECT on priv_db.t2 from test_yesprivs@localhost;
590 grant INSERT on priv_db.t2 to test_yesprivs@localhost;
591 insert into t1 (f1) values (4);
592 select f1 from t1 order by f1;
593 select f2 from t2 order by f2;
594
595 connection yes_353x;
596 use priv_db;
597 drop trigger trg1;
598
599 create trigger trg2 before insert on t1 for each row
600 update t2 set f2=new.f1-1;
601
602 connection default;
603 use priv_db;
604 insert into t1 (f1) values (2);
605 revoke INSERT on priv_db.t2 from test_yesprivs@localhost;
606 grant UPDATE on priv_db.t2 to test_yesprivs@localhost;
607 insert into t1 (f1) values (2);
608 select f1 from t1 order by f1;
609 select f2 from t2 order by f2;
610
611 connection yes_353x;
612 use priv_db;
613 drop trigger trg2;
614
615 create trigger trg3 before insert on t1 for each row
616 select f2 into @aaa from t2 where f2=new.f1;
617
618 connection default;
619 use priv_db;
620 insert into t1 (f1) values (1);
621 revoke UPDATE on priv_db.t2 from test_yesprivs@localhost;
622 grant SELECT on priv_db.t2 to test_yesprivs@localhost;
623 insert into t1 (f1) values (1);
624 select f1 from t1 order by f1;
625 select f2 from t2 order by f2;
626 select @aaa;
627
628 connection yes_353x;
629 use priv_db;
630 drop trigger trg3;
631
632 create trigger trg4 before insert on t1 for each row
633 delete from t2;
634
635 connection default;
636 use priv_db;
637 insert into t1 (f1) values (1);
638 revoke SELECT on priv_db.t2 from test_yesprivs@localhost;
639 grant DELETE on priv_db.t2 to test_yesprivs@localhost;
640 insert into t1 (f1) values (1);
641 select f1 from t1 order by f1;
642 select f2 from t2 order by f2;
643
644
645
646 # Cleanup 3.5.3
647 --disable_warnings
648 drop database if exists priv_db;
649 drop user test_yesprivs@localhost;
650 drop user test_noprivs@localhost;
651 drop user test_noprivs;
652 --enable_warnings
653
654 use test;
655 drop table tb3;
656
657 set GLOBAL sql_mode= @orig_sql_mode_global;
658 set SESSION sql_mode= @orig_sql_mode_session;