5
drop database if exists priv_db;
6
drop database if exists no_priv_db;
7
create database priv_db;
8
create database no_priv_db;
10
create table t1 (f1 char(20)) engine= innodb;
11
create User test_yesprivs@localhost;
12
set password for test_yesprivs@localhost = password('PWD');
13
revoke ALL PRIVILEGES, GRANT OPTION FROM test_yesprivs@localhost;
14
grant select on priv_db.* to test_yesprivs@localhost;
15
show grants for test_yesprivs@localhost;
16
Grants for test_yesprivs@localhost
17
GRANT USAGE ON *.* TO 'test_yesprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
18
GRANT SELECT ON `priv_db`.* TO 'test_yesprivs'@'localhost'
19
create User test_noprivs@localhost;
20
set password for test_noprivs@localhost = password('PWD');
21
revoke ALL PRIVILEGES, GRANT OPTION FROM test_noprivs@localhost;
22
grant select,insert on priv_db.* to test_noprivs@localhost;
23
show grants for test_noprivs@localhost;
24
Grants for test_noprivs@localhost
25
GRANT USAGE ON *.* TO 'test_noprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
26
GRANT SELECT, INSERT ON `priv_db`.* TO 'test_noprivs'@'localhost'
28
no trigger privilege on db level for create:
29
--------------------------------------------
31
create trigger trg1_1 before INSERT on t1 for each row
32
set new.f1 = 'trig 1_1-no';
33
ERROR 42000: TRIGGER command denied to user 'test_yesprivs'@'localhost' for table 't1'
35
insert into t1 (f1) values ('insert-yes');
36
select f1 from t1 order by f1;
42
grant TRIGGER on priv_db.* to test_yesprivs@localhost;
43
show grants for test_yesprivs@localhost;
44
Grants for test_yesprivs@localhost
45
GRANT USAGE ON *.* TO 'test_yesprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
46
GRANT SELECT, TRIGGER ON `priv_db`.* TO 'test_yesprivs'@'localhost'
48
trigger privilege on db level for create:
49
-----------------------------------------
52
test_yesprivs@localhost
54
create trigger trg1_2 before INSERT on t1 for each row
55
set new.f1 = 'trig 1_2-yes';
58
test_noprivs@localhost
60
insert into t1 (f1) values ('insert-yes');
61
ERROR 42000: UPDATE command denied to user 'test_yesprivs'@'localhost' for column 'f1' in table 't1'
62
select f1 from t1 order by f1;
68
grant UPDATE on priv_db.* to test_yesprivs@localhost;
70
insert into t1 (f1) values ('insert-no');
71
select f1 from t1 order by f1;
77
test_noprivs@localhost
79
insert into t1 (f1) values ('insert-yes');
80
select f1 from t1 order by f1;
88
revoke TRIGGER on priv_db.* from test_yesprivs@localhost;
89
show grants for test_yesprivs@localhost;
90
Grants for test_yesprivs@localhost
91
GRANT USAGE ON *.* TO 'test_yesprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
92
GRANT SELECT, UPDATE ON `priv_db`.* TO 'test_yesprivs'@'localhost'
94
no trigger privilege on db level for drop:
95
------------------------------------------
98
test_yesprivs@localhost
101
ERROR 42000: TRIGGER command denied to user 'test_yesprivs'@'localhost' for table 't1'
104
test_noprivs@localhost
107
no trigger privilege at activation time:
108
----------------------------------------
109
insert into t1 (f1) values ('insert-yes');
110
ERROR 42000: TRIGGER command denied to user 'test_yesprivs'@'localhost' for table 't1'
111
select f1 from t1 order by f1;
117
trigger privilege at activation time:
118
-------------------------------------
122
grant TRIGGER on priv_db.* to test_yesprivs@localhost;
125
test_noprivs@localhost
127
insert into t1 (f1) values ('insert-no');
128
select f1 from t1 order by f1;
135
trigger privilege on db level for drop:
136
---------------------------------------
139
test_yesprivs@localhost
140
show grants for test_yesprivs@localhost;
141
Grants for test_yesprivs@localhost
142
GRANT USAGE ON *.* TO 'test_yesprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
143
GRANT SELECT, UPDATE, TRIGGER ON `priv_db`.* TO 'test_yesprivs'@'localhost'
145
ERROR 42000: TRIGGER command denied to user 'test_yesprivs'@'localhost' for table 't1'
147
takes effect after use priv_db:
148
-------------------------------
155
insert into t1 (f1) values ('insert-yes');
156
select f1 from t1 order by f1;
164
switch to db without having trigger priv for it:
165
------------------------------------------------
167
create table t1 (f1 char(20)) engine= innodb;
168
grant SELECT,UPDATE on no_priv_db.* to test_yesprivs@localhost;
169
show grants for test_yesprivs@localhost;
170
Grants for test_yesprivs@localhost
171
GRANT USAGE ON *.* TO 'test_yesprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
172
GRANT SELECT, UPDATE, TRIGGER ON `priv_db`.* TO 'test_yesprivs'@'localhost'
173
GRANT SELECT, UPDATE ON `no_priv_db`.* TO 'test_yesprivs'@'localhost'
175
use db with trigger privilege on db level and without...:
176
---------------------------------------------------------
179
test_yesprivs@localhost
181
create trigger trg1_3 before INSERT on t1 for each row
182
set new.f1 = 'trig 1_3-no';
183
ERROR 42000: TRIGGER command denied to user 'test_yesprivs'@'localhost' for table 't1'
185
create trigger trg1_3 before INSERT on t1 for each row
186
set new.f1 = 'trig 1_3-yes';
188
create trigger trg1_4 before UPDATE on t1 for each row
189
set new.f1 = 'trig 1_4-no';
190
ERROR 42000: TRIGGER command denied to user 'test_yesprivs'@'localhost' for table 't1'
192
create trigger trg1_4 before UPDATE on t1 for each row
193
set new.f1 = 'trig 1_4-yes';
196
test_noprivs@localhost
198
ERROR 42000: Access denied for user 'test_noprivs'@'localhost' to database 'no_priv_db'
199
insert into t1 (f1) values ('insert-yes');
200
select f1 from t1 order by f1;
209
insert into t1 (f1) values ('insert-no');
210
select f1 from t1 order by f1;
221
test_yesprivs@localhost
224
ERROR HY000: Trigger does not exist
229
ERROR HY000: Trigger does not exist
235
drop table priv_db.t1;
236
drop table no_priv_db.t1;
237
drop database if exists priv_db;
238
drop database if exists no_priv_db;
239
drop user test_yesprivs@localhost;
240
drop user test_noprivs@localhost;
242
######### Testcase for table level: ########
243
---------------------------------------------------
244
drop database if exists priv_db;
245
create database priv_db;
247
create table t1 (f1 char(20)) engine= innodb;
248
create User test_yesprivs@localhost;
249
set password for test_yesprivs@localhost = password('PWD');
250
revoke ALL PRIVILEGES, GRANT OPTION FROM test_yesprivs@localhost;
251
create User test_noprivs@localhost;
252
set password for test_noprivs@localhost = password('PWD');
253
revoke ALL PRIVILEGES, GRANT OPTION FROM test_noprivs@localhost;
255
no trigger privilege on table level for create:
256
-----------------------------------------------
261
Trigger Event Table Statement Timing Created sql_mode Definer character_set_client collation_connection Database Collation
262
grant select, insert, update on priv_db.t1 to test_yesprivs@localhost;
263
show grants for test_yesprivs@localhost;
264
Grants for test_yesprivs@localhost
265
GRANT USAGE ON *.* TO 'test_yesprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
266
GRANT SELECT, INSERT, UPDATE ON `priv_db`.`t1` TO 'test_yesprivs'@'localhost'
267
grant select, update, insert on priv_db.t1 to test_noprivs@localhost;
268
show grants for test_noprivs@localhost;
269
Grants for test_noprivs@localhost
270
GRANT USAGE ON *.* TO 'test_noprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
271
GRANT SELECT, INSERT, UPDATE ON `priv_db`.`t1` TO 'test_noprivs'@'localhost'
274
test_yesprivs@localhost
279
create trigger trg1_1 before INSERT on t1 for each row
280
set new.f1 = 'trig 1_1-no';
281
ERROR 42000: TRIGGER command denied to user 'test_yesprivs'@'localhost' for table 't1'
284
test_noprivs@localhost
286
insert into t1 (f1) values ('insert1-yes');
287
select f1 from t1 order by f1;
294
Trigger Event Table Statement Timing Created sql_mode Definer character_set_client collation_connection Database Collation
298
insert into t1 (f1) values ('insert2-yes');
299
select f1 from t1 order by f1;
303
grant TRIGGER on priv_db.t1 to test_yesprivs@localhost;
304
show grants for test_yesprivs@localhost;
305
Grants for test_yesprivs@localhost
306
GRANT USAGE ON *.* TO 'test_yesprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
307
GRANT SELECT, INSERT, UPDATE, TRIGGER ON `priv_db`.`t1` TO 'test_yesprivs'@'localhost'
309
trigger privilege on table level for create:
310
--------------------------------------------
313
test_yesprivs@localhost
315
Trigger Event Table Statement Timing Created sql_mode Definer character_set_client collation_connection Database Collation
316
create trigger trg1_2 before INSERT on t1 for each row
317
set new.f1 = 'trig 1_2-yes';
320
test_noprivs@localhost
321
insert into t1 (f1) values ('insert3-no');
322
select f1 from t1 order by f1;
330
insert into t1 (f1) values ('insert4-no');
331
select f1 from t1 order by f1;
337
revoke TRIGGER on priv_db.t1 from test_yesprivs@localhost;
338
show grants for test_yesprivs@localhost;
339
Grants for test_yesprivs@localhost
340
GRANT USAGE ON *.* TO 'test_yesprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
341
GRANT SELECT, INSERT, UPDATE ON `priv_db`.`t1` TO 'test_yesprivs'@'localhost'
343
no trigger privilege on table level for drop:
344
---------------------------------------------
347
test_yesprivs@localhost
349
ERROR 42000: TRIGGER command denied to user 'test_yesprivs'@'localhost' for table 't1'
351
no trigger privilege at activation time:
352
----------------------------------------
355
test_noprivs@localhost
356
insert into t1 (f1) values ('insert5-no');
357
ERROR 42000: TRIGGER command denied to user 'test_yesprivs'@'localhost' for table 't1'
358
select f1 from t1 order by f1;
367
grant TRIGGER on priv_db.t1 to test_yesprivs@localhost;
369
trigger privilege at activation time:
370
-------------------------------------
373
test_noprivs@localhost
374
insert into t1 (f1) values ('insert6-no');
375
select f1 from t1 order by f1;
383
trigger privilege on table level for drop:
384
------------------------------------------
387
test_yesprivs@localhost
388
show grants for test_yesprivs@localhost;
389
Grants for test_yesprivs@localhost
390
GRANT USAGE ON *.* TO 'test_yesprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
391
GRANT SELECT, INSERT, UPDATE, TRIGGER ON `priv_db`.`t1` TO 'test_yesprivs'@'localhost'
395
test_noprivs@localhost
396
insert into t1 (f1) values ('insert7-yes');
397
select f1 from t1 order by f1;
408
insert into t1 (f1) values ('insert8-yes');
409
select f1 from t1 order by f1;
419
switch to table without having trigger priv for it:
420
---------------------------------------------------
421
create table t2 (f1 char(20)) engine= innodb;
422
grant SELECT, INSERT, UPDATE on priv_db.t2 to test_yesprivs@localhost;
423
show grants for test_yesprivs@localhost;
424
Grants for test_yesprivs@localhost
425
GRANT USAGE ON *.* TO 'test_yesprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
426
GRANT SELECT, INSERT, UPDATE ON `priv_db`.`t2` TO 'test_yesprivs'@'localhost'
427
GRANT SELECT, INSERT, UPDATE, TRIGGER ON `priv_db`.`t1` TO 'test_yesprivs'@'localhost'
428
grant SELECT, INSERT, UPDATE on priv_db.t2 to test_noprivs@localhost;
429
show grants for test_noprivs@localhost;
430
Grants for test_noprivs@localhost
431
GRANT USAGE ON *.* TO 'test_noprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
432
GRANT SELECT, INSERT, UPDATE ON `priv_db`.`t1` TO 'test_noprivs'@'localhost'
433
GRANT SELECT, INSERT, UPDATE ON `priv_db`.`t2` TO 'test_noprivs'@'localhost'
435
use table with trigger privilege and without...:
436
------------------------------------------------
439
test_yesprivs@localhost
440
create trigger trg2_1 before INSERT on t2 for each row
441
set new.f1 = 'trig 2_1-no';
442
ERROR 42000: TRIGGER command denied to user 'test_yesprivs'@'localhost' for table 't2'
443
create trigger trg1_3 before INSERT on t1 for each row
444
set new.f1 = 'trig 1_3-yes';
445
create trigger trg2_2 before UPDATE on t2 for each row
446
set new.f1 = 'trig 2_2-no';
447
ERROR 42000: TRIGGER command denied to user 'test_yesprivs'@'localhost' for table 't2'
448
create trigger trg1_4 before UPDATE on t1 for each row
449
set new.f1 = 'trig 1_4-yes';
451
Trigger Event Table Statement Timing Created sql_mode Definer character_set_client collation_connection Database Collation
452
trg1_3 INSERT t1 set new.f1 = 'trig 1_3-yes' BEFORE NULL NO_ENGINE_SUBSTITUTION test_yesprivs@localhost latin1 latin1_swedish_ci latin1_swedish_ci
453
trg1_4 UPDATE t1 set new.f1 = 'trig 1_4-yes' BEFORE NULL NO_ENGINE_SUBSTITUTION test_yesprivs@localhost latin1 latin1_swedish_ci latin1_swedish_ci
456
test_noprivs@localhost
457
insert into t2 (f1) values ('insert9-yes');
458
select f1 from t2 order by f1;
461
insert into t1 (f1) values ('insert10-no');
462
select f1 from t1 order by f1;
474
test_yesprivs@localhost
476
ERROR HY000: Trigger does not exist
479
ERROR HY000: Trigger does not exist
484
drop database if exists priv_db;
485
drop user test_yesprivs@localhost;
486
drop user test_noprivs@localhost;
488
#### Testcase for mix of user(global) and db level: ####
489
--------------------------------------------------------
490
drop database if exists priv_db;
491
drop database if exists no_priv_db;
492
create database priv_db;
493
create database no_priv_db;
495
create table t1 (f1 char(20)) engine= innodb;
497
create table t1 (f1 char(20)) engine= innodb;
498
create User test_yesprivs@localhost;
499
set password for test_yesprivs@localhost = password('PWD');
500
revoke ALL PRIVILEGES, GRANT OPTION FROM test_yesprivs@localhost;
501
grant ALL on *.* to test_yesprivs@localhost;
502
show grants for test_yesprivs@localhost;
503
Grants for test_yesprivs@localhost
504
GRANT ALL PRIVILEGES ON *.* TO 'test_yesprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
505
create User test_noprivs@localhost;
506
set password for test_noprivs@localhost = password('PWD');
507
revoke ALL PRIVILEGES, GRANT OPTION FROM test_noprivs@localhost;
508
grant SELECT,INSERT on *.* to test_noprivs@localhost;
509
show grants for test_noprivs@localhost;
510
Grants for test_noprivs@localhost
511
GRANT SELECT, INSERT ON *.* TO 'test_noprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
514
test_yesprivs@localhost
516
trigger privilege on user level for create:
517
-------------------------------------------
519
create trigger trg1_1 before INSERT on t1 for each row
520
set new.f1 = 'trig 1_1-yes';
521
insert into t1 (f1) values ('insert-no');
522
select f1 from t1 order by f1;
526
create trigger priv_db.trg1_5 before UPDATE on priv_db.t1
528
set new.f1 = 'trig 1_5-yes';
529
insert into priv_db.t1 (f1) values ('insert-no');
530
select f1 from priv_db.t1 order by f1;
534
drop trigger priv_db.trg1_5;
537
test_noprivs@localhost
539
insert into t1 (f1) values ('insert-no');
540
select f1 from t1 order by f1;
549
insert into t1 (f1) values ('insert-no');
550
select f1 from t1 order by f1;
556
revoke TRIGGER on *.* from test_yesprivs@localhost;
557
show grants for test_yesprivs@localhost;
558
Grants for test_yesprivs@localhost
559
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, CREATE TABLESPACE ON *.* TO 'test_yesprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
562
test_yesprivs@localhost
565
Trigger Event Table Statement Timing Created sql_mode Definer character_set_client collation_connection Database Collation
566
select * from information_schema.triggers;
567
TRIGGER_CATALOG TRIGGER_SCHEMA TRIGGER_NAME EVENT_MANIPULATION EVENT_OBJECT_CATALOG EVENT_OBJECT_SCHEMA EVENT_OBJECT_TABLE ACTION_ORDER ACTION_CONDITION ACTION_STATEMENT ACTION_ORIENTATION ACTION_TIMING ACTION_REFERENCE_OLD_TABLE ACTION_REFERENCE_NEW_TABLE ACTION_REFERENCE_OLD_ROW ACTION_REFERENCE_NEW_ROW CREATED SQL_MODE DEFINER CHARACTER_SET_CLIENT COLLATION_CONNECTION DATABASE_COLLATION
569
ERROR 42000: TRIGGER command denied to user 'test_yesprivs'@'localhost' for table 't1'
574
Grants for root@localhost
575
GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION
576
GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION
580
no trigger privilege on db level for create:
581
--------------------------------------------
584
test_yesprivs@localhost
585
create trigger trg1_1 before INSERT on t1 for each row
586
set new.f1 = 'trig 1_1-no';
587
ERROR 42000: TRIGGER command denied to user 'test_yesprivs'@'localhost' for table 't1'
590
test_noprivs@localhost
592
insert into t1 (f1) values ('insert-yes');
593
select f1 from t1 order by f1;
603
grant TRIGGER on priv_db.* to test_yesprivs@localhost;
604
show grants for test_yesprivs@localhost;
605
Grants for test_yesprivs@localhost
606
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, CREATE TABLESPACE ON *.* TO 'test_yesprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
607
GRANT TRIGGER ON `priv_db`.* TO 'test_yesprivs'@'localhost'
609
trigger privilege on db level for create:
610
-----------------------------------------
613
test_yesprivs@localhost
615
create trigger trg1_2 before INSERT on t1 for each row
616
set new.f1 = 'trig 1_2-yes';
617
create trigger no_priv_db.trg1_9 before insert on no_priv_db.t1
619
set new.f1 = 'trig 1_9-yes';
620
ERROR 42000: TRIGGER command denied to user 'test_yesprivs'@'localhost' for table 't1'
622
create trigger trg1_2 before INSERT on t1 for each row
623
set new.f1 = 'trig 1_2-no';
624
ERROR 42000: TRIGGER command denied to user 'test_yesprivs'@'localhost' for table 't1'
625
create trigger priv_db.trg1_9 before UPDATE on priv_db.t1
627
set new.f1 = 'trig 1_9-yes';
630
test_noprivs@localhost
632
insert into t1 (f1) values ('insert-yes');
633
select f1 from t1 order by f1;
642
insert into t1 (f1) values ('insert-yes');
643
select f1 from t1 order by f1;
646
drop trigger priv_db.trg1_9;
647
ERROR 42000: TRIGGER command denied to user 'test_noprivs'@'localhost' for table 't1'
651
drop trigger priv_db.trg1_9;
652
revoke TRIGGER on priv_db.* from test_yesprivs@localhost;
654
insert into t1 (f1) values ('insert-yes');
655
ERROR 42000: TRIGGER command denied to user 'test_yesprivs'@'localhost' for table 't1'
656
select f1 from t1 order by f1;
664
grant TRIGGER on *.* to test_yesprivs@localhost;
665
show grants for test_yesprivs@localhost;
666
Grants for test_yesprivs@localhost
667
GRANT ALL PRIVILEGES ON *.* TO 'test_yesprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
670
test_yesprivs@localhost
672
create trigger trg1_2 before INSERT on t1 for each row
673
set new.f1 = 'trig 1_2-no';
674
ERROR 42000: TRIGGER command denied to user 'test_yesprivs'@'localhost' for table 't1'
677
test_noprivs@localhost
679
insert into t1 (f1) values ('insert-no');
680
select f1 from t1 order by f1;
690
insert into t1 (f1) values ('insert-yes');
691
select f1 from t1 order by f1;
697
test_yesprivs@localhost
699
create trigger trg1_2 before INSERT on t1 for each row
700
set new.f1 = 'trig 1_2-yes';
703
test_noprivs@localhost
705
insert into t1 (f1) values ('insert-no');
706
select f1 from t1 order by f1;
717
insert into t1 (f1) values ('insert-no');
718
select f1 from t1 order by f1;
726
drop database if exists priv_db;
727
drop database if exists no_priv_db;
728
drop database if exists h1;
729
drop user test_yesprivs@localhost;
730
drop user test_noprivs@localhost;
731
call mtr.add_suppression("Did not write failed .* into binary log while "
732
"storing table level and column level grants "
733
"in the privilege tables.");
735
####### Testcase for mix of db and table level: #######
736
-------------------------------------------------------
737
drop database if exists priv1_db;
738
drop database if exists priv2_db;
739
create database priv1_db;
740
create database priv2_db;
742
create table t1 (f1 char(20)) engine= innodb;
743
create table t2 (f1 char(20)) engine= innodb;
745
create table t1 (f1 char(20)) engine= innodb;
746
create User test_yesprivs@localhost;
747
set password for test_yesprivs@localhost = password('PWD');
748
revoke ALL PRIVILEGES, GRANT OPTION FROM test_yesprivs@localhost;
749
grant ALL on priv1_db.* to test_yesprivs@localhost;
750
grant SELECT,UPDATE on priv2_db.* to test_yesprivs@localhost;
751
show grants for test_yesprivs@localhost;
752
Grants for test_yesprivs@localhost
753
GRANT USAGE ON *.* TO 'test_yesprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
754
GRANT ALL PRIVILEGES ON `priv1_db`.* TO 'test_yesprivs'@'localhost'
755
GRANT SELECT, UPDATE ON `priv2_db`.* TO 'test_yesprivs'@'localhost'
756
create User test_noprivs@localhost;
757
set password for test_noprivs@localhost = password('PWD');
758
revoke ALL PRIVILEGES, GRANT OPTION FROM test_noprivs@localhost;
759
grant SELECT,INSERT,UPDATE on priv1_db.* to test_noprivs@localhost;
760
grant SELECT,INSERT on priv2_db.* to test_noprivs@localhost;
761
show grants for test_noprivs@localhost;
762
Grants for test_noprivs@localhost
763
GRANT USAGE ON *.* TO 'test_noprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
764
GRANT SELECT, INSERT, UPDATE ON `priv1_db`.* TO 'test_noprivs'@'localhost'
765
GRANT SELECT, INSERT ON `priv2_db`.* TO 'test_noprivs'@'localhost'
769
trigger privilege on one db1 db level, not on db2
770
-------------------------------------------------
773
test_yesprivs@localhost
775
create trigger trg1_1 before INSERT on t1 for each row
776
set new.f1 = 'trig 1_1-yes';
777
create trigger trg2_1 before INSERT on t2 for each row
778
set new.f1 = 'trig 2_1-yes';
780
create trigger trg1_1 before INSERT on t1 for each row
781
set new.f1 = 'trig1_1-yes';
782
ERROR 42000: TRIGGER command denied to user 'test_yesprivs'@'localhost' for table 't1'
785
test_noprivs@localhost
786
insert into t1 (f1) values ('insert1_no');
787
select f1 from t1 order by f1;
790
insert into t2 (f1) values ('insert1_no');
791
select f1 from t2 order by f1;
794
insert into priv2_db.t1 (f1) values ('insert21-yes');
795
select f1 from priv2_db.t1 order by f1;
799
insert into t1 (f1) values ('insert1_yes');
800
select f1 from t1 order by f1;
804
insert into priv1_db.t1 (f1) values ('insert11-no');
805
select f1 from priv1_db.t1 order by f1;
809
insert into priv1_db.t2 (f1) values ('insert22-no');
810
select f1 from priv1_db.t2 order by f1;
815
revoke trigger privilege on table level (not existing)
816
------------------------------------------------------
821
revoke TRIGGER on priv1_db.t1 from test_yesprivs@localhost;
822
ERROR 42000: There is no such grant defined for user 'test_yesprivs' on host 'localhost' on table 't1'
823
show grants for test_yesprivs@localhost;
824
Grants for test_yesprivs@localhost
825
GRANT USAGE ON *.* TO 'test_yesprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
826
GRANT ALL PRIVILEGES ON `priv1_db`.* TO 'test_yesprivs'@'localhost'
827
GRANT SELECT, UPDATE ON `priv2_db`.* TO 'test_yesprivs'@'localhost'
830
test_yesprivs@localhost
832
ERROR HY000: Trigger does not exist
834
ERROR HY000: Trigger does not exist
842
revoke TRIGGER on priv1_db.* from test_yesprivs@localhost;
844
no trigger privilege on table level for create:
845
-----------------------------------------------
848
test_yesprivs@localhost
850
create trigger trg1_1 before INSERT on t1 for each row
851
set new.f1 = 'trig 1_1-no';
852
ERROR 42000: TRIGGER command denied to user 'test_yesprivs'@'localhost' for table 't1'
857
Trigger Event Table Statement Timing Created sql_mode Definer character_set_client collation_connection Database Collation
858
grant TRIGGER on priv1_db.t1 to test_yesprivs@localhost;
859
show grants for test_yesprivs@localhost;
860
Grants for test_yesprivs@localhost
861
GRANT USAGE ON *.* TO 'test_yesprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
862
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, EVENT ON `priv1_db`.* TO 'test_yesprivs'@'localhost'
863
GRANT SELECT, UPDATE ON `priv2_db`.* TO 'test_yesprivs'@'localhost'
864
GRANT TRIGGER ON `priv1_db`.`t1` TO 'test_yesprivs'@'localhost'
866
trigger privilege on table level for create:
867
--------------------------------------------
870
test_yesprivs@localhost
872
Trigger Event Table Statement Timing Created sql_mode Definer character_set_client collation_connection Database Collation
873
create trigger trg1_2 before INSERT on t1 for each row
874
set new.f1 = 'trig 1_2-yes';
875
create trigger trg2_1 before INSERT on t2 for each row
876
set new.f1 = 'trig 2_1-no';
877
ERROR 42000: TRIGGER command denied to user 'test_yesprivs'@'localhost' for table 't2'
880
test_noprivs@localhost
882
insert into t1 (f1) values ('insert2-no');
883
select f1 from t1 order by f1;
888
insert into t2 (f1) values ('insert2-yes');
889
select f1 from t2 order by f1;
894
insert into priv2_db.t1 (f1) values ('insert22-yes');
895
select f1 from priv2_db.t1 order by f1;
903
grant TRIGGER on priv1_db.* to test_yesprivs@localhost;
904
show grants for test_yesprivs@localhost;
905
Grants for test_yesprivs@localhost
906
GRANT USAGE ON *.* TO 'test_yesprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
907
GRANT ALL PRIVILEGES ON `priv1_db`.* TO 'test_yesprivs'@'localhost'
908
GRANT SELECT, UPDATE ON `priv2_db`.* TO 'test_yesprivs'@'localhost'
909
GRANT TRIGGER ON `priv1_db`.`t1` TO 'test_yesprivs'@'localhost'
912
test_yesprivs@localhost
913
create trigger trg2_1 before INSERT on t2 for each row
914
set new.f1 = 'trig 2_1-yes';
915
ERROR 42000: TRIGGER command denied to user 'test_yesprivs'@'localhost' for table 't2'
917
create trigger trg2_1 before INSERT on t2 for each row
918
set new.f1 = 'trig 2_1-yes';
921
test_noprivs@localhost
923
insert into t1 (f1) values ('insert3-no');
924
select f1 from t1 order by f1;
930
insert into t2 (f1) values ('insert3-no');
931
select f1 from t2 order by f1;
938
insert into priv1_db.t1 (f1) values ('insert12-no');
939
select f1 from priv1_db.t1 order by f1;
946
insert into priv1_db.t2 (f1) values ('insert23-no');
947
select f1 from priv1_db.t2 order by f1;
956
test_yesprivs@localhost
962
drop database if exists priv1_db;
963
drop database if exists priv2_db;
964
drop user test_yesprivs@localhost;
965
drop user test_noprivs@localhost;
967
#### Testcase for trigger privilege on execution time ########
968
--------------------------------------------------------------
969
drop database if exists priv_db;
970
create database priv_db;
972
create table t1 (f1 char(20)) engine= innodb;
973
create User test_yesprivs@localhost;
974
set password for test_yesprivs@localhost = password('PWD');
975
create User test_useprivs@localhost;
976
set password for test_useprivs@localhost = password('PWD');
977
revoke ALL PRIVILEGES, GRANT OPTION FROM test_yesprivs@localhost;
978
revoke ALL PRIVILEGES, GRANT OPTION FROM test_useprivs@localhost;
983
Trigger Event Table Statement Timing Created sql_mode Definer character_set_client collation_connection Database Collation
984
grant select, insert, update ,trigger
985
on priv_db.t1 to test_yesprivs@localhost
988
on priv_db.t1 to test_useprivs@localhost;
989
show grants for test_yesprivs@localhost;
990
Grants for test_yesprivs@localhost
991
GRANT USAGE ON *.* TO 'test_yesprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
992
GRANT SELECT, INSERT, UPDATE, TRIGGER ON `priv_db`.`t1` TO 'test_yesprivs'@'localhost' WITH GRANT OPTION
995
test_yesprivs@localhost
997
create trigger trg1_1 before INSERT on t1 for each row
998
set new.f1 = 'trig 1_1-yes';
999
grant insert on t1 to test_useprivs@localhost;
1000
prepare ins1 from 'insert into t1 (f1) values (''insert1-no'')';
1002
select f1 from t1 order by f1;
1005
prepare ins1 from 'insert into t1 (f1) values (''insert2-no'')';
1006
select current_user;
1008
test_useprivs@localhost
1010
prepare ins1 from 'insert into t1 (f1) values (''insert3-no'')';
1012
select f1 from t1 order by f1;
1016
select current_user;
1019
revoke TRIGGER on priv_db.t1 from test_yesprivs@localhost;
1020
show grants for test_yesprivs@localhost;
1021
Grants for test_yesprivs@localhost
1022
GRANT USAGE ON *.* TO 'test_yesprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
1023
GRANT SELECT, INSERT, UPDATE ON `priv_db`.`t1` TO 'test_yesprivs'@'localhost' WITH GRANT OPTION
1024
select current_user;
1026
test_yesprivs@localhost
1028
ERROR 42000: TRIGGER command denied to user 'test_yesprivs'@'localhost' for table 't1'
1029
select f1 from t1 order by f1;
1033
prepare ins1 from 'insert into t1 (f1) values (''insert4-no'')';
1034
select current_user;
1036
test_useprivs@localhost
1037
prepare ins1 from 'insert into t1 (f1) values (''insert5-no'')';
1039
ERROR 42000: TRIGGER command denied to user 'test_yesprivs'@'localhost' for table 't1'
1040
select f1 from t1 order by f1;
1044
select current_user;
1047
grant TRIGGER on priv_db.t1 to test_yesprivs@localhost;
1048
show grants for test_yesprivs@localhost;
1049
Grants for test_yesprivs@localhost
1050
GRANT USAGE ON *.* TO 'test_yesprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
1051
GRANT SELECT, INSERT, UPDATE, TRIGGER ON `priv_db`.`t1` TO 'test_yesprivs'@'localhost' WITH GRANT OPTION
1052
select current_user;
1054
test_yesprivs@localhost
1056
select f1 from t1 order by f1;
1061
prepare ins1 from 'insert into t1 (f1) values (''insert6-no'')';
1062
select current_user;
1064
test_useprivs@localhost
1066
select f1 from t1 order by f1;
1072
prepare ins1 from 'insert into t1 (f1) values (''insert7-no'')';
1073
select current_user;
1076
revoke TRIGGER on priv_db.t1 from test_yesprivs@localhost;
1077
show grants for test_yesprivs@localhost;
1078
Grants for test_yesprivs@localhost
1079
GRANT USAGE ON *.* TO 'test_yesprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
1080
GRANT SELECT, INSERT, UPDATE ON `priv_db`.`t1` TO 'test_yesprivs'@'localhost' WITH GRANT OPTION
1081
select current_user;
1083
test_yesprivs@localhost
1085
ERROR 42000: TRIGGER command denied to user 'test_yesprivs'@'localhost' for table 't1'
1086
select f1 from t1 order by f1;
1092
select current_user;
1094
test_useprivs@localhost
1096
ERROR 42000: TRIGGER command denied to user 'test_yesprivs'@'localhost' for table 't1'
1097
select f1 from t1 order by f1;
1103
select current_user;
1106
grant TRIGGER on priv_db.t1 to test_yesprivs@localhost;
1107
show grants for test_yesprivs@localhost;
1108
Grants for test_yesprivs@localhost
1109
GRANT USAGE ON *.* TO 'test_yesprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
1110
GRANT SELECT, INSERT, UPDATE, TRIGGER ON `priv_db`.`t1` TO 'test_yesprivs'@'localhost' WITH GRANT OPTION
1111
select current_user;
1113
test_yesprivs@localhost
1115
select f1 from t1 order by f1;
1122
select current_user;
1124
test_useprivs@localhost
1126
select f1 from t1 order by f1;
1134
select current_user;
1137
revoke TRIGGER on priv_db.t1 from test_yesprivs@localhost;
1138
show grants for test_yesprivs@localhost;
1139
Grants for test_yesprivs@localhost
1140
GRANT USAGE ON *.* TO 'test_yesprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
1141
GRANT SELECT, INSERT, UPDATE ON `priv_db`.`t1` TO 'test_yesprivs'@'localhost' WITH GRANT OPTION
1142
select current_user;
1144
test_yesprivs@localhost
1146
ERROR 42000: TRIGGER command denied to user 'test_yesprivs'@'localhost' for table 't1'
1147
select f1 from t1 order by f1;
1155
deallocate prepare ins1;
1156
select current_user;
1158
test_useprivs@localhost
1160
ERROR 42000: TRIGGER command denied to user 'test_yesprivs'@'localhost' for table 't1'
1161
select f1 from t1 order by f1;
1169
deallocate prepare ins1;
1170
select current_user;
1173
grant TRIGGER on priv_db.t1 to test_yesprivs@localhost;
1174
show grants for test_yesprivs@localhost;
1175
Grants for test_yesprivs@localhost
1176
GRANT USAGE ON *.* TO 'test_yesprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
1177
GRANT SELECT, INSERT, UPDATE, TRIGGER ON `priv_db`.`t1` TO 'test_yesprivs'@'localhost' WITH GRANT OPTION
1178
select current_user;
1180
test_yesprivs@localhost
1181
drop trigger trg1_1;
1182
select current_user;
1185
select current_user;
1188
drop database if exists priv_db;
1189
drop user test_yesprivs@localhost;
1190
drop user test_useprivs@localhost;
1192
######### Testcase for definer: ########
1193
-----------------------------------------------
1194
drop database if exists priv_db;
1195
create database priv_db;
1197
create table t1 (f1 char(20)) engine= innodb;
1198
create User test_yesprivs@localhost;
1199
set password for test_yesprivs@localhost = password('PWD');
1200
revoke ALL PRIVILEGES, GRANT OPTION FROM test_yesprivs@localhost;
1201
select current_user;
1204
create definer=not_ex_user@localhost trigger trg1_0
1205
before INSERT on t1 for each row
1206
set new.f1 = 'trig 1_0-yes';
1208
Note 1449 The user specified as a definer ('not_ex_user'@'localhost') does not exist
1209
drop trigger trg1_0;
1210
create definer=test_yesprivs@localhost trigger trg1_0
1211
before INSERT on t1 for each row
1212
set new.f1 = 'trig 1_0-yes';
1213
grant select, insert, update
1214
on priv_db.t1 to test_yesprivs@localhost;
1215
select current_user;
1217
test_yesprivs@localhost
1219
insert into t1 (f1) values ('insert-no');
1220
ERROR 42000: TRIGGER command denied to user 'test_yesprivs'@'localhost' for table 't1'
1221
select f1 from t1 order by f1;
1223
drop trigger trg1_0;
1224
ERROR 42000: TRIGGER command denied to user 'test_yesprivs'@'localhost' for table 't1'
1225
select current_user;
1228
grant select, insert, update ,trigger
1229
on priv_db.t1 to test_yesprivs@localhost;
1230
show grants for test_yesprivs@localhost;
1231
Grants for test_yesprivs@localhost
1232
GRANT USAGE ON *.* TO 'test_yesprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
1233
GRANT SELECT, INSERT, UPDATE, TRIGGER ON `priv_db`.`t1` TO 'test_yesprivs'@'localhost'
1234
select current_user;
1236
test_yesprivs@localhost
1237
insert into t1 (f1) values ('insert-no');
1238
select f1 from t1 order by f1;
1241
drop trigger trg1_0;
1242
create definer=not_ex_user@localhost trigger trg1_0
1243
before INSERT on t1 for each row
1244
set new.f1 = 'trig 1_0-yes';
1245
ERROR 42000: Access denied; you need (at least one of) the SUPER privilege(s) for this operation
1246
create definer=current_user trigger trg1_1
1247
before INSERT on t1 for each row
1248
set new.f1 = 'trig 1_1-yes';
1249
insert into t1 (f1) values ('insert-no');
1250
select f1 from t1 order by f1;
1254
create definer=test_yesprivs@localhost trigger trg1_2
1255
before UPDATE on t1 for each row
1256
set new.f1 = 'trig 1_2-yes';
1257
update t1 set f1 = 'update-yes' where f1 like '%trig%';
1258
select f1 from t1 order by f1;
1262
select current_user;
1265
grant trigger on priv_db.* to test_yesprivs@localhost
1267
select current_user;
1269
test_yesprivs@localhost
1271
Grants for test_yesprivs@localhost
1272
GRANT USAGE ON *.* TO 'test_yesprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
1273
GRANT TRIGGER ON `priv_db`.* TO 'test_yesprivs'@'localhost' WITH GRANT OPTION
1274
GRANT SELECT, INSERT, UPDATE, TRIGGER ON `priv_db`.`t1` TO 'test_yesprivs'@'localhost'
1275
create definer=not_ex_user@localhost trigger trg1_3
1276
after UPDATE on t1 for each row
1277
set @var1 = 'trig 1_3-yes';
1278
ERROR 42000: Access denied; you need (at least one of) the SUPER privilege(s) for this operation
1279
select current_user;
1282
select current_user;
1285
drop database if exists priv_db;
1286
drop user test_yesprivs@localhost;
1288
######### Testcase for transactions: ########
1289
----------------------------------------------------
1290
drop database if exists priv_db;
1291
create database priv_db;
1293
create table t1 (f1 char(20)) engine= innodb;
1294
create User test_yesprivs@localhost;
1295
set password for test_yesprivs@localhost = password('PWD');
1296
revoke ALL PRIVILEGES, GRANT OPTION FROM test_yesprivs@localhost;
1297
select current_user;
1300
grant select, insert, update ,trigger
1301
on priv_db.t1 to test_yesprivs@localhost;
1302
show grants for test_yesprivs@localhost;
1303
Grants for test_yesprivs@localhost
1304
GRANT USAGE ON *.* TO 'test_yesprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
1305
GRANT SELECT, INSERT, UPDATE, TRIGGER ON `priv_db`.`t1` TO 'test_yesprivs'@'localhost'
1306
select current_user;
1308
test_yesprivs@localhost
1311
create definer=current_user trigger trg1_1
1312
before INSERT on t1 for each row
1313
set new.f1 = 'trig 1_1-yes';
1315
insert into t1 (f1) values ('insert-no');
1316
select f1 from t1 order by f1;
1319
create definer=test_yesprivs@localhost trigger trg1_2
1320
before UPDATE on t1 for each row
1321
set new.f1 = 'trig 1_2-yes';
1323
update t1 set f1 = 'update-yes' where f1 like '%trig%';
1324
select f1 from t1 order by f1;
1328
drop trigger trg1_1;
1330
drop trigger trg1_1;
1331
ERROR HY000: Trigger does not exist
1332
drop trigger trg1_2;
1335
select current_user;
1338
select current_user;
1341
drop database if exists priv_db;
1342
drop user test_yesprivs@localhost;
1344
####### Testcase for column privileges of triggers: #######
1345
-----------------------------------------------------------
1346
drop database if exists priv_db;
1347
drop database if exists no_priv_db;
1348
create database priv_db;
1350
create table t1 (f1 char(20)) engine= innodb;
1351
create table t2 (f1 char(20)) engine= innodb;
1352
create User test_yesprivs@localhost;
1353
set password for test_yesprivs@localhost = password('PWD');
1354
revoke ALL PRIVILEGES, GRANT OPTION FROM test_yesprivs@localhost;
1355
grant TRIGGER on priv_db.* to test_yesprivs@localhost;
1356
show grants for test_yesprivs@localhost;
1357
Grants for test_yesprivs@localhost
1358
GRANT USAGE ON *.* TO 'test_yesprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
1359
GRANT TRIGGER ON `priv_db`.* TO 'test_yesprivs'@'localhost'
1360
create User test_noprivs@localhost;
1361
set password for test_noprivs@localhost = password('PWD');
1362
revoke ALL PRIVILEGES, GRANT OPTION FROM test_noprivs@localhost;
1363
grant SELECT,UPDATE on priv_db.* to test_noprivs@localhost;
1364
show grants for test_noprivs@localhost;
1365
Grants for test_noprivs@localhost
1366
GRANT USAGE ON *.* TO 'test_noprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
1367
GRANT SELECT, UPDATE ON `priv_db`.* TO 'test_noprivs'@'localhost'
1369
update only on column:
1370
----------------------
1371
select current_user;
1374
grant SELECT(f1),INSERT,UPDATE(f1) on priv_db.t1
1375
to test_yesprivs@localhost;
1376
grant SELECT(f1),INSERT,UPDATE(f1) on priv_db.t2
1377
to test_yesprivs@localhost;
1378
select current_user;
1380
test_yesprivs@localhost
1382
insert into t1 (f1) values ('insert1-yes');
1383
insert into t2 (f1) values ('insert1-yes');
1384
create trigger trg1_1 before UPDATE on t1 for each row
1385
set new.f1 = 'trig 1_1-yes';
1386
create trigger trg2_1 before UPDATE on t2 for each row
1387
set new.f1 = 'trig 2_1-yes';
1388
select current_user;
1390
test_noprivs@localhost
1392
select f1 from t1 order by f1;
1395
update t1 set f1 = 'update1_no'
1396
where f1 like '%insert%';
1397
select f1 from t1 order by f1;
1400
select f1 from t2 order by f1;
1403
update t2 set f1 = 'update1_no'
1404
where f1 like '%insert%';
1405
select f1 from t2 order by f1;
1408
select current_user;
1411
revoke UPDATE on priv_db.*
1412
from test_yesprivs@localhost;
1413
revoke UPDATE(f1) on priv_db.t2
1414
from test_yesprivs@localhost;
1415
show grants for test_yesprivs@localhost;
1416
Grants for test_yesprivs@localhost
1417
GRANT USAGE ON *.* TO 'test_yesprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
1418
GRANT TRIGGER ON `priv_db`.* TO 'test_yesprivs'@'localhost'
1419
GRANT SELECT (f1), INSERT ON `priv_db`.`t2` TO 'test_yesprivs'@'localhost'
1420
GRANT SELECT (f1), INSERT, UPDATE (f1) ON `priv_db`.`t1` TO 'test_yesprivs'@'localhost'
1421
select current_user;
1423
test_yesprivs@localhost
1425
insert into t1 (f1) values ('insert2-yes');
1426
insert into t2 (f1) values ('insert2-yes');
1427
select current_user;
1429
test_noprivs@localhost
1431
update t1 set f1 = 'update2_no'
1432
where f1 like '%insert%';
1433
update t2 set f1 = 'update2_no'
1434
where f1 like '%insert%';
1435
ERROR 42000: UPDATE command denied to user 'test_yesprivs'@'localhost' for column 'f1' in table 't2'
1436
update t1 set f1 = 'update3_no'
1437
where f1 like '%insert%';
1438
update t2 set f1 = 'update3_no'
1439
where f1 like '%insert%';
1440
ERROR 42000: UPDATE command denied to user 'test_yesprivs'@'localhost' for column 'f1' in table 't2'
1441
select f1 from t1 order by f1;
1445
select f1 from t2 order by f1;
1450
check if access only on one of three columns
1451
--------------------------------------------
1452
select current_user;
1455
alter table priv_db.t1 add f2 char(20), add f3 int;
1456
revoke TRIGGER on priv_db.* from test_yesprivs@localhost;
1457
grant TRIGGER,SELECT on priv_db.t1 to test_yesprivs@localhost;
1458
grant UPDATE on priv_db.t2 to test_yesprivs@localhost;
1459
select current_user;
1461
test_yesprivs@localhost
1463
insert into t1 values ('insert2-yes','insert2-yes',1);
1464
insert into t1 values ('insert3-yes','insert3-yes',2);
1465
select * from t1 order by f1;
1467
insert2-yes insert2-yes 1
1468
insert3-yes insert3-yes 2
1469
trig 1_1-yes NULL NULL
1470
trig 1_1-yes NULL NULL
1471
select current_user;
1473
test_noprivs@localhost
1475
update t1 set f1 = 'update4-no',
1478
where f2 like '%yes';
1479
select * from t1 order by f1,f2,f3;
1481
trig 1_1-yes NULL NULL
1482
trig 1_1-yes NULL NULL
1483
trig 1_1-yes update4-yes 10
1484
trig 1_1-yes update4-yes 20
1485
select current_user;
1487
test_yesprivs@localhost
1488
create trigger trg1_2 after UPDATE on t1 for each row
1489
set @f2 = 'trig 1_2-yes';
1490
select current_user;
1492
test_noprivs@localhost
1493
update t1 set f1 = 'update5-yes',
1495
where f2 like '%yes';
1496
select * from t1 order by f1,f2,f3;
1498
trig 1_1-yes NULL NULL
1499
trig 1_1-yes NULL NULL
1500
trig 1_1-yes update5-yes 10
1501
trig 1_1-yes update5-yes 20
1505
update t1 set f1 = 'update6_no'
1506
where f1 like '%insert%';
1507
update t2 set f1 = 'update6_no'
1508
where f1 like '%insert%';
1509
ERROR 42000: TRIGGER command denied to user 'test_yesprivs'@'localhost' for table 't2'
1510
update t1 set f1 = 'update7_no'
1511
where f1 like '%insert%';
1512
update t2 set f1 = 'update7_no'
1513
where f1 like '%insert%';
1514
ERROR 42000: TRIGGER command denied to user 'test_yesprivs'@'localhost' for table 't2'
1515
select f1 from t1 order by f1;
1521
select f1 from t2 order by f1;
1526
check if rejected without trigger privilege:
1527
--------------------------------------------
1528
select current_user;
1531
revoke TRIGGER on priv_db.t1 from test_yesprivs@localhost;
1532
select current_user;
1534
test_noprivs@localhost
1535
update t1 set f1 = 'update8-no',
1537
where f2 like '%yes';
1538
ERROR 42000: TRIGGER command denied to user 'test_yesprivs'@'localhost' for table 't1'
1539
select * from t1 order by f1,f2,f3;
1541
trig 1_1-yes NULL NULL
1542
trig 1_1-yes NULL NULL
1543
trig 1_1-yes update5-yes 10
1544
trig 1_1-yes update5-yes 20
1549
check trigger, but not update privilege on column:
1550
--------------------------------------------------
1551
select current_user;
1554
revoke UPDATE(f1) on priv_db.t1 from test_yesprivs@localhost;
1555
grant TRIGGER,UPDATE(f2),UPDATE(f3) on priv_db.t1
1556
to test_yesprivs@localhost;
1557
show grants for test_yesprivs@localhost;
1558
Grants for test_yesprivs@localhost
1559
GRANT USAGE ON *.* TO 'test_yesprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
1560
GRANT SELECT (f1), INSERT, UPDATE ON `priv_db`.`t2` TO 'test_yesprivs'@'localhost'
1561
GRANT SELECT, SELECT (f1), INSERT, UPDATE (f3, f2), TRIGGER ON `priv_db`.`t1` TO 'test_yesprivs'@'localhost'
1562
select current_user;
1564
test_yesprivs@localhost
1566
drop trigger trg1_1;
1567
create trigger trg1_3 before UPDATE on t1 for each row
1568
set new.f1 = 'trig 1_3-yes';
1569
select current_user;
1571
test_noprivs@localhost
1573
update t1 set f1 = 'update9-no',
1575
where f2 like '%yes';
1576
ERROR 42000: UPDATE command denied to user 'test_yesprivs'@'localhost' for column 'f1' in table 't1'
1577
select * from t1 order by f1,f2,f3;
1579
trig 1_1-yes NULL NULL
1580
trig 1_1-yes NULL NULL
1581
trig 1_1-yes update5-yes 10
1582
trig 1_1-yes update5-yes 20
1583
update t1 set f3= f3+1;
1584
ERROR 42000: UPDATE command denied to user 'test_yesprivs'@'localhost' for column 'f1' in table 't1'
1585
select f3 from t1 order by f3;
1591
select current_user;
1594
revoke TRIGGER on priv_db.t1 from test_yesprivs@localhost;
1595
grant UPDATE(f1),UPDATE(f2),UPDATE(f3) on priv_db.t1
1596
to test_yesprivs@localhost;
1597
show grants for test_yesprivs@localhost;
1598
Grants for test_yesprivs@localhost
1599
GRANT USAGE ON *.* TO 'test_yesprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
1600
GRANT SELECT (f1), INSERT, UPDATE ON `priv_db`.`t2` TO 'test_yesprivs'@'localhost'
1601
GRANT SELECT, SELECT (f1), INSERT, UPDATE (f3, f2, f1) ON `priv_db`.`t1` TO 'test_yesprivs'@'localhost'
1602
select current_user;
1604
test_noprivs@localhost
1606
update t1 set f3= f3+1;
1607
ERROR 42000: TRIGGER command denied to user 'test_yesprivs'@'localhost' for table 't1'
1608
select f3 from t1 order by f3;
1615
##### trigger privilege on column level? #######
1616
------------------------------------------------
1617
grant TRIGGER(f1) on priv_db.t1 to test_yesprivs@localhost;
1618
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(f1) on priv_db.t1 to test_yesprivs@localhost' at line 1
1619
select current_user;
1622
drop database if exists priv_db;
1623
drop user test_yesprivs@localhost;
1624
drop user test_noprivs@localhost;