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;