Edit File: innodb_storedproc_06.result
--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 = <engine_to_be_tested>; load data infile '<MYSQLTEST_VARDIR>/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 = <engine_to_be_tested>; load data infile '<MYSQLTEST_VARDIR>/std_data/funcs_1/t4.txt' into table t2; create table t3(f1 char(20),f2 char(20),f3 integer) engine = <engine_to_be_tested>; load data infile '<MYSQLTEST_VARDIR>/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 = <engine_to_be_tested>; load data infile '<MYSQLTEST_VARDIR>/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 = <engine_to_be_tested>; load data infile '<MYSQLTEST_VARDIR>/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 = <engine_to_be_tested>; load data infile '<MYSQLTEST_VARDIR>/std_data/funcs_1/t7.txt' 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 = <engine_to_be_tested>; load data infile '<MYSQLTEST_VARDIR>/std_data/funcs_1/t7.txt' 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 = <engine_to_be_tested>; load data infile '<MYSQLTEST_VARDIR>/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 = <engine_to_be_tested>; load data infile '<MYSQLTEST_VARDIR>/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 = <engine_to_be_tested>; load data infile '<MYSQLTEST_VARDIR>/std_data/funcs_1/t4.txt' into table t11; Section 3.1.6 - Privilege Checks: -------------------------------------------------------------------------------- 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; 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' USE db_storedproc_1; root@localhost db_storedproc_1 GRANT CREATE ROUTINE ON db_storedproc_1.* TO 'user_1'@'localhost'; 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// 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; 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// 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// 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 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; 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// user_2@localhost db_storedproc_1 CALL sp5_s_i(); ERROR 42000: SELECT command denied to user 'user_1'@'localhost' for table 't3165' CALL sp5_ins(); ERROR 42000: INSERT command denied to user 'user_1'@'localhost' for table 't3165' CALL sp5_sel(); ERROR 42000: SELECT command denied to user 'user_1'@'localhost' for table 't3165' root@localhost db_storedproc_1 CALL sp5_sel(); ERROR 42000: SELECT command denied to user 'user_1'@'localhost' for table 't3165' grant insert on db_storedproc_1.* to 'user_1'@'localhost'; flush privileges; user_2@localhost db_storedproc_1 CALL sp5_s_i(); ERROR 42000: SELECT command denied to user 'user_1'@'localhost' for table 't3165' CALL sp5_ins(); CALL sp5_sel(); ERROR 42000: SELECT command denied to user 'user_1'@'localhost' for table 't3165' root@localhost db_storedproc_1 CALL sp5_sel(); ERROR 42000: SELECT command denied to user 'user_1'@'localhost' for table 't3165' grant SELECT on db_storedproc_1.* to 'user_1'@'localhost'; flush privileges; 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 root@localhost db_storedproc_1 REVOKE INSERT on db_storedproc_1.* from 'user_1'@'localhost'; flush privileges; 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 't3165' CALL sp5_ins(); ERROR 42000: INSERT command denied to user 'user_1'@'localhost' for table '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 root@localhost db_storedproc_1 REVOKE SELECT on db_storedproc_1.* from 'user_1'@'localhost'; flush privileges; user_2@localhost db_storedproc_1 CALL sp5_s_i(); ERROR 42000: SELECT command denied to user 'user_1'@'localhost' for table 't3165' CALL sp5_ins(); ERROR 42000: INSERT command denied to user 'user_1'@'localhost' for table 't3165' CALL sp5_sel(); ERROR 42000: SELECT command denied to user 'user_1'@'localhost' for table 't3165' 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; 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// 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 't3166' CALL sp3166_ins(); ERROR 42000: INSERT command denied to user 'user_2'@'localhost' for table 't3166' CALL sp3166_sel(); c1 inserted outside SP root@localhost db_storedproc_1 CALL sp3166_sel(); c1 inserted outside SP GRANT INSERT ON db_storedproc_1.* TO 'user_2'@'localhost'; FLUSH PRIVILEGES; 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 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; user_2@localhost db_storedproc_1 CALL sp3166_s_i(); ERROR 42000: SELECT command denied to user 'user_2'@'localhost' for table 't3166' CALL sp3166_ins(); CALL sp3166_sel(); ERROR 42000: SELECT command denied to user 'user_2'@'localhost' for table 't3166' 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; 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' 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; . +++ END OF SCRIPT +++ --------------------------------------------------------------------------------