2
drop table if exists tb3;
4
f118 char not null DEFAULT 'a',
5
f119 char binary not null DEFAULT b'101',
6
f120 char ascii not null DEFAULT b'101',
9
f129 binary not null DEFAULT b'101',
10
f130 tinyint not null DEFAULT 99,
11
f131 tinyint unsigned not null DEFAULT 99,
12
f132 tinyint zerofill not null DEFAULT 99,
13
f133 tinyint unsigned zerofill not null DEFAULT 99,
14
f134 smallint not null DEFAULT 999,
15
f135 smallint unsigned not null DEFAULT 999,
16
f136 smallint zerofill not null DEFAULT 999,
17
f137 smallint unsigned zerofill not null DEFAULT 999,
18
f138 mediumint not null DEFAULT 9999,
19
f139 mediumint unsigned not null DEFAULT 9999,
20
f140 mediumint zerofill not null DEFAULT 9999,
21
f141 mediumint unsigned zerofill not null DEFAULT 9999,
22
f142 int not null DEFAULT 99999,
23
f143 int unsigned not null DEFAULT 99999,
24
f144 int zerofill not null DEFAULT 99999,
25
f145 int unsigned zerofill not null DEFAULT 99999,
26
f146 bigint not null DEFAULT 999999,
27
f147 bigint unsigned not null DEFAULT 999999,
28
f148 bigint zerofill not null DEFAULT 999999,
29
f149 bigint unsigned zerofill not null DEFAULT 999999,
30
f150 decimal not null DEFAULT 999.999,
31
f151 decimal unsigned not null DEFAULT 999.17,
32
f152 decimal zerofill not null DEFAULT 999.999,
33
f153 decimal unsigned zerofill,
36
f156 decimal (0) unsigned,
37
f157 decimal (64) unsigned,
38
f158 decimal (0) zerofill,
39
f159 decimal (64) zerofill,
40
f160 decimal (0) unsigned zerofill,
41
f161 decimal (64) unsigned zerofill,
44
f164 decimal (0,0) unsigned,
45
f165 decimal (63,30) unsigned,
46
f166 decimal (0,0) zerofill,
47
f167 decimal (63,30) zerofill,
48
f168 decimal (0,0) unsigned zerofill,
49
f169 decimal (63,30) unsigned zerofill,
51
f171 numeric unsigned,
52
f172 numeric zerofill,
53
f173 numeric unsigned zerofill,
56
) engine = <engine_to_be_used>;
58
Note 1265 Data truncated for column 'f150' at row 1
59
Note 1265 Data truncated for column 'f151' at row 1
60
Note 1265 Data truncated for column 'f152' at row 1
64
drop database if exists priv_db;
65
create database priv_db;
67
create table t1 (f1 char(20)) engine= <engine_to_be_used>;
68
create User test_noprivs@localhost;
69
set password for test_noprivs@localhost = password('PWD');
70
create User test_yesprivs@localhost;
71
set password for test_yesprivs@localhost = password('PWD');
75
revoke ALL PRIVILEGES, GRANT OPTION FROM test_noprivs@localhost;
76
grant ALL on *.* to test_noprivs@localhost;
77
revoke TRIGGER on *.* from test_noprivs@localhost;
78
show grants for test_noprivs@localhost;
79
Grants for test_noprivs@localhost
80
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'
81
revoke ALL PRIVILEGES, GRANT OPTION FROM test_yesprivs@localhost;
82
grant TRIGGER on *.* to test_yesprivs@localhost;
83
grant SELECT on priv_db.t1 to test_yesprivs@localhost;
84
show grants for test_yesprivs@localhost;
85
Grants for test_yesprivs@localhost
86
GRANT TRIGGER ON *.* TO 'test_yesprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
87
GRANT SELECT ON `priv_db`.`t1` TO 'test_yesprivs'@'localhost'
88
connect(localhost,test_noprivs,PWD,test,MASTER_MYPORT,MASTER_MYSOCK);
89
connect(localhost,test_yesprivs,PWD,test,MASTER_MYPORT,MASTER_MYSOCK);
95
test_noprivs@localhost
97
create trigger trg1_1 before INSERT on t1 for each row
98
set new.f1 = 'trig 3.5.3.2_1-no';
99
ERROR 42000: TRIGGER command denied to user 'test_noprivs'@'localhost' for table 't1'
101
insert into t1 (f1) values ('insert 3.5.3.2-no');
102
select f1 from t1 order by f1;
107
test_yesprivs@localhost
109
create trigger trg1_2 before INSERT on t1 for each row
110
set new.f1 = 'trig 3.5.3.2_2-yes';
115
insert into t1 (f1) values ('insert 3.5.3.2-yes');
116
ERROR 42000: UPDATE command denied to user 'test_yesprivs'@'localhost' for column 'f1' in table 't1'
117
select f1 from t1 order by f1;
120
grant UPDATE on priv_db.t1 to test_yesprivs@localhost;
121
insert into t1 (f1) values ('insert 3.5.3.2-yes');
122
select f1 from t1 order by f1;
131
ERROR 42000: TRIGGER command denied to user 'test_noprivs'@'localhost' for table 't1'
133
insert into t1 (f1) values ('insert 3.5.3.6-yes');
134
select f1 from t1 order by f1;
142
insert into t1 (f1) values ('insert 3.5.3.6-no');
143
select f1 from t1 order by f1;
153
revoke ALL PRIVILEGES, GRANT OPTION FROM test_noprivs@localhost;
154
grant ALL on *.* to test_noprivs@localhost;
155
revoke UPDATE on *.* from test_noprivs@localhost;
156
show grants for test_noprivs@localhost;
157
Grants for test_noprivs@localhost
158
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'
159
revoke ALL PRIVILEGES, GRANT OPTION FROM test_yesprivs@localhost;
160
grant TRIGGER, UPDATE on *.* to test_yesprivs@localhost;
161
show grants for test_yesprivs@localhost;
162
Grants for test_yesprivs@localhost
163
GRANT UPDATE, TRIGGER ON *.* TO 'test_yesprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
164
connect(localhost,test_noprivs,PWD,test,MASTER_MYPORT,MASTER_MYSOCK);
165
connect(localhost,test_yesprivs,PWD,test,MASTER_MYPORT,MASTER_MYSOCK);
168
test_noprivs@localhost
171
Grants for test_noprivs@localhost
172
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'
173
select f1 from t1 order by f1;
179
create trigger trg4a_1 before INSERT on t1 for each row
180
set new.f1 = 'trig 3.5.3.7-1a';
181
insert into t1 (f1) values ('insert 3.5.3.7-1a');
182
ERROR 42000: UPDATE command denied to user 'test_noprivs'@'localhost' for column 'f1' in table 't1'
183
select f1 from t1 order by f1;
189
drop trigger trg4a_1;
193
test_yesprivs@localhost
195
Grants for test_yesprivs@localhost
196
GRANT UPDATE, TRIGGER ON *.* TO 'test_yesprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
197
create trigger trg4a_2 before INSERT on t1 for each row
198
set new.f1 = 'trig 3.5.3.7-2a';
199
insert into t1 (f1) values ('insert 3.5.3.7-2b');
200
select f1 from t1 order by f1;
207
drop trigger trg4a_2;
211
revoke ALL PRIVILEGES, GRANT OPTION FROM test_noprivs@localhost;
212
grant TRIGGER on *.* to test_noprivs;
213
grant ALL on priv_db.* to test_noprivs@localhost;
214
revoke UPDATE on priv_db.* from test_noprivs@localhost;
215
show grants for test_noprivs;
216
Grants for test_noprivs@%
217
GRANT TRIGGER ON *.* TO 'test_noprivs'@'%'
218
revoke ALL PRIVILEGES, GRANT OPTION FROM test_yesprivs@localhost;
219
grant TRIGGER on *.* to test_yesprivs@localhost;
220
grant UPDATE on priv_db.* to test_yesprivs@localhost;
221
show grants for test_yesprivs@localhost;
222
Grants for test_yesprivs@localhost
223
GRANT TRIGGER ON *.* TO 'test_yesprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
224
GRANT UPDATE ON `priv_db`.* TO 'test_yesprivs'@'localhost'
225
connect(localhost,test_noprivs,PWD,test,MASTER_MYPORT,MASTER_MYSOCK);
226
connect(localhost,test_yesprivs,PWD,test,MASTER_MYPORT,MASTER_MYSOCK);
228
Grants for test_noprivs@localhost
229
GRANT USAGE ON *.* TO 'test_noprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
230
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'
232
create trigger trg4b_1 before UPDATE on t1 for each row
233
set new.f1 = 'trig 3.5.3.7-1b';
234
insert into t1 (f1) values ('insert 3.5.3.7-1b');
235
select f1 from t1 order by f1;
243
update t1 set f1 = 'update 3.5.3.7-1b' where f1 = 'insert 3.5.3.7-1b';
244
ERROR 42000: UPDATE command denied to user 'test_noprivs'@'localhost' for column 'f1' in table 't1'
245
select f1 from t1 order by f1;
253
drop trigger trg4b_1;
255
Grants for test_yesprivs@localhost
256
GRANT TRIGGER ON *.* TO 'test_yesprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
257
GRANT UPDATE ON `priv_db`.* TO 'test_yesprivs'@'localhost'
259
create trigger trg4b_2 before UPDATE on t1 for each row
260
set new.f1 = 'trig 3.5.3.7-2b';
261
insert into t1 (f1) values ('insert 3.5.3.7-2b');
262
select f1 from t1 order by f1;
271
update t1 set f1 = 'update 3.5.3.7-2b' where f1 = 'insert 3.5.3.7-2b';
272
select f1 from t1 order by f1;
281
drop trigger trg4b_2;
285
revoke ALL PRIVILEGES, GRANT OPTION FROM test_noprivs@localhost;
286
grant TRIGGER on *.* to test_noprivs@localhost;
287
grant ALL on priv_db.t1 to test_noprivs@localhost;
288
revoke UPDATE on priv_db.t1 from test_noprivs@localhost;
289
show grants for test_noprivs;
290
Grants for test_noprivs@%
291
GRANT TRIGGER ON *.* TO 'test_noprivs'@'%'
292
revoke ALL PRIVILEGES, GRANT OPTION FROM test_yesprivs@localhost;
293
grant TRIGGER on *.* to test_yesprivs@localhost;
294
grant UPDATE on priv_db.t1 to test_yesprivs@localhost;
295
show grants for test_yesprivs@localhost;
296
Grants for test_yesprivs@localhost
297
GRANT TRIGGER ON *.* TO 'test_yesprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
298
GRANT UPDATE ON `priv_db`.`t1` TO 'test_yesprivs'@'localhost'
299
connect(localhost,test_noprivs,PWD,test,MASTER_MYPORT,MASTER_MYSOCK);
300
connect(localhost,test_yesprivs,PWD,test,MASTER_MYPORT,MASTER_MYSOCK);
302
Grants for test_noprivs@localhost
303
GRANT TRIGGER ON *.* TO 'test_noprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
304
GRANT SELECT, INSERT, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER, CREATE VIEW, SHOW VIEW, TRIGGER ON `priv_db`.`t1` TO 'test_noprivs'@'localhost'
306
create trigger trg4c_1 before INSERT on t1 for each row
307
set new.f1 = 'trig 3.5.3.7-1c';
308
insert into t1 (f1) values ('insert 3.5.3.7-1c');
309
ERROR 42000: UPDATE command denied to user 'test_noprivs'@'localhost' for column 'f1' in table 't1'
310
select f1 from t1 order by f1;
319
drop trigger trg4c_1;
321
Grants for test_yesprivs@localhost
322
GRANT TRIGGER ON *.* TO 'test_yesprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
323
GRANT UPDATE ON `priv_db`.`t1` TO 'test_yesprivs'@'localhost'
325
create trigger trg4c_2 before INSERT on t1 for each row
326
set new.f1 = 'trig 3.5.3.7-2c';
327
insert into t1 (f1) values ('insert 3.5.3.7-2c');
328
select f1 from t1 order by f1;
338
drop trigger trg4c_2;
342
revoke ALL PRIVILEGES, GRANT OPTION FROM test_noprivs@localhost;
343
grant TRIGGER on *.* to test_noprivs@localhost;
344
grant SELECT (f1), INSERT (f1) on priv_db.t1 to test_noprivs@localhost;
345
show grants for test_noprivs;
346
Grants for test_noprivs@%
347
GRANT TRIGGER ON *.* TO 'test_noprivs'@'%'
348
revoke ALL PRIVILEGES, GRANT OPTION FROM test_yesprivs@localhost;
349
grant TRIGGER on *.* to test_yesprivs@localhost;
350
grant UPDATE (f1) on priv_db.t1 to test_yesprivs@localhost;
351
show grants for test_noprivs;
352
Grants for test_noprivs@%
353
GRANT TRIGGER ON *.* TO 'test_noprivs'@'%'
354
connect(localhost,test_noprivs,PWD,test,MASTER_MYPORT,MASTER_MYSOCK);
355
connect(localhost,test_yesprivs,PWD,test,MASTER_MYPORT,MASTER_MYSOCK);
357
Grants for test_noprivs@localhost
358
GRANT TRIGGER ON *.* TO 'test_noprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
359
GRANT SELECT (f1), INSERT (f1) ON `priv_db`.`t1` TO 'test_noprivs'@'localhost'
361
create trigger trg4d_1 before INSERT on t1 for each row
362
set new.f1 = 'trig 3.5.3.7-1d';
363
insert into t1 (f1) values ('insert 3.5.3.7-1d');
364
ERROR 42000: UPDATE command denied to user 'test_noprivs'@'localhost' for column 'f1' in table 't1'
365
select f1 from t1 order by f1;
375
drop trigger trg4d_1;
377
Grants for test_yesprivs@localhost
378
GRANT TRIGGER ON *.* TO 'test_yesprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
379
GRANT UPDATE (f1) ON `priv_db`.`t1` TO 'test_yesprivs'@'localhost'
381
create trigger trg4d_2 before INSERT on t1 for each row
382
set new.f1 = 'trig 3.5.3.7-2d';
383
insert into t1 (f1) values ('insert 3.5.3.7-2d');
384
select f1 from t1 order by f1;
395
drop trigger trg4d_2;
399
revoke ALL PRIVILEGES, GRANT OPTION FROM test_noprivs@localhost;
400
grant ALL on *.* to test_noprivs@localhost;
401
revoke SELECT on *.* from test_noprivs@localhost;
402
show grants for test_noprivs@localhost;
403
Grants for test_noprivs@localhost
404
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'
405
revoke ALL PRIVILEGES, GRANT OPTION FROM test_yesprivs@localhost;
406
grant TRIGGER, SELECT on *.* to test_yesprivs@localhost;
407
show grants for test_yesprivs@localhost;
408
Grants for test_yesprivs@localhost
409
GRANT SELECT, TRIGGER ON *.* TO 'test_yesprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
410
connect(localhost,test_noprivs,PWD,test,MASTER_MYPORT,MASTER_MYSOCK);
411
connect(localhost,test_yesprivs,PWD,test,MASTER_MYPORT,MASTER_MYSOCK);
414
test_noprivs@localhost
417
Grants for test_noprivs@localhost
418
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'
419
create trigger trg5a_1 before INSERT on t1 for each row
420
set @test_var = new.f1;
421
set @test_var = 'before trig 3.5.3.8-1a';
424
before trig 3.5.3.8-1a
425
insert into t1 (f1) values ('insert 3.5.3.8-1a');
426
ERROR 42000: SELECT command denied to user 'test_noprivs'@'localhost' for column 'f1' in table 't1'
429
before trig 3.5.3.8-1a
430
drop trigger trg5a_1;
434
test_yesprivs@localhost
436
Grants for test_yesprivs@localhost
437
GRANT SELECT, TRIGGER ON *.* TO 'test_yesprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
438
create trigger trg5a_2 before INSERT on t1 for each row
439
set @test_var= new.f1;
440
set @test_var= 'before trig 3.5.3.8-2a';
443
before trig 3.5.3.8-2a
444
insert into t1 (f1) values ('insert 3.5.3.8-2a');
448
drop trigger trg5a_2;
452
revoke ALL PRIVILEGES, GRANT OPTION FROM test_noprivs@localhost;
453
grant TRIGGER on *.* to test_noprivs@localhost;
454
grant ALL on priv_db.* to test_noprivs@localhost;
455
revoke SELECT on priv_db.* from test_noprivs@localhost;
456
show grants for test_noprivs@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'
460
revoke ALL PRIVILEGES, GRANT OPTION FROM test_yesprivs@localhost;
461
grant TRIGGER on *.* to test_yesprivs@localhost;
462
grant SELECT on priv_db.* to test_yesprivs@localhost;
463
show grants for test_yesprivs@localhost;
464
Grants for test_yesprivs@localhost
465
GRANT TRIGGER ON *.* TO 'test_yesprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
466
GRANT SELECT ON `priv_db`.* TO 'test_yesprivs'@'localhost'
467
connect(localhost,test_noprivs,PWD,test,MASTER_MYPORT,MASTER_MYSOCK);
468
connect(localhost,test_yesprivs,PWD,test,MASTER_MYPORT,MASTER_MYSOCK);
470
Grants for test_noprivs@localhost
471
GRANT TRIGGER ON *.* TO 'test_noprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
472
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'
474
create trigger trg5b_1 before UPDATE on t1 for each row
475
set @test_var= new.f1;
476
set @test_var= 'before trig 3.5.3.8-1b';
477
insert into t1 (f1) values ('insert 3.5.3.8-1b');
480
before trig 3.5.3.8-1b
481
update t1 set f1= 'update 3.5.3.8-1b' where f1 = 'insert 3.5.3.8-1b';
482
ERROR 42000: SELECT command denied to user 'test_noprivs'@'localhost' for column 'f1' in table 't1'
485
before trig 3.5.3.8-1b
486
drop trigger trg5b_1;
488
Grants for test_yesprivs@localhost
489
GRANT TRIGGER ON *.* TO 'test_yesprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
490
GRANT SELECT ON `priv_db`.* TO 'test_yesprivs'@'localhost'
492
create trigger trg5b_2 before UPDATE on t1 for each row
493
set @test_var= new.f1;
494
set @test_var= 'before trig 3.5.3.8-2b';
495
insert into t1 (f1) values ('insert 3.5.3.8-2b');
498
before trig 3.5.3.8-2b
499
update t1 set f1= 'update 3.5.3.8-2b' where f1 = 'insert 3.5.3.8-2b';
503
drop trigger trg5b_2;
507
revoke ALL PRIVILEGES, GRANT OPTION FROM test_noprivs@localhost;
508
grant TRIGGER on *.* to test_noprivs@localhost;
509
grant ALL on priv_db.t1 to test_noprivs@localhost;
510
revoke SELECT on priv_db.t1 from test_noprivs@localhost;
511
show grants for test_noprivs@localhost;
512
Grants for test_noprivs@localhost
513
GRANT TRIGGER ON *.* TO 'test_noprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
514
GRANT INSERT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER, CREATE VIEW, SHOW VIEW, TRIGGER ON `priv_db`.`t1` TO 'test_noprivs'@'localhost'
515
revoke ALL PRIVILEGES, GRANT OPTION FROM test_yesprivs@localhost;
516
grant TRIGGER on *.* to test_yesprivs@localhost;
517
grant SELECT on priv_db.t1 to test_yesprivs@localhost;
518
show grants for test_yesprivs@localhost;
519
Grants for test_yesprivs@localhost
520
GRANT TRIGGER ON *.* TO 'test_yesprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
521
GRANT SELECT ON `priv_db`.`t1` TO 'test_yesprivs'@'localhost'
522
connect(localhost,test_noprivs,PWD,test,MASTER_MYPORT,MASTER_MYSOCK);
523
connect(localhost,test_yesprivs,PWD,test,MASTER_MYPORT,MASTER_MYSOCK);
525
Grants for test_noprivs@localhost
526
GRANT TRIGGER ON *.* TO 'test_noprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
527
GRANT INSERT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER, CREATE VIEW, SHOW VIEW, TRIGGER ON `priv_db`.`t1` TO 'test_noprivs'@'localhost'
529
create trigger trg5c_1 before INSERT on t1 for each row
530
set @test_var= new.f1;
531
set @test_var= 'before trig 3.5.3.8-1c';
532
insert into t1 (f1) values ('insert 3.5.3.8-1c');
533
ERROR 42000: SELECT command denied to user 'test_noprivs'@'localhost' for column 'f1' in table 't1'
536
before trig 3.5.3.8-1c
537
drop trigger trg5c_1;
539
Grants for test_yesprivs@localhost
540
GRANT TRIGGER ON *.* TO 'test_yesprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
541
GRANT SELECT ON `priv_db`.`t1` TO 'test_yesprivs'@'localhost'
543
create trigger trg5c_2 before INSERT on t1 for each row
544
set @test_var= new.f1;
545
set @test_var='before trig 3.5.3.8-2c';
546
insert into t1 (f1) values ('insert 3.5.3.8-2c');
550
drop trigger trg5c_2;
554
revoke ALL PRIVILEGES, GRANT OPTION FROM test_noprivs@localhost;
555
grant TRIGGER on *.* to test_noprivs@localhost;
556
grant UPDATE (f1), INSERT (f1) on priv_db.t1 to test_noprivs@localhost;
557
show grants for test_noprivs@localhost;
558
Grants for test_noprivs@localhost
559
GRANT TRIGGER ON *.* TO 'test_noprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
560
GRANT INSERT (f1), UPDATE (f1) ON `priv_db`.`t1` TO 'test_noprivs'@'localhost'
561
revoke ALL PRIVILEGES, GRANT OPTION FROM test_yesprivs@localhost;
562
grant TRIGGER on *.* to test_yesprivs@localhost;
563
grant SELECT (f1) on priv_db.t1 to test_yesprivs@localhost;
564
show grants for test_noprivs@localhost;
565
Grants for test_noprivs@localhost
566
GRANT TRIGGER ON *.* TO 'test_noprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
567
GRANT INSERT (f1), UPDATE (f1) ON `priv_db`.`t1` TO 'test_noprivs'@'localhost'
568
connect(localhost,test_noprivs,PWD,test,MASTER_MYPORT,MASTER_MYSOCK);
569
connect(localhost,test_yesprivs,PWD,test,MASTER_MYPORT,MASTER_MYSOCK);
571
Grants for test_noprivs@localhost
572
GRANT TRIGGER ON *.* TO 'test_noprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
573
GRANT INSERT (f1), UPDATE (f1) ON `priv_db`.`t1` TO 'test_noprivs'@'localhost'
575
create trigger trg5d_1 before INSERT on t1 for each row
576
set @test_var= new.f1;
577
set @test_var='before trig 3.5.3.8-1d';
578
insert into t1 (f1) values ('insert 3.5.3.8-1d');
579
ERROR 42000: SELECT command denied to user 'test_noprivs'@'localhost' for column 'f1' in table 't1'
582
before trig 3.5.3.8-1d
583
drop trigger trg5d_1;
585
Grants for test_yesprivs@localhost
586
GRANT TRIGGER ON *.* TO 'test_yesprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
587
GRANT SELECT (f1) ON `priv_db`.`t1` TO 'test_yesprivs'@'localhost'
589
create trigger trg5d_2 before INSERT on t1 for each row
590
set @test_var= new.f1;
591
set @test_var='before trig 3.5.3.8-2d';
592
insert into t1 (f1) values ('insert 3.5.3.8-2d');
596
drop trigger trg5d_2;
601
drop table if exists t1;
602
drop table if exists t2;
603
create table t1 (f1 int) engine= <engine_to_be_used>;
604
create table t2 (f2 int) engine= <engine_to_be_used>;
605
revoke ALL PRIVILEGES, GRANT OPTION FROM test_yesprivs@localhost;
606
grant TRIGGER on *.* to test_yesprivs@localhost;
607
grant SELECT, UPDATE on priv_db.t1 to test_yesprivs@localhost;
608
grant SELECT on priv_db.t2 to test_yesprivs@localhost;
609
show grants for test_yesprivs@localhost;
610
Grants for test_yesprivs@localhost
611
GRANT TRIGGER ON *.* TO 'test_yesprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
612
GRANT SELECT ON `priv_db`.`t2` TO 'test_yesprivs'@'localhost'
613
GRANT SELECT, UPDATE ON `priv_db`.`t1` TO 'test_yesprivs'@'localhost'
614
connect(localhost,test_yesprivs,PWD,test,MASTER_MYPORT,MASTER_MYSOCK);
617
test_yesprivs@localhost
619
create trigger trg1 before insert on t1 for each row
620
insert into t2 values (new.f1);
622
insert into t1 (f1) values (4);
623
ERROR 42000: INSERT command denied to user 'test_yesprivs'@'localhost' for table 't2'
624
revoke SELECT on priv_db.t2 from test_yesprivs@localhost;
625
grant INSERT on priv_db.t2 to test_yesprivs@localhost;
626
insert into t1 (f1) values (4);
627
select f1 from t1 order by f1;
630
select f2 from t2 order by f2;
635
create trigger trg2 before insert on t1 for each row
636
update t2 set f2=new.f1-1;
638
insert into t1 (f1) values (2);
639
ERROR 42000: UPDATE command denied to user 'test_yesprivs'@'localhost' for table 't2'
640
revoke INSERT on priv_db.t2 from test_yesprivs@localhost;
641
grant UPDATE on priv_db.t2 to test_yesprivs@localhost;
642
insert into t1 (f1) values (2);
643
select f1 from t1 order by f1;
647
select f2 from t2 order by f2;
652
create trigger trg3 before insert on t1 for each row
653
select f2 into @aaa from t2 where f2=new.f1;
655
insert into t1 (f1) values (1);
656
ERROR 42000: SELECT command denied to user 'test_yesprivs'@'localhost' for table 't2'
657
revoke UPDATE on priv_db.t2 from test_yesprivs@localhost;
658
grant SELECT on priv_db.t2 to test_yesprivs@localhost;
659
insert into t1 (f1) values (1);
660
select f1 from t1 order by f1;
665
select f2 from t2 order by f2;
673
create trigger trg4 before insert on t1 for each row
676
insert into t1 (f1) values (1);
677
ERROR 42000: DELETE command denied to user 'test_yesprivs'@'localhost' for table 't2'
678
revoke SELECT on priv_db.t2 from test_yesprivs@localhost;
679
grant DELETE on priv_db.t2 to test_yesprivs@localhost;
680
insert into t1 (f1) values (1);
681
select f1 from t1 order by f1;
687
select f2 from t2 order by f2;
689
drop database if exists priv_db;
690
drop user test_yesprivs@localhost;
691
drop user test_noprivs@localhost;
692
drop user test_noprivs;