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
|
#
# 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;
use db1_secret;
create table t1 ( u varchar(64), i int );
# Our test procedure
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';
# root can, of course
call stamp(1);
select * from t1;
connect (con2user1,localhost,user1,,);
connect (con3anon,localhost,anon,,);
#
# User1 can
#
connection con2user1;
# This should work...
call db1_secret.stamp(2);
# ...but not this
--error 1044
select * from db1_secret.t1;
#
# Anonymous can
#
connection con3anon;
# This should work...
call db1_secret.stamp(3);
# ...but not this
--error 1044
select * from db1_secret.t1;
#
# 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';
# root still can
call stamp(4);
select * from t1;
#
# User1 cannot
#
connection con2user1;
# This should not work
--error 1044
call db1_secret.stamp(5);
#
# Anonymous cannot
#
connection con3anon;
# This should not work
--error 1044
call db1_secret.stamp(6);
#
# 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;
# Clean up
connection con1root;
drop procedure db1_secret.stamp;
drop procedure db2.p;
drop procedure db2.q;
use test;
drop database db1_secret;
drop database db2;
delete from mysql.user where user='user1' or user='user2';
|
