2
--source suite/funcs_1/storedproc/load_sp_tb.inc
3
--------------------------------------------------------------------------------
4
SET @@global.max_heap_table_size=4294967295;
5
SET @@session.max_heap_table_size=4294967295;
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;
14
create table t1(f1 char(20),f2 char(25),f3 date,f4 int,f5 char(25),f6 int) engine = falcon;
15
load data infile 'MYSQL_TEST_DIR/suite/funcs_1/data/t4.txt' into table t1;
16
create table t2(f1 char(20),f2 char(25),f3 date,f4 int,f5 char(25),f6 int) engine = falcon;
17
load data infile 'MYSQL_TEST_DIR/suite/funcs_1/data/t4.txt' into table t2;
18
create table t3(f1 char(20),f2 char(20),f3 integer) engine = falcon;
19
load data infile 'MYSQL_TEST_DIR/suite/funcs_1/data/t3.txt' into table t3;
20
create table t4(f1 char(20),f2 char(25),f3 date,f4 int,f5 char(25),f6 int) engine = falcon;
21
load data infile 'MYSQL_TEST_DIR/suite/funcs_1/data/t4.txt' into table t4;
23
create table t6(f1 char(20),f2 char(25),f3 date,f4 int,f5 char(25),f6 int) engine = falcon;
24
load data infile 'MYSQL_TEST_DIR/suite/funcs_1/data/t4.txt' into table t6;
26
create table t7 (f1 char(20), f2 char(25), f3 date, f4 int) engine = falcon;
27
load data infile 'MYSQL_TEST_DIR/suite/funcs_1/data/t7.txt' into table t7;
29
Warning 1265 Data truncated for column 'f3' at row 1
30
Warning 1265 Data truncated for column 'f3' at row 2
31
Warning 1265 Data truncated for column 'f3' at row 3
32
Warning 1265 Data truncated for column 'f3' at row 4
33
Warning 1265 Data truncated for column 'f3' at row 5
34
Warning 1265 Data truncated for column 'f3' at row 6
35
Warning 1265 Data truncated for column 'f3' at row 7
36
Warning 1265 Data truncated for column 'f3' at row 8
37
Warning 1265 Data truncated for column 'f3' at row 9
38
Warning 1265 Data truncated for column 'f3' at row 10
39
create table t8 (f1 char(20), f2 char(25), f3 date, f4 int) engine = falcon;
40
load data infile 'MYSQL_TEST_DIR/suite/funcs_1/data/t7.txt' into table t8;
42
Warning 1265 Data truncated for column 'f3' at row 1
43
Warning 1265 Data truncated for column 'f3' at row 2
44
Warning 1265 Data truncated for column 'f3' at row 3
45
Warning 1265 Data truncated for column 'f3' at row 4
46
Warning 1265 Data truncated for column 'f3' at row 5
47
Warning 1265 Data truncated for column 'f3' at row 6
48
Warning 1265 Data truncated for column 'f3' at row 7
49
Warning 1265 Data truncated for column 'f3' at row 8
50
Warning 1265 Data truncated for column 'f3' at row 9
51
Warning 1265 Data truncated for column 'f3' at row 10
52
create table t9(f1 int, f2 char(25), f3 int) engine = falcon;
53
load data infile 'MYSQL_TEST_DIR/suite/funcs_1/data/t9.txt' into table t9;
54
create table t10(f1 char(20),f2 char(25),f3 date,f4 int,f5 char(25),f6 int) engine = falcon;
55
load data infile 'MYSQL_TEST_DIR/suite/funcs_1/data/t4.txt' into table t10;
56
create table t11(f1 char(20),f2 char(25),f3 date,f4 int,f5 char(25),f6 int) engine = falcon;
57
load data infile 'MYSQL_TEST_DIR/suite/funcs_1/data/t4.txt' into table t11;
59
Section 3.1.6 - Privilege Checks:
60
--------------------------------------------------------------------------------
63
root@localhost db_storedproc_1
67
Ensure that no user may create a stored procedure without the GRANT CREATE
69
--------------------------------------------------------------------------------
70
create user 'user_1'@'localhost';
71
grant all on db_storedproc_1.* to 'user_1'@'localhost';
72
revoke create routine on db_storedproc_1.* from 'user_1'@'localhost';
74
DROP PROCEDURE IF EXISTS sp1;
75
connect(localhost,user_1,,db_storedproc_1,MYSQL_PORT,MYSQL_SOCK);
77
user_1@localhost db_storedproc_1
79
CREATE PROCEDURE sp1(v1 char(20))
81
SELECT * from db_storedproc_1.t6 where t6.f2= 'xyz';
83
ERROR 42000: Access denied for user 'user_1'@'localhost' to database 'db_storedproc_1'
86
root@localhost db_storedproc_1
87
GRANT CREATE ROUTINE ON db_storedproc_1.* TO 'user_1'@'localhost';
88
connect(localhost,user_1,,db_storedproc_1,MYSQL_PORT,MYSQL_SOCK);
90
user_1@localhost db_storedproc_1
92
CREATE PROCEDURE sp1(v1 char(20))
94
SELECT * from db_storedproc_1.t6 where t6.f2= 'xyz';
98
root@localhost db_storedproc_1
99
DROP USER 'user_1'@'localhost';
104
Ensure that root always has the GRANT CREATE ROUTINE privilege.
105
(checked by other testscases)
106
--------------------------------------------------------------------------------
107
grant create routine on db_storedproc_1.* to 'user_1'@'localhost';
109
connect(localhost,user_1,,db_storedproc_1,MYSQL_PORT,MYSQL_SOCK);
111
user_1@localhost db_storedproc_1
112
DROP PROCEDURE IF EXISTS sp3;
113
DROP FUNCTION IF EXISTS fn1;
114
CREATE PROCEDURE sp3(v1 char(20))
116
SELECT * from db_storedproc_1.t6 where t6.f2= 'xyz';
118
CREATE FUNCTION fn1(v1 int) returns int
124
root@localhost db_storedproc_1
125
drop user 'user_1'@'localhost';
129
Error 1133 Can't find any matching row in the user table
130
Error 1269 Can't revoke all privileges for one or more of the requested users
131
Warning 1405 Failed to revoke all privileges to dropped routine
135
Ensure that the default security provision of a stored procedure is SQL SECURITY
137
--------------------------------------------------------------------------------
138
CREATE USER 'user_1'@'localhost';
139
grant update on db_storedproc_1.t6 to 'user_1'@'localhost';
140
grant execute on db_storedproc_1.* to 'user_1'@'localhost';
143
DROP PROCEDURE IF EXISTS sp4;
144
CREATE PROCEDURE sp4(v1 char(20))
146
SELECT * from db_storedproc_1.t6 where t6.f2= 'xyz';
148
connect(localhost,user_1,,db_storedproc_1,MYSQL_PORT,MYSQL_SOCK);
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';
190
connect(localhost,user_1,,db_storedproc_1,MYSQL_PORT,MYSQL_SOCK);
192
user_1@localhost db_storedproc_1
193
CREATE PROCEDURE sp5_s_i () sql security definer
195
SELECT * from db_storedproc_1.t3165;
196
insert into db_storedproc_1.t3165 values ('inserted', 'from sp5_s_i', 1000);
198
CREATE PROCEDURE sp5_sel () sql security definer
200
SELECT * from db_storedproc_1.t3165;
202
CREATE PROCEDURE sp5_ins () sql security definer
204
insert into db_storedproc_1.t3165 values ('inserted', 'from sp5_ins', 1000);
206
connect(localhost,user_2,,db_storedproc_1,MYSQL_PORT,MYSQL_SOCK);
208
user_2@localhost db_storedproc_1
210
ERROR 42000: SELECT command denied to user 'user_1'@'localhost' for table 't3165'
212
ERROR 42000: INSERT command denied to user 'user_1'@'localhost' for table 't3165'
214
ERROR 42000: SELECT command denied to user 'user_1'@'localhost' for table 't3165'
216
root@localhost db_storedproc_1
218
ERROR 42000: SELECT command denied to user 'user_1'@'localhost' for table 't3165'
219
grant insert on db_storedproc_1.* to 'user_1'@'localhost';
222
user_2@localhost db_storedproc_1
224
ERROR 42000: SELECT command denied to user 'user_1'@'localhost' for table 't3165'
227
ERROR 42000: SELECT command denied to user 'user_1'@'localhost' for table 't3165'
229
root@localhost db_storedproc_1
231
ERROR 42000: SELECT command denied to user 'user_1'@'localhost' for table 't3165'
232
grant SELECT on db_storedproc_1.* to 'user_1'@'localhost';
235
user_2@localhost db_storedproc_1
238
inserted outside of SP NULL
239
inserted from sp5_ins 2000-10-00
243
inserted outside of SP NULL
244
inserted from sp5_ins 2000-10-00
245
inserted from sp5_s_i 2000-10-00
246
inserted from sp5_ins 2000-10-00
248
root@localhost db_storedproc_1
249
REVOKE INSERT on db_storedproc_1.* from 'user_1'@'localhost';
252
user_2@localhost db_storedproc_1
255
inserted outside of SP NULL
256
inserted from sp5_ins 2000-10-00
257
inserted from sp5_s_i 2000-10-00
258
inserted from sp5_ins 2000-10-00
259
ERROR 42000: INSERT command denied to user 'user_1'@'localhost' for table 't3165'
261
ERROR 42000: INSERT command denied to user 'user_1'@'localhost' for table 't3165'
264
inserted outside of SP NULL
265
inserted from sp5_ins 2000-10-00
266
inserted from sp5_s_i 2000-10-00
267
inserted from sp5_ins 2000-10-00
269
root@localhost db_storedproc_1
270
REVOKE SELECT on db_storedproc_1.* from 'user_1'@'localhost';
273
user_2@localhost db_storedproc_1
275
ERROR 42000: SELECT command denied to user 'user_1'@'localhost' for table 't3165'
277
ERROR 42000: INSERT command denied to user 'user_1'@'localhost' for table 't3165'
279
ERROR 42000: SELECT command denied to user 'user_1'@'localhost' for table 't3165'
281
root@localhost db_storedproc_1
282
DROP PROCEDURE sp5_s_i;
283
DROP PROCEDURE sp5_sel;
284
DROP PROCEDURE sp5_ins;
286
DROP USER 'user_1'@'localhost';
287
DROP USER 'user_2'@'localhost';
291
Ensure that a stored procedure defined with SQL SECURITY INVOKER can be
292
called/executed by any user, using only the privileges (including database
293
access privileges) associated with the user executing the stored procedure.
294
--------------------------------------------------------------------------------
296
CREATE TABLE t3166 ( c1 char(30) );
297
INSERT INTO db_storedproc_1.t3166 VALUES ('inserted outside SP');
298
create user 'user_1'@'localhost';
299
create user 'user_2'@'localhost';
300
GRANT CREATE ROUTINE ON db_storedproc_1.* TO 'user_1'@'localhost';
301
GRANT SELECT ON db_storedproc_1.* TO 'user_2'@'localhost';
302
GRANT EXECUTE ON db_storedproc_1.* TO 'user_2'@'localhost';
304
connect(localhost,user_1,,db_storedproc_1,MYSQL_PORT,MYSQL_SOCK);
306
user_1@localhost db_storedproc_1
307
CREATE PROCEDURE sp3166_s_i () SQL SECURITY INVOKER
309
SELECT * from db_storedproc_1.t3166;
310
insert into db_storedproc_1.t3166 values ('inserted from sp3166_s_i');
312
CREATE PROCEDURE sp3166_sel () SQL SECURITY INVOKER
314
SELECT * from db_storedproc_1.t3166;
316
CREATE PROCEDURE sp3166_ins () SQL SECURITY INVOKER
318
insert into db_storedproc_1.t3166 values ('inserted from sp3166_ins');
320
connect(localhost,user_2,,db_storedproc_1,MYSQL_PORT,MYSQL_SOCK);
322
user_2@localhost db_storedproc_1
326
ERROR 42000: INSERT command denied to user 'user_2'@'localhost' for table 't3166'
328
ERROR 42000: INSERT command denied to user 'user_2'@'localhost' for table 't3166'
333
root@localhost db_storedproc_1
337
GRANT INSERT ON db_storedproc_1.* TO 'user_2'@'localhost';
339
connect(localhost,user_2,,db_storedproc_1,MYSQL_PORT,MYSQL_SOCK);
341
user_2@localhost db_storedproc_1
349
inserted from sp3166_s_i
350
inserted from sp3166_ins
352
root@localhost db_storedproc_1
356
inserted from sp3166_s_i
357
inserted from sp3166_ins
358
REVOKE SELECT ON db_storedproc_1.* FROM 'user_2'@'localhost';
360
connect(localhost,user_2,,db_storedproc_1,MYSQL_PORT,MYSQL_SOCK);
362
user_2@localhost db_storedproc_1
364
ERROR 42000: SELECT command denied to user 'user_2'@'localhost' for table 't3166'
367
ERROR 42000: SELECT command denied to user 'user_2'@'localhost' for table 't3166'
371
inserted from sp3166_s_i
372
inserted from sp3166_ins
373
inserted from sp3166_ins
375
root@localhost db_storedproc_1
376
REVOKE EXECUTE on db_storedproc_1.* FROM 'user_2'@'localhost';
378
connect(localhost,user_2,,db_storedproc_1,MYSQL_PORT,MYSQL_SOCK);
380
user_2@localhost db_storedproc_1
382
ERROR 42000: execute command denied to user 'user_2'@'localhost' for routine 'db_storedproc_1.sp3166_s_i'
384
ERROR 42000: execute command denied to user 'user_2'@'localhost' for routine 'db_storedproc_1.sp3166_ins'
386
ERROR 42000: execute command denied to user 'user_2'@'localhost' for routine 'db_storedproc_1.sp3166_sel'
388
root@localhost db_storedproc_1
389
DROP PROCEDURE sp3166_s_i;
390
DROP PROCEDURE sp3166_sel;
391
DROP PROCEDURE sp3166_ins;
393
DROP USER 'user_1'@'localhost';
394
DROP USER 'user_2'@'localhost';
396
--source suite/funcs_1/storedproc/cleanup_sp_tb.inc
397
--------------------------------------------------------------------------------
398
DROP DATABASE IF EXISTS db_storedproc;
399
DROP DATABASE IF EXISTS db_storedproc_1;
401
. +++ END OF SCRIPT +++
402
--------------------------------------------------------------------------------