1
#### suite/funcs_1/storedproc/storedproc_06.inc
3
--source suite/funcs_1/storedproc/load_sp_tb.inc
5
# ==============================================================================
6
# (numbering from requirement document TP v1.0, Last updated: 25 Jan 2005 01:00)
8
# 3.1.6 Privilege checks:
10
# 1. Ensure that no user may create a stored procedure without the GRANT CREATE ROUTINE privilege.
11
# 2. Ensure that root always has the GRANT CREATE ROUTINE privilege.
12
# 3. Ensure that a user with the GRANT CREATE ROUTINE privilege can always create both a procedure and a function, on any appropriate database.
13
# 4. Ensure that the default security provision of a stored procedure is SQL SECURITY DEFINER.
14
# 5. Ensure that a stored procedure defined with SQL SECURITY DEFINER can be called/executed by any user, using only the privileges (including database access privileges) associated with the user who created the stored procedure.
15
# 6. Ensure that a stored procedure defined with SQL SECURITY INVOKER can be called/executed by any user, using only the privileges (including database access privileges) associated with the user executing the stored procedure.
17
# ==============================================================================
18
let $message= Section 3.1.6 - Privilege Checks:;
19
--source include/show_msg80.inc
24
--source suite/funcs_1/include/show_connection.inc
26
# ------------------------------------------------------------------------------
27
let $message= Testcase 3.1.6.1:
29
Ensure that no user may create a stored procedure without the GRANT CREATE
31
--source include/show_msg80.inc
33
create user 'user_1'@'localhost';
35
grant all on db_storedproc_1.* to 'user_1'@'localhost';
36
revoke create routine on db_storedproc_1.* from 'user_1'@'localhost';
40
DROP PROCEDURE IF EXISTS sp1;
43
--replace_result $MASTER_MYPORT MYSQL_PORT $MASTER_MYSOCK MYSQL_SOCK
44
connect (user1a, localhost, user_1, , db_storedproc_1);
45
--source suite/funcs_1/include/show_connection.inc
51
CREATE PROCEDURE sp1(v1 char(20))
53
SELECT * from db_storedproc_1.t6 where t6.f2= 'xyz';
59
# add privilege again and check
62
--source suite/funcs_1/include/show_connection.inc
64
GRANT CREATE ROUTINE ON db_storedproc_1.* TO 'user_1'@'localhost';
65
--replace_result $MASTER_MYPORT MYSQL_PORT $MASTER_MYSOCK MYSQL_SOCK
66
connect (user1b, localhost, user_1, , db_storedproc_1);
67
--source suite/funcs_1/include/show_connection.inc
72
CREATE PROCEDURE sp1(v1 char(20))
74
SELECT * from db_storedproc_1.t6 where t6.f2= 'xyz';
82
--source suite/funcs_1/include/show_connection.inc
84
DROP USER 'user_1'@'localhost';
88
# ------------------------------------------------------------------------------
89
let $message= Testcase 3.1.6.2:
91
Ensure that root always has the GRANT CREATE ROUTINE privilege.
92
(checked by other testscases);
93
--source include/show_msg80.inc
96
# ------------------------------------------------------------------------------
97
let $message= Testcase 3.1.6.3:
99
Ensure that a user with the GRANT CREATE ROUTINE privilege can always create
100
both a procedure and a function, on any appropriate database.
101
--source include/show_msg80.inc
104
create user 'user_1'@'localhost';
106
grant create routine on db_storedproc_1.* to 'user_1'@'localhost';
109
# disconnect default;
110
--replace_result $MASTER_MYPORT MYSQL_PORT $MASTER_MYSOCK MYSQL_SOCK
111
connect (user2, localhost, user_1, , db_storedproc_1);
112
--source suite/funcs_1/include/show_connection.inc
115
DROP PROCEDURE IF EXISTS sp3;
116
DROP FUNCTION IF EXISTS fn1;
120
CREATE PROCEDURE sp3(v1 char(20))
122
SELECT * from db_storedproc_1.t6 where t6.f2= 'xyz';
127
CREATE FUNCTION fn1(v1 int) returns int
138
--source suite/funcs_1/include/show_connection.inc
140
drop user 'user_1'@'localhost';
145
# ------------------------------------------------------------------------------
146
let $message= Testcase 3.1.6.4:
148
Ensure that the default security provision of a stored procedure is SQL SECURITY
150
--source include/show_msg80.inc
152
CREATE USER 'user_1'@'localhost';
154
grant update on db_storedproc_1.t6 to 'user_1'@'localhost';
155
grant execute on db_storedproc_1.* to 'user_1'@'localhost';
161
DROP PROCEDURE IF EXISTS sp4;
165
CREATE PROCEDURE sp4(v1 char(20))
167
SELECT * from db_storedproc_1.t6 where t6.f2= 'xyz';
173
--replace_result $MASTER_MYPORT MYSQL_PORT $MASTER_MYSOCK MYSQL_SOCK
174
connect (user3, localhost, user_1, , db_storedproc_1);
175
--source suite/funcs_1/include/show_connection.inc
181
SELECT SPECIFIC_NAME, ROUTINE_SCHEMA, ROUTINE_NAME, ROUTINE_TYPE,
182
ROUTINE_BODY, ROUTINE_DEFINITION, IS_DETERMINISTIC,
183
SQL_DATA_ACCESS, SECURITY_TYPE, SQL_MODE, ROUTINE_COMMENT
184
FROM information_schema.routines
185
WHERE routine_schema LIKE 'db_sto%';
192
--source suite/funcs_1/include/show_connection.inc
194
DROP USER 'user_1'@'localhost';
197
# ------------------------------------------------------------------------------
198
let $message= Testcase 3.1.6.5:
200
Ensure that a stored procedure defined with SQL SECURITY DEFINER can be
201
called/executed by any user, using only the privileges (including database
202
access privileges) associated with the user who created the stored procedure.;
203
--source include/show_msg80.inc
206
CREATE TABLE t3165 ( c1 char(20), c2 char(20), c3 date);
207
INSERT INTO t3165 VALUES ('inserted', 'outside of SP', NULL);
210
create user 'user_1'@'localhost';
213
create user 'user_2'@'localhost';
215
grant create routine on db_storedproc_1.* to 'user_1'@'localhost';
216
grant SELECT on db_storedproc_1.* to 'user_2'@'localhost';
217
grant execute on db_storedproc_1.* to 'user_2'@'localhost';
220
--replace_result $MASTER_MYPORT MYSQL_PORT $MASTER_MYSOCK MYSQL_SOCK
221
connect (user5_1, localhost, user_1, , db_storedproc_1);
222
--source suite/funcs_1/include/show_connection.inc
225
CREATE PROCEDURE sp5_s_i () sql security definer
227
SELECT * from db_storedproc_1.t3165;
228
insert into db_storedproc_1.t3165 values ('inserted', 'from sp5_s_i', 1000);
231
CREATE PROCEDURE sp5_sel () sql security definer
233
SELECT * from db_storedproc_1.t3165;
236
CREATE PROCEDURE sp5_ins () sql security definer
238
insert into db_storedproc_1.t3165 values ('inserted', 'from sp5_ins', 1000);
244
--replace_result $MASTER_MYPORT MYSQL_PORT $MASTER_MYSOCK MYSQL_SOCK
245
connect (user5_2, localhost, user_2, , db_storedproc_1);
246
--source suite/funcs_1/include/show_connection.inc
255
# now 'add' INSERT to DEFINER
257
--source suite/funcs_1/include/show_connection.inc
260
grant insert on db_storedproc_1.* to 'user_1'@'localhost';
264
--source suite/funcs_1/include/show_connection.inc
271
# now 'add' SELECT to DEFINER
273
--source suite/funcs_1/include/show_connection.inc
276
grant SELECT on db_storedproc_1.* to 'user_1'@'localhost';
277
#grant execute on db_storedproc_1.* to 'user_2'@'localhost';
281
--source suite/funcs_1/include/show_connection.inc
286
# now revoke INSERT FROM DEFINER
288
--source suite/funcs_1/include/show_connection.inc
289
REVOKE INSERT on db_storedproc_1.* from 'user_1'@'localhost';
293
--source suite/funcs_1/include/show_connection.inc
300
# now revoke SELECT FROM DEFINER
302
--source suite/funcs_1/include/show_connection.inc
303
REVOKE SELECT on db_storedproc_1.* from 'user_1'@'localhost';
307
--source suite/funcs_1/include/show_connection.inc
318
--source suite/funcs_1/include/show_connection.inc
320
DROP PROCEDURE sp5_s_i;
321
DROP PROCEDURE sp5_sel;
322
DROP PROCEDURE sp5_ins;
324
DROP USER 'user_1'@'localhost';
325
DROP USER 'user_2'@'localhost';
328
# ------------------------------------------------------------------------------
329
let $message= Testcase 3.1.6.6:
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
--source include/show_msg80.inc
337
CREATE TABLE t3166 ( c1 char(30) );
338
INSERT INTO db_storedproc_1.t3166 VALUES ('inserted outside SP');
341
create user 'user_1'@'localhost';
344
create user 'user_2'@'localhost';
346
GRANT CREATE ROUTINE ON db_storedproc_1.* TO 'user_1'@'localhost';
347
GRANT SELECT ON db_storedproc_1.* TO 'user_2'@'localhost';
348
GRANT EXECUTE ON db_storedproc_1.* TO 'user_2'@'localhost';
351
--replace_result $MASTER_MYPORT MYSQL_PORT $MASTER_MYSOCK MYSQL_SOCK
352
connect (user6_1, localhost, user_1, , db_storedproc_1);
353
--source suite/funcs_1/include/show_connection.inc
356
CREATE PROCEDURE sp3166_s_i () SQL SECURITY INVOKER
358
SELECT * from db_storedproc_1.t3166;
359
insert into db_storedproc_1.t3166 values ('inserted from sp3166_s_i');
362
CREATE PROCEDURE sp3166_sel () SQL SECURITY INVOKER
364
SELECT * from db_storedproc_1.t3166;
367
CREATE PROCEDURE sp3166_ins () SQL SECURITY INVOKER
369
insert into db_storedproc_1.t3166 values ('inserted from sp3166_ins');
375
--replace_result $MASTER_MYPORT MYSQL_PORT $MASTER_MYSOCK MYSQL_SOCK
376
connect (user6_2, localhost, user_2, , db_storedproc_1);
377
--source suite/funcs_1/include/show_connection.inc
385
# now 'add' INSERT to INVOKER
387
--source suite/funcs_1/include/show_connection.inc
389
GRANT INSERT ON db_storedproc_1.* TO 'user_2'@'localhost';
393
--replace_result $MASTER_MYPORT MYSQL_PORT $MASTER_MYSOCK MYSQL_SOCK
394
connect (user6_3, localhost, user_2, , db_storedproc_1);
395
--source suite/funcs_1/include/show_connection.inc
401
# now 'remove' SELECT from INVOKER
403
--source suite/funcs_1/include/show_connection.inc
405
REVOKE SELECT ON db_storedproc_1.* FROM 'user_2'@'localhost';
408
--replace_result $MASTER_MYPORT MYSQL_PORT $MASTER_MYSOCK MYSQL_SOCK
409
connect (user6_4, localhost, user_2, , db_storedproc_1);
410
--source suite/funcs_1/include/show_connection.inc
418
# now 'remove' EXECUTE FROM INVOKER
421
--source suite/funcs_1/include/show_connection.inc
422
REVOKE EXECUTE on db_storedproc_1.* FROM 'user_2'@'localhost';
425
--replace_result $MASTER_MYPORT MYSQL_PORT $MASTER_MYSOCK MYSQL_SOCK
426
connect (user6_5, localhost, user_2, , db_storedproc_1);
427
--source suite/funcs_1/include/show_connection.inc
438
--source suite/funcs_1/include/show_connection.inc
440
DROP PROCEDURE sp3166_s_i;
441
DROP PROCEDURE sp3166_sel;
442
DROP PROCEDURE sp3166_ins;
444
DROP USER 'user_1'@'localhost';
445
DROP USER 'user_2'@'localhost';
448
# ==============================================================================
449
# USE the same .inc to cleanup before and after the test
450
--source suite/funcs_1/storedproc/cleanup_sp_tb.inc
452
# ==============================================================================
453
let $message= . +++ END OF SCRIPT +++;
454
--source include/show_msg80.inc
455
# ==============================================================================