ClickHouse / ClickHouse/ClickHouse

EXPLAIN SYNTAX with old analyzer ignores SQL SECURITY DEFINER for parameterized views

Open
#105,634 1 comment 0 reactions 0 assignees View on GitHub
clickgap-analyzed comp-view culprit-pr-not-found
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

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.