opt_trace/r/security_ps_prot.result

set @old_size = @@global.optimizer_trace_max_mem_size;
set global optimizer_trace_max_mem_size=1048576;
select user();
user()
root@localhost
create database somedb;
use somedb;
create table t1(a varchar(100));
insert into t1 values("first");
create table t2(a varchar(100));
insert into t2 values("first");
create table t3(a varchar(100));
insert into t3 values("first");
SET sql_mode = 'ONLY_FULL_GROUP_BY,NO_ENGINE_SUBSTITUTION';
Warnings:
Warning	3090	Changing sql mode 'NO_AUTO_CREATE_USER' is deprecated. It will be removed in a future release.
create procedure p1() sql security definer
begin
declare b int;
if (select count(*) from t1)
then
select 22 into b from dual;
end if;
select a into b from t1 limit 1;
insert into t1 values(current_user());
end|
create function f1() returns int sql security definer
begin
declare b int;
select 48 into b from dual;
select a into b from t1 limit 1;
insert into t1 values(current_user());
return 36;
end|
create trigger trg2 before insert on t2 for each row
begin
insert into t3 select * from t3;
end|
SET sql_mode = default;
create sql security definer view v1 as select * from t1;
create user user1@localhost identified by '';
grant all on *.* to user1@localhost with grant option;

select user();
user()
user1@localhost
set optimizer_trace="enabled=on";
show grants;
Grants for user1@localhost
GRANT ALL PRIVILEGES ON *.* TO 'user1'@'localhost' WITH GRANT OPTION

# ==========================================================
# Part A.
# Test that security context changes are allowed when, and only
# when, invoker has all global privileges.
# ==========================================================

# Because invoker has all global privileges, all traces are visible:
set optimizer_trace_offset=0,optimizer_trace_limit=100;
call p1();
select QUERY, length(TRACE), INSUFFICIENT_PRIVILEGES from information_schema.OPTIMIZER_TRACE;
QUERY	length(TRACE)	INSUFFICIENT_PRIVILEGES
call p1()	20	0
set b@0 NULL	20	0
jump_if_not 3(3) (select count(0) from `somedb`.`t1`)	2731	0
select 22 into b from dual	407	0
select a into b from t1 limit 1	2164	0
insert into t1 values(current_user())	20	0
# this SET always purges all remembered traces
set optimizer_trace_offset=0,optimizer_trace_limit=100;
select f1();
f1()
36
select QUERY, length(TRACE), INSUFFICIENT_PRIVILEGES from information_schema.OPTIMIZER_TRACE;
QUERY	length(TRACE)	INSUFFICIENT_PRIVILEGES
select f1()	214	0
select f1()	413	0
set b@0 NULL	20	0
select 48 into b from dual	407	0
select a into b from t1 limit 1	2164	0
insert into t1 values(current_user())	20	0
freturn 3 36	20	0
set optimizer_trace_offset=0,optimizer_trace_limit=100;
select * from v1;
a
first
root@localhost
root@localhost
select QUERY, length(TRACE), INSUFFICIENT_PRIVILEGES from information_schema.OPTIMIZER_TRACE;
QUERY	length(TRACE)	INSUFFICIENT_PRIVILEGES
select * from v1	898	0
select * from v1	2156	0
set optimizer_trace_offset=0,optimizer_trace_limit=100;
insert into t2 values(current_user());
select QUERY, length(TRACE), INSUFFICIENT_PRIVILEGES from information_schema.OPTIMIZER_TRACE;
QUERY	length(TRACE)	INSUFFICIENT_PRIVILEGES
insert into t2 values(current_user())	20	0
insert into t2 values(current_user())	20	0
insert into t3 select * from t3	2532	0

# Show that really all global privileges are needed: let root
# revoke just one from user1. Because user1 does not have all global
# privileges anymore, security context changes are forbidden,
# thus there is no trace.

select user();
user()
root@localhost
revoke shutdown on *.* from user1@localhost;

select user();
user()
user1@localhost
set optimizer_trace="enabled=on";
show grants;
Grants for user1@localhost
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 ON *.* TO 'user1'@'localhost' WITH GRANT OPTION
set optimizer_trace_offset=0,optimizer_trace_limit=100;
call p1();
# In CALL we execute stored procedure and notice a security
# context change. The context change is probably only relevant
# for substatements, but we still hide CALL. This is to be
# consistent with what we do when routine body should not be
# exposed. And it also feels safer to disable I_S output as
# soon as possible.
# Ps-protocol-specific note: mysqltest uses normal protocol for CALL
select QUERY, length(TRACE), INSUFFICIENT_PRIVILEGES from information_schema.OPTIMIZER_TRACE;
QUERY	length(TRACE)	INSUFFICIENT_PRIVILEGES
	0	1
set optimizer_trace_offset=0,optimizer_trace_limit=100;
select f1();
f1()
36
select QUERY, TRACE, INSUFFICIENT_PRIVILEGES from information_schema.OPTIMIZER_TRACE;
QUERY	TRACE	INSUFFICIENT_PRIVILEGES
select f1()	{
  "steps": [
    {
      "join_preparation": {
        "select#": 1,
        "steps": [
          {
            "expanded_query": "/* select#1 */ select `f1`() AS `f1()`"
          }
        ]
      }
    }
  ]
}	0
		1
set optimizer_trace_offset=0,optimizer_trace_limit=100;
select * from v1;
a
first
root@localhost
root@localhost
root@localhost
root@localhost
select QUERY, length(TRACE), INSUFFICIENT_PRIVILEGES from information_schema.OPTIMIZER_TRACE;
QUERY	length(TRACE)	INSUFFICIENT_PRIVILEGES
	0	1
	0	1
set optimizer_trace_offset=0,optimizer_trace_limit=100;
insert into t2 values(current_user());
select QUERY, TRACE, INSUFFICIENT_PRIVILEGES from information_schema.OPTIMIZER_TRACE;
QUERY	TRACE	INSUFFICIENT_PRIVILEGES
insert into t2 values(current_user())	{
  "steps": [
  ]
}	0
		1

# Verify that user1 cannot circumvent security checks by
# setting @@optimizer_trace_offset so that I_S output is disabled
# before the object (routine) is checked, and enabled in the
# middle of object usage, when 'offset' is passed.

set optimizer_trace_offset=2,optimizer_trace_limit=1;
call p1();
# Even though the routine's execution started before
# 'offset', it detected the security context changes. So the
# trace of CALL gets the "missing privilege" mark but we don't
# see it as CALL was before 'offset'.
select QUERY, TRACE, INSUFFICIENT_PRIVILEGES from information_schema.OPTIMIZER_TRACE;
QUERY	TRACE	INSUFFICIENT_PRIVILEGES

# Finally, verify that if the routine's definer does modify
# @@optimizer_trace from "enabled=off" to "enabled=on", in the
# body of the routine, then tracing works. This is no security
# issue, as it was done by the routine's definer.

select user();
user()
root@localhost
create procedure p2() sql security definer
begin
declare b int;
set optimizer_trace="enabled=on";
select 22 into b from dual;
end|

select user();
user()
user1@localhost
set optimizer_trace="enabled=off";
set optimizer_trace_offset=0,optimizer_trace_limit=100;
call p2();
select QUERY, TRACE, INSUFFICIENT_PRIVILEGES from information_schema.OPTIMIZER_TRACE;
QUERY	TRACE	INSUFFICIENT_PRIVILEGES
select 22 into b from dual	{
  "steps": [
    {
      "join_preparation": {
        "select#": 1,
        "steps": [
          {
            "expanded_query": "/* select#1 */ select 22 AS `22`"
          }
        ]
      }
    },
    {
      "join_optimization": {
        "select#": 1,
        "steps": [
        ]
      }
    },
    {
      "join_execution": {
        "select#": 1,
        "steps": [
        ]
      }
    }
  ]
}	0
# Variable is as set by the routine
select @@optimizer_trace;
@@optimizer_trace
enabled=on,one_line=off

# ==========================================================
# Part B.
# Do same tests but with SQL SECURITY INVOKER objects, to verify that
# the restriction on security context changes is not present.
# ==========================================================

select user();
user()
root@localhost
alter procedure p1 sql security invoker;
alter function f1 sql security invoker;
alter sql security invoker view v1 as select * from t1;
# Triggers cannot be SQL SECURITY INVOKER so we don't test
# them here.
alter procedure p2 sql security invoker;
delete from t1 where a<>"first";

select user();
user()
user1@localhost
set optimizer_trace_offset=0,optimizer_trace_limit=100;
call p1();
select QUERY, length(TRACE), INSUFFICIENT_PRIVILEGES from information_schema.OPTIMIZER_TRACE;
QUERY	length(TRACE)	INSUFFICIENT_PRIVILEGES
call p1()	20	0
set b@0 NULL	20	0
jump_if_not 3(3) (select count(0) from `somedb`.`t1`)	2731	0
select 22 into b from dual	407	0
select a into b from t1 limit 1	2164	0
insert into t1 values(current_user())	20	0
set optimizer_trace_offset=0,optimizer_trace_limit=100;
select f1();
f1()
36
select QUERY, length(TRACE), INSUFFICIENT_PRIVILEGES from information_schema.OPTIMIZER_TRACE;
QUERY	length(TRACE)	INSUFFICIENT_PRIVILEGES
select f1()	214	0
select f1()	413	0
set b@0 NULL	20	0
select 48 into b from dual	407	0
select a into b from t1 limit 1	2164	0
insert into t1 values(current_user())	20	0
freturn 3 36	20	0
set optimizer_trace_offset=0,optimizer_trace_limit=100;
select * from v1;
a
first
user1@localhost
user1@localhost
select QUERY, length(TRACE), INSUFFICIENT_PRIVILEGES from information_schema.OPTIMIZER_TRACE;
QUERY	length(TRACE)	INSUFFICIENT_PRIVILEGES
select * from v1	898	0
select * from v1	2156	0
set optimizer_trace_offset=2,optimizer_trace_limit=1;
call p1();
select QUERY, length(TRACE), INSUFFICIENT_PRIVILEGES from information_schema.OPTIMIZER_TRACE;
QUERY	length(TRACE)	INSUFFICIENT_PRIVILEGES
jump_if_not 3(3) (select count(0) from `somedb`.`t1`)	2731	0
set optimizer_trace="enabled=off";
set optimizer_trace_offset=0,optimizer_trace_limit=100;
call p2();
# SELECT substatement is traced (no security context change)
select QUERY, TRACE, INSUFFICIENT_PRIVILEGES from information_schema.OPTIMIZER_TRACE;
QUERY	TRACE	INSUFFICIENT_PRIVILEGES
select 22 into b from dual	{
  "steps": [
    {
      "join_preparation": {
        "select#": 1,
        "steps": [
          {
            "expanded_query": "/* select#1 */ select 22 AS `22`"
          }
        ]
      }
    },
    {
      "join_optimization": {
        "select#": 1,
        "steps": [
        ]
      }
    },
    {
      "join_execution": {
        "select#": 1,
        "steps": [
        ]
      }
    }
  ]
}	0
select @@optimizer_trace;
@@optimizer_trace
enabled=on,one_line=off

# ==========================================================
# Part C.
# User1 got traces. Determine the minimum set of privileges he
# needed for that.
# ==========================================================

drop procedure p2;
select user();
user()
root@localhost
revoke all privileges, grant option from user1@localhost;
# Grant minimum privileges to use the routines and views,
# without considering optimizer trace:
grant execute on procedure p1 to user1@localhost;
grant execute on function f1 to user1@localhost;
grant select (a) on v1 to user1@localhost;
# Objects above are SQL SECURITY INVOKER, so invoker needs
# privileges on objects used internally:
grant select (a) on t1 to user1@localhost;
grant insert (a) on t1 to user1@localhost;
delete from t1 where a<>"first";

select user();
user()
user1@localhost
set optimizer_trace="enabled=on";
show grants;
Grants for user1@localhost
GRANT EXECUTE ON FUNCTION `somedb`.`f1` TO 'user1'@'localhost'
GRANT EXECUTE ON PROCEDURE `somedb`.`p1` TO 'user1'@'localhost'
GRANT SELECT (a) ON `somedb`.`v1` TO 'user1'@'localhost'
GRANT SELECT (a), INSERT (a) ON `somedb`.`t1` TO 'user1'@'localhost'
GRANT USAGE ON *.* TO 'user1'@'localhost'

# Those privileges are not enough to see traces:
set optimizer_trace_offset=0,optimizer_trace_limit=100;
call p1();
# In CALL we execute stored procedure and notice that body should
# not be exposed. The trace of this CALL would not expose the
# body. Trace of substatements would. But, due to
# implementation, CALL is hidden.
select QUERY, length(TRACE), INSUFFICIENT_PRIVILEGES from information_schema.OPTIMIZER_TRACE;
QUERY	length(TRACE)	INSUFFICIENT_PRIVILEGES
	0	1
set optimizer_trace_offset=0,optimizer_trace_limit=100;
select f1();
f1()
36
# SELECT is hidden (same reason as for CALL).
# Ps-protocol-specific note: preparation of SELECT above does not
# execute f1, so does not risk exposing body, so its trace is
# visible.
select QUERY, TRACE, INSUFFICIENT_PRIVILEGES from information_schema.OPTIMIZER_TRACE;
QUERY	TRACE	INSUFFICIENT_PRIVILEGES
select f1()	{
  "steps": [
    {
      "join_preparation": {
        "select#": 1,
        "steps": [
          {
            "expanded_query": "/* select#1 */ select `f1`() AS `f1()`"
          }
        ]
      }
    }
  ]
}	0
		1
set optimizer_trace_offset=0,optimizer_trace_limit=100;
select * from v1;
a
first
user1@localhost
user1@localhost
# Cannot see anything as it would expose body of view
select QUERY, length(TRACE), INSUFFICIENT_PRIVILEGES from information_schema.OPTIMIZER_TRACE;
QUERY	length(TRACE)	INSUFFICIENT_PRIVILEGES
	0	1
	0	1

# C.0) Add more privileges:

select user();
user()
root@localhost
# - for use of t1 in routines and view:
grant select on t1 to user1@localhost;
# - for use of routines:
grant select on mysql.proc to user1@localhost;
# - for use of view:
grant select, show view on v1 to user1@localhost;
delete from t1 where a<>"first";

select user();
user()
user1@localhost
set optimizer_trace_offset=0,optimizer_trace_limit=100;
call p1();
select QUERY, length(TRACE), INSUFFICIENT_PRIVILEGES from information_schema.OPTIMIZER_TRACE;
QUERY	length(TRACE)	INSUFFICIENT_PRIVILEGES
call p1()	20	0
set b@0 NULL	20	0
jump_if_not 3(3) (select count(0) from `somedb`.`t1`)	2731	0
select 22 into b from dual	407	0
select a into b from t1 limit 1	2164	0
insert into t1 values(current_user())	20	0
# Trace exposed body of routine, and content of t1, which we
# could see anyway:
show create procedure p1;
Procedure	sql_mode	Create Procedure	character_set_client	collation_connection	Database Collation
p1	ONLY_FULL_GROUP_BY,NO_ENGINE_SUBSTITUTION	CREATE DEFINER=`root`@`localhost` PROCEDURE `p1`()
    SQL SECURITY INVOKER
begin
declare b int;
if (select count(*) from t1)
then
select 22 into b from dual;
end if;
select a into b from t1 limit 1;
insert into t1 values(current_user());
end	latin1	latin1_swedish_ci	latin1_swedish_ci
select * from t1 limit 1;
a
first
set optimizer_trace_offset=0,optimizer_trace_limit=100;
select f1();
f1()
36
select QUERY, length(TRACE), INSUFFICIENT_PRIVILEGES from information_schema.OPTIMIZER_TRACE;
QUERY	length(TRACE)	INSUFFICIENT_PRIVILEGES
select f1()	214	0
select f1()	413	0
set b@0 NULL	20	0
select 48 into b from dual	407	0
select a into b from t1 limit 1	2164	0
insert into t1 values(current_user())	20	0
freturn 3 36	20	0
# Trace exposed body of routine, and content of t1, which we
# could see anyway:
show create function f1;
Function	sql_mode	Create Function	character_set_client	collation_connection	Database Collation
f1	ONLY_FULL_GROUP_BY,NO_ENGINE_SUBSTITUTION	CREATE DEFINER=`root`@`localhost` FUNCTION `f1`() RETURNS int(11)
    SQL SECURITY INVOKER
begin
declare b int;
select 48 into b from dual;
select a into b from t1 limit 1;
insert into t1 values(current_user());
return 36;
end	latin1	latin1_swedish_ci	latin1_swedish_ci
set optimizer_trace_offset=0,optimizer_trace_limit=100;
select * from v1;
a
first
user1@localhost
user1@localhost
select QUERY, length(TRACE), INSUFFICIENT_PRIVILEGES from information_schema.OPTIMIZER_TRACE;
QUERY	length(TRACE)	INSUFFICIENT_PRIVILEGES
select * from v1	898	0
select * from v1	2156	0
# Trace exposed body of view, and content of t1, which we
# could see anyway:
show create view v1;
View	Create View	character_set_client	collation_connection
v1	CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY INVOKER VIEW `v1` AS select `t1`.`a` AS `a` from `t1`	latin1	latin1_swedish_ci

# Now remove each privilege to verify that it was needed:
# C.1) remove table-level SELECT privilege on t1

select user();
user()
root@localhost
revoke select on t1 from user1@localhost;
grant select (a) on t1 to user1@localhost;
delete from t1 where a<>"first";

select user();
user()
user1@localhost
set optimizer_trace_offset=0,optimizer_trace_limit=100;
call p1();
# Cannot see those substatements which use t1
select QUERY, TRACE, INSUFFICIENT_PRIVILEGES from information_schema.OPTIMIZER_TRACE;
QUERY	TRACE	INSUFFICIENT_PRIVILEGES
call p1()	{
  "steps": [
  ]
}	0
set b@0 NULL	{
  "steps": [
  ]
}	0
		1
select 22 into b from dual	{
  "steps": [
    {
      "join_preparation": {
        "select#": 1,
        "steps": [
          {
            "expanded_query": "/* select#1 */ select 22 AS `22`"
          }
        ]
      }
    },
    {
      "join_optimization": {
        "select#": 1,
        "steps": [
        ]
      }
    },
    {
      "join_execution": {
        "select#": 1,
        "steps": [
        ]
      }
    }
  ]
}	0
		1
		1
set optimizer_trace_offset=0,optimizer_trace_limit=100;
select f1();
f1()
36
# Cannot see those substatements which use t1
select QUERY, length(TRACE), INSUFFICIENT_PRIVILEGES from information_schema.OPTIMIZER_TRACE;
QUERY	length(TRACE)	INSUFFICIENT_PRIVILEGES
select f1()	214	0
select f1()	413	0
set b@0 NULL	20	0
select 48 into b from dual	407	0
	0	1
	0	1
freturn 3 36	20	0
# Trace exposed body of routine, which we could see anyway:
set optimizer_trace="enabled=off";
show create function f1;
Function	sql_mode	Create Function	character_set_client	collation_connection	Database Collation
f1	ONLY_FULL_GROUP_BY,NO_ENGINE_SUBSTITUTION	CREATE DEFINER=`root`@`localhost` FUNCTION `f1`() RETURNS int(11)
    SQL SECURITY INVOKER
begin
declare b int;
select 48 into b from dual;
select a into b from t1 limit 1;
insert into t1 values(current_user());
return 36;
end	latin1	latin1_swedish_ci	latin1_swedish_ci
set optimizer_trace="enabled=on";
set optimizer_trace_offset=0,optimizer_trace_limit=100;
select * from v1;
a
first
user1@localhost
user1@localhost
# Cannot see anything as it might expose some data from columns
# of t1
select QUERY, length(TRACE), INSUFFICIENT_PRIVILEGES from information_schema.OPTIMIZER_TRACE;
QUERY	length(TRACE)	INSUFFICIENT_PRIVILEGES
	0	1
	0	1

# C.2) remove table-level SELECT privilege on mysql.proc

select user();
user()
root@localhost
# Put back privilege removed in C.1
grant select on t1 to user1@localhost;
# And remove a next one:
revoke select on mysql.proc from user1@localhost;
delete from t1 where a<>"first";

select user();
user()
user1@localhost
# We have no right to see routines' bodies:
set optimizer_trace="enabled=off";
show create procedure p1;
Procedure	sql_mode	Create Procedure	character_set_client	collation_connection	Database Collation
p1	ONLY_FULL_GROUP_BY,NO_ENGINE_SUBSTITUTION	NULL	latin1	latin1_swedish_ci	latin1_swedish_ci
show create function f1;
Function	sql_mode	Create Function	character_set_client	collation_connection	Database Collation
f1	ONLY_FULL_GROUP_BY,NO_ENGINE_SUBSTITUTION	NULL	latin1	latin1_swedish_ci	latin1_swedish_ci
# Verify that optimizer trace does not influence the privilege
# checking in SHOW CREATE:
set optimizer_trace="enabled=on";
show create procedure p1;
Procedure	sql_mode	Create Procedure	character_set_client	collation_connection	Database Collation
p1	ONLY_FULL_GROUP_BY,NO_ENGINE_SUBSTITUTION	NULL	latin1	latin1_swedish_ci	latin1_swedish_ci
show create function f1;
Function	sql_mode	Create Function	character_set_client	collation_connection	Database Collation
f1	ONLY_FULL_GROUP_BY,NO_ENGINE_SUBSTITUTION	NULL	latin1	latin1_swedish_ci	latin1_swedish_ci
set optimizer_trace_offset=0,optimizer_trace_limit=100;
call p1();
# Cannot see anything as it would expose body of routine
select QUERY, length(TRACE), INSUFFICIENT_PRIVILEGES from information_schema.OPTIMIZER_TRACE;
QUERY	length(TRACE)	INSUFFICIENT_PRIVILEGES
	0	1
set optimizer_trace_offset=0,optimizer_trace_limit=100;
select f1();
f1()
36
select QUERY, TRACE, INSUFFICIENT_PRIVILEGES from information_schema.OPTIMIZER_TRACE;
QUERY	TRACE	INSUFFICIENT_PRIVILEGES
select f1()	{
  "steps": [
    {
      "join_preparation": {
        "select#": 1,
        "steps": [
          {
            "expanded_query": "/* select#1 */ select `f1`() AS `f1()`"
          }
        ]
      }
    }
  ]
}	0
		1

# C.3) remove table-level SELECT privilege on view

select user();
user()
root@localhost
# Put back privilege removed in C.2
grant select on mysql.proc to user1@localhost;
# And remove a next one:
revoke select on v1 from user1@localhost;
grant select (a) on v1 to user1@localhost;
delete from t1 where a<>"first";

select user();
user()
user1@localhost
set optimizer_trace_offset=0,optimizer_trace_limit=100;
select * from v1;
a
first
# Cannot see anything as it might expose some data from columns
# of v1
select QUERY, length(TRACE), INSUFFICIENT_PRIVILEGES from information_schema.OPTIMIZER_TRACE;
QUERY	length(TRACE)	INSUFFICIENT_PRIVILEGES
	0	1
	0	1

# C.4) remove SHOW VIEW privilege on view

select user();
user()
root@localhost
# Put back privilege removed in C.3
grant select on v1 to user1@localhost;
# And remove a next one:
revoke show view on v1 from user1@localhost;
delete from t1 where a<>"first";

select user();
user()
user1@localhost
set optimizer_trace="enabled=off";
# We have no right to see view's body:
show create view v1;
ERROR 42000: SHOW VIEW command denied to user 'user1'@'localhost' for table 'v1'
set optimizer_trace="enabled=on";
# Verify that optimizer trace does not influence the privilege
# checking in SHOW CREATE:
show create view v1;
ERROR 42000: SHOW VIEW command denied to user 'user1'@'localhost' for table 'v1'
set optimizer_trace_offset=0,optimizer_trace_limit=100;
select * from v1;
a
first
# Cannot see anything as it would expose body of view
select QUERY, TRACE, INSUFFICIENT_PRIVILEGES from information_schema.OPTIMIZER_TRACE;
QUERY	TRACE	INSUFFICIENT_PRIVILEGES
		1
		1

# ==========================================================
# Part D.
# Like Part C, but instead of SQL SECURITY INVOKER objects
# created by root and used by User1, let's have SQL SECURITY
# DEFINER objects created and used by User1. Determine the
# minimum set of privileges he needs for that.
# ==========================================================

select user();
user()
root@localhost
drop procedure p1;
drop function f1;
drop view v1;
drop trigger trg2;
revoke all privileges, grant option from user1@localhost;
# Grant minimum privileges to create and use objects,
# without considering optimizer trace:
grant create routine on somedb.* to user1@localhost;
grant trigger on t2 to user1@localhost;
grant create view on somedb.* to user1@localhost;
grant select (a) on t1 to user1@localhost;
grant insert (a) on t1 to user1@localhost;
grant insert (a) on t2 to user1@localhost;
grant select (a) on t3 to user1@localhost;
grant insert (a) on t3 to user1@localhost;
delete from t1 where a<>"first";

select user();
user()
user1@localhost
set optimizer_trace="enabled=on";
SET sql_mode = 'ONLY_FULL_GROUP_BY,NO_ENGINE_SUBSTITUTION';
Warnings:
Warning	3090	Changing sql mode 'NO_AUTO_CREATE_USER' is deprecated. It will be removed in a future release.
create procedure p1() sql security definer
begin
declare b int;
if (select count(*) from t1)
then
select 22 into b from dual;
end if;
select a into b from t1 limit 1;
insert into t1 values(current_user());
end|
create function f1() returns int sql security definer
begin
declare b int;
select 48 into b from dual;
select a into b from t1 limit 1;
insert into t1 values(current_user());
return 36;
end|
create trigger trg2 before insert on t2 for each row
begin
insert into t3 select * from t3;
end|
create sql security definer view v1 as select * from t1;
SET sql_mode = default;
# Creating a view is not enough to be able to SELECT it...
select user();
user()
root@localhost
grant select (a) on v1 to user1@localhost;

select user();
user()
user1@localhost
# Those privileges are not enough to see traces:
set optimizer_trace_offset=0,optimizer_trace_limit=100;
call p1();
# Can see body of routine (as definer), but not statements using t1
select QUERY, length(TRACE), INSUFFICIENT_PRIVILEGES from information_schema.OPTIMIZER_TRACE;
QUERY	length(TRACE)	INSUFFICIENT_PRIVILEGES
call p1()	20	0
set b@0 NULL	20	0
	0	1
select 22 into b from dual	407	0
	0	1
	0	1
set optimizer_trace_offset=0,optimizer_trace_limit=100;
select f1();
f1()
36
# Can see body of routine (as definer), but not statements using t1
select QUERY, length(TRACE), INSUFFICIENT_PRIVILEGES from information_schema.OPTIMIZER_TRACE;
QUERY	length(TRACE)	INSUFFICIENT_PRIVILEGES
select f1()	214	0
select f1()	413	0
set b@0 NULL	20	0
select 48 into b from dual	407	0
	0	1
	0	1
freturn 3 36	20	0
show create function f1;
Function	sql_mode	Create Function	character_set_client	collation_connection	Database Collation
f1	ONLY_FULL_GROUP_BY,NO_ENGINE_SUBSTITUTION	CREATE DEFINER=`user1`@`localhost` FUNCTION `f1`() RETURNS int(11)
begin
declare b int;
select 48 into b from dual;
select a into b from t1 limit 1;
insert into t1 values(current_user());
return 36;
end	latin1	latin1_swedish_ci	latin1_swedish_ci
set optimizer_trace_offset=0,optimizer_trace_limit=100;
select * from v1;
a
first
user1@localhost
user1@localhost
# Cannot see anything as it might expose some data from columns
# of t1
select QUERY, length(TRACE), INSUFFICIENT_PRIVILEGES from information_schema.OPTIMIZER_TRACE;
QUERY	length(TRACE)	INSUFFICIENT_PRIVILEGES
	0	1
	0	1
set optimizer_trace_offset=0,optimizer_trace_limit=100;
insert into t2 values(current_user());
# Cannot see anything as it might expose some data from
# columns of t2
select QUERY, length(TRACE), INSUFFICIENT_PRIVILEGES from information_schema.OPTIMIZER_TRACE;
QUERY	length(TRACE)	INSUFFICIENT_PRIVILEGES
	0	1
	0	1
# Also test a query accessing t1 in FROM clause:
set optimizer_trace_offset=0,optimizer_trace_limit=100;
select a from (select a from t1 where a like "f%") as tt where a like "fi%";
a
first
select QUERY, length(TRACE), INSUFFICIENT_PRIVILEGES from information_schema.OPTIMIZER_TRACE;
QUERY	length(TRACE)	INSUFFICIENT_PRIVILEGES
	0	1
	0	1

# D.0) Add more privileges:

select user();
user()
root@localhost
# - for use of t1 in routines and view:
grant select on t1 to user1@localhost;
# - for use of view:
grant select, show view on v1 to user1@localhost;
# - for use of trigger
grant select on t2 to user1@localhost;
grant select on t3 to user1@localhost;
delete from t1 where a<>"first";

select user();
user()
user1@localhost
set optimizer_trace_offset=0,optimizer_trace_limit=100;
call p1();
select QUERY, length(TRACE), INSUFFICIENT_PRIVILEGES from information_schema.OPTIMIZER_TRACE;
QUERY	length(TRACE)	INSUFFICIENT_PRIVILEGES
call p1()	20	0
set b@0 NULL	20	0
jump_if_not 3(3) (select count(0) from `somedb`.`t1`)	2731	0
select 22 into b from dual	407	0
select a into b from t1 limit 1	2164	0
insert into t1 values(current_user())	20	0
# Trace exposed body of routine, and content of t1, which we
# could see anyway:
show create procedure p1;
Procedure	sql_mode	Create Procedure	character_set_client	collation_connection	Database Collation
p1	ONLY_FULL_GROUP_BY,NO_ENGINE_SUBSTITUTION	CREATE DEFINER=`user1`@`localhost` PROCEDURE `p1`()
begin
declare b int;
if (select count(*) from t1)
then
select 22 into b from dual;
end if;
select a into b from t1 limit 1;
insert into t1 values(current_user());
end	latin1	latin1_swedish_ci	latin1_swedish_ci
select * from t1 limit 1;
a
first
set optimizer_trace_offset=0,optimizer_trace_limit=100;
select f1();
f1()
36
select QUERY, length(TRACE), INSUFFICIENT_PRIVILEGES from information_schema.OPTIMIZER_TRACE;
QUERY	length(TRACE)	INSUFFICIENT_PRIVILEGES
select f1()	214	0
select f1()	413	0
set b@0 NULL	20	0
select 48 into b from dual	407	0
select a into b from t1 limit 1	2164	0
insert into t1 values(current_user())	20	0
freturn 3 36	20	0
# Trace exposed body of routine, and content of t1, which we
# could see anyway:
show create function f1;
Function	sql_mode	Create Function	character_set_client	collation_connection	Database Collation
f1	ONLY_FULL_GROUP_BY,NO_ENGINE_SUBSTITUTION	CREATE DEFINER=`user1`@`localhost` FUNCTION `f1`() RETURNS int(11)
begin
declare b int;
select 48 into b from dual;
select a into b from t1 limit 1;
insert into t1 values(current_user());
return 36;
end	latin1	latin1_swedish_ci	latin1_swedish_ci
set optimizer_trace_offset=0,optimizer_trace_limit=100;
select * from v1;
a
first
user1@localhost
user1@localhost
select QUERY, length(TRACE), INSUFFICIENT_PRIVILEGES from information_schema.OPTIMIZER_TRACE;
QUERY	length(TRACE)	INSUFFICIENT_PRIVILEGES
select * from v1	898	0
select * from v1	2156	0
# Trace exposed body of view, and content of t1, which we
# could see anyway:
show create view v1;
View	Create View	character_set_client	collation_connection
v1	CREATE ALGORITHM=UNDEFINED DEFINER=`user1`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `t1`.`a` AS `a` from `t1`	latin1	latin1_swedish_ci
set optimizer_trace_offset=0,optimizer_trace_limit=100;
insert into t2 values(current_user());
select QUERY, length(TRACE), INSUFFICIENT_PRIVILEGES from information_schema.OPTIMIZER_TRACE;
QUERY	length(TRACE)	INSUFFICIENT_PRIVILEGES
insert into t2 values(current_user())	20	0
insert into t2 values(current_user())	20	0
insert into t3 select * from t3	2532	0
# Trace exposed body of trigger, and content of t2/t3, which we
# could see anyway:
show create trigger trg2;
Trigger	sql_mode	SQL Original Statement	character_set_client	collation_connection	Database Collation	Created
trg2	ONLY_FULL_GROUP_BY,NO_ENGINE_SUBSTITUTION	CREATE DEFINER=`user1`@`localhost` trigger trg2 before insert on t2 for each row
begin
insert into t3 select * from t3;
end	latin1	latin1_swedish_ci	latin1_swedish_ci	#
select * from t2, t3 limit 1;
a	a
first	first
# Trace exposed content of t1 which we could see anyway:
set optimizer_trace_offset=0,optimizer_trace_limit=100;
select a from (select a from t1 where a like "f%") as tt where a like "fi%";
a
first
select QUERY, length(TRACE), INSUFFICIENT_PRIVILEGES from information_schema.OPTIMIZER_TRACE;
QUERY	length(TRACE)	INSUFFICIENT_PRIVILEGES
select a from (select a from t1 where a like "f%") as tt where a like "fi%"	1060	0
select a from (select a from t1 where a like "f%") as tt where a like "fi%"	3280	0

# For routines, as they only use t1 and we added only one
# privilege on t1, we have nothing to remove.

# Now remove each privilege to verify that it was needed for
# the view.
# D.1) remove table-level SELECT privilege on v1

select user();
user()
root@localhost
revoke select on v1 from user1@localhost;
grant select (a) on v1 to user1@localhost;

select user();
user()
user1@localhost
set optimizer_trace_offset=0,optimizer_trace_limit=100;
select * from v1;
a
first
user1@localhost
user1@localhost
# Cannot see anything as it might expose some data from columns
# of v1
select QUERY, length(TRACE), INSUFFICIENT_PRIVILEGES from information_schema.OPTIMIZER_TRACE;
QUERY	length(TRACE)	INSUFFICIENT_PRIVILEGES
	0	1
	0	1

# D.2) remove table-level SHOW VIEW privilege on v1

select user();
user()
root@localhost
# Put back privilege removed in D.1
grant select on v1 to user1@localhost;
# And remove a next one:
revoke show view on v1 from user1@localhost;

select user();
user()
user1@localhost
# We have no right to see view's body:
show create view v1;
ERROR 42000: SHOW VIEW command denied to user 'user1'@'localhost' for table 'v1'
set optimizer_trace_offset=0,optimizer_trace_limit=100;
select * from v1;
a
first
user1@localhost
user1@localhost
# Cannot see anything as it would expose body of view
select QUERY, TRACE, INSUFFICIENT_PRIVILEGES from information_schema.OPTIMIZER_TRACE;
QUERY	TRACE	INSUFFICIENT_PRIVILEGES
		1
		1

# D.3) remove table-level SELECT privilege on t1

select user();
user()
root@localhost
# Put back privilege removed in D.2
grant show view on v1 to user1@localhost;
# And remove a next one:
revoke select on t1 from user1@localhost;
grant select (a) on t1 to user1@localhost;

select user();
user()
user1@localhost
set optimizer_trace_offset=0,optimizer_trace_limit=100;
select * from v1;
a
first
user1@localhost
user1@localhost
# Cannot see anything as it might expose some data from columns
# of t1
select QUERY, length(TRACE), INSUFFICIENT_PRIVILEGES from information_schema.OPTIMIZER_TRACE;
QUERY	length(TRACE)	INSUFFICIENT_PRIVILEGES
	0	1
	0	1

# Now remove each privilege to verify that it was needed for
# the trigger:
# D.4) remove table-level SELECT privilege on t2

select user();
user()
root@localhost
revoke select on t2 from user1@localhost;
grant select (a) on t2 to user1@localhost;

select user();
user()
user1@localhost
set optimizer_trace_offset=0,optimizer_trace_limit=100;
insert into t2 values(current_user());
# Cannot see anything as it might expose some data from
# columns of t2
select QUERY, length(TRACE), INSUFFICIENT_PRIVILEGES from information_schema.OPTIMIZER_TRACE;
QUERY	length(TRACE)	INSUFFICIENT_PRIVILEGES
	0	1
	0	1

# D.5) remove table-level SELECT privilege on t3


select user();
user()
root@localhost
# Put back privilege removed in D.4
grant select on t2 to user1@localhost;
# And remove a next one:
revoke select on t3 from user1@localhost;
grant select (a) on t3 to user1@localhost;

select user();
user()
user1@localhost
set optimizer_trace_offset=0,optimizer_trace_limit=100;
insert into t2 values(current_user());
# Cannot see substatement as it might expose some data from
# columns of t3
select QUERY, length(TRACE), INSUFFICIENT_PRIVILEGES from information_schema.OPTIMIZER_TRACE;
QUERY	length(TRACE)	INSUFFICIENT_PRIVILEGES
insert into t2 values(current_user())	20	0
insert into t2 values(current_user())	20	0
	0	1

# Cleanup
select user();
user()
root@localhost
drop user user1@localhost;

# ==========================================================
# Part E.
# Misc tests.
# ==========================================================

select user();
user()
root@localhost
drop view v1;
create sql security definer view v1 as select * from t1 where 'secret';
create user user1@localhost identified by '';
grant create, insert, select on somedb.* to user1@localhost;
grant create routine on somedb.* to user1@localhost;

select user();
user()
user1@localhost
user1 cannot see view's body:
show create view v1;
ERROR 42000: SHOW VIEW command denied to user 'user1'@'localhost' for table 'v1'
SET sql_mode = 'ONLY_FULL_GROUP_BY,NO_ENGINE_SUBSTITUTION';
Warnings:
Warning	3090	Changing sql mode 'NO_AUTO_CREATE_USER' is deprecated. It will be removed in a future release.
user1 creates a procedure
create procedure proc() sql security definer
begin
set optimizer_trace="enabled=on";
set optimizer_trace_offset=0,optimizer_trace_limit=100;
select * from v1 limit 0;
create table leak select * from information_schema.optimizer_trace;
set optimizer_trace="enabled=off";
end|
SET sql_mode = default;
select user();
user()
root@localhost
root runs procedure, without fear of risk as it is SQL SECURITY DEFINER
call proc();
a

select user();
user()
user1@localhost
user1 cannot see view's body:
select * from leak;
QUERY	TRACE	MISSING_BYTES_BEYOND_MAX_MEM_SIZE	INSUFFICIENT_PRIVILEGES

# Cleanup
select user();
user()
root@localhost
drop database somedb;
drop user user1@localhost;
set @@global.optimizer_trace_max_mem_size = @old_size;