本文介绍ClickHouse RBAC访问控制模型。包括如何启用SQL管理,创建管理员用户,创建角色,授权,细粒度列和行级授权。并通过示例进行验证实现过程。
启用RBAC
在users.xml中启用SQL用户模式,在admin用户下加入下面内容。
<access_management>1</access_management>
<named_collection_control>1</named_collection_control>
<show_named_collections>1</show_named_collections>
<show_named_collections_secrets>1</show_named_collections_secrets>
default用户主要用于内部或后台操作,使用密码设置反而不方便,因为你必须在许多配置部分中更改它。最佳方式是保护default用户,仅允许在localhost或信任网络中。
<clickhouse>
<users>
<default>
......
<networks>
<ip>127.0.0.1/8</ip>
<ip>10.10.10.0/24</ip>
</networks>
......
</default>
</clickhouse>
现在我们创建dba用户,并启用SQL管理功能。
创建admin用户
创建DBA用户,就如MySQL中root,这里为admin:
<clickhouse>
<users>
<default>
....
</default>
<admin>
<!--
Password could be specified in plaintext or in SHA256 (in hex format).
If you want to specify password in plaintext (not recommended), place it in 'password' element.
Example: <password>qwerty</password>.
Password could be empty.
If you want to specify SHA256, place it in 'password_sha256_hex' element.
Example: <password_sha256_hex>65e84be33532fb784c48129675f9eff3a682b27168c0ea744b2cf58ee02337c5</password_sha256_hex>
Restrictions of SHA256: impossibility to connect to ClickHouse using MySQL JS client (as of July 2019).
If you want to specify double SHA1, place it in 'password_double_sha1_hex' element.
Example: <password_double_sha1_hex>e395796d6546b1b65db9d665cd43f0e858dd4303</password_double_sha1_hex>
-->
<password></password>
<networks>
<ip>::/0</ip>
</networks>
<!-- Settings profile for user. -->
<profile>default</profile>
<!-- Quota for user. -->
<quota>default</quota>
<!-- Set This parameter to Enable RBAC
Admin user can create other users and grant rights to them. -->
<access_management>1</access_management>
<named_collection_control>1</named_collection_control>
<show_named_collections>1</show_named_collections>
<show_named_collections_secrets>1</show_named_collections_secrets>
</admin>
...
</clickhouse>
admin为用户名,密码有三种方式支持明文和加密方式(参考注释)。然后是配置网络访问,为了安全dba也建议设置专门管理ip进行访问。profile配置查询资源配额,quota配置熔断资源配额。最后4行是重点,启动SQL管理。
创建用户和角色
现在可以像其他数据库一样,实用通用RBAC方法创建角色和用户,给角色授权,为不同应用创建用户等。
示例
创建三个角色:dba, dashboard_ro, ingester_rw
create role dba on cluster '{cluster}';
grant all on *.* to dba on cluster '{cluster}';
create user `user1` identified by 'pass1234' on cluster '{cluster}';
grant dba to user1 on cluster '{cluster}';
-- dashboard_ro 为只读角色,仅能访问default资源,通用dictGet访问字典
create role dashboard_ro on cluster '{cluster}';
grant select on default.* to dashboard_ro on cluster '{cluster}';
grant dictGet on *.* to dashboard_ro on cluster '{cluster}';
-- 给dashboard_ro设置熔断配额
create settings profile or replace profile_dashboard_ro on cluster '{cluster}'
settings max_concurrent_queries_for_user = 10 READONLY,
max_threads = 16 READONLY,
max_memory_usage_for_user = '30G' READONLY,
max_memory_usage = '30G' READONLY,
max_execution_time = 60 READONLY,
max_rows_to_read = 1000000000 READONLY,
max_bytes_to_read = '5000G' READONLY
TO dashboard_ro;
-- 创建用户,并授权dashboard_ro角色
create user `dash1` identified by 'pass1234' on cluster '{cluster}';
grant dashboard_ro to dash1 on cluster '{cluster}';
-- 创建读写权限角色
create role ingester_rw on cluster '{cluster}';
grant select,insert on default.* to ingester_rw on cluster '{cluster}';
create settings profile or replace profile_ingester_rw on cluster '{cluster}'
settings max_concurrent_queries_for_user = 40 READONLY, -- user can run 40 queries (select, insert ...) simultaneously
max_threads = 10 READONLY, -- each query can use up to 10 cpu (READONLY means user cannot override a value)
max_memory_usage_for_user = '30G' READONLY, -- all queries of the user can use up to 30G RAM
max_memory_usage = '25G' READONLY, -- each query can use up to 25G RAM
max_execution_time = 200 READONLY, -- each query can executes no longer 200 seconds
max_rows_to_read = 1000000000 READONLY, -- each query can read up to 1 billion rows
max_bytes_to_read = '5000G' READONLY -- each query can read up to 5 TB from a MergeTree
TO ingester_rw;
-- 创建用户并授权角色
create user `ingester_app1` identified by 'pass1234' on cluster '{cluster}';
grant ingester_rw to ingester_app1 on cluster '{cluster}';
创建用户还有更多选项,举例限定IP地址:
create user if not exists benjaminwootton_ip_restricted
identified with plaintext_password by 'password321' host ip '192.168.0.0/16';
检查
$ clickhouse-client -u dash1 --password pass1234
create table test ( A Int64) Engine=Log;
DB::Exception: dash1: Not enough privileges
$ clickhouse-client -u user1 --password pass1234
create table test ( A Int64) Engine=Log;
Ok.
drop table test;
Ok.
$ clickhouse-client -u ingester_app1 --password pass1234
select count() from system.numbers limit 1000000000000;
DB::Exception: Received from localhost:9000. DB::Exception: Limit for rows or bytes to read exceeded, max rows: 1.00 billion
清理测试数据
show profiles;
┌─name─────────────────┐
│ default │
│ profile_dashboard_ro │
│ profile_ingester_rw │
│ readonly │
└──────────────────────┘
drop profile if exists readonly on cluster '{cluster}';
drop profile if exists profile_dashboard_ro on cluster '{cluster}';
drop profile if exists profile_ingester_rw on cluster '{cluster}';
show roles;
┌─name─────────┐
│ dashboard_ro │
│ dba │
│ ingester_rw │
└──────────────┘
drop role if exists dba on cluster '{cluster}';
drop role if exists dashboard_ro on cluster '{cluster}';
drop role if exists ingester_rw on cluster '{cluster}';
show users;
┌─name──────────┐
│ dash1 │
│ default │
│ ingester_app1 │
│ user1 │
└───────────────┘
drop user if exists ingester_app1 on cluster '{cluster}';
drop user if exists user1 on cluster '{cluster}';
drop user if exists dash1 on cluster '{cluster}';
更细粒度控制示例
创建两个角色SALESPERSON 和 SALESMANAGER,两者有不同的权限:
SALESPERSON
- 能读写customers
- 能写sales
SALESMANAGER
- 能写customers
- 能写sales
- 能读employees
创建角色:
create role if not exists salesperson;
create role if not exists salesmanager;
给角色授权:
grant select on db.customers TO salesperson;
grant insert, select on db.sales TO salesperson;
grant insert on db.customers TO salesmanager;
grant insert on db.sales TO salesmanager;
grant select on db.employees to salesmanager;
给用户绑定角色:
grant salesmanager to testuser1;
-- 也可以创建用户时直接绑定
create user if not exists testuser2
identified with plaintext_password by 'password321'
default role salesperson
限制列权限
RBAC模型中最后组件是权限,它描述了对特定数据库对象执行特定类型查询的权限。举例,下面查询中,销售管理者角色可以查询表特定字段:
grant select(order_id,pizza_type) ON db.pizza_orders
to salesmanager with grant option
上面示例给角色授权,ClickHouse也支持给用户直接授权:
grant select(order_id,pizza_type) ON db.pizza_orders
to testuser1 with grant option
WITH GRANT OPTION子句意味着我们授予权限的用户反过来有权将相同的权限授予其他用户。在上面的例子中,我们可能让销售经理将读取数据的权限下放给他们的员工,但销售人员可能不会拥有同样的权限。具体的配置将取决于具体业务需要。
限制行权限(行策略)
除了按列限制数据访问外,还可以按行以更细粒度的方式限制用户可以看到的数据。这可以通过ROW POLICY对象来实现,它接受一个SQL查询,说明给定的用户或角色可以访问哪些行:
create row policy low_value_orders_policy on
mydb.pizza_orders USING pizza_value < 1000 TO salesperson