1 files changed, 216 insertions, 0 deletions
diff --git a/mysql-test/t/sp-security.test b/mysql-test/t/sp-security.test
new file mode 100644
index 00000000000..d1119499cf1
--- /dev/null
+++ b/mysql-test/t/sp-security.test
@@ -0,0 +1,216 @@
+#
+# Testing SQL SECURITY of stored procedures
+#
+
+connect (con1root,localhost,root,,);
+
+connection con1root;
+use test;
+
+# Create user user1 with no particular access rights
+grant usage on *.* to user1@localhost;
+flush privileges;
+
+--disable_warnings
+drop database if exists db1_secret;
+--enable_warnings
+# Create our secret database
+create database db1_secret;
+
+# Can create a procedure in other db
+create procedure db1_secret.dummy() begin end;
+drop procedure db1_secret.dummy;
+
+use db1_secret;
+
+create table t1 ( u varchar(64), i int );
+
+# A test procedure and function
+create procedure stamp(i int)
+  insert into db1_secret.t1 values (user(), i);
+--replace_column 5 '0000-00-00 00:00:00' 6 '0000-00-00 00:00:00'
+show procedure status like 'stamp';
+
+create function db() returns varchar(64) return database();
+--replace_column 5 '0000-00-00 00:00:00' 6 '0000-00-00 00:00:00'
+show function status like 'db';
+
+# root can, of course
+call stamp(1);
+select * from t1;
+select db();
+
+connect (con2user1,localhost,user1,,);
+connect (con3anon,localhost,anon,,);
+
+
+#
+# User1 can
+#
+connection con2user1;
+
+# This should work...
+call db1_secret.stamp(2);
+select db1_secret.db();
+
+# ...but not this
+--error 1044
+select * from db1_secret.t1;
+
+# ...and not this
+--error 1044
+create procedure db1_secret.dummy() begin end;
+--error 1305
+drop procedure db1_secret.dummy;
+
+
+#
+# Anonymous can
+#
+connection con3anon;
+
+# This should work...
+call db1_secret.stamp(3);
+select db1_secret.db();
+
+# ...but not this
+--error 1044
+select * from db1_secret.t1;
+
+# ...and not this
+--error 1044
+create procedure db1_secret.dummy() begin end;
+--error 1305
+drop procedure db1_secret.dummy;
+
+
+#
+# Check it out
+#
+connection con1root;
+select * from t1;
+
+#
+# Change to invoker's rights
+#
+alter procedure stamp sql security invoker;
+--replace_column 5 '0000-00-00 00:00:00' 6 '0000-00-00 00:00:00'
+show procedure status like 'stamp';
+
+alter function db sql security invoker;
+--replace_column 5 '0000-00-00 00:00:00' 6 '0000-00-00 00:00:00'
+show function status like 'db';
+
+# root still can
+call stamp(4);
+select * from t1;
+select db();
+
+#
+# User1 cannot
+#
+connection con2user1;
+
+# This should not work
+--error 1044
+call db1_secret.stamp(5);
+--error 1044
+select db1_secret.db();
+
+#
+# Anonymous cannot
+#
+connection con3anon;
+
+# This should not work
+--error 1044
+call db1_secret.stamp(6);
+--error 1044
+select db1_secret.db();
+
+#
+# BUG#2777
+#
+
+connection con1root;
+--disable_warnings
+drop database if exists db2;
+--enable_warnings
+create database db2;
+
+use db2;
+
+create table t2 (s1 int);
+insert into t2 values (0);
+
+grant usage on db2.* to user1@localhost;
+grant select on db2.* to user1@localhost;
+grant usage on db2.* to user2@localhost;
+grant select,insert,update,delete on db2.* to user2@localhost;
+flush privileges;
+
+connection con2user1;
+use db2;
+
+create procedure p () insert into t2 values (1);
+
+# Check that this doesn't work.
+--error 1044
+call p();
+
+connect (con4user2,localhost,user2,,);
+
+connection con4user2;
+use db2;
+
+# This should not work, since p is executed with definer's (user1's) rights.
+--error 1044
+call p();
+select * from t2;
+
+create procedure q () insert into t2 values (2);
+
+call q();
+select * from t2;
+
+connection con2user1;
+use db2;
+
+# This should work
+call q();
+select * from t2;
+
+
+#
+# BUG#6030: Stored procedure has no appropriate DROP privilege
+# (or ALTER for that matter)
+
+# still connection con2user1 in db2
+
+# This should work:
+alter procedure p modifies sql data;
+drop procedure p;
+
+# This should NOT work
+--error 1370
+alter procedure q modifies sql data;
+--error 1370
+drop procedure q;
+
+connection con1root;
+use db2;
+# But root always can
+alter procedure q modifies sql data;
+drop procedure q;
+
+
+# Clean up
+#Still connection con1root;
+use test;
+select type,db,name from mysql.proc;
+drop database db1_secret;
+drop database db2;
+# Make sure the routines are gone
+select type,db,name from mysql.proc;
+# Get rid of the users
+delete from mysql.user where user='user1' or user='user2';