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'
89
connect(localhost,test_noprivs,PWD,test,MASTER_MYPORT,MASTER_MYSOCK);
90
connect(localhost,test_yesprivs,PWD,test,MASTER_MYPORT,MASTER_MYSOCK);
96
test_noprivs@localhost
98
create trigger trg1_1 before INSERT on t1 for each row
99
set new.f1 = 'trig 3.5.3.2_1-no';
100
ERROR 42000: TRIGGER command denied to user 'test_noprivs'@'localhost' for table 't1'
102
insert into t1 (f1) values ('insert 3.5.3.2-no');
103
select f1 from t1 order by f1;
108
test_yesprivs@localhost
110
create trigger trg1_2 before INSERT on t1 for each row
111
set new.f1 = 'trig 3.5.3.2_2-yes';
116
insert into t1 (f1) values ('insert 3.5.3.2-yes');
117
ERROR 42000: UPDATE command denied to user 'test_yesprivs'@'localhost' for column 'f1' in table 't1'
118
select f1 from t1 order by f1;
121
grant UPDATE on priv_db.t1 to test_yesprivs@localhost;
122
insert into t1 (f1) values ('insert 3.5.3.2-yes');
123
select f1 from t1 order by f1;
132
ERROR 42000: TRIGGER command denied to user 'test_noprivs'@'localhost' for table 't1'
134
insert into t1 (f1) values ('insert 3.5.3.6-yes');
135
select f1 from t1 order by f1;
143
insert into t1 (f1) values ('insert 3.5.3.6-no');
144
select f1 from t1 order by f1;
154
revoke ALL PRIVILEGES, GRANT OPTION FROM test_noprivs@localhost;
155
grant ALL on *.* to test_noprivs@localhost;
156
revoke UPDATE on *.* from test_noprivs@localhost;
157
show grants for test_noprivs@localhost;
158
Grants for test_noprivs@localhost
159
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'
160
revoke ALL PRIVILEGES, GRANT OPTION FROM test_yesprivs@localhost;
161
grant TRIGGER, UPDATE on *.* to test_yesprivs@localhost;
162
show grants for test_yesprivs@localhost;
163
Grants for test_yesprivs@localhost
164
GRANT UPDATE, TRIGGER ON *.* TO 'test_yesprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
165
connect(localhost,test_noprivs,PWD,test,MASTER_MYPORT,MASTER_MYSOCK);
166
connect(localhost,test_yesprivs,PWD,test,MASTER_MYPORT,MASTER_MYSOCK);
169
test_noprivs@localhost
172
Grants for test_noprivs@localhost
173
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'
174
select f1 from t1 order by f1;
180
create trigger trg4a_1 before INSERT on t1 for each row
181
set new.f1 = 'trig 3.5.3.7-1a';
182
insert into t1 (f1) values ('insert 3.5.3.7-1a');
183
ERROR 42000: UPDATE command denied to user 'test_noprivs'@'localhost' for column 'f1' in table 't1'
184
select f1 from t1 order by f1;
190
drop trigger trg4a_1;
194
test_yesprivs@localhost
196
Grants for test_yesprivs@localhost
197
GRANT UPDATE, TRIGGER ON *.* TO 'test_yesprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
198
create trigger trg4a_2 before INSERT on t1 for each row
199
set new.f1 = 'trig 3.5.3.7-2a';
200
insert into t1 (f1) values ('insert 3.5.3.7-2b');
201
select f1 from t1 order by f1;
208
drop trigger trg4a_2;
212
revoke ALL PRIVILEGES, GRANT OPTION FROM test_noprivs@localhost;
213
grant TRIGGER on *.* to test_noprivs;
214
grant ALL on priv_db.* to test_noprivs@localhost;
215
revoke UPDATE on priv_db.* from test_noprivs@localhost;
216
show grants for test_noprivs;
217
Grants for test_noprivs@%
218
GRANT TRIGGER ON *.* TO 'test_noprivs'@'%'
219
revoke ALL PRIVILEGES, GRANT OPTION FROM test_yesprivs@localhost;
220
grant TRIGGER on *.* to test_yesprivs@localhost;
221
grant UPDATE on priv_db.* to test_yesprivs@localhost;
222
show grants for test_yesprivs@localhost;
223
Grants for test_yesprivs@localhost
224
GRANT TRIGGER ON *.* TO 'test_yesprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
225
GRANT UPDATE ON `priv_db`.* TO 'test_yesprivs'@'localhost'
226
connect(localhost,test_noprivs,PWD,test,MASTER_MYPORT,MASTER_MYSOCK);
227
connect(localhost,test_yesprivs,PWD,test,MASTER_MYPORT,MASTER_MYSOCK);
229
Grants for test_noprivs@localhost
230
GRANT USAGE ON *.* TO 'test_noprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
231
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'
233
create trigger trg4b_1 before UPDATE on t1 for each row
234
set new.f1 = 'trig 3.5.3.7-1b';
235
insert into t1 (f1) values ('insert 3.5.3.7-1b');
236
select f1 from t1 order by f1;
244
update t1 set f1 = 'update 3.5.3.7-1b' where f1 = 'insert 3.5.3.7-1b';
245
ERROR 42000: UPDATE command denied to user 'test_noprivs'@'localhost' for column 'f1' in table 't1'
246
select f1 from t1 order by f1;
254
drop trigger trg4b_1;
256
Grants for test_yesprivs@localhost
257
GRANT TRIGGER ON *.* TO 'test_yesprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
258
GRANT UPDATE ON `priv_db`.* TO 'test_yesprivs'@'localhost'
260
create trigger trg4b_2 before UPDATE on t1 for each row
261
set new.f1 = 'trig 3.5.3.7-2b';
262
insert into t1 (f1) values ('insert 3.5.3.7-2b');
263
select f1 from t1 order by f1;
272
update t1 set f1 = 'update 3.5.3.7-2b' where f1 = 'insert 3.5.3.7-2b';
273
select f1 from t1 order by f1;
282
drop trigger trg4b_2;
286
revoke ALL PRIVILEGES, GRANT OPTION FROM test_noprivs@localhost;
287
grant TRIGGER on *.* to test_noprivs@localhost;
288
grant ALL on priv_db.t1 to test_noprivs@localhost;
289
revoke UPDATE on priv_db.t1 from test_noprivs@localhost;
290
show grants for test_noprivs;
291
Grants for test_noprivs@%
292
GRANT TRIGGER ON *.* TO 'test_noprivs'@'%'
293
revoke ALL PRIVILEGES, GRANT OPTION FROM test_yesprivs@localhost;
294
grant TRIGGER on *.* to test_yesprivs@localhost;
295
grant UPDATE on priv_db.t1 to test_yesprivs@localhost;
296
show grants for test_yesprivs@localhost;
297
Grants for test_yesprivs@localhost
298
GRANT TRIGGER ON *.* TO 'test_yesprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
299
GRANT UPDATE ON `priv_db`.`t1` TO 'test_yesprivs'@'localhost'
300
connect(localhost,test_noprivs,PWD,test,MASTER_MYPORT,MASTER_MYSOCK);
301
connect(localhost,test_yesprivs,PWD,test,MASTER_MYPORT,MASTER_MYSOCK);
303
Grants for test_noprivs@localhost
304
GRANT TRIGGER ON *.* TO 'test_noprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
305
GRANT SELECT, INSERT, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER, CREATE VIEW, SHOW VIEW, TRIGGER ON `priv_db`.`t1` TO 'test_noprivs'@'localhost'
307
create trigger trg4c_1 before INSERT on t1 for each row
308
set new.f1 = 'trig 3.5.3.7-1c';
309
insert into t1 (f1) values ('insert 3.5.3.7-1c');
310
ERROR 42000: UPDATE command denied to user 'test_noprivs'@'localhost' for column 'f1' in table 't1'
311
select f1 from t1 order by f1;
320
drop trigger trg4c_1;
322
Grants for test_yesprivs@localhost
323
GRANT TRIGGER ON *.* TO 'test_yesprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
324
GRANT UPDATE ON `priv_db`.`t1` TO 'test_yesprivs'@'localhost'
326
create trigger trg4c_2 before INSERT on t1 for each row
327
set new.f1 = 'trig 3.5.3.7-2c';
328
insert into t1 (f1) values ('insert 3.5.3.7-2c');
329
select f1 from t1 order by f1;
339
drop trigger trg4c_2;
343
revoke ALL PRIVILEGES, GRANT OPTION FROM test_noprivs@localhost;
344
grant TRIGGER on *.* to test_noprivs@localhost;
345
grant SELECT (f1), INSERT (f1) on priv_db.t1 to test_noprivs@localhost;
346
show grants for test_noprivs;
347
Grants for test_noprivs@%
348
GRANT TRIGGER ON *.* TO 'test_noprivs'@'%'
349
revoke ALL PRIVILEGES, GRANT OPTION FROM test_yesprivs@localhost;
350
grant TRIGGER on *.* to test_yesprivs@localhost;
351
grant UPDATE (f1) on priv_db.t1 to test_yesprivs@localhost;
352
show grants for test_noprivs;
353
Grants for test_noprivs@%
354
GRANT TRIGGER ON *.* TO 'test_noprivs'@'%'
355
connect(localhost,test_noprivs,PWD,test,MASTER_MYPORT,MASTER_MYSOCK);
356
connect(localhost,test_yesprivs,PWD,test,MASTER_MYPORT,MASTER_MYSOCK);
358
Grants for test_noprivs@localhost
359
GRANT TRIGGER ON *.* TO 'test_noprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
360
GRANT SELECT (f1), INSERT (f1) ON `priv_db`.`t1` TO 'test_noprivs'@'localhost'
362
create trigger trg4d_1 before INSERT on t1 for each row
363
set new.f1 = 'trig 3.5.3.7-1d';
364
insert into t1 (f1) values ('insert 3.5.3.7-1d');
365
ERROR 42000: UPDATE command denied to user 'test_noprivs'@'localhost' for column 'f1' in table 't1'
366
select f1 from t1 order by f1;
376
drop trigger trg4d_1;
378
Grants for test_yesprivs@localhost
379
GRANT TRIGGER ON *.* TO 'test_yesprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
380
GRANT UPDATE (f1) ON `priv_db`.`t1` TO 'test_yesprivs'@'localhost'
382
create trigger trg4d_2 before INSERT on t1 for each row
383
set new.f1 = 'trig 3.5.3.7-2d';
384
insert into t1 (f1) values ('insert 3.5.3.7-2d');
385
select f1 from t1 order by f1;
396
drop trigger trg4d_2;
400
revoke ALL PRIVILEGES, GRANT OPTION FROM test_noprivs@localhost;
401
grant ALL on *.* to test_noprivs@localhost;
402
revoke SELECT on *.* from test_noprivs@localhost;
403
show grants for test_noprivs@localhost;
404
Grants for test_noprivs@localhost
405
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'
406
revoke ALL PRIVILEGES, GRANT OPTION FROM test_yesprivs@localhost;
407
grant TRIGGER, SELECT on *.* to test_yesprivs@localhost;
408
show grants for test_yesprivs@localhost;
409
Grants for test_yesprivs@localhost
410
GRANT SELECT, TRIGGER ON *.* TO 'test_yesprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
411
connect(localhost,test_noprivs,PWD,test,MASTER_MYPORT,MASTER_MYSOCK);
412
connect(localhost,test_yesprivs,PWD,test,MASTER_MYPORT,MASTER_MYSOCK);
415
test_noprivs@localhost
418
Grants for test_noprivs@localhost
419
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'
420
create trigger trg5a_1 before INSERT on t1 for each row
421
set @test_var = new.f1;
422
set @test_var = 'before trig 3.5.3.8-1a';
425
before trig 3.5.3.8-1a
426
insert into t1 (f1) values ('insert 3.5.3.8-1a');
427
ERROR 42000: SELECT command denied to user 'test_noprivs'@'localhost' for column 'f1' in table 't1'
430
before trig 3.5.3.8-1a
431
drop trigger trg5a_1;
435
test_yesprivs@localhost
437
Grants for test_yesprivs@localhost
438
GRANT SELECT, TRIGGER ON *.* TO 'test_yesprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
439
create trigger trg5a_2 before INSERT on t1 for each row
440
set @test_var= new.f1;
441
set @test_var= 'before trig 3.5.3.8-2a';
444
before trig 3.5.3.8-2a
445
insert into t1 (f1) values ('insert 3.5.3.8-2a');
449
drop trigger trg5a_2;
453
revoke ALL PRIVILEGES, GRANT OPTION FROM test_noprivs@localhost;
454
grant TRIGGER on *.* to test_noprivs@localhost;
455
grant ALL on priv_db.* to test_noprivs@localhost;
456
revoke SELECT on priv_db.* from test_noprivs@localhost;
457
show grants for test_noprivs@localhost;
458
Grants for test_noprivs@localhost
459
GRANT TRIGGER ON *.* TO 'test_noprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
460
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
revoke ALL PRIVILEGES, GRANT OPTION FROM test_yesprivs@localhost;
462
grant TRIGGER on *.* to test_yesprivs@localhost;
463
grant SELECT on priv_db.* to test_yesprivs@localhost;
464
show grants for test_yesprivs@localhost;
465
Grants for test_yesprivs@localhost
466
GRANT TRIGGER ON *.* TO 'test_yesprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
467
GRANT SELECT ON `priv_db`.* TO 'test_yesprivs'@'localhost'
468
connect(localhost,test_noprivs,PWD,test,MASTER_MYPORT,MASTER_MYSOCK);
469
connect(localhost,test_yesprivs,PWD,test,MASTER_MYPORT,MASTER_MYSOCK);
471
Grants for test_noprivs@localhost
472
GRANT TRIGGER ON *.* TO 'test_noprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
473
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'
475
create trigger trg5b_1 before UPDATE on t1 for each row
476
set @test_var= new.f1;
477
set @test_var= 'before trig 3.5.3.8-1b';
478
insert into t1 (f1) values ('insert 3.5.3.8-1b');
481
before trig 3.5.3.8-1b
482
update t1 set f1= 'update 3.5.3.8-1b' where f1 = 'insert 3.5.3.8-1b';
483
ERROR 42000: SELECT command denied to user 'test_noprivs'@'localhost' for column 'f1' in table 't1'
486
before trig 3.5.3.8-1b
487
drop trigger trg5b_1;
489
Grants for test_yesprivs@localhost
490
GRANT TRIGGER ON *.* TO 'test_yesprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
491
GRANT SELECT ON `priv_db`.* TO 'test_yesprivs'@'localhost'
493
create trigger trg5b_2 before UPDATE on t1 for each row
494
set @test_var= new.f1;
495
set @test_var= 'before trig 3.5.3.8-2b';
496
insert into t1 (f1) values ('insert 3.5.3.8-2b');
499
before trig 3.5.3.8-2b
500
update t1 set f1= 'update 3.5.3.8-2b' where f1 = 'insert 3.5.3.8-2b';
504
drop trigger trg5b_2;
508
revoke ALL PRIVILEGES, GRANT OPTION FROM test_noprivs@localhost;
509
grant TRIGGER on *.* to test_noprivs@localhost;
510
grant ALL on priv_db.t1 to test_noprivs@localhost;
511
revoke SELECT on priv_db.t1 from test_noprivs@localhost;
512
show grants for test_noprivs@localhost;
513
Grants for test_noprivs@localhost
514
GRANT TRIGGER ON *.* TO 'test_noprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
515
GRANT INSERT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER, CREATE VIEW, SHOW VIEW, TRIGGER ON `priv_db`.`t1` TO 'test_noprivs'@'localhost'
516
revoke ALL PRIVILEGES, GRANT OPTION FROM test_yesprivs@localhost;
517
grant TRIGGER on *.* to test_yesprivs@localhost;
518
grant SELECT on priv_db.t1 to test_yesprivs@localhost;
519
show grants for test_yesprivs@localhost;
520
Grants for test_yesprivs@localhost
521
GRANT TRIGGER ON *.* TO 'test_yesprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
522
GRANT SELECT ON `priv_db`.`t1` TO 'test_yesprivs'@'localhost'
523
connect(localhost,test_noprivs,PWD,test,MASTER_MYPORT,MASTER_MYSOCK);
524
connect(localhost,test_yesprivs,PWD,test,MASTER_MYPORT,MASTER_MYSOCK);
526
Grants for test_noprivs@localhost
527
GRANT TRIGGER ON *.* TO 'test_noprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
528
GRANT INSERT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER, CREATE VIEW, SHOW VIEW, TRIGGER ON `priv_db`.`t1` TO 'test_noprivs'@'localhost'
530
create trigger trg5c_1 before INSERT on t1 for each row
531
set @test_var= new.f1;
532
set @test_var= 'before trig 3.5.3.8-1c';
533
insert into t1 (f1) values ('insert 3.5.3.8-1c');
534
ERROR 42000: SELECT command denied to user 'test_noprivs'@'localhost' for column 'f1' in table 't1'
537
before trig 3.5.3.8-1c
538
drop trigger trg5c_1;
540
Grants for test_yesprivs@localhost
541
GRANT TRIGGER ON *.* TO 'test_yesprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
542
GRANT SELECT ON `priv_db`.`t1` TO 'test_yesprivs'@'localhost'
544
create trigger trg5c_2 before INSERT on t1 for each row
545
set @test_var= new.f1;
546
set @test_var='before trig 3.5.3.8-2c';
547
insert into t1 (f1) values ('insert 3.5.3.8-2c');
551
drop trigger trg5c_2;
555
revoke ALL PRIVILEGES, GRANT OPTION FROM test_noprivs@localhost;
556
grant TRIGGER on *.* to test_noprivs@localhost;
557
grant UPDATE (f1), INSERT (f1) on priv_db.t1 to test_noprivs@localhost;
558
show grants for test_noprivs@localhost;
559
Grants for test_noprivs@localhost
560
GRANT TRIGGER ON *.* TO 'test_noprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
561
GRANT INSERT (f1), UPDATE (f1) ON `priv_db`.`t1` TO 'test_noprivs'@'localhost'
562
revoke ALL PRIVILEGES, GRANT OPTION FROM test_yesprivs@localhost;
563
grant TRIGGER on *.* to test_yesprivs@localhost;
564
grant SELECT (f1) on priv_db.t1 to test_yesprivs@localhost;
565
show grants for test_noprivs@localhost;
566
Grants for test_noprivs@localhost
567
GRANT TRIGGER ON *.* TO 'test_noprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
568
GRANT INSERT (f1), UPDATE (f1) ON `priv_db`.`t1` TO 'test_noprivs'@'localhost'
569
connect(localhost,test_noprivs,PWD,test,MASTER_MYPORT,MASTER_MYSOCK);
570
connect(localhost,test_yesprivs,PWD,test,MASTER_MYPORT,MASTER_MYSOCK);
572
Grants for test_noprivs@localhost
573
GRANT TRIGGER ON *.* TO 'test_noprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
574
GRANT INSERT (f1), UPDATE (f1) ON `priv_db`.`t1` TO 'test_noprivs'@'localhost'
576
create trigger trg5d_1 before INSERT on t1 for each row
577
set @test_var= new.f1;
578
set @test_var='before trig 3.5.3.8-1d';
579
insert into t1 (f1) values ('insert 3.5.3.8-1d');
580
ERROR 42000: SELECT command denied to user 'test_noprivs'@'localhost' for column 'f1' in table 't1'
583
before trig 3.5.3.8-1d
584
drop trigger trg5d_1;
586
Grants for test_yesprivs@localhost
587
GRANT TRIGGER ON *.* TO 'test_yesprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
588
GRANT SELECT (f1) ON `priv_db`.`t1` TO 'test_yesprivs'@'localhost'
590
create trigger trg5d_2 before INSERT on t1 for each row
591
set @test_var= new.f1;
592
set @test_var='before trig 3.5.3.8-2d';
593
insert into t1 (f1) values ('insert 3.5.3.8-2d');
597
drop trigger trg5d_2;
602
drop table if exists t1;
603
drop table if exists t2;
604
create table t1 (f1 int) engine= <engine_to_be_used>;
605
create table t2 (f2 int) engine= <engine_to_be_used>;
606
revoke ALL PRIVILEGES, GRANT OPTION FROM test_yesprivs@localhost;
607
grant TRIGGER on *.* to test_yesprivs@localhost;
608
grant SELECT, UPDATE on priv_db.t1 to test_yesprivs@localhost;
609
grant SELECT on priv_db.t2 to test_yesprivs@localhost;
610
show grants for test_yesprivs@localhost;
611
Grants for test_yesprivs@localhost
612
GRANT TRIGGER ON *.* TO 'test_yesprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
613
GRANT SELECT ON `priv_db`.`t2` TO 'test_yesprivs'@'localhost'
614
GRANT SELECT, UPDATE ON `priv_db`.`t1` TO 'test_yesprivs'@'localhost'
615
connect(localhost,test_yesprivs,PWD,test,MASTER_MYPORT,MASTER_MYSOCK);
618
test_yesprivs@localhost
620
create trigger trg1 before insert on t1 for each row
621
insert into t2 values (new.f1);
623
insert into t1 (f1) values (4);
624
ERROR 42000: INSERT command denied to user 'test_yesprivs'@'localhost' for table 't2'
625
revoke SELECT on priv_db.t2 from test_yesprivs@localhost;
626
grant INSERT on priv_db.t2 to test_yesprivs@localhost;
627
insert into t1 (f1) values (4);
628
select f1 from t1 order by f1;
631
select f2 from t2 order by f2;
636
create trigger trg2 before insert on t1 for each row
637
update t2 set f2=new.f1-1;
639
insert into t1 (f1) values (2);
640
ERROR 42000: UPDATE command denied to user 'test_yesprivs'@'localhost' for table 't2'
641
revoke INSERT on priv_db.t2 from test_yesprivs@localhost;
642
grant UPDATE on priv_db.t2 to test_yesprivs@localhost;
643
insert into t1 (f1) values (2);
644
select f1 from t1 order by f1;
648
select f2 from t2 order by f2;
653
create trigger trg3 before insert on t1 for each row
654
select f2 into @aaa from t2 where f2=new.f1;
656
insert into t1 (f1) values (1);
657
ERROR 42000: SELECT command denied to user 'test_yesprivs'@'localhost' for table 't2'
658
revoke UPDATE on priv_db.t2 from test_yesprivs@localhost;
659
grant SELECT on priv_db.t2 to test_yesprivs@localhost;
660
insert into t1 (f1) values (1);
661
select f1 from t1 order by f1;
666
select f2 from t2 order by f2;
674
create trigger trg4 before insert on t1 for each row
677
insert into t1 (f1) values (1);
678
ERROR 42000: DELETE command denied to user 'test_yesprivs'@'localhost' for table 't2'
679
revoke SELECT on priv_db.t2 from test_yesprivs@localhost;
680
grant DELETE on priv_db.t2 to test_yesprivs@localhost;
681
insert into t1 (f1) values (1);
682
select f1 from t1 order by f1;
688
select f2 from t2 order by f2;
690
drop database if exists priv_db;
691
drop user test_yesprivs@localhost;
692
drop user test_noprivs@localhost;
693
drop user test_noprivs;