clickhouse-用户和角色

​

一、用户相关

1.赋予default用户权限

默认default用户是没有办法创建用户的，这时候需要我们修改配置文件users.xml，便能创建用户了,详见官网

<networks>
        <ip>::/0</ip>
    </networks>

    <!-- Settings profile for user. -->
    <profile>default</profile>

    <!-- Quota for user. -->
    <quota>default</quota>

    <!-- User can create other users and grant rights to them. -->
    <access_management>1</access_management> # 该部分控制权限，把原本注释去掉就能建用户了
    # 下面这几行也要加，不然授权all的时候会提示权限不足
    <named_collection_control>1</named_collection_control>
    <show_named_collections>1</show_named_collections>
    <show_named_collections_secrets>1</show_named_collections_secrets>
</default>
# 修改后需要重启clickhouse服务

2.创建管理用户

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']} | {WITH ssl_certificate CN 'common_name'}]
    [HOST {LOCAL | NAME 'name' | REGEXP 'name_regexp' | IP 'address' | LIKE 'pattern'} [,...] | ANY | NONE]
    [DEFAULT ROLE role [,...]]
    [DEFAULT DATABASE database | NONE]
    [GRANTEES {user | role | ANY | NONE} [,...] [EXCEPT {user | role} [,...]]]
    [SETTINGS variable [= value] [MIN [=] min_value] [MAX [=] max_value] [READONLY | WRITABLE] | PROFILE 'profile_name'] [,...]

2.1 密码验证方式

IDENTIFIED WITH no_password # 无密码
IDENTIFIED WITH plaintext_password BY 'qwerty'
IDENTIFIED WITH sha256_password BY 'qwerty'或IDENTIFIED BY 'password'
IDENTIFIED WITH sha256_hash BY 'hash'或IDENTIFIED WITH sha256_hash BY 'hash' SALT 'salt'
IDENTIFIED WITH double_sha1_password BY 'qwerty'
IDENTIFIED WITH double_sha1_hash BY 'hash'
IDENTIFIED WITH bcrypt_password BY 'qwerty'
IDENTIFIED WITH bcrypt_hash BY 'hash'
IDENTIFIED WITH ldap SERVER 'server_name'
IDENTIFIED WITH kerberos或IDENTIFIED WITH kerberos REALM 'realm'
IDENTIFIED WITH ssl_certificate CN 'mysite.com:user'
IDENTIFIED BY 'qwerty'

2.2 指定用户主机

HOST IP 'ip_address_or_subnetwork' # 用户只能从指定的 IP 地址或子网连接到 ClickHouse 服务器。例子：。对于在生产中使用，请仅指定元素（IP 地址及其掩码），因为使用 和可能会导致额外的延迟。HOST IP '192.168.0.0/16'HOST IP '2001:DB8::/32'HOST IPhosthost_regexp
HOST ANY # 用户可以从任何位置进行连接。这是默认选项。
HOST LOCAL # 用户只能在本地连接。
HOST NAME 'fqdn' # 可以将用户主机指定为 FQDN。例如。HOST NAME 'mysite.com'
HOST REGEXP 'regexp' # 您可以在指定用户主机时使用 pcre 正则表达式。例如。HOST REGEXP '.*\.mysite\.com'
HOST LIKE 'template' # 允许您使用 LIKE 运算符过滤用户主机。例如， 等效于 ，筛选域中的所有主机。HOST LIKE '%'HOST ANYHOST LIKE '%.mysite.com'mysite.com

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

2.3 指定允许从此用户接收权限的用户或角色

先决条件： 此用户还具有grant option的所有必需访问权限

GRANTEES详情

• user— 指定此用户可以向其授予权限的用户。
• role— 指定此用户可以授予权限的角色。
• ANY— 此用户可以向任何人授予权限。这是默认设置。
• NONE— 此用户可以不向任何人授予权限。

CREATE USER john DEFAULT ROLE role1, role2;
CREATE USER john DEFAULT ROLE ALL;
# 除了role1,role2的所有角色可以继承jack的权限
CREATE USER john DEFAULT ROLE ALL EXCEPT role1, role2;
# jack可以继承john的权限
CREATE USER john GRANTEES jack;

create user root identified by 'root';
grant all on *.* to root with grant option;

3.更改用户

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']} | {WITH ssl_certificate CN 'common_name'}]
    [[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 user DEFAULT ROLE ALL EXCEPT role1, role2

4.删除用户

DROP USER [IF EXISTS] name [,...] [ON CLUSTER cluster_name]

5.查看创建用户语句

SHOW CREATE USER [name1 [, name2 ...] | CURRENT_USER]

show create user admin
┌─CREATE USER admin─────────────────────────────────┐
│ CREATE USER admin IDENTIFIED WITH sha256_password │
└───────────────────────────────────────────────────┘

6.查看用户

show users

二、角色相关

1.创建角色

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

# 例子
# 创建角色并赋予角色权限
CREATE ROLE accountant;
GRANT SELECT ON db.* TO accountant;
# 将角色授权给用户
GRANT accountant TO mira;
# 激活当前用户角色，这样用户便拥有权限了
SET ROLE accountant;
SELECT * FROM db.*;

2.修改角色

ALTER ROLE [IF EXISTS] name1 [ON CLUSTER cluster_name1] [RENAME TO new_name1]
        [, name2 [ON CLUSTER cluster_name2] [RENAME TO new_name2] ...]
    [SETTINGS variable [= value] [MIN [=] min_value] [MAX [=] max_value] [CONST|READONLY|WRITABLE|CHANGEABLE_IN_READONLY] | PROFILE 'profile_name'] [,...]

3.删除角色

DROP ROLE [IF EXISTS] name [,...] [ON CLUSTER cluster_name]

4.激活角色

SET ROLE {DEFAULT | NONE | role [,...] | ALL | ALL EXCEPT role [,...]}

5.为用户设置默认角色

# 默认角色在用户登录时自动激活。
SET DEFAULT ROLE {NONE | role [,...] | ALL | ALL EXCEPT role [,...]} TO {user|CURRENT_USER} [,...]

# 例子
SET DEFAULT ROLE role1, role2, ... TO user

6.查看创建角色语句

SHOW CREATE ROLE name1 [, name2 ...]

7.查看所有角色

SHOW [CURRENT|ENABLED] ROLES

三、行策略相关

行策略是一个筛选器，用于定义哪些行可供用户或角色使用

1.创建行策略

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

# 例子
# 禁止用户mira, peter查看mydb.table1除b=1外的其他看，禁止其他用户查看mydb.table1的任何行
CREATE ROW POLICY pol1 ON mydb.table1 USING b=1 TO mira, peter

# 允许同一张表启用多个策略，默认情况下使用or运算符组合不同策略
CREATE ROW POLICY pol1 ON mydb.table1 USING b=1 TO mira, peter
CREATE ROW POLICY pol2 ON mydb.table1 USING c=2 TO peter, antonio

# AS 子句指定策略应如何与其他策略组合。
# 规则解释如下
# restrictive严格满足
# permissive宽松策略
# row_is_visible = (one or more of the permissive policies' conditions are non-zero) AND (all of the restrictive policies's conditions are non-zero)

# 所以下面的写法中peter仅能查看同时满足b=1 and c=2的行         
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
# 下面的写法中peter也仅能查看mysql.table1中同时满足b=1 and c=2的行
CREATE ROW POLICY pol1 ON mydb.* USING b=1 TO mira, peter
CREATE ROW POLICY pol2 ON mydb.table1 USING c=2 AS RESTRICTIVE TO peter, antonio

#默认情况下，CREATE、DROP、ALTER 和 RENAME 查询仅影响执行它们的当前服务器。 在集群设置中，可以使用 ON CLUSTER 子句以分布式方式运行此类查询。例如，以下查询在集群中的每个主机上创建 all_hits 分布式表：
CREATE TABLE IF NOT EXISTS all_hits ON CLUSTER cluster (p Date, i Int32) ENGINE = Distributed(cluster, default, hits)
# 为了正确运行这些查询，每个主机必须具有相同的集群定义（为了简化同步配置，您可以使用 ZooKeeper）。 它们还必须连接到 ZooKeeper 服务器。
# 本地版本的查询最终将在集群中的每个主机上执行，即使某些主机当前不可用。

# 一起其他的展示例子
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
CREATE ROW POLICY filter4 ON mydb.* USING 1 TO admin

2.更改行策略

ALTER [ROW] POLICY [IF EXISTS] name1 [ON CLUSTER cluster_name1] ON [database1.]table1 [RENAME TO new_name1]
        [, name2 [ON CLUSTER cluster_name2] ON [database2.]table2 [RENAME TO new_name2] ...]
    [AS {PERMISSIVE | RESTRICTIVE}]
    [FOR SELECT]
    [USING {condition | NONE}][,...]
    [TO {role [,...] | ALL | ALL EXCEPT role [,...]}]

3.删除行策略

DROP [ROW] POLICY [IF EXISTS] name [,...] ON [database.]table [,...] [ON CLUSTER cluster_name]

4.查看创建行策略

SHOW CREATE [ROW] POLICY name ON [database1.]table1 [, [database2.]table2 ...]

5.查看所有行策略

SHOW [ROW] POLICIES [ON [db.]table]

四、配置文件相关

设置配置文件包含设置和约束，以及应用此配置文件的角色和/或用户的列表。

1.创建配置文件

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

# 例子
# 使用 max_memory_usage 设置的值和约束创建 max_memory_usage_profile 设置配置文件，并将其分配给用户 robin：
CREATE
SETTINGS PROFILE max_memory_usage_profile SETTINGS max_memory_usage = 100000001 MIN 90000000 MAX 110000000
TO robin

2.更改配置文件

ALTER SETTINGS PROFILE [IF EXISTS] TO name1 [ON CLUSTER cluster_name1] [RENAME TO new_name1]
        [, name2 [ON CLUSTER cluster_name2] [RENAME TO new_name2] ...]
    [SETTINGS variable [= value] [MIN [=] min_value] [MAX [=] max_value] [CONST|READONLY|WRITABLE|CHANGEABLE_IN_READONLY] | INHERIT 'profile_name'] [,...]

3.删除配置文件

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

4.查看创建配置文件

SHOW CREATE [SETTINGS] PROFILE name1 [, name2 ...]

5.查看所有配置

SHOW [SETTINGS] PROFILES

五、配额相关

配额包含一组针对某些持续时间的限制，以及应使用此配额的角色和/或用户列表。

1.创建配额

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分钟内最大执行时间限制为半秒，5个季度内最大查询数限制为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;

2.修改配额

ALTER QUOTA [IF EXISTS] name [ON CLUSTER cluster_name]
    [RENAME TO new_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 [,...]}]

# 例子
ALTER QUOTA IF EXISTS qA FOR INTERVAL 15 month MAX queries = 123 TO CURRENT_USER;

3.删除配额

DROP QUOTA [IF EXISTS] name [,...] [ON CLUSTER cluster_name]

4.查看创建配额

SHOW CREATE QUOTA [name1 [, name2 ...] | CURRENT]

5.查看配额

SHOW CREATE QUOTA [name1 [, name2 ...] | CURRENT]
# 返回所有用户或当前用户的配额消耗。 要查看其他参数，请参见系统表system.quotas_usage 和system.quota_usage。
SHOW [CURRENT] QUOTA

六、补充

1.权限结构

.
├── ALTER (only for table and view)/
│   ├── ALTER TABLE/
│   │   ├── ALTER UPDATE
│   │   ├── ALTER DELETE
│   │   ├── ALTER COLUMN/
│   │   │   ├── ALTER ADD COLUMN
│   │   │   ├── ALTER DROP COLUMN
│   │   │   ├── ALTER MODIFY COLUMN
│   │   │   ├── ALTER COMMENT COLUMN
│   │   │   ├── ALTER CLEAR COLUMN
│   │   │   └── ALTER RENAME COLUMN
│   │   ├── ALTER INDEX/
│   │   │   ├── ALTER ORDER BY
│   │   │   ├── ALTER SAMPLE BY
│   │   │   ├── ALTER ADD INDEX
│   │   │   ├── ALTER DROP INDEX
│   │   │   ├── ALTER MATERIALIZE INDEX
│   │   │   └── ALTER CLEAR INDEX
│   │   ├── ALTER CONSTRAINT/
│   │   │   ├── ALTER ADD CONSTRAINT
│   │   │   └── ALTER DROP CONSTRAINT
│   │   ├── ALTER TTL/
│   │   │   └── ALTER MATERIALIZE TTL
│   │   ├── ALTER SETTINGS
│   │   ├── ALTER MOVE PARTITION
│   │   ├── ALTER FETCH PARTITION
│   │   └── ALTER FREEZE PARTITION
│   └── ALTER LIVE VIEW/
│       ├── ALTER LIVE VIEW REFRESH
│       └── ALTER LIVE VIEW MODIFY QUERY
├── ALTER DATABASE
├── ALTER USER
├── ALTER ROLE
├── ALTER QUOTA
├── ALTER [ROW] POLICY
└── ALTER [SETTINGS] PROFILE

GRANT ALTER COLUMN ON my_db.my_table TO my_user;

REVOKE ALTER ADD COLUMN ON my_db.my_table FROM my_user;

​