Debian Codegraph @

mysql-test/suite/funcs_1/r/memory_storedproc_06.result

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