clickhouse配置只读账号

环境:
OS:Centos 7
DB:clickhouse 21.6.6

 

#######################方法1:修改配置文件######################

1.编辑/etc/clickhouse-server/users.xml文件
在<users></users>栏目里加入如下项目,指定只读用户的profile为readonly

      <ureadonly>
        <password>ureadonly123</password>
        <access_management>1</access_management>
        <networks incl="networks" replace="replace">
                <ip>::/0</ip>
        </networks>
        <profile>readonly</profile>
        <quota>default</quota>
        <allow_databases>
                <database>db_sentinel</database>
                <database>sentinel_dev</database>
                <database>sentinel_test</database>
                <database>db_apisix</database>
        </allow_databases>
      </ureadonly>

 

2.重启服务
systemctl stop clickhouse-server.service
systemctl start clickhouse-server.service
systemctl status clickhouse-server.service

 

#######################方法2:执行SQL语句#####################

1.修改配置文件/etc/clickhouse-server/users.xml
找到如下项目,把注释去掉

<users>
<default>
  <access_management>1</access_management>
</default>
</users>

 

2.重启服务
systemctl stop clickhouse-server.service
systemctl start clickhouse-server.service

 

3.登录clickhouse进行授权

登录
clickhouse-client --host 192.168.1.136 --port=9000 --password 123456

##创建用户hxl,明文明码:123456
create user hxl identified with plaintext_password by '123456';

##创建角色 hxl_role
create role hxl_role;

##授权角色 hxl_role 可以读test_db/system 数据库
grant select on *.* to hxl_role;

##授权 hxl_role  角色给 reader 用户
grant hxl_role to hxl;

 

还可以指定具体的cluster

##创建用户hxl,明文明码:123456
create user hxl on cluster my_cluster identified with plaintext_password by '123456';

##创建角色 hxl_role
create role hxl_role on cluster my_cluster ;

##授权角色 hxl_role 可读所有的库

grant select on *.* to hxl_role on cluster my_cluster ;
##授权 hxl_role  角色给 hxl 用户
grant hxl_role to hxl on cluster my_cluster;