WL#1365: Implement definer's rights execution of stored procedures.

(Also put the hostpart back in the definer column.)


mysql-test/r/sp-error.result:
  Moved error test from sp.test
mysql-test/r/sp.result:
  Moved error test to sp-error.test.
  Put hostpart back into definer column in mysql.proc.
mysql-test/t/sp-error.test:
  Moved error test from sp.test
mysql-test/t/sp.test:
  Moved error test to sp-error.test.
  Put hostpart back into definer column in mysql.proc.
sql/item_func.cc:
  (Maybe) switch security context before invoking a stored function.
sql/sp.cc:
  Renamed creator into definer, for more consistent terminology, and put the
  hostpart back.
sql/sp_head.cc:
  Some fixes in the way things are allocated, and moved set_info() definition
  here from sp_head.h. creator is now called definer, and is split into a
  user and host part.
  Added functions for (possible) change and restore of privileges, for sql security
  definer calls.
sql/sp_head.h:
  Moved set_info() definition here from sp_head.h.
  creator is now called definer, and is split into a user and host part.
  Added functions for (possible) change and restore of privileges, for sql security
  definer calls.
sql/sql_acl.cc:
  New function acl_getroot_no_password() for getting the privileges used when
  calling an SP with sql security definer.
sql/sql_acl.h:
  New function acl_getroot_no_password() for getting the privileges used when
  calling an SP with sql security definer.
sql/sql_parse.cc:
  (Maybe) switch security context before invoking a stored procedure.
sql/sql_yacc.yy:
  Fixed typo.

6 files changed, 180 insertions, 36 deletions

diff --git a/mysql-test/r/sp-error.result b/mysql-test/r/sp-error.result
index 22f6d37f8de..ac952549803 100644
--- a/mysql-test/r/sp-error.result
+++ b/mysql-test/r/sp-error.result
@@ -280,4 +280,15 @@ create function bug1654()
 returns int
 return (select sum(t.data) from test.t2 t);
 ERROR 0A000: Statements like SELECT, INSERT, UPDATE (and others) are not allowed in a FUNCTION
+drop table if exists table_1;
+create table t3 (column_1_0 int);
+create procedure bug1653()
+update t3 set column_1 = 0;
+call bug1653();
+ERROR 42S22: Unknown column 'column_1' in 'field list'
+drop table t3;
+create table t3 (column_1 int);
+call bug1653();
+drop procedure bug1653;
+drop table t3;
 drop table t1;
diff --git a/mysql-test/r/sp-security.result b/mysql-test/r/sp-security.result
new file mode 100644
index 00000000000..00e22ceebd4
--- /dev/null
+++ b/mysql-test/r/sp-security.result
@@ -0,0 +1,44 @@
+use test;
+grant usage on *.* to dummy@localhost;
+drop database if exists db1_secret;
+create database db1_secret;
+use db1_secret;
+create table t1 ( u varchar(64), i int );
+create procedure stamp(i int)
+insert into db1_secret.t1 values (user(), i);
+show procedure status like 'stamp';
+Name	Type	Definer	Modified	Created	Security_type	Comment
+stamp	PROCEDURE	root@localhost	0000-00-00 00:00:00	0000-00-00 00:00:00	DEFINER	
+call stamp(1);
+select * from t1;
+u	i
+root@localhost	1
+call stamp(2);
+select * from db1_secret.t1;
+ERROR 42000: Access denied for user: 'dummy'@'localhost' to database 'db1_secret'
+call stamp(3);
+select * from db1_secret.t1;
+ERROR 42000: Access denied for user: ''@'localhost' to database 'db1_secret'
+select * from t1;
+u	i
+root@localhost	1
+dummy@localhost	2
+anon@localhost	3
+alter procedure stamp sql security invoker;
+show procedure status like 'stamp';
+Name	Type	Definer	Modified	Created	Security_type	Comment
+stamp	PROCEDURE	root@localhost	0000-00-00 00:00:00	0000-00-00 00:00:00	INVOKER	
+call stamp(4);
+select * from t1;
+u	i
+root@localhost	1
+dummy@localhost	2
+anon@localhost	3
+root@localhost	4
+call stamp(5);
+ERROR 42000: Access denied for user: 'dummy'@'localhost' to database 'db1_secret'
+call stamp(6);
+ERROR 42000: Access denied for user: ''@'localhost' to database 'db1_secret'
+use test;
+drop database db1_secret;
+delete from mysql.user where user='dummy';
diff --git a/mysql-test/r/sp.result b/mysql-test/r/sp.result
index 5c506e7bf14..05e6096f8ac 100644
--- a/mysql-test/r/sp.result
+++ b/mysql-test/r/sp.result
@@ -866,17 +866,6 @@ avg	0	4.4
 delete from t1;
 delete from t2;
 drop procedure bug1874;
-drop table if exists table_1;
-create table t3 (column_1_0 int);
-create procedure bug1653()
-update t3 set column_1 = 0;
-call bug1653();
-ERROR 42S22: Unknown column 'column_1' in 'field list'
-drop table t3;
-create table t3 (column_1 int);
-call bug1653();
-drop procedure bug1653;
-drop table t3;
 drop table if exists fac;
 create table fac (n int unsigned not null primary key, f bigint unsigned);
 create procedure ifac(n int unsigned)
@@ -918,7 +907,7 @@ n	f
 drop table fac;
 show function status like '%f%';
 Name	Type	Definer	Modified	Created	Security_type	Comment
-fac	FUNCTION	root	0000-00-00 00:00:00	0000-00-00 00:00:00	DEFINER	
+fac	FUNCTION	root@localhost	0000-00-00 00:00:00	0000-00-00 00:00:00	DEFINER	
 drop procedure ifac;
 drop function fac;
 show function status like '%f%';
@@ -1011,8 +1000,8 @@ end loop;
 end
 show procedure status like '%p%';
 Name	Type	Definer	Modified	Created	Security_type	Comment
-ip	PROCEDURE	root	0000-00-00 00:00:00	0000-00-00 00:00:00	DEFINER	
-opp	PROCEDURE	root	0000-00-00 00:00:00	0000-00-00 00:00:00	DEFINER	
+ip	PROCEDURE	root@localhost	0000-00-00 00:00:00	0000-00-00 00:00:00	DEFINER	
+opp	PROCEDURE	root@localhost	0000-00-00 00:00:00	0000-00-00 00:00:00	DEFINER	
 call ip(200);
 select * from primes where i=45 or i=100 or i=199;
 i	p
@@ -1074,7 +1063,7 @@ comment "111111111111" sql security invoker
 insert into test.t1 values (x, y);
 show procedure status like 'bar';
 Name	Type	Definer	Modified	Created	Security_type	Comment
-bar	PROCEDURE	root	0000-00-00 00:00:00	0000-00-00 00:00:00	INVOKER	111111111111
+bar	PROCEDURE	root@localhost	0000-00-00 00:00:00	0000-00-00 00:00:00	INVOKER	111111111111
 alter procedure bar name bar2 comment "2222222222" sql security definer;
 alter procedure bar2 name bar comment "3333333333";
 alter procedure bar;
@@ -1085,7 +1074,7 @@ bar	CREATE PROCEDURE bar(x char(16), y int)
 insert into test.t1 values (x, y)
 show procedure status like 'bar';
 Name	Type	Definer	Modified	Created	Security_type	Comment
-bar	PROCEDURE	root	0000-00-00 00:00:00	0000-00-00 00:00:00	DEFINER	3333333333
+bar	PROCEDURE	root@localhost	0000-00-00 00:00:00	0000-00-00 00:00:00	DEFINER	3333333333
 drop procedure bar;
 drop table t1;
 drop table t2;
diff --git a/mysql-test/t/sp-error.test b/mysql-test/t/sp-error.test
index 042e9baa47f..ab13559b860 100644
--- a/mysql-test/t/sp-error.test
+++ b/mysql-test/t/sp-error.test
@@ -379,6 +379,27 @@ create function bug1654()
   returns int
 return (select sum(t.data) from test.t2 t)|
 
+#
+# BUG#1653
+#
+--disable_warnings
+drop table if exists table_1|
+--enable_warnings
+create table t3 (column_1_0 int)|
+
+create procedure bug1653()
+  update t3 set column_1 = 0|
+
+--error 1054
+call bug1653()|
+drop table t3|
+create table t3 (column_1 int)|
+call bug1653()|
+
+drop procedure bug1653|
+drop table t3|
+
+
 drop table t1|
 
 delimiter ;|
diff --git a/mysql-test/t/sp-security.test b/mysql-test/t/sp-security.test
new file mode 100644
index 00000000000..923438c8525
--- /dev/null
+++ b/mysql-test/t/sp-security.test
@@ -0,0 +1,99 @@
+#
+# Testing SQL SECURITY of stored procedures
+#
+
+connect (con1root,localhost,root,,);
+
+connection con1root;
+use test;
+
+# Create dummy user with no particular access rights
+grant usage on *.* to dummy@localhost;
+
+--disable_warnings
+drop database if exists db1_secret;
+--enable_warnings
+# Create our secret database
+create database db1_secret;
+
+use db1_secret;
+
+create table t1 ( u varchar(64), i int );
+
+# Our test procedure
+create procedure stamp(i int)
+  insert into db1_secret.t1 values (user(), i);
+--replace_column 4 '0000-00-00 00:00:00' 5 '0000-00-00 00:00:00'
+show procedure status like 'stamp';
+
+# root can, of course
+call stamp(1);
+select * from t1;
+
+connect (con2dummy,localhost,dummy,,);
+connect (con3anon,localhost,anon,,);
+
+#
+# Dummy can
+#
+connection con2dummy;
+
+# This should work...
+call stamp(2);
+
+# ...but not this
+--error 1044
+select * from db1_secret.t1;
+
+#
+# Anonymous can
+#
+connection con3anon;
+
+# This should work...
+call stamp(3);
+
+# ...but not this
+--error 1044
+select * from db1_secret.t1;
+
+#
+# Check it out
+#
+connection con1root;
+select * from t1;
+
+#
+# Change to invoker's rights
+#
+alter procedure stamp sql security invoker;
+--replace_column 4 '0000-00-00 00:00:00' 5 '0000-00-00 00:00:00'
+show procedure status like 'stamp';
+
+# root still can
+call stamp(4);
+select * from t1;
+
+#
+# Dummy cannot
+#
+connection con2dummy;
+
+# This should not work
+--error 1044
+call stamp(5);
+
+#
+# Anonymous cannot
+#
+connection con3anon;
+
+# This should not work
+--error 1044
+call stamp(6);
+
+# Clean up
+connection con1root;
+use test;
+drop database db1_secret;
+delete from mysql.user where user='dummy';
diff --git a/mysql-test/t/sp.test b/mysql-test/t/sp.test
index 3681d9a9517..cda03e46e37 100644
--- a/mysql-test/t/sp.test
+++ b/mysql-test/t/sp.test
@@ -1013,26 +1013,6 @@ delete from t1|
 delete from t2|
 drop procedure bug1874|
 
-#
-# BUG#1653
-#
---disable_warnings
-drop table if exists table_1|
---enable_warnings
-create table t3 (column_1_0 int)|
-
-create procedure bug1653()
-  update t3 set column_1 = 0|
-
---error 1054
-call bug1653()|
-drop table t3|
-create table t3 (column_1 int)|
-call bug1653()|
-
-drop procedure bug1653|
-drop table t3|
-
 
 #
 # Some "real" examples