1
SET @NO_REFRESH = IF( '' = '', 0, 1);
3
set @@global.max_heap_table_size = 4294967295;
4
set @@session.max_heap_table_size = 4294967295;
5
drop table if exists tb3;
7
f118 char not null DEFAULT 'a',
8
f119 char binary not null DEFAULT b'101',
9
f120 char ascii not null DEFAULT b'101',
12
f129 binary not null DEFAULT b'101',
13
f130 tinyint not null DEFAULT 99,
14
f131 tinyint unsigned not null DEFAULT 99,
15
f132 tinyint zerofill not null DEFAULT 99,
16
f133 tinyint unsigned zerofill not null DEFAULT 99,
17
f134 smallint not null DEFAULT 999,
18
f135 smallint unsigned not null DEFAULT 999,
19
f136 smallint zerofill not null DEFAULT 999,
20
f137 smallint unsigned zerofill not null DEFAULT 999,
21
f138 mediumint not null DEFAULT 9999,
22
f139 mediumint unsigned not null DEFAULT 9999,
23
f140 mediumint zerofill not null DEFAULT 9999,
24
f141 mediumint unsigned zerofill not null DEFAULT 9999,
25
f142 int not null DEFAULT 99999,
26
f143 int unsigned not null DEFAULT 99999,
27
f144 int zerofill not null DEFAULT 99999,
28
f145 int unsigned zerofill not null DEFAULT 99999,
29
f146 bigint not null DEFAULT 999999,
30
f147 bigint unsigned not null DEFAULT 999999,
31
f148 bigint zerofill not null DEFAULT 999999,
32
f149 bigint unsigned zerofill not null DEFAULT 999999,
33
f150 decimal not null DEFAULT 999.999,
34
f151 decimal unsigned not null DEFAULT 999.17,
35
f152 decimal zerofill not null DEFAULT 999.999,
36
f153 decimal unsigned zerofill,
39
f156 decimal (0) unsigned,
40
f157 decimal (64) unsigned,
41
f158 decimal (0) zerofill,
42
f159 decimal (64) zerofill,
43
f160 decimal (0) unsigned zerofill,
44
f161 decimal (64) unsigned zerofill,
47
f164 decimal (0,0) unsigned,
48
f165 decimal (63,30) unsigned,
49
f166 decimal (0,0) zerofill,
50
f167 decimal (63,30) zerofill,
51
f168 decimal (0,0) unsigned zerofill,
52
f169 decimal (63,30) unsigned zerofill,
54
f171 numeric unsigned,
55
f172 numeric zerofill,
56
f173 numeric unsigned zerofill,
61
Note 1265 Data truncated for column 'f150' at row 1
62
Note 1265 Data truncated for column 'f151' at row 1
63
Note 1265 Data truncated for column 'f152' at row 1
64
load data infile 'MYSQL_TEST_DIR/suite/funcs_1/data/memory_tb3.txt' into table tb3 ;
68
drop database if exists priv_db;
69
create database priv_db;
71
create table t1 (f1 char(20)) engine= memory;
72
create User test_noprivs@localhost;
73
set password for test_noprivs@localhost = password('PWD');
74
create User test_yesprivs@localhost;
75
set password for test_yesprivs@localhost = password('PWD');
79
revoke ALL PRIVILEGES, GRANT OPTION FROM test_noprivs@localhost;
80
grant ALL on *.* to test_noprivs@localhost;
81
revoke SUPER on *.* from test_noprivs@localhost;
82
show grants for test_noprivs@localhost;
83
Grants for test_noprivs@localhost
84
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER ON *.* TO 'test_noprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
85
revoke ALL PRIVILEGES, GRANT OPTION FROM test_yesprivs@localhost;
86
grant SUPER on *.* to test_yesprivs@localhost;
87
grant SELECT on priv_db.t1 to test_yesprivs@localhost;
88
show grants for test_yesprivs@localhost;
89
Grants for test_yesprivs@localhost
90
GRANT SUPER ON *.* TO 'test_yesprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
91
GRANT SELECT ON `priv_db`.`t1` TO 'test_yesprivs'@'localhost'
92
connect(localhost,test_noprivs,PWD,test,MASTER_MYPORT,MASTER_MYSOCK);
93
connect(localhost,test_yesprivs,PWD,test,MASTER_MYPORT,MASTER_MYSOCK);
99
test_noprivs@localhost
101
create trigger trg1_1 before INSERT on t1 for each row
102
set new.f1 = 'trig 3.5.3.2_1-no';
103
ERROR 42000: Access denied; you need the SUPER privilege for this operation
105
insert into t1 (f1) values ('insert 3.5.3.2-no');
111
test_yesprivs@localhost
113
create trigger trg1_2 before INSERT on t1 for each row
114
set new.f1 = 'trig 3.5.3.2_2-yes';
119
insert into t1 (f1) values ('insert 3.5.3.2-yes');
120
ERROR 42000: UPDATE command denied to user 'test_yesprivs'@'localhost' for column 'f1' in table 't1'
124
grant UPDATE on priv_db.t1 to test_yesprivs@localhost;
126
note: once 15166 is fixed a similar case for SELECT needs to be added
127
---------------------------------------------------------------------
128
insert into t1 (f1) values ('insert 3.5.3.2-yes');
138
ERROR 42000: Access denied; you need the SUPER privilege for this operation
140
insert into t1 (f1) values ('insert 3.5.3.6-yes');
149
insert into t1 (f1) values ('insert 3.5.3.6-no');
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 ON *.* TO 'test_noprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
166
revoke ALL PRIVILEGES, GRANT OPTION FROM test_yesprivs@localhost;
167
grant SUPER, UPDATE on *.* to test_yesprivs@localhost;
168
show grants for test_yesprivs@localhost;
169
Grants for test_yesprivs@localhost
170
GRANT UPDATE, SUPER 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 ON *.* TO 'test_noprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
187
Trigger create disabled - should fail - Bug 8884
188
------------------------------------------------
189
insert into t1 (f1) values ('insert 3.5.3.7-1a');
197
drop trigger trg4a_1;
201
test_yesprivs@localhost
203
Grants for test_yesprivs@localhost
204
GRANT UPDATE, SUPER 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';
208
SELECT priv added to bypass bug 15166
209
-------------------------------------
210
grant SELECT on *.* to test_yesprivs@localhost;
211
insert into t1 (f1) values ('insert 3.5.3.7-2b');
220
drop trigger trg4a_2;
224
revoke ALL PRIVILEGES, GRANT OPTION FROM test_noprivs@localhost;
225
grant SUPER on *.* to test_noprivs;
226
grant ALL on priv_db.* to test_noprivs@localhost;
227
revoke UPDATE on priv_db.* from test_noprivs@localhost;
228
show grants for test_noprivs;
229
Grants for test_noprivs@%
230
GRANT SUPER ON *.* TO 'test_noprivs'@'%'
231
revoke ALL PRIVILEGES, GRANT OPTION FROM test_yesprivs@localhost;
232
grant SUPER on *.* to test_yesprivs@localhost;
233
grant UPDATE on priv_db.* to test_yesprivs@localhost;
234
show grants for test_yesprivs@localhost;
235
Grants for test_yesprivs@localhost
236
GRANT SUPER ON *.* TO 'test_yesprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
237
GRANT UPDATE ON `priv_db`.* TO 'test_yesprivs'@'localhost'
238
connect(localhost,test_noprivs,PWD,test,MASTER_MYPORT,MASTER_MYSOCK);
239
connect(localhost,test_yesprivs,PWD,test,MASTER_MYPORT,MASTER_MYSOCK);
241
Grants for test_noprivs@localhost
242
GRANT USAGE ON *.* TO 'test_noprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
243
GRANT SELECT, INSERT, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE ON `priv_db`.* TO 'test_noprivs'@'localhost'
246
Trigger create disabled - should fail - Bug 8884
247
------------------------------------------------
248
insert into t1 (f1) values ('insert 3.5.3.7-1b');
258
update t1 set f1 = 'update 3.5.3.7-1b' where f1 = 'insert 3.5.3.7-1b';
268
drop trigger trg4b_1;
270
Grants for test_yesprivs@localhost
271
GRANT SUPER ON *.* TO 'test_yesprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
272
GRANT UPDATE ON `priv_db`.* TO 'test_yesprivs'@'localhost'
274
create trigger trg4b_2 before UPDATE on t1 for each row
275
set new.f1 = 'trig 3.5.3.7-2b';
277
SELECT priv added to bypass bug 15166
278
-------------------------------------
279
grant SELECT on priv_db.* to test_yesprivs@localhost;
280
insert into t1 (f1) values ('insert 3.5.3.7-2b');
291
update t1 set f1 = 'update 3.5.3.7-2b' where f1 = 'insert 3.5.3.7-2b';
302
drop trigger trg4b_2;
306
revoke ALL PRIVILEGES, GRANT OPTION FROM test_noprivs@localhost;
307
grant SUPER on *.* to test_noprivs@localhost;
308
grant ALL on priv_db.t1 to test_noprivs@localhost;
309
revoke UPDATE on priv_db.t1 from test_noprivs@localhost;
310
show grants for test_noprivs;
311
Grants for test_noprivs@%
312
GRANT SUPER ON *.* TO 'test_noprivs'@'%'
313
revoke ALL PRIVILEGES, GRANT OPTION FROM test_yesprivs@localhost;
314
grant SUPER on *.* to test_yesprivs@localhost;
315
grant UPDATE on priv_db.t1 to test_yesprivs@localhost;
316
show grants for test_yesprivs@localhost;
317
Grants for test_yesprivs@localhost
318
GRANT SUPER ON *.* TO 'test_yesprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
319
GRANT UPDATE ON `priv_db`.`t1` TO 'test_yesprivs'@'localhost'
320
connect(localhost,test_noprivs,PWD,test,MASTER_MYPORT,MASTER_MYSOCK);
321
connect(localhost,test_yesprivs,PWD,test,MASTER_MYPORT,MASTER_MYSOCK);
323
Grants for test_noprivs@localhost
324
GRANT SUPER ON *.* TO 'test_noprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
325
GRANT SELECT, INSERT, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER, CREATE VIEW, SHOW VIEW ON `priv_db`.`t1` TO 'test_noprivs'@'localhost'
328
Trigger create disabled - should fail - Bug 8884
329
------------------------------------------------
330
insert into t1 (f1) values ('insert 3.5.3.7-1c');
342
drop trigger trg4c_1;
344
Grants for test_yesprivs@localhost
345
GRANT SUPER ON *.* TO 'test_yesprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
346
GRANT UPDATE ON `priv_db`.`t1` TO 'test_yesprivs'@'localhost'
348
create trigger trg4c_2 before INSERT on t1 for each row
349
set new.f1 = 'trig 3.5.3.7-2c';
351
SELECT priv added to bypass bug 15166
352
-------------------------------------
353
grant SELECT on priv_db.t1 to test_yesprivs@localhost;
354
insert into t1 (f1) values ('insert 3.5.3.7-2c');
367
drop trigger trg4c_2;
371
revoke ALL PRIVILEGES, GRANT OPTION FROM test_noprivs@localhost;
372
grant SUPER on *.* to test_noprivs@localhost;
373
grant SELECT (f1), INSERT (f1) on priv_db.t1 to test_noprivs@localhost;
374
show grants for test_noprivs;
375
Grants for test_noprivs@%
376
GRANT SUPER ON *.* TO 'test_noprivs'@'%'
377
revoke ALL PRIVILEGES, GRANT OPTION FROM test_yesprivs@localhost;
378
grant SUPER on *.* to test_yesprivs@localhost;
379
grant UPDATE (f1) on priv_db.t1 to test_yesprivs@localhost;
380
show grants for test_noprivs;
381
Grants for test_noprivs@%
382
GRANT SUPER ON *.* TO 'test_noprivs'@'%'
383
connect(localhost,test_noprivs,PWD,test,MASTER_MYPORT,MASTER_MYSOCK);
384
connect(localhost,test_yesprivs,PWD,test,MASTER_MYPORT,MASTER_MYSOCK);
386
Grants for test_noprivs@localhost
387
GRANT SUPER ON *.* TO 'test_noprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
388
GRANT SELECT (f1), INSERT (f1) ON `priv_db`.`t1` TO 'test_noprivs'@'localhost'
391
Trigger create disabled - should fail - Bug 8884
392
------------------------------------------------
393
insert into t1 (f1) values ('insert 3.5.3.7-1d');
407
drop trigger trg4d_1;
409
Grants for test_yesprivs@localhost
410
GRANT SUPER ON *.* TO 'test_yesprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
411
GRANT UPDATE (f1) ON `priv_db`.`t1` TO 'test_yesprivs'@'localhost'
413
create trigger trg4d_2 before INSERT on t1 for each row
414
set new.f1 = 'trig 3.5.3.7-2d';
416
SELECT priv added to bypass bug 15166
417
-------------------------------------
418
grant SELECT (f1) on priv_db.t1 to test_yesprivs@localhost;
419
insert into t1 (f1) values ('insert 3.5.3.7-2d');
434
drop trigger trg4d_2;
438
revoke ALL PRIVILEGES, GRANT OPTION FROM test_noprivs@localhost;
439
grant ALL on *.* to test_noprivs@localhost;
440
revoke SELECT on *.* from test_noprivs@localhost;
441
show grants for test_noprivs@localhost;
442
Grants for test_noprivs@localhost
443
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 ON *.* TO 'test_noprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
444
revoke ALL PRIVILEGES, GRANT OPTION FROM test_yesprivs@localhost;
445
grant SUPER, SELECT on *.* to test_yesprivs@localhost;
446
show grants for test_yesprivs@localhost;
447
Grants for test_yesprivs@localhost
448
GRANT SELECT, SUPER ON *.* TO 'test_yesprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
449
connect(localhost,test_noprivs,PWD,test,MASTER_MYPORT,MASTER_MYSOCK);
450
connect(localhost,test_yesprivs,PWD,test,MASTER_MYPORT,MASTER_MYSOCK);
453
test_noprivs@localhost
456
Grants for test_noprivs@localhost
457
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 ON *.* TO 'test_noprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
459
Trigger create disabled - should fail - Bug 8887
460
------------------------------------------------
461
set @test_var = 'before trig 3.5.3.8-1a';
464
before trig 3.5.3.8-1a
465
insert into t1 (f1) values ('insert 3.5.3.8-1a');
468
before trig 3.5.3.8-1a
469
drop trigger trg5a_1;
473
test_yesprivs@localhost
475
Grants for test_yesprivs@localhost
476
GRANT SELECT, SUPER ON *.* TO 'test_yesprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
477
create trigger trg5a_2 before INSERT on t1 for each row
478
set @test_var= new.f1;
479
set @test_var= 'before trig 3.5.3.8-2a';
482
before trig 3.5.3.8-2a
484
UPDATE priv added to bypass bug 15166
485
-------------------------------------
486
grant UPDATE on *.* to test_yesprivs@localhost;
487
insert into t1 (f1) values ('insert 3.5.3.8-2a');
491
drop trigger trg5a_2;
495
revoke ALL PRIVILEGES, GRANT OPTION FROM test_noprivs@localhost;
496
grant SUPER on *.* to test_noprivs@localhost;
497
grant ALL on priv_db.* to test_noprivs@localhost;
498
revoke SELECT on priv_db.* from test_noprivs@localhost;
499
show grants for test_noprivs@localhost;
500
Grants for test_noprivs@localhost
501
GRANT SUPER ON *.* TO 'test_noprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
502
GRANT INSERT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE ON `priv_db`.* TO 'test_noprivs'@'localhost'
503
revoke ALL PRIVILEGES, GRANT OPTION FROM test_yesprivs@localhost;
504
grant SUPER on *.* to test_yesprivs@localhost;
505
grant SELECT on priv_db.* to test_yesprivs@localhost;
506
show grants for test_yesprivs@localhost;
507
Grants for test_yesprivs@localhost
508
GRANT SUPER ON *.* TO 'test_yesprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
509
GRANT SELECT ON `priv_db`.* TO 'test_yesprivs'@'localhost'
510
connect(localhost,test_noprivs,PWD,test,MASTER_MYPORT,MASTER_MYSOCK);
511
connect(localhost,test_yesprivs,PWD,test,MASTER_MYPORT,MASTER_MYSOCK);
513
Grants for test_noprivs@localhost
514
GRANT SUPER ON *.* TO 'test_noprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
515
GRANT INSERT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE ON `priv_db`.* TO 'test_noprivs'@'localhost'
518
Trigger create disabled - should fail - Bug 8887
519
------------------------------------------------
520
set @test_var= 'before trig 3.5.3.8-1b';
521
insert into t1 (f1) values ('insert 3.5.3.8-1b');
524
before trig 3.5.3.8-1b
525
update t1 set f1= 'update 3.5.3.8-1b' where f1 = 'insert 3.5.3.8-1b';
528
before trig 3.5.3.8-1b
529
drop trigger trg5b_1;
531
Grants for test_yesprivs@localhost
532
GRANT SUPER ON *.* TO 'test_yesprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
533
GRANT SELECT ON `priv_db`.* TO 'test_yesprivs'@'localhost'
535
create trigger trg5b_2 before UPDATE on t1 for each row
536
set @test_var= new.f1;
537
set @test_var= 'before trig 3.5.3.8-2b';
538
insert into t1 (f1) values ('insert 3.5.3.8-2b');
541
before trig 3.5.3.8-2b
543
UPDATE priv added to bypass bug 15166
544
-------------------------------------
545
grant UPDATE on priv_db.* to test_yesprivs@localhost;
546
update t1 set f1= 'update 3.5.3.8-2b' where f1 = 'insert 3.5.3.8-2b';
550
drop trigger trg5b_2;
554
revoke ALL PRIVILEGES, GRANT OPTION FROM test_noprivs@localhost;
555
grant SUPER on *.* to test_noprivs@localhost;
556
grant ALL on priv_db.t1 to test_noprivs@localhost;
557
revoke SELECT on priv_db.t1 from test_noprivs@localhost;
558
show grants for test_noprivs@localhost;
559
Grants for test_noprivs@localhost
560
GRANT SUPER ON *.* TO 'test_noprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
561
GRANT INSERT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER, CREATE VIEW, SHOW VIEW ON `priv_db`.`t1` TO 'test_noprivs'@'localhost'
562
revoke ALL PRIVILEGES, GRANT OPTION FROM test_yesprivs@localhost;
563
grant SUPER on *.* to test_yesprivs@localhost;
564
grant SELECT on priv_db.t1 to test_yesprivs@localhost;
565
show grants for test_yesprivs@localhost;
566
Grants for test_yesprivs@localhost
567
GRANT SUPER ON *.* TO 'test_yesprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
568
GRANT SELECT ON `priv_db`.`t1` TO 'test_yesprivs'@'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 SUPER ON *.* TO 'test_noprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
574
GRANT INSERT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER, CREATE VIEW, SHOW VIEW ON `priv_db`.`t1` TO 'test_noprivs'@'localhost'
577
Trigger create disabled - should fail - Bug 8887
578
------------------------------------------------
579
set @test_var= 'before trig 3.5.3.8-1c';
580
insert into t1 (f1) values ('insert 3.5.3.8-1c');
583
before trig 3.5.3.8-1c
584
drop trigger trg5c_1;
586
Grants for test_yesprivs@localhost
587
GRANT SUPER ON *.* TO 'test_yesprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
588
GRANT SELECT ON `priv_db`.`t1` TO 'test_yesprivs'@'localhost'
590
create trigger trg5c_2 before INSERT on t1 for each row
591
set @test_var= new.f1;
592
set @test_var='before trig 3.5.3.8-2c';
594
UPDATE priv added to bypass bug 15166
595
-------------------------------------
596
grant UPDATE on priv_db.t1 to test_yesprivs@localhost;
597
insert into t1 (f1) values ('insert 3.5.3.8-2c');
601
drop trigger trg5c_2;
605
revoke ALL PRIVILEGES, GRANT OPTION FROM test_noprivs@localhost;
606
grant SUPER on *.* to test_noprivs@localhost;
607
grant UPDATE (f1), INSERT (f1) on priv_db.t1 to test_noprivs@localhost;
608
show grants for test_noprivs@localhost;
609
Grants for test_noprivs@localhost
610
GRANT SUPER ON *.* TO 'test_noprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
611
GRANT INSERT (f1), UPDATE (f1) ON `priv_db`.`t1` TO 'test_noprivs'@'localhost'
612
revoke ALL PRIVILEGES, GRANT OPTION FROM test_yesprivs@localhost;
613
grant SUPER on *.* to test_yesprivs@localhost;
614
grant SELECT (f1) on priv_db.t1 to test_yesprivs@localhost;
615
show grants for test_noprivs@localhost;
616
Grants for test_noprivs@localhost
617
GRANT SUPER ON *.* TO 'test_noprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
618
GRANT INSERT (f1), UPDATE (f1) ON `priv_db`.`t1` TO 'test_noprivs'@'localhost'
619
connect(localhost,test_noprivs,PWD,test,MASTER_MYPORT,MASTER_MYSOCK);
620
connect(localhost,test_yesprivs,PWD,test,MASTER_MYPORT,MASTER_MYSOCK);
622
Grants for test_noprivs@localhost
623
GRANT SUPER ON *.* TO 'test_noprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
624
GRANT INSERT (f1), UPDATE (f1) ON `priv_db`.`t1` TO 'test_noprivs'@'localhost'
627
Trigger create disabled - should fail - Bug 8887
628
------------------------------------------------
629
set @test_var='before trig 3.5.3.8-1d';
630
insert into t1 (f1) values ('insert 3.5.3.8-1d');
633
before trig 3.5.3.8-1d
634
drop trigger trg5d_1;
636
Grants for test_yesprivs@localhost
637
GRANT SUPER ON *.* TO 'test_yesprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
638
GRANT SELECT (f1) ON `priv_db`.`t1` TO 'test_yesprivs'@'localhost'
640
create trigger trg5d_2 before INSERT on t1 for each row
641
set @test_var= new.f1;
642
set @test_var='before trig 3.5.3.8-2d';
644
UPDATE priv added to bypass bug 15166
645
-------------------------------------
646
grant UPDATE (f1) on priv_db.t1 to test_yesprivs@localhost;
647
insert into t1 (f1) values ('insert 3.5.3.8-2d');
651
drop trigger trg5d_2;
656
drop table if exists t1;
657
drop table if exists t2;
658
create table t1 (f1 int) engine= memory;
659
create table t2 (f2 int) engine= memory;
660
revoke ALL PRIVILEGES, GRANT OPTION FROM test_yesprivs@localhost;
661
grant SUPER on *.* to test_yesprivs@localhost;
662
grant SELECT, UPDATE on priv_db.t1 to test_yesprivs@localhost;
663
grant SELECT on priv_db.t2 to test_yesprivs@localhost;
664
show grants for test_yesprivs@localhost;
665
Grants for test_yesprivs@localhost
666
GRANT SUPER ON *.* TO 'test_yesprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
667
GRANT SELECT ON `priv_db`.`t2` TO 'test_yesprivs'@'localhost'
668
GRANT SELECT, UPDATE ON `priv_db`.`t1` TO 'test_yesprivs'@'localhost'
669
connect(localhost,test_yesprivs,PWD,test,MASTER_MYPORT,MASTER_MYSOCK);
672
test_yesprivs@localhost
674
create trigger trg1 before insert on t1 for each row
675
insert into t2 values (new.f1);
677
insert into t1 (f1) values (4);
678
ERROR 42000: INSERT command denied to user 'test_yesprivs'@'localhost' for table 't2'
679
revoke SELECT on priv_db.t2 from test_yesprivs@localhost;
680
grant INSERT on priv_db.t2 to test_yesprivs@localhost;
681
insert into t1 (f1) values (4);
690
create trigger trg2 before insert on t1 for each row
691
update t2 set f2=new.f1-1;
693
insert into t1 (f1) values (2);
694
ERROR 42000: UPDATE command denied to user 'test_yesprivs'@'localhost' for table 't2'
695
revoke INSERT on priv_db.t2 from test_yesprivs@localhost;
696
grant UPDATE on priv_db.t2 to test_yesprivs@localhost;
697
insert into t1 (f1) values (2);
707
create trigger trg3 before insert on t1 for each row
708
select f2 into @aaa from t2 where f2=new.f1;
710
insert into t1 (f1) values (1);
711
ERROR 42000: SELECT command denied to user 'test_yesprivs'@'localhost' for table 't2'
712
revoke UPDATE on priv_db.t2 from test_yesprivs@localhost;
713
grant SELECT on priv_db.t2 to test_yesprivs@localhost;
714
insert into t1 (f1) values (1);
728
create trigger trg4 before insert on t1 for each row
731
insert into t1 (f1) values (1);
732
ERROR 42000: DELETE command denied to user 'test_yesprivs'@'localhost' for table 't2'
733
revoke SELECT on priv_db.t2 from test_yesprivs@localhost;
734
grant DELETE on priv_db.t2 to test_yesprivs@localhost;
735
insert into t1 (f1) values (1);
744
drop database if exists priv_db;
745
drop user test_yesprivs@localhost;
746
drop user test_noprivs@localhost;
747
drop user test_noprivs;