Debian Codegraph @

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

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