Clickhouse权限大全

所有权限

SHOW, SELECT, INSERT, ALTER, CREATE DATABASE, CREATE TABLE, CREATE VIEW, CREATE DICTIONARY, DROP, TRUNCATE, OPTIMIZE, SYSTEM MERGES, SYSTEM TTL MERGES, SYSTEM FETCHES, SYSTEM MOVES, SYSTEM SENDS, SYSTEM REPLICATION QUEUES, SYSTEM DROP REPLICA, SYSTEM SYNC REPLICA, SYSTEM RESTART REPLICA, SYSTEM RESTORE REPLICA, SYSTEM FLUSH DISTRIBUTED, dictGet

详细列表

ROLE授权

创建角色

CREATE ROLE adminALL; 
CREATE ROLE appSELECT; 
CREATE ROLE appRW; 
#集群角色 
CREATE ROLE adminRW on cluster ch_cluster_all;

角色赋权

GRANT ALL ON *.* TO adminALL; GRANT SELECT ON *.* TO appSELECT; 
#集群授权 
GRANT SHOW, SELECT, INSERT, ALTER, CREATE DATABASE, CREATE TABLE, CREATE VIEW, CREATE DICTIONARY, DROP, TRUNCATE, OPTIMIZE ON *.* TO appRW on cluster ch_cluster_all;

创建用户

创建管理员用户

CREATE USER dba IDENTIFIED WITH PLAINTEXT_PASSWORD BY '123456' on cluster ch_cluster_all; 
GRANT ALL ON *.* TO dba WITH GRANT OPTION on cluster ch_cluster_all;

或者基于role创建

CREATE USER dba IDENTIFIED WITH PLAINTEXT_PASSWORD BY '123456' DEFAULT ROLE adminALL; 
#集群用户
CREATE USER dbas IDENTIFIED WITH PLAINTEXT_PASSWORD BY '123456' DEFAULT ROLE adminALLL on cluster ch_cluster_all;

创建普通用户

CREATE USER test1 IDENTIFIED WITH PLAINTEXT_PASSWORD BY '123456' on cluster ch_cluster_all;

用户授权

GRANT SELECT ON testdb.* TO test1 WITH GRANT OPTION; GRANT SHOW, SELECT, INSERT, ALTER, CREATE DATABASE, CREATE TABLE, CREATE VIEW, CREATE DICTIONARY, DROP, TRUNCATE, OPTIMIZE ON testdb.* TO test2 WITH GRANT OPTION; 
#集群授权
GRANT SELECT ON testdb.* TO test1 WITH GRANT OPTION on cluster ch_cluster_all;

或者基于role创建

CREATE USER test3 IDENTIFIED WITH PLAINTEXT_PASSWORD BY '123456' DEFAULT ROLE appSELECT on cluster ch_cluster_all;
#集群创建
CREATE USER test4 IDENTIFIED WITH PLAINTEXT_PASSWORD BY '123456' DEFAULT ROLE appRW on cluster ch_cluster_all;

解除授权

REVOKE SELECT ON testdb.* FROM test1 on cluster ch_cluster_all;
REVOKE appRW FROM test4 on cluster ch_cluster_all;
REVOKE adminALL FROM dba on cluster ch_cluster_all;