Типовые запросы для управления доступом

В этой статье рассказывается об основах создания пользователей и ролей SQL, а также назначения им привилегий и разрешений для баз данных, таблиц, строк и столбцов.

В сервисах ClickHouse Cloud есть пользователь default с правами администратора, который создается при создании сервиса. Пароль задается при создании сервиса, и его могут сбросить пользователи ClickHouse Cloud с ролью Admin.

Когда вы добавляете дополнительных SQL-пользователей для своего сервиса ClickHouse Cloud, им потребуются SQL-имя пользователя и пароль. Если вы хотите предоставить им административные привилегии, назначьте новым пользователям роль default_role. Например, добавим пользователя clickhouse_admin:

CREATE USER IF NOT EXISTS clickhouse_admin
IDENTIFIED WITH sha256_password BY 'P!@ssword42!';

GRANT default_role TO clickhouse_admin;

Для SQL Console доступны две роли: sql_console_admin с теми же разрешениями, что и у default_role, и sql_console_read_only с разрешениями только для чтения.

Пользователям с ролью Admin по умолчанию назначается роль sql_console_admin, поэтому для них ничего не меняется. Однако роль sql_console_read_only позволяет предоставить пользователям без роли Admin доступ только для чтения или полный доступ к любому экземпляру. Этот доступ должен настроить пользователь с ролью Admin. Роли можно изменить с помощью команд GRANT или REVOKE, чтобы точнее соответствовать требованиям конкретного экземпляра, и все внесённые в них изменения будут сохранены.

Этот механизм управления доступом также можно настроить вручную с точностью до отдельного пользователя. Перед тем как назначать пользователям новые роли sql_console_*, следует создать роли базы данных для отдельных пользователей SQL Console по шаблону sql-console-role:<email>. Например:

CREATE ROLE OR REPLACE sql-console-role:<email>;
GRANT <some grants> TO sql-console-role:<email>;

Если обнаружена подходящая роль, она будет назначена пользователю вместо шаблонных ролей. Это позволяет настраивать более сложные схемы управления доступом, например создавать роли sql_console_sa_role и sql_console_pm_role и назначать их конкретным пользователям. Например:

CREATE ROLE OR REPLACE sql_console_sa_role;
GRANT <whatever level of access> TO sql_console_sa_role;
CREATE ROLE OR REPLACE sql_console_pm_role;
GRANT <whatever level of access> TO sql_console_pm_role;
CREATE ROLE OR REPLACE `sql-console-role:christoph@clickhouse.com`;
CREATE ROLE OR REPLACE `sql-console-role:jake@clickhouse.com`;
CREATE ROLE OR REPLACE `sql-console-role:zach@clickhouse.com`;
GRANT sql_console_sa_role to `sql-console-role:christoph@clickhouse.com`;
GRANT sql_console_sa_role to `sql-console-role:jake@clickhouse.com`;
GRANT sql_console_pm_role to `sql-console-role:zach@clickhouse.com`;

Выйдите из системы под пользователем default и снова войдите под пользователем clickhouse_admin.

Все перечисленные ниже действия должны выполниться успешно:

SHOW GRANTS FOR clickhouse_admin;

CREATE DATABASE db1

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

INSERT INTO db1.table1 (id, column1) VALUES (1, 'abc');

SELECT * FROM db1.table1;

DROP TABLE db1.table1;

DROP DATABASE db1;

Пользователям следует предоставить необходимые привилегии; не всем из них нужны права администратора. Далее в этом документе приведены примеры сценариев и указаны требуемые роли.

Создайте эти таблицы и пользователей для использования в примерах.

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');

SELECT *
FROM db1.table1

Query id: 475015cc-6f51-4b20-bda2-3c9c41404e49

┌─id─┬─column1─┬─column2─┐
│  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     │
└────┴─────────┴─────────┘

Пользователю можно назначить несколько ролей, чтобы получить нужную комбинацию привилегий. При использовании нескольких ролей система объединяет их для определения привилегий, то есть права, предоставленные ролями, суммируются.

Например, если role1 разрешает только SELECT по column1, а role2 разрешает SELECT по column1 и column2, то пользователь получит доступ к обоим столбцам.

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       │
└────┴─────────┘

Иногда пересечение или сочетание привилегий приводит к неожиданным результатам; чтобы сузить область поиска проблемы, можно использовать следующие команды, выполнив вход под учетной записью администратора

SHOW GRANTS FOR row_and_column_user

Query id: 6a73a3fe-2659-4aca-95c5-d012c138097b

┌─GRANTS FOR row_and_column_user───────────────────────────┐
│ GRANT A_rows_users, column1_users TO row_and_column_user │
└──────────────────────────────────────────────────────────┘

SHOW ROLES

Query id: 1e21440a-18d9-4e75-8f0e-66ec9b36470a

┌─name────────────┐
│ A_rows_users    │
│ column1_users   │
└─────────────────┘

SHOW ROW POLICIES

Query id: f2c636e9-f955-4d79-8e80-af40ea227ebc

┌─name───────────────────────────────────┐
│ A_row_filter ON db1.table1             │
│ allow_other_users_filter ON db1.table1 │
└────────────────────────────────────────┘

SHOW CREATE ROW POLICY A_row_filter ON db1.table1

Query id: 0d3b5846-95c7-4e62-9cdd-91d82b14b80b

┌─CREATE ROW POLICY A_row_filter ON db1.table1────────────────────────────────────────────────┐
│ CREATE ROW POLICY A_row_filter ON db1.table1 FOR SELECT USING column1 = 'A' TO A_rows_users │
└─────────────────────────────────────────────────────────────────────────────────────────────┘

Следующие команды можно использовать, чтобы:

• удалить привилегии
• удалить политики
• отвязать пользователей от ролей
• удалить пользователей и роли
  

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

DROP ROW POLICY A_row_filter ON db1.table1;

REVOKE A_rows_users FROM row_user;

DROP ROLE A_rows_users;

DROP USER row_user;

В этой статье мы рассмотрели основы создания SQL-пользователей и ролей, а также шаги по настройке и изменению привилегий для пользователей и ролей. Более подробную информацию по каждой из этих тем см. в наших руководствах пользователя и справочной документации.