diff options
Diffstat (limited to 'mysql-test/suite/roles/definer.result')
| -rw-r--r-- | mysql-test/suite/roles/definer.result | 625 |
1 files changed, 625 insertions, 0 deletions
diff --git a/mysql-test/suite/roles/definer.result b/mysql-test/suite/roles/definer.result new file mode 100644 index 00000000000..0010853be78 --- /dev/null +++ b/mysql-test/suite/roles/definer.result @@ -0,0 +1,625 @@ +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; +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 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` 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 +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 +11 100 +22 200 +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 +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 +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 +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 +tr1 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 'role1'@'' 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 +tr2 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 +tr3 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 +tr2 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 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 'role1'@'' 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 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 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 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 'role1'@'' 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 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 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 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 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 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 */; + +USE `test`; +SET @saved_cs_client = @@character_set_client; +SET character_set_client = utf8; +/*!50001 CREATE TABLE `v1` ( + `a+b` tinyint NOT NULL, + `c` tinyint NOT NULL +) ENGINE=MyISAM */; +SET character_set_client = @saved_cs_client; +SET @saved_cs_client = @@character_set_client; +SET character_set_client = utf8; +/*!50001 CREATE TABLE `v2` ( + `a+b` tinyint NOT NULL, + `c` tinyint NOT NULL +) ENGINE=MyISAM */; +SET character_set_client = @saved_cs_client; +SET @saved_cs_client = @@character_set_client; +SET character_set_client = utf8; +/*!50001 CREATE TABLE `v3` ( + `a+b` tinyint NOT NULL, + `c` tinyint NOT NULL +) ENGINE=MyISAM */; +SET character_set_client = @saved_cs_client; +SET @saved_cs_client = @@character_set_client; +SET character_set_client = utf8; +/*!50001 CREATE TABLE `v4` ( + `a+b` tinyint NOT NULL, + `c` tinyint NOT NULL +) ENGINE=MyISAM */; +SET character_set_client = @saved_cs_client; +SET @saved_cs_client = @@character_set_client; +SET character_set_client = utf8; +/*!50001 CREATE TABLE `v5` ( + `a+b` tinyint NOT NULL, + `c` tinyint NOT NULL +) ENGINE=MyISAM */; +SET character_set_client = @saved_cs_client; + +CREATE DATABASE /*!32312 IF NOT EXISTS*/ `mysqltest1` /*!40100 DEFAULT CHARACTER SET latin1 */; + +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; +/*!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; +/*!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 = '' */ ; +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 = '' */ ; +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 = '' */ ; +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 = '' */ ;; +/*!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 = '' */ ;; +/*!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 = '' */ ;; +/*!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_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 = '' */ ; +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_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 = '' */ ; +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_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 = '' */ ; +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_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 = '' */ ; +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_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 = '' */ ; +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_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 = '' */ ; +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 TABLE 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 TABLE 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` 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 TABLE 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 TABLE 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 TABLE 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; |