Debian Codegraph @

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