ClickHouse学习系列之六【访问权限和账户管理】

背景

      在之前写的文章【用户权限管理】里已经介绍了应该如何设置用户密码以及权限控制。但是只是针对修改配置文件的方式来进行用户权限管理,其实ClickHouse也支持基于RBAC(Role-Based Access Control)的访问控制管理,即通过SQL-driven来进行管理。在 RBAC  中,权限与角色相关联,通过成为角色的成员而得到这些角色的权限。简化了权限的管理。这样管理都是层级相互依赖的,权限赋予给角色,而把角色又赋予用户,ClickHouse推荐使用该方式进行用户权限管理。更多的信息可以看:访问权限和账户管理

说明

ClickHouse(version 21.6.3)权限包括:

用户账户
角色
行策略
设置描述
配额

可以通过如下方式配置权限:

  • 通过SQL-driven的工作流方式,需要手动开启该功能,默认关闭:
    users.xml:
      
    access_management:1 开启

        <users>
            <default>
                <password>123456</password>
                <networks>
                    <ip>::/0</ip>
                </networks>
                <profile>default</profile>
                <quota>default</quota>
                <access_management>1</access_management>
            </default>
        </users>
  • 通过服务端配置文件 users.xml 和 config.xml,像之前的用户权限管理文章说明的一样。

建议使用SQL-driven工作流的方式。当然配置的方式也可以同时起作用, 可以平滑的配置管理方式切换到SQL-driven的工作流方式。

使用

在介绍用户权限管理之前,再次了解下用户权限相关的几个方面:

  • 权限的类型
  • 配置组(Profiles)的设置,在users.xml文件中
  • 限制(constraints)
  • 熔断(quotas)

一 配置文件设置

1. 权限的类型:

ClickHouse中的查询可以分为几种类型:

  1. 读: SELECTSHOWDESCRIBEEXISTS.
  2. 写: INSERTOPTIMIZE.
  3. 设置: SETUSE.
  4. DDLCREATEALTERRENAMEATTACHDETACHDROP TRUNCATE.
  5. KILL:kill 查询

以下设置按查询类型规范用户权限:

  • readonly —  限制除 DDL 之外的所有查询类型的权限

        0:允许所有查询。
        1:仅允许读取数据查询。
        2:允许读取数据和更改设置查询。

    默认值0,设置 readonly=1 后不能执行 readonly 和 allow_ddl 在当前会话中的设置。

  • allow_ddl — 限制 DDL 的权限

        0:不允许 DDL 查询。
        1:允许 DDL 查询。

    默认值1,设置 allow_ddl=0 后不能执行 SET allow_ddl = 1。

  • kill  — 可以使用任何设置执行KILL QUERY

以上的权限通过配置标签来控制(users.xml):

<profiles>   --在profiles里设置
        ...
        <normal> --只读,不能DDL
            <readonly>1</readonly>
            <allow_ddl>0</allow_ddl>
        </normal>

        <normal_1> --读且能set,不能DDL
            <readonly>2</readonly>
            <allow_ddl>0</allow_ddl>
        </normal_1>

        <normal_2> --只读,即使DDL允许
            <readonly>1</readonly>
            <allow_ddl>1</allow_ddl>
        </normal_2>

        <normal_3> --读写,能DDL
            <readonly>0</readonly>
            <allow_ddl>1</allow_ddl>
        </normal_3>

    </profiles>

...
    <users>
        ...
        <test>
            <password>123456</password>
            <networks incl="networks" replace="replace">
                <ip>::/0</ip>
            </networks>
            <profile>normal_3</profile> --用户引用相关profile
            <quota>default</quota>
        </test>
    </users>
...
View Code

2. 配置组(Profiles)的设置:users.xml

profile的作用类似于用户角色,可以在users.xml中定义多组profile,并可以为每组profile定义不同的配置项:读、写、DDL权限,以及限制的设置,各个profile可以相互继承。设置好profile之后,可以在用户中被使用。

<?xml version="1.0"?>
<yandex>
    <profiles>
        <!--自定义profile,可以任意命名-->
        <default>
            <max_memory_usage>100000000</max_memory_usage>
            <use_uncompressed_cache>0</use_uncompressed_cache>
            <load_balancing>random</load_balancing>
        </default>

        <readonly>
            <readonly>1</readonly>
        </readonly>

        <readwrite>
            <constraints>
                <max_memory_usage>
                    <readonly/>
                </max_memory_usage>
                <force_index_by_date>
                    <readonly/>
                </force_index_by_date>
            </constraints>
        </readwrite>

    </profiles>

...
</yandex>

上面新增profile:readonly 和 readwrite,还可以在命令行里切换profile:

:) set profile = 'readonly';

切换了profile之后,就拥有了该profile下的权限。关于配置profile的修改,可以之前的文章【用户权限管理】

3. 限制(constraints): 

users.xml配置文件的profile选项组下constraints选项组里定义设置的约束,并禁止用户使用SET查询更改某些设置。constraints标签可以设置一组约束条件,以限制profile内的参数值被随意修改,约束条件有如下三种规则:

  • min:最小值约束,在设置相应参数的时候,取值不能小于该阈值;

  • max:最大值约束,在设置相应参数的时候,取值不能大于该阈值;

  • readonly:只读约束,该参数值不允许被修改。

...
    <profiles>
        <default>
            <max_memory_usage>10000000000</max_memory_usage>
            <use_uncompressed_cache>0</use_uncompressed_cache>
            <force_index_by_date>0</force_index_by_date>
            <load_balancing>random</load_balancing>
        </default>
        <test>
            <constraints>
                <max_memory_usage>
                    <min>100000</min>
                    <max>200000</max>
                </max_memory_usage>
                <force_index_by_date>
                    <readonly/>
                </force_index_by_date>
            </constraints>
        </test>
    </profiles>
...

上面对profile为test进行了限制,如果在该profile下试图违反约束,则会引发异常并且不会更改设置:

:) SET max_memory_usage=2000000;
-- Code: 452. DB::Exception: Received from localhost:9010. DB::Exception: Setting max_memory_usage shouldn't be greater than 200000.

:) SET max_memory_usage=10000;
-- Code: 452. DB::Exception: Received from localhost:9010. DB::Exception: Setting max_memory_usage shouldn't be less than 100000. 

:) SET force_index_by_date=1;
-- Code: 452. DB::Exception: Received from localhost:9010. DB::Exception: Setting force_index_by_date should not be changed. 

关于限制配置的修改,可以看之前的文章【用户权限管理】

4. 配额(quotas)

配合,限制使用资源,类似于熔断。限制有二种类型:一是在固定周期里的执行次数(quotas),二是限制用户或则查询的使用资源(profiles)。在users.xml配置文件的选项组quotas里设置,限制该用户一段时间内的资源使用,即对一段时间内运行的一组查询施加限制,而不是限制单个查询。模板:

    <!-- Quotas. -->
    <quotas>
        <!-- Name of quota. -->
        <default> --指定quotas名
            <!-- Limits for time interval. You could specify many intervals with different limits. -->
            <interval> --时间间隔
                <!-- Length of interval. -->
                <duration>3600</duration> --周期
                <!-- No limits. Just calculate resource usage for 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>:配额规则名。
  • <interval>:配置时间间隔,每个时间内的资源消耗限制。
  • <duration>:时间周期,单位秒。
  • <queries>:时间周期内允许的请求总数,0表示不限制。
  • <errors>:时间周期内允许的异常总数,0表示不限制。
  • <result_rows>:时间周期内允许返回的行数,0表示不限制。
  • <read_rows>:时间周期内允许在分布式查询中,远端节点读取的数据行数,0表示不限制。
  • <execution_time>:时间周期内允许执行的查询时间,单位是秒,0表示不限制。

关于配额熔断的配置,可以看之前的文章【用户权限管理】

5. 用户设置

users.xml配置文件中的users选项组是配置自定义的用户,定义一个新用户,必须包含以下几项属性:用户名、密码、访问ip、数据库、表等等。它还可以应用上面的profile、constraints、quota。如:

    <users>
        <default>
            <password>123456</password>
            <networks incl="networks" replace="replace">
                <ip>::/0</ip>
            </networks>
            <profile>default</profile>
            <quota>default</quota>
        </default>

        <zhoujy>
            <password_double_sha1_hex>6bb4837eb74329105ee4568dda7dc67ed2ca2ad9</password_double_sha1_hex>
            <networks incl="networks" replace="replace">
                <ip>::/0</ip>
            </networks>
            <profile>default</profile>
            <quota>default</quota>
            <allow_databases>
                <database>test</database>
            </allow_databases>
            <databases>
                <test>  
                    <xx>
                        <filter>id >= 500 </filter>  --行级限制
                    </xx>
                </test>
            </databases>
        </zhoujy>

    </users>
  • <profile>:指定用户的profile
  • <quota>:指定用户的quota,限制用户使用资源
  • <database_name>:指定用户访问的数据库
  • <table_name>:指定用户访问的表
  • <filter>:指定用户访问的过滤器,限制返回符合条件的行。如:id = 1 ,即查询表只返回id=1的行

该示例指定了两个用户:

default:指定了密码、访问IP、profile、quota。
zhoujy :指定了密码、访问IP、profile、quota,以及它只能使用test库,并且只能返回test库xx表id大于等于500的数据。

通过以上的设置,已经把用户权限的知识点大部分都已经介绍完了,包括了读写权限、权限的限制和要把这些“规则”应用到用户上,这样就完成了用户权限的定制了。

以上所有介绍的知识点都可以看之前的文章【用户权限管理】,该文章中都做了详细的说明。通过修改配置文件虽然能实现ACL,但是比较麻烦,不便于维护管理。所以推荐使用SQL方式进行配置。本文的重点是介绍通过SQL-driven来进行管理用户权限。

二 SQL设置

启用SQL-driven管理需要开启users.xml文件中users的参数:

<access_management>1</access_management>

通过SQL-driven设置创建的用户,都存储在access目录中,该目录的位置是由参数 local_directory 控制:

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

1. 创建用户(Create User) 

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 [,...]]
    [GRANTEES {user | role | ANY | NONE} [,...] [EXCEPT {user | role} [,...]]]
    [SETTINGS variable [= value] [MIN [=] min_value] [MAX [=] max_value] [READONLY | WRITABLE] | PROFILE 'profile_name'] [,...]

ON CLUSTER 子句允许在集群上创建用户。

① 用户认证:密码,用户连接密码。

  • IDENTIFIED WITH no_password   -- 没有密码
  • IDENTIFIED WITH plaintext_password BY 'qwerty'   -- 明文密码
  • IDENTIFIED WITH sha256_password BY 'qwerty' or IDENTIFIED BY 'password'  -- sha256_password 加密密码
  • IDENTIFIED WITH sha256_hash BY 'hash'  -- sha256_hash 加密密码
  • IDENTIFIED WITH double_sha1_password BY 'qwerty'  -- double_sha1_password 加密密码
  • IDENTIFIED WITH double_sha1_hash BY 'hash'  -- double_sha1_hash 加密密码
  • IDENTIFIED WITH ldap SERVER 'server_name'
  • IDENTIFIED WITH kerberos or IDENTIFIED WITH kerberos REALM 'realm'

② 用户主机:主机,用户连接地址。

  • HOST IP 'ip_address_or_subnetwork' —  用户通过指定IP连接。
  • HOST ANY — 可以从任何位置连接,默认。
  • HOST LOCAL — 只能在本地连接。
  • HOST NAME 'fqdn' — 用户主机可以指定为域名。
  • HOST NAME REGEXP 'regexp' — 主机使用正则表达式。
  • HOST LIKE 'template' — 使用 LIKE 运算符来过滤用户主机。 如HOST LIKE '%' 等价于 HOST ANY,HOST LIKE '%.mysite.com' 过滤 mysite.com 域中的所有主机。

指定主机的另一种方法是在用户名后使用@:

  • CREATE USER mira@'127.0.0.1' — 等效于 HOST IP 语法
  • CREATE USER mira@'localhost' — 等效于 HOST LOCAL 语法
  • CREATE USER mira@'192.168.%.%' — 等效于 HOST LIKE 语法

③  授权权限

通过GRANTEES来授权用户或则角色,可以获得创建该用户的权限。

  • user — 指定可以授予权限的用户
  • role — 指定可以授予权限的角色
  • ANY — 可以向任何人授予权限
  • NONE — 可以向 none 授予权限

④:例子

  1. 创建密码为123456的用户cc,只能本机登录
    :) create user cc host ip '127.0.0.1' identified with sha256_password by '123456';
  2. 创建用户帐户cao,为其分配角色并将此角色设为默认:
    :) CREATE USER cao DEFAULT ROLE role1, role2;
  3. 创建用户帐户 john 并将他未来的所有角色设为默认:
    :) CREATE USER john DEFAULT ROLE ALL;

    当将来某个角色分配给 john 时,它将自动变为默认值。

  4. 创建用户帐户 john 并将他未来的所有角色设为默认,除了 role1 和 role2:
    :) CREATE USER john DEFAULT ROLE ALL EXCEPT role1, role2;
  5. 创建用户帐户 john 并允许将权限授给具有 jack 帐户的用户:
    :) CREATE USER john GRANTEES jack;

    ----  DB::Exception: user `john`: cannot insert because user `john` already exists in local directory: Couldn't insert user `john`. Successfully inserted: none. 

2. 创建角色(Create Role) 

创建角色,角色是一组权限。分配了角色的用户获得该角色的所有权限。

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

一个用户可以分配多个角色。通过 SET ROLE 分配的角色。权限的范围是所有应用角色权限的组合集合。用户可以拥有适用于用户登录的默认角色,要设置默认角色,使用 SET DEFAULT ROLE ALTER USER 语句。使用 REVOKE 来撤销角色,使用 DROP ROLE 来删除角色。

①:创建角色

:) CREATE ROLE zjy;

②:给角色授权

:) GRANT SELECT ON dbtest.* TO zjy;

③:将角色分配给用户

 :) GRANT zjy TO cc;

④:执行角色拥有的权限

:) SET ROLE zjy;

:) SELECT * FROM testdb.*;

3. 创建行策略(Create 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 ...]}]
  • USING:指定过滤行的条件。 如果该行的条件计算为非零,则用户将看到该行。
  • TO:应用到适用的用户和角色。
    如果没有为表定义行策略,则任何用户都可以从表中选择所有行。为表定义一个或多个行策略,无论是否为当前用户定义了这些行策略,都可以根据行策略访问表。例如:
    :) CREATE ROW POLICY pol1 ON mydb.table1 USING b=1 TO mira, peter;

    禁止用户 mira 和 peter 查看 b != 1 的行,任何未提及的用户(例如,用户 paul)根本看不到 mydb.table1 中的行。

    CREATE ROW POLICY pol2 ON mydb.table1 USING 1 TO ALL EXCEPT mira, peter

    禁止用户 mira 和 peter 查看表数据。

  • AS:允许同时为同一用户在同一张表上启用多个策略。所以需要一种方法来组合来自多个策略的条件。
    :) CREATE ROW POLICY pol1 ON mydb.table1 USING b=1 TO mira, peter;
    
    :) CREATE ROW POLICY pol2 ON mydb.table1 USING c=2 AS RESTRICTIVE TO peter, antonio;

    当 b=1 和 c=2 时,用户 peter 才能看到行 

eg:

:) CREATE ROW POLICY filter1 ON mydb.mytable USING a<1000 TO accountant, john@localhost;

:) CREATE ROW POLICY filter2 ON mydb.mytable USING a<1000 AND b=5 TO ALL EXCEPT mira;

:) CREATE ROW POLICY filter3 ON mydb.mytable USING 1 TO admin;

4. 创建熔断配额策略(CREATE 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 [,...]}]

关键字:user_name、ip_address、client_key、client_key、user_name和client_key、ip_address对应system.quotas表中的字段。

关键字:querys、query_selects、query_inserts、errors、result_rows、result_bytes、read_rows、read_bytes、execution_time对应system.quotas_usage表中的字段。

①:将当前用户的最大查询数限制为 15 个月内123 次:

CREATE QUOTA qA FOR INTERVAL 15 month MAX queries = 123 TO CURRENT_USER;

②:对于默认用户,在30分钟之内限制最大执行时间为半秒,将最大查询数限制为 321,将最大错误数限制为 10 :

CREATE QUOTA qB FOR INTERVAL 30 minute MAX execution_time = 0.5, FOR INTERVAL 5 quarter MAX queries = 321, errors = 10 TO default;

在用户、角色和访问策略建立好之后,后面就需要授权了。

5. 创建配置组策略CREATE SETTINGS PROFILE

创建可分配给用户或角色的设置配置文件

CREATE SETTINGS PROFILE [IF NOT EXISTS | OR REPLACE] TO name1 [ON CLUSTER cluster_name1] 
        [, name2 [ON CLUSTER cluster_name2] ...]
    [SETTINGS variable [= value] [MIN [=] min_value] [MAX [=] max_value] [READONLY|WRITABLE] | INHERIT 'profile_name'] [,...]

eg:

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

使用 max_memory_usage 设置的值和约束创建 max_memory_usage_profile 设置配置文件,并将其分配给用户 robin:

6. 权限:权限是指执行特定操作的许可

权限有层级结构,一组允许的操作依赖相应的权限范围。 

级别(由低到高):

  • COLUMN - 可以授权到列,表,库或者全局
  • TABLE - 可以授权到表,库,或全局
  • VIEW - 可以授权到视图,库,或全局
  • DICTIONARY - 可以授权到字典,库,或全局
  • DATABASE - 可以授权到数据库或全局
  • GLABLE - 可以授权到全局
  • GROUP - 不同级别的权限分组。当授予 GROUP级别的权限时, 根据所用的语法,只有对应分组中的权限才会被分配。

权限的层级:

  • SELECT

    允许执行 SELECT 查询,权限级别: COLUMN。

    :) GRANT SELECT(x,y) ON db.table TO john;

    该权限允许 john 对 db.table表的列x,y执行SELECT查询。

    :) GRANT SELECT ON db.table TO john;

    该权限允许 john 对 db.table表的所有列执行SELECT查询。

  • INSERT

    允许执行 INSERT 操作,权限级别: COLUMN。

    :) GRANT INSERT(x,y) ON db.table TO john;

    该权限允许 john 对 db.table表的列x,y执行数据插入操作

    :) GRANT INSERT ON db.table TO john;

    该权限允许 john 对 db.table表的所有列执行数据插入操作

  • ALTER
    允许执行ALTER操作
    • ALTER TABLE. 级别: GROUP
      • ALTER UPDATE. 级别: COLUMN. 别名: UPDATE
        :) alter table ttt update address = 'TTTT' where id = 1;
      • ALTER DELETE. 级别: COLUMN. 别名: DELETE
        :) alter table ttt delete where id = 6;
      • ALTER COLUMN. 级别: GROUP
        • ALTER ADD COLUMN. 级别: COLUMN. 别名: ADD COLUMN
          :) alter table ttt add column col1 String;
        • ALTER DROP COLUMN. 级别: COLUMN. 别名: DROP COLUMN
          :) alter table ttt drop column col1;
        • ALTER MODIFY COLUMN. 级别: COLUMN. 别名: MODIFY COLUMN
          :) alter table ttt modify column col1 UInt16;
        • ALTER COMMENT COLUMN. 级别: COLUMN. 别名: COMMENT COLUMN
          :) alter table ttt comment column col1 'xxxx';
        • ALTER CLEAR COLUMN. 级别: COLUMN. 别名: CLEAR COLUMN
          :) alter table ttt clear column col1;
        • ALTER RENAME COLUMN. 级别: COLUMN. 别名: RENAME COLUMN
          :) alter table ttt rename column col1 to col2;
      • ALTER INDEX. 级别: GROUP. 别名: INDEX
        • ALTER ORDER BY. 级别: TABLE. 别名: ALTER MODIFY ORDER BYMODIFY ORDER BY
          :) alter table ttt modify order by name;
        • ALTER ADD INDEX. 级别: TABLE. 别名: ADD INDEX
          :) alter table ttt add index idx_name(name) type minmax granularity 5;
        • ALTER DROP INDEX. 级别: TABLE. 别名: DROP INDEX
          :) alter table ttt drop index idx_name;
        • ALTER MATERIALIZE INDEX. 级别: TABLE. 别名: MATERIALIZE INDEX
        • ALTER CLEAR INDEX. 级别: TABLE. 别名: CLEAR INDEX
      • ALTER CONSTRAINT. 级别: GROUP. 别名: CONSTRAINTALTER TTL. 级别: TABLE. 别名: ALTER MODIFY TTLMODIFY TTL
        • ALTER ADD CONSTRAINT. 级别: TABLE. 别名: ADD CONSTRAINT
        • ALTER DROP CONSTRAINT. 级别: TABLE. 别名: DROP CONSTRAINT
      • ALTER MATERIALIZE TTL. 级别: TABLE. 别名: MATERIALIZE TTL
      • ALTER SETTINGS. 级别: TABLE. 别名: ALTER SETTINGALTER MODIFY SETTINGMODIFY SETTING
      • ALTER MOVE PARTITION. 级别: TABLE. 别名: ALTER MOVE PARTMOVE PARTITIONMOVE PART
      • ALTER FETCH PARTITION. 级别: TABLE. 别名: FETCH PARTITION
      • ALTER FREEZE PARTITION. 级别: TABLE. 别名: FREEZE PARTITION
    • ALTER VIEW 级别: GROUP
      • ALTER VIEW REFRESH. 级别: VIEW. 别名: ALTER LIVE VIEW REFRESHREFRESH VIEW
      • ALTER VIEW MODIFY QUERY. 级别: VIEW. 别名: ALTER TABLE MODIFY QUERY
    ALTER 权限包含所有其它 ALTER * 的权限:
    :) GRANT ALTER  ON testdb.ttt TO zjy;

    该权限允许 zjy 对 testdb.ttt表执行数据matution操作,权限包括上面列出来的各个操作:DELETE、UPDATE、ADD/DROP COLUMN/INDEX、MODIFY、TTL等等。
    关于 ALTER 更多语法可以看手册

  • CREATE
    允许执行 CREATE 和 ATTACH 的权限
    • CREATE. 级别: GROUP
      • CREATE DATABASE. 级别: DATABASE
      • CREATE TABLE. 级别: TABLE
      • CREATE VIEW. 级别: VIEW
      • CREATE DICTIONARY. 级别: DICTIONARY
      • CREATE TEMPORARY TABLE. 级别: GLOBAL
    CREATE 权限包含所有其它 CREATE * 的权限:
    :) GRANT CREATE ON *.* TO zjy;

    该权限允许用户zjy建库、建表、建视图、字典、临时表等。

  • DROP

    允许执行 DROP 和 DETACH 权限:

    • DROP. 级别:
      • DROP DATABASE. 级别: DATABASE
      • DROP TABLE. 级别: TABLE
      • DROP VIEW. 级别: VIEW
      • DROP DICTIONARY. 级别: DICTIONARY
    :) GRANT DROP ON *.* TO zjy;

    该权限允许用户zjy删库、删表、删视图、删字典等。

  • TRUNCATE

    允许执行 TRUNCATE 权限,权限级别: TABLE.

    :) GRANT TRUNCATE ON *.* TO zjy;

    该权限允许用户zjy清空表。

  • OPTIMIZE

    允许执行 OPTIMIZE TABLE 权限,权限级别: TABLE.

    :) GRANT OPTIMIZE ON *.* TO zjy;

    该权限允许用户zjy optimize 表。

  • SHOW

    允许根据下面的权限层级来执行 SHOWDESCRIBEUSE, 和 EXISTS :

    • SHOW. 级别: GROUP
      • SHOW DATABASES. 级别: DATABASE. 允许执行 SHOW DATABASESSHOW CREATE DATABASEUSE <database> .
      • SHOW TABLES. 级别: TABLE. 允许执行 SHOW TABLESEXISTS <table>CHECK <table> .
      • SHOW COLUMNS. 级别: COLUMN. 允许执行 SHOW CREATE TABLEDESCRIBE .
      • SHOW DICTIONARIES. 级别: DICTIONARY. 允许执行 SHOW DICTIONARIESSHOW CREATE DICTIONARYEXISTS <dictionary> .
    :) GRANT SHOW ON *.* TO zjy;

    该权限允许用户zjy 执行show相关权限,show users 权限需要单独定义。注意:当用户对指定表,字典或数据库有其它的权限时,同时会授予SHOW权限。

  • KILL QUERY

    允许根据下面的权限层级来执行 KILL,权限级别: GLOBAL。

    :) GRANT KILL QUERY ON *.* TO zjy;

    该权限允许用户zjy 执行 kill query 相关权限。

  • ACCESS MANAGEMENT
    允许执行管理用户/角色和行规则的操作
    • ACCESS MANAGEMENT. 级别: GROUP
    :) grant ACCESS MANAGEMENT on *.* to zjy;

    该权限允许用户zjy管理用户权限,包括:创建/删除/修改 用户、角色、行规则、熔断规则、SETTING、SHOW 用户相关等等。

  • SYSTEM
    允许根据下面的权限层级来执行 SYSTEM,改权限包含服务的关闭、刷写、重载等。
    • SYSTEM. 级别: GROUP
      • SYSTEM SHUTDOWN. 级别: GLOBAL. 别名: SYSTEM KILLSHUTDOWN
      • SYSTEM DROP CACHE. 别名: DROP CACHE
        • SYSTEM DROP DNS CACHE. 级别: GLOBAL. 别名: SYSTEM DROP DNSDROP DNS CACHEDROP DNS
        • SYSTEM DROP MARK CACHE. 级别: GLOBAL. 别名: SYSTEM DROP MARKDROP MARK CACHEDROP MARKS
        • SYSTEM DROP UNCOMPRESSED CACHE. 级别: GLOBAL. 别名: SYSTEM DROP UNCOMPRESSEDDROP UNCOMPRESSED CACHEDROP UNCOMPRESSED
      • SYSTEM RELOAD. 级别: GROUP
        • SYSTEM RELOAD CONFIG. 级别: GLOBAL. 别名: RELOAD CONFIG
        • SYSTEM RELOAD DICTIONARY. 级别: GLOBAL. 别名: SYSTEM RELOAD DICTIONARIESRELOAD DICTIONARYRELOAD DICTIONARIES
        • SYSTEM RELOAD EMBEDDED DICTIONARIES. 级别: GLOBAL. 别名: RELOAD EMBEDDED DICTIONARIES
      • SYSTEM MERGES. 级别: TABLE. 别名: SYSTEM STOP MERGESSYSTEM START MERGESSTOP MERGESSTART MERGES
      • SYSTEM TTL MERGES. 级别: TABLE. 别名: SYSTEM STOP TTL MERGESSYSTEM START TTL MERGESSTOP TTL MERGESSTART TTL MERGES
      • SYSTEM FETCHES. 级别: TABLE. 别名: SYSTEM STOP FETCHESSYSTEM START FETCHESSTOP FETCHESSTART FETCHES
      • SYSTEM MOVES. 级别: TABLE. 别名: SYSTEM STOP MOVESSYSTEM START MOVESSTOP MOVESSTART MOVES
      • SYSTEM SENDS. 级别: GROUP. 别名: SYSTEM STOP SENDSSYSTEM START SENDSSTOP SENDSSTART SENDS
        • SYSTEM DISTRIBUTED SENDS. 级别: TABLE. 别名: SYSTEM STOP DISTRIBUTED SENDSSYSTEM START DISTRIBUTED SENDSSTOP DISTRIBUTED SENDSSTART DISTRIBUTED SENDS
        • SYSTEM REPLICATED SENDS. 级别: TABLE. 别名: SYSTEM STOP REPLICATED SENDSSYSTEM START REPLICATED SENDSSTOP REPLICATED SENDSSTART REPLICATED SENDS
      • SYSTEM REPLICATION QUEUES. 级别: TABLE. 别名: SYSTEM STOP REPLICATION QUEUESSYSTEM START REPLICATION QUEUESSTOP REPLICATION QUEUESSTART REPLICATION QUEUES
      • SYSTEM SYNC REPLICA. 级别: TABLE. 别名: SYNC REPLICA
      • SYSTEM RESTART REPLICA. 级别: TABLE. 别名: RESTART REPLICA
      • SYSTEM FLUSH. 级别: GROUP
        • SYSTEM FLUSH DISTRIBUTED. 级别: TABLE. 别名: FLUSH DISTRIBUTED
        • SYSTEM FLUSH LOGS. 级别: GLOBAL. 别名: FLUSH LOGS
    :) grant SYSTEM  on *.* to zjy;

    该权限允许用户zjy执行SYSTEM相关操作。

  • INTROSPECTION
  • SOURCES

    允许在 table engines 和 table functions中使用外部数据源。

    • SOURCES. 级别: GROUP
      • FILE. 级别: GLOBAL
      • URL. 级别: GLOBAL
      • REMOTE. 级别: GLOBAL
      • YSQL. 级别: GLOBAL
      • ODBC. 级别: GLOBAL
      • JDBC. 级别: GLOBAL
      • HDFS. 级别: GLOBAL
      • S3. 级别: GLOBAL
    :) grant sources  on *.* to zjy; 

    该权限允许用户zjy执行sources相关操作。

  • dictGet:别名: dictHasdictGetHierarchydictIsIn,权限级别: DICTIONARY
    :) grant dictGet  on *.* to zjy;
    允许用户执行 dictGetdictHasdictGetHierarchydictIsIn 等函数
  • ALL

    给用户或角色授予所有权限

    :) grant ALL  on *.* to zjy;
  • NONE
    不授予任何权限,类似于MySQL的USAGE。

    :) grant NONE  on *.* to zjy;
  • ADMIN OPTION

    :) grant xxx to zjy with admin option;

    允许用户将他们的角色分配给其它用户:把角色xxx分配给zjy,之后zjy用户也可以分配xxx角色。

  • GRANT OPTION

    :) grant all on *.* to zjy with grant option;

    授予 zjy 可以执行 GRANT 操作的权限,可将自身的权限对其他对象进行授权。

7. 授权(Grants privileges)

  • 给ClickHouse的用户或角色赋予权限
  • 将角色分配给用户或其他角色

取消权限,使用 REVOKE 语句,查看已授的权限使用 SHOW GRANTS 。

GRANT [ON CLUSTER cluster_name] privilege[(column_name [,...])] [,...] ON {db.table|db.*|*.*|table|*} TO {user | role | CURRENT_USER} [,...] [WITH GRANT OPTION]
  • privilege — 权限类型
  • role — 角色
  • user — 用户

WITH GRANT OPTION 授予 user 或 role执行 GRANT 操作的权限,之后该用户可将自身的权限对其他对象进行授权。

GRANT [ON CLUSTER cluster_name] role [,...] TO {user | another_role | CURRENT_USER} [,...] [WITH ADMIN OPTION]
  • role — 角色
  • user — 用户

WITH ADMIN OPTION 子句向用户或角色授予 ADMIN OPTION 特权。

使用 GRANT 账号必须有 GRANT OPTION的权限。用户只能将在自身权限范围内的权限进行授权。如:管理员有权通过下面的语句给 john账号添加授权

GRANT SELECT(x,y) ON db.table TO john WITH GRANT OPTION

john 有权执行 GRANT OPTION,他能给其它账号进行和自己账号权限范围相同的授权。可以使用* 号代替表或库名进行授权操作。同样,可以忽略库名,权限将指向当前的数据库。

可以一次给多个账号进行多种授权操作:

 GRANT SELECT,INSERT ON *.* TO john,robin;

允许 johnrobin 账号对任意数据库的任意表执行 INSERT和 SELECT操作。访问 systen数据库总是被允许的。

8. 撤权(revoke privileges)

  • 取消用户的权限
    REVOKE [ON CLUSTER cluster_name] privilege[(column_name [,...])] [,...] ON {db.table|db.*|*.*|table|*} FROM {user | CURRENT_USER} [,...] | ALL | ALL EXCEPT {user | CURRENT_USER} [,...]

    eg:

    :) revoke alter on *.* from zjy;

    说明:移除用户zjy 的alter权限。

    :) GRANT SELECT ON accounts.staff TO mira;
    :) REVOKE SELECT(wage) ON accounts.staff FROM mira;

    说明:授权 mira账号能查询 accounts.staff表的所有列,除了 wage这一列。

  • 取消用户的角色
    REVOKE [ON CLUSTER cluster_name] [ADMIN OPTION FOR] role [,...] FROM {user | role | CURRENT_USER} [,...] | ALL | ALL EXCEPT {user_name | role_name | CURRENT_USER} [,...]

    eg:

    :) revoke xxx from zjy;

    移除用户zjy上角色xxx的权限。

9. 修改权限(ALTER

  • ALTER USER:修改用户
    ALTER USER [IF EXISTS] name1 [ON CLUSTER cluster_name1] [RENAME TO new_name1] 
            [, name2 [ON CLUSTER cluster_name2] [RENAME TO new_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']}]
        [[ADD | DROP] HOST {LOCAL | NAME 'name' | REGEXP 'name_regexp' | IP 'address' | LIKE 'pattern'} [,...] | ANY | NONE]
        [DEFAULT ROLE role [,...] | ALL | ALL EXCEPT role [,...] ]
        [GRANTEES {user | role | ANY | NONE} [,...] [EXCEPT {user | role} [,...]]]
        [SETTINGS variable [= value] [MIN [=] min_value] [MAX [=] max_value] [READONLY | WRITABLE] | PROFILE 'profile_name'] [,...]

    eg: 

    -- 改用名
    :) alter user zjy rename  to zjj;
    
    -- 改密码
    :) alter user zjj IDENTIFIED with PLAINTEXT_PASSWORD by '123123';
    
    -- 新增白名单
    :) alter user zjj add host ip '10.2.2.2';
    
    -- 设置角色:
    :) alter user zjj default role all;
    
    -- 修改角色限制/profile
    :) alter user zjj SETTINGS PROFILE 'test';
  • ALTER ROLE:修改角色
    ALTER ROLE [IF EXISTS] name [ON CLUSTER cluster_name]
        [RENAME TO new_name]
        [SETTINGS variable [= value] [MIN [=] min_value] [MAX [=] max_value] [READONLY|WRITABLE] | PROFILE 'profile_name'] [,...]

    eg:

    -- 修改角色名
    :) alter role xyz rename to yyy;
    
    -- 修改角色限制/profile
    :) alter role yyy SETTINGS PROFILE 'test';
  • ALTER ROW POLICY:修改行策略
    ALTER [ROW] POLICY [IF EXISTS] name [ON CLUSTER cluster_name] ON [database.]table
        [RENAME TO new_name]
        [AS {PERMISSIVE | RESTRICTIVE}]
        [FOR SELECT]
        [USING {condition | NONE}][,...]
        [TO {role [,...] | ALL | ALL EXCEPT role [,...]}]
    eg:
    -- 改名
    :) alter row policy pol1 on testdb.ttt rename to pol2;
    
    -- 修改
    :) alter row policy pol1 on testdb.ttt using id = 2;
  • ALTER QUOTA:修改配额熔断规则
    ALTER QUOTA [IF EXISTS] name [ON CLUSTER cluster_name]
        [RENAME TO new_name]
        [KEYED BY {'none' | 'user name' | 'ip address' | 'client key' | 'client key or user name' | 'client key or ip address'}]
        [FOR [RANDOMIZED] INTERVAL number {SECOND | MINUTE | HOUR | DAY | WEEK | MONTH | QUARTER | YEAR}
            {MAX { {QUERIES | ERRORS | RESULT ROWS | RESULT BYTES | READ ROWS | READ BYTES | EXECUTION TIME} = number } [,...] |
            NO LIMITS | TRACKING ONLY} [,...]]
        [TO {role [,...] | ALL | ALL EXCEPT role [,...]}]

    eg:

    -- 改名
    :) alter QUOTA qB rename to qq;
    
    -- 修改
    :) alter QUOTA qq FOR INTERVAL 30 minute MAX execution_time = 1, FOR INTERVAL 3 quarter MAX queries = 4321, errors = 100 TO zjj;
  • ALTER SETTINGS PROFILE:修改settings配置
    ALTER SETTINGS PROFILE [IF EXISTS] name [ON CLUSTER cluster_name]
        [RENAME TO new_name]
        [SETTINGS variable [= value] [MIN [=] min_value] [MAX [=] max_value] [READONLY|WRITABLE] | INHERIT 'profile_name'] [,...]

    eg:

    -- 改名
    :) ALTER SETTINGS PROFILE max_memory_usage_profile123 rename to max_memory_usage_profile;
    
    -- 修改
     :) ALTER SETTINGS PROFILE max_memory_usage_profile SETTINGS max_memory_usage = 100000002 MIN 90000001 MAX 110000001;

10. 删除(drop)

  •  DROP USER删除用户
    :) DROP USER [IF EXISTS] name [,...] [ON CLUSTER cluster_name];
  • DROP ROLE:删除角色,删除的角色将从分配给它的所有实体中撤销。

    :) DROP ROLE [IF EXISTS] name [,...] [ON CLUSTER cluster_name];
  • DROP ROW POLICY:删除行策略,删除的行策略从分配给它的所有实体中撤销。

    :) DROP [ROW] POLICY [IF EXISTS] name [,...] ON [database.]table [,...] [ON CLUSTER cluster_name];
  • DROP QUOTA:删除配额熔断。 删除的配额熔断将从分配到它的所有实体中撤销。

    :) DROP QUOTA [IF EXISTS] name [,...] [ON CLUSTER cluster_name];
  • DROP SETTINGS PROFILE:删除profile文件。 已删除的文件将从分配给它的所有实体中撤销。

    :) DROP [SETTINGS] PROFILE [IF EXISTS] name [,...] [ON CLUSTER cluster_name];

11. 查看权限(show)

  • SHOW GRANTS:显示用户的权限
    :) SHOW GRANTS [FOR user]
  • SHOW CREATE USER:显示创建用户时用到的参数
    :) SHOW CREATE USER [name | CURRENT_USER]
  • SHOW CREATE ROLE:显示创建角色时用到的参数
    :) SHOW CREATE ROLE name;
  • SHOW CREATE ROW POLICY:显示创建行策略时用到的参数
    :) SHOW CREATE [ROW] POLICY name ON [database.]table;
  • SHOW CREATE QUOTA:显示创建时配额熔断时用到的参数

    :) SHOW CREATE QUOTA [name | CURRENT];
  • SHOW CREATE SETTINGS PROFILE:显示创建settings时用到的参数

    :) SHOW CREATE [SETTINGS] PROFILE name;
  • SHOW USERS:返回用户列表。查看用户参数,请参阅系统表system.users
    :) SHOW USERS;
  • SHOW ROLES:返回角色列表。查看角色参数,请参阅系统表system.roles 和 system.role-grants

    :) SHOW [CURRENT|ENABLED] ROLES;
  • SHOW PROFILES:返回配置文件列表。要查看配置参数,请参阅系统表settings_profiles

    :) SHOW [SETTINGS] PROFILES;
  • SHOW POLICIES:返回指定表的行策略列表。 要查看用户帐户参数,请参阅系统表system.row_policies

    :) SHOW [ROW] POLICIES [ON [db.]table];
  • SHOW QUOTAS:返回配额列表。 要查看配额参数,请参阅系统表system.quotas

    :) SHOW QUOTAS;
  • SHOW QUOTA:返回所有用户或当前用户的配额。 要查看其他参数,请参阅系统表system.quotas_usage 和 system.quota_usage

    :) SHOW [CURRENT] QUOTA;
  • SHOW ACCESS:显示目前所有的usersrolesprofilesgrants信息。

    :) SHOW ACCESS;
  • SHOW SETTINGS:显示系统设置的列表。 从 system.settings 表中选择数据。

    :) SHOW [CHANGED] SETTINGS LIKE|ILIKE <name>;

    LIKE | ILIKE 允许为设置名称指定匹配模式。 可以包含诸如 % 或 _ 之类的全局变量。
         LIKE:区分大小写,
         ILIKE:不区分大小写。
         CHANGED:查询仅返回从默认值更改的设置。

    eg:

    -- 使用 LIKE 子句查询:
    :) SHOW SETTINGS LIKE 'send_timeout';
    
    -- 使用 ILIKE 子句查询:
    :) SHOW SETTINGS ILIKE '%CONNECT_timeout%';
    
    -- 使用 CHANGED 子句查询:
    :) SHOW CHANGED SETTINGS ILIKE '%MEMORY%'
  • SHOW CLUSTER(s):返回集群列表。 所有可用的集群都列在 system.clusters  表中

    -- 指定查看一个集群
    :) SHOW CLUSTER '<name>';
    
    -- 模糊匹配集群
    :) SHOW CLUSTERS [LIKE|NOT LIKE '<pattern>'] [LIMIT <N>];
    
    -- 查看所有集群
    :) SHOW CLUSTERS;

到此,访问权限和账户管理的介绍已经结束,包括了配置文件和SQL方式的设置,从用户创建/修改/删除、角色创建/修改/删除、权限创建/撤等等,更多的信息信息可以见官网说明

实战

前提条件:因为是通过SQL-driven来进行账号操作的,在ClickHouse安装好之后,会有个默认账户default,在该用户下面开 access_management 参数即可。开启参数:

    <users>

        <default>
            <password>123456</password>
            <networks>
                <ip>::/0</ip>
            </networks>
            <profile>default</profile>
            <quota>default</quota>
            <access_management>1</access_management>
        </default>

    </users>

之后通过default用户进行SQL-driven的账号管理操作。

1. 创建账号

  • 管理账号
    -- 创建账号
    :) create user dba host ip '127.0.0.1' identified with sha256_password by '123456';
    
    -- 授权,all
    :) grant all on *.* to dba with grant option;
  • 普通业务读写账号
    -- 创建账号
    :) create user app host ip '127.0.0.1' identified with sha256_password by '123456';
    
    -- 授权,增删改查权限
    :) grant select,insert,alter delete,alter update on testdb.* to app; 
  • 只读账号
    -- 创建账号
    :) create user ro host ip '127.0.0.1' identified with sha256_password by '123456';
    
    -- 授权,增删改查权限
    :) grant select on testdb.* to ro;

更多的语法见上面的CREATE USER。

2. 创建Role

  • 管理Role
    -- 创建Role
    :) CREATE ROLE DBA;
    
    -- 授权管理
    :) GRANT ALL ON *.* TO DBA with grant option;
    
    -- 给用户授权角色
    :) GRANT DBA TO dba1;
  • 读写Role
    -- 创建Role
    :) CREATE ROLE WRITABLE;
    
    -- 授权增删改查
    :) GRANT select,insert,alter delete,alter update ON *.* TO WRITABLE;
    
    -- 给用户授权角色
    :) GRANT WRITABLE TO rw;
  • 只读Role
    -- 创建Role
    :) CREATE ROLE READONLY;
    
    -- 授权增删改查
    :) GRANT select ON *.* TO READONLY;
    
    -- 给用户授权角色
    :) GRANT READONLY TO ro;

更多的语法见上面的CREATE ROLE。

3. 创建行策略

用于确定用户可以从表中读取哪些行的过滤器,对应配置问了里的filter参数。注意:行策略仅对具有只读访问权限的用户有意义。

-- 创建行策略
:) CREATE ROW POLICY pol1 ON testdb.ttt USING b=1 TO app1;

-- 创建用户,该用户需要有select权限,才能应用
:) create user app1 host ip '127.0.0.1' identified with sha256_password by '123456'; 

-- 行策略应用,该策略只能访问x2表id大于5的数据
:) CREATE ROW POLICY pol1 ON testdb.x2 USING id>5 TO app1;

更多的语法见上面的 CREATE ROW POLICY。

4. 创建QUOTA
分配给用户或角色的配额熔断策略,限制用户的使用资源。
 

-- 创建quota
:) CREATE QUOTA qA FOR INTERVAL 5 minute MAX queries = 10 TO app1;

限制用户app1,5分钟之内最多执行次数,超过则报错:

Code: 201. DB::Exception: Received from localhost:9010. DB::Exception: Quota for user `app1` for 300s has been exceeded: queries = 11/10. Interval will end at 2021-07-01 00:20:00. Name of quota template: `qA`.

更多的语法见上面的 CREATE QUOTA。

5. 创建Profile

创建可分配给用户或角色的的配置文件。

-- 限制用户app的最大使用内存。
:) CREATE SETTINGS PROFILE max_memory_usage_profile SETTINGS max_memory_usage = 100000001 MIN 90000000 MAX 110000000 TO app;

更多的语法见上面的 CREATE SETTINGS PROFILE。到此,正常的用户权限设置已经完成。

 

总结

从上面看到,通过SQL-driven来设置用户权限和管理比修改配置文件要方便很多,官方也推荐使用该方式进行用户权限管理。通过该方式配置的用户都是以文件形式存储在access目录中,该目录的位置是由参数 local_directory 控制:

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

注意,如果把该目录的文件删除,则会让这些用户角色全部失效。

 

参考文章:

访问权限和账户管理

 

posted @ 2021-07-01 10:26  jyzhou  阅读(3292)  评论(0编辑  收藏  举报