1
by brian
clean slate |
1 |
#!/usr/bin/perl
|
2 |
#
|
|
3 |
# Testing of grants.
|
|
4 |
# Note that this will delete all table and column grants !
|
|
5 |
#
|
|
6 |
||
7 |
use DBI; |
|
8 |
use Getopt::Long; |
|
9 |
use strict; |
|
10 |
||
11 |
use vars qw($dbh $user_dbh $opt_help $opt_Information $opt_force $opt_debug |
|
12 |
$opt_verbose $opt_server $opt_root_user $opt_password $opt_user
|
|
13 |
$opt_database $opt_host $version $user $tables_cols $columns_cols
|
|
14 |
$tmp_table $opt_silent); |
|
15 |
||
16 |
$version="1.1"; |
|
17 |
$opt_help=$opt_Information=$opt_force=$opt_debug=$opt_verbose=$opt_silent=0; |
|
18 |
$opt_host="localhost", |
|
19 |
$opt_server="mysql"; |
|
20 |
$opt_root_user="root"; |
|
21 |
$opt_password=""; |
|
22 |
$opt_user="grant_user"; |
|
23 |
$opt_database="grant_test"; |
|
24 |
||
25 |
GetOptions("Information","help","server=s","root-user=s","password=s","user","database=s","force","host=s","debug","verbose","silent") || usage(); |
|
26 |
usage() if ($opt_help || $opt_Information); |
|
27 |
||
28 |
$user="$opt_user\@$opt_host"; |
|
29 |
||
30 |
if (!$opt_force) |
|
31 |
{
|
|
32 |
print_info() |
|
33 |
}
|
|
34 |
||
35 |
$|=1; |
|
36 |
||
37 |
$tables_cols="Host, Db, User, Table_name, Grantor, Table_priv, Column_priv"; |
|
38 |
$columns_cols="Host, Db, User, Table_name, Column_name, Column_priv"; |
|
39 |
$tmp_table="/tmp/mysql-grant.test"; # Can't use $$ as we are logging result |
|
40 |
unlink($tmp_table); |
|
41 |
||
42 |
#
|
|
43 |
# clear grant tables
|
|
44 |
#
|
|
45 |
||
46 |
$dbh = DBI->connect("DBI:mysql:mysql:$opt_host", |
|
47 |
$opt_root_user,$opt_password, |
|
48 |
{ PrintError => 0}) || die "Can't connect to mysql server with user '$opt_root_user': $DBI::errstr\n"; |
|
49 |
||
50 |
safe_query("delete from user where user='$opt_user' or user='${opt_user}2'"); |
|
51 |
safe_query("delete from db where user='$opt_user'"); |
|
52 |
safe_query("delete from tables_priv"); |
|
53 |
safe_query("delete from columns_priv"); |
|
54 |
safe_query("lock tables mysql.user write"); # Test lock tables |
|
55 |
safe_query("flush privileges"); |
|
56 |
safe_query("unlock tables"); # should already be unlocked |
|
57 |
safe_query("drop database $opt_database",3); # Don't print possible error |
|
58 |
safe_query("create database $opt_database"); |
|
59 |
||
60 |
# check that the user can't login yet
|
|
61 |
||
62 |
user_connect(1); |
|
63 |
#goto test;
|
|
64 |
||
65 |
#
|
|
66 |
# Enable column grant code
|
|
67 |
#
|
|
68 |
safe_query("grant select(user) on mysql.user to $user"); |
|
69 |
safe_query("revoke select(user) on mysql.user from $user"); |
|
70 |
||
71 |
#
|
|
72 |
# Test grants on user level
|
|
73 |
#
|
|
74 |
||
75 |
safe_query("grant select on *.* to $user"); |
|
76 |
safe_query("set password FOR ${opt_user}2\@$opt_host = password('test')",1); |
|
77 |
safe_query("set password FOR $opt_user\@$opt_host=password('test')"); |
|
78 |
user_connect(1); |
|
79 |
safe_query("set password FOR $opt_user\@$opt_host=''"); |
|
80 |
user_connect(0); |
|
81 |
user_query("select * from mysql.user where user = '$opt_user'"); |
|
82 |
user_query("select * from mysql.db where user = '$opt_user'"); |
|
83 |
safe_query("grant select on *.* to $user,$user"); |
|
84 |
safe_query("show grants for $user"); |
|
85 |
user_connect(0); |
|
86 |
||
87 |
# The following should fail
|
|
88 |
user_query("insert into mysql.user (host,user) values ('error','$opt_user')",1); |
|
89 |
user_query("update mysql.user set host='error' WHERE user='$opt_user'",1); |
|
90 |
user_query("create table $opt_database.test (a int,b int)",1); |
|
91 |
user_query("grant select on *.* to ${opt_user}2\@$opt_host",1); |
|
92 |
safe_query("revoke select on $opt_database.test from $opt_user\@opt_host",1); |
|
93 |
safe_query("revoke select on $opt_database.* from $opt_user\@opt_host",1); |
|
94 |
safe_query("revoke select on *.* from $opt_user",1); |
|
95 |
safe_query("grant select on $opt_database.not_exists to $opt_user",1); |
|
96 |
safe_query("grant FILE on $opt_database.test to $opt_user",1); |
|
97 |
safe_query("grant select on *.* to wrong___________user_name",1); |
|
98 |
safe_query("grant select on $opt_database.* to wrong___________user_name",1); |
|
99 |
user_connect(0); |
|
100 |
user_query("grant select on $opt_database.test to $opt_user with grant option",1); |
|
101 |
safe_query("set password FOR ''\@''=''",1); |
|
102 |
user_query("set password FOR root\@$opt_host = password('test')",1); |
|
103 |
||
104 |
# Change privileges for user
|
|
105 |
safe_query("revoke select on *.* from $user"); |
|
106 |
safe_query("grant create,update on *.* to $user"); |
|
107 |
user_connect(0); |
|
108 |
safe_query("flush privileges"); |
|
109 |
user_query("create table $opt_database.test (a int,b int)"); |
|
110 |
user_query("update $opt_database.test set b=b+1 where a > 0",1); |
|
111 |
safe_query("show grants for $user"); |
|
112 |
safe_query("revoke update on *.* from $user"); |
|
113 |
user_connect(0); |
|
114 |
safe_query("grant select(c) on $opt_database.test to $user",1); |
|
115 |
safe_query("revoke select(c) on $opt_database.test from $user",1); |
|
116 |
safe_query("grant select on $opt_database.test to wrong___________user_name",1); |
|
117 |
user_query("INSERT INTO $opt_database.test values (2,0)",1); |
|
118 |
||
119 |
safe_query("grant ALL PRIVILEGES on *.* to $user"); |
|
120 |
safe_query("REVOKE INSERT on *.* from $user"); |
|
121 |
user_connect(0); |
|
122 |
user_query("INSERT INTO $opt_database.test values (1,0)",1); |
|
123 |
safe_query("grant INSERT on *.* to $user"); |
|
124 |
user_connect(0); |
|
125 |
user_query("INSERT INTO $opt_database.test values (2,0)"); |
|
126 |
user_query("select count(*) from $opt_database.test"); |
|
127 |
safe_query("revoke SELECT on *.* from $user"); |
|
128 |
user_connect(0); |
|
129 |
user_query("select count(*) from $opt_database.test",1); |
|
130 |
user_query("INSERT INTO $opt_database.test values (3,0)"); |
|
131 |
safe_query("grant SELECT on *.* to $user"); |
|
132 |
user_connect(0); |
|
133 |
user_query("select count(*) from $opt_database.test"); |
|
134 |
safe_query("revoke ALL PRIVILEGES on *.* from $user"); |
|
135 |
user_connect(1); |
|
136 |
safe_query("delete from user where user='$opt_user'"); |
|
137 |
safe_query("flush privileges"); |
|
138 |
if (0) # Only if no anonymous user on localhost. |
|
139 |
{
|
|
140 |
safe_query("grant select on *.* to $opt_user"); |
|
141 |
user_connect(0); |
|
142 |
safe_query("revoke select on *.* from $opt_user"); |
|
143 |
user_connect(1); |
|
144 |
}
|
|
145 |
safe_query("delete from user where user='$opt_user'"); |
|
146 |
safe_query("flush privileges"); |
|
147 |
||
148 |
#
|
|
149 |
# Test grants on database level
|
|
150 |
#
|
|
151 |
safe_query("grant select on $opt_database.* to $user"); |
|
152 |
safe_query("select * from mysql.user where user = '$opt_user'"); |
|
153 |
safe_query("select * from mysql.db where user = '$opt_user'"); |
|
154 |
user_connect(0); |
|
155 |
user_query("select count(*) from $opt_database.test"); |
|
156 |
# The following should fail
|
|
157 |
user_query("select * from mysql.user where user = '$opt_user'",1); |
|
158 |
user_query("insert into $opt_database.test values (4,0)",1); |
|
159 |
user_query("update $opt_database.test set a=1",1); |
|
160 |
user_query("delete from $opt_database.test",1); |
|
161 |
user_query("create table $opt_database.test2 (a int)",1); |
|
162 |
user_query("ALTER TABLE $opt_database.test add c int",1); |
|
163 |
user_query("CREATE INDEX dummy ON $opt_database.test (a)",1); |
|
164 |
user_query("drop table $opt_database.test",1); |
|
165 |
user_query("grant ALL PRIVILEGES on $opt_database.* to ${opt_user}2\@$opt_host",1); |
|
166 |
||
167 |
# Change privileges for user
|
|
168 |
safe_query("grant ALL PRIVILEGES on $opt_database.* to $user WITH GRANT OPTION"); |
|
169 |
user_connect(0); |
|
170 |
user_query("insert into $opt_database.test values (5,0)"); |
|
171 |
safe_query("REVOKE ALL PRIVILEGES on * from $user",1); |
|
172 |
safe_query("REVOKE ALL PRIVILEGES on *.* from $user"); |
|
173 |
safe_query("REVOKE ALL PRIVILEGES on $opt_database.* from $user"); |
|
174 |
safe_query("REVOKE ALL PRIVILEGES on $opt_database.* from $user"); |
|
175 |
user_connect(0); |
|
176 |
user_query("insert into $opt_database.test values (6,0)",1); |
|
177 |
safe_query("REVOKE GRANT OPTION on $opt_database.* from $user"); |
|
178 |
user_connect(1); |
|
179 |
safe_query("grant ALL PRIVILEGES on $opt_database.* to $user"); |
|
180 |
||
181 |
user_connect(0); |
|
182 |
user_query("select * from mysql.user where user = '$opt_user'",1); |
|
183 |
user_query("insert into $opt_database.test values (7,0)"); |
|
184 |
user_query("update $opt_database.test set a=3 where a=2"); |
|
185 |
user_query("delete from $opt_database.test where a=3"); |
|
186 |
user_query("create table $opt_database.test2 (a int not null)"); |
|
187 |
user_query("alter table $opt_database.test2 add b int"); |
|
188 |
user_query("create index dummy on $opt_database.test2 (a)"); |
|
189 |
user_query("update test,test2 SET test.a=test2.a where test.a=test2.a"); |
|
190 |
user_query("drop table $opt_database.test2"); |
|
191 |
user_query("show tables from grant_test"); |
|
192 |
# These should fail
|
|
193 |
user_query("insert into mysql.user (host,user) values ('error','$opt_user',0)",1); |
|
194 |
||
195 |
# Revoke database privileges
|
|
196 |
safe_query("revoke ALL PRIVILEGES on $opt_database.* from $user"); |
|
197 |
safe_query("select * from mysql.user where user = '$opt_user'"); |
|
198 |
safe_query("select * from mysql.db where user = '$opt_user'"); |
|
199 |
||
200 |
# Test multi-updates
|
|
201 |
safe_query("grant CREATE,UPDATE,DROP on $opt_database.* to $user"); |
|
202 |
user_connect(0); |
|
203 |
user_query("create table $opt_database.test2 (a int not null)"); |
|
204 |
user_query("update test,test2 SET test.a=1 where 1",1); |
|
205 |
user_query("update test,test2 SET test.a=test2.a where 1",1); |
|
206 |
safe_query("grant SELECT on $opt_database.* to $user"); |
|
207 |
user_connect(0); |
|
208 |
user_query("update test,test2 SET test.a=test2.a where test2.a=test.a"); |
|
209 |
user_query("drop table $opt_database.test2"); |
|
210 |
||
211 |
# Revoke database privileges
|
|
212 |
safe_query("revoke ALL PRIVILEGES on $opt_database.* from $user"); |
|
213 |
user_connect(1); |
|
214 |
||
215 |
#
|
|
216 |
# Test of grants on table level
|
|
217 |
#
|
|
218 |
||
219 |
safe_query("grant create on $opt_database.test2 to $user"); |
|
220 |
user_connect(0); |
|
221 |
user_query("create table $opt_database.test2 (a int not null)"); |
|
222 |
user_query("show tables"); # Should only show test, not test2 |
|
223 |
user_query("show columns from test",1); |
|
224 |
user_query("show keys from test",1); |
|
225 |
user_query("show columns from test2"); |
|
226 |
user_query("show keys from test2"); |
|
227 |
user_query("select * from test",1); |
|
228 |
safe_query("grant insert on $opt_database.test to $user"); |
|
229 |
user_query("show tables"); |
|
230 |
user_query("insert into $opt_database.test values (8,0)"); |
|
231 |
user_query("update $opt_database.test set b=1",1); |
|
232 |
safe_query("grant update on $opt_database.test to $user"); |
|
233 |
user_query("update $opt_database.test set b=2"); |
|
234 |
||
235 |
user_query("update $opt_database.test,test2 SET test.b=3",1); |
|
236 |
safe_query("grant select on $opt_database.test2 to $user"); |
|
237 |
user_query("update $opt_database.test,test2 SET test.b=3"); |
|
238 |
safe_query("revoke select on $opt_database.test2 from $user"); |
|
239 |
||
240 |
user_query("delete from $opt_database.test",1); |
|
241 |
safe_query("grant delete on $opt_database.test to $user"); |
|
242 |
user_query("delete from $opt_database.test where a=1",1); |
|
243 |
user_query("update $opt_database.test set b=3 where b=1",1); |
|
244 |
user_query("update $opt_database.test set b=b+1",1); |
|
245 |
user_query("update $opt_database.test,test2 SET test.a=test2.a",1); |
|
246 |
||
247 |
#
|
|
248 |
# Test global SELECT privilege combined with table level privileges
|
|
249 |
#
|
|
250 |
||
251 |
safe_query("grant SELECT on *.* to $user"); |
|
252 |
user_connect(0); |
|
253 |
user_query("update $opt_database.test set b=b+1"); |
|
254 |
user_query("update $opt_database.test set b=b+1 where a > 0"); |
|
255 |
user_query("update $opt_database.test,test2 SET test.a=test2.a"); |
|
256 |
user_query("update $opt_database.test,test2 SET test2.a=test.a",1); |
|
257 |
safe_query("revoke SELECT on *.* from $user"); |
|
258 |
safe_query("grant SELECT on $opt_database.* to $user"); |
|
259 |
user_connect(0); |
|
260 |
user_query("update $opt_database.test set b=b+1"); |
|
261 |
user_query("update $opt_database.test set b=b+1 where a > 0"); |
|
262 |
safe_query("grant UPDATE on *.* to $user"); |
|
263 |
user_connect(0); |
|
264 |
user_query("update $opt_database.test set b=b+1"); |
|
265 |
user_query("update $opt_database.test set b=b+1 where a > 0"); |
|
266 |
safe_query("revoke UPDATE on *.* from $user"); |
|
267 |
safe_query("revoke SELECT on $opt_database.* from $user"); |
|
268 |
user_connect(0); |
|
269 |
user_query("update $opt_database.test set b=b+1 where a > 0",1); |
|
270 |
user_query("update $opt_database.test set b=b+1",1); |
|
271 |
||
272 |
# Add one privilege at a time until the user has all privileges
|
|
273 |
user_query("select * from test",1); |
|
274 |
safe_query("grant select on $opt_database.test to $user"); |
|
275 |
user_query("delete from $opt_database.test where a=1"); |
|
276 |
user_query("update $opt_database.test set b=2 where b=1"); |
|
277 |
user_query("update $opt_database.test set b=b+1"); |
|
278 |
user_query("select count(*) from test"); |
|
279 |
user_query("update test,test2 SET test.b=4",1); |
|
280 |
user_query("update test,test2 SET test2.a=test.a",1); |
|
281 |
user_query("update test,test2 SET test.a=test2.a",1); |
|
282 |
||
283 |
user_query("create table $opt_database.test3 (a int)",1); |
|
284 |
user_query("alter table $opt_database.test2 add c int",1); |
|
285 |
safe_query("grant alter on $opt_database.test2 to $user"); |
|
286 |
user_query("alter table $opt_database.test2 add c int"); |
|
287 |
user_query("create index dummy ON $opt_database.test (a)",1); |
|
288 |
safe_query("grant index on $opt_database.test2 to $user"); |
|
289 |
user_query("create index dummy ON $opt_database.test2 (a)"); |
|
290 |
user_query("insert into test2 SELECT a,a from test",1); |
|
291 |
safe_query("grant insert on test2 to $user",1); # No table: mysql.test2 |
|
292 |
safe_query("grant insert(a) on $opt_database.test2 to $user"); |
|
293 |
user_query("insert into test2 SELECT a,a from test",1); |
|
294 |
safe_query("grant insert(c) on $opt_database.test2 to $user"); |
|
295 |
user_query("insert into test2 SELECT a,a from test"); |
|
296 |
user_query("select count(*) from test2,test",1); |
|
297 |
user_query("select count(*) from test,test2",1); |
|
298 |
user_query("replace into test2 SELECT a from test",1); |
|
299 |
safe_query("grant update on $opt_database.test2 to $user"); |
|
300 |
user_query("update test,test2 SET test2.a=test.a"); |
|
301 |
user_query("update test,test2 SET test.b=test2.a where 0",1); |
|
302 |
user_query("update test,test2 SET test.a=2 where test2.a>100",1); |
|
303 |
user_query("update test,test2 SET test.a=test2.a",1); |
|
304 |
user_query("replace into test2 SELECT a,a from test",1); |
|
305 |
safe_query("grant DELETE on $opt_database.test2 to $user"); |
|
306 |
user_query("replace into test2 SELECT a,a from test"); |
|
307 |
user_query("insert into test (a) SELECT a from test2",1); |
|
308 |
safe_query("grant SELECT on $opt_database.test2 to $user"); |
|
309 |
user_query("update test,test2 SET test.b=test2.a where 0"); |
|
310 |
user_query("update test,test2 SET test.a=test2.a where test2.a>100"); |
|
311 |
||
312 |
safe_query("revoke UPDATE on $opt_database.test2 from $user"); |
|
313 |
safe_query("grant UPDATE (c) on $opt_database.test2 to $user"); |
|
314 |
user_query("update test,test2 SET test.b=test2.a where 0"); |
|
315 |
user_query("update test,test2 SET test.a=test2.a where test2.a>100"); |
|
316 |
user_query("update test,test2 SET test2.a=test2.a where test2.a>100",1); |
|
317 |
user_query("update test,test2 SET test2.c=test2.a where test2.a>100"); |
|
318 |
||
319 |
safe_query("revoke SELECT,UPDATE on $opt_database.test2 from $user"); |
|
320 |
safe_query("grant UPDATE on $opt_database.test2 to $user"); |
|
321 |
||
322 |
user_query("drop table $opt_database.test2",1); |
|
323 |
user_query("grant select on $opt_database.test2 to $user with grant option",1); |
|
324 |
safe_query("grant drop on $opt_database.test2 to $user with grant option"); |
|
325 |
user_query("grant drop on $opt_database.test2 to $user with grant option"); |
|
326 |
user_query("grant select on $opt_database.test2 to $user with grant option",1); |
|
327 |
||
328 |
# check rename privileges
|
|
329 |
user_query("rename table $opt_database.test2 to $opt_database.test3",1); |
|
330 |
safe_query("grant CREATE,DROP on $opt_database.test3 to $user"); |
|
331 |
user_query("rename table $opt_database.test2 to $opt_database.test3",1); |
|
332 |
user_query("create table $opt_database.test3 (a int)"); |
|
333 |
safe_query("grant INSERT on $opt_database.test3 to $user"); |
|
334 |
user_query("drop table $opt_database.test3"); |
|
335 |
user_query("rename table $opt_database.test2 to $opt_database.test3"); |
|
336 |
user_query("rename table $opt_database.test3 to $opt_database.test2",1); |
|
337 |
safe_query("grant ALTER on $opt_database.test3 to $user"); |
|
338 |
user_query("rename table $opt_database.test3 to $opt_database.test2"); |
|
339 |
safe_query("revoke DROP on $opt_database.test2 from $user"); |
|
340 |
user_query("rename table $opt_database.test2 to $opt_database.test3"); |
|
341 |
user_query("drop table if exists $opt_database.test2,$opt_database.test3",1); |
|
342 |
safe_query("drop table if exists $opt_database.test2,$opt_database.test3"); |
|
343 |
||
344 |
# Check that the user doesn't have some user privileges
|
|
345 |
user_query("create database $opt_database",1); |
|
346 |
user_query("drop database $opt_database",1); |
|
347 |
user_query("flush tables",1); |
|
348 |
safe_query("flush privileges"); |
|
349 |
||
350 |
safe_query("select $tables_cols from mysql.tables_priv"); |
|
351 |
safe_query("revoke ALL PRIVILEGES on $opt_database.test from $user"); |
|
352 |
safe_query("revoke ALL PRIVILEGES on $opt_database.test2 from $user"); |
|
353 |
safe_query("revoke ALL PRIVILEGES on $opt_database.test3 from $user"); |
|
354 |
safe_query("revoke GRANT OPTION on $opt_database.test2 from $user"); |
|
355 |
safe_query("select $tables_cols from mysql.tables_priv"); |
|
356 |
user_query("select count(a) from test",1); |
|
357 |
||
358 |
#
|
|
359 |
# Test some grants on column level
|
|
360 |
#
|
|
361 |
||
362 |
safe_query("grant create,update on $opt_database.test2 to $user"); |
|
363 |
user_query("create table $opt_database.test2 (a int not null)"); |
|
364 |
user_query("delete from $opt_database.test where a=2",1); |
|
365 |
user_query("delete from $opt_database.test where A=2",1); |
|
366 |
user_query("update test set b=5 where b>0",1); |
|
367 |
user_query("update test,test2 SET test.b=5 where b>0",1); |
|
368 |
||
369 |
safe_query("grant update(b),delete on $opt_database.test to $user"); |
|
370 |
safe_query("revoke update(a) on $opt_database.test from $user",1); |
|
371 |
user_query("delete from $opt_database.test where a=2",1); |
|
372 |
user_query("update test set b=5 where b>0",1); |
|
373 |
safe_query("grant select(a),select(b) on $opt_database.test to $user"); |
|
374 |
user_query("delete from $opt_database.test where a=2"); |
|
375 |
user_query("delete from $opt_database.test where A=2"); |
|
376 |
user_query("update test set b=5 where b>0"); |
|
377 |
user_query("update test set a=11 where b>5",1); |
|
378 |
user_query("update test,test2 SET test.b=5 where b>0",1); |
|
379 |
user_query("update test,test2 SET test.a=11 where b>0",1); |
|
380 |
user_query("update test,test2 SET test.b=test2.a where b>0",1); |
|
381 |
user_query("update test,test2 SET test.b=11 where test2.a>0",1); |
|
382 |
user_query("select a,A from test"); |
|
383 |
||
384 |
safe_query("select $tables_cols from mysql.tables_priv"); |
|
385 |
safe_query("revoke ALL PRIVILEGES on $opt_database.test from $user"); |
|
386 |
safe_query("select $tables_cols from mysql.tables_priv"); |
|
387 |
safe_query("revoke GRANT OPTION on $opt_database.test from $user",1); |
|
388 |
safe_query("drop table $opt_database.test2"); |
|
389 |
safe_query("revoke create,update on $opt_database.test2 from $user"); |
|
390 |
||
391 |
#
|
|
392 |
# Test grants on database level
|
|
393 |
#
|
|
394 |
||
395 |
safe_query("grant select(a) on $opt_database.test to $user"); |
|
396 |
user_query("show full columns from test"); |
|
397 |
safe_query("grant insert (b), update (b) on $opt_database.test to $user"); |
|
398 |
||
399 |
user_query("select count(a) from test"); |
|
400 |
user_query("select count(skr.a) from test as skr"); |
|
401 |
user_query("select count(a) from test where a > 5"); |
|
402 |
user_query("insert into test (b) values (5)"); |
|
403 |
user_query("insert into test (b) values (a)"); |
|
404 |
user_query("update test set b=3 where a > 0"); |
|
405 |
||
406 |
user_query("select * from test",1); |
|
407 |
user_query("select b from test",1); |
|
408 |
user_query("select a from test where b > 0",1); |
|
409 |
user_query("insert into test (a) values (10)",1); |
|
410 |
user_query("insert into test (b) values (b)",1); |
|
411 |
user_query("insert into test (a,b) values (1,5)",1); |
|
412 |
user_query("insert into test (b) values (1),(b)",1); |
|
413 |
user_query("update test set b=3 where b > 0",1); |
|
414 |
||
415 |
safe_query("select $tables_cols from mysql.tables_priv"); |
|
416 |
safe_query("select $columns_cols from mysql.columns_priv"); |
|
417 |
safe_query("revoke select(a), update (b) on $opt_database.test from $user"); |
|
418 |
safe_query("select $tables_cols from mysql.tables_priv"); |
|
419 |
safe_query("select $columns_cols from mysql.columns_priv"); |
|
420 |
||
421 |
user_query("select count(a) from test",1); |
|
422 |
user_query("update test set b=4",1); |
|
423 |
||
424 |
safe_query("grant select(a,b), update (a,b) on $opt_database.test to $user"); |
|
425 |
user_query("select count(a),count(b) from test where a+b > 0"); |
|
426 |
user_query("insert into test (b) values (9)"); |
|
427 |
user_query("update test set b=6 where b > 0"); |
|
428 |
||
429 |
safe_query("flush privileges"); # Test restoring privileges from disk |
|
430 |
safe_query("select $tables_cols from mysql.tables_priv"); |
|
431 |
safe_query("select $columns_cols from mysql.columns_priv"); |
|
432 |
||
433 |
# Try mixing of table and database privileges
|
|
434 |
||
435 |
user_query("insert into test (a,b) values (12,12)",1); |
|
436 |
safe_query("grant insert on $opt_database.* to $user"); |
|
437 |
user_connect(0); |
|
438 |
user_query("insert into test (a,b) values (13,13)"); |
|
439 |
||
440 |
# This grants and revokes SELECT on different levels.
|
|
441 |
safe_query("revoke select(b) on $opt_database.test from $user"); |
|
442 |
user_query("select count(a) from test where a+b > 0",1); |
|
443 |
user_query("update test set b=5 where a=2"); |
|
444 |
safe_query("grant select on $opt_database.test to $user"); |
|
445 |
user_connect(0); |
|
446 |
user_query("select count(a) from test where a+b > 0"); |
|
447 |
safe_query("revoke select(b) on $opt_database.test from $user"); |
|
448 |
user_query("select count(a) from test where a+b > 0"); |
|
449 |
safe_query("revoke select on $opt_database.test from $user"); |
|
450 |
user_connect(0); |
|
451 |
user_query("select count(a) from test where a+b > 0",1); |
|
452 |
safe_query("grant select(a) on $opt_database.test to $user"); |
|
453 |
user_query("select count(a) from test where a+b > 0",1); |
|
454 |
safe_query("grant select on *.* to $user"); |
|
455 |
user_connect(0); |
|
456 |
user_query("select count(a) from test where a+b > 0"); |
|
457 |
safe_query("revoke select on *.* from $user"); |
|
458 |
safe_query("grant select(b) on $opt_database.test to $user"); |
|
459 |
user_connect(0); |
|
460 |
user_query("select count(a) from test where a+b > 0"); |
|
461 |
||
462 |
||
463 |
safe_query("select * from mysql.db where user = '$opt_user'"); |
|
464 |
safe_query("select $tables_cols from mysql.tables_priv where user = '$opt_user'"); |
|
465 |
safe_query("select $columns_cols from mysql.columns_priv where user = '$opt_user'"); |
|
466 |
||
467 |
safe_query("revoke ALL PRIVILEGES on $opt_database.test from $user"); |
|
468 |
user_query("select count(a) from test",1); |
|
469 |
user_query("select * from mysql.user order by hostname",1); |
|
470 |
safe_query("select * from mysql.db where user = '$opt_user'"); |
|
471 |
safe_query("select $tables_cols from mysql.tables_priv where user = '$opt_user'"); |
|
472 |
safe_query("select $columns_cols from mysql.columns_priv where user = '$opt_user'"); |
|
473 |
||
474 |
#
|
|
475 |
# Clear up privileges to make future tests easier
|
|
476 |
||
477 |
safe_query("delete from user where user='$opt_user'"); |
|
478 |
safe_query("delete from db where user='$opt_user'"); |
|
479 |
safe_query("flush privileges"); |
|
480 |
safe_query("show grants for $user",1); |
|
481 |
||
482 |
#
|
|
483 |
# Test IDENTIFIED BY
|
|
484 |
#
|
|
485 |
||
486 |
safe_query("grant ALL PRIVILEGES on $opt_database.test to $user identified by 'dummy', ${opt_user}\@127.0.0.1 identified by 'dummy2'"); |
|
487 |
user_connect(0,"dummy"); |
|
488 |
safe_query("grant SELECT on $opt_database.* to $user identified by ''"); |
|
489 |
user_connect(0); |
|
490 |
safe_query("revoke ALL PRIVILEGES on $opt_database.test from $user identified by '', ${opt_user}\@127.0.0.1 identified by 'dummy2'"); |
|
491 |
safe_query("revoke ALL PRIVILEGES on $opt_database.* from $user identified by ''"); |
|
492 |
||
493 |
safe_query("show grants for $user"); |
|
494 |
||
495 |
#
|
|
496 |
# Test bug reported in SELECT INTO OUTFILE
|
|
497 |
#
|
|
498 |
||
499 |
safe_query("create table $opt_database.test3 (a int, b int)"); |
|
500 |
safe_query("grant SELECT on $opt_database.test3 to $user"); |
|
501 |
safe_query("grant FILE on *.* to $user"); |
|
502 |
safe_query("insert into $opt_database.test3 values (1,1)"); |
|
503 |
user_connect(0); |
|
504 |
user_query("select * into outfile '$tmp_table' from $opt_database.test3"); |
|
505 |
safe_query("revoke SELECT on $opt_database.test3 from $user"); |
|
506 |
safe_query("grant SELECT(a) on $opt_database.test3 to $user"); |
|
507 |
user_query("select a from $opt_database.test3"); |
|
508 |
user_query("select * from $opt_database.test3",1); |
|
509 |
user_query("select a,b from $opt_database.test3",1); |
|
510 |
user_query("select b from $opt_database.test3",1); |
|
511 |
||
512 |
safe_query("revoke SELECT(a) on $opt_database.test3 from $user"); |
|
513 |
safe_query("revoke FILE on *.* from $user"); |
|
514 |
safe_query("drop table $opt_database.test3"); |
|
515 |
||
516 |
#
|
|
517 |
# Test privileges needed for LOCK TABLES
|
|
518 |
#
|
|
519 |
||
520 |
safe_query("create table $opt_database.test3 (a int)"); |
|
521 |
user_connect(1); |
|
522 |
safe_query("grant INSERT on $opt_database.test3 to $user"); |
|
523 |
user_connect(0); |
|
524 |
user_query("select * into outfile '$tmp_table' from $opt_database.test3",1); |
|
525 |
safe_query("grant SELECT on $opt_database.test3 to $user"); |
|
526 |
user_connect(0); |
|
527 |
user_query("LOCK TABLES $opt_database.test3 READ",1); |
|
528 |
safe_query("grant LOCK TABLES on *.* to $user"); |
|
529 |
safe_query("show grants for $user"); |
|
530 |
safe_query("select * from mysql.user where user='$opt_user'"); |
|
531 |
user_connect(0); |
|
532 |
user_query("LOCK TABLES $opt_database.test3 READ"); |
|
533 |
user_query("UNLOCK TABLES"); |
|
534 |
safe_query("revoke SELECT,INSERT,UPDATE,DELETE on $opt_database.test3 from $user"); |
|
535 |
user_connect(0); |
|
536 |
safe_query("revoke LOCK TABLES on *.* from $user"); |
|
537 |
user_connect(1); |
|
538 |
safe_query("drop table $opt_database.test3"); |
|
539 |
||
540 |
#
|
|
541 |
# test new privileges in 4.0.2
|
|
542 |
#
|
|
543 |
||
544 |
safe_query("show grants for $user"); |
|
545 |
safe_query("grant all on *.* to $user WITH MAX_QUERIES_PER_HOUR 1 MAX_UPDATES_PER_HOUR 2 MAX_CONNECTIONS_PER_HOUR 3"); |
|
546 |
safe_query("show grants for $user"); |
|
547 |
safe_query("revoke LOCK TABLES on *.* from $user"); |
|
548 |
safe_query("flush privileges"); |
|
549 |
safe_query("show grants for $user"); |
|
550 |
safe_query("revoke ALL PRIVILEGES on *.* from $user"); |
|
551 |
safe_query("show grants for $user"); |
|
552 |
||
553 |
#
|
|
554 |
# Clean up things
|
|
555 |
#
|
|
556 |
||
557 |
unlink($tmp_table); |
|
558 |
safe_query("drop database $opt_database"); |
|
559 |
safe_query("delete from user where user='$opt_user'"); |
|
560 |
safe_query("delete from db where user='$opt_user'"); |
|
561 |
safe_query("delete from tables_priv"); |
|
562 |
safe_query("delete from columns_priv"); |
|
563 |
safe_query("flush privileges"); |
|
564 |
||
565 |
print "end of test\n"; |
|
566 |
exit 0; |
|
567 |
||
568 |
sub usage |
|
569 |
{
|
|
570 |
print <<EOF; |
|
571 |
$0 Ver $version
|
|
572 |
||
573 |
This program tests that the GRANT commands works by creating a temporary
|
|
574 |
database ($opt_database) and user ($opt_user).
|
|
575 |
||
576 |
Options:
|
|
577 |
||
578 |
--database (Default $opt_database)
|
|
579 |
In which database the test tables are created.
|
|
580 |
||
581 |
--force
|
|
582 |
Don''t ask any question before starting this test.
|
|
583 |
||
584 |
--host='host name' (Default $opt_host)
|
|
585 |
Host name where the database server is located.
|
|
586 |
||
587 |
--Information
|
|
588 |
--help
|
|
589 |
Print this help
|
|
590 |
||
591 |
--password
|
|
592 |
Password for root-user.
|
|
593 |
||
594 |
--server='server name' (Default $opt_server)
|
|
595 |
Run the test on the given SQL server.
|
|
596 |
||
597 |
--user (Default $opt_user)
|
|
598 |
A non-existing user on which we will test the GRANT commands.
|
|
599 |
||
600 |
--verbose
|
|
601 |
Write all queries when we are execute them.
|
|
602 |
||
603 |
--root-user='user name' (Default $opt_root_user)
|
|
604 |
User with privileges to modify the 'mysql' database.
|
|
605 |
EOF
|
|
606 |
exit(0); |
|
607 |
}
|
|
608 |
||
609 |
||
610 |
sub print_info |
|
611 |
{
|
|
612 |
my $tmp; |
|
613 |
print <<EOF; |
|
614 |
This test will clear your table and column grant table and recreate the
|
|
615 |
$opt_database database !
|
|
616 |
All privileges for $user will be destroyed !
|
|
617 |
||
618 |
Don\'t run this test if you have done any GRANT commands that you want to keep!
|
|
619 |
EOF
|
|
620 |
for (;;) |
|
621 |
{
|
|
622 |
print "Start test (yes/no) ? "; |
|
623 |
$tmp=<STDIN>; chomp($tmp); $tmp=lc($tmp); |
|
624 |
last if ($tmp =~ /^yes$/i); |
|
625 |
exit 1 if ($tmp =~ /^n/i); |
|
626 |
print "\n"; |
|
627 |
}
|
|
628 |
}
|
|
629 |
||
630 |
||
631 |
sub user_connect |
|
632 |
{
|
|
633 |
my ($ignore_error,$password)=@_; |
|
634 |
$password="" if (!defined($password)); |
|
635 |
||
636 |
print "Connecting $opt_user\n" if ($opt_verbose); |
|
637 |
$user_dbh->disconnect if (defined($user_dbh)); |
|
638 |
||
639 |
$user_dbh=DBI->connect("DBI:mysql:$opt_database:$opt_host",$opt_user, |
|
640 |
$password, { PrintError => 0}); |
|
641 |
if (!$user_dbh) |
|
642 |
{
|
|
643 |
if ($opt_verbose || !$ignore_error) |
|
644 |
{
|
|
645 |
print "Error on connect: $DBI::errstr\n"; |
|
646 |
}
|
|
647 |
if (!$ignore_error) |
|
648 |
{
|
|
649 |
die "The above should not have failed!"; |
|
650 |
}
|
|
651 |
}
|
|
652 |
elsif ($ignore_error) |
|
653 |
{
|
|
654 |
die "Connect succeeded when it shouldn't have !\n"; |
|
655 |
}
|
|
656 |
}
|
|
657 |
||
658 |
sub safe_query |
|
659 |
{
|
|
660 |
my ($query,$ignore_error)=@_; |
|
661 |
if (do_query($dbh,$query, $ignore_error)) |
|
662 |
{
|
|
663 |
if (!defined($ignore_error)) |
|
664 |
{
|
|
665 |
die "The above should not have failed!"; |
|
666 |
}
|
|
667 |
}
|
|
668 |
elsif (defined($ignore_error) && $ignore_error == 1) |
|
669 |
{
|
|
670 |
die "Query '$query' succeeded when it shouldn't have !\n"; |
|
671 |
}
|
|
672 |
}
|
|
673 |
||
674 |
||
675 |
sub user_query |
|
676 |
{
|
|
677 |
my ($query,$ignore_error)=@_; |
|
678 |
if (do_query($user_dbh,$query, $ignore_error)) |
|
679 |
{
|
|
680 |
if (!defined($ignore_error)) |
|
681 |
{
|
|
682 |
die "Query '$query' should not have failed!"; |
|
683 |
}
|
|
684 |
}
|
|
685 |
elsif (defined($ignore_error) && $ignore_error == 1) |
|
686 |
{
|
|
687 |
die "Query '$query' succeeded when it shouldn't have !\n"; |
|
688 |
}
|
|
689 |
}
|
|
690 |
||
691 |
||
692 |
sub do_query |
|
693 |
{
|
|
694 |
my ($my_dbh, $query, $ignore_error)=@_; |
|
695 |
my ($sth, $row, $tab, $col, $found, $fatal_error); |
|
696 |
||
697 |
print "$query\n" if ($opt_debug || $opt_verbose); |
|
698 |
if (!($sth= $my_dbh->prepare($query))) |
|
699 |
{
|
|
700 |
print "Error in prepare: $DBI::errstr\n"; |
|
701 |
return 1; |
|
702 |
}
|
|
703 |
if (!$sth->execute) |
|
704 |
{
|
|
705 |
$fatal_error= ($DBI::errstr =~ /parse error/); |
|
706 |
if (!$ignore_error || ($opt_verbose && $ignore_error != 3) || $fatal_error) |
|
707 |
{
|
|
708 |
print "Error in execute: $DBI::errstr\n"; |
|
709 |
}
|
|
710 |
die if ($fatal_error); |
|
711 |
$sth->finish; |
|
712 |
return 1; |
|
713 |
}
|
|
714 |
$found=0; |
|
715 |
if (!$opt_silent) |
|
716 |
{
|
|
717 |
while (($row=$sth->fetchrow_arrayref)) |
|
718 |
{
|
|
719 |
$found=1; |
|
720 |
$tab=""; |
|
721 |
foreach $col (@$row) |
|
722 |
{
|
|
723 |
print $tab; |
|
724 |
print defined($col) ? $col : "NULL"; |
|
725 |
$tab="\t"; |
|
726 |
}
|
|
727 |
print "\n"; |
|
728 |
}
|
|
729 |
print "\n" if ($found); |
|
730 |
}
|
|
731 |
$sth->finish; |
|
732 |
return 0; |
|
733 |
}
|