1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
|
#======================================================================
#
# Trigger Tests
# test cases for TRIGGER privilege on db, table and column level
#======================================================================
--disable_abort_on_error
###########################################
################ Section 3.5.3 ############
# Check for the definer of Triggers #
###########################################
# General setup to be used in all testcases
let $message= ######### Testcase for definer: ########;
--source include/show_msg.inc
--disable_warnings
drop database if exists priv_db;
--enable_warnings
create database priv_db;
use priv_db;
eval create table t1 (f1 char(20)) engine= $engine_type;
create User test_yesprivs@localhost;
set password for test_yesprivs@localhost = 'PWD';
revoke ALL PRIVILEGES, GRANT OPTION FROM test_yesprivs@localhost;
connect (yes_privs,localhost,test_yesprivs,PWD,test,$MASTER_MYPORT,$MASTER_MYSOCK);
# create trigger with not existing definer shall deliver a warning:
connection default;
select current_user;
# --warning 1449
create definer=not_ex_user@localhost trigger trg1_0
before INSERT on t1 for each row
set new.f1 = 'trig 1_0-yes';
drop trigger trg1_0;
# create trigger with definer test_yesprivs@localhost->succeed:
create definer=test_yesprivs@localhost trigger trg1_0
before INSERT on t1 for each row
set new.f1 = 'trig 1_0-yes';
grant select, insert, update
on priv_db.t1 to test_yesprivs@localhost;
connection yes_privs;
select current_user;
use priv_db;
# user hasn't trigger privilege->fail:
--error ER_TABLEACCESS_DENIED_ERROR
insert into t1 (f1) values ('insert-no');
select f1 from t1 order by f1;
--error ER_TABLEACCESS_DENIED_ERROR
drop trigger trg1_0;
connection default;
select current_user;
grant select, insert, update ,trigger
on priv_db.t1 to test_yesprivs@localhost;
show grants for test_yesprivs@localhost;
connection yes_privs;
select current_user;
# user now has trigger privilege->succeed:
insert into t1 (f1) values ('insert-no');
select f1 from t1 order by f1;
drop trigger trg1_0;
# user has not super privilege->fail:
--error ER_SPECIFIC_ACCESS_DENIED_ERROR
create definer=not_ex_user@localhost trigger trg1_0
before INSERT on t1 for each row
set new.f1 = 'trig 1_0-yes';
connection default;
create user definer_user@localhost identified by 'boo';
grant set_user_id,system_user, trigger on *.* to definer_user@localhost;
connect (con1,localhost,definer_user,boo,test,$MASTER_MYPORT,$MASTER_MYSOCK);
create table test.t1 (f1 varchar(20));
create definer=root@localhost trigger test.trg1_0
before INSERT on test.t1 for each row
set new.f1 = 'yes';
connection default;
drop trigger test.trg1_0;
drop user definer_user@localhost;
drop table test.t1;
connection yes_privs;
# shall always succeed:
create definer=current_user trigger trg1_1
before INSERT on t1 for each row
set new.f1 = 'trig 1_1-yes';
insert into t1 (f1) values ('insert-no');
select f1 from t1 order by f1;
# shall always succeed:
create definer=test_yesprivs@localhost trigger trg1_2
before UPDATE on t1 for each row
set new.f1 = 'trig 1_2-yes';
update t1 set f1 = 'update-yes' where f1 like '%trig%';
select f1 from t1 order by f1;
connection default;
select current_user;
grant trigger on priv_db.* to test_yesprivs@localhost
with grant option;
# user has not super privilege->fail:
connection yes_privs;
select current_user;
show grants;
--error ER_SPECIFIC_ACCESS_DENIED_ERROR
create definer=not_ex_user@localhost trigger trg1_3
after UPDATE on t1 for each row
set @var1 = 'trig 1_3-yes';
connection default;
select current_user;
# Cleanup prepare
--disable_warnings
disconnect yes_privs;
connection default;
select current_user;
--enable_warnings
# general Cleanup
--disable_warnings
drop database if exists priv_db;
drop user test_yesprivs@localhost;
--enable_warnings
|
