Debian Codegraph @

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

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