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中的查詢可以分為幾種類型:

讀: SELECT , SHOW DESCRIBE EXISTS .
寫: INSERT OPTIMIZE
設定: SET USE
DDL: CREATE ALTER RENAME ATTACH DETACH DROP TRUNCATE
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）：

ClickHouse學習系列之六【通路權限和賬戶管理】

<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 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 授予權限

④：例子

建立密碼為123456的使用者cc，隻能本機登入：

:) create user cc host ip '127.0.0.1' identified with sha256_password by '123456';

建立使用者帳戶cao，為其配置設定角色并将此角色設為預設：
```
:) CREATE USER cao DEFAULT ROLE role1, role2;      
```
建立使用者帳戶 john 并将他未來的所有角色設為預設：
```
:) CREATE USER john DEFAULT ROLE ALL;      
```
當将來某個角色配置設定給 john 時，它将自動變為預設值。
建立使用者帳戶 john 并将他未來的所有角色設為預設，除了 role1 和 role2：
```
:) CREATE USER john DEFAULT ROLE ALL EXCEPT role1, role2;      
```
建立使用者帳戶 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'] [,...]

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 BY MODIFY 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 CONSTRAINT ALTER TTL TABLE ALTER MODIFY TTL MODIFY TTL
    - ALTER ADD CONSTRAINT TABLE ADD CONSTRAINT
    - ALTER DROP CONSTRAINT TABLE DROP CONSTRAINT
  - ALTER MATERIALIZE TTL TABLE MATERIALIZE TTL
  - ALTER SETTINGS TABLE ALTER SETTING ALTER MODIFY SETTING MODIFY SETTING
  - ALTER MOVE PARTITION TABLE ALTER MOVE PART MOVE PARTITION MOVE PART
  - ALTER FETCH PARTITION TABLE FETCH PARTITION
  - ALTER FREEZE PARTITION TABLE FREEZE PARTITION
- ALTER VIEW 級别: GROUP
  - ALTER VIEW REFRESH VIEW ALTER LIVE VIEW REFRESH REFRESH VIEW
  - ALTER VIEW MODIFY QUERY VIEW ALTER TABLE MODIFY QUERY
ALTER 權限包含所有其它 ALTER * 的權限：
```
:) GRANT ALTER  ON testdb.ttt TO zjy;      
```
zjy 對 test db.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

允許根據下面的權限層級來執行
SHOW DESCRIBE USE , 和 EXISTS :
- SHOW GROUP
  - SHOW DATABASES DATABASE . 允許執行 SHOW DATABASES SHOW CREATE DATABASE USE <database> .
  - SHOW TABLES TABLE SHOW TABLES EXISTS <table> CHECK <table>
  - SHOW COLUMNS COLUMN SHOW CREATE TABLE DESCRIBE
  - SHOW DICTIONARIES DICTIONARY SHOW DICTIONARIES SHOW CREATE DICTIONARY EXISTS <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 KILL SHUTDOWN
  - SYSTEM DROP CACHE DROP CACHE
    - SYSTEM DROP DNS CACHE GLOBAL SYSTEM DROP DNS DROP DNS CACHE DROP DNS
    - SYSTEM DROP MARK CACHE GLOBAL SYSTEM DROP MARK DROP MARK CACHE DROP MARKS
    - SYSTEM DROP UNCOMPRESSED CACHE GLOBAL SYSTEM DROP UNCOMPRESSED DROP UNCOMPRESSED CACHE DROP UNCOMPRESSED
  - SYSTEM RELOAD GROUP
    - SYSTEM RELOAD CONFIG GLOBAL RELOAD CONFIG
    - SYSTEM RELOAD DICTIONARY GLOBAL SYSTEM RELOAD DICTIONARIES RELOAD DICTIONARY RELOAD DICTIONARIES
    - SYSTEM RELOAD EMBEDDED DICTIONARIES GLOBAL . 别名: R ELOAD EMBEDDED DICTIONARIES
  - SYSTEM MERGES TABLE SYSTEM STOP MERGES SYSTEM START MERGES STOP MERGES START MERGES
  - SYSTEM TTL MERGES TABLE SYSTEM STOP TTL MERGES SYSTEM START TTL MERGES STOP TTL MERGES START TTL MERGES
  - SYSTEM FETCHES TABLE SYSTEM STOP FETCHES SYSTEM START FETCHES STOP FETCHES START FETCHES
  - SYSTEM MOVES TABLE SYSTEM STOP MOVES SYSTEM START MOVES STOP MOVES START MOVES
  - SYSTEM SENDS GROUP SYSTEM STOP SENDS SYSTEM START SENDS STOP SENDS START SENDS
    - SYSTEM DISTRIBUTED SENDS TABLE SYSTEM STOP DISTRIBUTED SENDS SYSTEM START DISTRIBUTED SENDS STOP DISTRIBUTED SENDS START DISTRIBUTED SENDS
    - SYSTEM REPLICATED SENDS TABLE SYSTEM STOP REPLICATED SENDS SYSTEM START REPLICATED SENDS STOP REPLICATED SENDS START REPLICATED SENDS
  - SYSTEM REPLICATION QUEUES TABLE SYSTEM STOP REPLICATION QUEUES SYSTEM START REPLICATION QUEUES STOP REPLICATION QUEUES START 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：别名: dictHas dictGetHierarchy dictIsIn， 權限級别: DICTIONARY
```
:) grant dictGet  on *.* to zjy;      
```
允許使用者執行 dictGet, dictHas, dictGetHierarchy, dictIsIn 等函數
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;

允許

john

和

robin

賬号對任意資料庫的任意表執行

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} [,...]

:) 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} [,...]

:) 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 [,...]}]

-- 改名
:) 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 [,...]}]

-- 改名
:) 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'] [,...]

-- 改名
:) 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：顯示目前所有的users、roles、profiles、grants資訊。
```
:) SHOW ACCESS;      
```
SHOW SETTINGS：顯示系統設定的清單。從 system.settings 表中選擇資料。
```
:) SHOW [CHANGED] SETTINGS LIKE|ILIKE <name>;      
```
LIKE | ILIKE 允許為設定名稱指定比對模式。可以包含諸如 % 或 _ 之類的全局變量。

LIKE：區分大小寫，

ILIKE：不區分大小寫。

CHANGED：查詢僅傳回從預設值更改的設定。
```
-- 使用 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>

注意，如果把該目錄的檔案删除，則會讓這些使用者角色全部失效。

參考文章：

通路權限和賬戶管理

~~~~~~~~~~~~~~~

萬物之中,希望至美

~~~~~~~~~~~~~~~

ClickHouse學習系列之六【通路權限和賬戶管理】

背景

說明

使用

一配置檔案設定

二 SQL設定

實戰

總結

參考文章：

繼續閱讀

Clickhouse 字元串函數

ClickHouse JDBC Bridge 的安裝及連接配接sqlserver

Clickhouse 專欄---資料模型之留存分析

Kettle連接配接Clickhouse 自定義插件遇到的問題：注意：

CentOS7 Linux 安裝 clickhouse

ClickHouse部署CentOS7

clickhouse 一（centos7安裝clickhouse，設定使用者名密碼）

Clickhouse centos7叢集搭建Clickhouse centos7叢集搭建

centos7 yum 安裝及解除安裝 Clickhouse

clickhouse離線安裝和解除安裝1 clickhouse簡介2 安裝3 Clickhouse 啟動與驗證4 解除安裝

ClickHouse單節點安裝配置（CentOS7系統）關閉防火牆驗證是否支援SSE4.2指令集RPM包下載下傳安裝依賴下載下傳安裝啟動ClickHouse遠端通路配置密碼配置

clickhouse-1-clickhouse在centos7中的安裝和解除安裝1 clickhouse簡介2 離線安裝3 線上安裝

SqlServer替換換行符

Clickhouse常用系統表概覽一、常用系統表

【大資料開發】ClickHouse——深入ClickHouse第一章 clickhouse概述第二章單機版安裝第三章叢集安裝第四章入門操作第五章庫表引擎第六章函數第六章函數

S5700的ACL通路控制清單及實驗

ClickHouse學習系列之六【通路權限和賬戶管理】

背景

說明

使用

一 配置檔案設定

二 SQL設定

實戰

總結

參考文章：

繼續閱讀

一配置檔案設定