ClickHouse: 权限控制-喀秋莎发射指北

古语有云：“道路千万条，安全第一条，开车不规范，亲人两行泪”

纵观历史上的知名删库跑路战役，MongoDB、ES、Redis之前都出过由于权限缺失导致的重大安全事故。

ClickHouse 的访问控制包括以下元素：

- 用户帐户

- 角色(Role)

- 行策略(Row Policy)

- 设置配置文件(Profile)

- 配额(Quota)

配置访问权限方式有两种：

• SQL语句的工作流，类似MySQL。默认关闭需要启用此功能。

• 服务器配置文件 users.xml 和 config.xml 

目前两种方式同时生效，如果使用服务器配置文件来管理用户和访问权限，可以轻松地过渡到基于SQL语句的。

但是要注意：不能通过两种配置方法同时管理同一访问实体。

下面就对这两种方式分别测试下。

基于SQL方式的权限管理

官方建议使用SQL语句的方式来管理权限，虽然这是在最近版本中才加入的。

ClickHouse新版本已支持基于RBAC方法的访问控制管理

关于RBAC：

https://en.wikipedia.org/wiki/Role-based_access_control

默认情况下，ClickHouse服务器使用用户default连接数据库，它具有所有的权限，不需要密码。但却是基于配置文件管理的。

新部署的ClickHouse建议使用以下方式管理用户：

1. 为default用户启用 SQL方式 的访问控制

2. 用default用户登录并创建所有必需的用户。

   建议创建一个管理员帐户:

   GRANT ALL ON *.* TO ch_dba WITH GRANT OPTION

3. 限制default用户权限，并禁用 SQL方式 访问控制和帐户管理。

举个例子：

# 直接建立用户会报错：

bj80 :) CREATE USER ch_dba HOST IP '127.0.0.1' IDENTIFIED WITH sha256_password BY 'qwerty'; CREATE USER ch_dba IDENTIFIED WITH sha256_hash BY '65E84BE33532FB784C48129675F9EFF3A682B27168C0EA744B2CF58EE02337C5' HOST LOCALReceived exception from server (version 20.4.2):
Code: 497. DB::Exception: Received from localhost:9000. DB::Exception: default: Not enough privileges. To execute this query it s necessary to have the grant CREATE USER ON *.*. 0 rows in set. Elapsed: 0.811 sec. 

# 修改配置文件 

    /etc/clickhouse-server/users.xml

# 在user  default 用户里添加一行 access_management 参数启动SQL方式认证 (1启动 0禁止，默认0)

    <users><default><!-- User can create other users and grant rights to them. --><!-- <access_management>1</access_management> --><access_management>1</access_management></default></users>

# 再次建用户成功了：

bj80 :) CREATE USER ch_dba HOST IP '127.0.0.1' IDENTIFIED WITH sha256_password BY 'qwerty';CREATE USER ch_dba IDENTIFIED WITH sha256_hash BY '65E84BE33532FB784C48129675F9EFF3A682B27168C0EA744B2CF58EE02337C5' HOST LOCALOk.0 rows in set. Elapsed: 0.001 sec. bj80 :) 

# 对DBA用户进行授权：(官方文档中的语法有误，WITH GRANT OPTION要放在最后)

bj80 :) GRANT ALL ON *.* WITH GRANT OPTION TO ch_dba;Syntax error: failed at position 18:GRANT ALL ON *.* WITH GRANT OPTION TO ch_dba;Expected one of: Comma, ON, TO, tokenbj80 :) GRANT ALL ON *.* TO ch_dba WITH GRANT OPTION;GRANT ALL ON *.* TO ch_dba WITH GRANT OPTIONOk.0 rows in set. Elapsed: 0.001 sec. bj80 :) 

# 检查下权限：

bj80 :) show create user ch_dba;SHOW CREATE USER ch_dba┌─CREATE USER ch_dba────────────┐
│ CREATE USER ch_dba HOST LOCAL │
└───────────────────────────────┘1 rows in set. Elapsed: 0.001 sec. bj80 :) show grants for ch_dba;SHOW GRANTS FOR ch_dba┌─GRANTS FOR ch_dba────────────────────────────┐
│ GRANT ALL ON *.* TO ch_dba WITH GRANT OPTION │
└──────────────────────────────────────────────┘1 rows in set. Elapsed: 0.001 sec. 

建立的用户和权限，存储在config.xml配置文件access_control_path参数指定的路径，默认值：

/var/lib/clickhouse/access/

肉眼观察 .list 文件的名字就知道用途了：

[root@bj80 access]# pwd
/var/lib/clickhouse/access
[root@bj80 access]# ll
total 24
-rw-r----- 1 clickhouse clickhouse 182 May 15 16:00 8d420b64-b3b9-6e2b-b2c4-1e768913f09d.sql
-rw-r----- 1 clickhouse clickhouse   1 May 14 16:34 quotas.list
-rw-r----- 1 clickhouse clickhouse   1 May 14 16:34 roles.list
-rw-r----- 1 clickhouse clickhouse   1 May 14 16:34 row_policies.list
-rw-r----- 1 clickhouse clickhouse   1 May 14 16:34 settings_profiles.list
-rw-r----- 1 clickhouse clickhouse  44 May 15 15:47 users.list

.sql文件记录了用户授权的SQL，对应users.list中的加密串：

[root@bj80 access]# more users.list
ch_dba8d420b64-b3b9-6e2b-b2c4-1e768913f09d
[root@bj80 access]# more 8d420b64-b3b9-6e2b-b2c4-1e768913f09d.sql
ATTACH USER ch_dba IDENTIFIED WITH sha256_hash BY '****' HOST LOCAL;
ATTACH GRANT ALL ON *.* TO ch_dba WITH GRANT OPTION;
[root@bj80 access]# 

额外说明几点：

• 即使数据库和表不存在，也是可以对其授权的。

• 表被删除也不会撤消与该表对应的所有授权。

• 如果以后创建同名的新表，授权依然存在。

• 要撤消已删除表所有的权限，需执行：

  REVOKE ALL PRIVILEGES ON db.table FROM ALL 

• 权限没有生命周期设置

下面是一些权限相关的命令与用法：

# 用户相关：

• CREATE USER

• ALTER USER

• DROP USER

• SHOW CREATE USER

# 角色相关：

• CREATE ROLE

• ALTER ROLE

• DROP ROLE

• SET ROLE

• SET DEFAULT ROLE

• SHOW CREATE ROLE

这个和所有数据库的角色功能都差不多。

例如：

CREATE ROLE test_role;
GRANT SELECT ON db.* TO test_role;
GRANT test_role TO user_test;

# 行策略(Row Policy)相关：

• CREATE ROW POLICY

• ALTER ROW POLICY

• DROP ROW POLICY

• SHOW CREATE ROW POLICY

其实就是一个过滤器，用于定义用户或角色可以使用哪些行。

例子：

CREATE ROW POLICY filter ON mydb.mytable FOR SELECT USING a<1000 TO accountant, john@localhost
CREATE ROW POLICY filter ON mydb.mytable FOR SELECT USING a<1000 TO ALL EXCEPT mira  # 对所有用户生效，除了mira

# 配置文件(Profile)相关：

• CREATE SETTINGS PROFILE

• ALTER SETTINGS PROFILE

• DROP SETTINGS PROFILE

• SHOW CREATE SETTINGS PROFILE

可以设置一些限制并分配给用户或角色，并禁止它们通过SET查询更改某些设置。：

创建max_memory_usage_profile配置文件来限制max_memory_usage范围，并分配给robin：

CREATE SETTINGS PROFILE max_memory_usage_profile 
SETTINGS max_memory_usage = 100000001 MIN 90000000 MAX 110000000 TO robin

# Quota配额相关：

• CREATE QUOTA

• ALTER QUOTA

• DROP QUOTA

• SHOW CREATE QUOTA

限制了资源的使用，包含一组持续时间的限制，以及应使用此配额的角色、用户列表。

例子：在60分钟内，限制当前用户的最大查询数为100

CREATE QUOTA qA FOR INTERVAL 60 MINUTE MAX QUERIES 100 TO CURRENT_USER

基于配置文件的权限管理

配置文件方式的权限管理主要分3大块，基本和上面的差不多：

[root@VM_0_97_centos clickhouse-server]# cat users.xml 
<?xml version="1.0"?>
<yandex><!-- 用户配置 --><profiles></profiles><!-- 用户信息和访问控制，可以指定使用的profile和quota --><users></users><!-- 配额Quotas. --><quotas></quotas>
</yandex>

# 1. Profiles设置约束

https://clickhouse.tech/docs/en/operations/settings/constraints-on-settings/

用途：禁止用户通过SET查询更改某些设置。

如果用户尝试违反约束，则将引发异常并且设置不会更改。

目前支持三种类型的约束：min，max，readonly。min和max指定上下边界，可以组合使用。readonly约束指定用户根本无法更改相应的设置。

<profiles><default><max_memory_usage>10000000000</max_memory_usage><force_index_by_date>0</force_index_by_date>...<constraints><max_memory_usage><min>5000000000</min><max>20000000000</max></max_memory_usage><force_index_by_date><readonly/></force_index_by_date></constraints></default>
</profiles>

限制后以下查询均引发异常：

SET max_memory_usage=20000000001;
Code: 452, e.displayText() = DB::Exception: Setting max_memory_usage should not be greater than 20000000000.SET max_memory_usage=4999999999;
Code: 452, e.displayText() = DB::Exception: Setting max_memory_usage should not be less than 5000000000.SET force_index_by_date=1;
Code: 452, e.displayText() = DB::Exception: Setting force_index_by_date should not be changed.

# 2. Quotas配额

https://clickhouse.tech/docs/en/operations/quotas/

如下：

<!-- Quotas -->
<quotas><!-- Quota name. --><default><!-- Restrictions for a time period. You can set many intervals with different restrictions. --><interval><!-- Length of the interval. --><duration>3600</duration><!-- Unlimited. Just collect data for the specified time interval. --><queries>0</queries><errors>0</errors><result_rows>0</result_rows><read_rows>0</read_rows><execution_time>0</execution_time></interval></default>
</quotas>    

以上是对于“ default ”配额，每3600秒（1小时）的设置限制。

3600秒间隔结束时，将清除所有收集的值。接下来的一个小时，配额计算重新开始。

各种限制：

• queries   –请求总数。

• errors    –异常查询数。

• result_rows –结果返回总行数。

• read_rows –从表中读取的源行总数，以在所有远程服务器上运行查询。

• execution_time –查询总时间，单位秒。

如果在至少一个时间间隔内超过了限制，则将引发一个异常，并显示一条文本，内容是关于超出限制，针对哪个间隔以及何时开始新的间隔（何时可以再次发送查询）。

# 3. Users用户

这里包含用户名，密码，IP限制，允许访问的数据库和字典等信息

<users><web><password></password><networks incl="networks" /><profile>web</profile><quota>default</quota><allow_databases><database>test</database></allow_databases><allow_dictionaries><dictionary>test</dictionary></allow_dictionaries></web>
</users>

# 主要说下密码，有3种格式：

-1 不加密，使用明文密码（不建议）

  <password>qwerty</password>.

密码也可以为空

-2 使用 SHA256方式 加密  

生成加密的密码: （结果第一行是密码，第二行是加密密码）

[root@VM_0_97_centos ~]# PASSWORD=$(base64 < /dev/urandom | head -c8); echo "$PASSWORD"; echo -n "$PASSWORD" | sha256sum | tr -d '-'
OPZCWV/7
895d4a6ffad201712b1c65deb5d2b79fdfc385525d0d9efbbedc5ccdbff26f0c  

把加密后的密码，写到配置文件里:

 <password_sha256_hex>895d4a6ffad201712b1c65deb5d2b79fdfc385525d0d9efbbedc5ccdbff26f0c</password_sha256_hex>

注意：

为了与所有MySQL客户端兼容，建议在配置文件中使用双SHA1密码。

如果使用SHA256密码，某些客户端可能无法进行身份验证

Restrictions of SHA256: impossibility to connect to ClickHouse using MySQL JS client (as of July 2019).

-3 使用双 SHA1方式 加密  

生成加密的密码: （结果第一行是密码，第二行是加密密码）

[root@VM_0_97_centos ~]# PASSWORD=$(base64 < /dev/urandom | head -c8); echo "$PASSWORD"; echo -n "$PASSWORD" | sha1sum | tr -d '-' | xxd -r -p | sha1sum | tr -d '-'
RaCIkxIr
f19e600029895f54818170776a1b6882509803f4  

把加密后的密码，写到配置文件里:

 <password_double_sha1_hex>f19e600029895f54818170776a1b6882509803f4</password_double_sha1_hex>

下面测试下3种密码方式，建立对应3个用户：

# 修改后的配置：

         <test_user><password>123456</password>        <networks incl="networks" replace="replace"><ip>::/0</ip></networks><profile>readonly</profile><quota>default</quota><allow_databases><database>caihao</database><database>default</database></allow_databases></test_user><test_user_sha256><password_sha256_hex>895d4a6ffad201712b1c65deb5d2b79fdfc385525d0d9efbbedc5ccdbff26f0c</password_sha256_hex>        <networks incl="networks" replace="replace"><ip>::/0</ip></networks><profile>readonly</profile><quota>default</quota><allow_databases><database>default</database></allow_databases></test_user_sha256><test_user_sha1><password_double_sha1_hex>f19e600029895f54818170776a1b6882509803f4</password_double_sha1_hex>       <networks incl="networks" replace="replace"><ip>::/0</ip></networks><profile>readonly</profile><quota>default</quota><allow_databases><database>default</database></allow_databases></test_user_sha1>

# 默认可以访问所有数据库的，如果限制值访问某些数据库：

             <allow_databases><database>db_1</database><database>db_2</database></allow_databases>

# 测试连接，3个用户都可以：

clickhouse-client -u test_user        -h 127.0.0.1 --password 123456
clickhouse-client -u test_user_sha256 -h 127.0.0.1 --password OPZCWV/7
clickhouse-client -u test_user_sha1   -h 127.0.0.1 --password RaCIkxIr

再测试下用户的读写，分别再建立2个用户：

# 读写用户

         <user_wr><password>123</password>       <networks incl="networks" replace="replace"><ip>::/0</ip></networks><profile>default</profile><quota>default</quota></user_wr>

# 只读用户        

        <user_read><password>123</password>        <networks incl="networks" replace="replace"><ip>::/0</ip></networks><profile>readonly</profile><quota>default</quota></user_read>     

# 测试下：

clickhouse-client -u user_wr -h 127.0.0.1 --password 123
clickhouse-client -u user_read -h 127.0.0.1 --password 123

# 使用user_read 会有readonly mode提示：

VM_0_97_centos :) INSERT INTO caihao.test(id1, name1) VALUES (1, 'Ed'), (2, 'Ann'), (3, 'Emma');INSERT INTO caihao.test (id1, name1) VALUESReceived exception from server (version 20.3.5):
Code: 164. DB::Exception: Received from 127.0.0.1:9000. DB::Exception: user_read: Cannot execute query in readonly mode. 0 rows in set. Elapsed: 0.001 sec. 

随着数据库中的用户越来越多，user.xml中的配置会很乱，可以在users.d目录下，为每个用户提供单独的配置文件。

# 为新用户meph建立独立文件

/etc/clickhouse-server/users.d/meph.xml

<yandex><users><meph><profile>default</profile><networks><ip>::/0</ip></networks><password_sha256_hex>895d4a6ffad201712b1c65deb5d2b79fdfc385525d0d9efbbedc5ccdbff26f0c</password_sha256_hex><quota>default</quota></meph></users>
</yandex>

# 使用新用户连接数据库

[root@VM_0_97_centos ~]# clickhouse-client -u meph -h 127.0.0.1 --password OPZCWV/7
ClickHouse client version 20.3.5.21 (official build).
Connecting to 127.0.0.1:9000 as user meph.
Connected to ClickHouse server version 20.3.5 revision 54433.VM_0_97_centos :) 

要注意的是，users.d下定义的用户，是可以覆盖 users.xml 中的配置的。

比如上面的测试建立过用户user_wr，密码在users.xml中配置的是123。

我在users.d/下建立一个user_wr.xml配置并使用新密码new_pass123来覆盖它。

vi /etc/clickhouse-server/users.d/user_wr.xml<yandex><users><user_wr><password>new_pass123</password>       <networks incl="networks" replace="replace"><ip>::/0</ip></networks><profile>default</profile><quota>default</quota></user_wr></users>
</yandex>

# 再使用原密码已无法登陆

[root@VM_0_97_centos ~]# clickhouse-client -u user_wr -h 127.0.0.1 --password 123ClickHouse client version 20.3.5.21 (official build).Connecting to 127.0.0.1:9000 as user user_wr.Code: 516. DB::Exception: Received from 127.0.0.1:9000. DB::Exception: user_wr: Authentication failed: password is incorrect or there is no user with such name. 

# 试试新密码，是OK的。

[root@VM_0_97_centos ~]# clickhouse-client -u user_wr -h 127.0.0.1 --password new_pass123ClickHouse client version 20.3.5.21 (official build).Connecting to 127.0.0.1:9000 as user user_wr.Connected to ClickHouse server version 20.3.5 revision 54433.VM_0_97_centos :)