Debian Codegraph @

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

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