2 --source suite/funcs_1/storedproc/load_sp_tb.inc
3 --------------------------------------------------------------------------------
5 --source suite/funcs_1/storedproc/cleanup_sp_tb.inc
6 --------------------------------------------------------------------------------
7 DROP DATABASE IF EXISTS db_storedproc;
8 DROP DATABASE IF EXISTS db_storedproc_1;
9 CREATE DATABASE db_storedproc;
10 CREATE DATABASE db_storedproc_1;
12 create table t1(f1 char(20),f2 char(25),f3 date,f4 int,f5 char(25),f6 int)
13 engine = <engine_to_be_tested>;
14 load data infile '<MYSQLTEST_VARDIR>/std_data/funcs_1/t4.txt' into table t1;
15 create table t2(f1 char(20),f2 char(25),f3 date,f4 int,f5 char(25),f6 int)
16 engine = <engine_to_be_tested>;
17 load data infile '<MYSQLTEST_VARDIR>/std_data/funcs_1/t4.txt' into table t2;
18 create table t3(f1 char(20),f2 char(20),f3 integer) engine = <engine_to_be_tested>;
19 load data infile '<MYSQLTEST_VARDIR>/std_data/funcs_1/t3.txt' into table t3;
20 create table t4(f1 char(20),f2 char(25),f3 date,f4 int,f5 char(25),f6 int)
21 engine = <engine_to_be_tested>;
22 load data infile '<MYSQLTEST_VARDIR>/std_data/funcs_1/t4.txt' into table t4;
24 create table t6(f1 char(20),f2 char(25),f3 date,f4 int,f5 char(25),f6 int)
25 engine = <engine_to_be_tested>;
26 load data infile '<MYSQLTEST_VARDIR>/std_data/funcs_1/t4.txt' into table t6;
28 create table t7 (f1 char(20), f2 char(25), f3 date, f4 int)
29 engine = <engine_to_be_tested>;
30 load data infile '<MYSQLTEST_VARDIR>/std_data/funcs_1/t7.txt' into table t7;
32 Warning 1265 Data truncated for column 'f3' at row 1
33 Warning 1265 Data truncated for column 'f3' at row 2
34 Warning 1265 Data truncated for column 'f3' at row 3
35 Warning 1265 Data truncated for column 'f3' at row 4
36 Warning 1265 Data truncated for column 'f3' at row 5
37 Warning 1265 Data truncated for column 'f3' at row 6
38 Warning 1265 Data truncated for column 'f3' at row 7
39 Warning 1265 Data truncated for column 'f3' at row 8
40 Warning 1265 Data truncated for column 'f3' at row 9
41 Warning 1265 Data truncated for column 'f3' at row 10
42 create table t8 (f1 char(20), f2 char(25), f3 date, f4 int)
43 engine = <engine_to_be_tested>;
44 load data infile '<MYSQLTEST_VARDIR>/std_data/funcs_1/t7.txt' into table t8;
46 Warning 1265 Data truncated for column 'f3' at row 1
47 Warning 1265 Data truncated for column 'f3' at row 2
48 Warning 1265 Data truncated for column 'f3' at row 3
49 Warning 1265 Data truncated for column 'f3' at row 4
50 Warning 1265 Data truncated for column 'f3' at row 5
51 Warning 1265 Data truncated for column 'f3' at row 6
52 Warning 1265 Data truncated for column 'f3' at row 7
53 Warning 1265 Data truncated for column 'f3' at row 8
54 Warning 1265 Data truncated for column 'f3' at row 9
55 Warning 1265 Data truncated for column 'f3' at row 10
56 create table t9(f1 int, f2 char(25), f3 int) engine = <engine_to_be_tested>;
57 load data infile '<MYSQLTEST_VARDIR>/std_data/funcs_1/t9.txt' into table t9;
58 create table t10(f1 char(20),f2 char(25),f3 date,f4 int,f5 char(25),f6 int)
59 engine = <engine_to_be_tested>;
60 load data infile '<MYSQLTEST_VARDIR>/std_data/funcs_1/t4.txt' into table t10;
61 create table t11(f1 char(20),f2 char(25),f3 date,f4 int,f5 char(25),f6 int)
62 engine = <engine_to_be_tested>;
63 load data infile '<MYSQLTEST_VARDIR>/std_data/funcs_1/t4.txt' into table t11;
65 Section 3.1.6 - Privilege Checks:
66 --------------------------------------------------------------------------------
69 root@localhost db_storedproc_1
73 Ensure that no user may create a stored procedure without the GRANT CREATE
75 --------------------------------------------------------------------------------
76 create user 'user_1'@'localhost';
77 grant all on db_storedproc_1.* to 'user_1'@'localhost';
78 revoke create routine on db_storedproc_1.* from 'user_1'@'localhost';
80 DROP PROCEDURE IF EXISTS sp1;
82 user_1@localhost db_storedproc_1
84 CREATE PROCEDURE sp1(v1 char(20))
86 SELECT * from db_storedproc_1.t6 where t6.f2= 'xyz';
88 ERROR 42000: Access denied for user 'user_1'@'localhost' to database 'db_storedproc_1'
91 root@localhost db_storedproc_1
92 GRANT CREATE ROUTINE ON db_storedproc_1.* TO 'user_1'@'localhost';
94 user_1@localhost db_storedproc_1
96 CREATE PROCEDURE sp1(v1 char(20))
98 SELECT * from db_storedproc_1.t6 where t6.f2= 'xyz';
102 root@localhost db_storedproc_1
103 DROP USER 'user_1'@'localhost';
108 Ensure that root always has the GRANT CREATE ROUTINE privilege.
109 (checked by other testscases)
110 --------------------------------------------------------------------------------
111 grant create routine on db_storedproc_1.* to 'user_1'@'localhost';
114 user_1@localhost db_storedproc_1
115 DROP PROCEDURE IF EXISTS sp3;
116 DROP FUNCTION IF EXISTS fn1;
117 CREATE PROCEDURE sp3(v1 char(20))
119 SELECT * from db_storedproc_1.t6 where t6.f2= 'xyz';
121 CREATE FUNCTION fn1(v1 int) returns int
127 root@localhost db_storedproc_1
128 drop user 'user_1'@'localhost';
132 Warning 1403 There is no such grant defined for user 'user_1' on host 'localhost' on routine 'fn1'
136 Ensure that the default security provision of a stored procedure is SQL SECURITY
138 --------------------------------------------------------------------------------
139 CREATE USER 'user_1'@'localhost';
140 grant update on db_storedproc_1.t6 to 'user_1'@'localhost';
141 grant execute on db_storedproc_1.* to 'user_1'@'localhost';
144 DROP PROCEDURE IF EXISTS sp4;
145 CREATE PROCEDURE sp4(v1 char(20))
147 SELECT * from db_storedproc_1.t6 where t6.f2= 'xyz';
150 user_1@localhost db_storedproc_1
154 SELECT SPECIFIC_NAME, ROUTINE_SCHEMA, ROUTINE_NAME, ROUTINE_TYPE,
155 ROUTINE_BODY, ROUTINE_DEFINITION, IS_DETERMINISTIC,
156 SQL_DATA_ACCESS, SECURITY_TYPE, SQL_MODE, ROUTINE_COMMENT
157 FROM information_schema.routines
158 WHERE routine_schema LIKE 'db_sto%';
160 ROUTINE_SCHEMA db_storedproc_1
162 ROUTINE_TYPE PROCEDURE
164 ROUTINE_DEFINITION NULL
166 SQL_DATA_ACCESS CONTAINS SQL
167 SECURITY_TYPE DEFINER
171 root@localhost db_storedproc_1
173 DROP USER 'user_1'@'localhost';
177 Ensure that a stored procedure defined with SQL SECURITY DEFINER can be
178 called/executed by any user, using only the privileges (including database
179 access privileges) associated with the user who created the stored procedure.
180 --------------------------------------------------------------------------------
182 CREATE TABLE t3165 ( c1 char(20), c2 char(20), c3 date);
183 INSERT INTO t3165 VALUES ('inserted', 'outside of SP', NULL);
184 create user 'user_1'@'localhost';
185 create user 'user_2'@'localhost';
186 grant create routine on db_storedproc_1.* to 'user_1'@'localhost';
187 grant SELECT on db_storedproc_1.* to 'user_2'@'localhost';
188 grant execute on db_storedproc_1.* to 'user_2'@'localhost';
191 user_1@localhost db_storedproc_1
192 CREATE PROCEDURE sp5_s_i () sql security definer
194 SELECT * from db_storedproc_1.t3165;
195 insert into db_storedproc_1.t3165 values ('inserted', 'from sp5_s_i', 1000);
197 CREATE PROCEDURE sp5_sel () sql security definer
199 SELECT * from db_storedproc_1.t3165;
201 CREATE PROCEDURE sp5_ins () sql security definer
203 insert into db_storedproc_1.t3165 values ('inserted', 'from sp5_ins', 1000);
206 user_2@localhost db_storedproc_1
208 ERROR 42000: SELECT command denied to user 'user_1'@'localhost' for table 't3165'
210 ERROR 42000: INSERT command denied to user 'user_1'@'localhost' for table 't3165'
212 ERROR 42000: SELECT command denied to user 'user_1'@'localhost' for table 't3165'
214 root@localhost db_storedproc_1
216 ERROR 42000: SELECT command denied to user 'user_1'@'localhost' for table 't3165'
217 grant insert on db_storedproc_1.* to 'user_1'@'localhost';
220 user_2@localhost db_storedproc_1
222 ERROR 42000: SELECT command denied to user 'user_1'@'localhost' for table 't3165'
225 ERROR 42000: SELECT command denied to user 'user_1'@'localhost' for table 't3165'
227 root@localhost db_storedproc_1
229 ERROR 42000: SELECT command denied to user 'user_1'@'localhost' for table 't3165'
230 grant SELECT on db_storedproc_1.* to 'user_1'@'localhost';
233 user_2@localhost db_storedproc_1
236 inserted outside of SP NULL
237 inserted from sp5_ins 2000-10-00
241 inserted outside of SP NULL
242 inserted from sp5_ins 2000-10-00
243 inserted from sp5_s_i 2000-10-00
244 inserted from sp5_ins 2000-10-00
246 root@localhost db_storedproc_1
247 REVOKE INSERT on db_storedproc_1.* from 'user_1'@'localhost';
250 user_2@localhost db_storedproc_1
253 inserted outside of SP NULL
254 inserted from sp5_ins 2000-10-00
255 inserted from sp5_s_i 2000-10-00
256 inserted from sp5_ins 2000-10-00
257 ERROR 42000: INSERT command denied to user 'user_1'@'localhost' for table 't3165'
259 ERROR 42000: INSERT command denied to user 'user_1'@'localhost' for table 't3165'
262 inserted outside of SP NULL
263 inserted from sp5_ins 2000-10-00
264 inserted from sp5_s_i 2000-10-00
265 inserted from sp5_ins 2000-10-00
267 root@localhost db_storedproc_1
268 REVOKE SELECT on db_storedproc_1.* from 'user_1'@'localhost';
271 user_2@localhost db_storedproc_1
273 ERROR 42000: SELECT command denied to user 'user_1'@'localhost' for table 't3165'
275 ERROR 42000: INSERT command denied to user 'user_1'@'localhost' for table 't3165'
277 ERROR 42000: SELECT command denied to user 'user_1'@'localhost' for table 't3165'
279 root@localhost db_storedproc_1
280 DROP PROCEDURE sp5_s_i;
281 DROP PROCEDURE sp5_sel;
282 DROP PROCEDURE sp5_ins;
284 DROP USER 'user_1'@'localhost';
285 DROP USER 'user_2'@'localhost';
289 Ensure that a stored procedure defined with SQL SECURITY INVOKER can be
290 called/executed by any user, using only the privileges (including database
291 access privileges) associated with the user executing the stored procedure.
292 --------------------------------------------------------------------------------
294 CREATE TABLE t3166 ( c1 char(30) );
295 INSERT INTO db_storedproc_1.t3166 VALUES ('inserted outside SP');
296 create user 'user_1'@'localhost';
297 create user 'user_2'@'localhost';
298 GRANT CREATE ROUTINE ON db_storedproc_1.* TO 'user_1'@'localhost';
299 GRANT SELECT ON db_storedproc_1.* TO 'user_2'@'localhost';
300 GRANT EXECUTE ON db_storedproc_1.* TO 'user_2'@'localhost';
303 user_1@localhost db_storedproc_1
304 CREATE PROCEDURE sp3166_s_i () SQL SECURITY INVOKER
306 SELECT * from db_storedproc_1.t3166;
307 insert into db_storedproc_1.t3166 values ('inserted from sp3166_s_i');
309 CREATE PROCEDURE sp3166_sel () SQL SECURITY INVOKER
311 SELECT * from db_storedproc_1.t3166;
313 CREATE PROCEDURE sp3166_ins () SQL SECURITY INVOKER
315 insert into db_storedproc_1.t3166 values ('inserted from sp3166_ins');
318 user_2@localhost db_storedproc_1
322 ERROR 42000: INSERT command denied to user 'user_2'@'localhost' for table 't3166'
324 ERROR 42000: INSERT command denied to user 'user_2'@'localhost' for table 't3166'
329 root@localhost db_storedproc_1
333 GRANT INSERT ON db_storedproc_1.* TO 'user_2'@'localhost';
336 user_2@localhost db_storedproc_1
344 inserted from sp3166_s_i
345 inserted from sp3166_ins
347 root@localhost db_storedproc_1
351 inserted from sp3166_s_i
352 inserted from sp3166_ins
353 REVOKE SELECT ON db_storedproc_1.* FROM 'user_2'@'localhost';
356 user_2@localhost db_storedproc_1
358 ERROR 42000: SELECT command denied to user 'user_2'@'localhost' for table 't3166'
361 ERROR 42000: SELECT command denied to user 'user_2'@'localhost' for table 't3166'
365 inserted from sp3166_s_i
366 inserted from sp3166_ins
367 inserted from sp3166_ins
369 root@localhost db_storedproc_1
370 REVOKE EXECUTE on db_storedproc_1.* FROM 'user_2'@'localhost';
373 user_2@localhost db_storedproc_1
375 ERROR 42000: execute command denied to user 'user_2'@'localhost' for routine 'db_storedproc_1.sp3166_s_i'
377 ERROR 42000: execute command denied to user 'user_2'@'localhost' for routine 'db_storedproc_1.sp3166_ins'
379 ERROR 42000: execute command denied to user 'user_2'@'localhost' for routine 'db_storedproc_1.sp3166_sel'
381 root@localhost db_storedproc_1
382 DROP PROCEDURE sp3166_s_i;
383 DROP PROCEDURE sp3166_sel;
384 DROP PROCEDURE sp3166_ins;
386 DROP USER 'user_1'@'localhost';
387 DROP USER 'user_2'@'localhost';
389 --source suite/funcs_1/storedproc/cleanup_sp_tb.inc
390 --------------------------------------------------------------------------------
391 DROP DATABASE IF EXISTS db_storedproc;
392 DROP DATABASE IF EXISTS db_storedproc_1;
394 . +++ END OF SCRIPT +++
395 --------------------------------------------------------------------------------