1 SET @@session.sql_mode = 'NO_ENGINE_SUBSTITUTION';
3 drop table if exists tb3;
5 f118 char not null DEFAULT 'a',
6 f119 char binary not null DEFAULT b'101',
7 f120 char ascii not null DEFAULT b'101',
10 f129 binary not null DEFAULT b'101',
11 f130 tinyint not null DEFAULT 99,
12 f131 tinyint unsigned not null DEFAULT 99,
13 f132 tinyint zerofill not null DEFAULT 99,
14 f133 tinyint unsigned zerofill not null DEFAULT 99,
15 f134 smallint not null DEFAULT 999,
16 f135 smallint unsigned not null DEFAULT 999,
17 f136 smallint zerofill not null DEFAULT 999,
18 f137 smallint unsigned zerofill not null DEFAULT 999,
19 f138 mediumint not null DEFAULT 9999,
20 f139 mediumint unsigned not null DEFAULT 9999,
21 f140 mediumint zerofill not null DEFAULT 9999,
22 f141 mediumint unsigned zerofill not null DEFAULT 9999,
23 f142 int not null DEFAULT 99999,
24 f143 int unsigned not null DEFAULT 99999,
25 f144 int zerofill not null DEFAULT 99999,
26 f145 int unsigned zerofill not null DEFAULT 99999,
27 f146 bigint not null DEFAULT 999999,
28 f147 bigint unsigned not null DEFAULT 999999,
29 f148 bigint zerofill not null DEFAULT 999999,
30 f149 bigint unsigned zerofill not null DEFAULT 999999,
31 f150 decimal not null DEFAULT 999.999,
32 f151 decimal unsigned not null DEFAULT 999.17,
33 f152 decimal zerofill not null DEFAULT 999.999,
34 f153 decimal unsigned zerofill,
37 f156 decimal (0) unsigned,
38 f157 decimal (64) unsigned,
39 f158 decimal (0) zerofill,
40 f159 decimal (64) zerofill,
41 f160 decimal (0) unsigned zerofill,
42 f161 decimal (64) unsigned zerofill,
45 f164 decimal (0,0) unsigned,
46 f165 decimal (63,30) unsigned,
47 f166 decimal (0,0) zerofill,
48 f167 decimal (63,30) zerofill,
49 f168 decimal (0,0) unsigned zerofill,
50 f169 decimal (63,30) unsigned zerofill,
52 f171 numeric unsigned,
53 f172 numeric zerofill,
54 f173 numeric unsigned zerofill,
57 ) engine = <engine_to_be_used>;
59 Note 1265 Data truncated for column 'f150' at row 1
60 Note 1265 Data truncated for column 'f151' at row 1
61 Note 1265 Data truncated for column 'f152' at row 1
65 drop database if exists priv_db;
66 create database priv_db;
68 create table t1 (f1 char(20)) engine= <engine_to_be_used>;
69 create User test_noprivs@localhost;
70 set password for test_noprivs@localhost = password('PWD');
71 create User test_yesprivs@localhost;
72 set password for test_yesprivs@localhost = password('PWD');
76 revoke ALL PRIVILEGES, GRANT OPTION FROM test_noprivs@localhost;
77 grant ALL on *.* to test_noprivs@localhost;
78 revoke TRIGGER on *.* from test_noprivs@localhost;
79 show grants for test_noprivs@localhost;
80 Grants for test_noprivs@localhost
81 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 ON *.* TO 'test_noprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
82 revoke ALL PRIVILEGES, GRANT OPTION FROM test_yesprivs@localhost;
83 grant TRIGGER on *.* to test_yesprivs@localhost;
84 grant SELECT on priv_db.t1 to test_yesprivs@localhost;
85 show grants for test_yesprivs@localhost;
86 Grants for test_yesprivs@localhost
87 GRANT TRIGGER ON *.* TO 'test_yesprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
88 GRANT SELECT ON `priv_db`.`t1` TO 'test_yesprivs'@'localhost'
94 test_noprivs@localhost
96 create trigger trg1_1 before INSERT on t1 for each row
97 set new.f1 = 'trig 3.5.3.2_1-no';
98 ERROR 42000: TRIGGER command denied to user 'test_noprivs'@'localhost' for table 't1'
100 insert into t1 (f1) values ('insert 3.5.3.2-no');
101 select f1 from t1 order by f1;
106 test_yesprivs@localhost
108 create trigger trg1_2 before INSERT on t1 for each row
109 set new.f1 = 'trig 3.5.3.2_2-yes';
114 insert into t1 (f1) values ('insert 3.5.3.2-yes');
115 ERROR 42000: UPDATE command denied to user 'test_yesprivs'@'localhost' for column 'f1' in table 't1'
116 select f1 from t1 order by f1;
119 grant UPDATE on priv_db.t1 to test_yesprivs@localhost;
120 insert into t1 (f1) values ('insert 3.5.3.2-yes');
121 select f1 from t1 order by f1;
130 ERROR 42000: TRIGGER command denied to user 'test_noprivs'@'localhost' for table 't1'
132 insert into t1 (f1) values ('insert 3.5.3.6-yes');
133 select f1 from t1 order by f1;
141 insert into t1 (f1) values ('insert 3.5.3.6-no');
142 select f1 from t1 order by f1;
152 revoke ALL PRIVILEGES, GRANT OPTION FROM test_noprivs@localhost;
153 grant ALL on *.* to test_noprivs@localhost;
154 revoke UPDATE on *.* from test_noprivs@localhost;
155 show grants for test_noprivs@localhost;
156 Grants for test_noprivs@localhost
157 GRANT SELECT, INSERT, 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 ON *.* TO 'test_noprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
158 revoke ALL PRIVILEGES, GRANT OPTION FROM test_yesprivs@localhost;
159 grant TRIGGER, UPDATE on *.* to test_yesprivs@localhost;
160 show grants for test_yesprivs@localhost;
161 Grants for test_yesprivs@localhost
162 GRANT UPDATE, TRIGGER ON *.* TO 'test_yesprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
165 test_noprivs@localhost
168 Grants for test_noprivs@localhost
169 GRANT SELECT, INSERT, 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 ON *.* TO 'test_noprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
170 select f1 from t1 order by f1;
176 create trigger trg4a_1 before INSERT on t1 for each row
177 set new.f1 = 'trig 3.5.3.7-1a';
178 insert into t1 (f1) values ('insert 3.5.3.7-1a');
179 ERROR 42000: UPDATE command denied to user 'test_noprivs'@'localhost' for column 'f1' in table 't1'
180 select f1 from t1 order by f1;
186 drop trigger trg4a_1;
190 test_yesprivs@localhost
192 Grants for test_yesprivs@localhost
193 GRANT UPDATE, TRIGGER ON *.* TO 'test_yesprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
194 create trigger trg4a_2 before INSERT on t1 for each row
195 set new.f1 = 'trig 3.5.3.7-2a';
196 insert into t1 (f1) values ('insert 3.5.3.7-2b');
197 select f1 from t1 order by f1;
204 drop trigger trg4a_2;
208 revoke ALL PRIVILEGES, GRANT OPTION FROM test_noprivs@localhost;
209 grant TRIGGER on *.* to test_noprivs;
210 grant ALL on priv_db.* to test_noprivs@localhost;
211 revoke UPDATE on priv_db.* from test_noprivs@localhost;
212 show grants for test_noprivs;
213 Grants for test_noprivs@%
214 GRANT TRIGGER ON *.* TO 'test_noprivs'@'%'
215 revoke ALL PRIVILEGES, GRANT OPTION FROM test_yesprivs@localhost;
216 grant TRIGGER on *.* to test_yesprivs@localhost;
217 grant UPDATE on priv_db.* to test_yesprivs@localhost;
218 show grants for test_yesprivs@localhost;
219 Grants for test_yesprivs@localhost
220 GRANT TRIGGER ON *.* TO 'test_yesprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
221 GRANT UPDATE ON `priv_db`.* TO 'test_yesprivs'@'localhost'
223 Grants for test_noprivs@localhost
224 GRANT USAGE ON *.* TO 'test_noprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
225 GRANT SELECT, INSERT, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, EVENT, TRIGGER ON `priv_db`.* TO 'test_noprivs'@'localhost'
227 create trigger trg4b_1 before UPDATE on t1 for each row
228 set new.f1 = 'trig 3.5.3.7-1b';
229 insert into t1 (f1) values ('insert 3.5.3.7-1b');
230 select f1 from t1 order by f1;
238 update t1 set f1 = 'update 3.5.3.7-1b' where f1 = 'insert 3.5.3.7-1b';
239 ERROR 42000: UPDATE command denied to user 'test_noprivs'@'localhost' for column 'f1' in table 't1'
240 select f1 from t1 order by f1;
248 drop trigger trg4b_1;
250 Grants for test_yesprivs@localhost
251 GRANT TRIGGER ON *.* TO 'test_yesprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
252 GRANT UPDATE ON `priv_db`.* TO 'test_yesprivs'@'localhost'
254 create trigger trg4b_2 before UPDATE on t1 for each row
255 set new.f1 = 'trig 3.5.3.7-2b';
256 insert into t1 (f1) values ('insert 3.5.3.7-2b');
257 select f1 from t1 order by f1;
266 update t1 set f1 = 'update 3.5.3.7-2b' where f1 = 'insert 3.5.3.7-2b';
267 select f1 from t1 order by f1;
276 drop trigger trg4b_2;
280 revoke ALL PRIVILEGES, GRANT OPTION FROM test_noprivs@localhost;
281 grant TRIGGER on *.* to test_noprivs@localhost;
282 grant ALL on priv_db.t1 to test_noprivs@localhost;
283 revoke UPDATE on priv_db.t1 from test_noprivs@localhost;
284 show grants for test_noprivs;
285 Grants for test_noprivs@%
286 GRANT TRIGGER ON *.* TO 'test_noprivs'@'%'
287 revoke ALL PRIVILEGES, GRANT OPTION FROM test_yesprivs@localhost;
288 grant TRIGGER on *.* to test_yesprivs@localhost;
289 grant UPDATE on priv_db.t1 to test_yesprivs@localhost;
290 show grants for test_yesprivs@localhost;
291 Grants for test_yesprivs@localhost
292 GRANT TRIGGER ON *.* TO 'test_yesprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
293 GRANT UPDATE ON `priv_db`.`t1` TO 'test_yesprivs'@'localhost'
295 Grants for test_noprivs@localhost
296 GRANT TRIGGER ON *.* TO 'test_noprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
297 GRANT SELECT, INSERT, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER, CREATE VIEW, SHOW VIEW, TRIGGER ON `priv_db`.`t1` TO 'test_noprivs'@'localhost'
299 create trigger trg4c_1 before INSERT on t1 for each row
300 set new.f1 = 'trig 3.5.3.7-1c';
301 insert into t1 (f1) values ('insert 3.5.3.7-1c');
302 ERROR 42000: UPDATE command denied to user 'test_noprivs'@'localhost' for column 'f1' in table 't1'
303 select f1 from t1 order by f1;
312 drop trigger trg4c_1;
314 Grants for test_yesprivs@localhost
315 GRANT TRIGGER ON *.* TO 'test_yesprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
316 GRANT UPDATE ON `priv_db`.`t1` TO 'test_yesprivs'@'localhost'
318 create trigger trg4c_2 before INSERT on t1 for each row
319 set new.f1 = 'trig 3.5.3.7-2c';
320 insert into t1 (f1) values ('insert 3.5.3.7-2c');
321 select f1 from t1 order by f1;
331 drop trigger trg4c_2;
335 revoke ALL PRIVILEGES, GRANT OPTION FROM test_noprivs@localhost;
336 grant TRIGGER on *.* to test_noprivs@localhost;
337 grant SELECT (f1), INSERT (f1) on priv_db.t1 to test_noprivs@localhost;
338 show grants for test_noprivs;
339 Grants for test_noprivs@%
340 GRANT TRIGGER ON *.* TO 'test_noprivs'@'%'
341 revoke ALL PRIVILEGES, GRANT OPTION FROM test_yesprivs@localhost;
342 grant TRIGGER on *.* to test_yesprivs@localhost;
343 grant UPDATE (f1) on priv_db.t1 to test_yesprivs@localhost;
344 show grants for test_noprivs;
345 Grants for test_noprivs@%
346 GRANT TRIGGER ON *.* TO 'test_noprivs'@'%'
348 Grants for test_noprivs@localhost
349 GRANT TRIGGER ON *.* TO 'test_noprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
350 GRANT SELECT (f1), INSERT (f1) ON `priv_db`.`t1` TO 'test_noprivs'@'localhost'
352 create trigger trg4d_1 before INSERT on t1 for each row
353 set new.f1 = 'trig 3.5.3.7-1d';
354 insert into t1 (f1) values ('insert 3.5.3.7-1d');
355 ERROR 42000: UPDATE command denied to user 'test_noprivs'@'localhost' for column 'f1' in table 't1'
356 select f1 from t1 order by f1;
366 drop trigger trg4d_1;
368 Grants for test_yesprivs@localhost
369 GRANT TRIGGER ON *.* TO 'test_yesprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
370 GRANT UPDATE (f1) ON `priv_db`.`t1` TO 'test_yesprivs'@'localhost'
372 create trigger trg4d_2 before INSERT on t1 for each row
373 set new.f1 = 'trig 3.5.3.7-2d';
374 insert into t1 (f1) values ('insert 3.5.3.7-2d');
375 select f1 from t1 order by f1;
386 drop trigger trg4d_2;
390 revoke ALL PRIVILEGES, GRANT OPTION FROM test_noprivs@localhost;
391 grant ALL on *.* to test_noprivs@localhost;
392 revoke SELECT on *.* from test_noprivs@localhost;
393 show grants for test_noprivs@localhost;
394 Grants for test_noprivs@localhost
395 GRANT 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 ON *.* TO 'test_noprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
396 revoke ALL PRIVILEGES, GRANT OPTION FROM test_yesprivs@localhost;
397 grant TRIGGER, SELECT on *.* to test_yesprivs@localhost;
398 show grants for test_yesprivs@localhost;
399 Grants for test_yesprivs@localhost
400 GRANT SELECT, TRIGGER ON *.* TO 'test_yesprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
403 test_noprivs@localhost
406 Grants for test_noprivs@localhost
407 GRANT 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 ON *.* TO 'test_noprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
408 create trigger trg5a_1 before INSERT on t1 for each row
409 set @test_var = new.f1;
410 set @test_var = 'before trig 3.5.3.8-1a';
413 before trig 3.5.3.8-1a
414 insert into t1 (f1) values ('insert 3.5.3.8-1a');
415 ERROR 42000: SELECT command denied to user 'test_noprivs'@'localhost' for column 'f1' in table 't1'
418 before trig 3.5.3.8-1a
419 drop trigger trg5a_1;
423 test_yesprivs@localhost
425 Grants for test_yesprivs@localhost
426 GRANT SELECT, TRIGGER ON *.* TO 'test_yesprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
427 create trigger trg5a_2 before INSERT on t1 for each row
428 set @test_var= new.f1;
429 set @test_var= 'before trig 3.5.3.8-2a';
432 before trig 3.5.3.8-2a
433 insert into t1 (f1) values ('insert 3.5.3.8-2a');
437 drop trigger trg5a_2;
441 revoke ALL PRIVILEGES, GRANT OPTION FROM test_noprivs@localhost;
442 grant TRIGGER on *.* to test_noprivs@localhost;
443 grant ALL on priv_db.* to test_noprivs@localhost;
444 revoke SELECT on priv_db.* from test_noprivs@localhost;
445 show grants for test_noprivs@localhost;
446 Grants for test_noprivs@localhost
447 GRANT TRIGGER ON *.* TO 'test_noprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
448 GRANT INSERT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, EVENT, TRIGGER ON `priv_db`.* TO 'test_noprivs'@'localhost'
449 revoke ALL PRIVILEGES, GRANT OPTION FROM test_yesprivs@localhost;
450 grant TRIGGER on *.* to test_yesprivs@localhost;
451 grant SELECT on priv_db.* to test_yesprivs@localhost;
452 show grants for test_yesprivs@localhost;
453 Grants for test_yesprivs@localhost
454 GRANT TRIGGER ON *.* TO 'test_yesprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
455 GRANT SELECT ON `priv_db`.* TO 'test_yesprivs'@'localhost'
457 Grants for test_noprivs@localhost
458 GRANT TRIGGER ON *.* TO 'test_noprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
459 GRANT INSERT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, EVENT, TRIGGER ON `priv_db`.* TO 'test_noprivs'@'localhost'
461 create trigger trg5b_1 before UPDATE on t1 for each row
462 set @test_var= new.f1;
463 set @test_var= 'before trig 3.5.3.8-1b';
464 insert into t1 (f1) values ('insert 3.5.3.8-1b');
467 before trig 3.5.3.8-1b
468 update t1 set f1= 'update 3.5.3.8-1b' where f1 = 'insert 3.5.3.8-1b';
469 ERROR 42000: SELECT command denied to user 'test_noprivs'@'localhost' for column 'f1' in table 't1'
472 before trig 3.5.3.8-1b
473 drop trigger trg5b_1;
475 Grants for test_yesprivs@localhost
476 GRANT TRIGGER ON *.* TO 'test_yesprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
477 GRANT SELECT ON `priv_db`.* TO 'test_yesprivs'@'localhost'
479 create trigger trg5b_2 before UPDATE on t1 for each row
480 set @test_var= new.f1;
481 set @test_var= 'before trig 3.5.3.8-2b';
482 insert into t1 (f1) values ('insert 3.5.3.8-2b');
485 before trig 3.5.3.8-2b
486 update t1 set f1= 'update 3.5.3.8-2b' where f1 = 'insert 3.5.3.8-2b';
490 drop trigger trg5b_2;
494 revoke ALL PRIVILEGES, GRANT OPTION FROM test_noprivs@localhost;
495 grant TRIGGER on *.* to test_noprivs@localhost;
496 grant ALL on priv_db.t1 to test_noprivs@localhost;
497 revoke SELECT on priv_db.t1 from test_noprivs@localhost;
498 show grants for test_noprivs@localhost;
499 Grants for test_noprivs@localhost
500 GRANT TRIGGER ON *.* TO 'test_noprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
501 GRANT INSERT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER, CREATE VIEW, SHOW VIEW, TRIGGER ON `priv_db`.`t1` TO 'test_noprivs'@'localhost'
502 revoke ALL PRIVILEGES, GRANT OPTION FROM test_yesprivs@localhost;
503 grant TRIGGER on *.* to test_yesprivs@localhost;
504 grant SELECT on priv_db.t1 to test_yesprivs@localhost;
505 show grants for test_yesprivs@localhost;
506 Grants for test_yesprivs@localhost
507 GRANT TRIGGER ON *.* TO 'test_yesprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
508 GRANT SELECT ON `priv_db`.`t1` TO 'test_yesprivs'@'localhost'
510 Grants for test_noprivs@localhost
511 GRANT TRIGGER ON *.* TO 'test_noprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
512 GRANT INSERT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER, CREATE VIEW, SHOW VIEW, TRIGGER ON `priv_db`.`t1` TO 'test_noprivs'@'localhost'
514 create trigger trg5c_1 before INSERT on t1 for each row
515 set @test_var= new.f1;
516 set @test_var= 'before trig 3.5.3.8-1c';
517 insert into t1 (f1) values ('insert 3.5.3.8-1c');
518 ERROR 42000: SELECT command denied to user 'test_noprivs'@'localhost' for column 'f1' in table 't1'
521 before trig 3.5.3.8-1c
522 drop trigger trg5c_1;
524 Grants for test_yesprivs@localhost
525 GRANT TRIGGER ON *.* TO 'test_yesprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
526 GRANT SELECT ON `priv_db`.`t1` TO 'test_yesprivs'@'localhost'
528 create trigger trg5c_2 before INSERT on t1 for each row
529 set @test_var= new.f1;
530 set @test_var='before trig 3.5.3.8-2c';
531 insert into t1 (f1) values ('insert 3.5.3.8-2c');
535 drop trigger trg5c_2;
539 revoke ALL PRIVILEGES, GRANT OPTION FROM test_noprivs@localhost;
540 grant TRIGGER on *.* to test_noprivs@localhost;
541 grant UPDATE (f1), INSERT (f1) on priv_db.t1 to test_noprivs@localhost;
542 show grants for test_noprivs@localhost;
543 Grants for test_noprivs@localhost
544 GRANT TRIGGER ON *.* TO 'test_noprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
545 GRANT INSERT (f1), UPDATE (f1) ON `priv_db`.`t1` TO 'test_noprivs'@'localhost'
546 revoke ALL PRIVILEGES, GRANT OPTION FROM test_yesprivs@localhost;
547 grant TRIGGER on *.* to test_yesprivs@localhost;
548 grant SELECT (f1) on priv_db.t1 to test_yesprivs@localhost;
549 show grants for test_noprivs@localhost;
550 Grants for test_noprivs@localhost
551 GRANT TRIGGER ON *.* TO 'test_noprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
552 GRANT INSERT (f1), UPDATE (f1) ON `priv_db`.`t1` TO 'test_noprivs'@'localhost'
554 Grants for test_noprivs@localhost
555 GRANT TRIGGER ON *.* TO 'test_noprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
556 GRANT INSERT (f1), UPDATE (f1) ON `priv_db`.`t1` TO 'test_noprivs'@'localhost'
558 create trigger trg5d_1 before INSERT on t1 for each row
559 set @test_var= new.f1;
560 set @test_var='before trig 3.5.3.8-1d';
561 insert into t1 (f1) values ('insert 3.5.3.8-1d');
562 ERROR 42000: SELECT command denied to user 'test_noprivs'@'localhost' for column 'f1' in table 't1'
565 before trig 3.5.3.8-1d
566 drop trigger trg5d_1;
568 Grants for test_yesprivs@localhost
569 GRANT TRIGGER ON *.* TO 'test_yesprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
570 GRANT SELECT (f1) ON `priv_db`.`t1` TO 'test_yesprivs'@'localhost'
572 create trigger trg5d_2 before INSERT on t1 for each row
573 set @test_var= new.f1;
574 set @test_var='before trig 3.5.3.8-2d';
575 insert into t1 (f1) values ('insert 3.5.3.8-2d');
579 drop trigger trg5d_2;
584 drop table if exists t1;
585 drop table if exists t2;
586 create table t1 (f1 int) engine= <engine_to_be_used>;
587 create table t2 (f2 int) engine= <engine_to_be_used>;
588 revoke ALL PRIVILEGES, GRANT OPTION FROM test_yesprivs@localhost;
589 grant TRIGGER on *.* to test_yesprivs@localhost;
590 grant SELECT, UPDATE on priv_db.t1 to test_yesprivs@localhost;
591 grant SELECT on priv_db.t2 to test_yesprivs@localhost;
592 show grants for test_yesprivs@localhost;
593 Grants for test_yesprivs@localhost
594 GRANT TRIGGER ON *.* TO 'test_yesprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
595 GRANT SELECT ON `priv_db`.`t2` TO 'test_yesprivs'@'localhost'
596 GRANT SELECT, UPDATE ON `priv_db`.`t1` TO 'test_yesprivs'@'localhost'
599 test_yesprivs@localhost
601 create trigger trg1 before insert on t1 for each row
602 insert into t2 values (new.f1);
604 insert into t1 (f1) values (4);
605 ERROR 42000: INSERT command denied to user 'test_yesprivs'@'localhost' for table 't2'
606 revoke SELECT on priv_db.t2 from test_yesprivs@localhost;
607 grant INSERT on priv_db.t2 to test_yesprivs@localhost;
608 insert into t1 (f1) values (4);
609 select f1 from t1 order by f1;
612 select f2 from t2 order by f2;
617 create trigger trg2 before insert on t1 for each row
618 update t2 set f2=new.f1-1;
620 insert into t1 (f1) values (2);
621 ERROR 42000: UPDATE command denied to user 'test_yesprivs'@'localhost' for table 't2'
622 revoke INSERT on priv_db.t2 from test_yesprivs@localhost;
623 grant UPDATE on priv_db.t2 to test_yesprivs@localhost;
624 insert into t1 (f1) values (2);
625 select f1 from t1 order by f1;
629 select f2 from t2 order by f2;
634 create trigger trg3 before insert on t1 for each row
635 select f2 into @aaa from t2 where f2=new.f1;
637 insert into t1 (f1) values (1);
638 ERROR 42000: SELECT command denied to user 'test_yesprivs'@'localhost' for table 't2'
639 revoke UPDATE on priv_db.t2 from test_yesprivs@localhost;
640 grant SELECT on priv_db.t2 to test_yesprivs@localhost;
641 insert into t1 (f1) values (1);
642 select f1 from t1 order by f1;
647 select f2 from t2 order by f2;
655 create trigger trg4 before insert on t1 for each row
658 insert into t1 (f1) values (1);
659 ERROR 42000: DELETE command denied to user 'test_yesprivs'@'localhost' for table 't2'
660 revoke SELECT on priv_db.t2 from test_yesprivs@localhost;
661 grant DELETE on priv_db.t2 to test_yesprivs@localhost;
662 insert into t1 (f1) values (1);
663 select f1 from t1 order by f1;
669 select f2 from t2 order by f2;
671 drop database if exists priv_db;
672 drop user test_yesprivs@localhost;
673 drop user test_noprivs@localhost;
674 drop user test_noprivs;