Debian Codegraph @

mysql-test/suite/funcs_1/storedproc/storedproc_06.inc

raw
1 #### suite/funcs_1/storedproc/storedproc_06.inc
2
3 # This test cannot be used for the embedded server because we check here
4 # privileges.
5 --source include/not_embedded.inc
6
7 set GLOBAL sql_mode="";
8
9 --source suite/funcs_1/storedproc/load_sp_tb.inc
10
11 # ==============================================================================
12 # (numbering from requirement document TP v1.0, Last updated: 25 Jan 2005 01:00)
13 #
14 # 3.1.6 Privilege checks:
15 #
16 # 1. Ensure that no user may create a stored procedure without the
17 # GRANT CREATE ROUTINE privilege.
18 # 2. Ensure that root always has the GRANT CREATE ROUTINE privilege.
19 # 3. Ensure that a user with the GRANT CREATE ROUTINE privilege can always
20 # create both a procedure and a function, on any appropriate database.
21 # 4. Ensure that the default security provision of a stored procedure is
22 # SQL SECURITY DEFINER.
23 # 5. Ensure that a stored procedure defined with SQL SECURITY DEFINER can be
24 # called/executed by any user, using only the privileges (including
25 # database access privileges) associated with the user who created
26 # the stored procedure.
27 # 6. Ensure that a stored procedure defined with SQL SECURITY INVOKER can be
28 # called/executed by any user, using only the privileges (including
29 # database access privileges) associated with the user executing
30 # the stored procedure.
31 #
32 # ==============================================================================
33 let $message= Section 3.1.6 - Privilege Checks:;
34 --source include/show_msg80.inc
35
36
37 connection default;
38 USE db_storedproc_1;
39 --source suite/funcs_1/include/show_connection.inc
40
41 # ------------------------------------------------------------------------------
42 let $message= Testcase 3.1.6.1:
43 -----------------
44 Ensure that no user may create a stored procedure without the GRANT CREATE
45 ROUTINE privilege.;
46 --source include/show_msg80.inc
47
48 create user 'user_1'@'localhost';
49
50 grant all on db_storedproc_1.* to 'user_1'@'localhost';
51 revoke create routine on db_storedproc_1.* from 'user_1'@'localhost';
52 flush privileges;
53
54 --disable_warnings
55 DROP PROCEDURE IF EXISTS sp1;
56 --enable_warnings
57
58 connect (user1a, localhost, user_1, , db_storedproc_1);
59 --source suite/funcs_1/include/show_connection.inc
60
61 USE db_storedproc_1;
62
63 delimiter //;
64 --error ER_DBACCESS_DENIED_ERROR
65 CREATE PROCEDURE sp1(v1 char(20))
66 BEGIN
67 SELECT * from db_storedproc_1.t6 where t6.f2= 'xyz';
68 END//
69 delimiter ;//
70
71 disconnect user1a;
72
73 # add privilege again and check
74 connection default;
75 USE db_storedproc_1;
76 --source suite/funcs_1/include/show_connection.inc
77
78 GRANT CREATE ROUTINE ON db_storedproc_1.* TO 'user_1'@'localhost';
79 connect (user1b, localhost, user_1, , db_storedproc_1);
80 --source suite/funcs_1/include/show_connection.inc
81
82 USE db_storedproc_1;
83
84 delimiter //;
85 CREATE PROCEDURE sp1(v1 char(20))
86 BEGIN
87 SELECT * from db_storedproc_1.t6 where t6.f2= 'xyz';
88 END//
89 delimiter ;//
90 disconnect user1b;
91
92 # cleanup
93 connection default;
94 USE db_storedproc_1;
95 --source suite/funcs_1/include/show_connection.inc
96
97 DROP USER 'user_1'@'localhost';
98 DROP PROCEDURE sp1;
99
100
101 # ------------------------------------------------------------------------------
102 let $message= Testcase 3.1.6.2:
103 -----------------
104 Ensure that root always has the GRANT CREATE ROUTINE privilege.
105 (checked by other testscases);
106 --source include/show_msg80.inc
107
108
109 # ------------------------------------------------------------------------------
110 let $message= Testcase 3.1.6.3:
111 -----------------
112 Ensure that a user with the GRANT CREATE ROUTINE privilege can always create
113 both a procedure and a function, on any appropriate database.
114 --source include/show_msg80.inc
115
116
117 create user 'user_1'@'localhost';
118
119 grant create routine on db_storedproc_1.* to 'user_1'@'localhost';
120 flush privileges;
121
122 --disable_warnings
123 DROP PROCEDURE IF EXISTS db_storedproc_1.sp3;
124 DROP FUNCTION IF EXISTS db_storedproc_1.fn1;
125 --enable_warnings
126
127 # disconnect default;
128 connect (user2, localhost, user_1, , db_storedproc_1);
129 --source suite/funcs_1/include/show_connection.inc
130
131 delimiter //;
132 CREATE PROCEDURE sp3(v1 char(20))
133 BEGIN
134 SELECT * from db_storedproc_1.t6 where t6.f2= 'xyz';
135 END//
136 delimiter ;//
137
138 delimiter //;
139 CREATE FUNCTION fn1(v1 int) returns int
140 BEGIN
141 return v1;
142 END//
143 delimiter ;//
144
145 disconnect user2;
146
147 # cleanup
148 connection default;
149 USE db_storedproc_1;
150 --source suite/funcs_1/include/show_connection.inc
151
152 drop user 'user_1'@'localhost';
153 DROP PROCEDURE sp3;
154 DROP FUNCTION fn1;
155
156
157 # ------------------------------------------------------------------------------
158 let $message= Testcase 3.1.6.4:
159 -----------------
160 Ensure that the default security provision of a stored procedure is SQL SECURITY
161 DEFINER.;
162 --source include/show_msg80.inc
163
164 CREATE USER 'user_1'@'localhost';
165
166 grant update on db_storedproc_1.t6 to 'user_1'@'localhost';
167 grant execute on db_storedproc_1.* to 'user_1'@'localhost';
168 flush privileges;
169
170 USE db_storedproc_1;
171
172 --disable_warnings
173 DROP PROCEDURE IF EXISTS sp4;
174 --enable_warnings
175
176 delimiter //;
177 CREATE PROCEDURE sp4(v1 char(20))
178 BEGIN
179 SELECT * from db_storedproc_1.t6 where t6.f2= 'xyz';
180 END//
181 delimiter ;//
182
183 #disconnect default;
184
185 connect (user3, localhost, user_1, , db_storedproc_1);
186 --source suite/funcs_1/include/show_connection.inc
187
188 USE db_storedproc_1;
189 CALL sp4('a');
190
191 --vertical_results
192 SELECT SPECIFIC_NAME, ROUTINE_SCHEMA, ROUTINE_NAME, ROUTINE_TYPE,
193 ROUTINE_BODY, ROUTINE_DEFINITION, IS_DETERMINISTIC,
194 SQL_DATA_ACCESS, SECURITY_TYPE, SQL_MODE, ROUTINE_COMMENT
195 FROM information_schema.routines
196 WHERE routine_schema LIKE 'db_sto%';
197 --horizontal_results
198
199 disconnect user3;
200
201 # cleanup
202 connection default;
203 --source suite/funcs_1/include/show_connection.inc
204 DROP PROCEDURE sp4;
205 DROP USER 'user_1'@'localhost';
206
207
208 # ------------------------------------------------------------------------------
209 let $message= Testcase 3.1.6.5:
210 -----------------
211 Ensure that a stored procedure defined with SQL SECURITY DEFINER can be
212 called/executed by any user, using only the privileges (including database
213 access privileges) associated with the user who created the stored procedure.;
214 --source include/show_msg80.inc
215
216 USE db_storedproc_1;
217 CREATE TABLE t3165 ( c1 char(20), c2 char(20), c3 date);
218 INSERT INTO t3165 VALUES ('inserted', 'outside of SP', NULL);
219
220 # creates procedures
221 create user 'user_1'@'localhost';
222
223 #executes procedure
224 create user 'user_2'@'localhost';
225
226 grant create routine on db_storedproc_1.* to 'user_1'@'localhost';
227 grant SELECT on db_storedproc_1.* to 'user_2'@'localhost';
228 grant execute on db_storedproc_1.* to 'user_2'@'localhost';
229 flush privileges;
230
231 connect (user5_1, localhost, user_1, , db_storedproc_1);
232 --source suite/funcs_1/include/show_connection.inc
233
234 delimiter //;
235 CREATE PROCEDURE sp5_s_i () sql security definer
236 BEGIN
237 SELECT * from db_storedproc_1.t3165;
238 insert into db_storedproc_1.t3165 values ('inserted', 'from sp5_s_i', 1000);
239 END//
240
241 CREATE PROCEDURE sp5_sel () sql security definer
242 BEGIN
243 SELECT * from db_storedproc_1.t3165;
244 END//
245
246 CREATE PROCEDURE sp5_ins () sql security definer
247 BEGIN
248 insert into db_storedproc_1.t3165 values ('inserted', 'from sp5_ins', 1000);
249 END//
250 delimiter ;//
251
252 disconnect user5_1;
253
254 connect (user5_2, localhost, user_2, , db_storedproc_1);
255 --source suite/funcs_1/include/show_connection.inc
256
257 --error ER_TABLEACCESS_DENIED_ERROR
258 CALL sp5_s_i();
259 --error ER_TABLEACCESS_DENIED_ERROR
260 CALL sp5_ins();
261 --error ER_TABLEACCESS_DENIED_ERROR
262 CALL sp5_sel();
263
264 # now 'add' INSERT to DEFINER
265 connection default;
266 --source suite/funcs_1/include/show_connection.inc
267 --error ER_TABLEACCESS_DENIED_ERROR
268 CALL sp5_sel();
269 grant insert on db_storedproc_1.* to 'user_1'@'localhost';
270 flush privileges;
271
272 connection user5_2;
273 --source suite/funcs_1/include/show_connection.inc
274 --error ER_TABLEACCESS_DENIED_ERROR
275 CALL sp5_s_i();
276 CALL sp5_ins();
277 --error ER_TABLEACCESS_DENIED_ERROR
278 CALL sp5_sel();
279
280 # now 'add' SELECT to DEFINER
281 connection default;
282 --source suite/funcs_1/include/show_connection.inc
283 --error ER_TABLEACCESS_DENIED_ERROR
284 CALL sp5_sel();
285 grant SELECT on db_storedproc_1.* to 'user_1'@'localhost';
286 #grant execute on db_storedproc_1.* to 'user_2'@'localhost';
287 flush privileges;
288
289 connection user5_2;
290 --source suite/funcs_1/include/show_connection.inc
291 CALL sp5_s_i();
292 CALL sp5_ins();
293 CALL sp5_sel();
294
295 # now revoke INSERT FROM DEFINER
296 connection default;
297 --source suite/funcs_1/include/show_connection.inc
298 REVOKE INSERT on db_storedproc_1.* from 'user_1'@'localhost';
299 flush privileges;
300
301 connection user5_2;
302 --source suite/funcs_1/include/show_connection.inc
303 --error ER_TABLEACCESS_DENIED_ERROR
304 CALL sp5_s_i();
305 --error ER_TABLEACCESS_DENIED_ERROR
306 CALL sp5_ins();
307 CALL sp5_sel();
308
309 # now revoke SELECT FROM DEFINER
310 connection default;
311 --source suite/funcs_1/include/show_connection.inc
312 REVOKE SELECT on db_storedproc_1.* from 'user_1'@'localhost';
313 flush privileges;
314
315 connection user5_2;
316 --source suite/funcs_1/include/show_connection.inc
317 --error ER_TABLEACCESS_DENIED_ERROR
318 CALL sp5_s_i();
319 --error ER_TABLEACCESS_DENIED_ERROR
320 CALL sp5_ins();
321 --error ER_TABLEACCESS_DENIED_ERROR
322 CALL sp5_sel();
323
324 # cleanup
325 disconnect user5_2;
326 connection default;
327 --source suite/funcs_1/include/show_connection.inc
328
329 DROP PROCEDURE sp5_s_i;
330 DROP PROCEDURE sp5_sel;
331 DROP PROCEDURE sp5_ins;
332 DROP TABLE t3165;
333 DROP USER 'user_1'@'localhost';
334 DROP USER 'user_2'@'localhost';
335
336
337 # ------------------------------------------------------------------------------
338 let $message= Testcase 3.1.6.6:
339 -----------------
340 Ensure that a stored procedure defined with SQL SECURITY INVOKER can be
341 called/executed by any user, using only the privileges (including database
342 access privileges) associated with the user executing the stored procedure.;
343 --source include/show_msg80.inc
344
345 USE db_storedproc_1;
346 CREATE TABLE t3166 ( c1 char(30) );
347 INSERT INTO db_storedproc_1.t3166 VALUES ('inserted outside SP');
348
349 # DEFINER
350 create user 'user_1'@'localhost';
351
352 # INVOKER
353 create user 'user_2'@'localhost';
354
355 GRANT CREATE ROUTINE ON db_storedproc_1.* TO 'user_1'@'localhost';
356 GRANT SELECT ON db_storedproc_1.* TO 'user_2'@'localhost';
357 GRANT EXECUTE ON db_storedproc_1.* TO 'user_2'@'localhost';
358 FLUSH PRIVILEGES;
359
360 connect (user6_1, localhost, user_1, , db_storedproc_1);
361 --source suite/funcs_1/include/show_connection.inc
362
363 delimiter //;
364 CREATE PROCEDURE sp3166_s_i () SQL SECURITY INVOKER
365 BEGIN
366 SELECT * from db_storedproc_1.t3166;
367 insert into db_storedproc_1.t3166 values ('inserted from sp3166_s_i');
368 END//
369
370 CREATE PROCEDURE sp3166_sel () SQL SECURITY INVOKER
371 BEGIN
372 SELECT * from db_storedproc_1.t3166;
373 END//
374
375 CREATE PROCEDURE sp3166_ins () SQL SECURITY INVOKER
376 BEGIN
377 insert into db_storedproc_1.t3166 values ('inserted from sp3166_ins');
378 END//
379 delimiter ;//
380
381 disconnect user6_1;
382
383 connect (user6_2, localhost, user_2, , db_storedproc_1);
384 --source suite/funcs_1/include/show_connection.inc
385
386 --error ER_TABLEACCESS_DENIED_ERROR
387 CALL sp3166_s_i();
388 --error ER_TABLEACCESS_DENIED_ERROR
389 CALL sp3166_ins();
390 CALL sp3166_sel();
391
392 # now 'add' INSERT to INVOKER
393 connection default;
394 --source suite/funcs_1/include/show_connection.inc
395 CALL sp3166_sel();
396 GRANT INSERT ON db_storedproc_1.* TO 'user_2'@'localhost';
397 FLUSH PRIVILEGES;
398 disconnect user6_2;
399
400 connect (user6_3, localhost, user_2, , db_storedproc_1);
401 --source suite/funcs_1/include/show_connection.inc
402 CALL sp3166_s_i();
403 CALL sp3166_ins();
404 CALL sp3166_sel();
405 disconnect user6_3;
406
407 # now 'remove' SELECT from INVOKER
408 connection default;
409 --source suite/funcs_1/include/show_connection.inc
410 CALL sp3166_sel();
411 REVOKE SELECT ON db_storedproc_1.* FROM 'user_2'@'localhost';
412 FLUSH PRIVILEGES;
413
414 connect (user6_4, localhost, user_2, , db_storedproc_1);
415 --source suite/funcs_1/include/show_connection.inc
416 --error ER_TABLEACCESS_DENIED_ERROR
417 CALL sp3166_s_i();
418 CALL sp3166_ins();
419 --error ER_TABLEACCESS_DENIED_ERROR
420 CALL sp3166_sel();
421 disconnect user6_4;
422
423 # now 'remove' EXECUTE FROM INVOKER
424 connection default;
425 CALL sp3166_s_i();
426 --source suite/funcs_1/include/show_connection.inc
427 REVOKE EXECUTE on db_storedproc_1.* FROM 'user_2'@'localhost';
428 FLUSH PRIVILEGES;
429
430 connect (user6_5, localhost, user_2, , db_storedproc_1);
431 --source suite/funcs_1/include/show_connection.inc
432 --error ER_PROCACCESS_DENIED_ERROR
433 CALL sp3166_s_i();
434 --error ER_PROCACCESS_DENIED_ERROR
435 CALL sp3166_ins();
436 --error ER_PROCACCESS_DENIED_ERROR
437 CALL sp3166_sel();
438 disconnect user6_5;
439
440 # cleanup
441 connection default;
442 --source suite/funcs_1/include/show_connection.inc
443
444 DROP PROCEDURE sp3166_s_i;
445 DROP PROCEDURE sp3166_sel;
446 DROP PROCEDURE sp3166_ins;
447 DROP TABLE t3166;
448 DROP USER 'user_1'@'localhost';
449 DROP USER 'user_2'@'localhost';
450
451
452 # ==============================================================================
453 # USE the same .inc to cleanup before and after the test
454 --source suite/funcs_1/storedproc/cleanup_sp_tb.inc
455
456 set GLOBAL sql_mode=default;
457 # ==============================================================================
458 --echo
459 --echo . +++ END OF SCRIPT +++
460 --echo --------------------------------------------------------------------------------
461 # ==============================================================================