diff options
Diffstat (limited to 'mysql-test/suite/roles/definer.result')
| -rw-r--r-- | mysql-test/suite/roles/definer.result | 754 |
1 files changed, 754 insertions, 0 deletions
diff --git a/mysql-test/suite/roles/definer.result b/mysql-test/suite/roles/definer.result new file mode 100644 index 00000000..091ba255 --- /dev/null +++ b/mysql-test/suite/roles/definer.result @@ -0,0 +1,754 @@ +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 select on test.* 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 select on test.* to role2; +create user foo@localhost; +grant create view on mysqltest1.* to foo@localhost; +grant select, create view on test.* to foo@localhost; +create role role4; +grant select on mysqltest1.t1 to role4; +grant role4 to foo@localhost; +grant select on test.* to role4; +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` +GRANT SELECT, CREATE VIEW ON `test`.* 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 `mysqltest1`.`t1` +select * from t1; +ERROR 42000: SELECT command denied to user 'foo'@'localhost' for table `mysqltest1`.`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 `mysqltest1`.`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 `mysqltest1`.`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,,"*NO-ONE*",,,; +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,,"*NO-ONE*",,,; +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,,"*NO-ONE*",,,; +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; |