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',
10
f124 longtext unicode,
15
f129 binary not null DEFAULT b'101',
16
f130 tinyint not null DEFAULT 99,
17
f131 tinyint unsigned not null DEFAULT 99,
18
f132 tinyint zerofill not null DEFAULT 99,
19
f133 tinyint unsigned zerofill not null DEFAULT 99,
20
f134 smallint not null DEFAULT 999,
21
f135 smallint unsigned not null DEFAULT 999,
22
f136 smallint zerofill not null DEFAULT 999,
23
f137 smallint unsigned zerofill not null DEFAULT 999,
24
f138 mediumint not null DEFAULT 9999,
25
f139 mediumint unsigned not null DEFAULT 9999,
26
f140 mediumint zerofill not null DEFAULT 9999,
27
f141 mediumint unsigned zerofill not null DEFAULT 9999,
28
f142 int not null DEFAULT 99999,
29
f143 int unsigned not null DEFAULT 99999,
30
f144 int zerofill not null DEFAULT 99999,
31
f145 int unsigned zerofill not null DEFAULT 99999,
32
f146 bigint not null DEFAULT 999999,
33
f147 bigint unsigned not null DEFAULT 999999,
34
f148 bigint zerofill not null DEFAULT 999999,
35
f149 bigint unsigned zerofill not null DEFAULT 999999,
36
f150 decimal not null DEFAULT 999.999,
37
f151 decimal unsigned not null DEFAULT 999.17,
38
f152 decimal zerofill not null DEFAULT 999.999,
39
f153 decimal unsigned zerofill,
42
f156 decimal (0) unsigned,
43
f157 decimal (64) unsigned,
44
f158 decimal (0) zerofill,
45
f159 decimal (64) zerofill,
46
f160 decimal (0) unsigned zerofill,
47
f161 decimal (64) unsigned zerofill,
50
f164 decimal (0,0) unsigned,
51
f165 decimal (63,30) unsigned,
52
f166 decimal (0,0) zerofill,
53
f167 decimal (63,30) zerofill,
54
f168 decimal (0,0) unsigned zerofill,
55
f169 decimal (63,30) unsigned zerofill,
57
f171 numeric unsigned,
58
f172 numeric zerofill,
59
f173 numeric unsigned zerofill,
64
Note 1265 Data truncated for column 'f150' at row 1
65
Note 1265 Data truncated for column 'f151' at row 1
66
Note 1265 Data truncated for column 'f152' at row 1
67
load data infile 'MYSQL_TEST_DIR/suite/funcs_1/data/innodb_tb3.txt' into table tb3 ;
71
drop database if exists priv_db;
72
create database priv_db;
74
create table t1 (f1 char(20)) engine= innodb;
75
create User test_noprivs@localhost;
76
set password for test_noprivs@localhost = password('PWD');
77
create User test_yesprivs@localhost;
78
set password for test_yesprivs@localhost = password('PWD');
82
revoke ALL PRIVILEGES, GRANT OPTION FROM test_noprivs@localhost;
83
grant ALL on *.* to test_noprivs@localhost;
84
revoke TRIGGER on *.* from test_noprivs@localhost;
85
show grants for test_noprivs@localhost;
86
Grants for test_noprivs@localhost
87
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'
88
revoke ALL PRIVILEGES, GRANT OPTION FROM test_yesprivs@localhost;
89
grant TRIGGER on *.* to test_yesprivs@localhost;
90
grant SELECT on priv_db.t1 to test_yesprivs@localhost;
91
show grants for test_yesprivs@localhost;
92
Grants for test_yesprivs@localhost
93
GRANT TRIGGER ON *.* TO 'test_yesprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
94
GRANT SELECT ON `priv_db`.`t1` TO 'test_yesprivs'@'localhost'
95
connect(localhost,test_noprivs,PWD,test,MASTER_MYPORT,MASTER_MYSOCK);
96
connect(localhost,test_yesprivs,PWD,test,MASTER_MYPORT,MASTER_MYSOCK);
102
test_noprivs@localhost
104
create trigger trg1_1 before INSERT on t1 for each row
105
set new.f1 = 'trig 3.5.3.2_1-no';
106
Got one of the listed errors
108
insert into t1 (f1) values ('insert 3.5.3.2-no');
109
select f1 from t1 order by f1;
114
test_yesprivs@localhost
116
create trigger trg1_2 before INSERT on t1 for each row
117
set new.f1 = 'trig 3.5.3.2_2-yes';
122
insert into t1 (f1) values ('insert 3.5.3.2-yes');
123
ERROR 42000: UPDATE command denied to user 'test_yesprivs'@'localhost' for column 'f1' in table 't1'
124
select f1 from t1 order by f1;
127
grant UPDATE on priv_db.t1 to test_yesprivs@localhost;
128
insert into t1 (f1) values ('insert 3.5.3.2-yes');
129
select f1 from t1 order by f1;
138
Got one of the listed errors
140
insert into t1 (f1) values ('insert 3.5.3.6-yes');
141
select f1 from t1 order by f1;
149
insert into t1 (f1) values ('insert 3.5.3.6-no');
150
select f1 from t1 order by f1;
160
revoke ALL PRIVILEGES, GRANT OPTION FROM test_noprivs@localhost;
161
grant ALL on *.* to test_noprivs@localhost;
162
revoke UPDATE on *.* from test_noprivs@localhost;
163
show grants for test_noprivs@localhost;
164
Grants for test_noprivs@localhost
165
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'
166
revoke ALL PRIVILEGES, GRANT OPTION FROM test_yesprivs@localhost;
167
grant TRIGGER, UPDATE on *.* to test_yesprivs@localhost;
168
show grants for test_yesprivs@localhost;
169
Grants for test_yesprivs@localhost
170
GRANT UPDATE, TRIGGER ON *.* TO 'test_yesprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
171
connect(localhost,test_noprivs,PWD,test,MASTER_MYPORT,MASTER_MYSOCK);
172
connect(localhost,test_yesprivs,PWD,test,MASTER_MYPORT,MASTER_MYSOCK);
175
test_noprivs@localhost
178
Grants for test_noprivs@localhost
179
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'
180
select f1 from t1 order by f1;
186
create trigger trg4a_1 before INSERT on t1 for each row
187
set new.f1 = 'trig 3.5.3.7-1a';
188
insert into t1 (f1) values ('insert 3.5.3.7-1a');
189
ERROR 42000: UPDATE command denied to user 'test_noprivs'@'localhost' for column 'f1' in table 't1'
190
select f1 from t1 order by f1;
196
drop trigger trg4a_1;
200
test_yesprivs@localhost
202
Grants for test_yesprivs@localhost
203
GRANT UPDATE, TRIGGER ON *.* TO 'test_yesprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
204
create trigger trg4a_2 before INSERT on t1 for each row
205
set new.f1 = 'trig 3.5.3.7-2a';
206
insert into t1 (f1) values ('insert 3.5.3.7-2b');
207
select f1 from t1 order by f1;
214
drop trigger trg4a_2;
218
revoke ALL PRIVILEGES, GRANT OPTION FROM test_noprivs@localhost;
219
grant TRIGGER on *.* to test_noprivs;
220
grant ALL on priv_db.* to test_noprivs@localhost;
221
revoke UPDATE on priv_db.* from test_noprivs@localhost;
222
show grants for test_noprivs;
223
Grants for test_noprivs@%
224
GRANT TRIGGER ON *.* TO 'test_noprivs'@'%'
225
revoke ALL PRIVILEGES, GRANT OPTION FROM test_yesprivs@localhost;
226
grant TRIGGER on *.* to test_yesprivs@localhost;
227
grant UPDATE on priv_db.* to test_yesprivs@localhost;
228
show grants for test_yesprivs@localhost;
229
Grants for test_yesprivs@localhost
230
GRANT TRIGGER ON *.* TO 'test_yesprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
231
GRANT UPDATE ON `priv_db`.* TO 'test_yesprivs'@'localhost'
232
connect(localhost,test_noprivs,PWD,test,MASTER_MYPORT,MASTER_MYSOCK);
233
connect(localhost,test_yesprivs,PWD,test,MASTER_MYPORT,MASTER_MYSOCK);
235
Grants for test_noprivs@localhost
236
GRANT USAGE ON *.* TO 'test_noprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
237
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'
239
create trigger trg4b_1 before UPDATE on t1 for each row
240
set new.f1 = 'trig 3.5.3.7-1b';
241
insert into t1 (f1) values ('insert 3.5.3.7-1b');
242
select f1 from t1 order by f1;
250
update t1 set f1 = 'update 3.5.3.7-1b' where f1 = 'insert 3.5.3.7-1b';
251
ERROR 42000: UPDATE command denied to user 'test_noprivs'@'localhost' for column 'f1' in table 't1'
252
select f1 from t1 order by f1;
260
drop trigger trg4b_1;
262
Grants for test_yesprivs@localhost
263
GRANT TRIGGER ON *.* TO 'test_yesprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
264
GRANT UPDATE ON `priv_db`.* TO 'test_yesprivs'@'localhost'
266
create trigger trg4b_2 before UPDATE on t1 for each row
267
set new.f1 = 'trig 3.5.3.7-2b';
268
insert into t1 (f1) values ('insert 3.5.3.7-2b');
269
select f1 from t1 order by f1;
278
update t1 set f1 = 'update 3.5.3.7-2b' where f1 = 'insert 3.5.3.7-2b';
279
select f1 from t1 order by f1;
288
drop trigger trg4b_2;
292
revoke ALL PRIVILEGES, GRANT OPTION FROM test_noprivs@localhost;
293
grant TRIGGER on *.* to test_noprivs@localhost;
294
grant ALL on priv_db.t1 to test_noprivs@localhost;
295
revoke UPDATE on priv_db.t1 from test_noprivs@localhost;
296
show grants for test_noprivs;
297
Grants for test_noprivs@%
298
GRANT TRIGGER ON *.* TO 'test_noprivs'@'%'
299
revoke ALL PRIVILEGES, GRANT OPTION FROM test_yesprivs@localhost;
300
grant TRIGGER on *.* to test_yesprivs@localhost;
301
grant UPDATE on priv_db.t1 to test_yesprivs@localhost;
302
show grants for test_yesprivs@localhost;
303
Grants for test_yesprivs@localhost
304
GRANT TRIGGER ON *.* TO 'test_yesprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
305
GRANT UPDATE ON `priv_db`.`t1` TO 'test_yesprivs'@'localhost'
306
connect(localhost,test_noprivs,PWD,test,MASTER_MYPORT,MASTER_MYSOCK);
307
connect(localhost,test_yesprivs,PWD,test,MASTER_MYPORT,MASTER_MYSOCK);
309
Grants for test_noprivs@localhost
310
GRANT TRIGGER ON *.* TO 'test_noprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
311
GRANT SELECT, INSERT, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER, CREATE VIEW, SHOW VIEW, TRIGGER ON `priv_db`.`t1` TO 'test_noprivs'@'localhost'
313
create trigger trg4c_1 before INSERT on t1 for each row
314
set new.f1 = 'trig 3.5.3.7-1c';
315
insert into t1 (f1) values ('insert 3.5.3.7-1c');
316
ERROR 42000: UPDATE command denied to user 'test_noprivs'@'localhost' for column 'f1' in table 't1'
317
select f1 from t1 order by f1;
326
drop trigger trg4c_1;
328
Grants for test_yesprivs@localhost
329
GRANT TRIGGER ON *.* TO 'test_yesprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
330
GRANT UPDATE ON `priv_db`.`t1` TO 'test_yesprivs'@'localhost'
332
create trigger trg4c_2 before INSERT on t1 for each row
333
set new.f1 = 'trig 3.5.3.7-2c';
334
insert into t1 (f1) values ('insert 3.5.3.7-2c');
335
select f1 from t1 order by f1;
345
drop trigger trg4c_2;
349
revoke ALL PRIVILEGES, GRANT OPTION FROM test_noprivs@localhost;
350
grant TRIGGER on *.* to test_noprivs@localhost;
351
grant SELECT (f1), INSERT (f1) on priv_db.t1 to test_noprivs@localhost;
352
show grants for test_noprivs;
353
Grants for test_noprivs@%
354
GRANT TRIGGER ON *.* TO 'test_noprivs'@'%'
355
revoke ALL PRIVILEGES, GRANT OPTION FROM test_yesprivs@localhost;
356
grant TRIGGER on *.* to test_yesprivs@localhost;
357
grant UPDATE (f1) on priv_db.t1 to test_yesprivs@localhost;
358
show grants for test_noprivs;
359
Grants for test_noprivs@%
360
GRANT TRIGGER ON *.* TO 'test_noprivs'@'%'
361
connect(localhost,test_noprivs,PWD,test,MASTER_MYPORT,MASTER_MYSOCK);
362
connect(localhost,test_yesprivs,PWD,test,MASTER_MYPORT,MASTER_MYSOCK);
364
Grants for test_noprivs@localhost
365
GRANT TRIGGER ON *.* TO 'test_noprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
366
GRANT SELECT (f1), INSERT (f1) ON `priv_db`.`t1` TO 'test_noprivs'@'localhost'
368
create trigger trg4d_1 before INSERT on t1 for each row
369
set new.f1 = 'trig 3.5.3.7-1d';
370
insert into t1 (f1) values ('insert 3.5.3.7-1d');
371
ERROR 42000: UPDATE command denied to user 'test_noprivs'@'localhost' for column 'f1' in table 't1'
372
select f1 from t1 order by f1;
382
drop trigger trg4d_1;
384
Grants for test_yesprivs@localhost
385
GRANT TRIGGER ON *.* TO 'test_yesprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
386
GRANT UPDATE (f1) ON `priv_db`.`t1` TO 'test_yesprivs'@'localhost'
388
create trigger trg4d_2 before INSERT on t1 for each row
389
set new.f1 = 'trig 3.5.3.7-2d';
390
insert into t1 (f1) values ('insert 3.5.3.7-2d');
391
select f1 from t1 order by f1;
402
drop trigger trg4d_2;
406
revoke ALL PRIVILEGES, GRANT OPTION FROM test_noprivs@localhost;
407
grant ALL on *.* to test_noprivs@localhost;
408
revoke SELECT on *.* from test_noprivs@localhost;
409
show grants for test_noprivs@localhost;
410
Grants for test_noprivs@localhost
411
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'
412
revoke ALL PRIVILEGES, GRANT OPTION FROM test_yesprivs@localhost;
413
grant TRIGGER, SELECT on *.* to test_yesprivs@localhost;
414
show grants for test_yesprivs@localhost;
415
Grants for test_yesprivs@localhost
416
GRANT SELECT, TRIGGER ON *.* TO 'test_yesprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
417
connect(localhost,test_noprivs,PWD,test,MASTER_MYPORT,MASTER_MYSOCK);
418
connect(localhost,test_yesprivs,PWD,test,MASTER_MYPORT,MASTER_MYSOCK);
421
test_noprivs@localhost
424
Grants for test_noprivs@localhost
425
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'
426
create trigger trg5a_1 before INSERT on t1 for each row
427
set @test_var = new.f1;
428
set @test_var = 'before trig 3.5.3.8-1a';
431
before trig 3.5.3.8-1a
432
insert into t1 (f1) values ('insert 3.5.3.8-1a');
433
ERROR 42000: SELECT command denied to user 'test_noprivs'@'localhost' for column 'f1' in table 't1'
436
before trig 3.5.3.8-1a
437
drop trigger trg5a_1;
441
test_yesprivs@localhost
443
Grants for test_yesprivs@localhost
444
GRANT SELECT, TRIGGER ON *.* TO 'test_yesprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
445
create trigger trg5a_2 before INSERT on t1 for each row
446
set @test_var= new.f1;
447
set @test_var= 'before trig 3.5.3.8-2a';
450
before trig 3.5.3.8-2a
451
insert into t1 (f1) values ('insert 3.5.3.8-2a');
455
drop trigger trg5a_2;
459
revoke ALL PRIVILEGES, GRANT OPTION FROM test_noprivs@localhost;
460
grant TRIGGER on *.* to test_noprivs@localhost;
461
grant ALL on priv_db.* to test_noprivs@localhost;
462
revoke SELECT on priv_db.* from test_noprivs@localhost;
463
show grants for test_noprivs@localhost;
464
Grants for test_noprivs@localhost
465
GRANT TRIGGER ON *.* TO 'test_noprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
466
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'
467
revoke ALL PRIVILEGES, GRANT OPTION FROM test_yesprivs@localhost;
468
grant TRIGGER on *.* to test_yesprivs@localhost;
469
grant SELECT on priv_db.* to test_yesprivs@localhost;
470
show grants for test_yesprivs@localhost;
471
Grants for test_yesprivs@localhost
472
GRANT TRIGGER ON *.* TO 'test_yesprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
473
GRANT SELECT ON `priv_db`.* TO 'test_yesprivs'@'localhost'
474
connect(localhost,test_noprivs,PWD,test,MASTER_MYPORT,MASTER_MYSOCK);
475
connect(localhost,test_yesprivs,PWD,test,MASTER_MYPORT,MASTER_MYSOCK);
477
Grants for test_noprivs@localhost
478
GRANT TRIGGER ON *.* TO 'test_noprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
479
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'
481
create trigger trg5b_1 before UPDATE on t1 for each row
482
set @test_var= new.f1;
483
set @test_var= 'before trig 3.5.3.8-1b';
484
insert into t1 (f1) values ('insert 3.5.3.8-1b');
487
before trig 3.5.3.8-1b
488
update t1 set f1= 'update 3.5.3.8-1b' where f1 = 'insert 3.5.3.8-1b';
489
ERROR 42000: SELECT command denied to user 'test_noprivs'@'localhost' for column 'f1' in table 't1'
492
before trig 3.5.3.8-1b
493
drop trigger trg5b_1;
495
Grants for test_yesprivs@localhost
496
GRANT TRIGGER ON *.* TO 'test_yesprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
497
GRANT SELECT ON `priv_db`.* TO 'test_yesprivs'@'localhost'
499
create trigger trg5b_2 before UPDATE on t1 for each row
500
set @test_var= new.f1;
501
set @test_var= 'before trig 3.5.3.8-2b';
502
insert into t1 (f1) values ('insert 3.5.3.8-2b');
505
before trig 3.5.3.8-2b
506
update t1 set f1= 'update 3.5.3.8-2b' where f1 = 'insert 3.5.3.8-2b';
510
drop trigger trg5b_2;
514
revoke ALL PRIVILEGES, GRANT OPTION FROM test_noprivs@localhost;
515
grant TRIGGER on *.* to test_noprivs@localhost;
516
grant ALL on priv_db.t1 to test_noprivs@localhost;
517
revoke SELECT on priv_db.t1 from test_noprivs@localhost;
518
show grants for test_noprivs@localhost;
519
Grants for test_noprivs@localhost
520
GRANT TRIGGER ON *.* TO 'test_noprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
521
GRANT INSERT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER, CREATE VIEW, SHOW VIEW, TRIGGER ON `priv_db`.`t1` TO 'test_noprivs'@'localhost'
522
revoke ALL PRIVILEGES, GRANT OPTION FROM test_yesprivs@localhost;
523
grant TRIGGER on *.* to test_yesprivs@localhost;
524
grant SELECT on priv_db.t1 to test_yesprivs@localhost;
525
show grants for test_yesprivs@localhost;
526
Grants for test_yesprivs@localhost
527
GRANT TRIGGER ON *.* TO 'test_yesprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
528
GRANT SELECT ON `priv_db`.`t1` TO 'test_yesprivs'@'localhost'
529
connect(localhost,test_noprivs,PWD,test,MASTER_MYPORT,MASTER_MYSOCK);
530
connect(localhost,test_yesprivs,PWD,test,MASTER_MYPORT,MASTER_MYSOCK);
532
Grants for test_noprivs@localhost
533
GRANT TRIGGER ON *.* TO 'test_noprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
534
GRANT INSERT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER, CREATE VIEW, SHOW VIEW, TRIGGER ON `priv_db`.`t1` TO 'test_noprivs'@'localhost'
536
create trigger trg5c_1 before INSERT on t1 for each row
537
set @test_var= new.f1;
538
set @test_var= 'before trig 3.5.3.8-1c';
539
insert into t1 (f1) values ('insert 3.5.3.8-1c');
540
ERROR 42000: SELECT command denied to user 'test_noprivs'@'localhost' for column 'f1' in table 't1'
543
before trig 3.5.3.8-1c
544
drop trigger trg5c_1;
546
Grants for test_yesprivs@localhost
547
GRANT TRIGGER ON *.* TO 'test_yesprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
548
GRANT SELECT ON `priv_db`.`t1` TO 'test_yesprivs'@'localhost'
550
create trigger trg5c_2 before INSERT on t1 for each row
551
set @test_var= new.f1;
552
set @test_var='before trig 3.5.3.8-2c';
553
insert into t1 (f1) values ('insert 3.5.3.8-2c');
557
drop trigger trg5c_2;
561
revoke ALL PRIVILEGES, GRANT OPTION FROM test_noprivs@localhost;
562
grant TRIGGER on *.* to test_noprivs@localhost;
563
grant UPDATE (f1), INSERT (f1) on priv_db.t1 to test_noprivs@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
revoke ALL PRIVILEGES, GRANT OPTION FROM test_yesprivs@localhost;
569
grant TRIGGER on *.* to test_yesprivs@localhost;
570
grant SELECT (f1) on priv_db.t1 to test_yesprivs@localhost;
571
show grants for test_noprivs@localhost;
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'
575
connect(localhost,test_noprivs,PWD,test,MASTER_MYPORT,MASTER_MYSOCK);
576
connect(localhost,test_yesprivs,PWD,test,MASTER_MYPORT,MASTER_MYSOCK);
578
Grants for test_noprivs@localhost
579
GRANT TRIGGER ON *.* TO 'test_noprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
580
GRANT INSERT (f1), UPDATE (f1) ON `priv_db`.`t1` TO 'test_noprivs'@'localhost'
582
create trigger trg5d_1 before INSERT on t1 for each row
583
set @test_var= new.f1;
584
set @test_var='before trig 3.5.3.8-1d';
585
insert into t1 (f1) values ('insert 3.5.3.8-1d');
586
ERROR 42000: SELECT command denied to user 'test_noprivs'@'localhost' for column 'f1' in table 't1'
589
before trig 3.5.3.8-1d
590
drop trigger trg5d_1;
592
Grants for test_yesprivs@localhost
593
GRANT TRIGGER ON *.* TO 'test_yesprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
594
GRANT SELECT (f1) ON `priv_db`.`t1` TO 'test_yesprivs'@'localhost'
596
create trigger trg5d_2 before INSERT on t1 for each row
597
set @test_var= new.f1;
598
set @test_var='before trig 3.5.3.8-2d';
599
insert into t1 (f1) values ('insert 3.5.3.8-2d');
603
drop trigger trg5d_2;
608
drop table if exists t1;
609
drop table if exists t2;
610
create table t1 (f1 int) engine= innodb;
611
create table t2 (f2 int) engine= innodb;
612
revoke ALL PRIVILEGES, GRANT OPTION FROM test_yesprivs@localhost;
613
grant TRIGGER on *.* to test_yesprivs@localhost;
614
grant SELECT, UPDATE on priv_db.t1 to test_yesprivs@localhost;
615
grant SELECT on priv_db.t2 to test_yesprivs@localhost;
616
show grants for test_yesprivs@localhost;
617
Grants for test_yesprivs@localhost
618
GRANT TRIGGER ON *.* TO 'test_yesprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
619
GRANT SELECT ON `priv_db`.`t2` TO 'test_yesprivs'@'localhost'
620
GRANT SELECT, UPDATE ON `priv_db`.`t1` TO 'test_yesprivs'@'localhost'
621
connect(localhost,test_yesprivs,PWD,test,MASTER_MYPORT,MASTER_MYSOCK);
624
test_yesprivs@localhost
626
create trigger trg1 before insert on t1 for each row
627
insert into t2 values (new.f1);
629
insert into t1 (f1) values (4);
630
ERROR 42000: INSERT command denied to user 'test_yesprivs'@'localhost' for table 't2'
631
revoke SELECT on priv_db.t2 from test_yesprivs@localhost;
632
grant INSERT on priv_db.t2 to test_yesprivs@localhost;
633
insert into t1 (f1) values (4);
634
select f1 from t1 order by f1;
637
select f2 from t2 order by f2;
642
create trigger trg2 before insert on t1 for each row
643
update t2 set f2=new.f1-1;
645
insert into t1 (f1) values (2);
646
ERROR 42000: UPDATE command denied to user 'test_yesprivs'@'localhost' for table 't2'
647
revoke INSERT on priv_db.t2 from test_yesprivs@localhost;
648
grant UPDATE on priv_db.t2 to test_yesprivs@localhost;
649
insert into t1 (f1) values (2);
650
select f1 from t1 order by f1;
654
select f2 from t2 order by f2;
659
create trigger trg3 before insert on t1 for each row
660
select f2 into @aaa from t2 where f2=new.f1;
662
insert into t1 (f1) values (1);
663
ERROR 42000: SELECT command denied to user 'test_yesprivs'@'localhost' for table 't2'
664
revoke UPDATE on priv_db.t2 from test_yesprivs@localhost;
665
grant SELECT on priv_db.t2 to test_yesprivs@localhost;
666
insert into t1 (f1) values (1);
667
select f1 from t1 order by f1;
672
select f2 from t2 order by f2;
680
create trigger trg4 before insert on t1 for each row
683
insert into t1 (f1) values (1);
684
ERROR 42000: DELETE command denied to user 'test_yesprivs'@'localhost' for table 't2'
685
revoke SELECT on priv_db.t2 from test_yesprivs@localhost;
686
grant DELETE on priv_db.t2 to test_yesprivs@localhost;
687
insert into t1 (f1) values (1);
688
select f1 from t1 order by f1;
694
select f2 from t2 order by f2;
696
drop database if exists priv_db;
697
drop user test_yesprivs@localhost;
698
drop user test_noprivs@localhost;
699
drop user test_noprivs;