一、用户相关
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;