ClickHouse / ClickHouse/ClickHouse
EXPLAIN SYNTAX with old analyzer ignores SQL SECURITY DEFINER for parameterized views
- Dominant language
- C++
- Stars
- 49.9k
- Forks
- 9k
- Avg merge
- 21h 32m
- Merged PRs (30d)
- 515
Description
### Description
`EXPLAIN SYNTAX` with the old analyzer does not preserve `SQL SECURITY DEFINER`
semantics for parameterized views. A restricted user can successfully query a
`SQL SECURITY DEFINER` parameterized view, but `EXPLAIN SYNTAX` for the same
query fails with `ACCESS_DENIED` because it checks privileges on the underlying
table as the invoker.
This is reproducible in `26.4.2.10-stable`.
### Reproduction
Run setup as `default`:
```sql
DROP DATABASE IF EXISTS repro_definer_explain SYNC;
DROP USER IF EXISTS repro_definer_user;
CREATE DATABASE repro_definer_explain;
CREATE TABLE repro_definer_explain.secret_table
(
x UInt64
)
ENGINE = Memory;
INSERT INTO repro_definer_explain.secret_table VALUES (42);
CREATE USER repro_definer_user;
CREATE VIEW repro_definer_explain.definer_pv
DEFINER = CURRENT_USER SQL SECURITY DEFINER AS
SELECT x
FROM repro_definer_explain.secret_table
WHERE x = {v:UInt64};
GRANT SELECT ON repro_definer_explain.definer_pv TO repro_definer_user;
```
Run as `repro_definer_user`:
```sql
SELECT * FROM repro_definer_explain.secret_table;
```
This correctly fails with `ACCESS_DENIED`.
Run as `repro_definer_user`:
```sql
SELECT * FROM repro_definer_explain.definer_pv(v = 42);
```
This correctly returns:
```text
42
```
Run as `repro_definer_user`:
```sql
EXPLAIN SYNTAX
SELECT * FROM repro_definer_explain.definer_pv(v = 42)
SETTINGS allow_experimental_analyzer = 1;
```
This succeeds in `26.4.2.10-stable` and returns the unexpanded parameterized
view call.
Run as `repro_definer_user`:
```sql
EXPLAIN SYNTAX
SELECT * FROM repro_definer_explain.definer_pv(v = 42)
SETTINGS allow_experimental_analyzer = 0;
```
This fails:
```text
Code: 497. DB::Exception: repro_definer_user: Not enough privileges. To execute
this query, it's necessary to have the grant SELECT ON
repro_definer_explain.secret_table: While processing SELECT * FROM
`repro_definer_explain.definer_pv`(v = 42) SETTINGS allow_experimental_analyzer = 0.
(ACCESS_DENIED)
```
### Expected behavior
`EXPLAIN SYNTAX` should respect the same `SQL SECURITY DEFINER` semantics as
query execution. If `repro_definer_user` can execute
`SELECT * FROM repro_definer_explain.definer_pv(v = 42)`, the corresponding
`EXPLAIN SYNTAX` should not require direct `SELECT` privileges on
`repro_definer_explain.secret_table`.
### Notes
The issue appears specific to the old analyzer path. The analyzer path in
`26.4.2.10-stable` does not reproduce this access-denied behavior because it
does not expand the parameterized view in `EXPLAIN SYNTAX`.
Contributor guide
Assessment
This issue has not been assessed yet.