常用访问管理查询
如果您正在使用自管理 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，具有只读权限。
默认情况下，管理员用户被分配 sql_console_admin 角色，因此对他们来说没有任何变化。然而，sql_console_read_only 角色允许非管理员用户被授予对任何实例的只读或完全访问权限。管理员需要配置此访问权限。可以使用 GRANT 或 REVOKE 命令调整角色，以更好地适应特定实例的需求，并且对这些角色所做的任何修改都将被持久化。
细粒度访问控制
此访问控制功能也可以手动配置，以实现用户级别的细粒度。在将新的 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:[email protected]`;
CREATE ROLE OR REPLACE `sql-console-role:[email protected]`;
CREATE ROLE OR REPLACE `sql-console-role:[email protected]`;
GRANT sql_console_sa_role to `sql-console-role:[email protected]`;
GRANT sql_console_sa_role to `sql-console-role:[email protected]`;
GRANT sql_console_pm_role to `sql-console-role:[email protected]`;
测试管理员权限
以用户 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.table1Query 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';
创建角色
通过这组示例
- 将创建用于不同权限的角色，例如列和行
- 权限将被授予角色
- 用户将被分配给每个角色
角色用于为某些权限定义用户组，而不是单独管理每个用户。
-
创建一个角色，以限制此角色的用户只能在数据库
db1和表table1中看到column1CREATE ROLE column1_users; -
设置权限以允许查看
column1GRANT 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;注意当将策略附加到表时，系统将应用该策略，只有定义的那些用户和角色才能对表执行操作，所有其他用户将被拒绝任何操作。为了不将限制性行策略应用于其他用户，必须定义另一个策略以允许其他用户和角色具有常规或其他类型的访问权限。
验证
使用列限制用户测试角色权限
-
使用
clickhouse_admin用户登录 clickhouse 客户端clickhouse-client --user clickhouse_admin --password password -
使用管理员用户验证对数据库、表和所有行的访问。
SELECT *
FROM db1.table1Query id: f5e906ea-10c6-45b0-b649-36334902d31d
┌─id─┬─column1─┬─column2─┐
│ 1 │ A │ abc │
│ 2 │ A │ def │
│ 3 │ B │ abc │
│ 4 │ B │ def │
└────┴─────────┴─────────┘ -
使用
column_user用户登录 ClickHouse 客户端clickhouse-client --user column_user --password password -
测试使用所有列的
SELECTSELECT *
FROM db1.table1Query 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.table1Query id: cef9a083-d5ce-42ff-9678-f08dc60d4bb9
┌─id─┬─column1─┐
│ 1 │ A │
│ 2 │ A │
│ 3 │ B │
│ 4 │ B │
└────┴─────────┘
使用行限制用户测试角色权限
-
使用
row_user用户登录 ClickHouse 客户端clickhouse-client --user row_user --password password -
查看可用的行
SELECT *
FROM db1.table1Query id: a79a113c-1eca-4c3f-be6e-d034f9a220fb
┌─id─┬─column1─┬─column2─┐
│ 1 │ A │ abc │
│ 2 │ A │ def │
└────┴─────────┴─────────┘注意验证是否仅返回以上两行，应排除
column1中值为B的行。
修改用户和角色
可以为用户分配多个角色，以满足所需的权限组合。当使用多个角色时，系统将组合角色以确定权限，最终效果是角色权限将是累积的。
例如，如果 role1 仅允许对 column1 进行 select 操作，而 role2 允许对 column1 和 column2 进行 select 操作，则用户将有权访问这两列。
-
使用管理员帐户，创建新用户以使用默认角色按行和列进行限制
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; -
使用
row_and_column_user用户登录 ClickHouse 客户端clickhouse-client --user row_and_column_user --password password; -
使用所有列进行测试
SELECT *
FROM db1.table1Query 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.table1Query 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 用户和角色的基础知识，并提供了设置和修改用户和角色权限的步骤。有关每个步骤的更详细信息，请参阅我们的用户指南和参考文档。