path: root/mysql-test/suite/roles/definer.result

create database mysqltest1;
use mysqltest1;
create table t1 (a int, b int, c int);
insert t1 values (1,10,100),(2,20,200);
create role role1;
grant select (a) on mysqltest1.t1 to role1;
grant event,execute,trigger on mysqltest1.* to role1;
grant role1 to current_user;
create role role2;
grant insert,select on mysqltest1.t1 to role2;
grant event,execute,trigger on mysqltest1.* to role2;
create user foo@localhost;
grant create view on mysqltest1.* to foo@localhost;
create role role4;
grant select on mysqltest1.t1 to role4;
grant role4 to foo@localhost;
create definer=current_role view test.v1 as select a+b,c from t1;
ERROR 0L000: Invalid definer
set role role1;
create definer=current_role view test.v1 as select a+b,c from t1;
show create view test.v1;
View	Create View	character_set_client	collation_connection
v1	CREATE ALGORITHM=UNDEFINED DEFINER=`role1` SQL SECURITY DEFINER VIEW `test`.`v1` AS select `mysqltest1`.`t1`.`a` + `mysqltest1`.`t1`.`b` AS `a+b`,`mysqltest1`.`t1`.`c` AS `c` from `mysqltest1`.`t1`	latin1	latin1_swedish_ci
set role none;
create definer=role2 view test.v2 as select a+b,c,current_role() from t1;
show create view test.v2;
View	Create View	character_set_client	collation_connection
v2	CREATE ALGORITHM=UNDEFINED DEFINER=`role2` SQL SECURITY DEFINER VIEW `test`.`v2` AS select `mysqltest1`.`t1`.`a` + `mysqltest1`.`t1`.`b` AS `a+b`,`mysqltest1`.`t1`.`c` AS `c`,current_role() AS `current_role()` from `mysqltest1`.`t1`	latin1	latin1_swedish_ci
create definer=role3 view test.v3 as select a+b,c from t1;
Warnings:
Note	1449	The user specified as a definer ('role3'@'%') does not exist
show create view test.v3;
View	Create View	character_set_client	collation_connection
v3	CREATE ALGORITHM=UNDEFINED DEFINER=`role3`@`%` SQL SECURITY DEFINER VIEW `test`.`v3` AS select `mysqltest1`.`t1`.`a` + `mysqltest1`.`t1`.`b` AS `a+b`,`mysqltest1`.`t1`.`c` AS `c` from `mysqltest1`.`t1`	latin1	latin1_swedish_ci
Warnings:
Note	1449	The user specified as a definer ('role3'@'%') does not exist
connect  c1, localhost, foo,,mysqltest1;
connection c1;
show grants;
Grants for foo@localhost
GRANT `role4` TO `foo`@`localhost`
GRANT USAGE ON *.* TO `foo`@`localhost`
GRANT CREATE VIEW ON `mysqltest1`.* TO `foo`@`localhost`
select * from test.v1;
ERROR HY000: View 'test.v1' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
select * from test.v2;
a+b	c	current_role()
11	100	role2
22	200	role2
select * from test.v3;
ERROR 28000: Access denied for user 'foo'@'localhost' (using password: NO)
create definer=role4 view test.v4 as select a+b,c from t1;
ERROR 42000: ANY command denied to user 'foo'@'localhost' for table 't1'
select * from t1;
ERROR 42000: SELECT command denied to user 'foo'@'localhost' for table 't1'
set role role4;
select * from t1;
a	b	c
1	10	100
2	20	200
create view test.v4 as select a+b,c from t1;
create definer=role4 view test.v5 as select a+b,c from t1;
select * from test.v4;
ERROR HY000: View 'test.v4' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
select * from test.v5;
a+b	c
11	100
22	200
set role none;
select * from test.v4;
ERROR HY000: View 'test.v4' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
select * from test.v5;
a+b	c
11	100
22	200
connection default;
drop role role4;
show create view test.v5;
View	Create View	character_set_client	collation_connection
v5	CREATE ALGORITHM=UNDEFINED DEFINER=`role4` SQL SECURITY DEFINER VIEW `test`.`v5` AS select `mysqltest1`.`t1`.`a` + `mysqltest1`.`t1`.`b` AS `a+b`,`mysqltest1`.`t1`.`c` AS `c` from `mysqltest1`.`t1`	latin1	latin1_swedish_ci
Warnings:
Note	1449	The user specified as a definer ('role4'@'') does not exist
select * from test.v5;
ERROR HY000: The user specified as a definer ('role4'@'') does not exist
create user role4;
grant select on mysqltest1.t1 to role4;
show create view test.v5;
View	Create View	character_set_client	collation_connection
v5	CREATE ALGORITHM=UNDEFINED DEFINER=`role4` SQL SECURITY DEFINER VIEW `test`.`v5` AS select `mysqltest1`.`t1`.`a` + `mysqltest1`.`t1`.`b` AS `a+b`,`mysqltest1`.`t1`.`c` AS `c` from `mysqltest1`.`t1`	latin1	latin1_swedish_ci
Warnings:
Note	1449	The user specified as a definer ('role4'@'') does not exist
select * from test.v5;
ERROR HY000: The user specified as a definer ('role4'@'') does not exist
flush tables;
show create view test.v5;
View	Create View	character_set_client	collation_connection
v5	CREATE ALGORITHM=UNDEFINED DEFINER=`role4`@`%` SQL SECURITY DEFINER VIEW `test`.`v5` AS select `mysqltest1`.`t1`.`a` + `mysqltest1`.`t1`.`b` AS `a+b`,`mysqltest1`.`t1`.`c` AS `c` from `mysqltest1`.`t1`	latin1	latin1_swedish_ci
select * from test.v5;
a+b	c
11	100
22	200
drop user role4;
create table t2 select * from t1;
create definer=current_role trigger tr1 before insert on t2 for each row
insert t1 values (111, 222, 333);
ERROR 0L000: Invalid definer
set role role1;
create definer=current_role trigger tr1 before insert on t2 for each row
insert t1 values (111, 222, 333);
show create trigger tr1;
Trigger	sql_mode	SQL Original Statement	character_set_client	collation_connection	Database Collation	Created
tr1	STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION	CREATE DEFINER=`role1` trigger tr1 before insert on t2 for each row
insert t1 values (111, 222, 333)	latin1	latin1_swedish_ci	latin1_swedish_ci	#
set role none;
insert t2 values (11,22,33);
ERROR 42000: INSERT command denied to user ''@'' for table 't1'
select * from t1;
a	b	c
1	10	100
2	20	200
select * from t2;
a	b	c
1	10	100
2	20	200
create definer=role2 trigger tr2 before delete on t2 for each row
insert t1 values (111, 222, 333);
show create trigger tr2;
Trigger	sql_mode	SQL Original Statement	character_set_client	collation_connection	Database Collation	Created
tr2	STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION	CREATE DEFINER=`role2` trigger tr2 before delete on t2 for each row
insert t1 values (111, 222, 333)	latin1	latin1_swedish_ci	latin1_swedish_ci	#
delete from t2 where a=1;
select * from t1;
a	b	c
1	10	100
2	20	200
111	222	333
select * from t2;
a	b	c
2	20	200
delete from t1 where a=111;
create definer=role3 trigger tr3 before update on t2 for each row
insert t1 values (111, 222, 333);
Warnings:
Note	1449	The user specified as a definer ('role3'@'%') does not exist
show create trigger tr3;
Trigger	sql_mode	SQL Original Statement	character_set_client	collation_connection	Database Collation	Created
tr3	STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION	CREATE DEFINER=`role3`@`%` trigger tr3 before update on t2 for each row
insert t1 values (111, 222, 333)	latin1	latin1_swedish_ci	latin1_swedish_ci	#
update t2 set b=2 where a=2;
ERROR HY000: The user specified as a definer ('role3'@'%') does not exist
select * from t1;
a	b	c
1	10	100
2	20	200
select * from t2;
a	b	c
2	20	200
flush tables;
show create trigger tr2;
Trigger	sql_mode	SQL Original Statement	character_set_client	collation_connection	Database Collation	Created
tr2	STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION	CREATE DEFINER=`role2`@`` trigger tr2 before delete on t2 for each row
insert t1 values (111, 222, 333)	latin1	latin1_swedish_ci	latin1_swedish_ci	#
delete from t2 where a=2;
ERROR HY000: The user specified as a definer ('role2'@'%') does not exist
select * from t1;
a	b	c
1	10	100
2	20	200
select * from t2;
a	b	c
2	20	200
create definer=current_role procedure pr1() insert t1 values (111, 222, 333);
ERROR 0L000: Invalid definer
set role role1;
create definer=current_role procedure pr1() insert t1 values (111, 222, 333);
show create procedure pr1;
Procedure	sql_mode	Create Procedure	character_set_client	collation_connection	Database Collation
pr1	STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION	CREATE DEFINER=`role1` PROCEDURE `pr1`()
insert t1 values (111, 222, 333)	latin1	latin1_swedish_ci	latin1_swedish_ci
set role none;
call pr1();
ERROR 42000: INSERT command denied to user ''@'' for table 't1'
select * from t1;
a	b	c
1	10	100
2	20	200
create definer=role2 procedure pr2() insert t1 values (111, 222, 333);
show create procedure pr2;
Procedure	sql_mode	Create Procedure	character_set_client	collation_connection	Database Collation
pr2	STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION	CREATE DEFINER=`role2` PROCEDURE `pr2`()
insert t1 values (111, 222, 333)	latin1	latin1_swedish_ci	latin1_swedish_ci
call pr2();
select * from t1;
a	b	c
1	10	100
2	20	200
111	222	333
delete from t1 where a=111;
create definer=role3 procedure pr3() insert t1 values (111, 222, 333);
Warnings:
Note	1449	The user specified as a definer ('role3'@'%') does not exist
show create procedure pr3;
Procedure	sql_mode	Create Procedure	character_set_client	collation_connection	Database Collation
pr3	STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION	CREATE DEFINER=`role3`@`%` PROCEDURE `pr3`()
insert t1 values (111, 222, 333)	latin1	latin1_swedish_ci	latin1_swedish_ci
call pr3();
ERROR HY000: The user specified as a definer ('role3'@'%') does not exist
select * from t1;
a	b	c
1	10	100
2	20	200
update mysql.proc set definer='role2@' where definer='role2';
call pr2();
ERROR HY000: The user specified as a definer ('role2'@'%') does not exist
create definer=current_role function fn1() returns int return (select sum(a+b) from t1);
ERROR 0L000: Invalid definer
set role role1;
create definer=current_role function fn1() returns int return (select sum(a+b) from t1);
show create function fn1;
Function	sql_mode	Create Function	character_set_client	collation_connection	Database Collation
fn1	STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION	CREATE DEFINER=`role1` FUNCTION `fn1`() RETURNS int(11)
return (select sum(a+b) from t1)	latin1	latin1_swedish_ci	latin1_swedish_ci
set role none;
select fn1();
ERROR 42000: SELECT command denied to user ''@'' for column 'b' in table 't1'
select * from t1;
a	b	c
1	10	100
2	20	200
create definer=role2 function fn2() returns int return (select sum(a+b) from t1);
show create function fn2;
Function	sql_mode	Create Function	character_set_client	collation_connection	Database Collation
fn2	STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION	CREATE DEFINER=`role2` FUNCTION `fn2`() RETURNS int(11)
return (select sum(a+b) from t1)	latin1	latin1_swedish_ci	latin1_swedish_ci
select fn2();
fn2()
33
create definer=role3 function fn3() returns int return (select sum(a+b) from t1);
Warnings:
Note	1449	The user specified as a definer ('role3'@'%') does not exist
show create function fn3;
Function	sql_mode	Create Function	character_set_client	collation_connection	Database Collation
fn3	STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION	CREATE DEFINER=`role3`@`%` FUNCTION `fn3`() RETURNS int(11)
return (select sum(a+b) from t1)	latin1	latin1_swedish_ci	latin1_swedish_ci
select fn3();
ERROR HY000: The user specified as a definer ('role3'@'%') does not exist
set global event_scheduler=on;
create definer=current_role event e1 on schedule every 1 second starts '2000-01-01' do
insert t1 values (111, 1, 0);
ERROR 0L000: Invalid definer
set role role1;
create definer=current_role event e1 on schedule every 1 second starts '2000-01-01' do
insert t1 values (111, 2, 0);
show create event e1;
Event	sql_mode	time_zone	Create Event	character_set_client	collation_connection	Database Collation
e1	STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION	SYSTEM	CREATE DEFINER=`role1` EVENT `e1` ON SCHEDULE EVERY 1 SECOND STARTS '2000-01-01 00:00:00' ON COMPLETION NOT PRESERVE ENABLE DO insert t1 values (111, 2, 0)	latin1	latin1_swedish_ci	latin1_swedish_ci
set role none;
create definer=role3 event e3 on schedule every 1 second starts '2000-01-01' do
insert t1 values (111, 3, 0);
Warnings:
Note	1449	The user specified as a definer ('role3'@'%') does not exist
show create event e3;
Event	sql_mode	time_zone	Create Event	character_set_client	collation_connection	Database Collation
e3	STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION	SYSTEM	CREATE DEFINER=`role3`@`%` EVENT `e3` ON SCHEDULE EVERY 1 SECOND STARTS '2000-01-01 00:00:00' ON COMPLETION NOT PRESERVE ENABLE DO insert t1 values (111, 3, 0)	latin1	latin1_swedish_ci	latin1_swedish_ci
create definer=role2 event e2 on schedule every 1 second starts '2000-01-01' do
insert t1 values (111, 4, 0);
show create event e2;
Event	sql_mode	time_zone	Create Event	character_set_client	collation_connection	Database Collation
e2	STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION	SYSTEM	CREATE DEFINER=`role2` EVENT `e2` ON SCHEDULE EVERY 1 SECOND STARTS '2000-01-01 00:00:00' ON COMPLETION NOT PRESERVE ENABLE DO insert t1 values (111, 4, 0)	latin1	latin1_swedish_ci	latin1_swedish_ci
set global event_scheduler=off;
select distinct * from t1;
a	b	c
1	10	100
111	4	0
2	20	200
delete from t1 where a=111;

CREATE DATABASE /*!32312 IF NOT EXISTS*/ `test` /*!40100 DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci */;

USE `test`;
SET @saved_cs_client     = @@character_set_client;
SET character_set_client = utf8;
/*!50001 CREATE VIEW `v1` AS SELECT
 1 AS `a+b`,
  1 AS `c` */;
SET character_set_client = @saved_cs_client;
SET @saved_cs_client     = @@character_set_client;
SET character_set_client = utf8;
/*!50001 CREATE VIEW `v2` AS SELECT
 1 AS `a+b`,
  1 AS `c`,
  1 AS `current_role()` */;
SET character_set_client = @saved_cs_client;
SET @saved_cs_client     = @@character_set_client;
SET character_set_client = utf8;
/*!50001 CREATE VIEW `v3` AS SELECT
 1 AS `a+b`,
  1 AS `c` */;
SET character_set_client = @saved_cs_client;
SET @saved_cs_client     = @@character_set_client;
SET character_set_client = utf8;
/*!50001 CREATE VIEW `v4` AS SELECT
 1 AS `a+b`,
  1 AS `c` */;
SET character_set_client = @saved_cs_client;
SET @saved_cs_client     = @@character_set_client;
SET character_set_client = utf8;
/*!50001 CREATE VIEW `v5` AS SELECT
 1 AS `a+b`,
  1 AS `c` */;
SET character_set_client = @saved_cs_client;

CREATE DATABASE /*!32312 IF NOT EXISTS*/ `mysqltest1` /*!40100 DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci */;

USE `mysqltest1`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `t1` (
  `a` int(11) DEFAULT NULL,
  `b` int(11) DEFAULT NULL,
  `c` int(11) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
INSERT INTO `t1` VALUES (1,10,100),(2,20,200);
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `t2` (
  `a` int(11) DEFAULT NULL,
  `b` int(11) DEFAULT NULL,
  `c` int(11) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
INSERT INTO `t2` VALUES (2,20,200);
/*!50003 SET @saved_cs_client      = @@character_set_client */ ;
/*!50003 SET @saved_cs_results     = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
/*!50003 SET character_set_client  = latin1 */ ;
/*!50003 SET character_set_results = latin1 */ ;
/*!50003 SET collation_connection  = latin1_swedish_ci */ ;
/*!50003 SET @saved_sql_mode       = @@sql_mode */ ;
/*!50003 SET sql_mode              = 'STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' */ ;
DELIMITER ;;
/*!50003 CREATE*/ /*!50017 DEFINER=`role1`*/ /*!50003 trigger tr1 before insert on t2 for each row
insert t1 values (111, 222, 333) */;;
DELIMITER ;
/*!50003 SET sql_mode              = @saved_sql_mode */ ;
/*!50003 SET character_set_client  = @saved_cs_client */ ;
/*!50003 SET character_set_results = @saved_cs_results */ ;
/*!50003 SET collation_connection  = @saved_col_connection */ ;
/*!50003 SET @saved_cs_client      = @@character_set_client */ ;
/*!50003 SET @saved_cs_results     = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
/*!50003 SET character_set_client  = latin1 */ ;
/*!50003 SET character_set_results = latin1 */ ;
/*!50003 SET collation_connection  = latin1_swedish_ci */ ;
/*!50003 SET @saved_sql_mode       = @@sql_mode */ ;
/*!50003 SET sql_mode              = 'STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' */ ;
DELIMITER ;;
/*!50003 CREATE*/ /*!50017 DEFINER=`role3`@`%`*/ /*!50003 trigger tr3 before update on t2 for each row
insert t1 values (111, 222, 333) */;;
DELIMITER ;
/*!50003 SET sql_mode              = @saved_sql_mode */ ;
/*!50003 SET character_set_client  = @saved_cs_client */ ;
/*!50003 SET character_set_results = @saved_cs_results */ ;
/*!50003 SET collation_connection  = @saved_col_connection */ ;
/*!50003 SET @saved_cs_client      = @@character_set_client */ ;
/*!50003 SET @saved_cs_results     = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
/*!50003 SET character_set_client  = latin1 */ ;
/*!50003 SET character_set_results = latin1 */ ;
/*!50003 SET collation_connection  = latin1_swedish_ci */ ;
/*!50003 SET @saved_sql_mode       = @@sql_mode */ ;
/*!50003 SET sql_mode              = 'STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' */ ;
DELIMITER ;;
/*!50003 CREATE*/ /*!50017 DEFINER=`role2`@``*/ /*!50003 trigger tr2 before delete on t2 for each row
insert t1 values (111, 222, 333) */;;
DELIMITER ;
/*!50003 SET sql_mode              = @saved_sql_mode */ ;
/*!50003 SET character_set_client  = @saved_cs_client */ ;
/*!50003 SET character_set_results = @saved_cs_results */ ;
/*!50003 SET collation_connection  = @saved_col_connection */ ;
/*!50106 SET @save_time_zone= @@TIME_ZONE */ ;
DELIMITER ;;
/*!50003 SET @saved_cs_client      = @@character_set_client */ ;;
/*!50003 SET @saved_cs_results     = @@character_set_results */ ;;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;;
/*!50003 SET character_set_client  = latin1 */ ;;
/*!50003 SET character_set_results = latin1 */ ;;
/*!50003 SET collation_connection  = latin1_swedish_ci */ ;;
/*!50003 SET @saved_sql_mode       = @@sql_mode */ ;;
/*!50003 SET sql_mode              = 'STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' */ ;;
/*!50003 SET @saved_time_zone      = @@time_zone */ ;;
/*!50003 SET time_zone             = 'SYSTEM' */ ;;
/*!50106 CREATE*/ /*!50117 DEFINER=`role1`*/ /*!50106 EVENT `e1` ON SCHEDULE EVERY 1 SECOND STARTS '2000-01-01 00:00:00' ON COMPLETION NOT PRESERVE ENABLE DO insert t1 values (111, 2, 0) */ ;;
/*!50003 SET time_zone             = @saved_time_zone */ ;;
/*!50003 SET sql_mode              = @saved_sql_mode */ ;;
/*!50003 SET character_set_client  = @saved_cs_client */ ;;
/*!50003 SET character_set_results = @saved_cs_results */ ;;
/*!50003 SET collation_connection  = @saved_col_connection */ ;;
DELIMITER ;;
/*!50003 SET @saved_cs_client      = @@character_set_client */ ;;
/*!50003 SET @saved_cs_results     = @@character_set_results */ ;;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;;
/*!50003 SET character_set_client  = latin1 */ ;;
/*!50003 SET character_set_results = latin1 */ ;;
/*!50003 SET collation_connection  = latin1_swedish_ci */ ;;
/*!50003 SET @saved_sql_mode       = @@sql_mode */ ;;
/*!50003 SET sql_mode              = 'STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' */ ;;
/*!50003 SET @saved_time_zone      = @@time_zone */ ;;
/*!50003 SET time_zone             = 'SYSTEM' */ ;;
/*!50106 CREATE*/ /*!50117 DEFINER=`role2`*/ /*!50106 EVENT `e2` ON SCHEDULE EVERY 1 SECOND STARTS '2000-01-01 00:00:00' ON COMPLETION NOT PRESERVE ENABLE DO insert t1 values (111, 4, 0) */ ;;
/*!50003 SET time_zone             = @saved_time_zone */ ;;
/*!50003 SET sql_mode              = @saved_sql_mode */ ;;
/*!50003 SET character_set_client  = @saved_cs_client */ ;;
/*!50003 SET character_set_results = @saved_cs_results */ ;;
/*!50003 SET collation_connection  = @saved_col_connection */ ;;
DELIMITER ;;
/*!50003 SET @saved_cs_client      = @@character_set_client */ ;;
/*!50003 SET @saved_cs_results     = @@character_set_results */ ;;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;;
/*!50003 SET character_set_client  = latin1 */ ;;
/*!50003 SET character_set_results = latin1 */ ;;
/*!50003 SET collation_connection  = latin1_swedish_ci */ ;;
/*!50003 SET @saved_sql_mode       = @@sql_mode */ ;;
/*!50003 SET sql_mode              = 'STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' */ ;;
/*!50003 SET @saved_time_zone      = @@time_zone */ ;;
/*!50003 SET time_zone             = 'SYSTEM' */ ;;
/*!50106 CREATE*/ /*!50117 DEFINER=`role3`@`%`*/ /*!50106 EVENT `e3` ON SCHEDULE EVERY 1 SECOND STARTS '2000-01-01 00:00:00' ON COMPLETION NOT PRESERVE ENABLE DO insert t1 values (111, 3, 0) */ ;;
/*!50003 SET time_zone             = @saved_time_zone */ ;;
/*!50003 SET sql_mode              = @saved_sql_mode */ ;;
/*!50003 SET character_set_client  = @saved_cs_client */ ;;
/*!50003 SET character_set_results = @saved_cs_results */ ;;
/*!50003 SET collation_connection  = @saved_col_connection */ ;;
DELIMITER ;
/*!50106 SET TIME_ZONE= @save_time_zone */ ;
/*!50003 SET @saved_sql_mode       = @@sql_mode */ ;
/*!50003 SET sql_mode              = 'STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' */ ;
/*!50003 SET @saved_cs_client      = @@character_set_client */ ;
/*!50003 SET @saved_cs_results     = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
/*!50003 SET character_set_client  = latin1 */ ;
/*!50003 SET character_set_results = latin1 */ ;
/*!50003 SET collation_connection  = latin1_swedish_ci */ ;
DELIMITER ;;
CREATE DEFINER=`role1` FUNCTION `fn1`() RETURNS int(11)
return (select sum(a+b) from t1) ;;
DELIMITER ;
/*!50003 SET sql_mode              = @saved_sql_mode */ ;
/*!50003 SET character_set_client  = @saved_cs_client */ ;
/*!50003 SET character_set_results = @saved_cs_results */ ;
/*!50003 SET collation_connection  = @saved_col_connection */ ;
/*!50003 SET @saved_sql_mode       = @@sql_mode */ ;
/*!50003 SET sql_mode              = 'STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' */ ;
/*!50003 SET @saved_cs_client      = @@character_set_client */ ;
/*!50003 SET @saved_cs_results     = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
/*!50003 SET character_set_client  = latin1 */ ;
/*!50003 SET character_set_results = latin1 */ ;
/*!50003 SET collation_connection  = latin1_swedish_ci */ ;
DELIMITER ;;
CREATE DEFINER=`role2` FUNCTION `fn2`() RETURNS int(11)
return (select sum(a+b) from t1) ;;
DELIMITER ;
/*!50003 SET sql_mode              = @saved_sql_mode */ ;
/*!50003 SET character_set_client  = @saved_cs_client */ ;
/*!50003 SET character_set_results = @saved_cs_results */ ;
/*!50003 SET collation_connection  = @saved_col_connection */ ;
/*!50003 SET @saved_sql_mode       = @@sql_mode */ ;
/*!50003 SET sql_mode              = 'STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' */ ;
/*!50003 SET @saved_cs_client      = @@character_set_client */ ;
/*!50003 SET @saved_cs_results     = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
/*!50003 SET character_set_client  = latin1 */ ;
/*!50003 SET character_set_results = latin1 */ ;
/*!50003 SET collation_connection  = latin1_swedish_ci */ ;
DELIMITER ;;
CREATE DEFINER=`role3`@`%` FUNCTION `fn3`() RETURNS int(11)
return (select sum(a+b) from t1) ;;
DELIMITER ;
/*!50003 SET sql_mode              = @saved_sql_mode */ ;
/*!50003 SET character_set_client  = @saved_cs_client */ ;
/*!50003 SET character_set_results = @saved_cs_results */ ;
/*!50003 SET collation_connection  = @saved_col_connection */ ;
/*!50003 SET @saved_sql_mode       = @@sql_mode */ ;
/*!50003 SET sql_mode              = 'STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' */ ;
/*!50003 SET @saved_cs_client      = @@character_set_client */ ;
/*!50003 SET @saved_cs_results     = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
/*!50003 SET character_set_client  = latin1 */ ;
/*!50003 SET character_set_results = latin1 */ ;
/*!50003 SET collation_connection  = latin1_swedish_ci */ ;
DELIMITER ;;
CREATE DEFINER=`role1` PROCEDURE `pr1`()
insert t1 values (111, 222, 333) ;;
DELIMITER ;
/*!50003 SET sql_mode              = @saved_sql_mode */ ;
/*!50003 SET character_set_client  = @saved_cs_client */ ;
/*!50003 SET character_set_results = @saved_cs_results */ ;
/*!50003 SET collation_connection  = @saved_col_connection */ ;
/*!50003 SET @saved_sql_mode       = @@sql_mode */ ;
/*!50003 SET sql_mode              = 'STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' */ ;
/*!50003 SET @saved_cs_client      = @@character_set_client */ ;
/*!50003 SET @saved_cs_results     = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
/*!50003 SET character_set_client  = latin1 */ ;
/*!50003 SET character_set_results = latin1 */ ;
/*!50003 SET collation_connection  = latin1_swedish_ci */ ;
DELIMITER ;;
CREATE DEFINER=`role2`@`%` PROCEDURE `pr2`()
insert t1 values (111, 222, 333) ;;
DELIMITER ;
/*!50003 SET sql_mode              = @saved_sql_mode */ ;
/*!50003 SET character_set_client  = @saved_cs_client */ ;
/*!50003 SET character_set_results = @saved_cs_results */ ;
/*!50003 SET collation_connection  = @saved_col_connection */ ;
/*!50003 SET @saved_sql_mode       = @@sql_mode */ ;
/*!50003 SET sql_mode              = 'STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' */ ;
/*!50003 SET @saved_cs_client      = @@character_set_client */ ;
/*!50003 SET @saved_cs_results     = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
/*!50003 SET character_set_client  = latin1 */ ;
/*!50003 SET character_set_results = latin1 */ ;
/*!50003 SET collation_connection  = latin1_swedish_ci */ ;
DELIMITER ;;
CREATE DEFINER=`role3`@`%` PROCEDURE `pr3`()
insert t1 values (111, 222, 333) ;;
DELIMITER ;
/*!50003 SET sql_mode              = @saved_sql_mode */ ;
/*!50003 SET character_set_client  = @saved_cs_client */ ;
/*!50003 SET character_set_results = @saved_cs_results */ ;
/*!50003 SET collation_connection  = @saved_col_connection */ ;

USE `test`;
/*!50001 DROP VIEW IF EXISTS `v1`*/;
/*!50001 SET @saved_cs_client          = @@character_set_client */;
/*!50001 SET @saved_cs_results         = @@character_set_results */;
/*!50001 SET @saved_col_connection     = @@collation_connection */;
/*!50001 SET character_set_client      = latin1 */;
/*!50001 SET character_set_results     = latin1 */;
/*!50001 SET collation_connection      = latin1_swedish_ci */;
/*!50001 CREATE ALGORITHM=UNDEFINED DEFINER=`role1` SQL SECURITY DEFINER VIEW `v1` AS select `mysqltest1`.`t1`.`a` + `mysqltest1`.`t1`.`b` AS `a+b`,`mysqltest1`.`t1`.`c` AS `c` from `mysqltest1`.`t1` */;
/*!50001 SET character_set_client      = @saved_cs_client */;
/*!50001 SET character_set_results     = @saved_cs_results */;
/*!50001 SET collation_connection      = @saved_col_connection */;
/*!50001 DROP VIEW IF EXISTS `v2`*/;
/*!50001 SET @saved_cs_client          = @@character_set_client */;
/*!50001 SET @saved_cs_results         = @@character_set_results */;
/*!50001 SET @saved_col_connection     = @@collation_connection */;
/*!50001 SET character_set_client      = latin1 */;
/*!50001 SET character_set_results     = latin1 */;
/*!50001 SET collation_connection      = latin1_swedish_ci */;
/*!50001 CREATE ALGORITHM=UNDEFINED DEFINER=`role2` SQL SECURITY DEFINER VIEW `v2` AS select `mysqltest1`.`t1`.`a` + `mysqltest1`.`t1`.`b` AS `a+b`,`mysqltest1`.`t1`.`c` AS `c`,current_role() AS `current_role()` from `mysqltest1`.`t1` */;
/*!50001 SET character_set_client      = @saved_cs_client */;
/*!50001 SET character_set_results     = @saved_cs_results */;
/*!50001 SET collation_connection      = @saved_col_connection */;
/*!50001 DROP VIEW IF EXISTS `v3`*/;
/*!50001 SET @saved_cs_client          = @@character_set_client */;
/*!50001 SET @saved_cs_results         = @@character_set_results */;
/*!50001 SET @saved_col_connection     = @@collation_connection */;
/*!50001 SET character_set_client      = latin1 */;
/*!50001 SET character_set_results     = latin1 */;
/*!50001 SET collation_connection      = latin1_swedish_ci */;
/*!50001 CREATE ALGORITHM=UNDEFINED */
/*!50013 DEFINER=`role3`@`%` SQL SECURITY DEFINER */
/*!50001 VIEW `v3` AS select `mysqltest1`.`t1`.`a` + `mysqltest1`.`t1`.`b` AS `a+b`,`mysqltest1`.`t1`.`c` AS `c` from `mysqltest1`.`t1` */;
/*!50001 SET character_set_client      = @saved_cs_client */;
/*!50001 SET character_set_results     = @saved_cs_results */;
/*!50001 SET collation_connection      = @saved_col_connection */;
/*!50001 DROP VIEW IF EXISTS `v4`*/;
/*!50001 SET @saved_cs_client          = @@character_set_client */;
/*!50001 SET @saved_cs_results         = @@character_set_results */;
/*!50001 SET @saved_col_connection     = @@collation_connection */;
/*!50001 SET character_set_client      = latin1 */;
/*!50001 SET character_set_results     = latin1 */;
/*!50001 SET collation_connection      = latin1_swedish_ci */;
/*!50001 CREATE ALGORITHM=UNDEFINED */
/*!50013 DEFINER=`foo`@`localhost` SQL SECURITY DEFINER */
/*!50001 VIEW `v4` AS select `mysqltest1`.`t1`.`a` + `mysqltest1`.`t1`.`b` AS `a+b`,`mysqltest1`.`t1`.`c` AS `c` from `mysqltest1`.`t1` */;
/*!50001 SET character_set_client      = @saved_cs_client */;
/*!50001 SET character_set_results     = @saved_cs_results */;
/*!50001 SET collation_connection      = @saved_col_connection */;
/*!50001 DROP VIEW IF EXISTS `v5`*/;
/*!50001 SET @saved_cs_client          = @@character_set_client */;
/*!50001 SET @saved_cs_results         = @@character_set_results */;
/*!50001 SET @saved_col_connection     = @@collation_connection */;
/*!50001 SET character_set_client      = latin1 */;
/*!50001 SET character_set_results     = latin1 */;
/*!50001 SET collation_connection      = latin1_swedish_ci */;
/*!50001 CREATE ALGORITHM=UNDEFINED */
/*!50013 DEFINER=`role4`@`%` SQL SECURITY DEFINER */
/*!50001 VIEW `v5` AS select `mysqltest1`.`t1`.`a` + `mysqltest1`.`t1`.`b` AS `a+b`,`mysqltest1`.`t1`.`c` AS `c` from `mysqltest1`.`t1` */;
/*!50001 SET character_set_client      = @saved_cs_client */;
/*!50001 SET character_set_results     = @saved_cs_results */;
/*!50001 SET collation_connection      = @saved_col_connection */;

USE `mysqltest1`;
drop trigger tr1;
drop trigger tr2;
drop trigger tr3;
drop procedure pr1;
drop procedure pr2;
drop procedure pr3;
drop function fn1;
drop function fn2;
drop function fn3;
drop event e1;
drop event e2;
drop event e3;
drop view test.v1, test.v2, test.v3, test.v4, test.v5;
drop table t1, t2;
drop role role1, role2;
drop user foo@localhost;
drop database mysqltest1;
use test;
create user utest;
prepare stmt1 from 'grant select on *.* to utest';
execute stmt1;
show grants for utest;
Grants for utest@%
GRANT SELECT ON *.* TO `utest`@`%`
drop user utest;
create role utest;
execute stmt1;
show grants for utest;
Grants for utest
GRANT SELECT ON *.* TO `utest`
drop role utest;
#
# MDEV-13676: Field "create Procedure" is NULL, even if the the user
# has role which is the definer. (SHOW CREATE PROCEDURE)
#
create database rtest;
create role r1;
create role r2;
create role r3;
grant all privileges  on rtest.*  to r1;
create user user1;
grant r1 to user1;
grant r1 to r2;
grant r2 to user1;
grant r3 to user1;
connect  user1, localhost,user1,,,,,;
set role r2;
use rtest;
CREATE DEFINER=current_role() PROCEDURE user1_proc() SQL SECURITY INVOKER
BEGIN
SELECT NOW(), VERSION();
END;//
set role r2;
show create procedure user1_proc;
Procedure	sql_mode	Create Procedure	character_set_client	collation_connection	Database Collation
user1_proc	STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION	CREATE DEFINER=`r2` PROCEDURE `user1_proc`()
    SQL SECURITY INVOKER
BEGIN
SELECT NOW(), VERSION();
END	latin1	latin1_swedish_ci	latin1_swedish_ci
#
# Currently one can not use as definer any role except CURRENT_ROLE
#
CREATE DEFINER='r1' PROCEDURE user1_proc2() SQL SECURITY INVOKER
BEGIN
SELECT NOW(), VERSION();
END;//
ERROR 42000: Access denied; you need (at least one of) the SUPER, SET USER privilege(s) for this operation
set role r1;
CREATE DEFINER='r1' PROCEDURE user1_proc2() SQL SECURITY INVOKER
BEGIN
SELECT NOW(), VERSION();
END;//
show create procedure user1_proc2;
Procedure	sql_mode	Create Procedure	character_set_client	collation_connection	Database Collation
user1_proc2	STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION	CREATE DEFINER=`r1` PROCEDURE `user1_proc2`()
    SQL SECURITY INVOKER
BEGIN
SELECT NOW(), VERSION();
END	latin1	latin1_swedish_ci	latin1_swedish_ci
#
# Test to see if the user can still see the procedure code if the
# role that owns it is granted to him indirectly.
#
set role r2;
show create procedure user1_proc2;
Procedure	sql_mode	Create Procedure	character_set_client	collation_connection	Database Collation
user1_proc2	STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION	CREATE DEFINER=`r1` PROCEDURE `user1_proc2`()
    SQL SECURITY INVOKER
BEGIN
SELECT NOW(), VERSION();
END	latin1	latin1_swedish_ci	latin1_swedish_ci
#
# One should not be able to see the procedure code if the role that owns
# the procedure is not set by the user or is not in the subgraph of the
# currently active role.
#
set role r3;
show create procedure user1_proc2;
ERROR 42000: PROCEDURE user1_proc2 does not exist
connection default;
use rtest;
#
# Try a few edge cases, with usernames identical to role name;
#
create user user_like_role;
create user foo;
create role user_like_role;
grant select on rtest.* to user_like_role;
grant select on rtest.* to foo;
grant select on rtest.* to user_like_role@'%';
grant user_like_role to foo;
#
# Here we have a procedure that is owned by user_like_role USER
# We don't want user_like_role ROLE to have access to its code.
#
CREATE DEFINER=`user_like_role`@`%` PROCEDURE sensitive_proc() SQL SECURITY INVOKER
BEGIN
SELECT NOW(), VERSION();
END;//
connect  user_like_role, localhost, user_like_role,,,,,;
use rtest;
show create procedure sensitive_proc;
Procedure	sql_mode	Create Procedure	character_set_client	collation_connection	Database Collation
sensitive_proc	STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION	CREATE DEFINER=`user_like_role`@`%` PROCEDURE `sensitive_proc`()
    SQL SECURITY INVOKER
BEGIN
SELECT NOW(), VERSION();
END	latin1	latin1_swedish_ci	latin1_swedish_ci
connect  foo, localhost, foo,,,,,;
set role user_like_role;
use rtest;
#
# Foo has the set rolename identical to the procedure's definer's username.
# Foo should not have access to this procedure.
#
show create procedure sensitive_proc;
ERROR 42000: PROCEDURE sensitive_proc does not exist
connection default;
drop role r1;
drop role r2;
drop role r3;
drop role user_like_role;
drop user user1;
drop user foo;
drop user user_like_role;
drop procedure user1_proc;
drop procedure user1_proc2;
drop procedure sensitive_proc;
drop database rtest;