Description:
Query cache and VIEWs work only for SUPER user or for users granted database-wide privileges on databases used in the query. Cacheable queries are stored in the cache, but never "hit" - are never returned to the user - as can be observed with 'Query_cache_hits' variable.
In other words, for SQL SECURITY DEFINER views, the query cache practically is never used. For SQL SECURITY INVOKER views, it's used only if the invoker has database-wide grans.
How to repeat:
Below is a test case in mysqltest language, with comments, and a result file generated by the current 5.1-main tree. The test case and its result will also be attached to the report.
--source include/have_query_cache.inc
set global query_cache_size=1000000;
--disable_warnings
drop database if exists qcv;
--enable_warnings
create database qcv;
--echo
--echo It is important to use a database other than 'test' to avoid
--echo influence of the default privileges issued at bootstrap
--echo
use qcv;
--echo
--echo Case 1: if a view is created with SQL SECURITY DEFINER,
--echo and the user (invoker) has no privileges to read the underlying
--echo tables, the query is cached but not returned from the cache.
--echo
create table t1 (a varchar(10));
insert into t1 (a) values ("secret");
create view v1 as select a from t1;
grant select on qcv.v1 to foo@localhost;
flush query cache;
flush status;
connect (foo,localhost,foo,,);
connection foo;
show grants for current_user();
use qcv;
show status like 'Qcache_queries_in_cache';
show status like 'Qcache_hits';
select * from v1;
show status like 'Qcache_queries_in_cache';
show status like 'Qcache_hits';
select * from v1;
--echo
--echo You can observe that the query from the cache is not returned
--echo
show status like 'Qcache_queries_in_cache';
show status like 'Qcache_hits';
--echo
--echo Works fine if one has SUPER
--echo
connection default;
select * from v1;
show status like 'Qcache_queries_in_cache';
show status like 'Qcache_hits';
--echo
--echo Case 2: even a view with SQL SECURITY INVOKER,
--echo is not returned from the query cache unless there are database-wide
--echo grants or SUPER.
--echo
drop view v1;
create sql security invoker view v1 as select * from t1;
grant select on qcv.v1 to foo@localhost;
grant select on qcv.t1 to foo@localhost;
flush status;
connection foo;
show grants for current_user();
show status like 'Qcache_queries_in_cache';
show status like 'Qcache_hits';
select * from v1;
show status like 'Qcache_queries_in_cache';
show status like 'Qcache_hits';
select * from v1;
--echo
--echo You can observe that the query from the cache is not returned
--echo
show status like 'Qcache_queries_in_cache';
show status like 'Qcache_hits';
--echo
--echo Works fine if one has SUPER or database-wide grants
--echo
connection default;
select * from v1;
show status like 'Qcache_queries_in_cache';
show status like 'Qcache_hits';
--echo
--echo Grant database-wide grants and recheck
--echo
disconnect foo;
drop user foo@localhost;
grant select on qcv.* to foo@localhost;
flush status;
drop view v1;
create view v1 as select a from t1;
connect (foo,localhost,foo,,);
connection foo;
use qcv;
show grants for current_user();
show status like 'Qcache_queries_in_cache';
show status like 'Qcache_hits';
select * from v1;
show status like 'Qcache_queries_in_cache';
show status like 'Qcache_hits';
select * from v1;
show status like 'Qcache_queries_in_cache';
show status like 'Qcache_hits';
--echo
--echo Cleanup
--echo
disconnect foo;
connection default;
drop user foo@localhost;
drop database qcv;
---------------------------------------------------------------------------
Current result:
set global query_cache_size=1000000;
drop database if exists qcv;
create database qcv;
It is important to use a database other than 'test' to avoid
influence of the default privileges issued at bootstrap
use qcv;
Case 1: if a view is created with SQL SECURITY DEFINER,
and the user (invoker) has no privileges to read the underlying
tables, the query is cached but not returned from the cache.
create table t1 (a varchar(10));
insert into t1 (a) values ("secret");
create view v1 as select a from t1;
grant select on qcv.v1 to foo@localhost;
flush query cache;
flush status;
show grants for current_user();
Grants for foo@localhost
GRANT USAGE ON *.* TO 'foo'@'localhost'
GRANT SELECT ON `qcv`.`v1` TO 'foo'@'localhost'
use qcv;
show status like 'Qcache_queries_in_cache';
Variable_name Value
Qcache_queries_in_cache 0
show status like 'Qcache_hits';
Variable_name Value
Qcache_hits 0
select * from v1;
a
secret
show status like 'Qcache_queries_in_cache';
Variable_name Value
Qcache_queries_in_cache 1
show status like 'Qcache_hits';
Variable_name Value
Qcache_hits 0
select * from v1;
a
secret
You can observe that the query from the cache is not returned
show status like 'Qcache_queries_in_cache';
Variable_name Value
Qcache_queries_in_cache 1
show status like 'Qcache_hits';
Variable_name Value
Qcache_hits 0
Works fine if one has SUPER
select * from v1;
a
secret
show status like 'Qcache_queries_in_cache';
Variable_name Value
Qcache_queries_in_cache 1
show status like 'Qcache_hits';
Variable_name Value
Qcache_hits 1
Case 2: even a view with SQL SECURITY INVOKER,
is not returned from the query cache unless there are database-wide
grants or SUPER.
drop view v1;
create sql security invoker view v1 as select * from t1;
grant select on qcv.v1 to foo@localhost;
grant select on qcv.t1 to foo@localhost;
flush status;
show grants for current_user();
Grants for foo@localhost
GRANT USAGE ON *.* TO 'foo'@'localhost'
GRANT SELECT ON `qcv`.`t1` TO 'foo'@'localhost'
GRANT SELECT ON `qcv`.`v1` TO 'foo'@'localhost'
show status like 'Qcache_queries_in_cache';
Variable_name Value
Qcache_queries_in_cache 0
show status like 'Qcache_hits';
Variable_name Value
Qcache_hits 0
select * from v1;
a
secret
show status like 'Qcache_queries_in_cache';
Variable_name Value
Qcache_queries_in_cache 1
show status like 'Qcache_hits';
Variable_name Value
Qcache_hits 0
select * from v1;
a
secret
You can observe that the query from the cache is not returned
show status like 'Qcache_queries_in_cache';
Variable_name Value
Qcache_queries_in_cache 1
show status like 'Qcache_hits';
Variable_name Value
Qcache_hits 0
Works fine if one has SUPER or database-wide grants
select * from v1;
a
secret
show status like 'Qcache_queries_in_cache';
Variable_name Value
Qcache_queries_in_cache 1
show status like 'Qcache_hits';
Variable_name Value
Qcache_hits 1
Grant database-wide grants and recheck
drop user foo@localhost;
grant select on qcv.* to foo@localhost;
flush status;
drop view v1;
create view v1 as select a from t1;
use qcv;
show grants for current_user();
Grants for foo@localhost
GRANT USAGE ON *.* TO 'foo'@'localhost'
GRANT SELECT ON `qcv`.* TO 'foo'@'localhost'
show status like 'Qcache_queries_in_cache';
Variable_name Value
Qcache_queries_in_cache 0
show status like 'Qcache_hits';
Variable_name Value
Qcache_hits 0
select * from v1;
a
secret
show status like 'Qcache_queries_in_cache';
Variable_name Value
Qcache_queries_in_cache 1
show status like 'Qcache_hits';
Variable_name Value
Qcache_hits 0
select * from v1;
a
secret
show status like 'Qcache_queries_in_cache';
Variable_name Value
Qcache_queries_in_cache 1
show status like 'Qcache_hits';
Variable_name Value
Qcache_hits 1
Cleanup
drop user foo@localhost;
drop database qcv;
Suggested fix:
The problem is two-fold.
The first one affect SQL SECURITY DEFINER views.
The query cache does not save the definer of the view, so it can't check that the definer has any privileges on the underlying tables, and (wrongly) checks the privileges in the security context of the invoker.
This can be fixed either by storing the definer's credentials in the query cache (priv_host, priv_user), or by adding query_cache_invalidate to revoke() (seems more appropriate), and only mark the query cache tables that were checked in SQL security context of the definer as not needing a check on retrieval.
The second problem affects SQL SECURITY INVOKER views.
For view tables, the server always attempts to check column-level grants, to require minimal privileges possible. But we know, that if the query has been cached, then no column level grants were used. So, when check_table_access is called from send_result_to_client, it should not check column-level grants of view tables, if there are table-level grants.
Description: Query cache and VIEWs work only for SUPER user or for users granted database-wide privileges on databases used in the query. Cacheable queries are stored in the cache, but never "hit" - are never returned to the user - as can be observed with 'Query_cache_hits' variable. In other words, for SQL SECURITY DEFINER views, the query cache practically is never used. For SQL SECURITY INVOKER views, it's used only if the invoker has database-wide grans. How to repeat: Below is a test case in mysqltest language, with comments, and a result file generated by the current 5.1-main tree. The test case and its result will also be attached to the report. --source include/have_query_cache.inc set global query_cache_size=1000000; --disable_warnings drop database if exists qcv; --enable_warnings create database qcv; --echo --echo It is important to use a database other than 'test' to avoid --echo influence of the default privileges issued at bootstrap --echo use qcv; --echo --echo Case 1: if a view is created with SQL SECURITY DEFINER, --echo and the user (invoker) has no privileges to read the underlying --echo tables, the query is cached but not returned from the cache. --echo create table t1 (a varchar(10)); insert into t1 (a) values ("secret"); create view v1 as select a from t1; grant select on qcv.v1 to foo@localhost; flush query cache; flush status; connect (foo,localhost,foo,,); connection foo; show grants for current_user(); use qcv; show status like 'Qcache_queries_in_cache'; show status like 'Qcache_hits'; select * from v1; show status like 'Qcache_queries_in_cache'; show status like 'Qcache_hits'; select * from v1; --echo --echo You can observe that the query from the cache is not returned --echo show status like 'Qcache_queries_in_cache'; show status like 'Qcache_hits'; --echo --echo Works fine if one has SUPER --echo connection default; select * from v1; show status like 'Qcache_queries_in_cache'; show status like 'Qcache_hits'; --echo --echo Case 2: even a view with SQL SECURITY INVOKER, --echo is not returned from the query cache unless there are database-wide --echo grants or SUPER. --echo drop view v1; create sql security invoker view v1 as select * from t1; grant select on qcv.v1 to foo@localhost; grant select on qcv.t1 to foo@localhost; flush status; connection foo; show grants for current_user(); show status like 'Qcache_queries_in_cache'; show status like 'Qcache_hits'; select * from v1; show status like 'Qcache_queries_in_cache'; show status like 'Qcache_hits'; select * from v1; --echo --echo You can observe that the query from the cache is not returned --echo show status like 'Qcache_queries_in_cache'; show status like 'Qcache_hits'; --echo --echo Works fine if one has SUPER or database-wide grants --echo connection default; select * from v1; show status like 'Qcache_queries_in_cache'; show status like 'Qcache_hits'; --echo --echo Grant database-wide grants and recheck --echo disconnect foo; drop user foo@localhost; grant select on qcv.* to foo@localhost; flush status; drop view v1; create view v1 as select a from t1; connect (foo,localhost,foo,,); connection foo; use qcv; show grants for current_user(); show status like 'Qcache_queries_in_cache'; show status like 'Qcache_hits'; select * from v1; show status like 'Qcache_queries_in_cache'; show status like 'Qcache_hits'; select * from v1; show status like 'Qcache_queries_in_cache'; show status like 'Qcache_hits'; --echo --echo Cleanup --echo disconnect foo; connection default; drop user foo@localhost; drop database qcv; --------------------------------------------------------------------------- Current result: set global query_cache_size=1000000; drop database if exists qcv; create database qcv; It is important to use a database other than 'test' to avoid influence of the default privileges issued at bootstrap use qcv; Case 1: if a view is created with SQL SECURITY DEFINER, and the user (invoker) has no privileges to read the underlying tables, the query is cached but not returned from the cache. create table t1 (a varchar(10)); insert into t1 (a) values ("secret"); create view v1 as select a from t1; grant select on qcv.v1 to foo@localhost; flush query cache; flush status; show grants for current_user(); Grants for foo@localhost GRANT USAGE ON *.* TO 'foo'@'localhost' GRANT SELECT ON `qcv`.`v1` TO 'foo'@'localhost' use qcv; show status like 'Qcache_queries_in_cache'; Variable_name Value Qcache_queries_in_cache 0 show status like 'Qcache_hits'; Variable_name Value Qcache_hits 0 select * from v1; a secret show status like 'Qcache_queries_in_cache'; Variable_name Value Qcache_queries_in_cache 1 show status like 'Qcache_hits'; Variable_name Value Qcache_hits 0 select * from v1; a secret You can observe that the query from the cache is not returned show status like 'Qcache_queries_in_cache'; Variable_name Value Qcache_queries_in_cache 1 show status like 'Qcache_hits'; Variable_name Value Qcache_hits 0 Works fine if one has SUPER select * from v1; a secret show status like 'Qcache_queries_in_cache'; Variable_name Value Qcache_queries_in_cache 1 show status like 'Qcache_hits'; Variable_name Value Qcache_hits 1 Case 2: even a view with SQL SECURITY INVOKER, is not returned from the query cache unless there are database-wide grants or SUPER. drop view v1; create sql security invoker view v1 as select * from t1; grant select on qcv.v1 to foo@localhost; grant select on qcv.t1 to foo@localhost; flush status; show grants for current_user(); Grants for foo@localhost GRANT USAGE ON *.* TO 'foo'@'localhost' GRANT SELECT ON `qcv`.`t1` TO 'foo'@'localhost' GRANT SELECT ON `qcv`.`v1` TO 'foo'@'localhost' show status like 'Qcache_queries_in_cache'; Variable_name Value Qcache_queries_in_cache 0 show status like 'Qcache_hits'; Variable_name Value Qcache_hits 0 select * from v1; a secret show status like 'Qcache_queries_in_cache'; Variable_name Value Qcache_queries_in_cache 1 show status like 'Qcache_hits'; Variable_name Value Qcache_hits 0 select * from v1; a secret You can observe that the query from the cache is not returned show status like 'Qcache_queries_in_cache'; Variable_name Value Qcache_queries_in_cache 1 show status like 'Qcache_hits'; Variable_name Value Qcache_hits 0 Works fine if one has SUPER or database-wide grants select * from v1; a secret show status like 'Qcache_queries_in_cache'; Variable_name Value Qcache_queries_in_cache 1 show status like 'Qcache_hits'; Variable_name Value Qcache_hits 1 Grant database-wide grants and recheck drop user foo@localhost; grant select on qcv.* to foo@localhost; flush status; drop view v1; create view v1 as select a from t1; use qcv; show grants for current_user(); Grants for foo@localhost GRANT USAGE ON *.* TO 'foo'@'localhost' GRANT SELECT ON `qcv`.* TO 'foo'@'localhost' show status like 'Qcache_queries_in_cache'; Variable_name Value Qcache_queries_in_cache 0 show status like 'Qcache_hits'; Variable_name Value Qcache_hits 0 select * from v1; a secret show status like 'Qcache_queries_in_cache'; Variable_name Value Qcache_queries_in_cache 1 show status like 'Qcache_hits'; Variable_name Value Qcache_hits 0 select * from v1; a secret show status like 'Qcache_queries_in_cache'; Variable_name Value Qcache_queries_in_cache 1 show status like 'Qcache_hits'; Variable_name Value Qcache_hits 1 Cleanup drop user foo@localhost; drop database qcv; Suggested fix: The problem is two-fold. The first one affect SQL SECURITY DEFINER views. The query cache does not save the definer of the view, so it can't check that the definer has any privileges on the underlying tables, and (wrongly) checks the privileges in the security context of the invoker. This can be fixed either by storing the definer's credentials in the query cache (priv_host, priv_user), or by adding query_cache_invalidate to revoke() (seems more appropriate), and only mark the query cache tables that were checked in SQL security context of the definer as not needing a check on retrieval. The second problem affects SQL SECURITY INVOKER views. For view tables, the server always attempts to check column-level grants, to require minimal privileges possible. But we know, that if the query has been cached, then no column level grants were used. So, when check_table_access is called from send_result_to_client, it should not check column-level grants of view tables, if there are table-level grants.