SRE文档

深浅模式

1. ckRBAC管理 ​

1.1 介绍 ​

ClickHouse也支持基于RBAC（Role-Based Access Control）的访问控制管理，即通过SQL-driven来进行管理。在 RBAC 中，权限与角色相关联，通过成为角色的成员而得到这些角色的权限。简化了权限的管理。这样管理都是层级相互依赖的，权限赋予给角色，而把角色又赋予用户，ClickHouse推荐使用该方式进行用户权限管理。

1.2 SQL-driven ​

官当

ClickHouse提供了一个default账号，这个账号有所有的权限，但是不能使用SQL驱动方式的访问权限和账户管理。我们需要在配置文件中修改default账户，使其能够通过SQL驱动方式添加角色和用户。默认default用户是没有办法创建用户的

1.开启 ​

xml
<access_management>1</access_management>
<named_collection_control>1</named_collection_control>
<show_named_collections>1</show_named_collections>
<show_named_collections_secrets>1</show_named_collections_secrets>
<access_management>1</access_management>
<named_collection_control>1</named_collection_control>
<show_named_collections>1</show_named_collections>
<show_named_collections_secrets>1</show_named_collections_secrets>
xml
vim /etc/clickhouse-server/user.xml
#创建管理员用户，或者用default用户
    <users>
        <!-- Previous settings -->

        <!-- If user name was not specified, 'default' user is used. -->
        <account_admin> <!-- user_name -->
            <password>gRLTzih8mUqVpa88</password>

            <access_management>1</access_management>
            <named_collection_control>1</named_collection_control>
            <show_named_collections>1</show_named_collections>
            <show_named_collections_secrets>1</show_named_collections_secrets>

            <networks>
                <!-- <ip>::/0</ip> -->
                <ip>127.0.0.1</ip>
                <ip>10.132.60.101/10.132.60.104</ip>
            </networks>

            <profile>default</profile>
            <quota>default</quota>

        </account_admin>
        <!-- Other users settings -->
    </users>
vim /etc/clickhouse-server/user.xml
#创建管理员用户，或者用default用户
    <users>
        <!-- Previous settings -->

        <!-- If user name was not specified, 'default' user is used. -->
        <account_admin> <!-- user_name -->
            <password>gRLTzih8mUqVpa88</password>

            <access_management>1</access_management>
            <named_collection_control>1</named_collection_control>
            <show_named_collections>1</show_named_collections>
            <show_named_collections_secrets>1</show_named_collections_secrets>

            <networks>
                <!-- <ip>::/0</ip> -->
                <ip>127.0.0.1</ip>
                <ip>10.132.60.101/10.132.60.104</ip>
            </networks>

            <profile>default</profile>
            <quota>default</quota>

        </account_admin>
        <!-- Other users settings -->
    </users>

1-->开启 0-->关闭

  • 重启服务
bash
systemctl restart clickhouse-server.service
systemctl restart clickhouse-server.service

2.创建super用户 ​

  • 登录
bash
[root@kubeadm-master01 clickhouse-server]# clickhouse-client -m
ClickHouse client version 23.12.6.19 (official build).
Connecting to localhost:9000 as user default.
Password for user (default)
[root@kubeadm-master01 clickhouse-server]# clickhouse-client -m
ClickHouse client version 23.12.6.19 (official build).
Connecting to localhost:9000 as user default.
Password for user (default)
  • 创建超级管理员用户
sql
 #创建super_admin用户，默认密码保存方式是sha256_password
 CREATE USER super_admin IDENTIFIED BY 'passwd';
 
 #分配所有权限，并允许super_admin授权给其他用户
 GRANT ALL ON *.* TO super_admin WITH GRANT OPTION;
 
 #查看用户
 select * from system.users;
 #创建super_admin用户，默认密码保存方式是sha256_password
 CREATE USER super_admin IDENTIFIED BY 'passwd';
 
 #分配所有权限，并允许super_admin授权给其他用户
 GRANT ALL ON *.* TO super_admin WITH GRANT OPTION;
 
 #查看用户
 select * from system.users;

1.3 权限分类 ​

官当

1.4 创建用户 ​

官当

语法：

sql
CREATE USER [IF NOT EXISTS | OR REPLACE] name1 [, name2 [,...]] [ON CLUSTER cluster_name]
    [NOT IDENTIFIED | IDENTIFIED {[WITH {plaintext_password | sha256_password | sha256_hash | double_sha1_password | double_sha1_hash}] BY {'password' | 'hash'}} | WITH NO_PASSWORD | {WITH ldap SERVER 'server_name'} | {WITH kerberos [REALM 'realm']} | {WITH ssl_certificate CN 'common_name' | SAN 'TYPE:subject_alt_name'} | {WITH ssh_key BY KEY 'public_key' TYPE 'ssh-rsa|...'} | {WITH http SERVER 'server_name' [SCHEME 'Basic']} [VALID UNTIL datetime] 
    [, {[{plaintext_password | sha256_password | sha256_hash | ...}] BY {'password' | 'hash'}} | {ldap SERVER 'server_name'} | {...} | ... [,...]]]
    [HOST {LOCAL | NAME 'name' | REGEXP 'name_regexp' | IP 'address' | LIKE 'pattern'} [,...] | ANY | NONE]
    [VALID UNTIL datetime]
    [IN access_storage_type]
    [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'] [,...]
CREATE USER [IF NOT EXISTS | OR REPLACE] name1 [, name2 [,...]] [ON CLUSTER cluster_name]
    [NOT IDENTIFIED | IDENTIFIED {[WITH {plaintext_password | sha256_password | sha256_hash | double_sha1_password | double_sha1_hash}] BY {'password' | 'hash'}} | WITH NO_PASSWORD | {WITH ldap SERVER 'server_name'} | {WITH kerberos [REALM 'realm']} | {WITH ssl_certificate CN 'common_name' | SAN 'TYPE:subject_alt_name'} | {WITH ssh_key BY KEY 'public_key' TYPE 'ssh-rsa|...'} | {WITH http SERVER 'server_name' [SCHEME 'Basic']} [VALID UNTIL datetime] 
    [, {[{plaintext_password | sha256_password | sha256_hash | ...}] BY {'password' | 'hash'}} | {ldap SERVER 'server_name'} | {...} | ... [,...]]]
    [HOST {LOCAL | NAME 'name' | REGEXP 'name_regexp' | IP 'address' | LIKE 'pattern'} [,...] | ANY | NONE]
    [VALID UNTIL datetime]
    [IN access_storage_type]
    [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'] [,...]

1.创建用户sha256_password ​

sql
 #创建super_admin用户，默认密码保存方式是sha256_password，此处不授权只有登录的权限
 CREATE USER super_admin IDENTIFIED BY 'passwd';
 #创建super_admin用户，默认密码保存方式是sha256_password，此处不授权只有登录的权限
 CREATE USER super_admin IDENTIFIED BY 'passwd';
  • 查看用户
sql
show users;
show users;

2.创建用户 ​

指定加密方式

sql
CREATE USER name2 IDENTIFIED WITH plaintext_password BY 'my_password'
CREATE USER name2 IDENTIFIED WITH plaintext_password BY 'my_password'
sql
#创建管理设备表的角色
CREATE ROLE xxx_read_write; 
GRANT SELECT, INSERT ON db01.x1_table TO xxx_read_write; 
GRANT SELECT ON db01.x2_table TO device_read_write; 
CREATE USER user_001 IDENTIFIED WITH sha256_password BY '12xxyy' HOST IP '::/0' DEFAULT ROLE xxx_read_write;
#创建管理设备表的角色
CREATE ROLE xxx_read_write; 
GRANT SELECT, INSERT ON db01.x1_table TO xxx_read_write; 
GRANT SELECT ON db01.x2_table TO device_read_write; 
CREATE USER user_001 IDENTIFIED WITH sha256_password BY '12xxyy' HOST IP '::/0' DEFAULT ROLE xxx_read_write;

3.创建没有密码用户 ​

sql
CREATE USER user_name NOT IDENTIFIED
CREATE USER user_name NOT IDENTIFIED

4.创建指定ip用户 ​

sql
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';
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';
sql
CREATE USER mira@'127.0.0.1' — Equivalent to the HOST IP syntax.

CREATE USER mira@'localhost' — Equivalent to the HOST LOCAL syntax.

CREATE USER mira@'192.168.%.%' — Equivalent to the HOST LIKE syntax.
CREATE USER mira@'127.0.0.1' — Equivalent to the HOST IP syntax.

CREATE USER mira@'localhost' — Equivalent to the HOST LOCAL syntax.

CREATE USER mira@'192.168.%.%' — Equivalent to the HOST LIKE syntax.
sql
PASSWORD=$(base64 < /dev/urandom | head -c8); echo "$PASSWORD"; echo -n "$PASSWORD" | sha256sum | tr -d '-'


CREATE USER mira HOST IP '127.0.0.1' IDENTIFIED WITH sha256_password BY 'qwerty';
PASSWORD=$(base64 < /dev/urandom | head -c8); echo "$PASSWORD"; echo -n "$PASSWORD" | sha256sum | tr -d '-'


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

1.5 删除用户 ​

官当

语法：

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

1.6 查看用户权限 ​

  • 查看用户
sql
 select * from system.users;
 select * from system.users;
  • 查看某用户权限
sql
ch_accesslog :) show grants for user_name;

SHOW GRANTS FOR super_admin

Query id: 5ffca0b8-b194-4542-9fcb-5a80878e7a3f

┌─GRANTS FOR super_admin────────────────────────────┐
│ GRANT ALL ON *.* TO super_admin WITH GRANT OPTION │
└───────────────────────────────────────────────────┘

1 row in set. Elapsed: 0.018 sec.
ch_accesslog :) show grants for user_name;

SHOW GRANTS FOR super_admin

Query id: 5ffca0b8-b194-4542-9fcb-5a80878e7a3f

┌─GRANTS FOR super_admin────────────────────────────┐
│ GRANT ALL ON *.* TO super_admin WITH GRANT OPTION │
└───────────────────────────────────────────────────┘

1 row in set. Elapsed: 0.018 sec.

1.7 修改用户权限 ​

sql
#修改密码并配置远程访问
alter user user_name@'192.168.%.%' rename to username@'%' identified with sha256_password by 'password';

#只修改密码
#生成sha256的密码
echo -n "password" | sha256sum | tr -d '-'

ALTER USER abce IDENTIFIED WITH sha256_hash BY '5e884898da28047151d0e56f8dc6292773603d0d6aabbdd62a11ef721d1542d8';
#修改密码并配置远程访问
alter user user_name@'192.168.%.%' rename to username@'%' identified with sha256_password by 'password';

#只修改密码
#生成sha256的密码
echo -n "password" | sha256sum | tr -d '-'

ALTER USER abce IDENTIFIED WITH sha256_hash BY '5e884898da28047151d0e56f8dc6292773603d0d6aabbdd62a11ef721d1542d8';

1.8 权限授权 ​

官当

语法：

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

1.根据普通用户 ​

  • 创建数据库
sql
CREATE DATABASE IF NOT EXISTS your_database_name;
CREATE DATABASE IF NOT EXISTS your_database_name;
  • 创建用户
sql
CREATE USER your_username IDENTIFIED WITH plaintext_password BY 'your_password';
CREATE USER your_username IDENTIFIED WITH plaintext_password BY 'your_password';
  • 赋予读写
sql
GRANT SELECT, INSERT ON your_database_name.* TO your_username;
GRANT SELECT, INSERT ON your_database_name.* TO your_username;
  • 赋予所有
sql
GRANT ALL ON your_database_name.* TO your_username;
GRANT ALL ON your_database_name.* TO your_username;

2.根据角色，推荐 ​

❌ 注意

查看当前所有的权限,SHOW ACCESS

使用GRANT 查询可以把特权授予给角色。用REVOKE来撤回特权。

1.创建role ​

官当

语法：

sql
CREATE ROLE [IF NOT EXISTS | OR REPLACE] name
    [SETTINGS variable [= value] [MIN [=] min_value] [MAX [=] max_value] [READONLY|WRITABLE] | PROFILE 'profile_name'] [,...]
CREATE ROLE [IF NOT EXISTS | OR REPLACE] name
    [SETTINGS variable [= value] [MIN [=] min_value] [MAX [=] max_value] [READONLY|WRITABLE] | PROFILE 'profile_name'] [,...]
sql
#创建
create role dba_role;

#查看
show roles;
#创建
create role dba_role;

#查看
show roles;

2.给角色授权 ​

sql
GRANT SELECT ON you_db_name.* TO dba_role;
GRANT SELECT ON you_db_name.* TO dba_role;

3.给用户授权 ​

sql
GRANT dba_role TO user_name;

# 激活当前用户角色，这样用户便拥有权限了
SET ROLE dba_role;
GRANT dba_role TO user_name;

# 激活当前用户角色，这样用户便拥有权限了
SET ROLE dba_role;

4.创建帐户 ​

语法：

sql
CREATE USER [IF NOT EXISTS | OR REPLACE] name [ON CLUSTER cluster_name]
    [IDENTIFIED [WITH {NO_PASSWORD|PLAINTEXT_PASSWORD|SHA256_PASSWORD|SHA256_HASH|DOUBLE_SHA1_PASSWORD|DOUBLE_SHA1_HASH}] BY {'password'|'hash'}]
    [HOST {LOCAL | NAME 'name' | REGEXP 'name_regexp' | IP 'address' | LIKE 'pattern'} [,...] | ANY | NONE]
    [DEFAULT ROLE role [,...]]
    [SETTINGS variable [= value] [MIN [=] min_value] [MAX [=] max_value] [READONLY|WRITABLE] | PROFILE 'profile_name'] [,...]
CREATE USER [IF NOT EXISTS | OR REPLACE] name [ON CLUSTER cluster_name]
    [IDENTIFIED [WITH {NO_PASSWORD|PLAINTEXT_PASSWORD|SHA256_PASSWORD|SHA256_HASH|DOUBLE_SHA1_PASSWORD|DOUBLE_SHA1_HASH}] BY {'password'|'hash'}]
    [HOST {LOCAL | NAME 'name' | REGEXP 'name_regexp' | IP 'address' | LIKE 'pattern'} [,...] | ANY | NONE]
    [DEFAULT ROLE role [,...]]
    [SETTINGS variable [= value] [MIN [=] min_value] [MAX [=] max_value] [READONLY|WRITABLE] | PROFILE 'profile_name'] [,...]
sql
# PASSWORD=$(base64 < /dev/urandom | head -c8); echo "$PASSWORD"; echo -n "$PASSWORD" | sha256sum | tr -d '-'
Jt9Us0rG   ## 明文密码
de5a7f4866fd2205876184dce54bc89921052a9057a9fc4e4346f4e073a2123d   ## 加密后的密码

#
CREATE USER dba_u@'192.168.%' IDENTIFIED WITH sha256_password BY '123456';

#或者
CREATE USER `dba_u@192.168.%` IDENTIFIED WITH sha256_hash BY '8D969EEF6ECAD3C29A3A629280E686CF0C3F5D5A86AFF3CA12020C923ADC6C92' HOST LIKE '192.168.%'
# PASSWORD=$(base64 < /dev/urandom | head -c8); echo "$PASSWORD"; echo -n "$PASSWORD" | sha256sum | tr -d '-'
Jt9Us0rG   ## 明文密码
de5a7f4866fd2205876184dce54bc89921052a9057a9fc4e4346f4e073a2123d   ## 加密后的密码

#
CREATE USER dba_u@'192.168.%' IDENTIFIED WITH sha256_password BY '123456';

#或者
CREATE USER `dba_u@192.168.%` IDENTIFIED WITH sha256_hash BY '8D969EEF6ECAD3C29A3A629280E686CF0C3F5D5A86AFF3CA12020C923ADC6C92' HOST LIKE '192.168.%'

5.查看 ​

sql
#查看授权
SHOW GRANTS;

#
SELECT * FROM system.roles;
ch_accesslog :) select * from system.roles;
┌─name─┬─id───────────────────────────────────┬─storage─────────┐
│ dba  │ e8493f7d-5c7b-3ebd-d79f-67e500d22c8b │ local_directory │
└──────┴──────────────────────────────────────┴─────────────────┘

#查看用户
show users;
#查看授权
SHOW GRANTS;

#
SELECT * FROM system.roles;
ch_accesslog :) select * from system.roles;
┌─name─┬─id───────────────────────────────────┬─storage─────────┐
│ dba  │ e8493f7d-5c7b-3ebd-d79f-67e500d22c8b │ local_directory │
└──────┴──────────────────────────────────────┴─────────────────┘

#查看用户
show users;

6.删除角色 ​

语法：

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

7.修改角色 ​

语法：

sql
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'] [,...]
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.集群角色 ​

sql
-- 创建用户 guaoran、明文明码：123456
create user if not exists guaoran on cluster my_cluster 
IDENTIFIED WITH plaintext_password BY '123456';

-- 创建角色 guaoran_role
create role if not exists guaoran_role on cluster my_cluster ;


-- 授权 test_db 库查询权限给 角色 guaoran_role
GRANT SELECT ON test_db.* TO guaoran_role on cluster my_cluster ;

-- 分配角色给用户 guraoran
GRANT guaoran_role TO guaoran on cluster my_cluster ;
-- 创建用户 guaoran、明文明码：123456
create user if not exists guaoran on cluster my_cluster 
IDENTIFIED WITH plaintext_password BY '123456';

-- 创建角色 guaoran_role
create role if not exists guaoran_role on cluster my_cluster ;


-- 授权 test_db 库查询权限给 角色 guaoran_role
GRANT SELECT ON test_db.* TO guaoran_role on cluster my_cluster ;

-- 分配角色给用户 guraoran
GRANT guaoran_role TO guaoran on cluster my_cluster ;
  • 读
sql
-- 创建用户 reader、明文明码：123456
create user if not exists reader on cluster my_cluster 
IDENTIFIED WITH plaintext_password BY '123456';

-- 创建角色 reader_role
create role if not exists reader_role on cluster my_cluster ;

-- 授权角色 reader_role 可以读test_db/system 数据库
GRANT SELECT ON test_db.* TO reader_role on cluster my_cluster ;
GRANT SELECT ON system.* TO reader_role on cluster my_cluster ;

-- 授权 reader_role  角色给 reader 用户
GRANT reader_role TO reader on cluster my_cluster  ;
-- 创建用户 reader、明文明码：123456
create user if not exists reader on cluster my_cluster 
IDENTIFIED WITH plaintext_password BY '123456';

-- 创建角色 reader_role
create role if not exists reader_role on cluster my_cluster ;

-- 授权角色 reader_role 可以读test_db/system 数据库
GRANT SELECT ON test_db.* TO reader_role on cluster my_cluster ;
GRANT SELECT ON system.* TO reader_role on cluster my_cluster ;

-- 授权 reader_role  角色给 reader 用户
GRANT reader_role TO reader on cluster my_cluster  ;
  • 写
sql

-- 创建用户 writer、明文明码：123456
create user if not exists writer on cluster my_cluster 
IDENTIFIED WITH plaintext_password BY '123456';
-- 创建角色 wirter_role
create role if not exists wirter_role on cluster my_cluster ;
-- 授权角色 wirter_role  可以读写所有数据库
GRANT SHOW, SELECT, INSERT, ALTER, CREATE, DROP, TRUNCATE, OPTIMIZE, KILL QUERY, MOVE PARTITION BETWEEN SHARDS, SYSTEM, dictGet, INTROSPECTION, SOURCES ON *.* TO wirter_role on cluster my_cluster ;

-- 授权 wirter_role  角色给 guaoran 用户
GRANT wirter_role TO guaoran on cluster my_cluster ;

-- 收回 guaoran 用户 wirter_role  角色
REVOKE wirter_role from guaoran on cluster my_cluster ;

-- 创建用户 writer、明文明码：123456
create user if not exists writer on cluster my_cluster 
IDENTIFIED WITH plaintext_password BY '123456';
-- 创建角色 wirter_role
create role if not exists wirter_role on cluster my_cluster ;
-- 授权角色 wirter_role  可以读写所有数据库
GRANT SHOW, SELECT, INSERT, ALTER, CREATE, DROP, TRUNCATE, OPTIMIZE, KILL QUERY, MOVE PARTITION BETWEEN SHARDS, SYSTEM, dictGet, INTROSPECTION, SOURCES ON *.* TO wirter_role on cluster my_cluster ;

-- 授权 wirter_role  角色给 guaoran 用户
GRANT wirter_role TO guaoran on cluster my_cluster ;

-- 收回 guaoran 用户 wirter_role  角色
REVOKE wirter_role from guaoran on cluster my_cluster ;
  • 查看
sql
SHOW ACCESS
SHOW ACCESS

1.9 回收权限 ​

官当

语法：

sql
REVOKE [ON CLUSTER cluster_name] privilege[(column_name [,...])] [,...] ON {db.table|db.*|*.*|table|*} FROM {user | CURRENT_USER} [,...] | ALL | ALL EXCEPT {user | CURRENT_USER} [,...]
REVOKE [ON CLUSTER cluster_name] privilege[(column_name [,...])] [,...] ON {db.table|db.*|*.*|table|*} FROM {user | CURRENT_USER} [,...] | ALL | ALL EXCEPT {user | CURRENT_USER} [,...]
sql
REVOKE CREATE TABLE ON test_auth_db.* FROM test_user;
REVOKE DROP TABLE ON test_auth_db.* FROM test_user;
REVOKE ALTER ON test_auth_db.* FROM test_user;
REVOKE SELECT ON test_auth_db.* FROM test_user;
REVOKE CREATE TABLE ON test_auth_db.* FROM test_user;
REVOKE DROP TABLE ON test_auth_db.* FROM test_user;
REVOKE ALTER ON test_auth_db.* FROM test_user;
REVOKE SELECT ON test_auth_db.* FROM test_user;

https://blog.csdn.net/weixin_38070561/article/details/131421734

https://www.cnblogs.com/MrYang-11-GetKnow/p/15896355.html

http://www.mllib.cn/?id=9

https://www.cnblogs.com/MrYang-11-GetKnow/p/15896355.html