ClickHouse—用户权限控制

ClickHouse权限控制介绍

ClickHouse使用Role-Based Access Control（RBAC），进行用户权限管理。ClickHouse对于权限管理涉及到4个实体：

• 用户（User account）
• 角色（Role）
• 权限策略（Row Policy）
• 配置文件（Settings Profile）
• 资源配额（Quota）

可以通过两种方式对ClickHouse进行权限控制。

• 类似于MySQL的SQL驱动权限控制（SQL-driven workflow）

该方法默认关闭，需要开启（具体操作请看下文）

SQL-driven workflow方式在20.1.2.4版本才开始支持

• 通过配置文件进行权限控制（users.xml和 config.xml）

值得一提的是ClickHouse对于配置文件是动态生效的

官方文档建议使用SQL驱动来控制权限。需要注意的是，这两种方式是同时生效的。但同时也要主要这两种权限控制释放产生冲突

除此之外，ClickHouse可以接入外部权限控制，如LDAP、Kerberos和SSL X.509证书认证。读者朋友可以参考官方文档 External User Authenticators and Directories

语忆科技在ClickHouse集群的权限控制上，除了控制访问，更多的用于对QUERY的资源配额，控制QUERY对于集群资源的使用。

---

本文主要介绍通过SQL语言控制权限

开启SQL驱动权限控制

1.在配置文件 config.xml 中设置配置存储路径，默认为 /var/lib/clickhouse/access/

<user_directories>
    ... ...
    <local_directory>
        <!-- Path to folder where users created by SQL commands are stored. -->
        <path>/var/lib/clickhouse/access/</path>
    </local_directory>
    ... ...
</user_directories>

2. 使用SQL驱动来控制权限和账户管理必须要在 users.xml 配置一个用户

<users>
    <!-- If user name was not specified, 'default' user is used. -->
    <user_name>
        <password></password>
        <!-- Or -->
        <password_sha256_hex></password_sha256_hex>

        <access_management>0|1</access_management>

        <networks incl="networks" replace="replace">
        </networks>

        <profile>profile_name</profile>

        <quota>default</quota>cd
        <default_database>default</default_database>
        <databases>
            <database_name>
                <table_name>
                    <filter>expression</filter>
                <table_name>
            </database_name>
        </databases>
    </user_name>
    <!-- Other users settings -->
</users>

• user_name

用户名

• password

密码支持使用明文或者密文，密文支持SHA256（Hex）和双重SHA1（Hex），建议使用密文存储，ClickHouse官方是使用SHA256，双重SHA1则是为了兼容MySQL客户端，以下是两种密文的生成方式，第一行是随机明文，第二行则是相对应的密文。

SHA256：

PASSWORD=$(base64 < /dev/urandom | head -c8); echo "$PASSWORD"; echo -n "$PASSWORD" | sha256sum | tr -d '-'

双重SHA1：

PASSWORD=$(base64 < /dev/urandom | head -c8); echo "$PASSWORD"; echo -n "$PASSWORD" | sha1sum | tr -d '-' | xxd -r -p | sha1sum | tr -d '-'

• access_management

该用户是否允许SQL驱动权限管理

• • 不允许：0
  • 允许：1

默认为0

• networks

ClickHouse白名单

• • <ip>：支持IP地址和子网掩码，如192.168.17.1、10.0.0.1/8、10.0.0.1/255.255.255.0、2a02:6b8::3
  • <host>：DNS，如example01.host.yuyidata
  • <host_regexp>：DNS正则，如^example\d\d-\d\d-\d\.host\.yuyidata$

如果要完全开放，则配置

<ip>::/0</ip>

• profile

该用户引用的配置，在<profiles> .. ... </profiles>中定义，可以参考官方文档 settings-profiles

• quota

该用户允许的使用的资源限制，可以限制该用户的QUERY所使用的物理资源，如内存，CPU等。由于ClickHouse默认允许单个QUERY可以使用当前ClickHouse所有的物理资源，所以这是一个非常重要的配置。可以参考官方文档 Quotas

• databases

下面配置的含义是该用户只能查询 id=1000 的数据

<user1>
    <databases>
        <database_name>
            <table1>
                <filter>id = 1000</filter>
            </table1>
        </database_name>
    </databases>
</user1>

用于控制该用户对于表的SELECT权限，如返回的查询的WHERE条件等，本质上是其实是为该用户的查询外面包了一层。<filter></filter>中支持任何以8位整型返回结果的表达式，目前不支持PREWHERE和WHERE→PREWHERE

权限用法

如上面的Demo，ClickHouse默认提供了一个default 用户，拥有所有权限，但默认关闭SQL-driven权限控制。

刚搭建ClickHouse的朋友可以执行以下步骤，来保证数据库安全

1. 打开default 用户的SQL-driven权限。
2. 使用default 用户进行登录，创建一个administrator用户并赋予所有权限(GRANT ALL ON *.* TO admin_user_account WITH GRANT OPTION)。
3. 撤销default 权限和关闭 SQL-Driven。

⚠️注意

当前版本的配置和权限是没有生命周期（lifetime）的，如

1. 可以对一个不存在的DataBase或者Table赋予权限
2. 如果一个表被删除了，这个表的权限不会被删除，当同名的表被创建，相应的权限仍然生效，所以一定会要注意在删除库/表的时候，撤销权限REVOKE ALL PRIVILEGES ON db.table FROM ALL

下面来介绍具体每一个实例

用户（User Account）

创建用户

CREATE USER [IF NOT EXISTS | OR REPLACE] name1 [ON CLUSTER cluster_name1]
        [, name2 [ON CLUSTER cluster_name2] ...]
    [NOT IDENTIFIED | IDENTIFIED {[WITH {no_password | plaintext_password | sha256_password | sha256_hash | double_sha1_password | double_sha1_hash}] BY {'password' | 'hash'}} | {WITH ldap SERVER 'server_name'} | {WITH kerberos [REALM 'realm']}]
    [HOST {LOCAL | NAME 'name' | REGEXP 'name_regexp' | IP 'address' | LIKE 'pattern'} [,...] | ANY | NONE]
    [DEFAULT ROLE role [,...]]
    [DEFAULT DATABASE database | NONE]
    [GRANTEES {user | role | ANY | NONE} [,...] [EXCEPT {user | role} [,...]]]
    [SETTINGS variable [= value] [MIN [=] min_value] [MAX [=] max_value] [READONLY | WRITABLE] | PROFILE 'profile_name'] [,...]

ON CLUSTER 可用于创建集群用户（如果你的ClickHouse为集群模式）

• Identification​

可以通过下面几种方式对用户设置认证，与 user.xml 相同效果。

• • IDENTIFIED WITH no_password
  • IDENTIFIED WITH plaintext_password BY 'qwerty'
  • IDENTIFIED WITH sha256_password BY 'qwerty' or IDENTIFIED BY 'password'
  • IDENTIFIED WITH sha256_hash BY 'hash' or IDENTIFIED WITH sha256_hash BY 'hash' SALT 'salt'
  • IDENTIFIED WITH double_sha1_password BY 'qwerty'
  • IDENTIFIED WITH double_sha1_hash BY 'hash'
  • IDENTIFIED WITH ldap SERVER 'server_name'
  • IDENTIFIED WITH kerberos or IDENTIFIED WITH kerberos REALM 'realm'

• User Host

用户白名单，与 user.xml 相同效果。如：

• • HOST IP 'ip_address_or_subnetwork' 。如: HOST IP '192.168.0.0/16', HOST IP '2001:DB8::/32' 。
  • HOST ANY — 默认，可以被任何网络用户连接。
  • HOST LOCAL — 只能本地连接。
  • HOST NAME 'fqdn' — 指定FQDN，如 HOST NAME 'mysite.com'。
  • HOST REGEXP 'regexp' — 使用正则，如HOST REGEXP '.*\.mysite\.com'。
  • HOST LIKE 'template' — 使用 LIKE. 当 HOST LIKE '%' 效果与HOST ANY相同， HOST LIKE '%.mysite.com' 允许来自 mysite.com 的域名访问。

• GRANTEES Clause

该用户可以给哪些对象授权，只能赋予其他对象自己有的权限。GRANTEES 支持下面4中对象

• • user — 可以给该用户授权，如张三给李四授权。
  • role — 可以给该角色授权，如张三给李四授权。
  • ANY — 可以给任何人授权，默认此选项。
  • NONE — 不能给任何人授权。

举一些栗子：

zhangsan只能本地访问，密码是qwerty，以sha256密文存储。

CREATE USER zhangsan HOST IP '127.0.0.1' IDENTIFIED WITH sha256_password BY 'qwerty';

zhangsan默认角色为role1和role2.

CREATE USER zhangsan DEFAULT ROLE role1, role2;

zhangsan现在没权限，但后面有人赋予给他他就有了

CREATE USER zhangsan DEFAULT ROLE ALL;

zhangsan现在没权限，但后面有人赋予给他他就有了，除了角色role1和role2

CREATE USER zhangsan DEFAULT ROLE ALLEXCEPT role1, role2;

zhangsan可以给lisi自己的所有权限

CREATE USER zhangsan GRANTEES lisi;

角色（Role）

CREATE ROLE [IF NOT EXISTS | OR REPLACE] name1 [, name2 ...]
    [SETTINGS variable [= value] [MIN [=] min_value] [MAX [=] max_value] [READONLY|WRITABLE] | PROFILE 'profile_name'] [,...]

一个用户可以被分配多个角色，用户所拥有的权限就是所拥有角色的的权限的交集。

查询权限（Row Policy）

⚠️注意：只有 readonly权限的角色才感知 Row Policy ,没办法限制拥有修改权限的用户

CREATE [ROW] POLICY [IF NOT EXISTS | OR REPLACE] policy_name1 [ON CLUSTER cluster_name1] ON [db1.]table1
        [, policy_name2 [ON CLUSTER cluster_name2] ON [db2.]table2 ...]
    [FOR SELECT] USING condition
    [AS {PERMISSIVE | RESTRICTIVE}]
    [TO {role1 [, role2 ...] | ALL | ALL EXCEPT role1 [, role2 ...]}]

ClickHouse的 Row Policy 本质上是限制用户/角色查询表的行限制，其实就是一个 WHERE 条件。

配额（Quota）

CREATE QUOTA [IF NOT EXISTS | OR REPLACE] name [ON CLUSTER cluster_name]
    [KEYED BY {user_name | ip_address | client_key | client_key,user_name | client_key,ip_address} | NOT KEYED]
    [FOR [RANDOMIZED] INTERVAL number {second | minute | hour | day | week | month | quarter | year}
        {MAX { {queries | query_selects | query_inserts | errors | result_rows | result_bytes | read_rows | read_bytes | execution_time} = number } [,...] |
         NO LIMITS | TRACKING ONLY} [,...]]
    [TO {role [,...] | ALL | ALL EXCEPT role [,...]}]

限制单位时间内，用户/角色可以执行的任务数和执行时间等。如查/写语句SELECT、INSERT；返回数量的限制，如返回的行数、返回的字节数等。限制可以作用于client ip、client key、user、role等。

Quota的限制一般生产环境用不到，开发测试环境可以限制限制。

限制（Restrictions）

对于查询的复杂限制，可以参考Restrictions

该功能还是比较实用，可以限制查询的各种性能，避免一个误查询导致生产环境崩溃。

大部分参数参数值默认为0，代表不做限制。Restrictions有三种处理异常的模式

• throw – 默认模式，当超出限制则直接抛异常。
• break – 停止查询并返回限制内的数量，比如限制了1G，查询结果为10G，这时候就返回前1G的内容。
• any (only for group_by_overflow_mode) – 专门作用于aggregation方法下，如group by等。和break相似，当group by前的数据集超过限制，聚合数据集为限制的大小，并继续执行聚合。

常用限制参数

• max_memory_usage

每个服务器每次查询使用的最大内存，默认为10GB，可以使用SHOW PROCESSLIST 查看当前查询使用的内存数（某些聚合下不会被监控）。当min,max,any,anyLast,argMin,argMax等聚合方法作用在String和Array 上时，该限制也不会被跟踪。max_memory_usage_for_user等同于该参数，不过是作用在用户上。

• read_overflow_mode

设置read操作的处理异常的模式

• max_rows_to_group_by

聚合操作使用数据集的最大行数

• group_by_overflow_mode

设置聚合操作的处理异常的模式

配置文件（Settings Profile）

综合配置ClickHouse设置，并且作为一个单独的实体。可以参考Settings Profile

Let's Try

打开SQL-Driven，在users.xml（一般在/etc/clickhouse-server/）下添加新用户，并限制default用户只能本机访问

    ... ....
    <users>
        <default>
            <password></password>
            <networks>
                <ip>127.0.0.1</ip>
            </networks>
            <profile>default</profile>
            <quota>default</quota>
        </default>
        
        <test_admin>
            <password>123456</password>
            <access_management>1</access_management>
            <networks>
                <ip>::/0</ip>
            </networks>

            <profile>default</profile>
            <quota>default</quota>
            <default_database>testdb</default_database>
        </test_admin>
    </users>
    ... ....

使用test_admin用户登录，分别创建只读/可读可写的角色/用户

CREATE ROLE read_only_role;
GRANT SELECT ON *.* TO read_only_role;

CREATE ROLE read_write_role;
GRANT SELECT, INSERT, ALTER UPDATE, ALTER DELETE ON *.* TO read_write_role;

CREATE USER test_reader IDENTIFIED WITH plaintext_password BY '123456' HOST IP '::/0' DEFAULT ROLE read_only_role ;
CREATE USER test_rw IDENTIFIED WITH plaintext_password BY '123456' HOST IP '::/0' DEFAULT ROLE read_write_role ;

总结

具体语法读者朋友可以参考官网的 statements