Query cache is not isolated across different databases about clickhouse HOT 3 CLOSED

@rschu1ze result is incorrect with the cache enabled https://fiddle.clickhouse.com/b79e57c1-e687-4985-9489-17eb1ff20a94

In some sense this is a CVE, because an unauthorized user can peek a data.

from clickhouse.

Why this example is so over complicated ? It reproduces with any query https://fiddle.clickhouse.com/7ae271e9-e866-4187-a6d6-e84e89a8910e

from clickhouse.

Even more minimal repro: https://fiddle.clickhouse.com/6661f07d-1372-4088-a0ea-9ff0d20090f3

Yes, the user can peek data from tables/columns without having SELECT privileges (assigned directly or by a role).

The source of the problem is that the query cache is based on comparing query ASTs. The same identifier in two ASTs can mean different things ...

The right fix would be to compare the representation with resolved identifiers produced by the Analyzer. It would be a bigger rewrite, however.

A quickfix would be to introduce a privilege for running queries with the query cache. This basically recognizes that the query cache usage can be a security risk (this would also be documented) and must be explicitly enabled by assigning the corresponding privilege. Such a privilege would be similar in spirit to the INTROSPECTION privilege.

Not sure what is the best route here, let me discuss internally.

EDIT: After discussion, we'll go with the quickfix (#64199, will also be backported), and for master I'll switch the query cache to use the Analyzer-representation with resolved identifiers (#64223).

from clickhouse.