MySQL Bugs: #20262: Query Cache breaks when using permissions on views

Bug #20262	Query Cache breaks when using permissions on views
Submitted:	4 Jun 2006 23:12	Modified:	7 Jul 2017 9:13
Reporter:	Lukas Beeler	Email Updates:
Status:	Won't fix	Impact on me:	None
Category:	MySQL Server: Query Cache	Severity:	S4 (Feature request)
Version:	5.0.18	OS:	Linux (Debian Sarge)
Assigned to:		CPU Architecture:	Any

Description:
The Query Cache does not work you grant permissions only on the view used by the client, but not on the underlying tables used by the view.

How to repeat:

Our main "MailAccounts" View

CREATE VIEW MailAccounts AS 
	SELECT
		d.user AS sysuser,
		d.uid AS uid,
		m.user AS mailuser,
		m.password as password,
		m.domain AS domain,
		d.domain AS domainname,
		d.maildir AS homedir,
		concat(d.maildir ,'/',m.user,'/') AS maildir,
		concat(m.user,'@',d.domain) AS email,
		m.spamlimit AS spamlimit,
		m. virusinspamfolder AS virusinspamfolder,
		m.catchall AS catchall
	FROM
		Mail m,
		Domains d
	WHERE
		m.domain = d.id
		AND d.status = 'ACTIVE'
		AND d.uid > 0
		AND d.maildir IS NOT NULL
		AND m.forward IS NULL;

GRANT SELECT on apache.MailAccounts to postfix@localhost identified by "foo".

As you can see, the postfix user does not have rights on the Mail and Domains tables, but on the MailAccounts table. When using the user postfix to make queries on the MailAccounts table, it does not cache the results. When using the user root to make the queries, it does work.

For a quick workaround, the following is sufficient:

GRANT SELECT on apache.* to postfix@localhost identified by  "bar"

Here's some additional which might be helpful (table descriptions, sql queries used):

SELECT email, password,'',uid,uid,homedir,maildir,0,'','' 
FROM MailAccounts 
WHERE   domainname='kool.ch' 
AND     (mailuser='elian'  OR (catchall=1 
AND NOT EXISTS( 
SELECT *  FROM MailAccounts WHERE mailuser='elian' AND domainname='kool.ch'))); 

CREATE TABLE `Mail` (
  `id` int(11) NOT NULL auto_increment,
  `domain` int(11) NOT NULL,
  `user` varchar(100) collate latin1_german1_ci NOT NULL,
  `password` varchar(50) collate latin1_german1_ci default NULL,
  `catchall` tinyint(1) default '0',
  `forward` varchar(300) collate latin1_german1_ci default NULL,
  `spamlimit` tinyint(4) NOT NULL default '0',
  `virusinspamfolder` tinyint(1) NOT NULL default '0',
  PRIMARY KEY  (`id`),
  FULLTEXT KEY `mail_index` (`user`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_german1_ci 

CREATE TABLE `Domains` (
  `id` int(11) NOT NULL auto_increment,
  `domain` varchar(100) collate latin1_german1_ci NOT NULL default '',
  `namevirtualhost` int(11) default NULL,
  `abo` int(11) default NULL,
  `docroot` varchar(100) collate latin1_german1_ci default NULL,
  `phpflags` varchar(100) collate latin1_german1_ci default NULL,
  `sslcert` varchar(50) collate latin1_german1_ci default NULL,
  `user` varchar(50) collate latin1_german1_ci default NULL,
  `uid` int(11) NOT NULL,
  `logfile` varchar(50) collate latin1_german1_ci default NULL,
  `status` enum('ORDERED','ACTIVE','PASSIVE') collate latin1_german1_ci default 'ORDERED',
  `kontakt` int(11) default NULL,
  `kontakt_rech` int(11) default NULL,
  `startDate` date default NULL,
  `endDate` date default NULL,
  `bezahltBis` date default NULL,
  `betrag` double default NULL,
  `rabatt` double default NULL,
  `waehrung` int(11) default NULL,
  `abrechnungsart` int(11) NOT NULL default '1',
  `text` text collate latin1_german1_ci,
  `actionafterinstall` varchar(255) collate latin1_german1_ci default NULL,
  `maildir` varchar(100) collate latin1_german1_ci default NULL,
  PRIMARY KEY  (`id`),
  FULLTEXT KEY `domain_index` (`domain`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_german1_ci 

Suggested fix:
Make the query cache recognize permissions on views.

Thank you for a resonable feature request. I had checked. It is not a bug, but documented behavior: http://dev.mysql.com/doc/refman/5.0/en/query-cache-how.html

I've read the documentation link carefully before posting this bug report, but i fail the find the part of it which describes this behaviour.

Here is the quote: "Before a query result is fetched from the query cache, MySQL checks that the user has SELECT privilege for all databases and tables involved. If this is not the case, the cached result is not used."

Hmm, yes, i've read that sentence, but i tought it didn't apply in this case because i've used a view.

It might be helpful to reword this sentenence to make it a bit clearer, so you don't get such useless bug reports anymore.

Hello,

I must disagree with some of your words!

"It might be helpful to reword this sentenence to make it a bit clearer, so you
don't get such useless bug reports anymore."

Even if we consider this behaviour expected and documented the fact that you couldn't find where this is described in MySQL manual means the manual should be improved. 

If as a result of you bug report we improve the manual all MySQL users will benefit from it no matter how small the improvement is. Therefore I disagree you bug report is useless!

Please never hesitate to report such problems to us and thanks a lot for your effort

MySQL will no longer invest in the query cache, see:

http://mysqlserverteam.com/mysql-8-0-retiring-support-for-the-query-cache/