diff options
author | unknown <hhunger@hh-nb.hungers> | 2007-02-06 19:07:48 +0100 |
---|---|---|
committer | unknown <hhunger@hh-nb.hungers> | 2007-02-06 19:07:48 +0100 |
commit | 630091fed42e48df481d67835e48cb98cb863ae7 (patch) | |
tree | 1f3a82ed5e90d7c7be80041002990f6372f5dce9 /mysql-test/suite/funcs_1/r/myisam_storedproc_06.result | |
parent | 9bb750f5e8ddbf2b400d43f9e1799793f6ed42ce (diff) | |
download | mariadb-git-630091fed42e48df481d67835e48cb98cb863ae7.tar.gz |
copy from test-extra-5.0 to main tree
BitKeeper/etc/ignore:
Added mysql-test/mysql-test-run-shell to the ignore list
mysql-test/suite/funcs_2/include/check_charset.inc:
finished files with newline
Diffstat (limited to 'mysql-test/suite/funcs_1/r/myisam_storedproc_06.result')
-rw-r--r-- | mysql-test/suite/funcs_1/r/myisam_storedproc_06.result | 399 |
1 files changed, 399 insertions, 0 deletions
diff --git a/mysql-test/suite/funcs_1/r/myisam_storedproc_06.result b/mysql-test/suite/funcs_1/r/myisam_storedproc_06.result new file mode 100644 index 00000000000..11b1d3873b3 --- /dev/null +++ b/mysql-test/suite/funcs_1/r/myisam_storedproc_06.result @@ -0,0 +1,399 @@ + +--source suite/funcs_1/storedproc/load_sp_tb.inc +-------------------------------------------------------------------------------- +SET @@global.max_heap_table_size=4294967295; +SET @@session.max_heap_table_size=4294967295; + +--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 = myisam; +load data infile 'MYSQL_TEST_DIR/suite/funcs_1/data/t4.txt' into table t1; +create table t2(f1 char(20),f2 char(25),f3 date,f4 int,f5 char(25),f6 int) engine = myisam; +load data infile 'MYSQL_TEST_DIR/suite/funcs_1/data/t4.txt' into table t2; +create table t3(f1 char(20),f2 char(20),f3 integer) engine = myisam; +load data infile 'MYSQL_TEST_DIR/suite/funcs_1/data/t3.txt' into table t3; +create table t4(f1 char(20),f2 char(25),f3 date,f4 int,f5 char(25),f6 int) engine = myisam; +load data infile 'MYSQL_TEST_DIR/suite/funcs_1/data/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 = myisam; +load data infile 'MYSQL_TEST_DIR/suite/funcs_1/data/t4.txt' into table t6; +USE db_storedproc; +create table t7 (f1 char(20), f2 char(25), f3 date, f4 int) engine = myisam; +load data infile 'MYSQL_TEST_DIR/suite/funcs_1/data/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 = myisam; +load data infile 'MYSQL_TEST_DIR/suite/funcs_1/data/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 = myisam; +load data infile 'MYSQL_TEST_DIR/suite/funcs_1/data/t9.txt' into table t9; +create table t10(f1 char(20),f2 char(25),f3 date,f4 int,f5 char(25),f6 int) engine = myisam; +load data infile 'MYSQL_TEST_DIR/suite/funcs_1/data/t4.txt' into table t10; +create table t11(f1 char(20),f2 char(25),f3 date,f4 int,f5 char(25),f6 int) engine = myisam; +load data infile 'MYSQL_TEST_DIR/suite/funcs_1/data/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' + +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// + +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// + +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 +++ +-------------------------------------------------------------------------------- |