diff options
Diffstat (limited to 'mysql-test/suite/funcs_1/storedproc/storedproc_06.inc')
| -rw-r--r-- | mysql-test/suite/funcs_1/storedproc/storedproc_06.inc | 452 |
1 files changed, 452 insertions, 0 deletions
diff --git a/mysql-test/suite/funcs_1/storedproc/storedproc_06.inc b/mysql-test/suite/funcs_1/storedproc/storedproc_06.inc new file mode 100644 index 00000000000..e2b9e846b97 --- /dev/null +++ b/mysql-test/suite/funcs_1/storedproc/storedproc_06.inc @@ -0,0 +1,452 @@ +#### suite/funcs_1/storedproc/storedproc_06.inc +# +--source suite/funcs_1/storedproc/load_sp_tb.inc + +# ============================================================================== +# (numbering from requirement document TP v1.0, Last updated: 25 Jan 2005 01:00) +# +# 3.1.6 Privilege checks: +# +# 1. Ensure that no user may create a stored procedure without the GRANT CREATE ROUTINE privilege. +# 2. Ensure that root always has the GRANT CREATE ROUTINE privilege. +# 3. Ensure that a user with the GRANT CREATE ROUTINE privilege can always create both a procedure and a function, on any appropriate database. +# 4. Ensure that the default security provision of a stored procedure is SQL SECURITY DEFINER. +# 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. +# 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. +# +# ============================================================================== +let $message= Section 3.1.6 - Privilege Checks:; +--source include/show_msg80.inc + +USE db_storedproc_1; + +connection default; +--source suite/funcs_1/include/show_connection.inc + +# ------------------------------------------------------------------------------ +let $message= Testcase 3.1.6.1: + ----------------- +Ensure that no user may create a stored procedure without the GRANT CREATE +ROUTINE privilege.; +--source include/show_msg80.inc + +create user 'user_1'@'localhost'; + +grant all on db_storedproc_1.* to 'user_1'@'localhost'; +revoke create routine on db_storedproc_1.* from 'user_1'@'localhost'; +flush privileges; + +--disable_warnings +DROP PROCEDURE IF EXISTS sp1; +--enable_warnings + +--replace_result $MASTER_MYPORT MYSQL_PORT $MASTER_MYSOCK MYSQL_SOCK +connect (user1a, localhost, user_1, , db_storedproc_1); +--source suite/funcs_1/include/show_connection.inc + +USE db_storedproc_1; + +delimiter //; +--error 1044 +CREATE PROCEDURE sp1(v1 char(20)) +BEGIN + SELECT * from db_storedproc_1.t6 where t6.f2= 'xyz'; +END// +delimiter ;// + +disconnect user1a; + +# add privilege again and check +connection default; +--source suite/funcs_1/include/show_connection.inc + +GRANT CREATE ROUTINE ON db_storedproc_1.* TO 'user_1'@'localhost'; +--replace_result $MASTER_MYPORT MYSQL_PORT $MASTER_MYSOCK MYSQL_SOCK +connect (user1b, localhost, user_1, , db_storedproc_1); +--source suite/funcs_1/include/show_connection.inc + +USE db_storedproc_1; + +delimiter //; +CREATE PROCEDURE sp1(v1 char(20)) +BEGIN + SELECT * from db_storedproc_1.t6 where t6.f2= 'xyz'; +END// +delimiter ;// +disconnect user1b; + +# cleanup +connection default; +--source suite/funcs_1/include/show_connection.inc + +DROP USER 'user_1'@'localhost'; +DROP PROCEDURE sp1; + + +# ------------------------------------------------------------------------------ +let $message= Testcase 3.1.6.2: + ----------------- +Ensure that root always has the GRANT CREATE ROUTINE privilege. +(checked by other testscases); +--source include/show_msg80.inc + + +# ------------------------------------------------------------------------------ +let $message= Testcase 3.1.6.3: + ----------------- +Ensure that a user with the GRANT CREATE ROUTINE privilege can always create +both a procedure and a function, on any appropriate database. +--source include/show_msg80.inc + + +create user 'user_1'@'localhost'; + +grant create routine on db_storedproc_1.* to 'user_1'@'localhost'; +flush privileges; + +# disconnect default; +--replace_result $MASTER_MYPORT MYSQL_PORT $MASTER_MYSOCK MYSQL_SOCK +connect (user2, localhost, user_1, , db_storedproc_1); +--source suite/funcs_1/include/show_connection.inc + +--disable_warnings +DROP PROCEDURE IF EXISTS sp3; +DROP FUNCTION IF EXISTS fn1; +--enable_warnings + +delimiter //; +CREATE PROCEDURE sp3(v1 char(20)) +BEGIN + SELECT * from db_storedproc_1.t6 where t6.f2= 'xyz'; +END// +delimiter ;// + +delimiter //; +CREATE FUNCTION fn1(v1 int) returns int +BEGIN + return v1; +END// +delimiter ;// + +disconnect user2; + +# cleanup +connection default; +--source suite/funcs_1/include/show_connection.inc + +drop user 'user_1'@'localhost'; +DROP PROCEDURE sp3; +DROP FUNCTION fn1; + + +# ------------------------------------------------------------------------------ +let $message= Testcase 3.1.6.4: + ----------------- +Ensure that the default security provision of a stored procedure is SQL SECURITY +DEFINER.; +--source include/show_msg80.inc + +CREATE USER 'user_1'@'localhost'; + +grant update on db_storedproc_1.t6 to 'user_1'@'localhost'; +grant execute on db_storedproc_1.* to 'user_1'@'localhost'; +flush privileges; + +USE db_storedproc_1; + +--disable_warnings +DROP PROCEDURE IF EXISTS sp4; +--enable_warnings + +delimiter //; +CREATE PROCEDURE sp4(v1 char(20)) +BEGIN + SELECT * from db_storedproc_1.t6 where t6.f2= 'xyz'; +END// +delimiter ;// + +#disconnect default; + +--replace_result $MASTER_MYPORT MYSQL_PORT $MASTER_MYSOCK MYSQL_SOCK +connect (user3, localhost, user_1, , db_storedproc_1); +--source suite/funcs_1/include/show_connection.inc + +USE db_storedproc_1; +CALL sp4('a'); + +--vertical_results +SELECT SPECIFIC_NAME, ROUTINE_SCHEMA, ROUTINE_NAME, ROUTINE_TYPE, + ROUTINE_BODY, ROUTINE_DEFINITION, IS_DETERMINISTIC, + SQL_DATA_ACCESS, SECURITY_TYPE, SQL_MODE, ROUTINE_COMMENT +FROM information_schema.routines + WHERE routine_schema LIKE 'db_sto%'; +--horizontal_results + +disconnect user3; + +# cleanup +connection default; +--source suite/funcs_1/include/show_connection.inc +DROP PROCEDURE sp4; +DROP USER 'user_1'@'localhost'; + + +# ------------------------------------------------------------------------------ +let $message= Testcase 3.1.6.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.; +--source include/show_msg80.inc + +USE db_storedproc_1; +CREATE TABLE t3165 ( c1 char(20), c2 char(20), c3 date); +INSERT INTO t3165 VALUES ('inserted', 'outside of SP', NULL); + +# creates procedures +create user 'user_1'@'localhost'; + +#executes procedure +create user 'user_2'@'localhost'; + +grant create routine on db_storedproc_1.* to 'user_1'@'localhost'; +grant SELECT on db_storedproc_1.* to 'user_2'@'localhost'; +grant execute on db_storedproc_1.* to 'user_2'@'localhost'; +flush privileges; + +--replace_result $MASTER_MYPORT MYSQL_PORT $MASTER_MYSOCK MYSQL_SOCK +connect (user5_1, localhost, user_1, , db_storedproc_1); +--source suite/funcs_1/include/show_connection.inc + +delimiter //; +CREATE PROCEDURE sp5_s_i () sql security definer +BEGIN + SELECT * from db_storedproc_1.t3165; + insert into db_storedproc_1.t3165 values ('inserted', 'from sp5_s_i', 1000); +END// + +CREATE PROCEDURE sp5_sel () sql security definer +BEGIN + SELECT * from db_storedproc_1.t3165; +END// + +CREATE PROCEDURE sp5_ins () sql security definer +BEGIN + insert into db_storedproc_1.t3165 values ('inserted', 'from sp5_ins', 1000); +END// +delimiter ;// + +disconnect user5_1; + +--replace_result $MASTER_MYPORT MYSQL_PORT $MASTER_MYSOCK MYSQL_SOCK +connect (user5_2, localhost, user_2, , db_storedproc_1); +--source suite/funcs_1/include/show_connection.inc + +--error 1142 +CALL sp5_s_i(); +--error 1142 +CALL sp5_ins(); +--error 1142 +CALL sp5_sel(); + +# now 'add' INSERT to DEFINER +connection default; +--source suite/funcs_1/include/show_connection.inc +--error 1142 +CALL sp5_sel(); +grant insert on db_storedproc_1.* to 'user_1'@'localhost'; +flush privileges; + +connection user5_2; +--source suite/funcs_1/include/show_connection.inc +--error 1142 +CALL sp5_s_i(); +CALL sp5_ins(); +--error 1142 +CALL sp5_sel(); + +# now 'add' SELECT to DEFINER +connection default; +--source suite/funcs_1/include/show_connection.inc +--error 1142 +CALL sp5_sel(); +grant SELECT on db_storedproc_1.* to 'user_1'@'localhost'; +#grant execute on db_storedproc_1.* to 'user_2'@'localhost'; +flush privileges; + +connection user5_2; +--source suite/funcs_1/include/show_connection.inc +CALL sp5_s_i(); +CALL sp5_ins(); +CALL sp5_sel(); + +# now revoke INSERT FROM DEFINER +connection default; +--source suite/funcs_1/include/show_connection.inc +REVOKE INSERT on db_storedproc_1.* from 'user_1'@'localhost'; +flush privileges; + +connection user5_2; +--source suite/funcs_1/include/show_connection.inc +--error 1142 +CALL sp5_s_i(); +--error 1142 +CALL sp5_ins(); +CALL sp5_sel(); + +# now revoke SELECT FROM DEFINER +connection default; +--source suite/funcs_1/include/show_connection.inc +REVOKE SELECT on db_storedproc_1.* from 'user_1'@'localhost'; +flush privileges; + +connection user5_2; +--source suite/funcs_1/include/show_connection.inc +--error 1142 +CALL sp5_s_i(); +--error 1142 +CALL sp5_ins(); +--error 1142 +CALL sp5_sel(); + +# cleanup +disconnect user5_2; +connection default; +--source suite/funcs_1/include/show_connection.inc + +DROP PROCEDURE sp5_s_i; +DROP PROCEDURE sp5_sel; +DROP PROCEDURE sp5_ins; +DROP TABLE t3165; +DROP USER 'user_1'@'localhost'; +DROP USER 'user_2'@'localhost'; + + +# ------------------------------------------------------------------------------ +let $message= Testcase 3.1.6.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.; +--source include/show_msg80.inc + +USE db_storedproc_1; +CREATE TABLE t3166 ( c1 char(30) ); +INSERT INTO db_storedproc_1.t3166 VALUES ('inserted outside SP'); + +# DEFINER +create user 'user_1'@'localhost'; + +# INVOKER +create user 'user_2'@'localhost'; + +GRANT CREATE ROUTINE ON db_storedproc_1.* TO 'user_1'@'localhost'; +GRANT SELECT ON db_storedproc_1.* TO 'user_2'@'localhost'; +GRANT EXECUTE ON db_storedproc_1.* TO 'user_2'@'localhost'; +FLUSH PRIVILEGES; + +--replace_result $MASTER_MYPORT MYSQL_PORT $MASTER_MYSOCK MYSQL_SOCK +connect (user6_1, localhost, user_1, , db_storedproc_1); +--source suite/funcs_1/include/show_connection.inc + +delimiter //; +CREATE PROCEDURE sp3166_s_i () SQL SECURITY INVOKER +BEGIN + SELECT * from db_storedproc_1.t3166; + insert into db_storedproc_1.t3166 values ('inserted from sp3166_s_i'); +END// + +CREATE PROCEDURE sp3166_sel () SQL SECURITY INVOKER +BEGIN + SELECT * from db_storedproc_1.t3166; +END// + +CREATE PROCEDURE sp3166_ins () SQL SECURITY INVOKER +BEGIN + insert into db_storedproc_1.t3166 values ('inserted from sp3166_ins'); +END// +delimiter ;// + +disconnect user6_1; + +--replace_result $MASTER_MYPORT MYSQL_PORT $MASTER_MYSOCK MYSQL_SOCK +connect (user6_2, localhost, user_2, , db_storedproc_1); +--source suite/funcs_1/include/show_connection.inc + +--error 1142 +CALL sp3166_s_i(); +--error 1142 +CALL sp3166_ins(); +CALL sp3166_sel(); + +# now 'add' INSERT to INVOKER +connection default; +--source suite/funcs_1/include/show_connection.inc +CALL sp3166_sel(); +GRANT INSERT ON db_storedproc_1.* TO 'user_2'@'localhost'; +FLUSH PRIVILEGES; +disconnect user6_2; + +--replace_result $MASTER_MYPORT MYSQL_PORT $MASTER_MYSOCK MYSQL_SOCK +connect (user6_3, localhost, user_2, , db_storedproc_1); +--source suite/funcs_1/include/show_connection.inc +CALL sp3166_s_i(); +CALL sp3166_ins(); +CALL sp3166_sel(); +disconnect user6_3; + +# now 'remove' SELECT from INVOKER +connection default; +--source suite/funcs_1/include/show_connection.inc +CALL sp3166_sel(); +REVOKE SELECT ON db_storedproc_1.* FROM 'user_2'@'localhost'; +FLUSH PRIVILEGES; + +--replace_result $MASTER_MYPORT MYSQL_PORT $MASTER_MYSOCK MYSQL_SOCK +connect (user6_4, localhost, user_2, , db_storedproc_1); +--source suite/funcs_1/include/show_connection.inc +--error 1142 +CALL sp3166_s_i(); +CALL sp3166_ins(); +--error 1142 +CALL sp3166_sel(); +disconnect user6_4; + +# now 'remove' EXECUTE FROM INVOKER +connection default; +CALL sp3166_s_i(); +--source suite/funcs_1/include/show_connection.inc +REVOKE EXECUTE on db_storedproc_1.* FROM 'user_2'@'localhost'; +FLUSH PRIVILEGES; + +--replace_result $MASTER_MYPORT MYSQL_PORT $MASTER_MYSOCK MYSQL_SOCK +connect (user6_5, localhost, user_2, , db_storedproc_1); +--source suite/funcs_1/include/show_connection.inc +--error 1370 +CALL sp3166_s_i(); +--error 1370 +CALL sp3166_ins(); +--error 1370 +CALL sp3166_sel(); +disconnect user6_5; + +# cleanup +connection default; +--source suite/funcs_1/include/show_connection.inc + +DROP PROCEDURE sp3166_s_i; +DROP PROCEDURE sp3166_sel; +DROP PROCEDURE sp3166_ins; +DROP TABLE t3166; +DROP USER 'user_1'@'localhost'; +DROP USER 'user_2'@'localhost'; + + +# ============================================================================== +# USE the same .inc to cleanup before and after the test +--source suite/funcs_1/storedproc/cleanup_sp_tb.inc + +# ============================================================================== +let $message= . +++ END OF SCRIPT +++; +--source include/show_msg80.inc +# ============================================================================== |