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