--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_ln/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_ln/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_ln/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_ln/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_ln/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_ln/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_ln/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_ln/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_ln/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_ln/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; connect(localhost,user_1,,db_storedproc_1,MYSQL_PORT,MYSQL_SOCK); 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'; connect(localhost,user_1,,db_storedproc_1,MYSQL_PORT,MYSQL_SOCK); 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; connect(localhost,user_1,,db_storedproc_1,MYSQL_PORT,MYSQL_SOCK); user_1@localhost db_storedproc_1 DROP PROCEDURE IF EXISTS sp3; DROP FUNCTION IF EXISTS fn1; 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; Warnings: Error 1133 Can't find any matching row in the user table Error 1269 Can't revoke all privileges for one or more of the requested users Warning 1405 Failed to revoke all privileges to dropped routine 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(localhost,user_1,,db_storedproc_1,MYSQL_PORT,MYSQL_SOCK); 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 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; connect(localhost,user_1,,db_storedproc_1,MYSQL_PORT,MYSQL_SOCK); 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// connect(localhost,user_2,,db_storedproc_1,MYSQL_PORT,MYSQL_SOCK); 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; connect(localhost,user_1,,db_storedproc_1,MYSQL_PORT,MYSQL_SOCK); 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// connect(localhost,user_2,,db_storedproc_1,MYSQL_PORT,MYSQL_SOCK); 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; connect(localhost,user_2,,db_storedproc_1,MYSQL_PORT,MYSQL_SOCK); 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; connect(localhost,user_2,,db_storedproc_1,MYSQL_PORT,MYSQL_SOCK); 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; connect(localhost,user_2,,db_storedproc_1,MYSQL_PORT,MYSQL_SOCK); 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 +++ --------------------------------------------------------------------------------