Debian Codegraph @

mysql-test/suite/opt_trace/r/security_no_prot.result

raw
1 set @old_size = @@global.optimizer_trace_max_mem_size;
2 set global optimizer_trace_max_mem_size=1048576;
3 select user();
4 user()
5 root@localhost
6 create database somedb;
7 use somedb;
8 create table t1(a varchar(100));
9 insert into t1 values("first");
10 create table t2(a varchar(100));
11 insert into t2 values("first");
12 create table t3(a varchar(100));
13 insert into t3 values("first");
14 SET sql_mode = 'ONLY_FULL_GROUP_BY,NO_ENGINE_SUBSTITUTION';
15 create procedure p1() sql security definer
16 begin
17 declare b int;
18 if (select count(*) from t1)
19 then
20 select 22 into b from dual;
21 end if;
22 select a into b from t1 limit 1;
23 insert into t1 values(current_user());
24 end|
25 create function f1() returns int sql security definer
26 begin
27 declare b int;
28 select 48 into b from dual;
29 select a into b from t1 limit 1;
30 insert into t1 values(current_user());
31 return 36;
32 end|
33 create trigger trg2 before insert on t2 for each row
34 begin
35 insert into t3 select * from t3;
36 end|
37 SET sql_mode = default;
38 create sql security definer view v1 as select * from t1;
39 create user user1@localhost identified by '';
40 grant all on *.* to user1@localhost with grant option;
41
42 select user();
43 user()
44 user1@localhost
45 set optimizer_trace="enabled=on";
46 show grants;
47 Grants for user1@localhost
48 GRANT APPLICATION_PASSWORD_ADMIN,AUDIT_ABORT_EXEMPT,AUDIT_ADMIN,AUTHENTICATION_POLICY_ADMIN,BACKUP_ADMIN,BINLOG_ADMIN,BINLOG_ENCRYPTION_ADMIN,CLONE_ADMIN,CONNECTION_ADMIN,ENCRYPTION_KEY_ADMIN,FIREWALL_EXEMPT,FLUSH_OPTIMIZER_COSTS,FLUSH_STATUS,FLUSH_TABLES,FLUSH_USER_RESOURCES,GROUP_REPLICATION_ADMIN,GROUP_REPLICATION_STREAM,INNODB_REDO_LOG_ARCHIVE,INNODB_REDO_LOG_ENABLE,PASSWORDLESS_USER_ADMIN,PERSIST_RO_VARIABLES_ADMIN,REPLICATION_APPLIER,REPLICATION_SLAVE_ADMIN,RESOURCE_GROUP_ADMIN,RESOURCE_GROUP_USER,ROLE_ADMIN,SENSITIVE_VARIABLES_OBSERVER,SERVICE_CONNECTION_ADMIN,SESSION_VARIABLES_ADMIN,SET_USER_ID,SHOW_ROUTINE,SYSTEM_USER,SYSTEM_VARIABLES_ADMIN,TABLE_ENCRYPTION_ADMIN,TELEMETRY_LOG_ADMIN,XA_RECOVER_ADMIN ON *.* TO `user1`@`localhost` WITH GRANT OPTION
49 GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE, CREATE ROLE, DROP ROLE ON *.* TO `user1`@`localhost` WITH GRANT OPTION
50
51 # ==========================================================
52 # Part A.
53 # Test that security context changes are allowed when, and only
54 # when, invoker has all global privileges.
55 # ==========================================================
56
57 # Because invoker has all global privileges, all traces are visible:
58 set optimizer_trace_offset=0,optimizer_trace_limit=100;
59 call p1();
60 select QUERY, length(TRACE), INSUFFICIENT_PRIVILEGES from information_schema.OPTIMIZER_TRACE;
61 QUERY length(TRACE) INSUFFICIENT_PRIVILEGES
62 call p1() 20 0
63 set b@0 NULL 20 0
64 jump_if_not 3(3) (select count(0) from `somedb`.`t1`) 3065 0
65 select 22 into b from dual 407 0
66 select a into b from t1 limit 1 2253 0
67 insert into t1 values(current_user()) 20 0
68 # this SET always purges all remembered traces
69 set optimizer_trace_offset=0,optimizer_trace_limit=100;
70 select f1();
71 f1()
72 36
73 select QUERY, length(TRACE), INSUFFICIENT_PRIVILEGES from information_schema.OPTIMIZER_TRACE;
74 QUERY length(TRACE) INSUFFICIENT_PRIVILEGES
75 select f1() 413 0
76 set b@0 NULL 20 0
77 select 48 into b from dual 407 0
78 select a into b from t1 limit 1 2253 0
79 insert into t1 values(current_user()) 20 0
80 freturn 3 36 20 0
81 set optimizer_trace_offset=0,optimizer_trace_limit=100;
82 select * from v1;
83 a
84 first
85 root@localhost
86 root@localhost
87 select QUERY, length(TRACE), INSUFFICIENT_PRIVILEGES from information_schema.OPTIMIZER_TRACE;
88 QUERY length(TRACE) INSUFFICIENT_PRIVILEGES
89 select * from v1 2920 0
90 set optimizer_trace_offset=0,optimizer_trace_limit=100;
91 insert into t2 values(current_user());
92 select QUERY, length(TRACE), INSUFFICIENT_PRIVILEGES from information_schema.OPTIMIZER_TRACE;
93 QUERY length(TRACE) INSUFFICIENT_PRIVILEGES
94 insert into t2 values(current_user()) 20 0
95 insert into t3 select * from t3 3120 0
96
97 # Show that really all global privileges are needed: let root
98 # revoke just one from user1. Because user1 does not have all global
99 # privileges anymore, security context changes are forbidden,
100 # thus there is no trace.
101
102 select user();
103 user()
104 root@localhost
105 revoke shutdown on *.* from user1@localhost;
106
107 select user();
108 user()
109 user1@localhost
110 set optimizer_trace="enabled=on";
111 show grants;
112 Grants for user1@localhost
113 GRANT APPLICATION_PASSWORD_ADMIN,AUDIT_ABORT_EXEMPT,AUDIT_ADMIN,AUTHENTICATION_POLICY_ADMIN,BACKUP_ADMIN,BINLOG_ADMIN,BINLOG_ENCRYPTION_ADMIN,CLONE_ADMIN,CONNECTION_ADMIN,ENCRYPTION_KEY_ADMIN,FIREWALL_EXEMPT,FLUSH_OPTIMIZER_COSTS,FLUSH_STATUS,FLUSH_TABLES,FLUSH_USER_RESOURCES,GROUP_REPLICATION_ADMIN,GROUP_REPLICATION_STREAM,INNODB_REDO_LOG_ARCHIVE,INNODB_REDO_LOG_ENABLE,PASSWORDLESS_USER_ADMIN,PERSIST_RO_VARIABLES_ADMIN,REPLICATION_APPLIER,REPLICATION_SLAVE_ADMIN,RESOURCE_GROUP_ADMIN,RESOURCE_GROUP_USER,ROLE_ADMIN,SENSITIVE_VARIABLES_OBSERVER,SERVICE_CONNECTION_ADMIN,SESSION_VARIABLES_ADMIN,SET_USER_ID,SHOW_ROUTINE,SYSTEM_USER,SYSTEM_VARIABLES_ADMIN,TABLE_ENCRYPTION_ADMIN,TELEMETRY_LOG_ADMIN,XA_RECOVER_ADMIN ON *.* TO `user1`@`localhost` WITH GRANT OPTION
114 GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE, CREATE ROLE, DROP ROLE ON *.* TO `user1`@`localhost` WITH GRANT OPTION
115 set optimizer_trace_offset=0,optimizer_trace_limit=100;
116 call p1();
117 # In CALL we execute stored procedure and notice a security
118 # context change. The context change is probably only relevant
119 # for substatements, but we still hide CALL. This is to be
120 # consistent with what we do when routine body should not be
121 # exposed. And it also feels safer to disable I_S output as
122 # soon as possible.
123 # Ps-protocol-specific note: mysqltest uses normal protocol for CALL
124 select QUERY, length(TRACE), INSUFFICIENT_PRIVILEGES from information_schema.OPTIMIZER_TRACE;
125 QUERY length(TRACE) INSUFFICIENT_PRIVILEGES
126 0 1
127 set optimizer_trace_offset=0,optimizer_trace_limit=100;
128 select f1();
129 f1()
130 36
131 select QUERY, TRACE, INSUFFICIENT_PRIVILEGES from information_schema.OPTIMIZER_TRACE;
132 QUERY TRACE INSUFFICIENT_PRIVILEGES
133 1
134 set optimizer_trace_offset=0,optimizer_trace_limit=100;
135 select * from v1;
136 a
137 first
138 root@localhost
139 root@localhost
140 root@localhost
141 root@localhost
142 select QUERY, length(TRACE), INSUFFICIENT_PRIVILEGES from information_schema.OPTIMIZER_TRACE;
143 QUERY length(TRACE) INSUFFICIENT_PRIVILEGES
144 0 1
145 set optimizer_trace_offset=0,optimizer_trace_limit=100;
146 insert into t2 values(current_user());
147 select QUERY, TRACE, INSUFFICIENT_PRIVILEGES from information_schema.OPTIMIZER_TRACE;
148 QUERY TRACE INSUFFICIENT_PRIVILEGES
149 1
150
151 # Verify that user1 cannot circumvent security checks by
152 # setting @@optimizer_trace_offset so that I_S output is disabled
153 # before the object (routine) is checked, and enabled in the
154 # middle of object usage, when 'offset' is passed.
155
156 set optimizer_trace_offset=2,optimizer_trace_limit=1;
157 call p1();
158 # Even though the routine's execution started before
159 # 'offset', it detected the security context changes. So the
160 # trace of CALL gets the "missing privilege" mark but we don't
161 # see it as CALL was before 'offset'.
162 select QUERY, TRACE, INSUFFICIENT_PRIVILEGES from information_schema.OPTIMIZER_TRACE;
163 QUERY TRACE INSUFFICIENT_PRIVILEGES
164
165 # Finally, verify that if the routine's definer does modify
166 # @@optimizer_trace from "enabled=off" to "enabled=on", in the
167 # body of the routine, then tracing works. This is no security
168 # issue, as it was done by the routine's definer.
169
170 select user();
171 user()
172 root@localhost
173 create procedure p2() sql security definer
174 begin
175 declare b int;
176 set optimizer_trace="enabled=on";
177 select 22 into b from dual;
178 end|
179
180 select user();
181 user()
182 user1@localhost
183 set optimizer_trace="enabled=off";
184 set optimizer_trace_offset=0,optimizer_trace_limit=100;
185 call p2();
186 select QUERY, TRACE, INSUFFICIENT_PRIVILEGES from information_schema.OPTIMIZER_TRACE;
187 QUERY TRACE INSUFFICIENT_PRIVILEGES
188 select 22 into b from dual {
189 "steps": [
190 {
191 "join_preparation": {
192 "select#": 1,
193 "steps": [
194 {
195 "expanded_query": "/* select#1 */ select 22 AS `22`"
196 }
197 ]
198 }
199 },
200 {
201 "join_optimization": {
202 "select#": 1,
203 "steps": [
204 ]
205 }
206 },
207 {
208 "join_execution": {
209 "select#": 1,
210 "steps": [
211 ]
212 }
213 }
214 ]
215 } 0
216 # Variable is as set by the routine
217 select @@optimizer_trace;
218 @@optimizer_trace
219 enabled=on,one_line=off
220
221 # ==========================================================
222 # Part B.
223 # Do same tests but with SQL SECURITY INVOKER objects, to verify that
224 # the restriction on security context changes is not present.
225 # ==========================================================
226
227 select user();
228 user()
229 root@localhost
230 alter procedure p1 sql security invoker;
231 alter function f1 sql security invoker;
232 alter sql security invoker view v1 as select * from t1;
233 # Triggers cannot be SQL SECURITY INVOKER so we don't test
234 # them here.
235 alter procedure p2 sql security invoker;
236 delete from t1 where a<>"first";
237
238 select user();
239 user()
240 user1@localhost
241 set optimizer_trace_offset=0,optimizer_trace_limit=100;
242 call p1();
243 select QUERY, length(TRACE), INSUFFICIENT_PRIVILEGES from information_schema.OPTIMIZER_TRACE;
244 QUERY length(TRACE) INSUFFICIENT_PRIVILEGES
245 call p1() 20 0
246 set b@0 NULL 20 0
247 jump_if_not 3(3) (select count(0) from `somedb`.`t1`) 3065 0
248 select 22 into b from dual 407 0
249 select a into b from t1 limit 1 2253 0
250 insert into t1 values(current_user()) 20 0
251 set optimizer_trace_offset=0,optimizer_trace_limit=100;
252 select f1();
253 f1()
254 36
255 select QUERY, length(TRACE), INSUFFICIENT_PRIVILEGES from information_schema.OPTIMIZER_TRACE;
256 QUERY length(TRACE) INSUFFICIENT_PRIVILEGES
257 select f1() 413 0
258 set b@0 NULL 20 0
259 select 48 into b from dual 407 0
260 select a into b from t1 limit 1 2253 0
261 insert into t1 values(current_user()) 20 0
262 freturn 3 36 20 0
263 set optimizer_trace_offset=0,optimizer_trace_limit=100;
264 select * from v1;
265 a
266 first
267 user1@localhost
268 user1@localhost
269 select QUERY, length(TRACE), INSUFFICIENT_PRIVILEGES from information_schema.OPTIMIZER_TRACE;
270 QUERY length(TRACE) INSUFFICIENT_PRIVILEGES
271 select * from v1 2920 0
272 set optimizer_trace_offset=2,optimizer_trace_limit=1;
273 call p1();
274 select QUERY, length(TRACE), INSUFFICIENT_PRIVILEGES from information_schema.OPTIMIZER_TRACE;
275 QUERY length(TRACE) INSUFFICIENT_PRIVILEGES
276 jump_if_not 3(3) (select count(0) from `somedb`.`t1`) 2868 0
277 set optimizer_trace="enabled=off";
278 set optimizer_trace_offset=0,optimizer_trace_limit=100;
279 call p2();
280 # SELECT substatement is traced (no security context change)
281 select QUERY, TRACE, INSUFFICIENT_PRIVILEGES from information_schema.OPTIMIZER_TRACE;
282 QUERY TRACE INSUFFICIENT_PRIVILEGES
283 select 22 into b from dual {
284 "steps": [
285 {
286 "join_preparation": {
287 "select#": 1,
288 "steps": [
289 {
290 "expanded_query": "/* select#1 */ select 22 AS `22`"
291 }
292 ]
293 }
294 },
295 {
296 "join_optimization": {
297 "select#": 1,
298 "steps": [
299 ]
300 }
301 },
302 {
303 "join_execution": {
304 "select#": 1,
305 "steps": [
306 ]
307 }
308 }
309 ]
310 } 0
311 select @@optimizer_trace;
312 @@optimizer_trace
313 enabled=on,one_line=off
314
315 # ==========================================================
316 # Part C.
317 # User1 got traces. Determine the minimum set of privileges he
318 # needed for that.
319 # ==========================================================
320
321 drop procedure p2;
322 select user();
323 user()
324 root@localhost
325 revoke all privileges, grant option from user1@localhost;
326 # Grant minimum privileges to use the routines and views,
327 # without considering optimizer trace:
328 grant execute on procedure p1 to user1@localhost;
329 grant execute on function f1 to user1@localhost;
330 grant select (a) on v1 to user1@localhost;
331 # Objects above are SQL SECURITY INVOKER, so invoker needs
332 # privileges on objects used internally:
333 grant select (a) on t1 to user1@localhost;
334 grant insert (a) on t1 to user1@localhost;
335 delete from t1 where a<>"first";
336
337 select user();
338 user()
339 user1@localhost
340 set optimizer_trace="enabled=on";
341 show grants;
342 Grants for user1@localhost
343 GRANT EXECUTE ON FUNCTION `somedb`.`f1` TO `user1`@`localhost`
344 GRANT EXECUTE ON PROCEDURE `somedb`.`p1` TO `user1`@`localhost`
345 GRANT SELECT (`a`) ON `somedb`.`v1` TO `user1`@`localhost`
346 GRANT SELECT (`a`), INSERT (`a`) ON `somedb`.`t1` TO `user1`@`localhost`
347 GRANT USAGE ON *.* TO `user1`@`localhost`
348
349 # Those privileges are not enough to see traces:
350 set optimizer_trace_offset=0,optimizer_trace_limit=100;
351 call p1();
352 # In CALL we execute stored procedure and notice that body should
353 # not be exposed. The trace of this CALL would not expose the
354 # body. Trace of substatements would. But, due to
355 # implementation, CALL is hidden.
356 select QUERY, length(TRACE), INSUFFICIENT_PRIVILEGES from information_schema.OPTIMIZER_TRACE;
357 QUERY length(TRACE) INSUFFICIENT_PRIVILEGES
358 0 1
359 set optimizer_trace_offset=0,optimizer_trace_limit=100;
360 select f1();
361 f1()
362 36
363 # SELECT is hidden (same reason as for CALL).
364 # Ps-protocol-specific note: preparation of SELECT above does not
365 # execute f1, so does not risk exposing body, so its trace is
366 # visible.
367 select QUERY, TRACE, INSUFFICIENT_PRIVILEGES from information_schema.OPTIMIZER_TRACE;
368 QUERY TRACE INSUFFICIENT_PRIVILEGES
369 1
370 set optimizer_trace_offset=0,optimizer_trace_limit=100;
371 select * from v1;
372 a
373 first
374 user1@localhost
375 user1@localhost
376 # Cannot see anything as it would expose body of view
377 select QUERY, length(TRACE), INSUFFICIENT_PRIVILEGES from information_schema.OPTIMIZER_TRACE;
378 QUERY length(TRACE) INSUFFICIENT_PRIVILEGES
379 0 1
380
381 # C.0) Add more privileges:
382
383 select user();
384 user()
385 root@localhost
386 # - for use of t1 in routines and view:
387 grant select on t1 to user1@localhost;
388 # - for use of view:
389 grant select, show view on v1 to user1@localhost;
390 delete from t1 where a<>"first";
391
392 select user();
393 user()
394 user1@localhost
395 set optimizer_trace_offset=0,optimizer_trace_limit=100;
396 call p1();
397 select QUERY, length(TRACE), INSUFFICIENT_PRIVILEGES from information_schema.OPTIMIZER_TRACE;
398 QUERY length(TRACE) INSUFFICIENT_PRIVILEGES
399 0 1
400 # Trace exposed body of routine, and content of t1, which we
401 # could see anyway:
402 show create procedure p1;
403 Procedure sql_mode Create Procedure character_set_client collation_connection Database Collation
404 p1 ONLY_FULL_GROUP_BY,NO_ENGINE_SUBSTITUTION NULL utf8mb4 utf8mb4_0900_ai_ci utf8mb4_0900_ai_ci
405 select * from t1 limit 1;
406 a
407 first
408 set optimizer_trace_offset=0,optimizer_trace_limit=100;
409 select f1();
410 f1()
411 36
412 select QUERY, length(TRACE), INSUFFICIENT_PRIVILEGES from information_schema.OPTIMIZER_TRACE;
413 QUERY length(TRACE) INSUFFICIENT_PRIVILEGES
414 0 1
415 # Trace exposed body of routine, and content of t1, which we
416 # could see anyway:
417 show create function f1;
418 Function sql_mode Create Function character_set_client collation_connection Database Collation
419 f1 ONLY_FULL_GROUP_BY,NO_ENGINE_SUBSTITUTION NULL utf8mb4 utf8mb4_0900_ai_ci utf8mb4_0900_ai_ci
420 set optimizer_trace_offset=0,optimizer_trace_limit=100;
421 select * from v1;
422 a
423 first
424 user1@localhost
425 user1@localhost
426 select QUERY, length(TRACE), INSUFFICIENT_PRIVILEGES from information_schema.OPTIMIZER_TRACE;
427 QUERY length(TRACE) INSUFFICIENT_PRIVILEGES
428 select * from v1 2920 0
429 # Trace exposed body of view, and content of t1, which we
430 # could see anyway:
431 show create view v1;
432 View Create View character_set_client collation_connection
433 v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY INVOKER VIEW `v1` AS select `t1`.`a` AS `a` from `t1` utf8mb4 utf8mb4_0900_ai_ci
434
435 # Now remove each privilege to verify that it was needed:
436 # C.1) remove table-level SELECT privilege on t1
437
438 select user();
439 user()
440 root@localhost
441 revoke select on t1 from user1@localhost;
442 grant select (a) on t1 to user1@localhost;
443 delete from t1 where a<>"first";
444
445 select user();
446 user()
447 user1@localhost
448 set optimizer_trace_offset=0,optimizer_trace_limit=100;
449 call p1();
450 # Cannot see those substatements which use t1
451 select QUERY, TRACE, INSUFFICIENT_PRIVILEGES from information_schema.OPTIMIZER_TRACE;
452 QUERY TRACE INSUFFICIENT_PRIVILEGES
453 1
454 set optimizer_trace_offset=0,optimizer_trace_limit=100;
455 select f1();
456 f1()
457 36
458 # Cannot see those substatements which use t1
459 select QUERY, length(TRACE), INSUFFICIENT_PRIVILEGES from information_schema.OPTIMIZER_TRACE;
460 QUERY length(TRACE) INSUFFICIENT_PRIVILEGES
461 0 1
462 # Trace exposed body of routine, which we could see anyway:
463 set optimizer_trace="enabled=off";
464 show create function f1;
465 Function sql_mode Create Function character_set_client collation_connection Database Collation
466 f1 ONLY_FULL_GROUP_BY,NO_ENGINE_SUBSTITUTION NULL utf8mb4 utf8mb4_0900_ai_ci utf8mb4_0900_ai_ci
467 set optimizer_trace="enabled=on";
468 set optimizer_trace_offset=0,optimizer_trace_limit=100;
469 select * from v1;
470 a
471 first
472 user1@localhost
473 user1@localhost
474 # Cannot see anything as it might expose some data from columns
475 # of t1
476 select QUERY, length(TRACE), INSUFFICIENT_PRIVILEGES from information_schema.OPTIMIZER_TRACE;
477 QUERY length(TRACE) INSUFFICIENT_PRIVILEGES
478 0 1
479
480 # C.2) remove table-level SELECT privilege on view
481
482 select user();
483 user()
484 root@localhost
485 # Put back privilege removed in C.1
486 grant select on t1 to user1@localhost;
487 # And remove a next one:
488 revoke select on v1 from user1@localhost;
489 grant select (a) on v1 to user1@localhost;
490 delete from t1 where a<>"first";
491
492 select user();
493 user()
494 user1@localhost
495 set optimizer_trace_offset=0,optimizer_trace_limit=100;
496 select * from v1;
497 a
498 first
499 # Cannot see anything as it might expose some data from columns
500 # of v1
501 select QUERY, length(TRACE), INSUFFICIENT_PRIVILEGES from information_schema.OPTIMIZER_TRACE;
502 QUERY length(TRACE) INSUFFICIENT_PRIVILEGES
503 0 1
504
505 # C.3) remove SHOW VIEW privilege on view
506
507 select user();
508 user()
509 root@localhost
510 # Put back privilege removed in C.3
511 grant select on v1 to user1@localhost;
512 # And remove a next one:
513 revoke show view on v1 from user1@localhost;
514 delete from t1 where a<>"first";
515
516 select user();
517 user()
518 user1@localhost
519 set optimizer_trace="enabled=off";
520 # We have no right to see view's body:
521 show create view v1;
522 ERROR 42000: SHOW VIEW command denied to user 'user1'@'localhost' for table 'v1'
523 set optimizer_trace="enabled=on";
524 # Verify that optimizer trace does not influence the privilege
525 # checking in SHOW CREATE:
526 show create view v1;
527 ERROR 42000: SHOW VIEW command denied to user 'user1'@'localhost' for table 'v1'
528 set optimizer_trace_offset=0,optimizer_trace_limit=100;
529 select * from v1;
530 a
531 first
532 # Cannot see anything as it would expose body of view
533 select QUERY, TRACE, INSUFFICIENT_PRIVILEGES from information_schema.OPTIMIZER_TRACE;
534 QUERY TRACE INSUFFICIENT_PRIVILEGES
535 1
536
537 # ==========================================================
538 # Part D.
539 # Like Part C, but instead of SQL SECURITY INVOKER objects
540 # created by root and used by User1, let's have SQL SECURITY
541 # DEFINER objects created and used by User1. Determine the
542 # minimum set of privileges he needs for that.
543 # ==========================================================
544
545 select user();
546 user()
547 root@localhost
548 drop procedure p1;
549 drop function f1;
550 drop view v1;
551 drop trigger trg2;
552 revoke all privileges, grant option from user1@localhost;
553 # Grant minimum privileges to create and use objects,
554 # without considering optimizer trace:
555 grant create routine on somedb.* to user1@localhost;
556 grant trigger on t2 to user1@localhost;
557 grant create view on somedb.* to user1@localhost;
558 grant select (a) on t1 to user1@localhost;
559 grant insert (a) on t1 to user1@localhost;
560 grant insert (a) on t2 to user1@localhost;
561 grant select (a) on t3 to user1@localhost;
562 grant insert (a) on t3 to user1@localhost;
563 delete from t1 where a<>"first";
564
565 select user();
566 user()
567 user1@localhost
568 set optimizer_trace="enabled=on";
569 SET sql_mode = 'ONLY_FULL_GROUP_BY,NO_ENGINE_SUBSTITUTION';
570 create procedure p1() sql security definer
571 begin
572 declare b int;
573 if (select count(*) from t1)
574 then
575 select 22 into b from dual;
576 end if;
577 select a into b from t1 limit 1;
578 insert into t1 values(current_user());
579 end|
580 create function f1() returns int sql security definer
581 begin
582 declare b int;
583 select 48 into b from dual;
584 select a into b from t1 limit 1;
585 insert into t1 values(current_user());
586 return 36;
587 end|
588 create trigger trg2 before insert on t2 for each row
589 begin
590 insert into t3 select * from t3;
591 end|
592 create sql security definer view v1 as select * from t1;
593 SET sql_mode = default;
594 # Creating a view is not enough to be able to SELECT it...
595 select user();
596 user()
597 root@localhost
598 grant select (a) on v1 to user1@localhost;
599
600 select user();
601 user()
602 user1@localhost
603 # Those privileges are not enough to see traces:
604 set optimizer_trace_offset=0,optimizer_trace_limit=100;
605 call p1();
606 # Can see body of routine (as definer), but not statements using t1
607 select QUERY, length(TRACE), INSUFFICIENT_PRIVILEGES from information_schema.OPTIMIZER_TRACE;
608 QUERY length(TRACE) INSUFFICIENT_PRIVILEGES
609 call p1() 20 0
610 set b@0 NULL 20 0
611 0 1
612 select 22 into b from dual 407 0
613 0 1
614 0 1
615 set optimizer_trace_offset=0,optimizer_trace_limit=100;
616 select f1();
617 f1()
618 36
619 # Can see body of routine (as definer), but not statements using t1
620 select QUERY, length(TRACE), INSUFFICIENT_PRIVILEGES from information_schema.OPTIMIZER_TRACE;
621 QUERY length(TRACE) INSUFFICIENT_PRIVILEGES
622 select f1() 413 0
623 set b@0 NULL 20 0
624 select 48 into b from dual 407 0
625 0 1
626 0 1
627 freturn 3 36 20 0
628 show create function f1;
629 Function sql_mode Create Function character_set_client collation_connection Database Collation
630 f1 ONLY_FULL_GROUP_BY,NO_ENGINE_SUBSTITUTION CREATE DEFINER=`user1`@`localhost` FUNCTION `f1`() RETURNS int
631 begin
632 declare b int;
633 select 48 into b from dual;
634 select a into b from t1 limit 1;
635 insert into t1 values(current_user());
636 return 36;
637 end utf8mb4 utf8mb4_0900_ai_ci utf8mb4_0900_ai_ci
638 set optimizer_trace_offset=0,optimizer_trace_limit=100;
639 select * from v1;
640 a
641 first
642 user1@localhost
643 user1@localhost
644 # Cannot see anything as it might expose some data from columns
645 # of t1
646 select QUERY, length(TRACE), INSUFFICIENT_PRIVILEGES from information_schema.OPTIMIZER_TRACE;
647 QUERY length(TRACE) INSUFFICIENT_PRIVILEGES
648 0 1
649 set optimizer_trace_offset=0,optimizer_trace_limit=100;
650 insert into t2 values(current_user());
651 # Cannot see anything as it might expose some data from
652 # columns of t2
653 select QUERY, length(TRACE), INSUFFICIENT_PRIVILEGES from information_schema.OPTIMIZER_TRACE;
654 QUERY length(TRACE) INSUFFICIENT_PRIVILEGES
655 0 1
656 # Also test a query accessing t1 in FROM clause:
657 set optimizer_trace_offset=0,optimizer_trace_limit=100;
658 select a from (select a from t1 where a like "f%") as tt where a like "fi%";
659 a
660 first
661 select QUERY, length(TRACE), INSUFFICIENT_PRIVILEGES from information_schema.OPTIMIZER_TRACE;
662 QUERY length(TRACE) INSUFFICIENT_PRIVILEGES
663 0 1
664
665 # D.0) Add more privileges:
666
667 select user();
668 user()
669 root@localhost
670 # - for use of t1 in routines and view:
671 grant select on t1 to user1@localhost;
672 # - for use of view:
673 grant select, show view on v1 to user1@localhost;
674 # - for use of trigger
675 grant select on t2 to user1@localhost;
676 grant select on t3 to user1@localhost;
677 delete from t1 where a<>"first";
678
679 select user();
680 user()
681 user1@localhost
682 set optimizer_trace_offset=0,optimizer_trace_limit=100;
683 call p1();
684 select QUERY, length(TRACE), INSUFFICIENT_PRIVILEGES from information_schema.OPTIMIZER_TRACE;
685 QUERY length(TRACE) INSUFFICIENT_PRIVILEGES
686 call p1() 20 0
687 set b@0 NULL 20 0
688 jump_if_not 3(3) (select count(0) from `somedb`.`t1`) 2868 0
689 select 22 into b from dual 218 0
690 select a into b from t1 limit 1 2041 0
691 insert into t1 values(current_user()) 20 0
692 # Trace exposed body of routine, and content of t1, which we
693 # could see anyway:
694 show create procedure p1;
695 Procedure sql_mode Create Procedure character_set_client collation_connection Database Collation
696 p1 ONLY_FULL_GROUP_BY,NO_ENGINE_SUBSTITUTION CREATE DEFINER=`user1`@`localhost` PROCEDURE `p1`()
697 begin
698 declare b int;
699 if (select count(*) from t1)
700 then
701 select 22 into b from dual;
702 end if;
703 select a into b from t1 limit 1;
704 insert into t1 values(current_user());
705 end utf8mb4 utf8mb4_0900_ai_ci utf8mb4_0900_ai_ci
706 select * from t1 limit 1;
707 a
708 first
709 set optimizer_trace_offset=0,optimizer_trace_limit=100;
710 select f1();
711 f1()
712 36
713 select QUERY, length(TRACE), INSUFFICIENT_PRIVILEGES from information_schema.OPTIMIZER_TRACE;
714 QUERY length(TRACE) INSUFFICIENT_PRIVILEGES
715 select f1() 413 0
716 set b@0 NULL 20 0
717 select 48 into b from dual 218 0
718 select a into b from t1 limit 1 2041 0
719 insert into t1 values(current_user()) 20 0
720 freturn 3 36 20 0
721 # Trace exposed body of routine, and content of t1, which we
722 # could see anyway:
723 show create function f1;
724 Function sql_mode Create Function character_set_client collation_connection Database Collation
725 f1 ONLY_FULL_GROUP_BY,NO_ENGINE_SUBSTITUTION CREATE DEFINER=`user1`@`localhost` FUNCTION `f1`() RETURNS int
726 begin
727 declare b int;
728 select 48 into b from dual;
729 select a into b from t1 limit 1;
730 insert into t1 values(current_user());
731 return 36;
732 end utf8mb4 utf8mb4_0900_ai_ci utf8mb4_0900_ai_ci
733 set optimizer_trace_offset=0,optimizer_trace_limit=100;
734 select * from v1;
735 a
736 first
737 user1@localhost
738 user1@localhost
739 select QUERY, length(TRACE), INSUFFICIENT_PRIVILEGES from information_schema.OPTIMIZER_TRACE;
740 QUERY length(TRACE) INSUFFICIENT_PRIVILEGES
741 select * from v1 2920 0
742 # Trace exposed body of view, and content of t1, which we
743 # could see anyway:
744 show create view v1;
745 View Create View character_set_client collation_connection
746 v1 CREATE ALGORITHM=UNDEFINED DEFINER=`user1`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `t1`.`a` AS `a` from `t1` utf8mb4 utf8mb4_0900_ai_ci
747 set optimizer_trace_offset=0,optimizer_trace_limit=100;
748 insert into t2 values(current_user());
749 select QUERY, length(TRACE), INSUFFICIENT_PRIVILEGES from information_schema.OPTIMIZER_TRACE;
750 QUERY length(TRACE) INSUFFICIENT_PRIVILEGES
751 insert into t2 values(current_user()) 20 0
752 insert into t3 select * from t3 2881 0
753 # Trace exposed body of trigger, and content of t2/t3, which we
754 # could see anyway:
755 show create trigger trg2;
756 Trigger sql_mode SQL Original Statement character_set_client collation_connection Database Collation Created
757 trg2 ONLY_FULL_GROUP_BY,NO_ENGINE_SUBSTITUTION CREATE DEFINER=`user1`@`localhost` TRIGGER `trg2` BEFORE INSERT ON `t2` FOR EACH ROW begin
758 insert into t3 select * from t3;
759 end utf8mb4 utf8mb4_0900_ai_ci utf8mb4_0900_ai_ci #
760 select * from t2, t3 order by t2.a, t3.a limit 1;
761 a a
762 first first
763 # Trace exposed content of t1 which we could see anyway:
764 set optimizer_trace_offset=0,optimizer_trace_limit=100;
765 select a from (select a from t1 where a like "f%") as tt where a like "fi%";
766 a
767 first
768 select QUERY, length(TRACE), INSUFFICIENT_PRIVILEGES from information_schema.OPTIMIZER_TRACE;
769 QUERY length(TRACE) INSUFFICIENT_PRIVILEGES
770 select a from (select a from t1 where a like "f%") as tt where a like "fi%" 4451 0
771
772 # For routines, as they only use t1 and we added only one
773 # privilege on t1, we have nothing to remove.
774
775 # Now remove each privilege to verify that it was needed for
776 # the view.
777 # D.1) remove table-level SELECT privilege on v1
778
779 select user();
780 user()
781 root@localhost
782 revoke select on v1 from user1@localhost;
783 grant select (a) on v1 to user1@localhost;
784
785 select user();
786 user()
787 user1@localhost
788 set optimizer_trace_offset=0,optimizer_trace_limit=100;
789 select * from v1;
790 a
791 first
792 user1@localhost
793 user1@localhost
794 # Cannot see anything as it might expose some data from columns
795 # of v1
796 select QUERY, length(TRACE), INSUFFICIENT_PRIVILEGES from information_schema.OPTIMIZER_TRACE;
797 QUERY length(TRACE) INSUFFICIENT_PRIVILEGES
798 0 1
799
800 # D.2) remove table-level SHOW VIEW privilege on v1
801
802 select user();
803 user()
804 root@localhost
805 # Put back privilege removed in D.1
806 grant select on v1 to user1@localhost;
807 # And remove a next one:
808 revoke show view on v1 from user1@localhost;
809
810 select user();
811 user()
812 user1@localhost
813 # We have no right to see view's body:
814 show create view v1;
815 ERROR 42000: SHOW VIEW command denied to user 'user1'@'localhost' for table 'v1'
816 set optimizer_trace_offset=0,optimizer_trace_limit=100;
817 select * from v1;
818 a
819 first
820 user1@localhost
821 user1@localhost
822 # Cannot see anything as it would expose body of view
823 select QUERY, TRACE, INSUFFICIENT_PRIVILEGES from information_schema.OPTIMIZER_TRACE;
824 QUERY TRACE INSUFFICIENT_PRIVILEGES
825 1
826
827 # D.3) remove table-level SELECT privilege on t1
828
829 select user();
830 user()
831 root@localhost
832 # Put back privilege removed in D.2
833 grant show view on v1 to user1@localhost;
834 # And remove a next one:
835 revoke select on t1 from user1@localhost;
836 grant select (a) on t1 to user1@localhost;
837
838 select user();
839 user()
840 user1@localhost
841 set optimizer_trace_offset=0,optimizer_trace_limit=100;
842 select * from v1;
843 a
844 first
845 user1@localhost
846 user1@localhost
847 # Cannot see anything as it might expose some data from columns
848 # of t1
849 select QUERY, length(TRACE), INSUFFICIENT_PRIVILEGES from information_schema.OPTIMIZER_TRACE;
850 QUERY length(TRACE) INSUFFICIENT_PRIVILEGES
851 0 1
852
853 # Now remove each privilege to verify that it was needed for
854 # the trigger:
855 # D.4) remove table-level SELECT privilege on t2
856
857 select user();
858 user()
859 root@localhost
860 revoke select on t2 from user1@localhost;
861 grant select (a) on t2 to user1@localhost;
862
863 select user();
864 user()
865 user1@localhost
866 set optimizer_trace_offset=0,optimizer_trace_limit=100;
867 insert into t2 values(current_user());
868 # Cannot see anything as it might expose some data from
869 # columns of t2
870 select QUERY, length(TRACE), INSUFFICIENT_PRIVILEGES from information_schema.OPTIMIZER_TRACE;
871 QUERY length(TRACE) INSUFFICIENT_PRIVILEGES
872 0 1
873
874 # D.5) remove table-level SELECT privilege on t3
875
876
877 select user();
878 user()
879 root@localhost
880 # Put back privilege removed in D.4
881 grant select on t2 to user1@localhost;
882 # And remove a next one:
883 revoke select on t3 from user1@localhost;
884 grant select (a) on t3 to user1@localhost;
885
886 select user();
887 user()
888 user1@localhost
889 set optimizer_trace_offset=0,optimizer_trace_limit=100;
890 insert into t2 values(current_user());
891 # Cannot see substatement as it might expose some data from
892 # columns of t3
893 select QUERY, length(TRACE), INSUFFICIENT_PRIVILEGES from information_schema.OPTIMIZER_TRACE;
894 QUERY length(TRACE) INSUFFICIENT_PRIVILEGES
895 insert into t2 values(current_user()) 20 0
896 0 1
897
898 # Cleanup
899 select user();
900 user()
901 root@localhost
902 drop user user1@localhost;
903 Warnings:
904 Warning 4005 User 'user1'@'localhost' is referenced as a definer account in a view.
905 Warning 4005 User 'user1'@'localhost' is referenced as a definer account in a stored routine.
906 Warning 4005 User 'user1'@'localhost' is referenced as a definer account in a trigger.
907
908 # ==========================================================
909 # Part E.
910 # Misc tests.
911 # ==========================================================
912
913 select user();
914 user()
915 root@localhost
916 drop view v1;
917 create sql security definer view v1 as select * from t1 where 'secret';
918 create user user1@localhost identified by '';
919 Warnings:
920 Warning 4005 User 'user1'@'localhost' is referenced as a definer account in a stored routine.
921 Warning 4005 User 'user1'@'localhost' is referenced as a definer account in a trigger.
922 grant create, insert, select on somedb.* to user1@localhost;
923 grant create routine on somedb.* to user1@localhost;
924
925 select user();
926 user()
927 user1@localhost
928 user1 cannot see view's body:
929 show create view v1;
930 ERROR 42000: SHOW VIEW command denied to user 'user1'@'localhost' for table 'v1'
931 SET sql_mode = 'ONLY_FULL_GROUP_BY,NO_ENGINE_SUBSTITUTION';
932 user1 creates a procedure
933 create procedure proc() sql security definer
934 begin
935 set optimizer_trace="enabled=on";
936 set optimizer_trace_offset=0,optimizer_trace_limit=100;
937 select * from v1 limit 0;
938 create table leak select * from information_schema.optimizer_trace;
939 set optimizer_trace="enabled=off";
940 end|
941 SET sql_mode = default;
942 select user();
943 user()
944 root@localhost
945 root runs procedure, without fear of risk as it is SQL SECURITY DEFINER
946 call proc();
947 a
948
949 select user();
950 user()
951 user1@localhost
952 user1 cannot see view's body:
953 select * from leak;
954 QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES
955
956 # Cleanup
957 select user();
958 user()
959 root@localhost
960 drop database somedb;
961 drop user user1@localhost;
962 set @@global.optimizer_trace_max_mem_size = @old_size;