clickhouse 添加用户 分配权限

clickhouse的官方文档上面是有create user 和grant授权的，但是命令行下运行都提示无权限。

1，根据官方文档操作报错

testticdc :-] CREATE USER mira HOST IP '127.0.0.1' IDENTIFIED WITH sha256_password BY 'qwerty';

CREATE USER mira IDENTIFIED WITH sha256_hash BY '65E84BE33532FB784C48129675F9EFF3A682B27168C0EA744B2CF58EE02337C5' HOST LOCAL

Received exception from server (version 20.8.3):
Code: 497. DB::Exception: Received from 127.0.0.1:9000. DB::Exception: default: Not enough privileges. To execute this query it's necessary to have the grant CREATE USER ON *.*. 

0 rows in set. Elapsed: 0.002 sec.

这是官方文档上面的demo，跑不通

2，修改users.xml文件

<tanktest>
     <password>123456</password>
     <networks incl="networks" replace="replace">
         <ip>::/0</ip>
     </networks>
     <profile>default</profile>
     <quota>default</quota>
     <allow_databases>
          <database>netjoytest1</database>
     </allow_databases>
     <access_management>1</access_management>
</tanktest>

<testread>
    <password>123456</password>
    <networks incl="networks" replace="replace">
        <ip>::/0</ip>
    </networks>
    <profile>readonly</profile>
    <quota>default</quota>
</testread>

注意：allow_databases允许的数据库，show databases 只能看到允许的库，如果没有表示所有数据库。

scp users.xml 10.0.55.17:/etc/clickhouse-server
scp users.xml 10.0.55.16:/etc/clickhouse-server
scp users.xml 10.0.10.24:/etc/clickhouse-server

ssh 10.0.55.17 "/etc/init.d/clickhouse-server restart"
ssh 10.0.55.16 "/etc/init.d/clickhouse-server restart"
ssh 10.0.10.24 "/etc/init.d/clickhouse-server restart"

只读账号插入会报错。

testpd :-] insert into tanktest values(1, 'tank', '2021-08-23 14:21:30');

INSERT INTO tanktest VALUES

Received exception from server (version 20.8.3):
Code: 164. DB::Exception: Received from 127.0.0.1:9000. DB::Exception: testread: Cannot execute query in readonly mode. 

0 rows in set. Elapsed: 0.003 sec.