Fixed BUG#2777: Stored procedure doesn't observe definer's rights.

SQL SECURITY DEFINER must enforce reduced rights too, not just additional rights.


mysql-test/r/sp-security.result:
  Test case for BUG#2777: Make sure that SQL SECURITY DEFINER enforces reduced rights.
mysql-test/t/sp-security.test:
  Test case for BUG#2777: Make sure that SQL SECURITY DEFINER enforces reduced rights.
sql/sql_acl.cc:
  Clear rights before changing them in acl_getroot_no_password so that
  reduced rights work too, and take care of db acls as well.

1 files changed, 43 insertions, 6 deletions

diff --git a/mysql-test/r/sp-security.result b/mysql-test/r/sp-security.result
index 9d5f71225b6..c4fbece9d72 100644
--- a/mysql-test/r/sp-security.result
+++ b/mysql-test/r/sp-security.result
@@ -1,5 +1,6 @@
 use test;
-grant usage on *.* to dummy@localhost;
+grant usage on *.* to user1@localhost;
+flush privileges;
 drop database if exists db1_secret;
 create database db1_secret;
 use db1_secret;
@@ -15,14 +16,14 @@ 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'
+ERROR 42000: Access denied for user: 'user1'@'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
+user1@localhost	2
 anon@localhost	3
 alter procedure stamp sql security invoker;
 show procedure status like 'stamp';
@@ -32,14 +33,50 @@ call stamp(4);
 select * from t1;
 u	i
 root@localhost	1
-dummy@localhost	2
+user1@localhost	2
 anon@localhost	3
 root@localhost	4
 call stamp(5);
-ERROR 42000: Access denied for user: 'dummy'@'localhost' to database 'db1_secret'
+ERROR 42000: Access denied for user: 'user1'@'localhost' to database 'db1_secret'
 call stamp(6);
 ERROR 42000: Access denied for user: ''@'localhost' to database 'db1_secret'
+drop database if exists db2;
+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;
+use db2;
+create procedure p () insert into t2 values (1);
+call p();
+ERROR 42000: Access denied for user: 'user1'@'localhost' to database 'db2'
+use db2;
+call p();
+ERROR 42000: Access denied for user: 'user1'@'localhost' to database 'db2'
+select * from t2;
+s1
+0
+create procedure q () insert into t2 values (2);
+call q();
+select * from t2;
+s1
+0
+2
+use db2;
+call q();
+select * from t2;
+s1
+0
+2
+2
 drop procedure stamp;
+drop procedure p;
+drop procedure q;
 use test;
 drop database db1_secret;
-delete from mysql.user where user='dummy';
+drop database db2;
+delete from mysql.user where user='user1' or user='user2';