ClickHouse 基于角色访问控制(RBAC)最佳实践

2023-05-16

本文介绍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

本文内容由网友自发贡献，版权归原作者所有，本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容，请联系:hwhale#tublm.com(使用前将#替换为@)