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= falcon;
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= falcon;
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= falcon;
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= falcon;
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= falcon;
501
create table t1 (f1 char(20)) engine= falcon;
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= falcon;
747
create table t2 (f1 char(20)) engine= falcon;
749
create table t1 (f1 char(20)) engine= falcon;
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= falcon;
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= falcon;
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 transactions: ########
1298
----------------------------------------------------
1299
drop database if exists priv_db;
1300
create database priv_db;
1302
create table t1 (f1 char(20)) engine= falcon;
1303
create User test_yesprivs@localhost;
1304
set password for test_yesprivs@localhost = password('PWD');
1305
revoke ALL PRIVILEGES, GRANT OPTION FROM test_yesprivs@localhost;
1306
connect(localhost,test_yesprivs,PWD,test,MASTER_MYPORT,MASTER_MYSOCK);
1307
select current_user;
1310
grant select, insert, update ,trigger
1311
on priv_db.t1 to test_yesprivs@localhost;
1312
show grants for test_yesprivs@localhost;
1313
Grants for test_yesprivs@localhost
1314
GRANT USAGE ON *.* TO 'test_yesprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
1315
GRANT SELECT, INSERT, UPDATE, TRIGGER ON `priv_db`.`t1` TO 'test_yesprivs'@'localhost'
1316
select current_user;
1318
test_yesprivs@localhost
1321
create definer=current_user trigger trg1_1
1322
before INSERT on t1 for each row
1323
set new.f1 = 'trig 1_1-yes';
1325
insert into t1 (f1) values ('insert-no');
1326
select f1 from t1 order by f1;
1329
create definer=test_yesprivs@localhost trigger trg1_2
1330
before UPDATE on t1 for each row
1331
set new.f1 = 'trig 1_2-yes';
1333
update t1 set f1 = 'update-yes' where f1 like '%trig%';
1334
select f1 from t1 order by f1;
1338
drop trigger trg1_1;
1340
drop trigger trg1_1;
1341
ERROR HY000: Trigger does not exist
1342
drop trigger trg1_2;
1345
select current_user;
1348
select current_user;
1351
drop database if exists priv_db;
1352
drop user test_yesprivs@localhost;
1354
####### Testcase for column privileges of triggers: #######
1355
-----------------------------------------------------------
1356
drop database if exists priv_db;
1357
drop database if exists no_priv_db;
1358
create database priv_db;
1360
create table t1 (f1 char(20)) engine= falcon;
1361
create table t2 (f1 char(20)) engine= falcon;
1362
create User test_yesprivs@localhost;
1363
set password for test_yesprivs@localhost = password('PWD');
1364
revoke ALL PRIVILEGES, GRANT OPTION FROM test_yesprivs@localhost;
1365
grant TRIGGER on priv_db.* to test_yesprivs@localhost;
1366
show grants for test_yesprivs@localhost;
1367
Grants for test_yesprivs@localhost
1368
GRANT USAGE ON *.* TO 'test_yesprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
1369
GRANT TRIGGER ON `priv_db`.* TO 'test_yesprivs'@'localhost'
1370
create User test_noprivs@localhost;
1371
set password for test_noprivs@localhost = password('PWD');
1372
revoke ALL PRIVILEGES, GRANT OPTION FROM test_noprivs@localhost;
1373
grant SELECT,UPDATE on priv_db.* to test_noprivs@localhost;
1374
show grants for test_noprivs@localhost;
1375
Grants for test_noprivs@localhost
1376
GRANT USAGE ON *.* TO 'test_noprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
1377
GRANT SELECT, UPDATE ON `priv_db`.* TO 'test_noprivs'@'localhost'
1378
connect(localhost,test_yesprivs,PWD,test,MASTER_MYPORT,MASTER_MYSOCK);
1379
connect(localhost,test_noprivs,PWD,test,MASTER_MYPORT,MASTER_MYSOCK);
1381
update only on column:
1382
----------------------
1383
select current_user;
1386
grant SELECT(f1),INSERT,UPDATE(f1) on priv_db.t1
1387
to test_yesprivs@localhost;
1388
grant SELECT(f1),INSERT,UPDATE(f1) on priv_db.t2
1389
to test_yesprivs@localhost;
1390
select current_user;
1392
test_yesprivs@localhost
1394
insert into t1 (f1) values ('insert1-yes');
1395
insert into t2 (f1) values ('insert1-yes');
1396
create trigger trg1_1 before UPDATE on t1 for each row
1397
set new.f1 = 'trig 1_1-yes';
1398
create trigger trg2_1 before UPDATE on t2 for each row
1399
set new.f1 = 'trig 2_1-yes';
1400
select current_user;
1402
test_noprivs@localhost
1404
select f1 from t1 order by f1;
1407
update t1 set f1 = 'update1_no'
1408
where f1 like '%insert%';
1409
select f1 from t1 order by f1;
1412
select f1 from t2 order by f1;
1415
update t2 set f1 = 'update1_no'
1416
where f1 like '%insert%';
1417
select f1 from t2 order by f1;
1420
select current_user;
1423
revoke UPDATE on priv_db.*
1424
from test_yesprivs@localhost;
1425
revoke UPDATE(f1) on priv_db.t2
1426
from test_yesprivs@localhost;
1427
show grants for test_yesprivs@localhost;
1428
Grants for test_yesprivs@localhost
1429
GRANT USAGE ON *.* TO 'test_yesprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
1430
GRANT TRIGGER ON `priv_db`.* TO 'test_yesprivs'@'localhost'
1431
GRANT SELECT (f1), INSERT ON `priv_db`.`t2` TO 'test_yesprivs'@'localhost'
1432
GRANT SELECT (f1), INSERT, UPDATE (f1) ON `priv_db`.`t1` TO 'test_yesprivs'@'localhost'
1433
select current_user;
1435
test_yesprivs@localhost
1437
insert into t1 (f1) values ('insert2-yes');
1438
insert into t2 (f1) values ('insert2-yes');
1439
select current_user;
1441
test_noprivs@localhost
1443
update t1 set f1 = 'update2_no'
1444
where f1 like '%insert%';
1445
update t2 set f1 = 'update2_no'
1446
where f1 like '%insert%';
1447
ERROR 42000: UPDATE command denied to user 'test_yesprivs'@'localhost' for column 'f1' in table 't2'
1448
update t1 set f1 = 'update3_no'
1449
where f1 like '%insert%';
1450
update t2 set f1 = 'update3_no'
1451
where f1 like '%insert%';
1452
ERROR 42000: UPDATE command denied to user 'test_yesprivs'@'localhost' for column 'f1' in table 't2'
1453
select f1 from t1 order by f1;
1457
select f1 from t2 order by f1;
1462
check if access only on one of three columns
1463
--------------------------------------------
1464
select current_user;
1467
alter table priv_db.t1 add f2 char(20), add f3 int;
1468
revoke TRIGGER on priv_db.* from test_yesprivs@localhost;
1469
grant TRIGGER,SELECT on priv_db.t1 to test_yesprivs@localhost;
1470
grant UPDATE on priv_db.t2 to test_yesprivs@localhost;
1471
select current_user;
1473
test_yesprivs@localhost
1475
insert into t1 values ('insert2-yes','insert2-yes',1);
1476
insert into t1 values ('insert3-yes','insert3-yes',2);
1477
select * from t1 order by f1;
1479
insert2-yes insert2-yes 1
1480
insert3-yes insert3-yes 2
1481
trig 1_1-yes NULL NULL
1482
trig 1_1-yes NULL NULL
1483
select current_user;
1485
test_noprivs@localhost
1487
update t1 set f1 = 'update4-no',
1490
where f2 like '%yes';
1491
select * from t1 order by f1,f2,f3;
1493
trig 1_1-yes NULL NULL
1494
trig 1_1-yes NULL NULL
1495
trig 1_1-yes update4-yes 10
1496
trig 1_1-yes update4-yes 20
1497
select current_user;
1499
test_yesprivs@localhost
1500
create trigger trg1_2 after UPDATE on t1 for each row
1501
set @f2 = 'trig 1_2-yes';
1502
select current_user;
1504
test_noprivs@localhost
1505
update t1 set f1 = 'update5-yes',
1507
where f2 like '%yes';
1508
select * from t1 order by f1,f2,f3;
1510
trig 1_1-yes NULL NULL
1511
trig 1_1-yes NULL NULL
1512
trig 1_1-yes update5-yes 10
1513
trig 1_1-yes update5-yes 20
1517
update t1 set f1 = 'update6_no'
1518
where f1 like '%insert%';
1519
update t2 set f1 = 'update6_no'
1520
where f1 like '%insert%';
1521
ERROR 42000: TRIGGER command denied to user 'test_yesprivs'@'localhost' for table 't2'
1522
update t1 set f1 = 'update7_no'
1523
where f1 like '%insert%';
1524
update t2 set f1 = 'update7_no'
1525
where f1 like '%insert%';
1526
ERROR 42000: TRIGGER command denied to user 'test_yesprivs'@'localhost' for table 't2'
1527
select f1 from t1 order by f1;
1533
select f1 from t2 order by f1;
1538
check if rejected without trigger privilege:
1539
--------------------------------------------
1540
select current_user;
1543
revoke TRIGGER on priv_db.t1 from test_yesprivs@localhost;
1544
select current_user;
1546
test_noprivs@localhost
1547
update t1 set f1 = 'update8-no',
1549
where f2 like '%yes';
1550
ERROR 42000: TRIGGER command denied to user 'test_yesprivs'@'localhost' for table 't1'
1551
select * from t1 order by f1,f2,f3;
1553
trig 1_1-yes NULL NULL
1554
trig 1_1-yes NULL NULL
1555
trig 1_1-yes update5-yes 10
1556
trig 1_1-yes update5-yes 20
1561
check trigger, but not update privilege on column:
1562
--------------------------------------------------
1563
select current_user;
1566
revoke UPDATE(f1) on priv_db.t1 from test_yesprivs@localhost;
1567
grant TRIGGER,UPDATE(f2),UPDATE(f3) on priv_db.t1
1568
to test_yesprivs@localhost;
1569
show grants for test_yesprivs@localhost;
1570
Grants for test_yesprivs@localhost
1571
GRANT USAGE ON *.* TO 'test_yesprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
1572
GRANT SELECT (f1), INSERT, UPDATE ON `priv_db`.`t2` TO 'test_yesprivs'@'localhost'
1573
GRANT SELECT, SELECT (f1), INSERT, UPDATE (f3, f2), TRIGGER ON `priv_db`.`t1` TO 'test_yesprivs'@'localhost'
1574
select current_user;
1576
test_yesprivs@localhost
1578
drop trigger trg1_1;
1579
create trigger trg1_3 before UPDATE on t1 for each row
1580
set new.f1 = 'trig 1_3-yes';
1581
select current_user;
1583
test_noprivs@localhost
1585
update t1 set f1 = 'update9-no',
1587
where f2 like '%yes';
1588
ERROR 42000: UPDATE command denied to user 'test_yesprivs'@'localhost' for column 'f1' in table 't1'
1589
select * from t1 order by f1,f2,f3;
1591
trig 1_1-yes NULL NULL
1592
trig 1_1-yes NULL NULL
1593
trig 1_1-yes update5-yes 10
1594
trig 1_1-yes update5-yes 20
1595
update t1 set f3= f3+1;
1596
ERROR 42000: UPDATE command denied to user 'test_yesprivs'@'localhost' for column 'f1' in table 't1'
1597
select f3 from t1 order by f3;
1603
select current_user;
1606
revoke TRIGGER on priv_db.t1 from test_yesprivs@localhost;
1607
grant UPDATE(f1),UPDATE(f2),UPDATE(f3) on priv_db.t1
1608
to test_yesprivs@localhost;
1609
show grants for test_yesprivs@localhost;
1610
Grants for test_yesprivs@localhost
1611
GRANT USAGE ON *.* TO 'test_yesprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
1612
GRANT SELECT (f1), INSERT, UPDATE ON `priv_db`.`t2` TO 'test_yesprivs'@'localhost'
1613
GRANT SELECT, SELECT (f1), INSERT, UPDATE (f3, f2, f1) ON `priv_db`.`t1` TO 'test_yesprivs'@'localhost'
1614
select current_user;
1616
test_noprivs@localhost
1618
update t1 set f3= f3+1;
1619
ERROR 42000: TRIGGER command denied to user 'test_yesprivs'@'localhost' for table 't1'
1620
select f3 from t1 order by f3;
1627
##### trigger privilege on column level? #######
1628
------------------------------------------------
1629
grant TRIGGER(f1) on priv_db.t1 to test_yesprivs@localhost;
1630
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
1631
select current_user;
1634
drop database if exists priv_db;
1635
drop user test_yesprivs@localhost;
1636
drop user test_noprivs@localhost;