SET @@session.sql_mode = 'NO_ENGINE_SUBSTITUTION'; set GLOBAL sql_mode=""; --source suite/funcs_1/storedproc/load_sp_tb.inc -------------------------------------------------------------------------------- --source suite/funcs_1/storedproc/cleanup_sp_tb.inc -------------------------------------------------------------------------------- DROP DATABASE IF EXISTS db_storedproc; DROP DATABASE IF EXISTS db_storedproc_1; CREATE DATABASE db_storedproc; CREATE DATABASE db_storedproc_1; USE db_storedproc; create table t1(f1 char(20),f2 char(25),f3 date,f4 int,f5 char(25),f6 int) engine = ; load data infile '/std_data/funcs_1/t4.txt' into table t1; create table t2(f1 char(20),f2 char(25),f3 date,f4 int,f5 char(25),f6 int) engine = ; load data infile '/std_data/funcs_1/t4.txt' into table t2; create table t3(f1 char(20),f2 char(20),f3 integer) engine = ; load data infile '/std_data/funcs_1/t3.txt' into table t3; create table t4(f1 char(20),f2 char(25),f3 date,f4 int,f5 char(25),f6 int) engine = ; load data infile '/std_data/funcs_1/t4.txt' into table t4; USE db_storedproc_1; create table t6(f1 char(20),f2 char(25),f3 date,f4 int,f5 char(25),f6 int) engine = ; load data infile '/std_data/funcs_1/t4.txt' into table t6; USE db_storedproc; create table t7 (f1 char(20), f2 char(25), f3 date, f4 int) engine = ; load data infile '/std_data/funcs_1/t7.txt' ignore into table t7; Warnings: Warning 1265 Data truncated for column 'f3' at row 1 Warning 1265 Data truncated for column 'f3' at row 2 Warning 1265 Data truncated for column 'f3' at row 3 Warning 1265 Data truncated for column 'f3' at row 4 Warning 1265 Data truncated for column 'f3' at row 5 Warning 1265 Data truncated for column 'f3' at row 6 Warning 1265 Data truncated for column 'f3' at row 7 Warning 1265 Data truncated for column 'f3' at row 8 Warning 1265 Data truncated for column 'f3' at row 9 Warning 1265 Data truncated for column 'f3' at row 10 create table t8 (f1 char(20), f2 char(25), f3 date, f4 int) engine = ; load data infile '/std_data/funcs_1/t7.txt' ignore into table t8; Warnings: Warning 1265 Data truncated for column 'f3' at row 1 Warning 1265 Data truncated for column 'f3' at row 2 Warning 1265 Data truncated for column 'f3' at row 3 Warning 1265 Data truncated for column 'f3' at row 4 Warning 1265 Data truncated for column 'f3' at row 5 Warning 1265 Data truncated for column 'f3' at row 6 Warning 1265 Data truncated for column 'f3' at row 7 Warning 1265 Data truncated for column 'f3' at row 8 Warning 1265 Data truncated for column 'f3' at row 9 Warning 1265 Data truncated for column 'f3' at row 10 create table t9(f1 int, f2 char(25), f3 int) engine = ; load data infile '/std_data/funcs_1/t9.txt' into table t9; create table t10(f1 char(20),f2 char(25),f3 date,f4 int,f5 char(25),f6 int) engine = ; load data infile '/std_data/funcs_1/t4.txt' into table t10; create table t11(f1 char(20),f2 char(25),f3 date,f4 int,f5 char(25),f6 int) engine = ; load data infile '/std_data/funcs_1/t4.txt' into table t11; Section 3.1.6 - Privilege Checks: -------------------------------------------------------------------------------- connection default; USE db_storedproc_1; root@localhost db_storedproc_1 Testcase 3.1.6.1: ----------------- Ensure that no user may create a stored procedure without the GRANT CREATE ROUTINE privilege. -------------------------------------------------------------------------------- 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; DROP PROCEDURE IF EXISTS sp1; connect user1a, localhost, user_1, , db_storedproc_1; user_1@localhost db_storedproc_1 USE db_storedproc_1; CREATE PROCEDURE sp1(v1 char(20)) BEGIN SELECT * from db_storedproc_1.t6 where t6.f2= 'xyz'; END// ERROR 42000: Access denied for user 'user_1'@'localhost' to database 'db_storedproc_1' disconnect user1a; connection default; USE db_storedproc_1; root@localhost db_storedproc_1 GRANT CREATE ROUTINE ON db_storedproc_1.* TO 'user_1'@'localhost'; connect user1b, localhost, user_1, , db_storedproc_1; user_1@localhost db_storedproc_1 USE db_storedproc_1; CREATE PROCEDURE sp1(v1 char(20)) BEGIN SELECT * from db_storedproc_1.t6 where t6.f2= 'xyz'; END// disconnect user1b; connection default; USE db_storedproc_1; root@localhost db_storedproc_1 DROP USER 'user_1'@'localhost'; DROP PROCEDURE sp1; Testcase 3.1.6.2: ----------------- Ensure that root always has the GRANT CREATE ROUTINE privilege. (checked by other testscases) -------------------------------------------------------------------------------- grant create routine on db_storedproc_1.* to 'user_1'@'localhost'; flush privileges; DROP PROCEDURE IF EXISTS db_storedproc_1.sp3; DROP FUNCTION IF EXISTS db_storedproc_1.fn1; connect user2, localhost, user_1, , db_storedproc_1; user_1@localhost db_storedproc_1 CREATE PROCEDURE sp3(v1 char(20)) BEGIN SELECT * from db_storedproc_1.t6 where t6.f2= 'xyz'; END// CREATE FUNCTION fn1(v1 int) returns int BEGIN return v1; END// disconnect user2; connection default; USE db_storedproc_1; root@localhost db_storedproc_1 drop user 'user_1'@'localhost'; DROP PROCEDURE sp3; DROP FUNCTION fn1; Testcase 3.1.6.4: ----------------- Ensure that the default security provision of a stored procedure is SQL SECURITY DEFINER. -------------------------------------------------------------------------------- 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; DROP PROCEDURE IF EXISTS sp4; CREATE PROCEDURE sp4(v1 char(20)) BEGIN SELECT * from db_storedproc_1.t6 where t6.f2= 'xyz'; END// connect user3, localhost, user_1, , db_storedproc_1; user_1@localhost db_storedproc_1 USE db_storedproc_1; CALL sp4('a'); f1 f2 f3 f4 f5 f6 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%'; SPECIFIC_NAME sp4 ROUTINE_SCHEMA db_storedproc_1 ROUTINE_NAME sp4 ROUTINE_TYPE PROCEDURE ROUTINE_BODY SQL ROUTINE_DEFINITION NULL IS_DETERMINISTIC NO SQL_DATA_ACCESS CONTAINS SQL SECURITY_TYPE DEFINER SQL_MODE NO_ENGINE_SUBSTITUTION ROUTINE_COMMENT disconnect user3; connection default; root@localhost db_storedproc_1 DROP PROCEDURE sp4; DROP USER 'user_1'@'localhost'; 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. -------------------------------------------------------------------------------- USE db_storedproc_1; CREATE TABLE t3165 ( c1 char(20), c2 char(20), c3 date); INSERT INTO t3165 VALUES ('inserted', 'outside of SP', NULL); create user 'user_1'@'localhost'; 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; connect user5_1, localhost, user_1, , db_storedproc_1; user_1@localhost db_storedproc_1 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// disconnect user5_1; connect user5_2, localhost, user_2, , db_storedproc_1; user_2@localhost db_storedproc_1 CALL sp5_s_i(); ERROR 42000: SELECT command denied to user 'user_1'@'localhost' for table `db_storedproc_1`.`t3165` CALL sp5_ins(); ERROR 42000: INSERT command denied to user 'user_1'@'localhost' for table `db_storedproc_1`.`t3165` CALL sp5_sel(); ERROR 42000: SELECT command denied to user 'user_1'@'localhost' for table `db_storedproc_1`.`t3165` connection default; root@localhost db_storedproc_1 CALL sp5_sel(); ERROR 42000: SELECT command denied to user 'user_1'@'localhost' for table `db_storedproc_1`.`t3165` grant insert on db_storedproc_1.* to 'user_1'@'localhost'; flush privileges; connection user5_2; user_2@localhost db_storedproc_1 CALL sp5_s_i(); ERROR 42000: SELECT command denied to user 'user_1'@'localhost' for table `db_storedproc_1`.`t3165` CALL sp5_ins(); CALL sp5_sel(); ERROR 42000: SELECT command denied to user 'user_1'@'localhost' for table `db_storedproc_1`.`t3165` connection default; root@localhost db_storedproc_1 CALL sp5_sel(); ERROR 42000: SELECT command denied to user 'user_1'@'localhost' for table `db_storedproc_1`.`t3165` grant SELECT on db_storedproc_1.* to 'user_1'@'localhost'; flush privileges; connection user5_2; user_2@localhost db_storedproc_1 CALL sp5_s_i(); c1 c2 c3 inserted outside of SP NULL inserted from sp5_ins 2000-10-00 CALL sp5_ins(); CALL sp5_sel(); c1 c2 c3 inserted outside of SP NULL inserted from sp5_ins 2000-10-00 inserted from sp5_s_i 2000-10-00 inserted from sp5_ins 2000-10-00 connection default; root@localhost db_storedproc_1 REVOKE INSERT on db_storedproc_1.* from 'user_1'@'localhost'; flush privileges; connection user5_2; user_2@localhost db_storedproc_1 CALL sp5_s_i(); c1 c2 c3 inserted outside of SP NULL inserted from sp5_ins 2000-10-00 inserted from sp5_s_i 2000-10-00 inserted from sp5_ins 2000-10-00 ERROR 42000: INSERT command denied to user 'user_1'@'localhost' for table `db_storedproc_1`.`t3165` CALL sp5_ins(); ERROR 42000: INSERT command denied to user 'user_1'@'localhost' for table `db_storedproc_1`.`t3165` CALL sp5_sel(); c1 c2 c3 inserted outside of SP NULL inserted from sp5_ins 2000-10-00 inserted from sp5_s_i 2000-10-00 inserted from sp5_ins 2000-10-00 connection default; root@localhost db_storedproc_1 REVOKE SELECT on db_storedproc_1.* from 'user_1'@'localhost'; flush privileges; connection user5_2; user_2@localhost db_storedproc_1 CALL sp5_s_i(); ERROR 42000: SELECT command denied to user 'user_1'@'localhost' for table `db_storedproc_1`.`t3165` CALL sp5_ins(); ERROR 42000: INSERT command denied to user 'user_1'@'localhost' for table `db_storedproc_1`.`t3165` CALL sp5_sel(); ERROR 42000: SELECT command denied to user 'user_1'@'localhost' for table `db_storedproc_1`.`t3165` disconnect user5_2; connection default; root@localhost db_storedproc_1 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'; 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. -------------------------------------------------------------------------------- USE db_storedproc_1; CREATE TABLE t3166 ( c1 char(30) ); INSERT INTO db_storedproc_1.t3166 VALUES ('inserted outside SP'); create user 'user_1'@'localhost'; 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; connect user6_1, localhost, user_1, , db_storedproc_1; user_1@localhost db_storedproc_1 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// disconnect user6_1; connect user6_2, localhost, user_2, , db_storedproc_1; user_2@localhost db_storedproc_1 CALL sp3166_s_i(); c1 inserted outside SP ERROR 42000: INSERT command denied to user 'user_2'@'localhost' for table `db_storedproc_1`.`t3166` CALL sp3166_ins(); ERROR 42000: INSERT command denied to user 'user_2'@'localhost' for table `db_storedproc_1`.`t3166` CALL sp3166_sel(); c1 inserted outside SP connection default; root@localhost db_storedproc_1 CALL sp3166_sel(); c1 inserted outside SP GRANT INSERT ON db_storedproc_1.* TO 'user_2'@'localhost'; FLUSH PRIVILEGES; disconnect user6_2; connect user6_3, localhost, user_2, , db_storedproc_1; user_2@localhost db_storedproc_1 CALL sp3166_s_i(); c1 inserted outside SP CALL sp3166_ins(); CALL sp3166_sel(); c1 inserted outside SP inserted from sp3166_s_i inserted from sp3166_ins disconnect user6_3; connection default; root@localhost db_storedproc_1 CALL sp3166_sel(); c1 inserted outside SP inserted from sp3166_s_i inserted from sp3166_ins REVOKE SELECT ON db_storedproc_1.* FROM 'user_2'@'localhost'; FLUSH PRIVILEGES; connect user6_4, localhost, user_2, , db_storedproc_1; user_2@localhost db_storedproc_1 CALL sp3166_s_i(); ERROR 42000: SELECT command denied to user 'user_2'@'localhost' for table `db_storedproc_1`.`t3166` CALL sp3166_ins(); CALL sp3166_sel(); ERROR 42000: SELECT command denied to user 'user_2'@'localhost' for table `db_storedproc_1`.`t3166` disconnect user6_4; connection default; CALL sp3166_s_i(); c1 inserted outside SP inserted from sp3166_s_i inserted from sp3166_ins inserted from sp3166_ins root@localhost db_storedproc_1 REVOKE EXECUTE on db_storedproc_1.* FROM 'user_2'@'localhost'; FLUSH PRIVILEGES; connect user6_5, localhost, user_2, , db_storedproc_1; user_2@localhost db_storedproc_1 CALL sp3166_s_i(); ERROR 42000: execute command denied to user 'user_2'@'localhost' for routine 'db_storedproc_1.sp3166_s_i' CALL sp3166_ins(); ERROR 42000: execute command denied to user 'user_2'@'localhost' for routine 'db_storedproc_1.sp3166_ins' CALL sp3166_sel(); ERROR 42000: execute command denied to user 'user_2'@'localhost' for routine 'db_storedproc_1.sp3166_sel' disconnect user6_5; connection default; root@localhost db_storedproc_1 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'; --source suite/funcs_1/storedproc/cleanup_sp_tb.inc -------------------------------------------------------------------------------- DROP DATABASE IF EXISTS db_storedproc; DROP DATABASE IF EXISTS db_storedproc_1; set GLOBAL sql_mode=default; . +++ END OF SCRIPT +++ --------------------------------------------------------------------------------