Debian Codegraph @

mysql-test/suite/roles/definer.result

raw
1 call mtr.add_suppression('Event Scheduler:');
2 create database mysqltest1;
3 use mysqltest1;
4 create table t1 (a int, b int, c int);
5 insert t1 values (1,10,100),(2,20,200);
6 create role role1;
7 grant select (a) on mysqltest1.t1 to role1;
8 grant event,execute,trigger on mysqltest1.* to role1;
9 grant select on test.* to role1;
10 grant role1 to current_user;
11 create role role2;
12 grant insert,select on mysqltest1.t1 to role2;
13 grant event,execute,trigger on mysqltest1.* to role2;
14 grant select on test.* to role2;
15 create user foo@localhost;
16 grant create view on mysqltest1.* to foo@localhost;
17 grant select, create view on test.* to foo@localhost;
18 create role role4;
19 grant select on mysqltest1.t1 to role4;
20 grant role4 to foo@localhost;
21 grant select on test.* to role4;
22 create definer=current_role view test.v1 as select a+b,c from t1;
23 ERROR 0L000: Invalid definer
24 set role role1;
25 create definer=current_role view test.v1 as select a+b,c from t1;
26 show create view test.v1;
27 View Create View character_set_client collation_connection
28 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
29 set role none;
30 create definer=role2 view test.v2 as select a+b,c,current_role() from t1;
31 show create view test.v2;
32 View Create View character_set_client collation_connection
33 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
34 create definer=role3 view test.v3 as select a+b,c from t1;
35 Warnings:
36 Note 1449 The user specified as a definer ('role3'@'%') does not exist
37 show create view test.v3;
38 View Create View character_set_client collation_connection
39 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
40 Warnings:
41 Note 1449 The user specified as a definer ('role3'@'%') does not exist
42 connect c1, localhost, foo,,mysqltest1;
43 connection c1;
44 show grants;
45 Grants for foo@localhost
46 GRANT `role4` TO `foo`@`localhost`
47 GRANT USAGE ON *.* TO `foo`@`localhost`
48 GRANT CREATE VIEW ON `mysqltest1`.* TO `foo`@`localhost`
49 GRANT SELECT, CREATE VIEW ON `test`.* TO `foo`@`localhost`
50 select * from test.v1;
51 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
52 select * from test.v2;
53 a+b c current_role()
54 11 100 role2
55 22 200 role2
56 select * from test.v3;
57 ERROR 28000: Access denied for user 'foo'@'localhost' (using password: NO)
58 create definer=role4 view test.v4 as select a+b,c from t1;
59 ERROR 42000: ANY command denied to user 'foo'@'localhost' for table `mysqltest1`.`t1`
60 select * from t1;
61 ERROR 42000: SELECT command denied to user 'foo'@'localhost' for table `mysqltest1`.`t1`
62 set role role4;
63 select * from t1;
64 a b c
65 1 10 100
66 2 20 200
67 create view test.v4 as select a+b,c from t1;
68 create definer=role4 view test.v5 as select a+b,c from t1;
69 select * from test.v4;
70 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
71 select * from test.v5;
72 a+b c
73 11 100
74 22 200
75 set role none;
76 select * from test.v4;
77 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
78 select * from test.v5;
79 a+b c
80 11 100
81 22 200
82 connection default;
83 drop role role4;
84 show create view test.v5;
85 View Create View character_set_client collation_connection
86 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
87 Warnings:
88 Note 1449 The user specified as a definer ('role4'@'') does not exist
89 select * from test.v5;
90 ERROR HY000: The user specified as a definer ('role4'@'') does not exist
91 create user role4;
92 grant select on mysqltest1.t1 to role4;
93 show create view test.v5;
94 View Create View character_set_client collation_connection
95 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
96 Warnings:
97 Note 1449 The user specified as a definer ('role4'@'') does not exist
98 select * from test.v5;
99 ERROR HY000: The user specified as a definer ('role4'@'') does not exist
100 flush tables;
101 show create view test.v5;
102 View Create View character_set_client collation_connection
103 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
104 select * from test.v5;
105 a+b c
106 11 100
107 22 200
108 drop user role4;
109 create table t2 select * from t1;
110 create definer=current_role trigger tr1 before insert on t2 for each row
111 insert t1 values (111, 222, 333);
112 ERROR 0L000: Invalid definer
113 set role role1;
114 create definer=current_role trigger tr1 before insert on t2 for each row
115 insert t1 values (111, 222, 333);
116 show create trigger tr1;
117 Trigger sql_mode SQL Original Statement character_set_client collation_connection Database Collation Created
118 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
119 insert t1 values (111, 222, 333) latin1 latin1_swedish_ci utf8mb4_uca1400_ai_ci #
120 set role none;
121 insert t2 values (11,22,33);
122 ERROR 42000: INSERT command denied to user ''@'' for table `mysqltest1`.`t1`
123 select * from t1;
124 a b c
125 1 10 100
126 2 20 200
127 select * from t2;
128 a b c
129 1 10 100
130 2 20 200
131 create definer=role2 trigger tr2 before delete on t2 for each row
132 insert t1 values (111, 222, 333);
133 show create trigger tr2;
134 Trigger sql_mode SQL Original Statement character_set_client collation_connection Database Collation Created
135 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
136 insert t1 values (111, 222, 333) latin1 latin1_swedish_ci utf8mb4_uca1400_ai_ci #
137 delete from t2 where a=1;
138 select * from t1;
139 a b c
140 1 10 100
141 2 20 200
142 111 222 333
143 select * from t2;
144 a b c
145 2 20 200
146 delete from t1 where a=111;
147 create definer=role3 trigger tr3 before update on t2 for each row
148 insert t1 values (111, 222, 333);
149 Warnings:
150 Note 1449 The user specified as a definer ('role3'@'%') does not exist
151 show create trigger tr3;
152 Trigger sql_mode SQL Original Statement character_set_client collation_connection Database Collation Created
153 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
154 insert t1 values (111, 222, 333) latin1 latin1_swedish_ci utf8mb4_uca1400_ai_ci #
155 update t2 set b=2 where a=2;
156 ERROR HY000: The user specified as a definer ('role3'@'%') does not exist
157 select * from t1;
158 a b c
159 1 10 100
160 2 20 200
161 select * from t2;
162 a b c
163 2 20 200
164 flush tables;
165 show create trigger tr2;
166 Trigger sql_mode SQL Original Statement character_set_client collation_connection Database Collation Created
167 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
168 insert t1 values (111, 222, 333) latin1 latin1_swedish_ci utf8mb4_uca1400_ai_ci #
169 delete from t2 where a=2;
170 ERROR HY000: The user specified as a definer ('role2'@'%') does not exist
171 select * from t1;
172 a b c
173 1 10 100
174 2 20 200
175 select * from t2;
176 a b c
177 2 20 200
178 create definer=current_role procedure pr1() insert t1 values (111, 222, 333);
179 ERROR 0L000: Invalid definer
180 set role role1;
181 create definer=current_role procedure pr1() insert t1 values (111, 222, 333);
182 show create procedure pr1;
183 Procedure sql_mode Create Procedure character_set_client collation_connection Database Collation
184 pr1 STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION CREATE DEFINER=`role1` PROCEDURE `pr1`()
185 insert t1 values (111, 222, 333) latin1 latin1_swedish_ci utf8mb4_uca1400_ai_ci
186 set role none;
187 call pr1();
188 ERROR 42000: INSERT command denied to user ''@'' for table `mysqltest1`.`t1`
189 select * from t1;
190 a b c
191 1 10 100
192 2 20 200
193 create definer=role2 procedure pr2() insert t1 values (111, 222, 333);
194 show create procedure pr2;
195 Procedure sql_mode Create Procedure character_set_client collation_connection Database Collation
196 pr2 STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION CREATE DEFINER=`role2` PROCEDURE `pr2`()
197 insert t1 values (111, 222, 333) latin1 latin1_swedish_ci utf8mb4_uca1400_ai_ci
198 call pr2();
199 select * from t1;
200 a b c
201 1 10 100
202 2 20 200
203 111 222 333
204 delete from t1 where a=111;
205 create definer=role3 procedure pr3() insert t1 values (111, 222, 333);
206 Warnings:
207 Note 1449 The user specified as a definer ('role3'@'%') does not exist
208 show create procedure pr3;
209 Procedure sql_mode Create Procedure character_set_client collation_connection Database Collation
210 pr3 STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION CREATE DEFINER=`role3`@`%` PROCEDURE `pr3`()
211 insert t1 values (111, 222, 333) latin1 latin1_swedish_ci utf8mb4_uca1400_ai_ci
212 call pr3();
213 ERROR HY000: The user specified as a definer ('role3'@'%') does not exist
214 select * from t1;
215 a b c
216 1 10 100
217 2 20 200
218 update mysql.proc set definer='role2@' where definer='role2';
219 call pr2();
220 ERROR HY000: The user specified as a definer ('role2'@'%') does not exist
221 create definer=current_role function fn1() returns int return (select sum(a+b) from t1);
222 ERROR 0L000: Invalid definer
223 set role role1;
224 create definer=current_role function fn1() returns int return (select sum(a+b) from t1);
225 show create function fn1;
226 Function sql_mode Create Function character_set_client collation_connection Database Collation
227 fn1 STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION CREATE DEFINER=`role1` FUNCTION `fn1`() RETURNS int(11)
228 return (select sum(a+b) from t1) latin1 latin1_swedish_ci utf8mb4_uca1400_ai_ci
229 set role none;
230 select fn1();
231 ERROR 42000: SELECT command denied to user ''@'' for column 'b' in table 't1'
232 select * from t1;
233 a b c
234 1 10 100
235 2 20 200
236 create definer=role2 function fn2() returns int return (select sum(a+b) from t1);
237 show create function fn2;
238 Function sql_mode Create Function character_set_client collation_connection Database Collation
239 fn2 STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION CREATE DEFINER=`role2` FUNCTION `fn2`() RETURNS int(11)
240 return (select sum(a+b) from t1) latin1 latin1_swedish_ci utf8mb4_uca1400_ai_ci
241 select fn2();
242 fn2()
243 33
244 create definer=role3 function fn3() returns int return (select sum(a+b) from t1);
245 Warnings:
246 Note 1449 The user specified as a definer ('role3'@'%') does not exist
247 show create function fn3;
248 Function sql_mode Create Function character_set_client collation_connection Database Collation
249 fn3 STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION CREATE DEFINER=`role3`@`%` FUNCTION `fn3`() RETURNS int(11)
250 return (select sum(a+b) from t1) latin1 latin1_swedish_ci utf8mb4_uca1400_ai_ci
251 select fn3();
252 ERROR HY000: The user specified as a definer ('role3'@'%') does not exist
253 set global event_scheduler=on;
254 create definer=current_role event e1 on schedule every 1 second starts '2000-01-01' do
255 insert t1 values (111, 1, 0);
256 ERROR 0L000: Invalid definer
257 set role role1;
258 create definer=current_role event e1 on schedule every 1 second starts '2000-01-01' do
259 insert t1 values (111, 2, 0);
260 show create event e1;
261 Event sql_mode time_zone Create Event character_set_client collation_connection Database Collation
262 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 utf8mb4_uca1400_ai_ci
263 set role none;
264 create definer=role3 event e3 on schedule every 1 second starts '2000-01-01' do
265 insert t1 values (111, 3, 0);
266 Warnings:
267 Note 1449 The user specified as a definer ('role3'@'%') does not exist
268 show create event e3;
269 Event sql_mode time_zone Create Event character_set_client collation_connection Database Collation
270 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 utf8mb4_uca1400_ai_ci
271 create definer=role2 event e2 on schedule every 1 second starts '2000-01-01' do
272 insert t1 values (111, 4, 0);
273 show create event e2;
274 Event sql_mode time_zone Create Event character_set_client collation_connection Database Collation
275 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 utf8mb4_uca1400_ai_ci
276 set global event_scheduler=off;
277 select distinct * from t1;
278 a b c
279 1 10 100
280 111 4 0
281 2 20 200
282 delete from t1 where a=111;
283 /*M!999999\- enable the sandbox mode */
284
285 CREATE DATABASE /*!32312 IF NOT EXISTS*/ `test` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_uca1400_ai_ci */;
286
287 USE `test`;
288 SET @saved_cs_client = @@character_set_client;
289 SET character_set_client = utf8mb4;
290 /*!50001 CREATE VIEW `v1` AS SELECT
291 NULL AS `a+b`,
292 NULL AS `c` */;
293 SET character_set_client = @saved_cs_client;
294 SET @saved_cs_client = @@character_set_client;
295 SET character_set_client = utf8mb4;
296 /*!50001 CREATE VIEW `v2` AS SELECT
297 NULL AS `a+b`,
298 NULL AS `c`,
299 NULL AS `current_role()` */;
300 SET character_set_client = @saved_cs_client;
301 SET @saved_cs_client = @@character_set_client;
302 SET character_set_client = utf8mb4;
303 /*!50001 CREATE VIEW `v3` AS SELECT
304 NULL AS `a+b`,
305 NULL AS `c` */;
306 SET character_set_client = @saved_cs_client;
307 SET @saved_cs_client = @@character_set_client;
308 SET character_set_client = utf8mb4;
309 /*!50001 CREATE VIEW `v4` AS SELECT
310 NULL AS `a+b`,
311 NULL AS `c` */;
312 SET character_set_client = @saved_cs_client;
313 SET @saved_cs_client = @@character_set_client;
314 SET character_set_client = utf8mb4;
315 /*!50001 CREATE VIEW `v5` AS SELECT
316 NULL AS `a+b`,
317 NULL AS `c` */;
318 SET character_set_client = @saved_cs_client;
319
320 CREATE DATABASE /*!32312 IF NOT EXISTS*/ `mysqltest1` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_uca1400_ai_ci */;
321
322 USE `mysqltest1`;
323 /*!40101 SET @saved_cs_client = @@character_set_client */;
324 /*!40101 SET character_set_client = utf8mb4 */;
325 CREATE TABLE `t1` (
326 `a` int(11) DEFAULT NULL,
327 `b` int(11) DEFAULT NULL,
328 `c` int(11) DEFAULT NULL
329 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci;
330 /*!40101 SET character_set_client = @saved_cs_client */;
331 INSERT INTO `t1` VALUES
332 (1,10,100),
333 (2,20,200);
334 /*!40101 SET @saved_cs_client = @@character_set_client */;
335 /*!40101 SET character_set_client = utf8mb4 */;
336 CREATE TABLE `t2` (
337 `a` int(11) DEFAULT NULL,
338 `b` int(11) DEFAULT NULL,
339 `c` int(11) DEFAULT NULL
340 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci;
341 /*!40101 SET character_set_client = @saved_cs_client */;
342 INSERT INTO `t2` VALUES
343 (2,20,200);
344 /*!50003 SET @saved_cs_client = @@character_set_client */ ;
345 /*!50003 SET @saved_cs_results = @@character_set_results */ ;
346 /*!50003 SET @saved_col_connection = @@collation_connection */ ;
347 /*!50003 SET character_set_client = latin1 */ ;
348 /*!50003 SET character_set_results = latin1 */ ;
349 /*!50003 SET collation_connection = latin1_swedish_ci */ ;
350 /*!50003 SET @saved_sql_mode = @@sql_mode */ ;
351 /*!50003 SET sql_mode = 'STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' */ ;
352 DELIMITER ;;
353 /*!50003 CREATE*/ /*!50017 DEFINER=`role1`*/ /*!50003 trigger tr1 before insert on t2 for each row
354 insert t1 values (111, 222, 333)
355 */;;
356 DELIMITER ;
357 /*!50003 SET sql_mode = @saved_sql_mode */ ;
358 /*!50003 SET character_set_client = @saved_cs_client */ ;
359 /*!50003 SET character_set_results = @saved_cs_results */ ;
360 /*!50003 SET collation_connection = @saved_col_connection */ ;
361 /*!50003 SET @saved_cs_client = @@character_set_client */ ;
362 /*!50003 SET @saved_cs_results = @@character_set_results */ ;
363 /*!50003 SET @saved_col_connection = @@collation_connection */ ;
364 /*!50003 SET character_set_client = latin1 */ ;
365 /*!50003 SET character_set_results = latin1 */ ;
366 /*!50003 SET collation_connection = latin1_swedish_ci */ ;
367 /*!50003 SET @saved_sql_mode = @@sql_mode */ ;
368 /*!50003 SET sql_mode = 'STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' */ ;
369 DELIMITER ;;
370 /*!50003 CREATE*/ /*!50017 DEFINER=`role3`@`%`*/ /*!50003 trigger tr3 before update on t2 for each row
371 insert t1 values (111, 222, 333)
372 */;;
373 DELIMITER ;
374 /*!50003 SET sql_mode = @saved_sql_mode */ ;
375 /*!50003 SET character_set_client = @saved_cs_client */ ;
376 /*!50003 SET character_set_results = @saved_cs_results */ ;
377 /*!50003 SET collation_connection = @saved_col_connection */ ;
378 /*!50003 SET @saved_cs_client = @@character_set_client */ ;
379 /*!50003 SET @saved_cs_results = @@character_set_results */ ;
380 /*!50003 SET @saved_col_connection = @@collation_connection */ ;
381 /*!50003 SET character_set_client = latin1 */ ;
382 /*!50003 SET character_set_results = latin1 */ ;
383 /*!50003 SET collation_connection = latin1_swedish_ci */ ;
384 /*!50003 SET @saved_sql_mode = @@sql_mode */ ;
385 /*!50003 SET sql_mode = 'STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' */ ;
386 DELIMITER ;;
387 /*!50003 CREATE*/ /*!50017 DEFINER=`role2`@``*/ /*!50003 trigger tr2 before delete on t2 for each row
388 insert t1 values (111, 222, 333)
389 */;;
390 DELIMITER ;
391 /*!50003 SET sql_mode = @saved_sql_mode */ ;
392 /*!50003 SET character_set_client = @saved_cs_client */ ;
393 /*!50003 SET character_set_results = @saved_cs_results */ ;
394 /*!50003 SET collation_connection = @saved_col_connection */ ;
395 /*!50106 SET @save_time_zone= @@TIME_ZONE */ ;
396 DELIMITER ;;
397 /*!50003 SET @saved_cs_client = @@character_set_client */ ;;
398 /*!50003 SET @saved_cs_results = @@character_set_results */ ;;
399 /*!50003 SET @saved_col_connection = @@collation_connection */ ;;
400 /*!50003 SET character_set_client = latin1 */ ;;
401 /*!50003 SET character_set_results = latin1 */ ;;
402 /*!50003 SET collation_connection = latin1_swedish_ci */ ;;
403 /*!50003 SET @saved_sql_mode = @@sql_mode */ ;;
404 /*!50003 SET sql_mode = 'STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' */ ;;
405 /*!50003 SET @saved_time_zone = @@time_zone */ ;;
406 /*!50003 SET time_zone = 'SYSTEM' */ ;;
407 /*!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)
408 */ ;;
409 /*!50003 SET time_zone = @saved_time_zone */ ;;
410 /*!50003 SET sql_mode = @saved_sql_mode */ ;;
411 /*!50003 SET character_set_client = @saved_cs_client */ ;;
412 /*!50003 SET character_set_results = @saved_cs_results */ ;;
413 /*!50003 SET collation_connection = @saved_col_connection */ ;;
414 DELIMITER ;;
415 /*!50003 SET @saved_cs_client = @@character_set_client */ ;;
416 /*!50003 SET @saved_cs_results = @@character_set_results */ ;;
417 /*!50003 SET @saved_col_connection = @@collation_connection */ ;;
418 /*!50003 SET character_set_client = latin1 */ ;;
419 /*!50003 SET character_set_results = latin1 */ ;;
420 /*!50003 SET collation_connection = latin1_swedish_ci */ ;;
421 /*!50003 SET @saved_sql_mode = @@sql_mode */ ;;
422 /*!50003 SET sql_mode = 'STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' */ ;;
423 /*!50003 SET @saved_time_zone = @@time_zone */ ;;
424 /*!50003 SET time_zone = 'SYSTEM' */ ;;
425 /*!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)
426 */ ;;
427 /*!50003 SET time_zone = @saved_time_zone */ ;;
428 /*!50003 SET sql_mode = @saved_sql_mode */ ;;
429 /*!50003 SET character_set_client = @saved_cs_client */ ;;
430 /*!50003 SET character_set_results = @saved_cs_results */ ;;
431 /*!50003 SET collation_connection = @saved_col_connection */ ;;
432 DELIMITER ;;
433 /*!50003 SET @saved_cs_client = @@character_set_client */ ;;
434 /*!50003 SET @saved_cs_results = @@character_set_results */ ;;
435 /*!50003 SET @saved_col_connection = @@collation_connection */ ;;
436 /*!50003 SET character_set_client = latin1 */ ;;
437 /*!50003 SET character_set_results = latin1 */ ;;
438 /*!50003 SET collation_connection = latin1_swedish_ci */ ;;
439 /*!50003 SET @saved_sql_mode = @@sql_mode */ ;;
440 /*!50003 SET sql_mode = 'STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' */ ;;
441 /*!50003 SET @saved_time_zone = @@time_zone */ ;;
442 /*!50003 SET time_zone = 'SYSTEM' */ ;;
443 /*!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)
444 */ ;;
445 /*!50003 SET time_zone = @saved_time_zone */ ;;
446 /*!50003 SET sql_mode = @saved_sql_mode */ ;;
447 /*!50003 SET character_set_client = @saved_cs_client */ ;;
448 /*!50003 SET character_set_results = @saved_cs_results */ ;;
449 /*!50003 SET collation_connection = @saved_col_connection */ ;;
450 DELIMITER ;
451 /*!50106 SET TIME_ZONE= @save_time_zone */ ;
452 /*!50003 SET @saved_sql_mode = @@sql_mode */ ;
453 /*!50003 SET sql_mode = 'STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' */ ;
454 /*!50003 SET @saved_cs_client = @@character_set_client */ ;
455 /*!50003 SET @saved_cs_results = @@character_set_results */ ;
456 /*!50003 SET @saved_col_connection = @@collation_connection */ ;
457 /*!50003 SET character_set_client = latin1 */ ;
458 /*!50003 SET character_set_results = latin1 */ ;
459 /*!50003 SET collation_connection = latin1_swedish_ci */ ;
460 DELIMITER ;;
461 CREATE DEFINER=`role1` FUNCTION `fn1`() RETURNS int(11)
462 return (select sum(a+b) from t1)
463 ;;
464 DELIMITER ;
465 /*!50003 SET sql_mode = @saved_sql_mode */ ;
466 /*!50003 SET character_set_client = @saved_cs_client */ ;
467 /*!50003 SET character_set_results = @saved_cs_results */ ;
468 /*!50003 SET collation_connection = @saved_col_connection */ ;
469 /*!50003 SET @saved_sql_mode = @@sql_mode */ ;
470 /*!50003 SET sql_mode = 'STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' */ ;
471 /*!50003 SET @saved_cs_client = @@character_set_client */ ;
472 /*!50003 SET @saved_cs_results = @@character_set_results */ ;
473 /*!50003 SET @saved_col_connection = @@collation_connection */ ;
474 /*!50003 SET character_set_client = latin1 */ ;
475 /*!50003 SET character_set_results = latin1 */ ;
476 /*!50003 SET collation_connection = latin1_swedish_ci */ ;
477 DELIMITER ;;
478 CREATE DEFINER=`role2` FUNCTION `fn2`() RETURNS int(11)
479 return (select sum(a+b) from t1)
480 ;;
481 DELIMITER ;
482 /*!50003 SET sql_mode = @saved_sql_mode */ ;
483 /*!50003 SET character_set_client = @saved_cs_client */ ;
484 /*!50003 SET character_set_results = @saved_cs_results */ ;
485 /*!50003 SET collation_connection = @saved_col_connection */ ;
486 /*!50003 SET @saved_sql_mode = @@sql_mode */ ;
487 /*!50003 SET sql_mode = 'STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' */ ;
488 /*!50003 SET @saved_cs_client = @@character_set_client */ ;
489 /*!50003 SET @saved_cs_results = @@character_set_results */ ;
490 /*!50003 SET @saved_col_connection = @@collation_connection */ ;
491 /*!50003 SET character_set_client = latin1 */ ;
492 /*!50003 SET character_set_results = latin1 */ ;
493 /*!50003 SET collation_connection = latin1_swedish_ci */ ;
494 DELIMITER ;;
495 CREATE DEFINER=`role3`@`%` FUNCTION `fn3`() RETURNS int(11)
496 return (select sum(a+b) from t1)
497 ;;
498 DELIMITER ;
499 /*!50003 SET sql_mode = @saved_sql_mode */ ;
500 /*!50003 SET character_set_client = @saved_cs_client */ ;
501 /*!50003 SET character_set_results = @saved_cs_results */ ;
502 /*!50003 SET collation_connection = @saved_col_connection */ ;
503 /*!50003 SET @saved_sql_mode = @@sql_mode */ ;
504 /*!50003 SET sql_mode = 'STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' */ ;
505 /*!50003 SET @saved_cs_client = @@character_set_client */ ;
506 /*!50003 SET @saved_cs_results = @@character_set_results */ ;
507 /*!50003 SET @saved_col_connection = @@collation_connection */ ;
508 /*!50003 SET character_set_client = latin1 */ ;
509 /*!50003 SET character_set_results = latin1 */ ;
510 /*!50003 SET collation_connection = latin1_swedish_ci */ ;
511 DELIMITER ;;
512 CREATE DEFINER=`role1` PROCEDURE `pr1`()
513 insert t1 values (111, 222, 333)
514 ;;
515 DELIMITER ;
516 /*!50003 SET sql_mode = @saved_sql_mode */ ;
517 /*!50003 SET character_set_client = @saved_cs_client */ ;
518 /*!50003 SET character_set_results = @saved_cs_results */ ;
519 /*!50003 SET collation_connection = @saved_col_connection */ ;
520 /*!50003 SET @saved_sql_mode = @@sql_mode */ ;
521 /*!50003 SET sql_mode = 'STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' */ ;
522 /*!50003 SET @saved_cs_client = @@character_set_client */ ;
523 /*!50003 SET @saved_cs_results = @@character_set_results */ ;
524 /*!50003 SET @saved_col_connection = @@collation_connection */ ;
525 /*!50003 SET character_set_client = latin1 */ ;
526 /*!50003 SET character_set_results = latin1 */ ;
527 /*!50003 SET collation_connection = latin1_swedish_ci */ ;
528 DELIMITER ;;
529 CREATE DEFINER=`role2`@`%` PROCEDURE `pr2`()
530 insert t1 values (111, 222, 333)
531 ;;
532 DELIMITER ;
533 /*!50003 SET sql_mode = @saved_sql_mode */ ;
534 /*!50003 SET character_set_client = @saved_cs_client */ ;
535 /*!50003 SET character_set_results = @saved_cs_results */ ;
536 /*!50003 SET collation_connection = @saved_col_connection */ ;
537 /*!50003 SET @saved_sql_mode = @@sql_mode */ ;
538 /*!50003 SET sql_mode = 'STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' */ ;
539 /*!50003 SET @saved_cs_client = @@character_set_client */ ;
540 /*!50003 SET @saved_cs_results = @@character_set_results */ ;
541 /*!50003 SET @saved_col_connection = @@collation_connection */ ;
542 /*!50003 SET character_set_client = latin1 */ ;
543 /*!50003 SET character_set_results = latin1 */ ;
544 /*!50003 SET collation_connection = latin1_swedish_ci */ ;
545 DELIMITER ;;
546 CREATE DEFINER=`role3`@`%` PROCEDURE `pr3`()
547 insert t1 values (111, 222, 333)
548 ;;
549 DELIMITER ;
550 /*!50003 SET sql_mode = @saved_sql_mode */ ;
551 /*!50003 SET character_set_client = @saved_cs_client */ ;
552 /*!50003 SET character_set_results = @saved_cs_results */ ;
553 /*!50003 SET collation_connection = @saved_col_connection */ ;
554
555 USE `test`;
556 /*!50001 DROP VIEW IF EXISTS `v1`*/;
557 /*!50001 SET @saved_cs_client = @@character_set_client */;
558 /*!50001 SET @saved_cs_results = @@character_set_results */;
559 /*!50001 SET @saved_col_connection = @@collation_connection */;
560 /*!50001 SET character_set_client = latin1 */;
561 /*!50001 SET character_set_results = latin1 */;
562 /*!50001 SET collation_connection = latin1_swedish_ci */;
563 /*!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` */;
564 /*!50001 SET character_set_client = @saved_cs_client */;
565 /*!50001 SET character_set_results = @saved_cs_results */;
566 /*!50001 SET collation_connection = @saved_col_connection */;
567 /*!50001 DROP VIEW IF EXISTS `v2`*/;
568 /*!50001 SET @saved_cs_client = @@character_set_client */;
569 /*!50001 SET @saved_cs_results = @@character_set_results */;
570 /*!50001 SET @saved_col_connection = @@collation_connection */;
571 /*!50001 SET character_set_client = latin1 */;
572 /*!50001 SET character_set_results = latin1 */;
573 /*!50001 SET collation_connection = latin1_swedish_ci */;
574 /*!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` */;
575 /*!50001 SET character_set_client = @saved_cs_client */;
576 /*!50001 SET character_set_results = @saved_cs_results */;
577 /*!50001 SET collation_connection = @saved_col_connection */;
578 /*!50001 DROP VIEW IF EXISTS `v3`*/;
579 /*!50001 SET @saved_cs_client = @@character_set_client */;
580 /*!50001 SET @saved_cs_results = @@character_set_results */;
581 /*!50001 SET @saved_col_connection = @@collation_connection */;
582 /*!50001 SET character_set_client = latin1 */;
583 /*!50001 SET character_set_results = latin1 */;
584 /*!50001 SET collation_connection = latin1_swedish_ci */;
585 /*!50001 CREATE ALGORITHM=UNDEFINED */
586 /*!50013 DEFINER=`role3`@`%` SQL SECURITY DEFINER */
587 /*!50001 VIEW `v3` AS select `mysqltest1`.`t1`.`a` + `mysqltest1`.`t1`.`b` AS `a+b`,`mysqltest1`.`t1`.`c` AS `c` from `mysqltest1`.`t1` */;
588 /*!50001 SET character_set_client = @saved_cs_client */;
589 /*!50001 SET character_set_results = @saved_cs_results */;
590 /*!50001 SET collation_connection = @saved_col_connection */;
591 /*!50001 DROP VIEW IF EXISTS `v4`*/;
592 /*!50001 SET @saved_cs_client = @@character_set_client */;
593 /*!50001 SET @saved_cs_results = @@character_set_results */;
594 /*!50001 SET @saved_col_connection = @@collation_connection */;
595 /*!50001 SET character_set_client = latin1 */;
596 /*!50001 SET character_set_results = latin1 */;
597 /*!50001 SET collation_connection = latin1_swedish_ci */;
598 /*!50001 CREATE ALGORITHM=UNDEFINED */
599 /*!50013 DEFINER=`foo`@`localhost` SQL SECURITY DEFINER */
600 /*!50001 VIEW `v4` AS select `mysqltest1`.`t1`.`a` + `mysqltest1`.`t1`.`b` AS `a+b`,`mysqltest1`.`t1`.`c` AS `c` from `mysqltest1`.`t1` */;
601 /*!50001 SET character_set_client = @saved_cs_client */;
602 /*!50001 SET character_set_results = @saved_cs_results */;
603 /*!50001 SET collation_connection = @saved_col_connection */;
604 /*!50001 DROP VIEW IF EXISTS `v5`*/;
605 /*!50001 SET @saved_cs_client = @@character_set_client */;
606 /*!50001 SET @saved_cs_results = @@character_set_results */;
607 /*!50001 SET @saved_col_connection = @@collation_connection */;
608 /*!50001 SET character_set_client = latin1 */;
609 /*!50001 SET character_set_results = latin1 */;
610 /*!50001 SET collation_connection = latin1_swedish_ci */;
611 /*!50001 CREATE ALGORITHM=UNDEFINED */
612 /*!50013 DEFINER=`role4`@`%` SQL SECURITY DEFINER */
613 /*!50001 VIEW `v5` AS select `mysqltest1`.`t1`.`a` + `mysqltest1`.`t1`.`b` AS `a+b`,`mysqltest1`.`t1`.`c` AS `c` from `mysqltest1`.`t1` */;
614 /*!50001 SET character_set_client = @saved_cs_client */;
615 /*!50001 SET character_set_results = @saved_cs_results */;
616 /*!50001 SET collation_connection = @saved_col_connection */;
617
618 USE `mysqltest1`;
619 drop trigger tr1;
620 drop trigger tr2;
621 drop trigger tr3;
622 drop procedure pr1;
623 drop procedure pr2;
624 drop procedure pr3;
625 drop function fn1;
626 drop function fn2;
627 drop function fn3;
628 drop event e1;
629 drop event e2;
630 drop event e3;
631 drop view test.v1, test.v2, test.v3, test.v4, test.v5;
632 drop table t1, t2;
633 drop role role1, role2;
634 drop user foo@localhost;
635 drop database mysqltest1;
636 use test;
637 create user utest;
638 prepare stmt1 from 'grant select on *.* to utest';
639 execute stmt1;
640 show grants for utest;
641 Grants for utest@%
642 GRANT SELECT ON *.* TO `utest`@`%`
643 drop user utest;
644 create role utest;
645 execute stmt1;
646 show grants for utest;
647 Grants for utest
648 GRANT SELECT ON *.* TO `utest`
649 drop role utest;
650 #
651 # MDEV-13676: Field "create Procedure" is NULL, even if the the user
652 # has role which is the definer. (SHOW CREATE PROCEDURE)
653 #
654 create database rtest;
655 create role r1;
656 create role r2;
657 create role r3;
658 grant all privileges on rtest.* to r1;
659 create user user1;
660 grant r1 to user1;
661 grant r1 to r2;
662 grant r2 to user1;
663 grant r3 to user1;
664 connect user1, localhost,user1,,"*NO-ONE*",,,;
665 set role r2;
666 use rtest;
667 CREATE DEFINER=current_role() PROCEDURE user1_proc() SQL SECURITY INVOKER
668 BEGIN
669 SELECT NOW(), VERSION();
670 END;//
671 set role r2;
672 show create procedure user1_proc;
673 Procedure sql_mode Create Procedure character_set_client collation_connection Database Collation
674 user1_proc STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION CREATE DEFINER=`r2` PROCEDURE `user1_proc`()
675 SQL SECURITY INVOKER
676 BEGIN
677 SELECT NOW(), VERSION();
678 END latin1 latin1_swedish_ci utf8mb4_uca1400_ai_ci
679 #
680 # Currently one can not use as definer any role except CURRENT_ROLE
681 #
682 CREATE DEFINER='r1' PROCEDURE user1_proc2() SQL SECURITY INVOKER
683 BEGIN
684 SELECT NOW(), VERSION();
685 END;//
686 ERROR 42000: Access denied; you need (at least one of) the SET USER privilege(s) for this operation
687 set role r1;
688 CREATE DEFINER='r1' PROCEDURE user1_proc2() SQL SECURITY INVOKER
689 BEGIN
690 SELECT NOW(), VERSION();
691 END;//
692 show create procedure user1_proc2;
693 Procedure sql_mode Create Procedure character_set_client collation_connection Database Collation
694 user1_proc2 STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION CREATE DEFINER=`r1` PROCEDURE `user1_proc2`()
695 SQL SECURITY INVOKER
696 BEGIN
697 SELECT NOW(), VERSION();
698 END latin1 latin1_swedish_ci utf8mb4_uca1400_ai_ci
699 #
700 # Test to see if the user can still see the procedure code if the
701 # role that owns it is granted to him indirectly.
702 #
703 set role r2;
704 show create procedure user1_proc2;
705 Procedure sql_mode Create Procedure character_set_client collation_connection Database Collation
706 user1_proc2 STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION CREATE DEFINER=`r1` PROCEDURE `user1_proc2`()
707 SQL SECURITY INVOKER
708 BEGIN
709 SELECT NOW(), VERSION();
710 END latin1 latin1_swedish_ci utf8mb4_uca1400_ai_ci
711 #
712 # One should not be able to see the procedure code if the role that owns
713 # the procedure is not set by the user or is not in the subgraph of the
714 # currently active role.
715 #
716 set role r3;
717 show create procedure user1_proc2;
718 ERROR 42000: PROCEDURE user1_proc2 does not exist
719 connection default;
720 use rtest;
721 #
722 # Try a few edge cases, with usernames identical to role name;
723 #
724 create user user_like_role;
725 create user foo;
726 create role user_like_role;
727 grant select on rtest.* to user_like_role;
728 grant select on rtest.* to foo;
729 grant select on rtest.* to user_like_role@'%';
730 grant user_like_role to foo;
731 #
732 # Here we have a procedure that is owned by user_like_role USER
733 # We don't want user_like_role ROLE to have access to its code.
734 #
735 CREATE DEFINER=`user_like_role`@`%` PROCEDURE sensitive_proc() SQL SECURITY INVOKER
736 BEGIN
737 SELECT NOW(), VERSION();
738 END;//
739 connect user_like_role, localhost, user_like_role,,"*NO-ONE*",,,;
740 use rtest;
741 show create procedure sensitive_proc;
742 Procedure sql_mode Create Procedure character_set_client collation_connection Database Collation
743 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`()
744 SQL SECURITY INVOKER
745 BEGIN
746 SELECT NOW(), VERSION();
747 END latin1 latin1_swedish_ci utf8mb4_uca1400_ai_ci
748 connect foo, localhost, foo,,"*NO-ONE*",,,;
749 set role user_like_role;
750 use rtest;
751 #
752 # Foo has the set rolename identical to the procedure's definer's username.
753 # Foo should not have access to this procedure.
754 #
755 show create procedure sensitive_proc;
756 ERROR 42000: PROCEDURE sensitive_proc does not exist
757 connection default;
758 drop role r1;
759 drop role r2;
760 drop role r3;
761 drop role user_like_role;
762 drop user user1;
763 drop user foo;
764 drop user user_like_role;
765 drop procedure user1_proc;
766 drop procedure user1_proc2;
767 drop procedure sensitive_proc;
768 drop database rtest;