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
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
|
#
# Testing SQL SECURITY of stored procedures
#
connect (con1root,localhost,root,,);
connection con1root;
use test;
# Create user user1 with no particular access rights
grant usage on *.* to user1@localhost;
flush privileges;
--disable_warnings
drop database if exists db1_secret;
--enable_warnings
# Create our secret database
create database db1_secret;
# Can create a procedure in other db
create procedure db1_secret.dummy() begin end;
drop procedure db1_secret.dummy;
use db1_secret;
create table t1 ( u varchar(64), i int );
# A test procedure and function
create procedure stamp(i int)
insert into db1_secret.t1 values (user(), i);
--replace_column 5 '0000-00-00 00:00:00' 6 '0000-00-00 00:00:00'
show procedure status like 'stamp';
create function db() returns varchar(64) return database();
--replace_column 5 '0000-00-00 00:00:00' 6 '0000-00-00 00:00:00'
show function status like 'db';
# root can, of course
call stamp(1);
select * from t1;
select db();
connect (con2user1,localhost,user1,,);
connect (con3anon,localhost,anon,,);
#
# User1 can
#
connection con2user1;
# This should work...
call db1_secret.stamp(2);
select db1_secret.db();
# ...but not this
--error 1044
select * from db1_secret.t1;
# ...and not this
--error 1044
create procedure db1_secret.dummy() begin end;
--error 1305
drop procedure db1_secret.dummy;
#
# Anonymous can
#
connection con3anon;
# This should work...
call db1_secret.stamp(3);
select db1_secret.db();
# ...but not this
--error 1044
select * from db1_secret.t1;
# ...and not this
--error 1044
create procedure db1_secret.dummy() begin end;
--error 1305
drop procedure db1_secret.dummy;
#
# Check it out
#
connection con1root;
select * from t1;
#
# Change to invoker's rights
#
alter procedure stamp sql security invoker;
--replace_column 5 '0000-00-00 00:00:00' 6 '0000-00-00 00:00:00'
show procedure status like 'stamp';
alter function db sql security invoker;
--replace_column 5 '0000-00-00 00:00:00' 6 '0000-00-00 00:00:00'
show function status like 'db';
# root still can
call stamp(4);
select * from t1;
select db();
#
# User1 cannot
#
connection con2user1;
# This should not work
--error 1044
call db1_secret.stamp(5);
--error 1044
select db1_secret.db();
#
# Anonymous cannot
#
connection con3anon;
# This should not work
--error 1044
call db1_secret.stamp(6);
--error 1044
select db1_secret.db();
#
# BUG#2777
#
connection con1root;
--disable_warnings
drop database if exists db2;
--enable_warnings
create database db2;
use db2;
create table t2 (s1 int);
insert into t2 values (0);
grant usage on db2.* to user1@localhost;
grant select on db2.* to user1@localhost;
grant usage on db2.* to user2@localhost;
grant select,insert,update,delete on db2.* to user2@localhost;
flush privileges;
connection con2user1;
use db2;
create procedure p () insert into t2 values (1);
# Check that this doesn't work.
--error 1044
call p();
connect (con4user2,localhost,user2,,);
connection con4user2;
use db2;
# This should not work, since p is executed with definer's (user1's) rights.
--error 1044
call p();
select * from t2;
create procedure q () insert into t2 values (2);
call q();
select * from t2;
connection con2user1;
use db2;
# This should work
call q();
select * from t2;
#
# BUG#6030: Stored procedure has no appropriate DROP privilege
# (or ALTER for that matter)
# still connection con2user1 in db2
# This should work:
alter procedure p modifies sql data;
drop procedure p;
# This should NOT work
--error 1370
alter procedure q modifies sql data;
--error 1370
drop procedure q;
connection con1root;
use db2;
# But root always can
alter procedure q modifies sql data;
drop procedure q;
# Clean up
#Still connection con1root;
use test;
select type,db,name from mysql.proc;
drop database db1_secret;
drop database db2;
# Make sure the routines are gone
select type,db,name from mysql.proc;
# Get rid of the users
delete from mysql.user where user='user1' or user='user2';
|
