ClickHouse 创建用户

ClickHouse由2种方法可以创建用户，分别为配置文件和SQL2种；

一、配置文件
编辑/etc/clickhouse-server/users.xml文件
在栏目里加入如下内容,如果是只读权限，则需要加上指定只读用户的profile为readonly

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

然后重启服务

systemctl stop clickhouse-server.service
systemctl start clickhouse-server.service
systemctl status clickhouse-server.service

二、用SQL配置
这种方式需要提前将默认的default用户的最高权限打开

<access_management>1</access_management>

同样，需要时上面配置生效，需要重启服务，不通过最好在安装时就配置好

登录

clickhouse-client --host 127.0.0.1 --port=9000 --password 123456
或者全部使用默认参数
clickhouse-client

检查用户状态

select * from system.users;

不指定cluster
创建用户user_archive,明文明码:123456

create user user_archive identified with plaintext_password by '123456';

创建角色 user_archive_role

create role user_archive_role;

授权角色 user_archive_role 可以读test_db/system 数据库

grant select on test_db.* to user_archive_role;
grant select on system.* to user_archive_role;

授权 user_archive_role 角色给 user_archive 用户

grant user_archive_role to user_archive;

指定cluster
创建用户user_archive,明文明码:123456

create user user_archive on cluster my_cluster identified with plaintext_password by '123456';

创建角色 user_archive_role

create role user_archive_role on cluster my_cluster ;

授权角色 user_archive_role 可读所有的库

grant select on *.* to user_archive_role on cluster my_cluster ;

授权 user_archive_role 角色给 user_archive 用户

grant user_archive_role to user_archive on cluster my_cluster;

同样，后期修改用户密码为明文密码

ALTER USER 'user_archive' IDENTIFIED BY 'user_archive_password';

或者，如果需要明确指定密码策略

ALTER USER 'user_archive' IDENTIFIED WITH PLAINTEXT_PASSWORD BY 'user_archive_password';