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= memory;
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'
27
connect(localhost,test_yesprivs,PWD,test,MASTER_MYPORT,MASTER_MYSOCK);
29
no trigger privilege on db level for create:
30
--------------------------------------------
32
create trigger trg1_1 before INSERT on t1 for each row
33
set new.f1 = 'trig 1_1-no';
34
ERROR 42000: TRIGGER command denied to user 'test_yesprivs'@'localhost' for table 't1'
35
connect(localhost,test_noprivs,PWD,test,MASTER_MYPORT,MASTER_MYSOCK);
37
insert into t1 (f1) values ('insert-yes');
38
select f1 from t1 order by f1;
44
grant TRIGGER on priv_db.* to test_yesprivs@localhost;
45
show grants for test_yesprivs@localhost;
46
Grants for test_yesprivs@localhost
47
GRANT USAGE ON *.* TO 'test_yesprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
48
GRANT SELECT, TRIGGER ON `priv_db`.* TO 'test_yesprivs'@'localhost'
50
trigger privilege on db level for create:
51
-----------------------------------------
54
test_yesprivs@localhost
56
create trigger trg1_2 before INSERT on t1 for each row
57
set new.f1 = 'trig 1_2-yes';
60
test_noprivs@localhost
62
insert into t1 (f1) values ('insert-yes');
63
ERROR 42000: UPDATE command denied to user 'test_yesprivs'@'localhost' for column 'f1' in table 't1'
64
select f1 from t1 order by f1;
70
grant UPDATE on priv_db.* to test_yesprivs@localhost;
72
insert into t1 (f1) values ('insert-no');
73
select f1 from t1 order by f1;
79
test_noprivs@localhost
81
insert into t1 (f1) values ('insert-yes');
82
select f1 from t1 order by f1;
90
revoke TRIGGER on priv_db.* from test_yesprivs@localhost;
91
show grants for test_yesprivs@localhost;
92
Grants for test_yesprivs@localhost
93
GRANT USAGE ON *.* TO 'test_yesprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
94
GRANT SELECT, UPDATE ON `priv_db`.* TO 'test_yesprivs'@'localhost'
96
no trigger privilege on db level for drop:
97
------------------------------------------
100
test_yesprivs@localhost
103
ERROR 42000: TRIGGER command denied to user 'test_yesprivs'@'localhost' for table 't1'
106
test_noprivs@localhost
109
no trigger privilege at activation time:
110
----------------------------------------
111
insert into t1 (f1) values ('insert-yes');
112
ERROR 42000: TRIGGER command denied to user 'test_yesprivs'@'localhost' for table 't1'
113
select f1 from t1 order by f1;
119
trigger privilege at activation time:
120
-------------------------------------
124
grant TRIGGER on priv_db.* to test_yesprivs@localhost;
127
test_noprivs@localhost
129
insert into t1 (f1) values ('insert-no');
130
select f1 from t1 order by f1;
137
trigger privilege on db level for drop:
138
---------------------------------------
141
test_yesprivs@localhost
142
show grants for test_yesprivs@localhost;
143
Grants for test_yesprivs@localhost
144
GRANT USAGE ON *.* TO 'test_yesprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
145
GRANT SELECT, UPDATE, TRIGGER ON `priv_db`.* TO 'test_yesprivs'@'localhost'
147
ERROR 42000: TRIGGER command denied to user 'test_yesprivs'@'localhost' for table 't1'
149
takes effect after use priv_db:
150
-------------------------------
157
insert into t1 (f1) values ('insert-yes');
158
select f1 from t1 order by f1;
166
switch to db without having trigger priv for it:
167
------------------------------------------------
169
create table t1 (f1 char(20)) engine= memory;
170
grant SELECT,UPDATE on no_priv_db.* to test_yesprivs@localhost;
171
show grants for test_yesprivs@localhost;
172
Grants for test_yesprivs@localhost
173
GRANT USAGE ON *.* TO 'test_yesprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
174
GRANT SELECT, UPDATE, TRIGGER ON `priv_db`.* TO 'test_yesprivs'@'localhost'
175
GRANT SELECT, UPDATE ON `no_priv_db`.* TO 'test_yesprivs'@'localhost'
177
use db with trigger privilege on db level and without...:
178
---------------------------------------------------------
181
test_yesprivs@localhost
183
create trigger trg1_3 before INSERT on t1 for each row
184
set new.f1 = 'trig 1_3-no';
185
ERROR 42000: TRIGGER command denied to user 'test_yesprivs'@'localhost' for table 't1'
187
create trigger trg1_3 before INSERT on t1 for each row
188
set new.f1 = 'trig 1_3-yes';
190
create trigger trg1_4 before UPDATE on t1 for each row
191
set new.f1 = 'trig 1_4-no';
192
ERROR 42000: TRIGGER command denied to user 'test_yesprivs'@'localhost' for table 't1'
194
create trigger trg1_4 before UPDATE on t1 for each row
195
set new.f1 = 'trig 1_4-yes';
198
test_noprivs@localhost
200
ERROR 42000: Access denied for user 'test_noprivs'@'localhost' to database 'no_priv_db'
201
insert into t1 (f1) values ('insert-yes');
202
select f1 from t1 order by f1;
211
insert into t1 (f1) values ('insert-no');
212
select f1 from t1 order by f1;
223
test_yesprivs@localhost
226
ERROR HY000: Trigger does not exist
231
ERROR HY000: Trigger does not exist
237
drop table priv_db.t1;
238
drop table no_priv_db.t1;
239
drop database if exists priv_db;
240
drop database if exists no_priv_db;
241
drop user test_yesprivs@localhost;
242
drop user test_noprivs@localhost;
244
######### Testcase for table level: ########
245
---------------------------------------------------
246
drop database if exists priv_db;
247
create database priv_db;
249
create table t1 (f1 char(20)) engine= memory;
250
create User test_yesprivs@localhost;
251
set password for test_yesprivs@localhost = password('PWD');
252
revoke ALL PRIVILEGES, GRANT OPTION FROM test_yesprivs@localhost;
253
create User test_noprivs@localhost;
254
set password for test_noprivs@localhost = password('PWD');
255
revoke ALL PRIVILEGES, GRANT OPTION FROM test_noprivs@localhost;
256
connect(localhost,test_yesprivs,PWD,test,MASTER_MYPORT,MASTER_MYSOCK);
257
connect(localhost,test_noprivs,PWD,test,MASTER_MYPORT,MASTER_MYSOCK);
259
no trigger privilege on table level for create:
260
-----------------------------------------------
265
Trigger Event Table Statement Timing Created sql_mode Definer character_set_client collation_connection Database Collation
266
grant select, insert, update on priv_db.t1 to test_yesprivs@localhost;
267
show grants for test_yesprivs@localhost;
268
Grants for test_yesprivs@localhost
269
GRANT USAGE ON *.* TO 'test_yesprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
270
GRANT SELECT, INSERT, UPDATE ON `priv_db`.`t1` TO 'test_yesprivs'@'localhost'
271
grant select, update, insert on priv_db.t1 to test_noprivs@localhost;
272
show grants for test_noprivs@localhost;
273
Grants for test_noprivs@localhost
274
GRANT USAGE ON *.* TO 'test_noprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
275
GRANT SELECT, INSERT, UPDATE ON `priv_db`.`t1` TO 'test_noprivs'@'localhost'
278
test_yesprivs@localhost
283
create trigger trg1_1 before INSERT on t1 for each row
284
set new.f1 = 'trig 1_1-no';
285
ERROR 42000: TRIGGER command denied to user 'test_yesprivs'@'localhost' for table 't1'
288
test_noprivs@localhost
290
insert into t1 (f1) values ('insert1-yes');
291
select f1 from t1 order by f1;
298
Trigger Event Table Statement Timing Created sql_mode Definer character_set_client collation_connection Database Collation
302
insert into t1 (f1) values ('insert2-yes');
303
select f1 from t1 order by f1;
307
grant TRIGGER on priv_db.t1 to test_yesprivs@localhost;
308
show grants for test_yesprivs@localhost;
309
Grants for test_yesprivs@localhost
310
GRANT USAGE ON *.* TO 'test_yesprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
311
GRANT SELECT, INSERT, UPDATE, TRIGGER ON `priv_db`.`t1` TO 'test_yesprivs'@'localhost'
313
trigger privilege on table level for create:
314
--------------------------------------------
317
test_yesprivs@localhost
319
Trigger Event Table Statement Timing Created sql_mode Definer character_set_client collation_connection Database Collation
320
create trigger trg1_2 before INSERT on t1 for each row
321
set new.f1 = 'trig 1_2-yes';
324
test_noprivs@localhost
325
insert into t1 (f1) values ('insert3-no');
326
select f1 from t1 order by f1;
334
insert into t1 (f1) values ('insert4-no');
335
select f1 from t1 order by f1;
341
revoke TRIGGER on priv_db.t1 from test_yesprivs@localhost;
342
show grants for test_yesprivs@localhost;
343
Grants for test_yesprivs@localhost
344
GRANT USAGE ON *.* TO 'test_yesprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
345
GRANT SELECT, INSERT, UPDATE ON `priv_db`.`t1` TO 'test_yesprivs'@'localhost'
347
no trigger privilege on table level for drop:
348
---------------------------------------------
351
test_yesprivs@localhost
353
ERROR 42000: TRIGGER command denied to user 'test_yesprivs'@'localhost' for table 't1'
355
no trigger privilege at activation time:
356
----------------------------------------
359
test_noprivs@localhost
360
insert into t1 (f1) values ('insert5-no');
361
ERROR 42000: TRIGGER command denied to user 'test_yesprivs'@'localhost' for table 't1'
362
select f1 from t1 order by f1;
371
grant TRIGGER on priv_db.t1 to test_yesprivs@localhost;
373
trigger privilege at activation time:
374
-------------------------------------
377
test_noprivs@localhost
378
insert into t1 (f1) values ('insert6-no');
379
select f1 from t1 order by f1;
387
trigger privilege on table level for drop:
388
------------------------------------------
391
test_yesprivs@localhost
392
show grants for test_yesprivs@localhost;
393
Grants for test_yesprivs@localhost
394
GRANT USAGE ON *.* TO 'test_yesprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
395
GRANT SELECT, INSERT, UPDATE, TRIGGER ON `priv_db`.`t1` TO 'test_yesprivs'@'localhost'
399
test_noprivs@localhost
400
insert into t1 (f1) values ('insert7-yes');
401
select f1 from t1 order by f1;
412
insert into t1 (f1) values ('insert8-yes');
413
select f1 from t1 order by f1;
423
switch to table without having trigger priv for it:
424
---------------------------------------------------
425
create table t2 (f1 char(20)) engine= memory;
426
grant SELECT, INSERT, UPDATE on priv_db.t2 to test_yesprivs@localhost;
427
show grants for test_yesprivs@localhost;
428
Grants for test_yesprivs@localhost
429
GRANT USAGE ON *.* TO 'test_yesprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
430
GRANT SELECT, INSERT, UPDATE ON `priv_db`.`t2` TO 'test_yesprivs'@'localhost'
431
GRANT SELECT, INSERT, UPDATE, TRIGGER ON `priv_db`.`t1` TO 'test_yesprivs'@'localhost'
432
grant SELECT, INSERT, UPDATE on priv_db.t2 to test_noprivs@localhost;
433
show grants for test_noprivs@localhost;
434
Grants for test_noprivs@localhost
435
GRANT USAGE ON *.* TO 'test_noprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
436
GRANT SELECT, INSERT, UPDATE ON `priv_db`.`t1` TO 'test_noprivs'@'localhost'
437
GRANT SELECT, INSERT, UPDATE ON `priv_db`.`t2` TO 'test_noprivs'@'localhost'
439
use table with trigger privilege and without...:
440
------------------------------------------------
443
test_yesprivs@localhost
444
create trigger trg2_1 before INSERT on t2 for each row
445
set new.f1 = 'trig 2_1-no';
446
ERROR 42000: TRIGGER command denied to user 'test_yesprivs'@'localhost' for table 't2'
447
create trigger trg1_3 before INSERT on t1 for each row
448
set new.f1 = 'trig 1_3-yes';
449
create trigger trg2_2 before UPDATE on t2 for each row
450
set new.f1 = 'trig 2_2-no';
451
ERROR 42000: TRIGGER command denied to user 'test_yesprivs'@'localhost' for table 't2'
452
create trigger trg1_4 before UPDATE on t1 for each row
453
set new.f1 = 'trig 1_4-yes';
455
Trigger Event Table Statement Timing Created sql_mode Definer character_set_client collation_connection Database Collation
456
trg1_3 INSERT t1 set new.f1 = 'trig 1_3-yes' BEFORE NULL test_yesprivs@localhost latin1 latin1_swedish_ci latin1_swedish_ci
457
trg1_4 UPDATE t1 set new.f1 = 'trig 1_4-yes' BEFORE NULL test_yesprivs@localhost latin1 latin1_swedish_ci latin1_swedish_ci
460
test_noprivs@localhost
461
insert into t2 (f1) values ('insert9-yes');
462
select f1 from t2 order by f1;
465
insert into t1 (f1) values ('insert10-no');
466
select f1 from t1 order by f1;
478
test_yesprivs@localhost
480
ERROR HY000: Trigger does not exist
483
ERROR HY000: Trigger does not exist
488
drop database if exists priv_db;
489
drop user test_yesprivs@localhost;
490
drop user test_noprivs@localhost;
492
#### Testcase for mix of user(global) and db level: ####
493
--------------------------------------------------------
494
drop database if exists priv_db;
495
drop database if exists no_priv_db;
496
create database priv_db;
497
create database no_priv_db;
499
create table t1 (f1 char(20)) engine= memory;
501
create table t1 (f1 char(20)) engine= memory;
502
create User test_yesprivs@localhost;
503
set password for test_yesprivs@localhost = password('PWD');
504
revoke ALL PRIVILEGES, GRANT OPTION FROM test_yesprivs@localhost;
505
grant ALL on *.* to test_yesprivs@localhost;
506
show grants for test_yesprivs@localhost;
507
Grants for test_yesprivs@localhost
508
GRANT ALL PRIVILEGES ON *.* TO 'test_yesprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
509
create User test_noprivs@localhost;
510
set password for test_noprivs@localhost = password('PWD');
511
revoke ALL PRIVILEGES, GRANT OPTION FROM test_noprivs@localhost;
512
grant SELECT,INSERT on *.* to test_noprivs@localhost;
513
show grants for test_noprivs@localhost;
514
Grants for test_noprivs@localhost
515
GRANT SELECT, INSERT ON *.* TO 'test_noprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
516
connect(localhost,test_yesprivs,PWD,test,MASTER_MYPORT,MASTER_MYSOCK);
517
connect(localhost,test_noprivs,PWD,test,MASTER_MYPORT,MASTER_MYSOCK);
520
test_yesprivs@localhost
522
trigger privilege on user level for create:
523
-------------------------------------------
525
create trigger trg1_1 before INSERT on t1 for each row
526
set new.f1 = 'trig 1_1-yes';
527
insert into t1 (f1) values ('insert-no');
528
select f1 from t1 order by f1;
532
create trigger priv_db.trg1_5 before UPDATE on priv_db.t1
534
set new.f1 = 'trig 1_5-yes';
535
insert into priv_db.t1 (f1) values ('insert-no');
536
select f1 from priv_db.t1 order by f1;
540
drop trigger priv_db.trg1_5;
543
test_noprivs@localhost
545
insert into t1 (f1) values ('insert-no');
546
select f1 from t1 order by f1;
555
insert into t1 (f1) values ('insert-no');
556
select f1 from t1 order by f1;
562
revoke TRIGGER on *.* from test_yesprivs@localhost;
563
show grants for test_yesprivs@localhost;
564
Grants for test_yesprivs@localhost
565
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_yesprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
566
connect(localhost,test_yesprivs,PWD,test,MASTER_MYPORT,MASTER_MYSOCK);
569
test_yesprivs@localhost
572
Trigger Event Table Statement Timing Created sql_mode Definer character_set_client collation_connection Database Collation
573
select * from information_schema.triggers;
574
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
576
ERROR 42000: TRIGGER command denied to user 'test_yesprivs'@'localhost' for table 't1'
581
Grants for root@localhost
582
GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION
586
no trigger privilege on db level for create:
587
--------------------------------------------
590
test_yesprivs@localhost
591
create trigger trg1_1 before INSERT on t1 for each row
592
set new.f1 = 'trig 1_1-no';
593
ERROR 42000: TRIGGER command denied to user 'test_yesprivs'@'localhost' for table 't1'
596
test_noprivs@localhost
598
insert into t1 (f1) values ('insert-yes');
599
select f1 from t1 order by f1;
609
grant TRIGGER on priv_db.* to test_yesprivs@localhost;
610
show grants for test_yesprivs@localhost;
611
Grants for test_yesprivs@localhost
612
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_yesprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
613
GRANT TRIGGER ON `priv_db`.* TO 'test_yesprivs'@'localhost'
615
trigger privilege on db level for create:
616
-----------------------------------------
619
test_yesprivs@localhost
621
create trigger trg1_2 before INSERT on t1 for each row
622
set new.f1 = 'trig 1_2-yes';
623
create trigger no_priv_db.trg1_9 before insert on no_priv_db.t1
625
set new.f1 = 'trig 1_9-yes';
626
ERROR 42000: TRIGGER command denied to user 'test_yesprivs'@'localhost' for table 't1'
628
create trigger trg1_2 before INSERT on t1 for each row
629
set new.f1 = 'trig 1_2-no';
630
ERROR 42000: TRIGGER command denied to user 'test_yesprivs'@'localhost' for table 't1'
631
create trigger priv_db.trg1_9 before UPDATE on priv_db.t1
633
set new.f1 = 'trig 1_9-yes';
636
test_noprivs@localhost
638
insert into t1 (f1) values ('insert-yes');
639
select f1 from t1 order by f1;
648
insert into t1 (f1) values ('insert-yes');
649
select f1 from t1 order by f1;
652
drop trigger priv_db.trg1_9;
653
ERROR 42000: TRIGGER command denied to user 'test_noprivs'@'localhost' for table 't1'
657
drop trigger priv_db.trg1_9;
658
revoke TRIGGER on priv_db.* from test_yesprivs@localhost;
660
insert into t1 (f1) values ('insert-yes');
661
ERROR 42000: TRIGGER command denied to user 'test_yesprivs'@'localhost' for table 't1'
662
select f1 from t1 order by f1;
670
grant TRIGGER on *.* to test_yesprivs@localhost;
671
show grants for test_yesprivs@localhost;
672
Grants for test_yesprivs@localhost
673
GRANT ALL PRIVILEGES ON *.* TO 'test_yesprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
676
test_yesprivs@localhost
678
create trigger trg1_2 before INSERT on t1 for each row
679
set new.f1 = 'trig 1_2-no';
680
ERROR 42000: TRIGGER command denied to user 'test_yesprivs'@'localhost' for table 't1'
683
test_noprivs@localhost
685
insert into t1 (f1) values ('insert-no');
686
select f1 from t1 order by f1;
696
insert into t1 (f1) values ('insert-yes');
697
select f1 from t1 order by f1;
701
connect(localhost,test_yesprivs,PWD,test,MASTER_MYPORT,MASTER_MYSOCK);
704
test_yesprivs@localhost
706
create trigger trg1_2 before INSERT on t1 for each row
707
set new.f1 = 'trig 1_2-yes';
710
test_noprivs@localhost
712
insert into t1 (f1) values ('insert-no');
713
select f1 from t1 order by f1;
724
insert into t1 (f1) values ('insert-no');
725
select f1 from t1 order by f1;
733
drop database if exists priv_db;
734
drop database if exists no_priv_db;
735
drop database if exists h1;
736
drop user test_yesprivs@localhost;
737
drop user test_noprivs@localhost;
739
####### Testcase for mix of db and table level: #######
740
-------------------------------------------------------
741
drop database if exists priv1_db;
742
drop database if exists priv2_db;
743
create database priv1_db;
744
create database priv2_db;
746
create table t1 (f1 char(20)) engine= memory;
747
create table t2 (f1 char(20)) engine= memory;
749
create table t1 (f1 char(20)) engine= memory;
750
create User test_yesprivs@localhost;
751
set password for test_yesprivs@localhost = password('PWD');
752
revoke ALL PRIVILEGES, GRANT OPTION FROM test_yesprivs@localhost;
753
grant ALL on priv1_db.* to test_yesprivs@localhost;
754
grant SELECT,UPDATE on priv2_db.* to test_yesprivs@localhost;
755
show grants for test_yesprivs@localhost;
756
Grants for test_yesprivs@localhost
757
GRANT USAGE ON *.* TO 'test_yesprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
758
GRANT ALL PRIVILEGES ON `priv1_db`.* TO 'test_yesprivs'@'localhost'
759
GRANT SELECT, UPDATE ON `priv2_db`.* TO 'test_yesprivs'@'localhost'
760
create User test_noprivs@localhost;
761
set password for test_noprivs@localhost = password('PWD');
762
revoke ALL PRIVILEGES, GRANT OPTION FROM test_noprivs@localhost;
763
grant SELECT,INSERT,UPDATE on priv1_db.* to test_noprivs@localhost;
764
grant SELECT,INSERT on priv2_db.* to test_noprivs@localhost;
765
show grants for test_noprivs@localhost;
766
Grants for test_noprivs@localhost
767
GRANT USAGE ON *.* TO 'test_noprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
768
GRANT SELECT, INSERT, UPDATE ON `priv1_db`.* TO 'test_noprivs'@'localhost'
769
GRANT SELECT, INSERT ON `priv2_db`.* TO 'test_noprivs'@'localhost'
770
connect(localhost,test_yesprivs,PWD,test,MASTER_MYPORT,MASTER_MYSOCK);
772
connect(localhost,test_noprivs,PWD,test,MASTER_MYPORT,MASTER_MYSOCK);
775
trigger privilege on one db1 db level, not on db2
776
-------------------------------------------------
779
test_yesprivs@localhost
781
create trigger trg1_1 before INSERT on t1 for each row
782
set new.f1 = 'trig 1_1-yes';
783
create trigger trg2_1 before INSERT on t2 for each row
784
set new.f1 = 'trig 2_1-yes';
786
create trigger trg1_1 before INSERT on t1 for each row
787
set new.f1 = 'trig1_1-yes';
788
ERROR 42000: TRIGGER command denied to user 'test_yesprivs'@'localhost' for table 't1'
791
test_noprivs@localhost
792
insert into t1 (f1) values ('insert1_no');
793
select f1 from t1 order by f1;
796
insert into t2 (f1) values ('insert1_no');
797
select f1 from t2 order by f1;
800
insert into priv2_db.t1 (f1) values ('insert21-yes');
801
select f1 from priv2_db.t1 order by f1;
805
insert into t1 (f1) values ('insert1_yes');
806
select f1 from t1 order by f1;
810
insert into priv1_db.t1 (f1) values ('insert11-no');
811
select f1 from priv1_db.t1 order by f1;
815
insert into priv1_db.t2 (f1) values ('insert22-no');
816
select f1 from priv1_db.t2 order by f1;
821
revoke trigger privilege on table level (not existing)
822
------------------------------------------------------
827
revoke TRIGGER on priv1_db.t1 from test_yesprivs@localhost;
828
ERROR 42000: There is no such grant defined for user 'test_yesprivs' on host 'localhost' on table 't1'
829
show grants for test_yesprivs@localhost;
830
Grants for test_yesprivs@localhost
831
GRANT USAGE ON *.* TO 'test_yesprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
832
GRANT ALL PRIVILEGES ON `priv1_db`.* TO 'test_yesprivs'@'localhost'
833
GRANT SELECT, UPDATE ON `priv2_db`.* TO 'test_yesprivs'@'localhost'
836
test_yesprivs@localhost
838
ERROR HY000: Trigger does not exist
840
ERROR HY000: Trigger does not exist
848
revoke TRIGGER on priv1_db.* from test_yesprivs@localhost;
850
no trigger privilege on table level for create:
851
-----------------------------------------------
854
test_yesprivs@localhost
856
create trigger trg1_1 before INSERT on t1 for each row
857
set new.f1 = 'trig 1_1-no';
858
ERROR 42000: TRIGGER command denied to user 'test_yesprivs'@'localhost' for table 't1'
863
Trigger Event Table Statement Timing Created sql_mode Definer character_set_client collation_connection Database Collation
864
grant TRIGGER on priv1_db.t1 to test_yesprivs@localhost;
865
show grants for test_yesprivs@localhost;
866
Grants for test_yesprivs@localhost
867
GRANT USAGE ON *.* TO 'test_yesprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
868
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'
869
GRANT SELECT, UPDATE ON `priv2_db`.* TO 'test_yesprivs'@'localhost'
870
GRANT TRIGGER ON `priv1_db`.`t1` TO 'test_yesprivs'@'localhost'
872
trigger privilege on table level for create:
873
--------------------------------------------
876
test_yesprivs@localhost
878
Trigger Event Table Statement Timing Created sql_mode Definer character_set_client collation_connection Database Collation
879
create trigger trg1_2 before INSERT on t1 for each row
880
set new.f1 = 'trig 1_2-yes';
881
create trigger trg2_1 before INSERT on t2 for each row
882
set new.f1 = 'trig 2_1-no';
883
ERROR 42000: TRIGGER command denied to user 'test_yesprivs'@'localhost' for table 't2'
886
test_noprivs@localhost
888
insert into t1 (f1) values ('insert2-no');
889
select f1 from t1 order by f1;
894
insert into t2 (f1) values ('insert2-yes');
895
select f1 from t2 order by f1;
900
insert into priv2_db.t1 (f1) values ('insert22-yes');
901
select f1 from priv2_db.t1 order by f1;
909
grant TRIGGER on priv1_db.* to test_yesprivs@localhost;
910
show grants for test_yesprivs@localhost;
911
Grants for test_yesprivs@localhost
912
GRANT USAGE ON *.* TO 'test_yesprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
913
GRANT ALL PRIVILEGES ON `priv1_db`.* TO 'test_yesprivs'@'localhost'
914
GRANT SELECT, UPDATE ON `priv2_db`.* TO 'test_yesprivs'@'localhost'
915
GRANT TRIGGER ON `priv1_db`.`t1` TO 'test_yesprivs'@'localhost'
918
test_yesprivs@localhost
919
create trigger trg2_1 before INSERT on t2 for each row
920
set new.f1 = 'trig 2_1-yes';
921
ERROR 42000: TRIGGER command denied to user 'test_yesprivs'@'localhost' for table 't2'
923
create trigger trg2_1 before INSERT on t2 for each row
924
set new.f1 = 'trig 2_1-yes';
927
test_noprivs@localhost
929
insert into t1 (f1) values ('insert3-no');
930
select f1 from t1 order by f1;
936
insert into t2 (f1) values ('insert3-no');
937
select f1 from t2 order by f1;
944
insert into priv1_db.t1 (f1) values ('insert12-no');
945
select f1 from priv1_db.t1 order by f1;
952
insert into priv1_db.t2 (f1) values ('insert23-no');
953
select f1 from priv1_db.t2 order by f1;
962
test_yesprivs@localhost
968
drop database if exists priv1_db;
969
drop database if exists priv2_db;
970
drop user test_yesprivs@localhost;
971
drop user test_noprivs@localhost;
973
#### Testcase for trigger privilege on execution time ########
974
--------------------------------------------------------------
975
drop database if exists priv_db;
976
create database priv_db;
978
create table t1 (f1 char(20)) engine= memory;
979
create User test_yesprivs@localhost;
980
set password for test_yesprivs@localhost = password('PWD');
981
create User test_useprivs@localhost;
982
set password for test_useprivs@localhost = password('PWD');
983
revoke ALL PRIVILEGES, GRANT OPTION FROM test_yesprivs@localhost;
984
revoke ALL PRIVILEGES, GRANT OPTION FROM test_useprivs@localhost;
985
connect(localhost,test_yesprivs,PWD,test,MASTER_MYPORT,MASTER_MYSOCK);
990
Trigger Event Table Statement Timing Created sql_mode Definer character_set_client collation_connection Database Collation
991
grant select, insert, update ,trigger
992
on priv_db.t1 to test_yesprivs@localhost
995
on priv_db.t1 to test_useprivs@localhost;
996
show grants for test_yesprivs@localhost;
997
Grants for test_yesprivs@localhost
998
GRANT USAGE ON *.* TO 'test_yesprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
999
GRANT SELECT, INSERT, UPDATE, TRIGGER ON `priv_db`.`t1` TO 'test_yesprivs'@'localhost' WITH GRANT OPTION
1000
select current_user;
1002
test_yesprivs@localhost
1004
create trigger trg1_1 before INSERT on t1 for each row
1005
set new.f1 = 'trig 1_1-yes';
1006
grant insert on t1 to test_useprivs@localhost;
1007
prepare ins1 from 'insert into t1 (f1) values (''insert1-no'')';
1009
select f1 from t1 order by f1;
1012
prepare ins1 from 'insert into t1 (f1) values (''insert2-no'')';
1013
connect(localhost,test_useprivs,PWD,test,MASTER_MYPORT,MASTER_MYSOCK);
1014
select current_user;
1016
test_useprivs@localhost
1018
prepare ins1 from 'insert into t1 (f1) values (''insert3-no'')';
1020
select f1 from t1 order by f1;
1024
select current_user;
1027
revoke TRIGGER on priv_db.t1 from test_yesprivs@localhost;
1028
show grants for test_yesprivs@localhost;
1029
Grants for test_yesprivs@localhost
1030
GRANT USAGE ON *.* TO 'test_yesprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
1031
GRANT SELECT, INSERT, UPDATE ON `priv_db`.`t1` TO 'test_yesprivs'@'localhost' WITH GRANT OPTION
1032
select current_user;
1034
test_yesprivs@localhost
1036
ERROR 42000: TRIGGER command denied to user 'test_yesprivs'@'localhost' for table 't1'
1037
select f1 from t1 order by f1;
1041
prepare ins1 from 'insert into t1 (f1) values (''insert4-no'')';
1042
select current_user;
1044
test_useprivs@localhost
1045
prepare ins1 from 'insert into t1 (f1) values (''insert5-no'')';
1047
ERROR 42000: TRIGGER command denied to user 'test_yesprivs'@'localhost' for table 't1'
1048
select f1 from t1 order by f1;
1052
select current_user;
1055
grant TRIGGER on priv_db.t1 to test_yesprivs@localhost;
1056
show grants for test_yesprivs@localhost;
1057
Grants for test_yesprivs@localhost
1058
GRANT USAGE ON *.* TO 'test_yesprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
1059
GRANT SELECT, INSERT, UPDATE, TRIGGER ON `priv_db`.`t1` TO 'test_yesprivs'@'localhost' WITH GRANT OPTION
1060
select current_user;
1062
test_yesprivs@localhost
1064
select f1 from t1 order by f1;
1069
prepare ins1 from 'insert into t1 (f1) values (''insert6-no'')';
1070
select current_user;
1072
test_useprivs@localhost
1074
select f1 from t1 order by f1;
1080
prepare ins1 from 'insert into t1 (f1) values (''insert7-no'')';
1081
select current_user;
1084
revoke TRIGGER on priv_db.t1 from test_yesprivs@localhost;
1085
show grants for test_yesprivs@localhost;
1086
Grants for test_yesprivs@localhost
1087
GRANT USAGE ON *.* TO 'test_yesprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
1088
GRANT SELECT, INSERT, UPDATE ON `priv_db`.`t1` TO 'test_yesprivs'@'localhost' WITH GRANT OPTION
1089
select current_user;
1091
test_yesprivs@localhost
1093
ERROR 42000: TRIGGER command denied to user 'test_yesprivs'@'localhost' for table 't1'
1094
select f1 from t1 order by f1;
1100
select current_user;
1102
test_useprivs@localhost
1104
ERROR 42000: TRIGGER command denied to user 'test_yesprivs'@'localhost' for table 't1'
1105
select f1 from t1 order by f1;
1111
select current_user;
1114
grant TRIGGER on priv_db.t1 to test_yesprivs@localhost;
1115
show grants for test_yesprivs@localhost;
1116
Grants for test_yesprivs@localhost
1117
GRANT USAGE ON *.* TO 'test_yesprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
1118
GRANT SELECT, INSERT, UPDATE, TRIGGER ON `priv_db`.`t1` TO 'test_yesprivs'@'localhost' WITH GRANT OPTION
1119
select current_user;
1121
test_yesprivs@localhost
1123
select f1 from t1 order by f1;
1130
select current_user;
1132
test_useprivs@localhost
1134
select f1 from t1 order by f1;
1142
select current_user;
1145
revoke TRIGGER on priv_db.t1 from test_yesprivs@localhost;
1146
show grants for test_yesprivs@localhost;
1147
Grants for test_yesprivs@localhost
1148
GRANT USAGE ON *.* TO 'test_yesprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
1149
GRANT SELECT, INSERT, UPDATE ON `priv_db`.`t1` TO 'test_yesprivs'@'localhost' WITH GRANT OPTION
1150
select current_user;
1152
test_yesprivs@localhost
1154
ERROR 42000: TRIGGER command denied to user 'test_yesprivs'@'localhost' for table 't1'
1155
select f1 from t1 order by f1;
1163
deallocate prepare ins1;
1164
select current_user;
1166
test_useprivs@localhost
1168
ERROR 42000: TRIGGER command denied to user 'test_yesprivs'@'localhost' for table 't1'
1169
select f1 from t1 order by f1;
1177
deallocate prepare ins1;
1178
select current_user;
1181
grant TRIGGER on priv_db.t1 to test_yesprivs@localhost;
1182
show grants for test_yesprivs@localhost;
1183
Grants for test_yesprivs@localhost
1184
GRANT USAGE ON *.* TO 'test_yesprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
1185
GRANT SELECT, INSERT, UPDATE, TRIGGER ON `priv_db`.`t1` TO 'test_yesprivs'@'localhost' WITH GRANT OPTION
1186
select current_user;
1188
test_yesprivs@localhost
1189
drop trigger trg1_1;
1190
select current_user;
1193
select current_user;
1196
drop database if exists priv_db;
1197
drop user test_yesprivs@localhost;
1198
drop user test_useprivs@localhost;
1200
######### Testcase for definer: ########
1201
-----------------------------------------------
1202
drop database if exists priv_db;
1203
create database priv_db;
1205
create table t1 (f1 char(20)) engine= memory;
1206
create User test_yesprivs@localhost;
1207
set password for test_yesprivs@localhost = password('PWD');
1208
revoke ALL PRIVILEGES, GRANT OPTION FROM test_yesprivs@localhost;
1209
connect(localhost,test_yesprivs,PWD,test,MASTER_MYPORT,MASTER_MYSOCK);
1210
select current_user;
1213
create definer=not_ex_user@localhost trigger trg1_0
1214
before INSERT on t1 for each row
1215
set new.f1 = 'trig 1_0-yes';
1217
Note 1449 There is no 'not_ex_user'@'localhost' registered
1218
drop trigger trg1_0;
1219
create definer=test_yesprivs@localhost trigger trg1_0
1220
before INSERT on t1 for each row
1221
set new.f1 = 'trig 1_0-yes';
1222
grant select, insert, update
1223
on priv_db.t1 to test_yesprivs@localhost;
1224
select current_user;
1226
test_yesprivs@localhost
1228
insert into t1 (f1) values ('insert-no');
1229
ERROR 42000: TRIGGER command denied to user 'test_yesprivs'@'localhost' for table 't1'
1230
select f1 from t1 order by f1;
1232
drop trigger trg1_0;
1233
ERROR 42000: TRIGGER command denied to user 'test_yesprivs'@'localhost' for table 't1'
1234
select current_user;
1237
grant select, insert, update ,trigger
1238
on priv_db.t1 to test_yesprivs@localhost;
1239
show grants for test_yesprivs@localhost;
1240
Grants for test_yesprivs@localhost
1241
GRANT USAGE ON *.* TO 'test_yesprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
1242
GRANT SELECT, INSERT, UPDATE, TRIGGER ON `priv_db`.`t1` TO 'test_yesprivs'@'localhost'
1243
select current_user;
1245
test_yesprivs@localhost
1246
insert into t1 (f1) values ('insert-no');
1247
select f1 from t1 order by f1;
1250
drop trigger trg1_0;
1251
create definer=not_ex_user@localhost trigger trg1_0
1252
before INSERT on t1 for each row
1253
set new.f1 = 'trig 1_0-yes';
1254
ERROR 42000: Access denied; you need the SUPER privilege for this operation
1255
create definer=current_user trigger trg1_1
1256
before INSERT on t1 for each row
1257
set new.f1 = 'trig 1_1-yes';
1258
insert into t1 (f1) values ('insert-no');
1259
select f1 from t1 order by f1;
1263
create definer=test_yesprivs@localhost trigger trg1_2
1264
before UPDATE on t1 for each row
1265
set new.f1 = 'trig 1_2-yes';
1266
update t1 set f1 = 'update-yes' where f1 like '%trig%';
1267
select f1 from t1 order by f1;
1271
select current_user;
1274
grant trigger on priv_db.* to test_yesprivs@localhost
1276
select current_user;
1278
test_yesprivs@localhost
1280
Grants for test_yesprivs@localhost
1281
GRANT USAGE ON *.* TO 'test_yesprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
1282
GRANT TRIGGER ON `priv_db`.* TO 'test_yesprivs'@'localhost' WITH GRANT OPTION
1283
GRANT SELECT, INSERT, UPDATE, TRIGGER ON `priv_db`.`t1` TO 'test_yesprivs'@'localhost'
1284
create definer=not_ex_user@localhost trigger trg1_3
1285
after UPDATE on t1 for each row
1286
set @var1 = 'trig 1_3-yes';
1287
ERROR 42000: Access denied; you need the SUPER privilege for this operation
1288
select current_user;
1291
select current_user;
1294
drop database if exists priv_db;
1295
drop user test_yesprivs@localhost;
1297
####### Testcase for column privileges of triggers: #######
1298
-----------------------------------------------------------
1299
drop database if exists priv_db;
1300
drop database if exists no_priv_db;
1301
create database priv_db;
1303
create table t1 (f1 char(20)) engine= memory;
1304
create table t2 (f1 char(20)) engine= memory;
1305
create User test_yesprivs@localhost;
1306
set password for test_yesprivs@localhost = password('PWD');
1307
revoke ALL PRIVILEGES, GRANT OPTION FROM test_yesprivs@localhost;
1308
grant TRIGGER on priv_db.* to test_yesprivs@localhost;
1309
show grants for test_yesprivs@localhost;
1310
Grants for test_yesprivs@localhost
1311
GRANT USAGE ON *.* TO 'test_yesprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
1312
GRANT TRIGGER ON `priv_db`.* TO 'test_yesprivs'@'localhost'
1313
create User test_noprivs@localhost;
1314
set password for test_noprivs@localhost = password('PWD');
1315
revoke ALL PRIVILEGES, GRANT OPTION FROM test_noprivs@localhost;
1316
grant SELECT,UPDATE on priv_db.* to test_noprivs@localhost;
1317
show grants for test_noprivs@localhost;
1318
Grants for test_noprivs@localhost
1319
GRANT USAGE ON *.* TO 'test_noprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
1320
GRANT SELECT, UPDATE ON `priv_db`.* TO 'test_noprivs'@'localhost'
1321
connect(localhost,test_yesprivs,PWD,test,MASTER_MYPORT,MASTER_MYSOCK);
1322
connect(localhost,test_noprivs,PWD,test,MASTER_MYPORT,MASTER_MYSOCK);
1324
update only on column:
1325
----------------------
1326
select current_user;
1329
grant SELECT(f1),INSERT,UPDATE(f1) on priv_db.t1
1330
to test_yesprivs@localhost;
1331
grant SELECT(f1),INSERT,UPDATE(f1) on priv_db.t2
1332
to test_yesprivs@localhost;
1333
select current_user;
1335
test_yesprivs@localhost
1337
insert into t1 (f1) values ('insert1-yes');
1338
insert into t2 (f1) values ('insert1-yes');
1339
create trigger trg1_1 before UPDATE on t1 for each row
1340
set new.f1 = 'trig 1_1-yes';
1341
create trigger trg2_1 before UPDATE on t2 for each row
1342
set new.f1 = 'trig 2_1-yes';
1343
select current_user;
1345
test_noprivs@localhost
1347
select f1 from t1 order by f1;
1350
update t1 set f1 = 'update1_no'
1351
where f1 like '%insert%';
1352
select f1 from t1 order by f1;
1355
select f1 from t2 order by f1;
1358
update t2 set f1 = 'update1_no'
1359
where f1 like '%insert%';
1360
select f1 from t2 order by f1;
1363
select current_user;
1366
revoke UPDATE on priv_db.*
1367
from test_yesprivs@localhost;
1368
revoke UPDATE(f1) on priv_db.t2
1369
from test_yesprivs@localhost;
1370
show grants for test_yesprivs@localhost;
1371
Grants for test_yesprivs@localhost
1372
GRANT USAGE ON *.* TO 'test_yesprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
1373
GRANT TRIGGER ON `priv_db`.* TO 'test_yesprivs'@'localhost'
1374
GRANT SELECT (f1), INSERT ON `priv_db`.`t2` TO 'test_yesprivs'@'localhost'
1375
GRANT SELECT (f1), INSERT, UPDATE (f1) ON `priv_db`.`t1` TO 'test_yesprivs'@'localhost'
1376
select current_user;
1378
test_yesprivs@localhost
1380
insert into t1 (f1) values ('insert2-yes');
1381
insert into t2 (f1) values ('insert2-yes');
1382
select current_user;
1384
test_noprivs@localhost
1386
update t1 set f1 = 'update2_no'
1387
where f1 like '%insert%';
1388
update t2 set f1 = 'update2_no'
1389
where f1 like '%insert%';
1390
ERROR 42000: UPDATE command denied to user 'test_yesprivs'@'localhost' for column 'f1' in table 't2'
1391
update t1 set f1 = 'update3_no'
1392
where f1 like '%insert%';
1393
update t2 set f1 = 'update3_no'
1394
where f1 like '%insert%';
1395
ERROR 42000: UPDATE command denied to user 'test_yesprivs'@'localhost' for column 'f1' in table 't2'
1396
select f1 from t1 order by f1;
1400
select f1 from t2 order by f1;
1405
check if access only on one of three columns
1406
--------------------------------------------
1407
select current_user;
1410
alter table priv_db.t1 add f2 char(20), add f3 int;
1411
revoke TRIGGER on priv_db.* from test_yesprivs@localhost;
1412
grant TRIGGER,SELECT on priv_db.t1 to test_yesprivs@localhost;
1413
grant UPDATE on priv_db.t2 to test_yesprivs@localhost;
1414
select current_user;
1416
test_yesprivs@localhost
1418
insert into t1 values ('insert2-yes','insert2-yes',1);
1419
insert into t1 values ('insert3-yes','insert3-yes',2);
1420
select * from t1 order by f1;
1422
insert2-yes insert2-yes 1
1423
insert3-yes insert3-yes 2
1424
trig 1_1-yes NULL NULL
1425
trig 1_1-yes NULL NULL
1426
select current_user;
1428
test_noprivs@localhost
1430
update t1 set f1 = 'update4-no',
1433
where f2 like '%yes';
1434
select * from t1 order by f1,f2,f3;
1436
trig 1_1-yes NULL NULL
1437
trig 1_1-yes NULL NULL
1438
trig 1_1-yes update4-yes 10
1439
trig 1_1-yes update4-yes 20
1440
select current_user;
1442
test_yesprivs@localhost
1443
create trigger trg1_2 after UPDATE on t1 for each row
1444
set @f2 = 'trig 1_2-yes';
1445
select current_user;
1447
test_noprivs@localhost
1448
update t1 set f1 = 'update5-yes',
1450
where f2 like '%yes';
1451
select * from t1 order by f1,f2,f3;
1453
trig 1_1-yes NULL NULL
1454
trig 1_1-yes NULL NULL
1455
trig 1_1-yes update5-yes 10
1456
trig 1_1-yes update5-yes 20
1460
update t1 set f1 = 'update6_no'
1461
where f1 like '%insert%';
1462
update t2 set f1 = 'update6_no'
1463
where f1 like '%insert%';
1464
ERROR 42000: TRIGGER command denied to user 'test_yesprivs'@'localhost' for table 't2'
1465
update t1 set f1 = 'update7_no'
1466
where f1 like '%insert%';
1467
update t2 set f1 = 'update7_no'
1468
where f1 like '%insert%';
1469
ERROR 42000: TRIGGER command denied to user 'test_yesprivs'@'localhost' for table 't2'
1470
select f1 from t1 order by f1;
1476
select f1 from t2 order by f1;
1481
check if rejected without trigger privilege:
1482
--------------------------------------------
1483
select current_user;
1486
revoke TRIGGER on priv_db.t1 from test_yesprivs@localhost;
1487
select current_user;
1489
test_noprivs@localhost
1490
update t1 set f1 = 'update8-no',
1492
where f2 like '%yes';
1493
ERROR 42000: TRIGGER command denied to user 'test_yesprivs'@'localhost' for table 't1'
1494
select * from t1 order by f1,f2,f3;
1496
trig 1_1-yes NULL NULL
1497
trig 1_1-yes NULL NULL
1498
trig 1_1-yes update5-yes 10
1499
trig 1_1-yes update5-yes 20
1504
check trigger, but not update privilege on column:
1505
--------------------------------------------------
1506
select current_user;
1509
revoke UPDATE(f1) on priv_db.t1 from test_yesprivs@localhost;
1510
grant TRIGGER,UPDATE(f2),UPDATE(f3) on priv_db.t1
1511
to test_yesprivs@localhost;
1512
show grants for test_yesprivs@localhost;
1513
Grants for test_yesprivs@localhost
1514
GRANT USAGE ON *.* TO 'test_yesprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
1515
GRANT SELECT (f1), INSERT, UPDATE ON `priv_db`.`t2` TO 'test_yesprivs'@'localhost'
1516
GRANT SELECT, SELECT (f1), INSERT, UPDATE (f3, f2), TRIGGER ON `priv_db`.`t1` TO 'test_yesprivs'@'localhost'
1517
select current_user;
1519
test_yesprivs@localhost
1521
drop trigger trg1_1;
1522
create trigger trg1_3 before UPDATE on t1 for each row
1523
set new.f1 = 'trig 1_3-yes';
1524
select current_user;
1526
test_noprivs@localhost
1528
update t1 set f1 = 'update9-no',
1530
where f2 like '%yes';
1531
ERROR 42000: UPDATE command denied to user 'test_yesprivs'@'localhost' for column 'f1' in table 't1'
1532
select * from t1 order by f1,f2,f3;
1534
trig 1_1-yes NULL NULL
1535
trig 1_1-yes NULL NULL
1536
trig 1_1-yes update5-yes 10
1537
trig 1_1-yes update5-yes 20
1538
update t1 set f3= f3+1;
1539
ERROR 42000: UPDATE command denied to user 'test_yesprivs'@'localhost' for column 'f1' in table 't1'
1540
select f3 from t1 order by f3;
1546
select current_user;
1549
revoke TRIGGER on priv_db.t1 from test_yesprivs@localhost;
1550
grant UPDATE(f1),UPDATE(f2),UPDATE(f3) on priv_db.t1
1551
to test_yesprivs@localhost;
1552
show grants for test_yesprivs@localhost;
1553
Grants for test_yesprivs@localhost
1554
GRANT USAGE ON *.* TO 'test_yesprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
1555
GRANT SELECT (f1), INSERT, UPDATE ON `priv_db`.`t2` TO 'test_yesprivs'@'localhost'
1556
GRANT SELECT, SELECT (f1), INSERT, UPDATE (f3, f2, f1) ON `priv_db`.`t1` TO 'test_yesprivs'@'localhost'
1557
select current_user;
1559
test_noprivs@localhost
1561
update t1 set f3= f3+1;
1562
ERROR 42000: TRIGGER command denied to user 'test_yesprivs'@'localhost' for table 't1'
1563
select f3 from t1 order by f3;
1570
##### trigger privilege on column level? #######
1571
------------------------------------------------
1572
grant TRIGGER(f1) on priv_db.t1 to test_yesprivs@localhost;
1573
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
1574
select current_user;
1577
drop database if exists priv_db;
1578
drop user test_yesprivs@localhost;
1579
drop user test_noprivs@localhost;