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
Error 1478 Table storage engine 'ndbcluster' does not support the create option 'Binlog of table with BLOB attribute and no PK'
68
load data infile 'MYSQL_TEST_DIR/suite/funcs_1/data/ndb_tb3.txt' into table tb3 ;
72
drop database if exists priv_db;
73
create database priv_db;
75
create table t1 (f1 char(20)) engine= ndb;
76
create User test_noprivs@localhost;
77
set password for test_noprivs@localhost = password('PWD');
78
create User test_yesprivs@localhost;
79
set password for test_yesprivs@localhost = password('PWD');
83
revoke ALL PRIVILEGES, GRANT OPTION FROM test_noprivs@localhost;
84
grant ALL on *.* to test_noprivs@localhost;
85
revoke TRIGGER on *.* from test_noprivs@localhost;
86
show grants for test_noprivs@localhost;
87
Grants for test_noprivs@localhost
88
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'
89
revoke ALL PRIVILEGES, GRANT OPTION FROM test_yesprivs@localhost;
90
grant TRIGGER on *.* to test_yesprivs@localhost;
91
grant SELECT on priv_db.t1 to test_yesprivs@localhost;
92
show grants for test_yesprivs@localhost;
93
Grants for test_yesprivs@localhost
94
GRANT TRIGGER ON *.* TO 'test_yesprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
95
GRANT SELECT ON `priv_db`.`t1` TO 'test_yesprivs'@'localhost'
96
connect(localhost,test_noprivs,PWD,test,MASTER_MYPORT,MASTER_MYSOCK);
97
connect(localhost,test_yesprivs,PWD,test,MASTER_MYPORT,MASTER_MYSOCK);
103
test_noprivs@localhost
105
create trigger trg1_1 before INSERT on t1 for each row
106
set new.f1 = 'trig 3.5.3.2_1-no';
107
Got one of the listed errors
109
insert into t1 (f1) values ('insert 3.5.3.2-no');
110
select f1 from t1 order by f1;
115
test_yesprivs@localhost
117
create trigger trg1_2 before INSERT on t1 for each row
118
set new.f1 = 'trig 3.5.3.2_2-yes';
123
insert into t1 (f1) values ('insert 3.5.3.2-yes');
124
ERROR 42000: UPDATE command denied to user 'test_yesprivs'@'localhost' for column 'f1' in table 't1'
125
select f1 from t1 order by f1;
128
grant UPDATE on priv_db.t1 to test_yesprivs@localhost;
129
insert into t1 (f1) values ('insert 3.5.3.2-yes');
130
select f1 from t1 order by f1;
139
Got one of the listed errors
141
insert into t1 (f1) values ('insert 3.5.3.6-yes');
142
select f1 from t1 order by f1;
150
insert into t1 (f1) values ('insert 3.5.3.6-no');
151
select f1 from t1 order by f1;
161
revoke ALL PRIVILEGES, GRANT OPTION FROM test_noprivs@localhost;
162
grant ALL on *.* to test_noprivs@localhost;
163
revoke UPDATE on *.* from test_noprivs@localhost;
164
show grants for test_noprivs@localhost;
165
Grants for test_noprivs@localhost
166
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'
167
revoke ALL PRIVILEGES, GRANT OPTION FROM test_yesprivs@localhost;
168
grant TRIGGER, UPDATE on *.* to test_yesprivs@localhost;
169
show grants for test_yesprivs@localhost;
170
Grants for test_yesprivs@localhost
171
GRANT UPDATE, TRIGGER ON *.* TO 'test_yesprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
172
connect(localhost,test_noprivs,PWD,test,MASTER_MYPORT,MASTER_MYSOCK);
173
connect(localhost,test_yesprivs,PWD,test,MASTER_MYPORT,MASTER_MYSOCK);
176
test_noprivs@localhost
179
Grants for test_noprivs@localhost
180
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'
181
select f1 from t1 order by f1;
187
create trigger trg4a_1 before INSERT on t1 for each row
188
set new.f1 = 'trig 3.5.3.7-1a';
189
insert into t1 (f1) values ('insert 3.5.3.7-1a');
190
ERROR 42000: UPDATE command denied to user 'test_noprivs'@'localhost' for column 'f1' in table 't1'
191
select f1 from t1 order by f1;
197
drop trigger trg4a_1;
201
test_yesprivs@localhost
203
Grants for test_yesprivs@localhost
204
GRANT UPDATE, TRIGGER ON *.* TO 'test_yesprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
205
create trigger trg4a_2 before INSERT on t1 for each row
206
set new.f1 = 'trig 3.5.3.7-2a';
207
insert into t1 (f1) values ('insert 3.5.3.7-2b');
208
select f1 from t1 order by f1;
215
drop trigger trg4a_2;
219
revoke ALL PRIVILEGES, GRANT OPTION FROM test_noprivs@localhost;
220
grant TRIGGER on *.* to test_noprivs;
221
grant ALL on priv_db.* to test_noprivs@localhost;
222
revoke UPDATE on priv_db.* from test_noprivs@localhost;
223
show grants for test_noprivs;
224
Grants for test_noprivs@%
225
GRANT TRIGGER ON *.* TO 'test_noprivs'@'%'
226
revoke ALL PRIVILEGES, GRANT OPTION FROM test_yesprivs@localhost;
227
grant TRIGGER on *.* to test_yesprivs@localhost;
228
grant UPDATE on priv_db.* to test_yesprivs@localhost;
229
show grants for test_yesprivs@localhost;
230
Grants for test_yesprivs@localhost
231
GRANT TRIGGER ON *.* TO 'test_yesprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
232
GRANT UPDATE ON `priv_db`.* TO 'test_yesprivs'@'localhost'
233
connect(localhost,test_noprivs,PWD,test,MASTER_MYPORT,MASTER_MYSOCK);
234
connect(localhost,test_yesprivs,PWD,test,MASTER_MYPORT,MASTER_MYSOCK);
236
Grants for test_noprivs@localhost
237
GRANT USAGE ON *.* TO 'test_noprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
238
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'
240
create trigger trg4b_1 before UPDATE on t1 for each row
241
set new.f1 = 'trig 3.5.3.7-1b';
242
insert into t1 (f1) values ('insert 3.5.3.7-1b');
243
select f1 from t1 order by f1;
251
update t1 set f1 = 'update 3.5.3.7-1b' where f1 = 'insert 3.5.3.7-1b';
252
ERROR 42000: UPDATE command denied to user 'test_noprivs'@'localhost' for column 'f1' in table 't1'
253
select f1 from t1 order by f1;
261
drop trigger trg4b_1;
263
Grants for test_yesprivs@localhost
264
GRANT TRIGGER ON *.* TO 'test_yesprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
265
GRANT UPDATE ON `priv_db`.* TO 'test_yesprivs'@'localhost'
267
create trigger trg4b_2 before UPDATE on t1 for each row
268
set new.f1 = 'trig 3.5.3.7-2b';
269
insert into t1 (f1) values ('insert 3.5.3.7-2b');
270
select f1 from t1 order by f1;
279
update t1 set f1 = 'update 3.5.3.7-2b' where f1 = 'insert 3.5.3.7-2b';
280
select f1 from t1 order by f1;
289
drop trigger trg4b_2;
293
revoke ALL PRIVILEGES, GRANT OPTION FROM test_noprivs@localhost;
294
grant TRIGGER on *.* to test_noprivs@localhost;
295
grant ALL on priv_db.t1 to test_noprivs@localhost;
296
revoke UPDATE on priv_db.t1 from test_noprivs@localhost;
297
show grants for test_noprivs;
298
Grants for test_noprivs@%
299
GRANT TRIGGER ON *.* TO 'test_noprivs'@'%'
300
revoke ALL PRIVILEGES, GRANT OPTION FROM test_yesprivs@localhost;
301
grant TRIGGER on *.* to test_yesprivs@localhost;
302
grant UPDATE on priv_db.t1 to test_yesprivs@localhost;
303
show grants for test_yesprivs@localhost;
304
Grants for test_yesprivs@localhost
305
GRANT TRIGGER ON *.* TO 'test_yesprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
306
GRANT UPDATE ON `priv_db`.`t1` TO 'test_yesprivs'@'localhost'
307
connect(localhost,test_noprivs,PWD,test,MASTER_MYPORT,MASTER_MYSOCK);
308
connect(localhost,test_yesprivs,PWD,test,MASTER_MYPORT,MASTER_MYSOCK);
310
Grants for test_noprivs@localhost
311
GRANT TRIGGER ON *.* TO 'test_noprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
312
GRANT SELECT, INSERT, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER, CREATE VIEW, SHOW VIEW, TRIGGER ON `priv_db`.`t1` TO 'test_noprivs'@'localhost'
314
create trigger trg4c_1 before INSERT on t1 for each row
315
set new.f1 = 'trig 3.5.3.7-1c';
316
insert into t1 (f1) values ('insert 3.5.3.7-1c');
317
ERROR 42000: UPDATE command denied to user 'test_noprivs'@'localhost' for column 'f1' in table 't1'
318
select f1 from t1 order by f1;
327
drop trigger trg4c_1;
329
Grants for test_yesprivs@localhost
330
GRANT TRIGGER ON *.* TO 'test_yesprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
331
GRANT UPDATE ON `priv_db`.`t1` TO 'test_yesprivs'@'localhost'
333
create trigger trg4c_2 before INSERT on t1 for each row
334
set new.f1 = 'trig 3.5.3.7-2c';
335
insert into t1 (f1) values ('insert 3.5.3.7-2c');
336
select f1 from t1 order by f1;
346
drop trigger trg4c_2;
350
revoke ALL PRIVILEGES, GRANT OPTION FROM test_noprivs@localhost;
351
grant TRIGGER on *.* to test_noprivs@localhost;
352
grant SELECT (f1), INSERT (f1) on priv_db.t1 to test_noprivs@localhost;
353
show grants for test_noprivs;
354
Grants for test_noprivs@%
355
GRANT TRIGGER ON *.* TO 'test_noprivs'@'%'
356
revoke ALL PRIVILEGES, GRANT OPTION FROM test_yesprivs@localhost;
357
grant TRIGGER on *.* to test_yesprivs@localhost;
358
grant UPDATE (f1) on priv_db.t1 to test_yesprivs@localhost;
359
show grants for test_noprivs;
360
Grants for test_noprivs@%
361
GRANT TRIGGER ON *.* TO 'test_noprivs'@'%'
362
connect(localhost,test_noprivs,PWD,test,MASTER_MYPORT,MASTER_MYSOCK);
363
connect(localhost,test_yesprivs,PWD,test,MASTER_MYPORT,MASTER_MYSOCK);
365
Grants for test_noprivs@localhost
366
GRANT TRIGGER ON *.* TO 'test_noprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
367
GRANT SELECT (f1), INSERT (f1) ON `priv_db`.`t1` TO 'test_noprivs'@'localhost'
369
create trigger trg4d_1 before INSERT on t1 for each row
370
set new.f1 = 'trig 3.5.3.7-1d';
371
insert into t1 (f1) values ('insert 3.5.3.7-1d');
372
ERROR 42000: UPDATE command denied to user 'test_noprivs'@'localhost' for column 'f1' in table 't1'
373
select f1 from t1 order by f1;
383
drop trigger trg4d_1;
385
Grants for test_yesprivs@localhost
386
GRANT TRIGGER ON *.* TO 'test_yesprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
387
GRANT UPDATE (f1) ON `priv_db`.`t1` TO 'test_yesprivs'@'localhost'
389
create trigger trg4d_2 before INSERT on t1 for each row
390
set new.f1 = 'trig 3.5.3.7-2d';
391
insert into t1 (f1) values ('insert 3.5.3.7-2d');
392
select f1 from t1 order by f1;
403
drop trigger trg4d_2;
407
revoke ALL PRIVILEGES, GRANT OPTION FROM test_noprivs@localhost;
408
grant ALL on *.* to test_noprivs@localhost;
409
revoke SELECT on *.* from test_noprivs@localhost;
410
show grants for test_noprivs@localhost;
411
Grants for test_noprivs@localhost
412
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'
413
revoke ALL PRIVILEGES, GRANT OPTION FROM test_yesprivs@localhost;
414
grant TRIGGER, SELECT on *.* to test_yesprivs@localhost;
415
show grants for test_yesprivs@localhost;
416
Grants for test_yesprivs@localhost
417
GRANT SELECT, TRIGGER ON *.* TO 'test_yesprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
418
connect(localhost,test_noprivs,PWD,test,MASTER_MYPORT,MASTER_MYSOCK);
419
connect(localhost,test_yesprivs,PWD,test,MASTER_MYPORT,MASTER_MYSOCK);
422
test_noprivs@localhost
425
Grants for test_noprivs@localhost
426
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'
427
create trigger trg5a_1 before INSERT on t1 for each row
428
set @test_var = new.f1;
429
set @test_var = 'before trig 3.5.3.8-1a';
432
before trig 3.5.3.8-1a
433
insert into t1 (f1) values ('insert 3.5.3.8-1a');
434
ERROR 42000: SELECT command denied to user 'test_noprivs'@'localhost' for column 'f1' in table 't1'
437
before trig 3.5.3.8-1a
438
drop trigger trg5a_1;
442
test_yesprivs@localhost
444
Grants for test_yesprivs@localhost
445
GRANT SELECT, TRIGGER ON *.* TO 'test_yesprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
446
create trigger trg5a_2 before INSERT on t1 for each row
447
set @test_var= new.f1;
448
set @test_var= 'before trig 3.5.3.8-2a';
451
before trig 3.5.3.8-2a
452
insert into t1 (f1) values ('insert 3.5.3.8-2a');
456
drop trigger trg5a_2;
460
revoke ALL PRIVILEGES, GRANT OPTION FROM test_noprivs@localhost;
461
grant TRIGGER on *.* to test_noprivs@localhost;
462
grant ALL on priv_db.* to test_noprivs@localhost;
463
revoke SELECT on priv_db.* from test_noprivs@localhost;
464
show grants for test_noprivs@localhost;
465
Grants for test_noprivs@localhost
466
GRANT TRIGGER ON *.* TO 'test_noprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
467
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'
468
revoke ALL PRIVILEGES, GRANT OPTION FROM test_yesprivs@localhost;
469
grant TRIGGER on *.* to test_yesprivs@localhost;
470
grant SELECT on priv_db.* to test_yesprivs@localhost;
471
show grants for test_yesprivs@localhost;
472
Grants for test_yesprivs@localhost
473
GRANT TRIGGER ON *.* TO 'test_yesprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
474
GRANT SELECT ON `priv_db`.* TO 'test_yesprivs'@'localhost'
475
connect(localhost,test_noprivs,PWD,test,MASTER_MYPORT,MASTER_MYSOCK);
476
connect(localhost,test_yesprivs,PWD,test,MASTER_MYPORT,MASTER_MYSOCK);
478
Grants for test_noprivs@localhost
479
GRANT TRIGGER ON *.* TO 'test_noprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
480
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'
482
create trigger trg5b_1 before UPDATE on t1 for each row
483
set @test_var= new.f1;
484
set @test_var= 'before trig 3.5.3.8-1b';
485
insert into t1 (f1) values ('insert 3.5.3.8-1b');
488
before trig 3.5.3.8-1b
489
update t1 set f1= 'update 3.5.3.8-1b' where f1 = 'insert 3.5.3.8-1b';
490
ERROR 42000: SELECT command denied to user 'test_noprivs'@'localhost' for column 'f1' in table 't1'
493
before trig 3.5.3.8-1b
494
drop trigger trg5b_1;
496
Grants for test_yesprivs@localhost
497
GRANT TRIGGER ON *.* TO 'test_yesprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
498
GRANT SELECT ON `priv_db`.* TO 'test_yesprivs'@'localhost'
500
create trigger trg5b_2 before UPDATE on t1 for each row
501
set @test_var= new.f1;
502
set @test_var= 'before trig 3.5.3.8-2b';
503
insert into t1 (f1) values ('insert 3.5.3.8-2b');
506
before trig 3.5.3.8-2b
507
update t1 set f1= 'update 3.5.3.8-2b' where f1 = 'insert 3.5.3.8-2b';
511
drop trigger trg5b_2;
515
revoke ALL PRIVILEGES, GRANT OPTION FROM test_noprivs@localhost;
516
grant TRIGGER on *.* to test_noprivs@localhost;
517
grant ALL on priv_db.t1 to test_noprivs@localhost;
518
revoke SELECT on priv_db.t1 from test_noprivs@localhost;
519
show grants for test_noprivs@localhost;
520
Grants for test_noprivs@localhost
521
GRANT TRIGGER ON *.* TO 'test_noprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
522
GRANT INSERT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER, CREATE VIEW, SHOW VIEW, TRIGGER ON `priv_db`.`t1` TO 'test_noprivs'@'localhost'
523
revoke ALL PRIVILEGES, GRANT OPTION FROM test_yesprivs@localhost;
524
grant TRIGGER on *.* to test_yesprivs@localhost;
525
grant SELECT on priv_db.t1 to test_yesprivs@localhost;
526
show grants for test_yesprivs@localhost;
527
Grants for test_yesprivs@localhost
528
GRANT TRIGGER ON *.* TO 'test_yesprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
529
GRANT SELECT ON `priv_db`.`t1` TO 'test_yesprivs'@'localhost'
530
connect(localhost,test_noprivs,PWD,test,MASTER_MYPORT,MASTER_MYSOCK);
531
connect(localhost,test_yesprivs,PWD,test,MASTER_MYPORT,MASTER_MYSOCK);
533
Grants for test_noprivs@localhost
534
GRANT TRIGGER ON *.* TO 'test_noprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
535
GRANT INSERT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER, CREATE VIEW, SHOW VIEW, TRIGGER ON `priv_db`.`t1` TO 'test_noprivs'@'localhost'
537
create trigger trg5c_1 before INSERT on t1 for each row
538
set @test_var= new.f1;
539
set @test_var= 'before trig 3.5.3.8-1c';
540
insert into t1 (f1) values ('insert 3.5.3.8-1c');
541
ERROR 42000: SELECT command denied to user 'test_noprivs'@'localhost' for column 'f1' in table 't1'
544
before trig 3.5.3.8-1c
545
drop trigger trg5c_1;
547
Grants for test_yesprivs@localhost
548
GRANT TRIGGER ON *.* TO 'test_yesprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
549
GRANT SELECT ON `priv_db`.`t1` TO 'test_yesprivs'@'localhost'
551
create trigger trg5c_2 before INSERT on t1 for each row
552
set @test_var= new.f1;
553
set @test_var='before trig 3.5.3.8-2c';
554
insert into t1 (f1) values ('insert 3.5.3.8-2c');
558
drop trigger trg5c_2;
562
revoke ALL PRIVILEGES, GRANT OPTION FROM test_noprivs@localhost;
563
grant TRIGGER on *.* to test_noprivs@localhost;
564
grant UPDATE (f1), INSERT (f1) on priv_db.t1 to test_noprivs@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
revoke ALL PRIVILEGES, GRANT OPTION FROM test_yesprivs@localhost;
570
grant TRIGGER on *.* to test_yesprivs@localhost;
571
grant SELECT (f1) on priv_db.t1 to test_yesprivs@localhost;
572
show grants for test_noprivs@localhost;
573
Grants for test_noprivs@localhost
574
GRANT TRIGGER ON *.* TO 'test_noprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
575
GRANT INSERT (f1), UPDATE (f1) ON `priv_db`.`t1` TO 'test_noprivs'@'localhost'
576
connect(localhost,test_noprivs,PWD,test,MASTER_MYPORT,MASTER_MYSOCK);
577
connect(localhost,test_yesprivs,PWD,test,MASTER_MYPORT,MASTER_MYSOCK);
579
Grants for test_noprivs@localhost
580
GRANT TRIGGER ON *.* TO 'test_noprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
581
GRANT INSERT (f1), UPDATE (f1) ON `priv_db`.`t1` TO 'test_noprivs'@'localhost'
583
create trigger trg5d_1 before INSERT on t1 for each row
584
set @test_var= new.f1;
585
set @test_var='before trig 3.5.3.8-1d';
586
insert into t1 (f1) values ('insert 3.5.3.8-1d');
587
ERROR 42000: SELECT command denied to user 'test_noprivs'@'localhost' for column 'f1' in table 't1'
590
before trig 3.5.3.8-1d
591
drop trigger trg5d_1;
593
Grants for test_yesprivs@localhost
594
GRANT TRIGGER ON *.* TO 'test_yesprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
595
GRANT SELECT (f1) ON `priv_db`.`t1` TO 'test_yesprivs'@'localhost'
597
create trigger trg5d_2 before INSERT on t1 for each row
598
set @test_var= new.f1;
599
set @test_var='before trig 3.5.3.8-2d';
600
insert into t1 (f1) values ('insert 3.5.3.8-2d');
604
drop trigger trg5d_2;
609
drop table if exists t1;
610
drop table if exists t2;
611
create table t1 (f1 int) engine= ndb;
612
create table t2 (f2 int) engine= ndb;
613
revoke ALL PRIVILEGES, GRANT OPTION FROM test_yesprivs@localhost;
614
grant TRIGGER on *.* to test_yesprivs@localhost;
615
grant SELECT, UPDATE on priv_db.t1 to test_yesprivs@localhost;
616
grant SELECT on priv_db.t2 to test_yesprivs@localhost;
617
show grants for test_yesprivs@localhost;
618
Grants for test_yesprivs@localhost
619
GRANT TRIGGER ON *.* TO 'test_yesprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
620
GRANT SELECT ON `priv_db`.`t2` TO 'test_yesprivs'@'localhost'
621
GRANT SELECT, UPDATE ON `priv_db`.`t1` TO 'test_yesprivs'@'localhost'
622
connect(localhost,test_yesprivs,PWD,test,MASTER_MYPORT,MASTER_MYSOCK);
625
test_yesprivs@localhost
627
create trigger trg1 before insert on t1 for each row
628
insert into t2 values (new.f1);
630
insert into t1 (f1) values (4);
631
ERROR 42000: INSERT command denied to user 'test_yesprivs'@'localhost' for table 't2'
632
revoke SELECT on priv_db.t2 from test_yesprivs@localhost;
633
grant INSERT on priv_db.t2 to test_yesprivs@localhost;
634
insert into t1 (f1) values (4);
635
select f1 from t1 order by f1;
638
select f2 from t2 order by f2;
643
create trigger trg2 before insert on t1 for each row
644
update t2 set f2=new.f1-1;
646
insert into t1 (f1) values (2);
647
ERROR 42000: UPDATE command denied to user 'test_yesprivs'@'localhost' for table 't2'
648
revoke INSERT on priv_db.t2 from test_yesprivs@localhost;
649
grant UPDATE on priv_db.t2 to test_yesprivs@localhost;
650
insert into t1 (f1) values (2);
651
select f1 from t1 order by f1;
655
select f2 from t2 order by f2;
660
create trigger trg3 before insert on t1 for each row
661
select f2 into @aaa from t2 where f2=new.f1;
663
insert into t1 (f1) values (1);
664
ERROR 42000: SELECT command denied to user 'test_yesprivs'@'localhost' for table 't2'
665
revoke UPDATE on priv_db.t2 from test_yesprivs@localhost;
666
grant SELECT on priv_db.t2 to test_yesprivs@localhost;
667
insert into t1 (f1) values (1);
668
select f1 from t1 order by f1;
673
select f2 from t2 order by f2;
681
create trigger trg4 before insert on t1 for each row
684
insert into t1 (f1) values (1);
685
ERROR 42000: DELETE command denied to user 'test_yesprivs'@'localhost' for table 't2'
686
revoke SELECT on priv_db.t2 from test_yesprivs@localhost;
687
grant DELETE on priv_db.t2 to test_yesprivs@localhost;
688
insert into t1 (f1) values (1);
689
select f1 from t1 order by f1;
695
select f2 from t2 order by f2;
697
drop database if exists priv_db;
698
drop user test_yesprivs@localhost;
699
drop user test_noprivs@localhost;
700
drop user test_noprivs;