Debian Codegraph @

mysql-test/suite/funcs_1/r/innodb_trig_03e.result

raw
1 USE test;
2
3 Testcase for db level:
4 ----------------------
5 drop database if exists priv_db;
6 drop database if exists no_priv_db;
7 create database priv_db;
8 create database no_priv_db;
9 use priv_db;
10 create table t1 (f1 char(20)) engine= innodb;
11 create User test_yesprivs@localhost;
12 set password for test_yesprivs@localhost = password('PWD');
13 revoke ALL PRIVILEGES, GRANT OPTION FROM test_yesprivs@localhost;
14 grant select on priv_db.* to test_yesprivs@localhost;
15 show grants for test_yesprivs@localhost;
16 Grants for test_yesprivs@localhost
17 GRANT USAGE ON *.* TO `test_yesprivs`@`localhost` IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
18 GRANT SELECT ON `priv_db`.* TO `test_yesprivs`@`localhost`
19 create User test_noprivs@localhost;
20 set password for test_noprivs@localhost = password('PWD');
21 revoke ALL PRIVILEGES, GRANT OPTION FROM test_noprivs@localhost;
22 grant select,insert on priv_db.* to test_noprivs@localhost;
23 show grants for test_noprivs@localhost;
24 Grants for test_noprivs@localhost
25 GRANT USAGE ON *.* TO `test_noprivs`@`localhost` IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
26 GRANT SELECT, INSERT ON `priv_db`.* TO `test_noprivs`@`localhost`
27 connect yes_privs,localhost,test_yesprivs,PWD,priv_db,$MASTER_MYPORT,$MASTER_MYSOCK;
28 connection yes_privs;
29
30 no trigger privilege on db level for create:
31 --------------------------------------------
32 use priv_db;
33 create trigger trg1_1 before INSERT on t1 for each row
34 set new.f1 = 'trig 1_1-no';
35 ERROR 42000: TRIGGER command denied to user 'test_yesprivs'@'localhost' for table `priv_db`.`t1`
36 connect no_privs,localhost,test_noprivs,PWD,priv_db,$MASTER_MYPORT,$MASTER_MYSOCK;
37 connection no_privs;
38 insert into t1 (f1) values ('insert-yes');
39 select f1 from t1 order by f1;
40 f1
41 insert-yes
42 connection default;
43 select current_user;
44 current_user
45 root@localhost
46 grant TRIGGER on priv_db.* to test_yesprivs@localhost;
47 show grants for test_yesprivs@localhost;
48 Grants for test_yesprivs@localhost
49 GRANT USAGE ON *.* TO `test_yesprivs`@`localhost` IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
50 GRANT SELECT, TRIGGER ON `priv_db`.* TO `test_yesprivs`@`localhost`
51
52 trigger privilege on db level for create:
53 -----------------------------------------
54 connection yes_privs;
55 select current_user;
56 current_user
57 test_yesprivs@localhost
58 use priv_db;
59 create trigger trg1_2 before INSERT on t1 for each row
60 set new.f1 = 'trig 1_2-yes';
61 connection no_privs;
62 select current_user;
63 current_user
64 test_noprivs@localhost
65 use priv_db;
66 insert into t1 (f1) values ('insert-yes');
67 ERROR 42000: UPDATE command denied to user 'test_yesprivs'@'localhost' for column 'f1' in table 't1'
68 select f1 from t1 order by f1;
69 f1
70 insert-yes
71 connection default;
72 select current_user;
73 current_user
74 root@localhost
75 grant UPDATE on priv_db.* to test_yesprivs@localhost;
76 use priv_db;
77 insert into t1 (f1) values ('insert-no');
78 select f1 from t1 order by f1;
79 f1
80 insert-yes
81 trig 1_2-yes
82 connection no_privs;
83 select current_user;
84 current_user
85 test_noprivs@localhost
86 use priv_db;
87 insert into t1 (f1) values ('insert-yes');
88 select f1 from t1 order by f1;
89 f1
90 insert-yes
91 trig 1_2-yes
92 trig 1_2-yes
93 connection default;
94 select current_user;
95 current_user
96 root@localhost
97 revoke TRIGGER on priv_db.* from test_yesprivs@localhost;
98 show grants for test_yesprivs@localhost;
99 Grants for test_yesprivs@localhost
100 GRANT USAGE ON *.* TO `test_yesprivs`@`localhost` IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
101 GRANT SELECT, UPDATE ON `priv_db`.* TO `test_yesprivs`@`localhost`
102
103 no trigger privilege on db level for drop:
104 ------------------------------------------
105 connection yes_privs;
106 select current_user;
107 current_user
108 test_yesprivs@localhost
109 use priv_db;
110 drop trigger trg1_2;
111 ERROR 42000: TRIGGER command denied to user 'test_yesprivs'@'localhost' for table `priv_db`.`t1`
112 connection no_privs;
113 select current_user;
114 current_user
115 test_noprivs@localhost
116 use priv_db;
117
118 no trigger privilege at activation time:
119 ----------------------------------------
120 insert into t1 (f1) values ('insert-yes');
121 ERROR 42000: TRIGGER command denied to user 'test_yesprivs'@'localhost' for table `priv_db`.`t1`
122 select f1 from t1 order by f1;
123 f1
124 insert-yes
125 trig 1_2-yes
126 trig 1_2-yes
127
128 trigger privilege at activation time:
129 -------------------------------------
130 connection default;
131 select current_user;
132 current_user
133 root@localhost
134 grant TRIGGER on priv_db.* to test_yesprivs@localhost;
135 connection no_privs;
136 select current_user;
137 current_user
138 test_noprivs@localhost
139 use priv_db;
140 insert into t1 (f1) values ('insert-no');
141 select f1 from t1 order by f1;
142 f1
143 insert-yes
144 trig 1_2-yes
145 trig 1_2-yes
146 trig 1_2-yes
147
148 trigger privilege on db level for drop:
149 ---------------------------------------
150 connection yes_privs;
151 select current_user;
152 current_user
153 test_yesprivs@localhost
154 show grants for test_yesprivs@localhost;
155 Grants for test_yesprivs@localhost
156 GRANT USAGE ON *.* TO `test_yesprivs`@`localhost` IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
157 GRANT SELECT, UPDATE, TRIGGER ON `priv_db`.* TO `test_yesprivs`@`localhost`
158 drop trigger trg1_2;
159 ERROR 42000: TRIGGER command denied to user 'test_yesprivs'@'localhost' for table `priv_db`.`t1`
160
161 takes effect after use priv_db:
162 -------------------------------
163 use priv_db;
164 drop trigger trg1_2;
165 connection default;
166 select current_user;
167 current_user
168 root@localhost
169 use priv_db;
170 insert into t1 (f1) values ('insert-yes');
171 select f1 from t1 order by f1;
172 f1
173 insert-yes
174 insert-yes
175 trig 1_2-yes
176 trig 1_2-yes
177 trig 1_2-yes
178
179 switch to db without having trigger priv for it:
180 ------------------------------------------------
181 use no_priv_db;
182 create table t1 (f1 char(20)) engine= innodb;
183 grant SELECT,UPDATE on no_priv_db.* to test_yesprivs@localhost;
184 show grants for test_yesprivs@localhost;
185 Grants for test_yesprivs@localhost
186 GRANT USAGE ON *.* TO `test_yesprivs`@`localhost` IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
187 GRANT SELECT, UPDATE, TRIGGER ON `priv_db`.* TO `test_yesprivs`@`localhost`
188 GRANT SELECT, UPDATE ON `no_priv_db`.* TO `test_yesprivs`@`localhost`
189
190 use db with trigger privilege on db level and without...:
191 ---------------------------------------------------------
192 connection yes_privs;
193 select current_user;
194 current_user
195 test_yesprivs@localhost
196 use no_priv_db;
197 create trigger trg1_3 before INSERT on t1 for each row
198 set new.f1 = 'trig 1_3-no';
199 ERROR 42000: TRIGGER command denied to user 'test_yesprivs'@'localhost' for table `no_priv_db`.`t1`
200 use priv_db;
201 create trigger trg1_3 before INSERT on t1 for each row
202 set new.f1 = 'trig 1_3-yes';
203 use no_priv_db;
204 create trigger trg1_4 before UPDATE on t1 for each row
205 set new.f1 = 'trig 1_4-no';
206 ERROR 42000: TRIGGER command denied to user 'test_yesprivs'@'localhost' for table `no_priv_db`.`t1`
207 use priv_db;
208 create trigger trg1_4 before UPDATE on t1 for each row
209 set new.f1 = 'trig 1_4-yes';
210 connection no_privs;
211 select current_user;
212 current_user
213 test_noprivs@localhost
214 use no_priv_db;
215 ERROR 42000: Access denied for user 'test_noprivs'@'localhost' to database 'no_priv_db'
216 insert into t1 (f1) values ('insert-yes');
217 select f1 from t1 order by f1;
218 f1
219 insert-yes
220 insert-yes
221 trig 1_2-yes
222 trig 1_2-yes
223 trig 1_2-yes
224 trig 1_3-yes
225 use priv_db;
226 insert into t1 (f1) values ('insert-no');
227 select f1 from t1 order by f1;
228 f1
229 insert-yes
230 insert-yes
231 trig 1_2-yes
232 trig 1_2-yes
233 trig 1_2-yes
234 trig 1_3-yes
235 trig 1_3-yes
236 disconnect no_privs;
237 connection yes_privs;
238 select current_user;
239 current_user
240 test_yesprivs@localhost
241 use no_priv_db;
242 drop trigger trg1_3;
243 ERROR HY000: Trigger does not exist
244 use priv_db;
245 drop trigger trg1_3;
246 use no_priv_db;
247 drop trigger trg1_4;
248 ERROR HY000: Trigger does not exist
249 use priv_db;
250 drop trigger trg1_4;
251 disconnect yes_privs;
252 connection default;
253 select current_user;
254 current_user
255 root@localhost
256 drop table priv_db.t1;
257 drop table no_priv_db.t1;
258 drop database if exists priv_db;
259 drop database if exists no_priv_db;
260 drop user test_yesprivs@localhost;
261 drop user test_noprivs@localhost;
262
263 ######### Testcase for table level: ########
264 ---------------------------------------------------
265 drop database if exists priv_db;
266 create database priv_db;
267 use priv_db;
268 create table t1 (f1 char(20)) engine= innodb;
269 create User test_yesprivs@localhost;
270 set password for test_yesprivs@localhost = password('PWD');
271 revoke ALL PRIVILEGES, GRANT OPTION FROM test_yesprivs@localhost;
272 create User test_noprivs@localhost;
273 set password for test_noprivs@localhost = password('PWD');
274 revoke ALL PRIVILEGES, GRANT OPTION FROM test_noprivs@localhost;
275 connect yes_privs,localhost,test_yesprivs,PWD,"*NO-ONE*",$MASTER_MYPORT,$MASTER_MYSOCK;
276 connection yes_privs;
277 connect no_privs,localhost,test_noprivs,PWD,"*NO-ONE*",$MASTER_MYPORT,$MASTER_MYSOCK;
278 connection no_privs;
279
280 no trigger privilege on table level for create:
281 -----------------------------------------------
282 connection default;
283 select current_user;
284 current_user
285 root@localhost
286 show triggers;
287 Trigger Event Table Statement Timing Created sql_mode Definer character_set_client collation_connection Database Collation
288 grant select, insert, update on priv_db.t1 to test_yesprivs@localhost;
289 show grants for test_yesprivs@localhost;
290 Grants for test_yesprivs@localhost
291 GRANT USAGE ON *.* TO `test_yesprivs`@`localhost` IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
292 GRANT SELECT, INSERT, UPDATE ON `priv_db`.`t1` TO `test_yesprivs`@`localhost`
293 grant select, update, insert on priv_db.t1 to test_noprivs@localhost;
294 show grants for test_noprivs@localhost;
295 Grants for test_noprivs@localhost
296 GRANT USAGE ON *.* TO `test_noprivs`@`localhost` IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
297 GRANT SELECT, INSERT, UPDATE ON `priv_db`.`t1` TO `test_noprivs`@`localhost`
298 connection yes_privs;
299 select current_user;
300 current_user
301 test_yesprivs@localhost
302 use priv_db;
303 show tables;
304 Tables_in_priv_db
305 t1
306 create trigger trg1_1 before INSERT on t1 for each row
307 set new.f1 = 'trig 1_1-no';
308 ERROR 42000: TRIGGER command denied to user 'test_yesprivs'@'localhost' for table `priv_db`.`t1`
309 connection no_privs;
310 select current_user;
311 current_user
312 test_noprivs@localhost
313 use priv_db;
314 insert into t1 (f1) values ('insert1-yes');
315 select f1 from t1 order by f1;
316 f1
317 insert1-yes
318 connection default;
319 select current_user;
320 current_user
321 root@localhost
322 show triggers;
323 Trigger Event Table Statement Timing Created sql_mode Definer character_set_client collation_connection Database Collation
324 show tables;
325 Tables_in_priv_db
326 t1
327 insert into t1 (f1) values ('insert2-yes');
328 select f1 from t1 order by f1;
329 f1
330 insert1-yes
331 insert2-yes
332 grant TRIGGER on priv_db.t1 to test_yesprivs@localhost;
333 show grants for test_yesprivs@localhost;
334 Grants for test_yesprivs@localhost
335 GRANT USAGE ON *.* TO `test_yesprivs`@`localhost` IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
336 GRANT SELECT, INSERT, UPDATE, TRIGGER ON `priv_db`.`t1` TO `test_yesprivs`@`localhost`
337
338 trigger privilege on table level for create:
339 --------------------------------------------
340 connection yes_privs;
341 select current_user;
342 current_user
343 test_yesprivs@localhost
344 show triggers;
345 Trigger Event Table Statement Timing Created sql_mode Definer character_set_client collation_connection Database Collation
346 create trigger trg1_2 before INSERT on t1 for each row
347 set new.f1 = 'trig 1_2-yes';
348 connection no_privs;
349 select current_user;
350 current_user
351 test_noprivs@localhost
352 insert into t1 (f1) values ('insert3-no');
353 select f1 from t1 order by f1;
354 f1
355 insert1-yes
356 insert2-yes
357 trig 1_2-yes
358 connection default;
359 select current_user;
360 current_user
361 root@localhost
362 insert into t1 (f1) values ('insert4-no');
363 select f1 from t1 order by f1;
364 f1
365 insert1-yes
366 insert2-yes
367 trig 1_2-yes
368 trig 1_2-yes
369 revoke TRIGGER on priv_db.t1 from test_yesprivs@localhost;
370 show grants for test_yesprivs@localhost;
371 Grants for test_yesprivs@localhost
372 GRANT USAGE ON *.* TO `test_yesprivs`@`localhost` IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
373 GRANT SELECT, INSERT, UPDATE ON `priv_db`.`t1` TO `test_yesprivs`@`localhost`
374
375 no trigger privilege on table level for drop:
376 ---------------------------------------------
377 connection yes_privs;
378 select current_user;
379 current_user
380 test_yesprivs@localhost
381 drop trigger trg1_2;
382 ERROR 42000: TRIGGER command denied to user 'test_yesprivs'@'localhost' for table `priv_db`.`t1`
383
384 no trigger privilege at activation time:
385 ----------------------------------------
386 connection no_privs;
387 select current_user;
388 current_user
389 test_noprivs@localhost
390 insert into t1 (f1) values ('insert5-no');
391 ERROR 42000: TRIGGER command denied to user 'test_yesprivs'@'localhost' for table `priv_db`.`t1`
392 select f1 from t1 order by f1;
393 f1
394 insert1-yes
395 insert2-yes
396 trig 1_2-yes
397 trig 1_2-yes
398 connection default;
399 select current_user;
400 current_user
401 root@localhost
402 grant TRIGGER on priv_db.t1 to test_yesprivs@localhost;
403
404 trigger privilege at activation time:
405 -------------------------------------
406 connection no_privs;
407 select current_user;
408 current_user
409 test_noprivs@localhost
410 insert into t1 (f1) values ('insert6-no');
411 select f1 from t1 order by f1;
412 f1
413 insert1-yes
414 insert2-yes
415 trig 1_2-yes
416 trig 1_2-yes
417 trig 1_2-yes
418
419 trigger privilege on table level for drop:
420 ------------------------------------------
421 connection yes_privs;
422 select current_user;
423 current_user
424 test_yesprivs@localhost
425 show grants for test_yesprivs@localhost;
426 Grants for test_yesprivs@localhost
427 GRANT USAGE ON *.* TO `test_yesprivs`@`localhost` IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
428 GRANT SELECT, INSERT, UPDATE, TRIGGER ON `priv_db`.`t1` TO `test_yesprivs`@`localhost`
429 drop trigger trg1_2;
430 connection no_privs;
431 select current_user;
432 current_user
433 test_noprivs@localhost
434 insert into t1 (f1) values ('insert7-yes');
435 select f1 from t1 order by f1;
436 f1
437 insert1-yes
438 insert2-yes
439 insert7-yes
440 trig 1_2-yes
441 trig 1_2-yes
442 trig 1_2-yes
443 connection default;
444 select current_user;
445 current_user
446 root@localhost
447 insert into t1 (f1) values ('insert8-yes');
448 select f1 from t1 order by f1;
449 f1
450 insert1-yes
451 insert2-yes
452 insert7-yes
453 insert8-yes
454 trig 1_2-yes
455 trig 1_2-yes
456 trig 1_2-yes
457
458 switch to table without having trigger priv for it:
459 ---------------------------------------------------
460 create table t2 (f1 char(20)) engine= innodb;
461 grant SELECT, INSERT, UPDATE on priv_db.t2 to test_yesprivs@localhost;
462 show grants for test_yesprivs@localhost;
463 Grants for test_yesprivs@localhost
464 GRANT USAGE ON *.* TO `test_yesprivs`@`localhost` IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
465 GRANT SELECT, INSERT, UPDATE ON `priv_db`.`t2` TO `test_yesprivs`@`localhost`
466 GRANT SELECT, INSERT, UPDATE, TRIGGER ON `priv_db`.`t1` TO `test_yesprivs`@`localhost`
467 grant SELECT, INSERT, UPDATE on priv_db.t2 to test_noprivs@localhost;
468 show grants for test_noprivs@localhost;
469 Grants for test_noprivs@localhost
470 GRANT USAGE ON *.* TO `test_noprivs`@`localhost` IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
471 GRANT SELECT, INSERT, UPDATE ON `priv_db`.`t1` TO `test_noprivs`@`localhost`
472 GRANT SELECT, INSERT, UPDATE ON `priv_db`.`t2` TO `test_noprivs`@`localhost`
473
474 use table with trigger privilege and without...:
475 ------------------------------------------------
476 connection yes_privs;
477 select current_user;
478 current_user
479 test_yesprivs@localhost
480 create trigger trg2_1 before INSERT on t2 for each row
481 set new.f1 = 'trig 2_1-no';
482 ERROR 42000: TRIGGER command denied to user 'test_yesprivs'@'localhost' for table `priv_db`.`t2`
483 create trigger trg1_3 before INSERT on t1 for each row
484 set new.f1 = 'trig 1_3-yes';
485 create trigger trg2_2 before UPDATE on t2 for each row
486 set new.f1 = 'trig 2_2-no';
487 ERROR 42000: TRIGGER command denied to user 'test_yesprivs'@'localhost' for table `priv_db`.`t2`
488 create trigger trg1_4 before UPDATE on t1 for each row
489 set new.f1 = 'trig 1_4-yes';
490 show triggers;
491 Trigger Event Table Statement Timing Created sql_mode Definer character_set_client collation_connection Database Collation
492 trg1_3 INSERT t1 set new.f1 = 'trig 1_3-yes' BEFORE # STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION test_yesprivs@localhost latin1 latin1_swedish_ci utf8mb4_uca1400_ai_ci
493 trg1_4 UPDATE t1 set new.f1 = 'trig 1_4-yes' BEFORE # STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION test_yesprivs@localhost latin1 latin1_swedish_ci utf8mb4_uca1400_ai_ci
494 connection no_privs;
495 select current_user;
496 current_user
497 test_noprivs@localhost
498 insert into t2 (f1) values ('insert9-yes');
499 select f1 from t2 order by f1;
500 f1
501 insert9-yes
502 insert into t1 (f1) values ('insert10-no');
503 select f1 from t1 order by f1;
504 f1
505 insert1-yes
506 insert2-yes
507 insert7-yes
508 insert8-yes
509 trig 1_2-yes
510 trig 1_2-yes
511 trig 1_2-yes
512 trig 1_3-yes
513 disconnect no_privs;
514 connection yes_privs;
515 select current_user;
516 current_user
517 test_yesprivs@localhost
518 drop trigger trg2_1;
519 ERROR HY000: Trigger does not exist
520 drop trigger trg1_3;
521 drop trigger trg2_2;
522 ERROR HY000: Trigger does not exist
523 drop trigger trg1_4;
524 disconnect yes_privs;
525 connection default;
526 select current_user;
527 current_user
528 root@localhost
529 drop database if exists priv_db;
530 drop user test_yesprivs@localhost;
531 drop user test_noprivs@localhost;
532
533 #### Testcase for mix of user(global) and db level: ####
534 --------------------------------------------------------
535 drop database if exists priv_db;
536 drop database if exists no_priv_db;
537 create database priv_db;
538 create database no_priv_db;
539 use priv_db;
540 create table t1 (f1 char(20)) engine= innodb;
541 use no_priv_db;
542 create table t1 (f1 char(20)) engine= innodb;
543 create User test_yesprivs@localhost;
544 set password for test_yesprivs@localhost = password('PWD');
545 revoke ALL PRIVILEGES, GRANT OPTION FROM test_yesprivs@localhost;
546 grant ALL on *.* to test_yesprivs@localhost;
547 show grants for test_yesprivs@localhost;
548 Grants for test_yesprivs@localhost
549 GRANT ALL PRIVILEGES ON *.* TO `test_yesprivs`@`localhost` IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
550 create User test_noprivs@localhost;
551 set password for test_noprivs@localhost = password('PWD');
552 revoke ALL PRIVILEGES, GRANT OPTION FROM test_noprivs@localhost;
553 grant SELECT,INSERT on *.* to test_noprivs@localhost;
554 show grants for test_noprivs@localhost;
555 Grants for test_noprivs@localhost
556 GRANT SELECT, INSERT ON *.* TO `test_noprivs`@`localhost` IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
557 connect yes_privs,localhost,test_yesprivs,PWD,test,$MASTER_MYPORT,$MASTER_MYSOCK;
558 connect no_privs,localhost,test_noprivs,PWD,test,$MASTER_MYPORT,$MASTER_MYSOCK;
559 connection yes_privs;
560 select current_user;
561 current_user
562 test_yesprivs@localhost
563
564 trigger privilege on user level for create:
565 -------------------------------------------
566 use priv_db;
567 create trigger trg1_1 before INSERT on t1 for each row
568 set new.f1 = 'trig 1_1-yes';
569 insert into t1 (f1) values ('insert-no');
570 select f1 from t1 order by f1;
571 f1
572 trig 1_1-yes
573 use no_priv_db;
574 create trigger priv_db.trg1_5 before UPDATE on priv_db.t1
575 for each row
576 set new.f1 = 'trig 1_5-yes';
577 insert into priv_db.t1 (f1) values ('insert-no');
578 select f1 from priv_db.t1 order by f1;
579 f1
580 trig 1_1-yes
581 trig 1_1-yes
582 drop trigger priv_db.trg1_5;
583 connection no_privs;
584 select current_user;
585 current_user
586 test_noprivs@localhost
587 use priv_db;
588 insert into t1 (f1) values ('insert-no');
589 select f1 from t1 order by f1;
590 f1
591 trig 1_1-yes
592 trig 1_1-yes
593 trig 1_1-yes
594 connection default;
595 select current_user;
596 current_user
597 root@localhost
598 use priv_db;
599 insert into t1 (f1) values ('insert-no');
600 select f1 from t1 order by f1;
601 f1
602 trig 1_1-yes
603 trig 1_1-yes
604 trig 1_1-yes
605 trig 1_1-yes
606 revoke TRIGGER on *.* from test_yesprivs@localhost;
607 show grants for test_yesprivs@localhost;
608 Grants for test_yesprivs@localhost
609 GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, BINLOG MONITOR, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, CREATE TABLESPACE, DELETE HISTORY, SET USER, FEDERATED ADMIN, CONNECTION ADMIN, READ_ONLY ADMIN, REPLICATION SLAVE ADMIN, REPLICATION MASTER ADMIN, BINLOG ADMIN, BINLOG REPLAY, SLAVE MONITOR, SHOW CREATE ROUTINE ON *.* TO `test_yesprivs`@`localhost` IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
610 disconnect yes_privs;
611 connect yes_privs,localhost,test_yesprivs,PWD,test,$MASTER_MYPORT,$MASTER_MYSOCK;
612 select current_user;
613 current_user
614 test_yesprivs@localhost
615 use priv_db;
616 show triggers;
617 Trigger Event Table Statement Timing Created sql_mode Definer character_set_client collation_connection Database Collation
618 trg1_1 INSERT t1 NULL BEFORE # STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION NULL latin1 latin1_swedish_ci utf8mb4_uca1400_ai_ci
619 select * from information_schema.triggers;
620 TRIGGER_CATALOG TRIGGER_SCHEMA TRIGGER_NAME EVENT_MANIPULATION EVENT_OBJECT_CATALOG EVENT_OBJECT_SCHEMA EVENT_OBJECT_TABLE ACTION_ORDER ACTION_CONDITION ACTION_STATEMENT ACTION_ORIENTATION ACTION_TIMING ACTION_REFERENCE_OLD_TABLE ACTION_REFERENCE_NEW_TABLE ACTION_REFERENCE_OLD_ROW ACTION_REFERENCE_NEW_ROW CREATED SQL_MODE DEFINER CHARACTER_SET_CLIENT COLLATION_CONNECTION DATABASE_COLLATION
621 def mtr gs_insert INSERT def mtr global_suppressions 1 NULL NULL ROW BEFORE NULL NULL OLD NEW # NULL latin1 latin1_swedish_ci utf8mb4_uca1400_ai_ci
622 def mtr ts_insert INSERT def mtr test_suppressions 1 NULL NULL ROW BEFORE NULL NULL OLD NEW # NULL latin1 latin1_swedish_ci utf8mb4_uca1400_ai_ci
623 def priv_db trg1_1 INSERT def priv_db t1 1 NULL NULL ROW BEFORE NULL NULL OLD NEW # STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION NULL latin1 latin1_swedish_ci utf8mb4_uca1400_ai_ci
624 drop trigger trg1_1;
625 ERROR 42000: TRIGGER command denied to user 'test_yesprivs'@'localhost' for table `priv_db`.`t1`
626 connection default;
627 select current_user;
628 current_user
629 root@localhost
630 show grants;
631 Grants for root@localhost
632 GRANT ALL PRIVILEGES ON *.* TO `root`@`localhost` WITH GRANT OPTION
633 GRANT PROXY ON ''@'%' TO 'root'@'localhost' WITH GRANT OPTION
634 drop trigger trg1_1;
635 use priv_db;
636
637 no trigger privilege on db level for create:
638 --------------------------------------------
639 connection yes_privs;
640 select current_user;
641 current_user
642 test_yesprivs@localhost
643 create trigger trg1_1 before INSERT on t1 for each row
644 set new.f1 = 'trig 1_1-no';
645 ERROR 42000: TRIGGER command denied to user 'test_yesprivs'@'localhost' for table `priv_db`.`t1`
646 connection no_privs;
647 select current_user;
648 current_user
649 test_noprivs@localhost
650 use priv_db;
651 insert into t1 (f1) values ('insert-yes');
652 select f1 from t1 order by f1;
653 f1
654 insert-yes
655 trig 1_1-yes
656 trig 1_1-yes
657 trig 1_1-yes
658 trig 1_1-yes
659 connection default;
660 select current_user;
661 current_user
662 root@localhost
663 grant TRIGGER on priv_db.* to test_yesprivs@localhost;
664 show grants for test_yesprivs@localhost;
665 Grants for test_yesprivs@localhost
666 GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, BINLOG MONITOR, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, CREATE TABLESPACE, DELETE HISTORY, SET USER, FEDERATED ADMIN, CONNECTION ADMIN, READ_ONLY ADMIN, REPLICATION SLAVE ADMIN, REPLICATION MASTER ADMIN, BINLOG ADMIN, BINLOG REPLAY, SLAVE MONITOR, SHOW CREATE ROUTINE ON *.* TO `test_yesprivs`@`localhost` IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
667 GRANT TRIGGER ON `priv_db`.* TO `test_yesprivs`@`localhost`
668
669 trigger privilege on db level for create:
670 -----------------------------------------
671 connection yes_privs;
672 select current_user;
673 current_user
674 test_yesprivs@localhost
675 use priv_db;
676 create trigger trg1_2 before INSERT on t1 for each row
677 set new.f1 = 'trig 1_2-yes';
678 create trigger no_priv_db.trg1_9 before insert on no_priv_db.t1
679 for each row
680 set new.f1 = 'trig 1_9-yes';
681 ERROR 42000: TRIGGER command denied to user 'test_yesprivs'@'localhost' for table `no_priv_db`.`t1`
682 use no_priv_db;
683 create trigger trg1_2 before INSERT on t1 for each row
684 set new.f1 = 'trig 1_2-no';
685 ERROR 42000: TRIGGER command denied to user 'test_yesprivs'@'localhost' for table `no_priv_db`.`t1`
686 create trigger priv_db.trg1_9 before UPDATE on priv_db.t1
687 for each row
688 set new.f1 = 'trig 1_9-yes';
689 connection no_privs;
690 select current_user;
691 current_user
692 test_noprivs@localhost
693 use priv_db;
694 insert into t1 (f1) values ('insert-yes');
695 select f1 from t1 order by f1;
696 f1
697 insert-yes
698 trig 1_1-yes
699 trig 1_1-yes
700 trig 1_1-yes
701 trig 1_1-yes
702 trig 1_2-yes
703 use no_priv_db;
704 insert into t1 (f1) values ('insert-yes');
705 select f1 from t1 order by f1;
706 f1
707 insert-yes
708 drop trigger priv_db.trg1_9;
709 ERROR 42000: TRIGGER command denied to user 'test_noprivs'@'localhost' for table `priv_db`.`t1`
710 connection default;
711 select current_user;
712 current_user
713 root@localhost
714 drop trigger priv_db.trg1_9;
715 revoke TRIGGER on priv_db.* from test_yesprivs@localhost;
716 use priv_db;
717 insert into t1 (f1) values ('insert-yes');
718 ERROR 42000: TRIGGER command denied to user 'test_yesprivs'@'localhost' for table `priv_db`.`t1`
719 select f1 from t1 order by f1;
720 f1
721 insert-yes
722 trig 1_1-yes
723 trig 1_1-yes
724 trig 1_1-yes
725 trig 1_1-yes
726 trig 1_2-yes
727 grant TRIGGER on *.* to test_yesprivs@localhost;
728 show grants for test_yesprivs@localhost;
729 Grants for test_yesprivs@localhost
730 GRANT ALL PRIVILEGES ON *.* TO `test_yesprivs`@`localhost` IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
731 connection yes_privs;
732 select current_user;
733 current_user
734 test_yesprivs@localhost
735 use no_priv_db;
736 create trigger trg1_2 before INSERT on t1 for each row
737 set new.f1 = 'trig 1_2-no';
738 ERROR 42000: TRIGGER command denied to user 'test_yesprivs'@'localhost' for table `no_priv_db`.`t1`
739 connection no_privs;
740 select current_user;
741 current_user
742 test_noprivs@localhost
743 use priv_db;
744 insert into t1 (f1) values ('insert-no');
745 select f1 from t1 order by f1;
746 f1
747 insert-yes
748 trig 1_1-yes
749 trig 1_1-yes
750 trig 1_1-yes
751 trig 1_1-yes
752 trig 1_2-yes
753 trig 1_2-yes
754 use no_priv_db;
755 insert into t1 (f1) values ('insert-yes');
756 select f1 from t1 order by f1;
757 f1
758 insert-yes
759 insert-yes
760 disconnect yes_privs;
761 connect yes_privs,localhost,test_yesprivs,PWD,test,$MASTER_MYPORT,$MASTER_MYSOCK;
762 select current_user;
763 current_user
764 test_yesprivs@localhost
765 use no_priv_db;
766 create trigger trg1_2 before INSERT on t1 for each row
767 set new.f1 = 'trig 1_2-yes';
768 disconnect yes_privs;
769 connection no_privs;
770 select current_user;
771 current_user
772 test_noprivs@localhost
773 use priv_db;
774 insert into t1 (f1) values ('insert-no');
775 select f1 from t1 order by f1;
776 f1
777 insert-yes
778 trig 1_1-yes
779 trig 1_1-yes
780 trig 1_1-yes
781 trig 1_1-yes
782 trig 1_2-yes
783 trig 1_2-yes
784 trig 1_2-yes
785 use no_priv_db;
786 insert into t1 (f1) values ('insert-no');
787 select f1 from t1 order by f1;
788 f1
789 insert-yes
790 insert-yes
791 trig 1_2-yes
792 disconnect no_privs;
793 connection default;
794 select current_user;
795 current_user
796 root@localhost
797 drop database if exists priv_db;
798 drop database if exists no_priv_db;
799 drop database if exists h1;
800 drop user test_yesprivs@localhost;
801 drop user test_noprivs@localhost;
802
803 ####### Testcase for mix of db and table level: #######
804 -------------------------------------------------------
805 drop database if exists priv1_db;
806 drop database if exists priv2_db;
807 create database priv1_db;
808 create database priv2_db;
809 use priv1_db;
810 create table t1 (f1 char(20)) engine= innodb;
811 create table t2 (f1 char(20)) engine= innodb;
812 use priv2_db;
813 create table t1 (f1 char(20)) engine= innodb;
814 create User test_yesprivs@localhost;
815 set password for test_yesprivs@localhost = password('PWD');
816 revoke ALL PRIVILEGES, GRANT OPTION FROM test_yesprivs@localhost;
817 grant ALL on priv1_db.* to test_yesprivs@localhost;
818 grant SELECT,UPDATE on priv2_db.* to test_yesprivs@localhost;
819 show grants for test_yesprivs@localhost;
820 Grants for test_yesprivs@localhost
821 GRANT USAGE ON *.* TO `test_yesprivs`@`localhost` IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
822 GRANT ALL PRIVILEGES ON `priv1_db`.* TO `test_yesprivs`@`localhost`
823 GRANT SELECT, UPDATE ON `priv2_db`.* TO `test_yesprivs`@`localhost`
824 create User test_noprivs@localhost;
825 set password for test_noprivs@localhost = password('PWD');
826 revoke ALL PRIVILEGES, GRANT OPTION FROM test_noprivs@localhost;
827 grant SELECT,INSERT,UPDATE on priv1_db.* to test_noprivs@localhost;
828 grant SELECT,INSERT on priv2_db.* to test_noprivs@localhost;
829 show grants for test_noprivs@localhost;
830 Grants for test_noprivs@localhost
831 GRANT USAGE ON *.* TO `test_noprivs`@`localhost` IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
832 GRANT SELECT, INSERT, UPDATE ON `priv1_db`.* TO `test_noprivs`@`localhost`
833 GRANT SELECT, INSERT ON `priv2_db`.* TO `test_noprivs`@`localhost`
834 connect yes_privs,localhost,test_yesprivs,PWD,priv1_db,$MASTER_MYPORT,$MASTER_MYSOCK;
835 connect no_privs,localhost,test_noprivs,PWD,priv1_db,$MASTER_MYPORT,$MASTER_MYSOCK;
836
837 trigger privilege on one db1 db level, not on db2
838 -------------------------------------------------
839 connection yes_privs;
840 select current_user;
841 current_user
842 test_yesprivs@localhost
843 use priv1_db;
844 create trigger trg1_1 before INSERT on t1 for each row
845 set new.f1 = 'trig 1_1-yes';
846 create trigger trg2_1 before INSERT on t2 for each row
847 set new.f1 = 'trig 2_1-yes';
848 use priv2_db;
849 create trigger trg1_1 before INSERT on t1 for each row
850 set new.f1 = 'trig1_1-yes';
851 ERROR 42000: TRIGGER command denied to user 'test_yesprivs'@'localhost' for table `priv2_db`.`t1`
852 connection no_privs;
853 select current_user;
854 current_user
855 test_noprivs@localhost
856 insert into t1 (f1) values ('insert1_no');
857 select f1 from t1 order by f1;
858 f1
859 trig 1_1-yes
860 insert into t2 (f1) values ('insert1_no');
861 select f1 from t2 order by f1;
862 f1
863 trig 2_1-yes
864 insert into priv2_db.t1 (f1) values ('insert21-yes');
865 select f1 from priv2_db.t1 order by f1;
866 f1
867 insert21-yes
868 use priv2_db;
869 insert into t1 (f1) values ('insert1_yes');
870 select f1 from t1 order by f1;
871 f1
872 insert1_yes
873 insert21-yes
874 insert into priv1_db.t1 (f1) values ('insert11-no');
875 select f1 from priv1_db.t1 order by f1;
876 f1
877 trig 1_1-yes
878 trig 1_1-yes
879 insert into priv1_db.t2 (f1) values ('insert22-no');
880 select f1 from priv1_db.t2 order by f1;
881 f1
882 trig 2_1-yes
883 trig 2_1-yes
884
885 revoke trigger privilege on table level (not existing)
886 ------------------------------------------------------
887 connection default;
888 select current_user;
889 current_user
890 root@localhost
891 use priv1_db;
892 revoke TRIGGER on priv1_db.t1 from test_yesprivs@localhost;
893 ERROR 42000: There is no such grant defined for user 'test_yesprivs' on host 'localhost' on table 't1'
894 show grants for test_yesprivs@localhost;
895 Grants for test_yesprivs@localhost
896 GRANT USAGE ON *.* TO `test_yesprivs`@`localhost` IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
897 GRANT ALL PRIVILEGES ON `priv1_db`.* TO `test_yesprivs`@`localhost`
898 GRANT SELECT, UPDATE ON `priv2_db`.* TO `test_yesprivs`@`localhost`
899 connection yes_privs;
900 select current_user;
901 current_user
902 test_yesprivs@localhost
903 drop trigger trg1_1;
904 ERROR HY000: Trigger does not exist
905 drop trigger trg2_1;
906 ERROR HY000: Trigger does not exist
907 use priv1_db;
908 drop trigger trg1_1;
909 drop trigger trg2_1;
910 connection default;
911 select current_user;
912 current_user
913 root@localhost
914 use priv1_db;
915 revoke TRIGGER on priv1_db.* from test_yesprivs@localhost;
916
917 no trigger privilege on table level for create:
918 -----------------------------------------------
919 connection yes_privs;
920 select current_user;
921 current_user
922 test_yesprivs@localhost
923 use priv1_db;
924 create trigger trg1_1 before INSERT on t1 for each row
925 set new.f1 = 'trig 1_1-no';
926 ERROR 42000: TRIGGER command denied to user 'test_yesprivs'@'localhost' for table `priv1_db`.`t1`
927 connection default;
928 select current_user;
929 current_user
930 root@localhost
931 show triggers;
932 Trigger Event Table Statement Timing Created sql_mode Definer character_set_client collation_connection Database Collation
933 grant TRIGGER on priv1_db.t1 to test_yesprivs@localhost;
934 show grants for test_yesprivs@localhost;
935 Grants for test_yesprivs@localhost
936 GRANT USAGE ON *.* TO `test_yesprivs`@`localhost` IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
937 GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, EVENT, DELETE HISTORY, SHOW CREATE ROUTINE ON `priv1_db`.* TO `test_yesprivs`@`localhost`
938 GRANT SELECT, UPDATE ON `priv2_db`.* TO `test_yesprivs`@`localhost`
939 GRANT TRIGGER ON `priv1_db`.`t1` TO `test_yesprivs`@`localhost`
940
941 trigger privilege on table level for create:
942 --------------------------------------------
943 connection yes_privs;
944 select current_user;
945 current_user
946 test_yesprivs@localhost
947 show triggers;
948 Trigger Event Table Statement Timing Created sql_mode Definer character_set_client collation_connection Database Collation
949 create trigger trg1_2 before INSERT on t1 for each row
950 set new.f1 = 'trig 1_2-yes';
951 create trigger trg2_1 before INSERT on t2 for each row
952 set new.f1 = 'trig 2_1-no';
953 ERROR 42000: TRIGGER command denied to user 'test_yesprivs'@'localhost' for table `priv1_db`.`t2`
954 connection no_privs;
955 select current_user;
956 current_user
957 test_noprivs@localhost
958 use priv1_db;
959 insert into t1 (f1) values ('insert2-no');
960 select f1 from t1 order by f1;
961 f1
962 trig 1_1-yes
963 trig 1_1-yes
964 trig 1_2-yes
965 insert into t2 (f1) values ('insert2-yes');
966 select f1 from t2 order by f1;
967 f1
968 insert2-yes
969 trig 2_1-yes
970 trig 2_1-yes
971 insert into priv2_db.t1 (f1) values ('insert22-yes');
972 select f1 from priv2_db.t1 order by f1;
973 f1
974 insert1_yes
975 insert21-yes
976 insert22-yes
977 connection default;
978 select current_user;
979 current_user
980 root@localhost
981 grant TRIGGER on priv1_db.* to test_yesprivs@localhost;
982 show grants for test_yesprivs@localhost;
983 Grants for test_yesprivs@localhost
984 GRANT USAGE ON *.* TO `test_yesprivs`@`localhost` IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
985 GRANT ALL PRIVILEGES ON `priv1_db`.* TO `test_yesprivs`@`localhost`
986 GRANT SELECT, UPDATE ON `priv2_db`.* TO `test_yesprivs`@`localhost`
987 GRANT TRIGGER ON `priv1_db`.`t1` TO `test_yesprivs`@`localhost`
988 connection yes_privs;
989 select current_user;
990 current_user
991 test_yesprivs@localhost
992 create trigger trg2_1 before INSERT on t2 for each row
993 set new.f1 = 'trig 2_1-yes';
994 ERROR 42000: TRIGGER command denied to user 'test_yesprivs'@'localhost' for table `priv1_db`.`t2`
995 use priv1_db;
996 create trigger trg2_1 before INSERT on t2 for each row
997 set new.f1 = 'trig 2_1-yes';
998 connection no_privs;
999 select current_user;
1000 current_user
1001 test_noprivs@localhost
1002 use priv1_db;
1003 insert into t1 (f1) values ('insert3-no');
1004 select f1 from t1 order by f1;
1005 f1
1006 trig 1_1-yes
1007 trig 1_1-yes
1008 trig 1_2-yes
1009 trig 1_2-yes
1010 insert into t2 (f1) values ('insert3-no');
1011 select f1 from t2 order by f1;
1012 f1
1013 insert2-yes
1014 trig 2_1-yes
1015 trig 2_1-yes
1016 trig 2_1-yes
1017 use priv2_db;
1018 insert into priv1_db.t1 (f1) values ('insert12-no');
1019 select f1 from priv1_db.t1 order by f1;
1020 f1
1021 trig 1_1-yes
1022 trig 1_1-yes
1023 trig 1_2-yes
1024 trig 1_2-yes
1025 trig 1_2-yes
1026 insert into priv1_db.t2 (f1) values ('insert23-no');
1027 select f1 from priv1_db.t2 order by f1;
1028 f1
1029 insert2-yes
1030 trig 2_1-yes
1031 trig 2_1-yes
1032 trig 2_1-yes
1033 trig 2_1-yes
1034 disconnect no_privs;
1035 connection yes_privs;
1036 select current_user;
1037 current_user
1038 test_yesprivs@localhost
1039 drop trigger trg1_2;
1040 drop trigger trg2_1;
1041 disconnect yes_privs;
1042 connection default;
1043 select current_user;
1044 current_user
1045 root@localhost
1046 drop database if exists priv1_db;
1047 drop database if exists priv2_db;
1048 drop user test_yesprivs@localhost;
1049 drop user test_noprivs@localhost;
1050
1051 #### Testcase for trigger privilege on execution time ########
1052 --------------------------------------------------------------
1053 drop database if exists priv_db;
1054 create database priv_db;
1055 use priv_db;
1056 create table t1 (f1 char(20)) engine= innodb;
1057 create User test_yesprivs@localhost;
1058 set password for test_yesprivs@localhost = password('PWD');
1059 create User test_useprivs@localhost;
1060 set password for test_useprivs@localhost = password('PWD');
1061 revoke ALL PRIVILEGES, GRANT OPTION FROM test_yesprivs@localhost;
1062 revoke ALL PRIVILEGES, GRANT OPTION FROM test_useprivs@localhost;
1063 connect yes_privs,localhost,test_yesprivs,PWD,"*NO-ONE*",$MASTER_MYPORT,$MASTER_MYSOCK;
1064 connection yes_privs;
1065 connection default;
1066 select current_user;
1067 current_user
1068 root@localhost
1069 show triggers;
1070 Trigger Event Table Statement Timing Created sql_mode Definer character_set_client collation_connection Database Collation
1071 grant select, insert, update ,trigger
1072 on priv_db.t1 to test_yesprivs@localhost
1073 with grant option;
1074 grant select
1075 on priv_db.t1 to test_useprivs@localhost;
1076 show grants for test_yesprivs@localhost;
1077 Grants for test_yesprivs@localhost
1078 GRANT USAGE ON *.* TO `test_yesprivs`@`localhost` IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
1079 GRANT SELECT, INSERT, UPDATE, TRIGGER ON `priv_db`.`t1` TO `test_yesprivs`@`localhost` WITH GRANT OPTION
1080 connection yes_privs;
1081 select current_user;
1082 current_user
1083 test_yesprivs@localhost
1084 use priv_db;
1085 create trigger trg1_1 before INSERT on t1 for each row
1086 set new.f1 = 'trig 1_1-yes';
1087 grant insert on t1 to test_useprivs@localhost;
1088 prepare ins1 from 'insert into t1 (f1) values (''insert1-no'')';
1089 execute ins1;
1090 select f1 from t1 order by f1;
1091 f1
1092 trig 1_1-yes
1093 prepare ins1 from 'insert into t1 (f1) values (''insert2-no'')';
1094 connect use_privs,localhost,test_useprivs,PWD,priv_db,$MASTER_MYPORT,$MASTER_MYSOCK;
1095 connection use_privs;
1096 select current_user;
1097 current_user
1098 test_useprivs@localhost
1099 prepare ins1 from 'insert into t1 (f1) values (''insert3-no'')';
1100 execute ins1;
1101 select f1 from t1 order by f1;
1102 f1
1103 trig 1_1-yes
1104 trig 1_1-yes
1105 connection default;
1106 select current_user;
1107 current_user
1108 root@localhost
1109 revoke TRIGGER on priv_db.t1 from test_yesprivs@localhost;
1110 show grants for test_yesprivs@localhost;
1111 Grants for test_yesprivs@localhost
1112 GRANT USAGE ON *.* TO `test_yesprivs`@`localhost` IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
1113 GRANT SELECT, INSERT, UPDATE ON `priv_db`.`t1` TO `test_yesprivs`@`localhost` WITH GRANT OPTION
1114 connection yes_privs;
1115 select current_user;
1116 current_user
1117 test_yesprivs@localhost
1118 execute ins1;
1119 ERROR 42000: TRIGGER command denied to user 'test_yesprivs'@'localhost' for table `priv_db`.`t1`
1120 select f1 from t1 order by f1;
1121 f1
1122 trig 1_1-yes
1123 trig 1_1-yes
1124 prepare ins1 from 'insert into t1 (f1) values (''insert4-no'')';
1125 connection use_privs;
1126 select current_user;
1127 current_user
1128 test_useprivs@localhost
1129 prepare ins1 from 'insert into t1 (f1) values (''insert5-no'')';
1130 execute ins1;
1131 ERROR 42000: TRIGGER command denied to user 'test_yesprivs'@'localhost' for table `priv_db`.`t1`
1132 select f1 from t1 order by f1;
1133 f1
1134 trig 1_1-yes
1135 trig 1_1-yes
1136 connection default;
1137 select current_user;
1138 current_user
1139 root@localhost
1140 grant TRIGGER on priv_db.t1 to test_yesprivs@localhost;
1141 show grants for test_yesprivs@localhost;
1142 Grants for test_yesprivs@localhost
1143 GRANT USAGE ON *.* TO `test_yesprivs`@`localhost` IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
1144 GRANT SELECT, INSERT, UPDATE, TRIGGER ON `priv_db`.`t1` TO `test_yesprivs`@`localhost` WITH GRANT OPTION
1145 connection yes_privs;
1146 select current_user;
1147 current_user
1148 test_yesprivs@localhost
1149 execute ins1;
1150 select f1 from t1 order by f1;
1151 f1
1152 trig 1_1-yes
1153 trig 1_1-yes
1154 trig 1_1-yes
1155 prepare ins1 from 'insert into t1 (f1) values (''insert6-no'')';
1156 connection use_privs;
1157 select current_user;
1158 current_user
1159 test_useprivs@localhost
1160 execute ins1;
1161 select f1 from t1 order by f1;
1162 f1
1163 trig 1_1-yes
1164 trig 1_1-yes
1165 trig 1_1-yes
1166 trig 1_1-yes
1167 prepare ins1 from 'insert into t1 (f1) values (''insert7-no'')';
1168 connection default;
1169 select current_user;
1170 current_user
1171 root@localhost
1172 revoke TRIGGER on priv_db.t1 from test_yesprivs@localhost;
1173 show grants for test_yesprivs@localhost;
1174 Grants for test_yesprivs@localhost
1175 GRANT USAGE ON *.* TO `test_yesprivs`@`localhost` IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
1176 GRANT SELECT, INSERT, UPDATE ON `priv_db`.`t1` TO `test_yesprivs`@`localhost` WITH GRANT OPTION
1177 connection yes_privs;
1178 select current_user;
1179 current_user
1180 test_yesprivs@localhost
1181 execute ins1;
1182 ERROR 42000: TRIGGER command denied to user 'test_yesprivs'@'localhost' for table `priv_db`.`t1`
1183 select f1 from t1 order by f1;
1184 f1
1185 trig 1_1-yes
1186 trig 1_1-yes
1187 trig 1_1-yes
1188 trig 1_1-yes
1189 connection use_privs;
1190 select current_user;
1191 current_user
1192 test_useprivs@localhost
1193 execute ins1;
1194 ERROR 42000: TRIGGER command denied to user 'test_yesprivs'@'localhost' for table `priv_db`.`t1`
1195 select f1 from t1 order by f1;
1196 f1
1197 trig 1_1-yes
1198 trig 1_1-yes
1199 trig 1_1-yes
1200 trig 1_1-yes
1201 connection default;
1202 select current_user;
1203 current_user
1204 root@localhost
1205 grant TRIGGER on priv_db.t1 to test_yesprivs@localhost;
1206 show grants for test_yesprivs@localhost;
1207 Grants for test_yesprivs@localhost
1208 GRANT USAGE ON *.* TO `test_yesprivs`@`localhost` IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
1209 GRANT SELECT, INSERT, UPDATE, TRIGGER ON `priv_db`.`t1` TO `test_yesprivs`@`localhost` WITH GRANT OPTION
1210 connection yes_privs;
1211 select current_user;
1212 current_user
1213 test_yesprivs@localhost
1214 execute ins1;
1215 select f1 from t1 order by f1;
1216 f1
1217 trig 1_1-yes
1218 trig 1_1-yes
1219 trig 1_1-yes
1220 trig 1_1-yes
1221 trig 1_1-yes
1222 connection use_privs;
1223 select current_user;
1224 current_user
1225 test_useprivs@localhost
1226 execute ins1;
1227 select f1 from t1 order by f1;
1228 f1
1229 trig 1_1-yes
1230 trig 1_1-yes
1231 trig 1_1-yes
1232 trig 1_1-yes
1233 trig 1_1-yes
1234 trig 1_1-yes
1235 connection default;
1236 select current_user;
1237 current_user
1238 root@localhost
1239 revoke TRIGGER on priv_db.t1 from test_yesprivs@localhost;
1240 show grants for test_yesprivs@localhost;
1241 Grants for test_yesprivs@localhost
1242 GRANT USAGE ON *.* TO `test_yesprivs`@`localhost` IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
1243 GRANT SELECT, INSERT, UPDATE ON `priv_db`.`t1` TO `test_yesprivs`@`localhost` WITH GRANT OPTION
1244 connection yes_privs;
1245 select current_user;
1246 current_user
1247 test_yesprivs@localhost
1248 execute ins1;
1249 ERROR 42000: TRIGGER command denied to user 'test_yesprivs'@'localhost' for table `priv_db`.`t1`
1250 select f1 from t1 order by f1;
1251 f1
1252 trig 1_1-yes
1253 trig 1_1-yes
1254 trig 1_1-yes
1255 trig 1_1-yes
1256 trig 1_1-yes
1257 trig 1_1-yes
1258 deallocate prepare ins1;
1259 connection use_privs;
1260 select current_user;
1261 current_user
1262 test_useprivs@localhost
1263 execute ins1;
1264 ERROR 42000: TRIGGER command denied to user 'test_yesprivs'@'localhost' for table `priv_db`.`t1`
1265 select f1 from t1 order by f1;
1266 f1
1267 trig 1_1-yes
1268 trig 1_1-yes
1269 trig 1_1-yes
1270 trig 1_1-yes
1271 trig 1_1-yes
1272 trig 1_1-yes
1273 deallocate prepare ins1;
1274 connection default;
1275 select current_user;
1276 current_user
1277 root@localhost
1278 grant TRIGGER on priv_db.t1 to test_yesprivs@localhost;
1279 show grants for test_yesprivs@localhost;
1280 Grants for test_yesprivs@localhost
1281 GRANT USAGE ON *.* TO `test_yesprivs`@`localhost` IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
1282 GRANT SELECT, INSERT, UPDATE, TRIGGER ON `priv_db`.`t1` TO `test_yesprivs`@`localhost` WITH GRANT OPTION
1283 connection yes_privs;
1284 select current_user;
1285 current_user
1286 test_yesprivs@localhost
1287 drop trigger trg1_1;
1288 connection default;
1289 select current_user;
1290 current_user
1291 root@localhost
1292 disconnect yes_privs;
1293 connection default;
1294 select current_user;
1295 current_user
1296 root@localhost
1297 drop database if exists priv_db;
1298 drop user test_yesprivs@localhost;
1299 drop user test_useprivs@localhost;
1300
1301 ######### Testcase for definer: ########
1302 -----------------------------------------------
1303 drop database if exists priv_db;
1304 create database priv_db;
1305 use priv_db;
1306 create table t1 (f1 char(20)) engine= innodb;
1307 create User test_yesprivs@localhost;
1308 set password for test_yesprivs@localhost = password('PWD');
1309 revoke ALL PRIVILEGES, GRANT OPTION FROM test_yesprivs@localhost;
1310 connect yes_privs,localhost,test_yesprivs,PWD,"*NO-ONE*",$MASTER_MYPORT,$MASTER_MYSOCK;
1311 connection yes_privs;
1312 connection default;
1313 select current_user;
1314 current_user
1315 root@localhost
1316 create definer=not_ex_user@localhost trigger trg1_0
1317 before INSERT on t1 for each row
1318 set new.f1 = 'trig 1_0-yes';
1319 Warnings:
1320 Note 1449 The user specified as a definer ('not_ex_user'@'localhost') does not exist
1321 drop trigger trg1_0;
1322 create definer=test_yesprivs@localhost trigger trg1_0
1323 before INSERT on t1 for each row
1324 set new.f1 = 'trig 1_0-yes';
1325 grant select, insert, update
1326 on priv_db.t1 to test_yesprivs@localhost;
1327 connection yes_privs;
1328 select current_user;
1329 current_user
1330 test_yesprivs@localhost
1331 use priv_db;
1332 insert into t1 (f1) values ('insert-no');
1333 ERROR 42000: TRIGGER command denied to user 'test_yesprivs'@'localhost' for table `priv_db`.`t1`
1334 select f1 from t1 order by f1;
1335 f1
1336 drop trigger trg1_0;
1337 ERROR 42000: TRIGGER command denied to user 'test_yesprivs'@'localhost' for table `priv_db`.`t1`
1338 connection default;
1339 select current_user;
1340 current_user
1341 root@localhost
1342 grant select, insert, update ,trigger
1343 on priv_db.t1 to test_yesprivs@localhost;
1344 show grants for test_yesprivs@localhost;
1345 Grants for test_yesprivs@localhost
1346 GRANT USAGE ON *.* TO `test_yesprivs`@`localhost` IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
1347 GRANT SELECT, INSERT, UPDATE, TRIGGER ON `priv_db`.`t1` TO `test_yesprivs`@`localhost`
1348 connection yes_privs;
1349 select current_user;
1350 current_user
1351 test_yesprivs@localhost
1352 insert into t1 (f1) values ('insert-no');
1353 select f1 from t1 order by f1;
1354 f1
1355 trig 1_0-yes
1356 drop trigger trg1_0;
1357 create definer=not_ex_user@localhost trigger trg1_0
1358 before INSERT on t1 for each row
1359 set new.f1 = 'trig 1_0-yes';
1360 ERROR 42000: Access denied; you need (at least one of) the SET USER privilege(s) for this operation
1361 create definer=current_user trigger trg1_1
1362 before INSERT on t1 for each row
1363 set new.f1 = 'trig 1_1-yes';
1364 insert into t1 (f1) values ('insert-no');
1365 select f1 from t1 order by f1;
1366 f1
1367 trig 1_0-yes
1368 trig 1_1-yes
1369 create definer=test_yesprivs@localhost trigger trg1_2
1370 before UPDATE on t1 for each row
1371 set new.f1 = 'trig 1_2-yes';
1372 update t1 set f1 = 'update-yes' where f1 like '%trig%';
1373 select f1 from t1 order by f1;
1374 f1
1375 trig 1_2-yes
1376 trig 1_2-yes
1377 connection default;
1378 select current_user;
1379 current_user
1380 root@localhost
1381 grant trigger on priv_db.* to test_yesprivs@localhost
1382 with grant option;
1383 connection yes_privs;
1384 select current_user;
1385 current_user
1386 test_yesprivs@localhost
1387 show grants;
1388 Grants for test_yesprivs@localhost
1389 GRANT USAGE ON *.* TO `test_yesprivs`@`localhost` IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
1390 GRANT TRIGGER ON `priv_db`.* TO `test_yesprivs`@`localhost` WITH GRANT OPTION
1391 GRANT SELECT, INSERT, UPDATE, TRIGGER ON `priv_db`.`t1` TO `test_yesprivs`@`localhost`
1392 create definer=not_ex_user@localhost trigger trg1_3
1393 after UPDATE on t1 for each row
1394 set @var1 = 'trig 1_3-yes';
1395 ERROR 42000: Access denied; you need (at least one of) the SET USER privilege(s) for this operation
1396 connection default;
1397 select current_user;
1398 current_user
1399 root@localhost
1400 disconnect yes_privs;
1401 connection default;
1402 select current_user;
1403 current_user
1404 root@localhost
1405 drop database if exists priv_db;
1406 drop user test_yesprivs@localhost;
1407
1408 ######### Testcase for transactions: ########
1409 ----------------------------------------------------
1410 drop database if exists priv_db;
1411 create database priv_db;
1412 use priv_db;
1413 create table t1 (f1 char(20)) engine= innodb;
1414 create User test_yesprivs@localhost;
1415 set password for test_yesprivs@localhost = password('PWD');
1416 revoke ALL PRIVILEGES, GRANT OPTION FROM test_yesprivs@localhost;
1417 connection default;
1418 select current_user;
1419 current_user
1420 root@localhost
1421 grant select, insert, update ,trigger
1422 on priv_db.t1 to test_yesprivs@localhost;
1423 show grants for test_yesprivs@localhost;
1424 Grants for test_yesprivs@localhost
1425 GRANT USAGE ON *.* TO `test_yesprivs`@`localhost` IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
1426 GRANT SELECT, INSERT, UPDATE, TRIGGER ON `priv_db`.`t1` TO `test_yesprivs`@`localhost`
1427 connect yes_privs,localhost,test_yesprivs,PWD,priv_db,$MASTER_MYPORT,$MASTER_MYSOCK;
1428 connection yes_privs;
1429 select current_user;
1430 current_user
1431 test_yesprivs@localhost
1432 set autocommit=0;
1433 create definer=current_user trigger trg1_1
1434 before INSERT on t1 for each row
1435 set new.f1 = 'trig 1_1-yes';
1436 rollback work;
1437 insert into t1 (f1) values ('insert-no');
1438 select f1 from t1 order by f1;
1439 f1
1440 trig 1_1-yes
1441 create definer=test_yesprivs@localhost trigger trg1_2
1442 before UPDATE on t1 for each row
1443 set new.f1 = 'trig 1_2-yes';
1444 commit work;
1445 update t1 set f1 = 'update-yes' where f1 like '%trig%';
1446 select f1 from t1 order by f1;
1447 f1
1448 trig 1_2-yes
1449 commit work;
1450 drop trigger trg1_1;
1451 rollback work;
1452 drop trigger trg1_1;
1453 ERROR HY000: Trigger does not exist
1454 drop trigger trg1_2;
1455 commit work;
1456 set autocommit=1;
1457 connection default;
1458 select current_user;
1459 current_user
1460 root@localhost
1461 disconnect yes_privs;
1462 connection default;
1463 select current_user;
1464 current_user
1465 root@localhost
1466 drop database if exists priv_db;
1467 drop user test_yesprivs@localhost;
1468
1469 ####### Testcase for column privileges of triggers: #######
1470 -----------------------------------------------------------
1471 drop database if exists priv_db;
1472 drop database if exists no_priv_db;
1473 create database priv_db;
1474 use priv_db;
1475 create table t1 (f1 char(20)) engine= innodb;
1476 create table t2 (f1 char(20)) engine= innodb;
1477 create User test_yesprivs@localhost;
1478 set password for test_yesprivs@localhost = password('PWD');
1479 revoke ALL PRIVILEGES, GRANT OPTION FROM test_yesprivs@localhost;
1480 grant TRIGGER on priv_db.* to test_yesprivs@localhost;
1481 show grants for test_yesprivs@localhost;
1482 Grants for test_yesprivs@localhost
1483 GRANT USAGE ON *.* TO `test_yesprivs`@`localhost` IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
1484 GRANT TRIGGER ON `priv_db`.* TO `test_yesprivs`@`localhost`
1485 create User test_noprivs@localhost;
1486 set password for test_noprivs@localhost = password('PWD');
1487 revoke ALL PRIVILEGES, GRANT OPTION FROM test_noprivs@localhost;
1488 grant SELECT,UPDATE on priv_db.* to test_noprivs@localhost;
1489 show grants for test_noprivs@localhost;
1490 Grants for test_noprivs@localhost
1491 GRANT USAGE ON *.* TO `test_noprivs`@`localhost` IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
1492 GRANT SELECT, UPDATE ON `priv_db`.* TO `test_noprivs`@`localhost`
1493 connect yes_privs,localhost,test_yesprivs,PWD,"*NO-ONE*",$MASTER_MYPORT,$MASTER_MYSOCK;
1494 connection yes_privs;
1495 connect no_privs,localhost,test_noprivs,PWD,"*NO-ONE*",$MASTER_MYPORT,$MASTER_MYSOCK;
1496 connection no_privs;
1497
1498 update only on column:
1499 ----------------------
1500 connection default;
1501 select current_user;
1502 current_user
1503 root@localhost
1504 grant SELECT(f1),INSERT,UPDATE(f1) on priv_db.t1
1505 to test_yesprivs@localhost;
1506 grant SELECT(f1),INSERT,UPDATE(f1) on priv_db.t2
1507 to test_yesprivs@localhost;
1508 connection yes_privs;
1509 select current_user;
1510 current_user
1511 test_yesprivs@localhost
1512 use priv_db;
1513 insert into t1 (f1) values ('insert1-yes');
1514 insert into t2 (f1) values ('insert1-yes');
1515 create trigger trg1_1 before UPDATE on t1 for each row
1516 set new.f1 = 'trig 1_1-yes';
1517 create trigger trg2_1 before UPDATE on t2 for each row
1518 set new.f1 = 'trig 2_1-yes';
1519 connection no_privs;
1520 select current_user;
1521 current_user
1522 test_noprivs@localhost
1523 use priv_db;
1524 select f1 from t1 order by f1;
1525 f1
1526 insert1-yes
1527 update t1 set f1 = 'update1_no'
1528 where f1 like '%insert%';
1529 select f1 from t1 order by f1;
1530 f1
1531 trig 1_1-yes
1532 select f1 from t2 order by f1;
1533 f1
1534 insert1-yes
1535 update t2 set f1 = 'update1_no'
1536 where f1 like '%insert%';
1537 select f1 from t2 order by f1;
1538 f1
1539 trig 2_1-yes
1540 connection default;
1541 select current_user;
1542 current_user
1543 root@localhost
1544 revoke UPDATE on priv_db.*
1545 from test_yesprivs@localhost;
1546 revoke UPDATE(f1) on priv_db.t2
1547 from test_yesprivs@localhost;
1548 show grants for test_yesprivs@localhost;
1549 Grants for test_yesprivs@localhost
1550 GRANT USAGE ON *.* TO `test_yesprivs`@`localhost` IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
1551 GRANT TRIGGER ON `priv_db`.* TO `test_yesprivs`@`localhost`
1552 GRANT SELECT (`f1`), INSERT ON `priv_db`.`t2` TO `test_yesprivs`@`localhost`
1553 GRANT SELECT (`f1`), INSERT, UPDATE (`f1`) ON `priv_db`.`t1` TO `test_yesprivs`@`localhost`
1554 connection yes_privs;
1555 select current_user;
1556 current_user
1557 test_yesprivs@localhost
1558 use priv_db;
1559 insert into t1 (f1) values ('insert2-yes');
1560 insert into t2 (f1) values ('insert2-yes');
1561 connection no_privs;
1562 select current_user;
1563 current_user
1564 test_noprivs@localhost
1565 use priv_db;
1566 update t1 set f1 = 'update2_no'
1567 where f1 like '%insert%';
1568 update t2 set f1 = 'update2_no'
1569 where f1 like '%insert%';
1570 ERROR 42000: UPDATE command denied to user 'test_yesprivs'@'localhost' for column 'f1' in table 't2'
1571 update t1 set f1 = 'update3_no'
1572 where f1 like '%insert%';
1573 update t2 set f1 = 'update3_no'
1574 where f1 like '%insert%';
1575 ERROR 42000: UPDATE command denied to user 'test_yesprivs'@'localhost' for column 'f1' in table 't2'
1576 select f1 from t1 order by f1;
1577 f1
1578 trig 1_1-yes
1579 trig 1_1-yes
1580 select f1 from t2 order by f1;
1581 f1
1582 insert2-yes
1583 trig 2_1-yes
1584
1585 check if access only on one of three columns
1586 --------------------------------------------
1587 connection default;
1588 select current_user;
1589 current_user
1590 root@localhost
1591 alter table priv_db.t1 add f2 char(20), add f3 int;
1592 revoke TRIGGER on priv_db.* from test_yesprivs@localhost;
1593 grant TRIGGER,SELECT on priv_db.t1 to test_yesprivs@localhost;
1594 grant UPDATE on priv_db.t2 to test_yesprivs@localhost;
1595 connection yes_privs;
1596 select current_user;
1597 current_user
1598 test_yesprivs@localhost
1599 use priv_db;
1600 insert into t1 values ('insert2-yes','insert2-yes',1);
1601 insert into t1 values ('insert3-yes','insert3-yes',2);
1602 select * from t1 order by f1;
1603 f1 f2 f3
1604 insert2-yes insert2-yes 1
1605 insert3-yes insert3-yes 2
1606 trig 1_1-yes NULL NULL
1607 trig 1_1-yes NULL NULL
1608 connection no_privs;
1609 select current_user;
1610 current_user
1611 test_noprivs@localhost
1612 use priv_db;
1613 update t1 set f1 = 'update4-no',
1614 f2 = 'update4-yes',
1615 f3 = f3*10
1616 where f2 like '%yes';
1617 select * from t1 order by f1,f2,f3;
1618 f1 f2 f3
1619 trig 1_1-yes NULL NULL
1620 trig 1_1-yes NULL NULL
1621 trig 1_1-yes update4-yes 10
1622 trig 1_1-yes update4-yes 20
1623 connection yes_privs;
1624 select current_user;
1625 current_user
1626 test_yesprivs@localhost
1627 create trigger trg1_2 after UPDATE on t1 for each row
1628 set @f2 = 'trig 1_2-yes';
1629 connection no_privs;
1630 select current_user;
1631 current_user
1632 test_noprivs@localhost
1633 update t1 set f1 = 'update5-yes',
1634 f2 = 'update5-yes'
1635 where f2 like '%yes';
1636 select * from t1 order by f1,f2,f3;
1637 f1 f2 f3
1638 trig 1_1-yes NULL NULL
1639 trig 1_1-yes NULL NULL
1640 trig 1_1-yes update5-yes 10
1641 trig 1_1-yes update5-yes 20
1642 select @f2;
1643 @f2
1644 trig 1_2-yes
1645 update t1 set f1 = 'update6_no'
1646 where f1 like '%insert%';
1647 update t2 set f1 = 'update6_no'
1648 where f1 like '%insert%';
1649 ERROR 42000: TRIGGER command denied to user 'test_yesprivs'@'localhost' for table `priv_db`.`t2`
1650 update t1 set f1 = 'update7_no'
1651 where f1 like '%insert%';
1652 update t2 set f1 = 'update7_no'
1653 where f1 like '%insert%';
1654 ERROR 42000: TRIGGER command denied to user 'test_yesprivs'@'localhost' for table `priv_db`.`t2`
1655 select f1 from t1 order by f1;
1656 f1
1657 trig 1_1-yes
1658 trig 1_1-yes
1659 trig 1_1-yes
1660 trig 1_1-yes
1661 select f1 from t2 order by f1;
1662 f1
1663 insert2-yes
1664 trig 2_1-yes
1665
1666 check if rejected without trigger privilege:
1667 --------------------------------------------
1668 connection default;
1669 select current_user;
1670 current_user
1671 root@localhost
1672 revoke TRIGGER on priv_db.t1 from test_yesprivs@localhost;
1673 connection no_privs;
1674 select current_user;
1675 current_user
1676 test_noprivs@localhost
1677 update t1 set f1 = 'update8-no',
1678 f2 = 'update8-no'
1679 where f2 like '%yes';
1680 ERROR 42000: TRIGGER command denied to user 'test_yesprivs'@'localhost' for table `priv_db`.`t1`
1681 select * from t1 order by f1,f2,f3;
1682 f1 f2 f3
1683 trig 1_1-yes NULL NULL
1684 trig 1_1-yes NULL NULL
1685 trig 1_1-yes update5-yes 10
1686 trig 1_1-yes update5-yes 20
1687 select @f2;
1688 @f2
1689 trig 1_2-yes
1690
1691 check trigger, but not update privilege on column:
1692 --------------------------------------------------
1693 connection default;
1694 select current_user;
1695 current_user
1696 root@localhost
1697 revoke UPDATE(f1) on priv_db.t1 from test_yesprivs@localhost;
1698 grant TRIGGER,UPDATE(f2),UPDATE(f3) on priv_db.t1
1699 to test_yesprivs@localhost;
1700 show grants for test_yesprivs@localhost;
1701 Grants for test_yesprivs@localhost
1702 GRANT USAGE ON *.* TO `test_yesprivs`@`localhost` IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
1703 GRANT SELECT (`f1`), INSERT, UPDATE ON `priv_db`.`t2` TO `test_yesprivs`@`localhost`
1704 GRANT SELECT, SELECT (`f1`), INSERT, UPDATE (`f3`, `f2`), TRIGGER ON `priv_db`.`t1` TO `test_yesprivs`@`localhost`
1705 connection yes_privs;
1706 select current_user;
1707 current_user
1708 test_yesprivs@localhost
1709 use priv_db;
1710 drop trigger trg1_1;
1711 create trigger trg1_3 before UPDATE on t1 for each row
1712 set new.f1 = 'trig 1_3-yes';
1713 connection no_privs;
1714 select current_user;
1715 current_user
1716 test_noprivs@localhost
1717 use priv_db;
1718 update t1 set f1 = 'update9-no',
1719 f2 = 'update9-no'
1720 where f2 like '%yes';
1721 ERROR 42000: UPDATE command denied to user 'test_yesprivs'@'localhost' for column 'f1' in table 't1'
1722 select * from t1 order by f1,f2,f3;
1723 f1 f2 f3
1724 trig 1_1-yes NULL NULL
1725 trig 1_1-yes NULL NULL
1726 trig 1_1-yes update5-yes 10
1727 trig 1_1-yes update5-yes 20
1728 update t1 set f3= f3+1;
1729 ERROR 42000: UPDATE command denied to user 'test_yesprivs'@'localhost' for column 'f1' in table 't1'
1730 select f3 from t1 order by f3;
1731 f3
1732 NULL
1733 NULL
1734 10
1735 20
1736 connection default;
1737 select current_user;
1738 current_user
1739 root@localhost
1740 revoke TRIGGER on priv_db.t1 from test_yesprivs@localhost;
1741 grant UPDATE(f1),UPDATE(f2),UPDATE(f3) on priv_db.t1
1742 to test_yesprivs@localhost;
1743 show grants for test_yesprivs@localhost;
1744 Grants for test_yesprivs@localhost
1745 GRANT USAGE ON *.* TO `test_yesprivs`@`localhost` IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
1746 GRANT SELECT (`f1`), INSERT, UPDATE ON `priv_db`.`t2` TO `test_yesprivs`@`localhost`
1747 GRANT SELECT, SELECT (`f1`), INSERT, UPDATE (`f3`, `f2`, `f1`) ON `priv_db`.`t1` TO `test_yesprivs`@`localhost`
1748 connection no_privs;
1749 select current_user;
1750 current_user
1751 test_noprivs@localhost
1752 use priv_db;
1753 update t1 set f3= f3+1;
1754 ERROR 42000: TRIGGER command denied to user 'test_yesprivs'@'localhost' for table `priv_db`.`t1`
1755 select f3 from t1 order by f3;
1756 f3
1757 NULL
1758 NULL
1759 10
1760 20
1761
1762 ##### trigger privilege on column level? #######
1763 ------------------------------------------------
1764 grant TRIGGER(f1) on priv_db.t1 to test_yesprivs@localhost;
1765 ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '(f1) on priv_db.t1 to test_yesprivs@localhost' at line 1
1766 disconnect yes_privs;
1767 disconnect no_privs;
1768 connection default;
1769 select current_user;
1770 current_user
1771 root@localhost
1772 drop database if exists priv_db;
1773 drop user test_yesprivs@localhost;
1774 drop user test_noprivs@localhost;