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) engine = innodb;
13
load data infile 'MYSQL_TEST_DIR/suite/funcs_1/data/t4.txt' into table t1;
14
create table t2(f1 char(20),f2 char(25),f3 date,f4 int,f5 char(25),f6 int) engine = innodb;
15
load data infile 'MYSQL_TEST_DIR/suite/funcs_1/data/t4.txt' into table t2;
16
create table t3(f1 char(20),f2 char(20),f3 integer) engine = innodb;
17
load data infile 'MYSQL_TEST_DIR/suite/funcs_1/data/t3.txt' into table t3;
18
create table t4(f1 char(20),f2 char(25),f3 date,f4 int,f5 char(25),f6 int) engine = innodb;
19
load data infile 'MYSQL_TEST_DIR/suite/funcs_1/data/t4.txt' into table t4;
21
create table t6(f1 char(20),f2 char(25),f3 date,f4 int,f5 char(25),f6 int) engine = innodb;
22
load data infile 'MYSQL_TEST_DIR/suite/funcs_1/data/t4.txt' into table t6;
24
create table t7 (f1 char(20), f2 char(25), f3 date, f4 int) engine = innodb;
25
load data infile 'MYSQL_TEST_DIR/suite/funcs_1/data/t7.txt' into table t7;
27
Warning 1265 Data truncated for column 'f3' at row 1
28
Warning 1265 Data truncated for column 'f3' at row 2
29
Warning 1265 Data truncated for column 'f3' at row 3
30
Warning 1265 Data truncated for column 'f3' at row 4
31
Warning 1265 Data truncated for column 'f3' at row 5
32
Warning 1265 Data truncated for column 'f3' at row 6
33
Warning 1265 Data truncated for column 'f3' at row 7
34
Warning 1265 Data truncated for column 'f3' at row 8
35
Warning 1265 Data truncated for column 'f3' at row 9
36
Warning 1265 Data truncated for column 'f3' at row 10
37
create table t8 (f1 char(20), f2 char(25), f3 date, f4 int) engine = innodb;
38
load data infile 'MYSQL_TEST_DIR/suite/funcs_1/data/t7.txt' into table t8;
40
Warning 1265 Data truncated for column 'f3' at row 1
41
Warning 1265 Data truncated for column 'f3' at row 2
42
Warning 1265 Data truncated for column 'f3' at row 3
43
Warning 1265 Data truncated for column 'f3' at row 4
44
Warning 1265 Data truncated for column 'f3' at row 5
45
Warning 1265 Data truncated for column 'f3' at row 6
46
Warning 1265 Data truncated for column 'f3' at row 7
47
Warning 1265 Data truncated for column 'f3' at row 8
48
Warning 1265 Data truncated for column 'f3' at row 9
49
Warning 1265 Data truncated for column 'f3' at row 10
50
create table t9(f1 int, f2 char(25), f3 int) engine = innodb;
51
load data infile 'MYSQL_TEST_DIR/suite/funcs_1/data/t9.txt' into table t9;
52
create table t10(f1 char(20),f2 char(25),f3 date,f4 int,f5 char(25),f6 int) engine = innodb;
53
load data infile 'MYSQL_TEST_DIR/suite/funcs_1/data/t4.txt' into table t10;
54
create table t11(f1 char(20),f2 char(25),f3 date,f4 int,f5 char(25),f6 int) engine = innodb;
55
load data infile 'MYSQL_TEST_DIR/suite/funcs_1/data/t4.txt' into table t11;
57
Section 3.1.6 - Privilege Checks:
58
--------------------------------------------------------------------------------
61
root@localhost db_storedproc_1
65
Ensure that no user may create a stored procedure without the GRANT CREATE
67
--------------------------------------------------------------------------------
68
create user 'user_1'@'localhost';
69
grant all on db_storedproc_1.* to 'user_1'@'localhost';
70
revoke create routine on db_storedproc_1.* from 'user_1'@'localhost';
72
DROP PROCEDURE IF EXISTS sp1;
73
connect(localhost,user_1,,db_storedproc_1,MYSQL_PORT,MYSQL_SOCK);
75
user_1@localhost db_storedproc_1
77
CREATE PROCEDURE sp1(v1 char(20))
79
SELECT * from db_storedproc_1.t6 where t6.f2= 'xyz';
81
ERROR 42000: Access denied for user 'user_1'@'localhost' to database 'db_storedproc_1'
84
root@localhost db_storedproc_1
85
GRANT CREATE ROUTINE ON db_storedproc_1.* TO 'user_1'@'localhost';
86
connect(localhost,user_1,,db_storedproc_1,MYSQL_PORT,MYSQL_SOCK);
88
user_1@localhost db_storedproc_1
90
CREATE PROCEDURE sp1(v1 char(20))
92
SELECT * from db_storedproc_1.t6 where t6.f2= 'xyz';
96
root@localhost db_storedproc_1
97
DROP USER 'user_1'@'localhost';
102
Ensure that root always has the GRANT CREATE ROUTINE privilege.
103
(checked by other testscases)
104
--------------------------------------------------------------------------------
105
grant create routine on db_storedproc_1.* to 'user_1'@'localhost';
107
connect(localhost,user_1,,db_storedproc_1,MYSQL_PORT,MYSQL_SOCK);
109
user_1@localhost db_storedproc_1
110
DROP PROCEDURE IF EXISTS sp3;
111
DROP FUNCTION IF EXISTS fn1;
112
CREATE PROCEDURE sp3(v1 char(20))
114
SELECT * from db_storedproc_1.t6 where t6.f2= 'xyz';
116
CREATE FUNCTION fn1(v1 int) returns int
122
root@localhost db_storedproc_1
123
drop user 'user_1'@'localhost';
127
Error 1133 Can't find any matching row in the user table
128
Error 1269 Can't revoke all privileges for one or more of the requested users
129
Warning 1405 Failed to revoke all privileges to dropped routine
133
Ensure that the default security provision of a stored procedure is SQL SECURITY
135
--------------------------------------------------------------------------------
136
CREATE USER 'user_1'@'localhost';
137
grant update on db_storedproc_1.t6 to 'user_1'@'localhost';
138
grant execute on db_storedproc_1.* to 'user_1'@'localhost';
141
DROP PROCEDURE IF EXISTS sp4;
142
CREATE PROCEDURE sp4(v1 char(20))
144
SELECT * from db_storedproc_1.t6 where t6.f2= 'xyz';
146
connect(localhost,user_1,,db_storedproc_1,MYSQL_PORT,MYSQL_SOCK);
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
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';
188
connect(localhost,user_1,,db_storedproc_1,MYSQL_PORT,MYSQL_SOCK);
190
user_1@localhost db_storedproc_1
191
CREATE PROCEDURE sp5_s_i () sql security definer
193
SELECT * from db_storedproc_1.t3165;
194
insert into db_storedproc_1.t3165 values ('inserted', 'from sp5_s_i', 1000);
196
CREATE PROCEDURE sp5_sel () sql security definer
198
SELECT * from db_storedproc_1.t3165;
200
CREATE PROCEDURE sp5_ins () sql security definer
202
insert into db_storedproc_1.t3165 values ('inserted', 'from sp5_ins', 1000);
204
connect(localhost,user_2,,db_storedproc_1,MYSQL_PORT,MYSQL_SOCK);
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';
302
connect(localhost,user_1,,db_storedproc_1,MYSQL_PORT,MYSQL_SOCK);
304
user_1@localhost db_storedproc_1
305
CREATE PROCEDURE sp3166_s_i () SQL SECURITY INVOKER
307
SELECT * from db_storedproc_1.t3166;
308
insert into db_storedproc_1.t3166 values ('inserted from sp3166_s_i');
310
CREATE PROCEDURE sp3166_sel () SQL SECURITY INVOKER
312
SELECT * from db_storedproc_1.t3166;
314
CREATE PROCEDURE sp3166_ins () SQL SECURITY INVOKER
316
insert into db_storedproc_1.t3166 values ('inserted from sp3166_ins');
318
connect(localhost,user_2,,db_storedproc_1,MYSQL_PORT,MYSQL_SOCK);
320
user_2@localhost db_storedproc_1
324
ERROR 42000: INSERT command denied to user 'user_2'@'localhost' for table 't3166'
326
ERROR 42000: INSERT command denied to user 'user_2'@'localhost' for table 't3166'
331
root@localhost db_storedproc_1
335
GRANT INSERT ON db_storedproc_1.* TO 'user_2'@'localhost';
337
connect(localhost,user_2,,db_storedproc_1,MYSQL_PORT,MYSQL_SOCK);
339
user_2@localhost db_storedproc_1
347
inserted from sp3166_s_i
348
inserted from sp3166_ins
350
root@localhost db_storedproc_1
354
inserted from sp3166_s_i
355
inserted from sp3166_ins
356
REVOKE SELECT ON db_storedproc_1.* FROM 'user_2'@'localhost';
358
connect(localhost,user_2,,db_storedproc_1,MYSQL_PORT,MYSQL_SOCK);
360
user_2@localhost db_storedproc_1
362
ERROR 42000: SELECT command denied to user 'user_2'@'localhost' for table 't3166'
365
ERROR 42000: SELECT command denied to user 'user_2'@'localhost' for table 't3166'
369
inserted from sp3166_s_i
370
inserted from sp3166_ins
371
inserted from sp3166_ins
373
root@localhost db_storedproc_1
374
REVOKE EXECUTE on db_storedproc_1.* FROM 'user_2'@'localhost';
376
connect(localhost,user_2,,db_storedproc_1,MYSQL_PORT,MYSQL_SOCK);
378
user_2@localhost db_storedproc_1
380
ERROR 42000: execute command denied to user 'user_2'@'localhost' for routine 'db_storedproc_1.sp3166_s_i'
382
ERROR 42000: execute command denied to user 'user_2'@'localhost' for routine 'db_storedproc_1.sp3166_ins'
384
ERROR 42000: execute command denied to user 'user_2'@'localhost' for routine 'db_storedproc_1.sp3166_sel'
386
root@localhost db_storedproc_1
387
DROP PROCEDURE sp3166_s_i;
388
DROP PROCEDURE sp3166_sel;
389
DROP PROCEDURE sp3166_ins;
391
DROP USER 'user_1'@'localhost';
392
DROP USER 'user_2'@'localhost';
394
--source suite/funcs_1/storedproc/cleanup_sp_tb.inc
395
--------------------------------------------------------------------------------
396
DROP DATABASE IF EXISTS db_storedproc;
397
DROP DATABASE IF EXISTS db_storedproc_1;
399
. +++ END OF SCRIPT +++
400
--------------------------------------------------------------------------------