Debian Codegraph @

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

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