summaryrefslogtreecommitdiff
path: root/mysql-test
diff options
context:
space:
mode:
Diffstat (limited to 'mysql-test')
-rw-r--r--mysql-test/r/gis.result2
-rw-r--r--mysql-test/r/information_schema.result4
-rw-r--r--mysql-test/r/mysqldump.result14
-rw-r--r--mysql-test/r/rpl_ddl.result4
-rw-r--r--mysql-test/r/rpl_sp.result30
-rw-r--r--mysql-test/r/rpl_trigger.result43
-rw-r--r--mysql-test/r/skip_grants.result21
-rw-r--r--mysql-test/r/sp-security.result96
-rw-r--r--mysql-test/r/sp.result28
-rw-r--r--mysql-test/r/sql_mode.result8
-rw-r--r--mysql-test/std_data/bug16266.000001bin0 -> 532 bytes
-rw-r--r--mysql-test/t/rpl_trigger.test74
-rw-r--r--mysql-test/t/skip_grants.test53
-rw-r--r--mysql-test/t/sp-security.test196
14 files changed, 515 insertions, 58 deletions
diff --git a/mysql-test/r/gis.result b/mysql-test/r/gis.result
index 049ba784dc9..13e2d56d83e 100644
--- a/mysql-test/r/gis.result
+++ b/mysql-test/r/gis.result
@@ -680,7 +680,7 @@ drop procedure if exists fn3;
create function fn3 () returns point return GeomFromText("point(1 1)");
show create function fn3;
Function sql_mode Create Function
-fn3 CREATE FUNCTION `fn3`() RETURNS point
+fn3 CREATE DEFINER=`root`@`localhost` FUNCTION `fn3`() RETURNS point
return GeomFromText("point(1 1)")
select astext(fn3());
astext(fn3())
diff --git a/mysql-test/r/information_schema.result b/mysql-test/r/information_schema.result
index d3576e24a44..1c845c73e4b 100644
--- a/mysql-test/r/information_schema.result
+++ b/mysql-test/r/information_schema.result
@@ -309,7 +309,7 @@ Function sql_mode Create Function
sub1
show create function sub2;
Function sql_mode Create Function
-sub2 CREATE FUNCTION `sub2`(i int) RETURNS int(11)
+sub2 CREATE DEFINER=`mysqltest_1`@`localhost` FUNCTION `sub2`(i int) RETURNS int(11)
return i+1
show function status like "sub2";
Db Name Type Definer Modified Created Security_type Comment
@@ -317,7 +317,7 @@ test sub2 FUNCTION mysqltest_1@localhost # # DEFINER
drop function sub2;
show create procedure sel2;
Procedure sql_mode Create Procedure
-sel2 CREATE PROCEDURE `sel2`()
+sel2 CREATE DEFINER=`root`@`localhost` PROCEDURE `sel2`()
begin
select * from t1;
select * from t2;
diff --git a/mysql-test/r/mysqldump.result b/mysql-test/r/mysqldump.result
index d156e711167..e6ac9fbc740 100644
--- a/mysql-test/r/mysqldump.result
+++ b/mysql-test/r/mysqldump.result
@@ -2210,12 +2210,12 @@ UNLOCK TABLES;
DELIMITER ;;
/*!50003 DROP FUNCTION IF EXISTS `bug9056_func1` */;;
/*!50003 SET SESSION SQL_MODE=""*/;;
-/*!50003 CREATE FUNCTION `bug9056_func1`(a INT, b INT) RETURNS int(11)
+/*!50003 CREATE*/ /*!50019 DEFINER=`root`@`localhost`*/ /*!50003 FUNCTION `bug9056_func1`(a INT, b INT) RETURNS int(11)
RETURN a+b */;;
/*!50003 SET SESSION SQL_MODE=@OLD_SQL_MODE*/;;
/*!50003 DROP FUNCTION IF EXISTS `bug9056_func2` */;;
/*!50003 SET SESSION SQL_MODE=""*/;;
-/*!50003 CREATE FUNCTION `bug9056_func2`(f1 char binary) RETURNS char(1)
+/*!50003 CREATE*/ /*!50019 DEFINER=`root`@`localhost`*/ /*!50003 FUNCTION `bug9056_func2`(f1 char binary) RETURNS char(1)
begin
set f1= concat( 'hello', f1 );
return f1;
@@ -2223,17 +2223,17 @@ end */;;
/*!50003 SET SESSION SQL_MODE=@OLD_SQL_MODE*/;;
/*!50003 DROP PROCEDURE IF EXISTS `a'b` */;;
/*!50003 SET SESSION SQL_MODE="REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ANSI"*/;;
-/*!50003 CREATE PROCEDURE "a'b"()
+/*!50003 CREATE*/ /*!50019 DEFINER="root"@"localhost"*/ /*!50003 PROCEDURE "a'b"()
select 1 */;;
/*!50003 SET SESSION SQL_MODE=@OLD_SQL_MODE*/;;
/*!50003 DROP PROCEDURE IF EXISTS `bug9056_proc1` */;;
/*!50003 SET SESSION SQL_MODE=""*/;;
-/*!50003 CREATE PROCEDURE `bug9056_proc1`(IN a INT, IN b INT, OUT c INT)
+/*!50003 CREATE*/ /*!50019 DEFINER=`root`@`localhost`*/ /*!50003 PROCEDURE `bug9056_proc1`(IN a INT, IN b INT, OUT c INT)
BEGIN SELECT a+b INTO c; end */;;
/*!50003 SET SESSION SQL_MODE=@OLD_SQL_MODE*/;;
/*!50003 DROP PROCEDURE IF EXISTS `bug9056_proc2` */;;
/*!50003 SET SESSION SQL_MODE=""*/;;
-/*!50003 CREATE PROCEDURE `bug9056_proc2`(OUT a INT)
+/*!50003 CREATE*/ /*!50019 DEFINER=`root`@`localhost`*/ /*!50003 PROCEDURE `bug9056_proc2`(OUT a INT)
BEGIN
select sum(id) from t1 into a;
END */;;
@@ -2685,11 +2685,11 @@ return 42 */|
select 42 */|
show create function f;
Function sql_mode Create Function
-f CREATE FUNCTION `f`() RETURNS bigint(20)
+f CREATE DEFINER=`root`@`localhost` FUNCTION `f`() RETURNS bigint(20)
return 42
show create procedure p;
Procedure sql_mode Create Procedure
-p CREATE PROCEDURE `p`()
+p CREATE DEFINER=`root`@`localhost` PROCEDURE `p`()
select 42
drop function f;
drop procedure p;
diff --git a/mysql-test/r/rpl_ddl.result b/mysql-test/r/rpl_ddl.result
index c56c9f20cf8..92e91b31459 100644
--- a/mysql-test/r/rpl_ddl.result
+++ b/mysql-test/r/rpl_ddl.result
@@ -1133,7 +1133,7 @@ SHOW PROCEDURE STATUS LIKE 'p1';
Db mysqltest1
Name p1
Type PROCEDURE
-Definer @
+Definer root@localhost
Modified #
Created #
Security_type DEFINER
@@ -1199,7 +1199,7 @@ SHOW PROCEDURE STATUS LIKE 'p1';
Db mysqltest1
Name p1
Type PROCEDURE
-Definer @
+Definer root@localhost
Modified #
Created #
Security_type DEFINER
diff --git a/mysql-test/r/rpl_sp.result b/mysql-test/r/rpl_sp.result
index a42c33ce333..5dfda16c763 100644
--- a/mysql-test/r/rpl_sp.result
+++ b/mysql-test/r/rpl_sp.result
@@ -31,7 +31,7 @@ declare b int;
set b = 8;
insert into t1 values (b);
insert into t1 values (unix_timestamp());
-end @ # #
+end root@localhost # #
set timestamp=1000000000;
call foo();
select * from t1;
@@ -118,7 +118,7 @@ select * from mysql.proc where name="foo4" and db='mysqltest1';
db name type specific_name language sql_data_access is_deterministic security_type param_list returns body definer created modified sql_mode comment
mysqltest1 foo4 PROCEDURE foo4 SQL CONTAINS_SQL YES DEFINER begin
insert into t2 values(20),(20);
-end @ # #
+end root@localhost # #
drop procedure foo4;
select * from mysql.proc where name="foo4" and db='mysqltest1';
db name type specific_name language sql_data_access is_deterministic security_type param_list returns body definer created modified sql_mode comment
@@ -223,13 +223,13 @@ select * from mysql.proc where db='mysqltest1';
db name type specific_name language sql_data_access is_deterministic security_type param_list returns body definer created modified sql_mode comment
mysqltest1 fn1 FUNCTION fn1 SQL NO_SQL NO DEFINER int(11) begin
return unix_timestamp();
-end @ # #
+end root@localhost # #
mysqltest1 fn2 FUNCTION fn2 SQL NO_SQL NO DEFINER int(11) begin
return unix_timestamp();
-end @ # #
+end zedjzlcsjhd@localhost # #
mysqltest1 fn3 FUNCTION fn3 SQL READS_SQL_DATA NO DEFINER int(11) begin
return 0;
-end @ # #
+end root@localhost # #
delete from t2;
alter table t2 add unique (a);
drop function fn1;
@@ -274,7 +274,7 @@ Log_name Pos Event_type Server_id End_log_pos Info
master-bin.000001 # Query 1 # drop database if exists mysqltest1
master-bin.000001 # Query 1 # create database mysqltest1
master-bin.000001 # Query 1 # use `mysqltest1`; create table t1 (a varchar(100))
-master-bin.000001 # Query 1 # use `mysqltest1`; create procedure foo()
+master-bin.000001 # Query 1 # use `mysqltest1`; CREATE DEFINER=`root`@`localhost` procedure foo()
begin
declare b int;
set b = 8;
@@ -284,19 +284,19 @@ end
master-bin.000001 # Query 1 # use `mysqltest1`; insert into t1 values ( NAME_CONST('b',8))
master-bin.000001 # Query 1 # use `mysqltest1`; insert into t1 values (unix_timestamp())
master-bin.000001 # Query 1 # use `mysqltest1`; delete from t1
-master-bin.000001 # Query 1 # use `mysqltest1`; create procedure foo2()
+master-bin.000001 # Query 1 # use `mysqltest1`; CREATE DEFINER=`root`@`localhost` procedure foo2()
select * from mysqltest1.t1
master-bin.000001 # Query 1 # use `mysqltest1`; alter procedure foo2 contains sql
master-bin.000001 # Query 1 # use `mysqltest1`; drop table t1
master-bin.000001 # Query 1 # use `mysqltest1`; create table t1 (a int)
master-bin.000001 # Query 1 # use `mysqltest1`; create table t2 like t1
-master-bin.000001 # Query 1 # use `mysqltest1`; create procedure foo3()
+master-bin.000001 # Query 1 # use `mysqltest1`; CREATE DEFINER=`root`@`localhost` procedure foo3()
deterministic
insert into t1 values (15)
master-bin.000001 # Query 1 # use `mysqltest1`; grant CREATE ROUTINE, EXECUTE on mysqltest1.* to "zedjzlcsjhd"@127.0.0.1
master-bin.000001 # Query 1 # use `mysqltest1`; grant SELECT on mysqltest1.t1 to "zedjzlcsjhd"@127.0.0.1
master-bin.000001 # Query 1 # use `mysqltest1`; grant SELECT, INSERT on mysqltest1.t2 to "zedjzlcsjhd"@127.0.0.1
-master-bin.000001 # Query 1 # use `mysqltest1`; create procedure foo4()
+master-bin.000001 # Query 1 # use `mysqltest1`; CREATE DEFINER=`zedjzlcsjhd`@`127.0.0.1` procedure foo4()
deterministic
begin
insert into t2 values(3);
@@ -311,7 +311,7 @@ master-bin.000001 # Query 1 # use `mysqltest1`; insert into t1 values (5)
master-bin.000001 # Query 1 # use `mysqltest1`; delete from t2
master-bin.000001 # Query 1 # use `mysqltest1`; alter table t2 add unique (a)
master-bin.000001 # Query 1 # use `mysqltest1`; drop procedure foo4
-master-bin.000001 # Query 1 # use `mysqltest1`; create procedure foo4()
+master-bin.000001 # Query 1 # use `mysqltest1`; CREATE DEFINER=`root`@`localhost` procedure foo4()
deterministic
begin
insert into t2 values(20),(20);
@@ -321,7 +321,7 @@ master-bin.000001 # Query 1 # use `mysqltest1`; drop procedure foo4
master-bin.000001 # Query 1 # use `mysqltest1`; drop procedure foo
master-bin.000001 # Query 1 # use `mysqltest1`; drop procedure foo2
master-bin.000001 # Query 1 # use `mysqltest1`; drop procedure foo3
-master-bin.000001 # Query 1 # use `mysqltest1`; create function fn1(x int)
+master-bin.000001 # Query 1 # use `mysqltest1`; CREATE DEFINER=`root`@`localhost` function fn1(x int)
returns int
deterministic
begin
@@ -332,7 +332,7 @@ master-bin.000001 # Query 1 # use `mysqltest1`; delete t1,t2 from t1,t2
master-bin.000001 # Query 1 # use `mysqltest1`; SELECT `fn1`(20)
master-bin.000001 # Query 1 # use `mysqltest1`; insert into t2 values(fn1(21))
master-bin.000001 # Query 1 # use `mysqltest1`; drop function fn1
-master-bin.000001 # Query 1 # use `mysqltest1`; create function fn1()
+master-bin.000001 # Query 1 # use `mysqltest1`; CREATE DEFINER=`root`@`localhost` function fn1()
returns int
no sql
begin
@@ -340,13 +340,13 @@ return unix_timestamp();
end
master-bin.000001 # Query 1 # use `mysqltest1`; delete from t1
master-bin.000001 # Query 1 # use `mysqltest1`; insert into t1 values(fn1())
-master-bin.000001 # Query 1 # use `mysqltest1`; create function fn2()
+master-bin.000001 # Query 1 # use `mysqltest1`; CREATE DEFINER=`zedjzlcsjhd`@`127.0.0.1` function fn2()
returns int
no sql
begin
return unix_timestamp();
end
-master-bin.000001 # Query 1 # use `mysqltest1`; create function fn3()
+master-bin.000001 # Query 1 # use `mysqltest1`; CREATE DEFINER=`root`@`localhost` function fn3()
returns int
not deterministic
reads sql data
@@ -356,7 +356,7 @@ end
master-bin.000001 # Query 1 # use `mysqltest1`; delete from t2
master-bin.000001 # Query 1 # use `mysqltest1`; alter table t2 add unique (a)
master-bin.000001 # Query 1 # use `mysqltest1`; drop function fn1
-master-bin.000001 # Query 1 # use `mysqltest1`; create function fn1(x int)
+master-bin.000001 # Query 1 # use `mysqltest1`; CREATE DEFINER=`root`@`localhost` function fn1(x int)
returns int
begin
insert into t2 values(x),(x);
diff --git a/mysql-test/r/rpl_trigger.result b/mysql-test/r/rpl_trigger.result
index ccd880c1e07..b9b20b82acd 100644
--- a/mysql-test/r/rpl_trigger.result
+++ b/mysql-test/r/rpl_trigger.result
@@ -102,7 +102,7 @@ t1_first root@localhost
SELECT routine_name, definer
FROM information_schema.routines;
routine_name definer
-bug12480 @
+bug12480 root@localhost
SELECT trigger_name, definer
FROM information_schema.triggers;
trigger_name definer
@@ -855,3 +855,44 @@ f3
drop trigger trg11;
drop table t21,t31;
drop table t11;
+STOP SLAVE;
+FLUSH LOGS;
+RESET SLAVE;
+START SLAVE;
+SELECT MASTER_POS_WAIT('master-bin.000001', 513) >= 0;
+MASTER_POS_WAIT('master-bin.000001', 513) >= 0
+1
+SHOW TABLES;
+Tables_in_test
+t1
+t2
+SHOW TRIGGERS;
+Trigger Event Table Statement Timing Created sql_mode Definer
+trg1 INSERT t1 INSERT INTO t2 VALUES(CURRENT_USER()) AFTER NULL
+SELECT * FROM t1;
+c
+1
+SELECT * FROM t2;
+s
+@
+INSERT INTO t1 VALUES(2);
+SELECT * FROM t1;
+c
+1
+2
+SELECT * FROM t2;
+s
+@
+root@localhost
+DROP TRIGGER trg1;
+Warnings:
+Warning 1454 No definer attribute for trigger 'test'.'trg1'. The trigger will be activated under the authorization of the invoker, which may have insufficient privileges. Please recreate the trigger.
+DROP TABLE t1;
+DROP TABLE t2;
+STOP SLAVE;
+RESET SLAVE;
+SHOW TABLES;
+Tables_in_test
+SHOW TRIGGERS;
+Trigger Event Table Statement Timing Created sql_mode Definer
+RESET MASTER;
diff --git a/mysql-test/r/skip_grants.result b/mysql-test/r/skip_grants.result
index 95464ee4ce5..6714fa9d217 100644
--- a/mysql-test/r/skip_grants.result
+++ b/mysql-test/r/skip_grants.result
@@ -2,10 +2,6 @@ drop table if exists t1,v1;
drop view if exists t1,v1;
drop procedure if exists f1;
use test;
-create table t1 (field1 INT);
-CREATE VIEW v1 AS SELECT field1 FROM t1;
-ERROR HY000: Definer is not fully qualified
-drop table t1;
create procedure f1() select 1;
drop procedure f1;
create table t1 (a int);
@@ -15,3 +11,20 @@ drop table t1;
drop function if exists f1;
Warnings:
Note 1305 FUNCTION f1 does not exist
+DROP VIEW IF EXISTS v1;
+DROP VIEW IF EXISTS v2;
+DROP TABLE IF EXISTS t1;
+CREATE TABLE t1(c INT);
+CREATE TRIGGER t1_bi BEFORE INSERT ON t1
+FOR EACH ROW
+SET @a = 1;
+CREATE VIEW v1 AS SELECT * FROM t1;
+CREATE DEFINER=a@b TRIGGER ti_ai AFTER INSERT ON t1
+FOR EACH ROW
+SET @b = 1;
+CREATE DEFINER=a@b VIEW v2 AS SELECT * FROM t1;
+DROP TRIGGER t1_bi;
+DROP TRIGGER ti_ai;
+DROP VIEW v1;
+DROP VIEW v2;
+DROP TABLE t1;
diff --git a/mysql-test/r/sp-security.result b/mysql-test/r/sp-security.result
index d8abc387677..896b6fa572c 100644
--- a/mysql-test/r/sp-security.result
+++ b/mysql-test/r/sp-security.result
@@ -323,3 +323,99 @@ Warning 1287 'SHOW INNODB STATUS' is deprecated; use 'SHOW ENGINE INNODB STATUS'
GRANT EXECUTE ON PROCEDURE p1 TO user_bug7787@localhost;
DROP DATABASE db_bug7787;
use test;
+
+---> connection: root
+DROP DATABASE IF EXISTS mysqltest;
+CREATE DATABASE mysqltest;
+CREATE USER mysqltest_1@localhost;
+GRANT ALL PRIVILEGES ON mysqltest.* TO mysqltest_1@localhost;
+CREATE USER mysqltest_2@localhost;
+GRANT SUPER ON *.* TO mysqltest_2@localhost;
+GRANT ALL PRIVILEGES ON mysqltest.* TO mysqltest_2@localhost;
+
+---> connection: mysqltest_2_con
+use mysqltest;
+CREATE PROCEDURE wl2897_p1() SELECT 1;
+CREATE FUNCTION wl2897_f1() RETURNS INT RETURN 1;
+
+---> connection: mysqltest_1_con
+use mysqltest;
+CREATE DEFINER=root@localhost PROCEDURE wl2897_p2() SELECT 2;
+ERROR 42000: Access denied; you need the SUPER privilege for this operation
+CREATE DEFINER=root@localhost FUNCTION wl2897_f2() RETURNS INT RETURN 2;
+ERROR 42000: Access denied; you need the SUPER privilege for this operation
+
+---> connection: mysqltest_2_con
+use mysqltest;
+CREATE DEFINER='a @ b @ c'@localhost PROCEDURE wl2897_p3() SELECT 3;
+Warnings:
+Note 1449 There is no 'a @ b @ c'@'localhost' registered
+CREATE DEFINER='a @ b @ c'@localhost FUNCTION wl2897_f3() RETURNS INT RETURN 3;
+Warnings:
+Note 1449 There is no 'a @ b @ c'@'localhost' registered
+
+---> connection: con1root
+use mysqltest;
+SHOW CREATE PROCEDURE wl2897_p1;
+Procedure sql_mode Create Procedure
+wl2897_p1 CREATE DEFINER=`mysqltest_2`@`localhost` PROCEDURE `wl2897_p1`()
+SELECT 1
+SHOW CREATE PROCEDURE wl2897_p3;
+Procedure sql_mode Create Procedure
+wl2897_p3 CREATE DEFINER=`a @ b @ c`@`localhost` PROCEDURE `wl2897_p3`()
+SELECT 3
+SHOW CREATE FUNCTION wl2897_f1;
+Function sql_mode Create Function
+wl2897_f1 CREATE DEFINER=`mysqltest_2`@`localhost` FUNCTION `wl2897_f1`() RETURNS int(11)
+RETURN 1
+SHOW CREATE FUNCTION wl2897_f3;
+Function sql_mode Create Function
+wl2897_f3 CREATE DEFINER=`a @ b @ c`@`localhost` FUNCTION `wl2897_f3`() RETURNS int(11)
+RETURN 3
+DROP USER mysqltest_1@localhost;
+DROP USER mysqltest_2@localhost;
+DROP DATABASE mysqltest;
+
+---> connection: root
+DROP DATABASE IF EXISTS mysqltest;
+CREATE DATABASE mysqltest;
+CREATE USER mysqltest_1@localhost;
+GRANT ALL PRIVILEGES ON mysqltest.* TO mysqltest_1@localhost;
+CREATE USER mysqltest_2@localhost;
+GRANT ALL PRIVILEGES ON mysqltest.* TO mysqltest_2@localhost;
+
+---> connection: mysqltest_1_con
+use mysqltest;
+CREATE PROCEDURE bug13198_p1()
+SELECT 1;
+CREATE FUNCTION bug13198_f1() RETURNS INT
+RETURN 1;
+CALL bug13198_p1();
+1
+1
+SELECT bug13198_f1();
+bug13198_f1()
+1
+
+---> connection: mysqltest_2_con
+use mysqltest;
+CALL bug13198_p1();
+1
+1
+SELECT bug13198_f1();
+bug13198_f1()
+1
+
+---> connection: root
+DROP USER mysqltest_1@localhost;
+
+---> connection: mysqltest_2_con
+use mysqltest;
+CALL bug13198_p1();
+ERROR HY000: There is no 'mysqltest_1'@'localhost' registered
+SELECT bug13198_f1();
+ERROR HY000: There is no 'mysqltest_1'@'localhost' registered
+
+---> connection: root
+DROP USER mysqltest_2@localhost;
+DROP DATABASE mysqltest;
diff --git a/mysql-test/r/sp.result b/mysql-test/r/sp.result
index e6a22867db6..b03c49b72e7 100644
--- a/mysql-test/r/sp.result
+++ b/mysql-test/r/sp.result
@@ -788,7 +788,7 @@ comment 'Characteristics procedure test'
insert into t1 values ("chistics", 1)|
show create procedure chistics|
Procedure sql_mode Create Procedure
-chistics CREATE PROCEDURE `chistics`()
+chistics CREATE DEFINER=`root`@`localhost` PROCEDURE `chistics`()
MODIFIES SQL DATA
COMMENT 'Characteristics procedure test'
insert into t1 values ("chistics", 1)
@@ -800,7 +800,7 @@ delete from t1|
alter procedure chistics sql security invoker|
show create procedure chistics|
Procedure sql_mode Create Procedure
-chistics CREATE PROCEDURE `chistics`()
+chistics CREATE DEFINER=`root`@`localhost` PROCEDURE `chistics`()
MODIFIES SQL DATA
SQL SECURITY INVOKER
COMMENT 'Characteristics procedure test'
@@ -815,7 +815,7 @@ comment 'Characteristics procedure test'
return 42|
show create function chistics|
Function sql_mode Create Function
-chistics CREATE FUNCTION `chistics`() RETURNS int(11)
+chistics CREATE DEFINER=`root`@`localhost` FUNCTION `chistics`() RETURNS int(11)
DETERMINISTIC
SQL SECURITY INVOKER
COMMENT 'Characteristics procedure test'
@@ -828,7 +828,7 @@ no sql
comment 'Characteristics function test'|
show create function chistics|
Function sql_mode Create Function
-chistics CREATE FUNCTION `chistics`() RETURNS int(11)
+chistics CREATE DEFINER=`root`@`localhost` FUNCTION `chistics`() RETURNS int(11)
NO SQL
DETERMINISTIC
SQL SECURITY INVOKER
@@ -1287,7 +1287,7 @@ end while;
end|
show create procedure opp|
Procedure sql_mode Create Procedure
-opp CREATE PROCEDURE `opp`(n bigint unsigned, out pp bool)
+opp CREATE DEFINER=`root`@`localhost` PROCEDURE `opp`(n bigint unsigned, out pp bool)
begin
declare r double;
declare b, s bigint unsigned default 0;
@@ -1386,7 +1386,7 @@ alter procedure bar comment "3333333333"|
alter procedure bar|
show create procedure bar|
Procedure sql_mode Create Procedure
-bar CREATE PROCEDURE `bar`(x char(16), y int)
+bar CREATE DEFINER=`root`@`localhost` PROCEDURE `bar`(x char(16), y int)
COMMENT '3333333333'
insert into test.t1 values (x, y)
show procedure status like 'bar'|
@@ -1966,13 +1966,13 @@ Db Name Type Definer Modified Created Security_type Comment
test bug2267_4 FUNCTION root@localhost 0000-00-00 00:00:00 0000-00-00 00:00:00 DEFINER
call bug2267_3()|
Procedure sql_mode Create Procedure
-bug2267_1 CREATE PROCEDURE `bug2267_1`()
+bug2267_1 CREATE DEFINER=`root`@`localhost` PROCEDURE `bug2267_1`()
begin
show procedure status;
end
call bug2267_4()|
Function sql_mode Create Function
-bug2267_4 CREATE FUNCTION `bug2267_4`() RETURNS int(11)
+bug2267_4 CREATE DEFINER=`root`@`localhost` FUNCTION `bug2267_4`() RETURNS int(11)
return 100
drop procedure bug2267_1|
drop procedure bug2267_2|
@@ -2333,20 +2333,20 @@ return x || y$
set @@sql_mode = ''|
show create procedure bug2564_1|
Procedure sql_mode Create Procedure
-bug2564_1 CREATE PROCEDURE `bug2564_1`()
+bug2564_1 CREATE DEFINER=`root`@`localhost` PROCEDURE `bug2564_1`()
COMMENT 'Joe''s procedure'
insert into `t1` values ("foo", 1)
show create procedure bug2564_2|
Procedure sql_mode Create Procedure
-bug2564_2 ANSI_QUOTES CREATE PROCEDURE "bug2564_2"()
+bug2564_2 ANSI_QUOTES CREATE DEFINER="root"@"localhost" PROCEDURE "bug2564_2"()
insert into "t1" values ('foo', 1)
show create function bug2564_3|
Function sql_mode Create Function
-bug2564_3 CREATE FUNCTION `bug2564_3`(x int, y int) RETURNS int(11)
+bug2564_3 CREATE DEFINER=`root`@`localhost` FUNCTION `bug2564_3`(x int, y int) RETURNS int(11)
return x || y
show create function bug2564_4|
Function sql_mode Create Function
-bug2564_4 REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ANSI CREATE FUNCTION "bug2564_4"(x int, y int) RETURNS int(11)
+bug2564_4 REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ANSI CREATE DEFINER="root"@"localhost" FUNCTION "bug2564_4"(x int, y int) RETURNS int(11)
return x || y
drop procedure bug2564_1|
drop procedure bug2564_2|
@@ -4056,7 +4056,7 @@ return 42;
end */;;
show create function bug14723;;
Function sql_mode Create Function
-bug14723 CREATE FUNCTION `bug14723`() RETURNS bigint(20)
+bug14723 CREATE DEFINER=`root`@`localhost` FUNCTION `bug14723`() RETURNS bigint(20)
main_loop: begin
return 42;
end
@@ -4069,7 +4069,7 @@ select 42;
end */;;
show create procedure bug14723;;
Procedure sql_mode Create Procedure
-bug14723 CREATE PROCEDURE `bug14723`()
+bug14723 CREATE DEFINER=`root`@`localhost` PROCEDURE `bug14723`()
main_loop: begin
select 42;
end
diff --git a/mysql-test/r/sql_mode.result b/mysql-test/r/sql_mode.result
index 66df424919b..b05680f9c54 100644
--- a/mysql-test/r/sql_mode.result
+++ b/mysql-test/r/sql_mode.result
@@ -445,23 +445,23 @@ SET @@SQL_MODE='';
create function `foo` () returns int return 5;
show create function `foo`;
Function sql_mode Create Function
-foo CREATE FUNCTION `foo`() RETURNS int(11)
+foo CREATE DEFINER=`root`@`localhost` FUNCTION `foo`() RETURNS int(11)
return 5
SET @@SQL_MODE='ANSI_QUOTES';
show create function `foo`;
Function sql_mode Create Function
-foo CREATE FUNCTION `foo`() RETURNS int(11)
+foo CREATE DEFINER=`root`@`localhost` FUNCTION `foo`() RETURNS int(11)
return 5
drop function `foo`;
create function `foo` () returns int return 5;
show create function `foo`;
Function sql_mode Create Function
-foo ANSI_QUOTES CREATE FUNCTION "foo"() RETURNS int(11)
+foo ANSI_QUOTES CREATE DEFINER="root"@"localhost" FUNCTION "foo"() RETURNS int(11)
return 5
SET @@SQL_MODE='';
show create function `foo`;
Function sql_mode Create Function
-foo ANSI_QUOTES CREATE FUNCTION "foo"() RETURNS int(11)
+foo ANSI_QUOTES CREATE DEFINER="root"@"localhost" FUNCTION "foo"() RETURNS int(11)
return 5
drop function `foo`;
SET @@SQL_MODE='';
diff --git a/mysql-test/std_data/bug16266.000001 b/mysql-test/std_data/bug16266.000001
new file mode 100644
index 00000000000..1b24d231511
--- /dev/null
+++ b/mysql-test/std_data/bug16266.000001
Binary files differ
diff --git a/mysql-test/t/rpl_trigger.test b/mysql-test/t/rpl_trigger.test
index f3db1cb5841..90822e0654c 100644
--- a/mysql-test/t/rpl_trigger.test
+++ b/mysql-test/t/rpl_trigger.test
@@ -162,6 +162,7 @@ use test;
drop table t1,t2;
drop database other;
+
#
# Test specific triggers including SELECT into var with replication
# BUG#13227:
@@ -257,6 +258,79 @@ while ($rnd)
}
+#
+# BUG#16266: Definer is not fully qualified error during replication.
+#
+# The idea of this test is to emulate replication of a trigger from the old
+# master (master w/o "DEFINER in triggers" support) to the new slave and check
+# that:
+# 1. the trigger on the slave will be replicated w/o errors;
+# 2. the trigger on the slave will be non-SUID (will have no DEFINER);
+# 3. the trigger can be activated later on the slave w/o errors.
+#
+# In order to emulate this kind of replication, we make the slave playing the binlog,
+# recorded by 5.0.16 master. This binlog contains the following statements:
+# CREATE TABLE t1(c INT);
+# CREATE TABLE t2(s CHAR(200));
+# CREATE TRIGGER trg1 AFTER INSERT ON t1
+# FOR EACH ROW
+# INSERT INTO t2 VALUES(CURRENT_USER());
+# INSERT INTO t1 VALUES(1);
+#
+
+# 1. Check that the trigger's replication is succeeded.
+
+# Stop the slave.
+
+connection slave;
+STOP SLAVE;
+
+# Replace master's binlog.
+
+connection master;
+FLUSH LOGS;
+exec cp $MYSQL_TEST_DIR/std_data/bug16266.000001 $MYSQLTEST_VARDIR/log/master-bin.000001;
+
+# Make the slave to replay the new binlog.
+
+connection slave;
+RESET SLAVE;
+START SLAVE;
+
+SELECT MASTER_POS_WAIT('master-bin.000001', 513) >= 0;
+
+# Check that the replication succeeded.
+
+SHOW TABLES;
+SHOW TRIGGERS;
+SELECT * FROM t1;
+SELECT * FROM t2;
+
+# 2. Check that the trigger is non-SUID on the slave;
+# 3. Check that the trigger can be activated on the slave.
+
+INSERT INTO t1 VALUES(2);
+
+SELECT * FROM t1;
+SELECT * FROM t2;
+
+# That's all, cleanup.
+
+DROP TRIGGER trg1;
+DROP TABLE t1;
+DROP TABLE t2;
+
+STOP SLAVE;
+RESET SLAVE;
+
+# The master should be clean.
+
+connection master;
+SHOW TABLES;
+SHOW TRIGGERS;
+
+RESET MASTER;
+
#
# End of tests
diff --git a/mysql-test/t/skip_grants.test b/mysql-test/t/skip_grants.test
index 27220c9b507..93e052d8c71 100644
--- a/mysql-test/t/skip_grants.test
+++ b/mysql-test/t/skip_grants.test
@@ -9,14 +9,6 @@ drop procedure if exists f1;
use test;
#
-# test that we can create VIEW if privileges check switched off
-#
-create table t1 (field1 INT);
--- error ER_MALFORMED_DEFINER
-CREATE VIEW v1 AS SELECT field1 FROM t1;
-drop table t1;
-
-#
# Test that we can create and drop procedure without warnings
# see bug#9993
#
@@ -33,3 +25,48 @@ drop table t1;
# BUG#17595: DROP FUNCTION IF EXISTS f1 crashes server
drop function if exists f1;
+
+#
+# BUG#16777: Can not create trigger nor view w/o definer if --skip-grant-tables
+# specified
+#
+# Also, a test that we can create VIEW if privileges check switched off has
+# been moved here.
+#
+
+# Prepare.
+
+--disable_warnings
+
+DROP VIEW IF EXISTS v1;
+DROP VIEW IF EXISTS v2;
+
+DROP TABLE IF EXISTS t1;
+
+--enable_warnings
+
+# Test case.
+
+CREATE TABLE t1(c INT);
+
+CREATE TRIGGER t1_bi BEFORE INSERT ON t1
+ FOR EACH ROW
+ SET @a = 1;
+
+CREATE VIEW v1 AS SELECT * FROM t1;
+
+CREATE DEFINER=a@b TRIGGER ti_ai AFTER INSERT ON t1
+ FOR EACH ROW
+ SET @b = 1;
+
+CREATE DEFINER=a@b VIEW v2 AS SELECT * FROM t1;
+
+# Cleanup.
+
+DROP TRIGGER t1_bi;
+DROP TRIGGER ti_ai;
+
+DROP VIEW v1;
+DROP VIEW v2;
+
+DROP TABLE t1;
diff --git a/mysql-test/t/sp-security.test b/mysql-test/t/sp-security.test
index 19f94a32d9c..f369dc64b0e 100644
--- a/mysql-test/t/sp-security.test
+++ b/mysql-test/t/sp-security.test
@@ -547,4 +547,200 @@ GRANT EXECUTE ON PROCEDURE p1 TO user_bug7787@localhost;
DROP DATABASE db_bug7787;
use test;
+
+#
+# WL#2897: Complete definer support in the stored routines.
+#
+# The following cases are tested:
+# 1. check that if DEFINER-clause is not explicitly specified, stored routines
+# are created with CURRENT_USER privileges;
+# 2. check that if DEFINER-clause specifies non-current user, SUPER privilege
+# is required to create a stored routine;
+# 3. check that if DEFINER-clause specifies non-existent user, a warning is
+# emitted.
+# 4. check that SHOW CREATE PROCEDURE | FUNCTION works correctly;
+#
+# The following cases are tested in other test suites:
+# - check that mysqldump dumps new attribute correctly;
+# - check that slave replicates CREATE-statements with explicitly specified
+# DEFINER correctly.
+#
+
+# Setup the environment.
+
+--echo
+--echo ---> connection: root
+--connection con1root
+
+--disable_warnings
+DROP DATABASE IF EXISTS mysqltest;
+--enable_warnings
+
+CREATE DATABASE mysqltest;
+
+CREATE USER mysqltest_1@localhost;
+GRANT ALL PRIVILEGES ON mysqltest.* TO mysqltest_1@localhost;
+
+CREATE USER mysqltest_2@localhost;
+GRANT SUPER ON *.* TO mysqltest_2@localhost;
+GRANT ALL PRIVILEGES ON mysqltest.* TO mysqltest_2@localhost;
+
+--connect (mysqltest_2_con,localhost,mysqltest_2,,mysqltest)
+--connect (mysqltest_1_con,localhost,mysqltest_1,,mysqltest)
+
+# test case (1).
+
+--echo
+--echo ---> connection: mysqltest_2_con
+--connection mysqltest_2_con
+
+use mysqltest;
+
+CREATE PROCEDURE wl2897_p1() SELECT 1;
+
+CREATE FUNCTION wl2897_f1() RETURNS INT RETURN 1;
+
+# test case (2).
+
+--echo
+--echo ---> connection: mysqltest_1_con
+--connection mysqltest_1_con
+
+use mysqltest;
+
+--error ER_SPECIFIC_ACCESS_DENIED_ERROR
+CREATE DEFINER=root@localhost PROCEDURE wl2897_p2() SELECT 2;
+
+--error ER_SPECIFIC_ACCESS_DENIED_ERROR
+CREATE DEFINER=root@localhost FUNCTION wl2897_f2() RETURNS INT RETURN 2;
+
+# test case (3).
+
+--echo
+--echo ---> connection: mysqltest_2_con
+--connection mysqltest_2_con
+
+use mysqltest;
+
+CREATE DEFINER='a @ b @ c'@localhost PROCEDURE wl2897_p3() SELECT 3;
+
+CREATE DEFINER='a @ b @ c'@localhost FUNCTION wl2897_f3() RETURNS INT RETURN 3;
+
+# test case (4).
+
+--echo
+--echo ---> connection: con1root
+--connection con1root
+
+use mysqltest;
+
+SHOW CREATE PROCEDURE wl2897_p1;
+SHOW CREATE PROCEDURE wl2897_p3;
+
+SHOW CREATE FUNCTION wl2897_f1;
+SHOW CREATE FUNCTION wl2897_f3;
+
+# Cleanup.
+
+DROP USER mysqltest_1@localhost;
+DROP USER mysqltest_2@localhost;
+
+DROP DATABASE mysqltest;
+
+--disconnect mysqltest_1_con
+--disconnect mysqltest_2_con
+
+
+#
+# BUG#13198: SP executes if definer does not exist
+#
+
+# Prepare environment.
+
+--echo
+--echo ---> connection: root
+--connection con1root
+
+--disable_warnings
+DROP DATABASE IF EXISTS mysqltest;
+--enable_warnings
+
+CREATE DATABASE mysqltest;
+
+CREATE USER mysqltest_1@localhost;
+GRANT ALL PRIVILEGES ON mysqltest.* TO mysqltest_1@localhost;
+
+CREATE USER mysqltest_2@localhost;
+GRANT ALL PRIVILEGES ON mysqltest.* TO mysqltest_2@localhost;
+
+--connect (mysqltest_1_con,localhost,mysqltest_1,,mysqltest)
+--connect (mysqltest_2_con,localhost,mysqltest_2,,mysqltest)
+
+# Create a procedure/function under u1.
+
+--echo
+--echo ---> connection: mysqltest_1_con
+--connection mysqltest_1_con
+
+use mysqltest;
+
+CREATE PROCEDURE bug13198_p1()
+ SELECT 1;
+
+CREATE FUNCTION bug13198_f1() RETURNS INT
+ RETURN 1;
+
+CALL bug13198_p1();
+
+SELECT bug13198_f1();
+
+# Check that u2 can call the procedure/function.
+
+--echo
+--echo ---> connection: mysqltest_2_con
+--connection mysqltest_2_con
+
+use mysqltest;
+
+CALL bug13198_p1();
+
+SELECT bug13198_f1();
+
+# Drop user u1 (definer of the object);
+
+--echo
+--echo ---> connection: root
+--connection con1root
+
+--disconnect mysqltest_1_con
+
+DROP USER mysqltest_1@localhost;
+
+# Check that u2 can not call the procedure/function.
+
+--echo
+--echo ---> connection: mysqltest_2_con
+--connection mysqltest_2_con
+
+use mysqltest;
+
+--error ER_NO_SUCH_USER
+CALL bug13198_p1();
+
+--error ER_NO_SUCH_USER
+SELECT bug13198_f1();
+
+# Cleanup.
+
+--echo
+--echo ---> connection: root
+--connection con1root
+
+--disconnect mysqltest_2_con
+
+DROP USER mysqltest_2@localhost;
+
+DROP DATABASE mysqltest;
+
+
# End of 5.0 bugs.
View Lorry Controller Status