常见访问管理查询 - ClickHouse Documentation

自管理如果你使用的是自管理 ClickHouse，请参阅 SQL 用户和角色。

本文介绍定义 SQL 用户和角色的基础知识，以及如何将这些特权和权限应用于数据库、表、行和列。

管理员用户

ClickHouse Cloud 服务有一个管理员用户 default，会在服务创建时自动创建。密码会在创建服务时提供，并且拥有 Admin 角色的 ClickHouse Cloud 用户可以重置该密码。当你为 ClickHouse Cloud 服务添加额外的 SQL 用户时，他们需要提供 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 控制台时，你的 SQL 语句不会以 default 用户身份执行。相反，这些语句会以名为 sql-console:${cloud_login_email} 的用户身份执行，其中 cloud_login_email 是当前运行查询的用户的电子邮件地址。这些自动生成的 SQL 控制台用户具有 default 角色。

无密码身份验证

SQL 控制台提供两个角色：sql_console_admin，其权限与 default_role 完全一致；以及 sql_console_read_only，具有只读权限。 Admin 用户默认会被分配 sql_console_admin 角色，因此对他们来说无需做任何更改。不过，sql_console_read_only 角色使非 Admin 用户也可以被授予任意 instance 的只读或完全访问权限。此类访问需要由 Admin 配置。可以使用 GRANT 或 REVOKE 命令调整这些角色，以更好地满足特定 instance 的需求，并且对这些角色所做的任何修改都会被保留。

细粒度访问控制

此访问控制功能也支持手动配置到用户级粒度。为用户分配新的 sql_console_* 角色之前，应先创建与命名空间 sql-console-role:<email> 对应的 SQL 控制台用户专用数据库角色。例如：

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

创建角色

通过这组示例，您将了解如何：

创建具有不同权限的角色，例如针对列和行的角色
为角色授予权限
将用户分配给各个角色

角色用于为特定权限定义用户组，而不是逐个管理用户。

创建一个角色，将该角色的用户限制为只能查看数据库 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;

为数据库和表设置权限

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;

将策略附加到表后，系统会应用该策略，只有策略中定义的用户和角色才能对该表执行操作，其他所有用户和角色都将被拒绝执行任何操作。为了避免将这种限制性的行策略应用到其他用户，必须另外定义一条策略，以允许其他用户和角色保留常规访问权限或其他类型的访问权限。

验证

使用列受限用户测试角色权限

验证管理员用户对数据库、表和所有行的访问权限。

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

测试使用所有列执行 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       │
└────┴─────────┘

使用行级受限用户测试角色权限

查看可访问的行

SELECT *
FROM db1.table1

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

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

确认只返回上述两行，column1 中值为 B 的行应被排除。

修改用户和角色

可以为用户分配多个角色，以组合获得所需的权限。使用多个角色时，系统会将这些角色合并后再判定权限，最终效果是各角色的权限会累加生效。例如，如果 role1 只允许查询 column1，而 role2 允许查询 column1 和 column2，那么该用户将有权访问这两列。

使用管理员账户，创建一个按行和列限制且带默认角色的新用户

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

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

使用所有列进行测试：

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

管理角色、策略和用户的示例命令

以下命令可用于：

删除权限
删除策略
将用户从角色中移除
删除用户和角色

请以管理员用户或 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 用户和角色的基础知识，并说明了如何为用户和角色设置及修改权限。有关各项内容的更多信息，请参阅我们的用户指南和参考文档。

​管理员用户

​无密码身份验证

​细粒度访问控制

​测试管理员权限

​非管理员用户

​准备工作

​创建示例数据库、表和数据行

​创建角色

​验证

​使用列受限用户测试角色权限

​使用行级受限用户测试角色权限

​修改用户和角色

​故障排查

​列出用户的授权和角色

​查看 ClickHouse 中的角色

​查看策略

​查看策略定义及当前权限

​管理角色、策略和用户的示例命令

​撤销角色权限

​删除策略

​取消向用户分配角色

​删除角色

​删除用户

​总结

管理员用户

无密码身份验证

细粒度访问控制

测试管理员权限

非管理员用户

准备工作

创建示例数据库、表和数据行

创建角色

验证

使用列受限用户测试角色权限

使用行级受限用户测试角色权限

修改用户和角色

故障排查

列出用户的授权和角色

查看 ClickHouse 中的角色

查看策略

查看策略定义及当前权限

管理角色、策略和用户的示例命令

撤销角色权限

删除策略

取消向用户分配角色

删除角色

删除用户

总结