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

万能修实验室 2021-07-14

3557

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

纵观历史上的知名删库跑路战役，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建议使用以下方式管理用户：

为default用户启用 SQL方式的访问控制
用default用户登录并创建所有必需的用户。
建议创建一个管理员帐户:
GRANT ALL ON *.* TO ch_dba WITH GRANT OPTION
限制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 LOCAL

Received 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 LOCAL

Ok.

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, token

bj80 :) GRANT ALL ON *.* TO ch_dba WITH GRANT OPTION;

GRANT ALL ON *.* TO ch_dba WITH GRANT OPTION

Ok.

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) VALUES


Received 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 123

ClickHouse 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_pass123

ClickHouse 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 :) 

复制

数据库 clickhouse

文章转载自万能修实验室，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

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

评论

相关阅读