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';
113
DROP PROCEDURE IF EXISTS db_storedproc_1.sp3;
114
DROP FUNCTION IF EXISTS db_storedproc_1.fn1;
116
user_1@localhost db_storedproc_1
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';
134
Ensure that the default security provision of a stored procedure is SQL SECURITY
136
--------------------------------------------------------------------------------
137
CREATE USER 'user_1'@'localhost';
138
grant update on db_storedproc_1.t6 to 'user_1'@'localhost';
139
grant execute on db_storedproc_1.* to 'user_1'@'localhost';
142
DROP PROCEDURE IF EXISTS sp4;
143
CREATE PROCEDURE sp4(v1 char(20))
145
SELECT * from db_storedproc_1.t6 where t6.f2= 'xyz';
148
user_1@localhost db_storedproc_1
152
SELECT SPECIFIC_NAME, ROUTINE_SCHEMA, ROUTINE_NAME, ROUTINE_TYPE,
153
ROUTINE_BODY, ROUTINE_DEFINITION, IS_DETERMINISTIC,
154
SQL_DATA_ACCESS, SECURITY_TYPE, SQL_MODE, ROUTINE_COMMENT
155
FROM information_schema.routines
156
WHERE routine_schema LIKE 'db_sto%';
158
ROUTINE_SCHEMA db_storedproc_1
160
ROUTINE_TYPE PROCEDURE
162
ROUTINE_DEFINITION NULL
164
SQL_DATA_ACCESS CONTAINS SQL
165
SECURITY_TYPE DEFINER
166
SQL_MODE NO_ENGINE_SUBSTITUTION
169
root@localhost db_storedproc_1
171
DROP USER 'user_1'@'localhost';
175
Ensure that a stored procedure defined with SQL SECURITY DEFINER can be
176
called/executed by any user, using only the privileges (including database
177
access privileges) associated with the user who created the stored procedure.
178
--------------------------------------------------------------------------------
180
CREATE TABLE t3165 ( c1 char(20), c2 char(20), c3 date);
181
INSERT INTO t3165 VALUES ('inserted', 'outside of SP', NULL);
182
create user 'user_1'@'localhost';
183
create user 'user_2'@'localhost';
184
grant create routine on db_storedproc_1.* to 'user_1'@'localhost';
185
grant SELECT on db_storedproc_1.* to 'user_2'@'localhost';
186
grant execute on db_storedproc_1.* to 'user_2'@'localhost';
189
user_1@localhost db_storedproc_1
190
CREATE PROCEDURE sp5_s_i () sql security definer
192
SELECT * from db_storedproc_1.t3165;
193
insert into db_storedproc_1.t3165 values ('inserted', 'from sp5_s_i', 1000);
195
CREATE PROCEDURE sp5_sel () sql security definer
197
SELECT * from db_storedproc_1.t3165;
199
CREATE PROCEDURE sp5_ins () sql security definer
201
insert into db_storedproc_1.t3165 values ('inserted', 'from sp5_ins', 1000);
204
user_2@localhost db_storedproc_1
206
ERROR 42000: SELECT command denied to user 'user_1'@'localhost' for table 't3165'
208
ERROR 42000: INSERT command denied to user 'user_1'@'localhost' for table 't3165'
210
ERROR 42000: SELECT command denied to user 'user_1'@'localhost' for table 't3165'
212
root@localhost db_storedproc_1
214
ERROR 42000: SELECT command denied to user 'user_1'@'localhost' for table 't3165'
215
grant insert on db_storedproc_1.* to 'user_1'@'localhost';
218
user_2@localhost db_storedproc_1
220
ERROR 42000: SELECT command denied to user 'user_1'@'localhost' for table 't3165'
223
ERROR 42000: SELECT command denied to user 'user_1'@'localhost' for table 't3165'
225
root@localhost db_storedproc_1
227
ERROR 42000: SELECT command denied to user 'user_1'@'localhost' for table 't3165'
228
grant SELECT on db_storedproc_1.* to 'user_1'@'localhost';
231
user_2@localhost db_storedproc_1
234
inserted outside of SP NULL
235
inserted from sp5_ins 2000-10-00
239
inserted outside of SP NULL
240
inserted from sp5_ins 2000-10-00
241
inserted from sp5_s_i 2000-10-00
242
inserted from sp5_ins 2000-10-00
244
root@localhost db_storedproc_1
245
REVOKE INSERT on db_storedproc_1.* from 'user_1'@'localhost';
248
user_2@localhost db_storedproc_1
251
inserted outside of SP NULL
252
inserted from sp5_ins 2000-10-00
253
inserted from sp5_s_i 2000-10-00
254
inserted from sp5_ins 2000-10-00
255
ERROR 42000: INSERT command denied to user 'user_1'@'localhost' for table 't3165'
257
ERROR 42000: INSERT command denied to user 'user_1'@'localhost' for table 't3165'
260
inserted outside of SP NULL
261
inserted from sp5_ins 2000-10-00
262
inserted from sp5_s_i 2000-10-00
263
inserted from sp5_ins 2000-10-00
265
root@localhost db_storedproc_1
266
REVOKE SELECT on db_storedproc_1.* from 'user_1'@'localhost';
269
user_2@localhost db_storedproc_1
271
ERROR 42000: SELECT command denied to user 'user_1'@'localhost' for table 't3165'
273
ERROR 42000: INSERT command denied to user 'user_1'@'localhost' for table 't3165'
275
ERROR 42000: SELECT command denied to user 'user_1'@'localhost' for table 't3165'
277
root@localhost db_storedproc_1
278
DROP PROCEDURE sp5_s_i;
279
DROP PROCEDURE sp5_sel;
280
DROP PROCEDURE sp5_ins;
282
DROP USER 'user_1'@'localhost';
283
DROP USER 'user_2'@'localhost';
287
Ensure that a stored procedure defined with SQL SECURITY INVOKER can be
288
called/executed by any user, using only the privileges (including database
289
access privileges) associated with the user executing the stored procedure.
290
--------------------------------------------------------------------------------
292
CREATE TABLE t3166 ( c1 char(30) );
293
INSERT INTO db_storedproc_1.t3166 VALUES ('inserted outside SP');
294
create user 'user_1'@'localhost';
295
create user 'user_2'@'localhost';
296
GRANT CREATE ROUTINE ON db_storedproc_1.* TO 'user_1'@'localhost';
297
GRANT SELECT ON db_storedproc_1.* TO 'user_2'@'localhost';
298
GRANT EXECUTE ON db_storedproc_1.* TO 'user_2'@'localhost';
301
user_1@localhost db_storedproc_1
302
CREATE PROCEDURE sp3166_s_i () SQL SECURITY INVOKER
304
SELECT * from db_storedproc_1.t3166;
305
insert into db_storedproc_1.t3166 values ('inserted from sp3166_s_i');
307
CREATE PROCEDURE sp3166_sel () SQL SECURITY INVOKER
309
SELECT * from db_storedproc_1.t3166;
311
CREATE PROCEDURE sp3166_ins () SQL SECURITY INVOKER
313
insert into db_storedproc_1.t3166 values ('inserted from sp3166_ins');
316
user_2@localhost db_storedproc_1
320
ERROR 42000: INSERT command denied to user 'user_2'@'localhost' for table 't3166'
322
ERROR 42000: INSERT command denied to user 'user_2'@'localhost' for table 't3166'
327
root@localhost db_storedproc_1
331
GRANT INSERT ON db_storedproc_1.* TO 'user_2'@'localhost';
334
user_2@localhost db_storedproc_1
342
inserted from sp3166_s_i
343
inserted from sp3166_ins
345
root@localhost db_storedproc_1
349
inserted from sp3166_s_i
350
inserted from sp3166_ins
351
REVOKE SELECT ON db_storedproc_1.* FROM 'user_2'@'localhost';
354
user_2@localhost db_storedproc_1
356
ERROR 42000: SELECT command denied to user 'user_2'@'localhost' for table 't3166'
359
ERROR 42000: SELECT command denied to user 'user_2'@'localhost' for table 't3166'
363
inserted from sp3166_s_i
364
inserted from sp3166_ins
365
inserted from sp3166_ins
367
root@localhost db_storedproc_1
368
REVOKE EXECUTE on db_storedproc_1.* FROM 'user_2'@'localhost';
371
user_2@localhost db_storedproc_1
373
ERROR 42000: execute command denied to user 'user_2'@'localhost' for routine 'db_storedproc_1.sp3166_s_i'
375
ERROR 42000: execute command denied to user 'user_2'@'localhost' for routine 'db_storedproc_1.sp3166_ins'
377
ERROR 42000: execute command denied to user 'user_2'@'localhost' for routine 'db_storedproc_1.sp3166_sel'
379
root@localhost db_storedproc_1
380
DROP PROCEDURE sp3166_s_i;
381
DROP PROCEDURE sp3166_sel;
382
DROP PROCEDURE sp3166_ins;
384
DROP USER 'user_1'@'localhost';
385
DROP USER 'user_2'@'localhost';
387
--source suite/funcs_1/storedproc/cleanup_sp_tb.inc
388
--------------------------------------------------------------------------------
389
DROP DATABASE IF EXISTS db_storedproc;
390
DROP DATABASE IF EXISTS db_storedproc_1;
392
. +++ END OF SCRIPT +++
393
--------------------------------------------------------------------------------