Consultas comuns de gerenciamento de acesso

CREATE DATABASE db1;

CREATE TABLE db1.table1 (
   id UInt64,
   column1 String,
   column2 String
)
ENGINE MergeTree
ORDER BY id;

INSERT INTO db1.table1
   (id, column1, column2)
VALUES
   (1, 'A', 'abc'),
   (2, 'A', 'def'),
   (3, 'B', 'abc'),
   (4, 'B', 'def');

CREATE USER column_user IDENTIFIED BY 'password';

CREATE USER row_user IDENTIFIED BY 'password';

CREATE ROLE column1_users;

GRANT SELECT(id, column1) ON db1.table1 TO column1_users;

GRANT column1_users TO column_user;

CREATE ROLE A_rows_users;

GRANT A_rows_users TO row_user;

CREATE ROW POLICY A_row_filter ON db1.table1 FOR SELECT USING column1 = 'A' TO A_rows_users;

GRANT SELECT(id, column1, column2) ON db1.table1 TO A_rows_users;

CREATE ROW POLICY allow_other_users_filter 
ON db1.table1 FOR SELECT USING 1 TO clickhouse_admin, column1_users;

clickhouse-client --user clickhouse_admin --password password

SELECT *
FROM db1.table1

Query id: f5e906ea-10c6-45b0-b649-36334902d31d

┌─id─┬─column1─┬─column2─┐
│  1 │ A       │ abc     │
│  2 │ A       │ def     │
│  3 │ B       │ abc     │
│  4 │ B       │ def     │
└────┴─────────┴─────────┘

clickhouse-client --user column_user --password password

SELECT *
FROM db1.table1

Query id: 5576f4eb-7450-435c-a2d6-d6b49b7c4a23

0 rows in set. Elapsed: 0.006 sec.

Received exception from server (version 22.3.2):
Code: 497. DB::Exception: Received from localhost:9000. 
DB::Exception: column_user: Not enough privileges. 
To execute this query it's necessary to have grant 
SELECT(id, column1, column2) ON db1.table1. (ACCESS_DENIED)

SELECT
    id,
    column1
FROM db1.table1

Query id: cef9a083-d5ce-42ff-9678-f08dc60d4bb9

┌─id─┬─column1─┐
│  1 │ A       │
│  2 │ A       │
│  3 │ B       │
│  4 │ B       │
└────┴─────────┘

clickhouse-client --user row_user --password password

SELECT *
FROM db1.table1

Query id: a79a113c-1eca-4c3f-be6e-d034f9a220fb

┌─id─┬─column1─┬─column2─┐
│  1 │ A       │ abc     │
│  2 │ A       │ def     │
└────┴─────────┴─────────┘

CREATE USER row_and_column_user IDENTIFIED BY 'password' DEFAULT ROLE A_rows_users;

REVOKE SELECT(id, column1, column2) ON db1.table1 FROM A_rows_users;

GRANT SELECT(id, column1) ON db1.table1 TO A_rows_users;

clickhouse-client --user row_and_column_user --password password;

SELECT *
FROM db1.table1

Query id: 8cdf0ff5-e711-4cbe-bd28-3c02e52e8bc4

0 rows in set. Elapsed: 0.005 sec.

Received exception from server (version 22.3.2):
Code: 497. DB::Exception: Received from localhost:9000. 
DB::Exception: row_and_column_user: Not enough privileges. 
To execute this query it's necessary to have grant 
SELECT(id, column1, column2) ON db1.table1. (ACCESS_DENIED)

SELECT
    id,
    column1
FROM db1.table1

Query id: 5e30b490-507a-49e9-9778-8159799a6ed0

┌─id─┬─column1─┐
│  1 │ A       │
│  2 │ A       │
└────┴─────────┘