clickhouse通过RABC即SQL-driven来管理用户权限
ClickHouse也支持基于RBAC（Role-Based Access Control）的访问控制管理，即通过SQL-driven来进行管理。在 RBAC 中，权限与角色相关联，通过成为角色的成员而得到这些角色的权限。简化了权限的管理。这样管理都是层级相互依赖的，权限赋予给角色，而把角色又赋予用户，ClickHouse推荐使用该方式进行用户权限管理。
说明：
ClickHouse（version 20.9.3.45）权限包括：
- 用户账户
- 角色
- 行策略
- 设置描述
- 配额
可以通过如下方式配置权限：
通过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>
建议使用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）：
<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> ...
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 授予权限
④：例子
--创建密码为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 才能看到行
-
--例如
:) 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. 别名: CONSTRAINTALTER 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 对 testdb.ttt表执行数据matution操作，权限包括上面列出来的各个操作：DELETE、UPDATE、ADD/DROP COLUMN/INDEX、MODIFY、TTL等等。
关于 ALTER 更多语法可以看手册。
CREATE
允许执行 CREATE 和 ATTACH 的权限
CREATE. 级别:GROUPCREATE DATABASE. 级别:DATABASECREATE TABLE. 级别:TABLECREATE VIEW. 级别:VIEWCREATE DICTIONARY. 级别:DICTIONARYCREATE TEMPORARY TABLE. 级别:GLOBAL
CREATE 权限包含所有其它 CREATE * 的权限：
:) GRANT CREATE ON *.* TO zjy;
该权限允许用户zjy建库、建表、建视图、字典、临时表等。
DROP
允许执行 DROP 和 DETACH 权限:
DROP. 级别:DROP DATABASE. 级别:DATABASEDROP TABLE. 级别:TABLEDROP VIEW. 级别:VIEWDROP 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 CREATE USER. 级别: GLOBAL ALTER USER. 级别: GLOBAL DROP USER. 级别: GLOBAL CREATE ROLE. 级别: GLOBAL ALTER ROLE. 级别: GLOBAL DROP ROLE. 级别: GLOBAL ROLE ADMIN. 级别: GLOBAL CREATE ROW POLICY. 级别: GLOBAL. 别名: CREATE POLICY ALTER ROW POLICY. 级别: GLOBAL. 别名: ALTER POLICY DROP ROW POLICY. 级别: GLOBAL. 别名: DROP POLICY CREATE QUOTA. 级别: GLOBAL ALTER QUOTA. 级别: GLOBAL DROP QUOTA. 级别: GLOBAL CREATE SETTINGS PROFILE. 级别: GLOBAL. 别名: CREATE PROFILE ALTER SETTINGS PROFILE. 级别: GLOBAL. 别名: ALTER PROFILE DROP SETTINGS PROFILE. 级别: GLOBAL. 别名: DROP PROFILE SHOW ACCESS. 级别: GROUP SHOW_USERS. 级别: GLOBAL. 别名: SHOW CREATE USER SHOW_ROLES. 级别: GLOBAL. 别名: SHOW CREATE ROLE SHOW_ROW_POLICIES. 级别: GLOBAL. 别名: SHOW POLICIES, SHOW CREATE ROW POLICY, SHOW CREATE POLICY SHOW_QUOTAS. 级别: GLOBAL. 别名: SHOW CREATE QUOTA SHOW_SETTINGS_PROFILES. 级别: GLOBAL. 别名: SHOW PROFILES, SHOW CREATE SETTINGS PROFILE, SHOW CREATE PROFILE :) 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. 别名: RELOAD 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'] [,...] --例如: -- 改用名 :) 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'] [,...] --例如: -- 修改角色名 :) 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。到此，正常的用户权限设置已经完成。
简单应用示例
CREATE SETTINGS PROFILE low_mem_readonly SETTINGS max_threads = 4, max_memory_usage = 2000000000 READONLY;--创建profile，设置最大最小线程 最大最小内存使用范围，只读权限 CREATE ROLE accountant SETTINGS PROFILE 'low_mem_readonly';--创建角色 设置它依赖的profile --此处还可以限制配额 --CREATE QUOTA IF NOT EXISTS batch_quota FOR INTERVAL 3600 second MAX queries 60, MAX result_rows 1000000 TO accountant 时间区间内最大查询次数以及最大返回结果数。 GRANT SELECT ON isv_data_prod.dm_order TO accountant;--将数据库查询权限给角色 GRANT SELECT ON isv_data_prod.dwd_merchant_allattr TO accountant;--将数据库查询权限给角色 CREATE USER IF NOT EXISTS isv_read_ward IDENTIFIED WITH PLAINTEXT_PASSWORD BY 'TKMWq9WE' DEFAULT ROLE accountant; --不指定ip默认都可以进行连接 把角色分配给用户 --登录方式 clickhouse-client -u isv_read_ward -h 10.3.97.14 --password TKMWq9WE
总结
从上面看到，通过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>
注意，如果把该目录的文件删除，则会让这些用户角色全部失效。
