大数据-ClickHouse技术五（用户角色管理-SQL工作流）

一、官方网址

访问权限和账户管理 | ClickHouse Docs

默认ClickHouse提供了一个 default 账号，这个账号有所有的权限，但是不能使用SQL驱动方式的访问权限和账户管理。default主要用在用户名还未设置的情况，比如从客户端登录或者执行分布式查询。在分布式查询中如果服务端或者集群没有指定用户名密码那默认的账户就会被使用

查看用户信息：SELECT * from `system`.users;
查看角色信息：SELECT *from `system`.roles;
查看用户角色：SELECT *from system.role_grants;
查看角色权限：SELECT *from `system`.grants;

二、初始用户设置

在安装完Clickhouse后，可以按照default用户进行登录。后续需要自行创建其他用户进行管理。本文通过创建三个角色 role_admin （查看全部数据库全部权限最高权限角色）、

role_read(只读数据库权限)、role_write(读写数据库权限，不可操作用户)

以及对应三个用户user_admin、user_read、user_write进行后续使用

可以通过两种方式配置

三、方式二 通过SQL工作流

1、主要操作步骤有三步

①为 default 用户开启SQL驱动方式的访问权限和账户管理

②使用 default 用户登录并且创建所需要的所有用户。 

③限制 default 用户的权限并且禁用SQL驱动方式的访问权限和账户管理

2、为 default 用户开启SQL驱动方式的访问权限和账户管理 

在/etc/clickhouse-server/user.d/default-password.xml增加配置  <access_management>1</access_management>

3、使用 default 用户登录并且创建所需要的所有用户。 不要忘记创建管理员账户 (GRANT ALL ON *.* WITH GRANT OPTION TO admin_user_account)

创建角色

CREATE ROLE IF NOT EXISTS role1 ON CLUSTER my_shard;

给角色赋权(本人集群DDL有问题，分开执行)：

GRANT ON CLUSTER my_shard privilege ON {db.table|db.*|*.*} TO {user|role} [WITH GRANT OPTION] [WITH REPLACE OPTION]

privilege 可以是 ALL(全部权限)、NONE（不授权限）、SELECT、INSERT、ALTER、CREATE、DROP、TRUNCATE、OPTIMIZE、SHOW、KILL QUERY、ACCESS MANAGEMENT（操作用户角色）多个权限逗号
WITH GRANT OPTION 授予 user 或 role执行 GRANT 操作的权限。用户可将在自身权限范围内的权限进行授权 
WITH REPLACE OPTION 以当前sql里的新权限替代掉 user 或 role的旧权限，如果没有该选项则是追加授权。

创建用户 user_test

CREATE USER IF NOT EXISTS user_test ON CLUSTER my_shard
IDENTIFIED WITH sha256_password BY 'qwerty'
HOST LIKE '%'
DEFAULT ROLE ALL
GRANTEES ANY;

IDENTIFIED WITH sha256_password  或者 IDENTIFIED BY 'qwerty' 
HOST:CREATE USER mira@'127.0.0.1' ip访问
CREATE USER mira@'192.168.%.%' IP集合访问
CREATE USER mira@'%' 全部可以访问
DEFAULT ROLE ALL 时需要后续赋予角色，如果直接给默认角色可以
GRANTEES {user | role | ANY | NONE}  将自己权限授予用户角色

授予角色给用户

GRANT ON CLUSTER my_shard role1 TO {mira|user2}  [WITH ADMIN OPTION] [WITH REPLACE OPTION];

WITH ADMIN OPTION 授予 user 或 role 执行ADMIN OPTION 的权限
WITH REPLACE OPTION 以当前sql里的新role替代掉 user 或 role的旧role，如果没有该选项则是追加roles

3、限制 default 用户的权限并且禁用SQL驱动方式的访问权限和账户管理。

在/etc/clickhouse-server/user.d/default-password.xml增加配置  <access_management>0</access_management>
access_management 是否可以通过SQL工作流管理

4、额外的用户角色查询SQL

修改角色名称：
alter role if exists role1 on cluster my_shard rename to role11;
删除用户：
DROP USER IF EXISTS cicc on cluster my_shard;
删除角色
DROP ROLE IF EXISTS role1 on cluster my_shard;
取消角色的权限
REVOKE insert on default.* FROM role2 ;
取消用户的角色
REVOKE ON CLUSTER my_shard role2 from cicc2;

手动授权：

直接设置角色并查询(不好用)
SET ROLE accountant;
SELECT * FROM db.*;

四、实例代码：

role_admin角色 & user_admin

--新建角色role_admin
CREATE ROLE IF NOT EXISTS role_admin ON CLUSTER my_shard;
--角色授权role_admin
GRANT ALL on *.* to role_admin with grant option with replace option ;
--新建用户 带默认角色
CREATE USER IF NOT EXISTS user_admin ON CLUSTER my_shard
IDENTIFIED WITH sha256_password BY 'qwerty'
HOST LIKE '%'
DEFAULT ROLE role_admin
GRANTEES ANY;

 role_read角色 & user_read

--新建角色role_read
CREATE ROLE IF NOT EXISTS role_read ON CLUSTER my_shard;
--角色授权role_admin
GRANT SELECT on *.* to role_read with grant option with replace option ;
--新建用户 带默认角色
CREATE USER IF NOT EXISTS user_read ON CLUSTER my_shard
IDENTIFIED WITH sha256_password BY 'qwerty'
HOST LIKE '%'
DEFAULT ROLE role_read 
GRANTEES ANY;

 role_write角色 & user_write

--新建角色role_write
CREATE ROLE IF NOT EXISTS role_write ON CLUSTER my_shard;
--角色授权role_admin
GRANT select,insert,alter,create,drop,truncate,optimize,kill query on *.* to role_write with grant option with replace option ;
--新建用户 带默认角色
CREATE USER IF NOT EXISTS user_write ON CLUSTER my_shard
IDENTIFIED WITH sha256_password BY 'qwerty'
HOST LIKE '%'
DEFAULT ROLE role_write 
GRANTEES ANY;

Tips:授权语句在测试中一直不成功，因此暂时先在各个服务器执行。按照官方文档应该是可以直接通过集群的分布式DDL执行的