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

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 +++
+--------------------------------------------------------------------------------