一般的なアクセス管理クエリ - ClickHouse Documentation

セルフマネージドセルフマネージドの ClickHouse を使用している場合は、SQL ユーザーとロールを参照してください。

この記事では、SQL ユーザーとロールを定義する基本と、それらの権限やアクセス許可をデータベース、テーブル、行、カラムに適用する方法を説明します。

Admin ユーザー

ClickHouse Cloud サービスには、サービスの作成時に default という admin ユーザーが作成されます。パスワードはサービス作成時に設定され、Admin ロールを持つ ClickHouse Cloud ユーザーであればリセットできます。 ClickHouse Cloud サービスに追加の SQL ユーザーを作成する場合、それらのユーザーには SQL の username と password が必要です。管理者レベルの privileges を付与する場合は、新しいユーザーにロール 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 ステートメントは default ユーザーとしては実行されません。代わりに、sql-console:${cloud_login_email} という名前のユーザーとして実行されます。ここで、cloud_login_email は現在クエリを実行しているユーザーのメールアドレスです。これらの自動生成された SQL Console ユーザーには、default ロールが付与されます。

パスワードレス認証

SQL Console で使用できるロールは 2 つあります。default_role と同じ権限を持つ sql_console_admin と、読み取り専用権限を持つ sql_console_read_only です。 Admin ユーザーにはデフォルトで sql_console_admin ロールが割り当てられているため、特に変更はありません。一方、sql_console_read_only ロールを使うと、Admin 以外のユーザーに対して、任意のインスタンスへの読み取り専用アクセスまたはフルアクセスを付与できます。このアクセスは Admin が設定する必要があります。これらのロールは、インスタンスごとの要件に合わせて GRANT または REVOKE コマンドで調整でき、加えた変更は永続的に保持されます。

きめ細かなアクセス制御

このアクセス制御機能は、ユーザーレベルできめ細かく手動設定することもできます。新しいsql_console_*ロールをユーザーに割り当てる前に、sql-console-role:<email>ネームスペースに対応する、SQL Consoleのユーザー固有のデータベースロールを作成しておく必要があります。例:

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

`column_user` を作成する

特定のカラムへのアクセス制限を示すために使用する一般ユーザーを作成します。

CREATE USER column_user IDENTIFIED BY 'password';

`row_user` を作成する

特定の値を持つ行へのアクセス制限を示すために使用する一般ユーザーを作成します。

CREATE USER row_user IDENTIFIED BY 'password';

ロールの作成

この一連の例では、次のことを行います。

カラムや行など、異なる権限に対応するロールを作成します
ロールに権限を付与します
各ロールにユーザーを割り当てます

ロールは、ユーザーごとに個別に管理する代わりに、特定の権限を持つユーザーのグループを定義するために使用します。

このロールのユーザーが database `db1` の `table1` で `column1` のみを参照できるよう制限するロールを作成します

CREATE ROLE column1_users;

`column1` の参照を許可する権限を設定します

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

ユーザー `column_user` を `column1_users` ロールに追加します

GRANT column1_users TO column_user;

このロールのユーザーが選択された行のみを参照できるよう制限するロールを作成します。この例では、`column1` に `A` を含む行のみです

CREATE ROLE A_rows_users;

`row_user` を `A_rows_users` ロールに追加します

GRANT A_rows_users TO row_user;

`column1` の値が `A` の場合にのみ参照を許可するポリシーを作成します

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

database と table に対する権限を設定します

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_admin` ユーザーで ClickHouse client にログインします

clickhouse-client --user clickhouse_admin --password password

admin ユーザーでデータベース、テーブル、およびすべての行へのアクセスを確認します。

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

`column_user` ユーザーで ClickHouse client にログインします

clickhouse-client --user column_user --password password

すべてのカラムを指定して `SELECT` をテストします

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)

すべてのカラムを指定していますが、このユーザーにアクセスが許可されているのは id と column1 のみであるため、アクセスは拒否されます

指定されていて、かつ許可されているカラムのみを使った `SELECT` クエリを確認します:

SELECT
    id,
    column1
FROM db1.table1

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

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

行制限付きユーザーでロール権限をテストする

`row_user` を使用して ClickHouse client にログインする

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

上記の 2 行だけが返されることを確認してください。column1 の値が B の行は除外されているはずです。

ユーザーとロールの変更

必要な権限の組み合わせに応じて、ユーザーには複数のロールを割り当てることができます。複数のロールを使用する場合、システムはそれらを組み合わせて権限を判定するため、結果としてロールの権限は累積されます。たとえば、role1 が column1 に対する SELECT のみを許可し、role2 が column1 と column2 に対する SELECT を許可している場合、ユーザーは両方のカラムにアクセスできます。

admin アカウントを使用して、デフォルトロール付きで行とカラムの両方に制限をかけた新しいユーザーを作成する

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

`A_rows_users` ロールの既存の権限を取り消す

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

`A_row_users` ロールが `column1` のみを SELECT できるようにする

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

`row_and_column_user` で ClickHouse client にログインする

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

ClickHouse のロールを一覧表示する

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

ロール、ポリシー、ユーザーを管理するためのコマンド例

次のコマンドでは、以下の操作を実行できます。

権限を削除する
ポリシーを削除する
ユーザーのロール割り当てを解除する
ユーザーとロールを削除する

これらのコマンドは、admin ユーザーまたは default ユーザーとして実行してください

ロールから権限を削除する

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 のユーザーとロールの作成の基本と、ユーザーおよびロールの権限を設定・変更する手順を説明しました。各項目の詳細については、ユーザーガイドとリファレンスドキュメントを参照してください。

​Admin ユーザー

​パスワードレス認証

​きめ細かなアクセス制御

​管理者権限を確認する

​非管理者ユーザー

​準備

​サンプルのデータベース、テーブル、および行を作成する

テスト用データベースを作成する

テーブルを作成する

テーブルにサンプル行を追加する

テーブルを確認する

column_user を作成する

row_user を作成する

​ロールの作成

このロールのユーザーが database db1 の table1 で column1 のみを参照できるよう制限するロールを作成します

column1 の参照を許可する権限を設定します

ユーザー column_user を column1_users ロールに追加します

このロールのユーザーが選択された行のみを参照できるよう制限するロールを作成します。この例では、column1 に A を含む行のみです

row_user を A_rows_users ロールに追加します

column1 の値が A の場合にのみ参照を許可するポリシーを作成します

database と table に対する権限を設定します

他のロールが引き続きすべての行にアクセスできるよう、明示的な権限を付与します

​確認

​カラム制限付きユーザーでロール権限をテストする

clickhouse_admin ユーザーで ClickHouse client にログインします

admin ユーザーでデータベース、テーブル、およびすべての行へのアクセスを確認します。

column_user ユーザーで ClickHouse client にログインします

すべてのカラムを指定して SELECT をテストします

指定されていて、かつ許可されているカラムのみを使った SELECT クエリを確認します:

​行制限付きユーザーでロール権限をテストする

row_user を使用して ClickHouse client にログインする

表示される行を確認する

​ユーザーとロールの変更

admin アカウントを使用して、デフォルトロール付きで行とカラムの両方に制限をかけた新しいユーザーを作成する

A_rows_users ロールの既存の権限を取り消す

A_row_users ロールが column1 のみを SELECT できるようにする

row_and_column_user で ClickHouse client にログインする

すべてのカラムでテストする:

許可されたカラムのみに絞ってテストする:

​トラブルシューティング

​ユーザーの権限とロールを一覧表示する

​ClickHouse のロールを一覧表示する

​ポリシーを表示

​ポリシーの定義内容と現在の権限を確認する

​ロール、ポリシー、ユーザーを管理するためのコマンド例

​ロールから権限を削除する

​ポリシーを削除する

​ユーザーからロールの割り当てを解除する

​ロールを削除する

​ユーザーを削除する

​要約

Admin ユーザー

パスワードレス認証

きめ細かなアクセス制御

管理者権限を確認する

非管理者ユーザー

準備

サンプルのデータベース、テーブル、および行を作成する

`column_user` を作成する

`row_user` を作成する

ロールの作成

このロールのユーザーが database `db1` の `table1` で `column1` のみを参照できるよう制限するロールを作成します

`column1` の参照を許可する権限を設定します

ユーザー `column_user` を `column1_users` ロールに追加します

このロールのユーザーが選択された行のみを参照できるよう制限するロールを作成します。この例では、`column1` に `A` を含む行のみです

`row_user` を `A_rows_users` ロールに追加します

`column1` の値が `A` の場合にのみ参照を許可するポリシーを作成します

確認

カラム制限付きユーザーでロール権限をテストする

`clickhouse_admin` ユーザーで ClickHouse client にログインします

`column_user` ユーザーで ClickHouse client にログインします

すべてのカラムを指定して `SELECT` をテストします

指定されていて、かつ許可されているカラムのみを使った `SELECT` クエリを確認します:

行制限付きユーザーでロール権限をテストする

`row_user` を使用して ClickHouse client にログインする

ユーザーとロールの変更

`A_rows_users` ロールの既存の権限を取り消す

`A_row_users` ロールが `column1` のみを SELECT できるようにする

`row_and_column_user` で ClickHouse client にログインする

トラブルシューティング

ユーザーの権限とロールを一覧表示する

ClickHouse のロールを一覧表示する

ポリシーを表示

ポリシーの定義内容と現在の権限を確認する

ロール、ポリシー、ユーザーを管理するためのコマンド例

ロールから権限を削除する

ポリシーを削除する

ユーザーからロールの割り当てを解除する

ロールを削除する

ユーザーを削除する

要約