## 一、演示环境说明 服务器单机安装完成clickhouse服务启动后都是默认绑定127.0.0.1的地址访问的，外网和其他内网之间的服务器是访问不了的，需要调整下默认配置。 **演示环境版本:** ClickHouse server version 20.8.3.18 **环境是阿里的ECS服务器，需要提前在阿里的安全组放开下端口服务，允许特定IP访问下面的端口:** ``` [root@tidb06 ~]# ss -lntup|grep click tcp LISTEN 0 64 127.0.0.1:9000 *:* users:(("clickhouse-serv",pid=1698,fd=37)) tcp LISTEN 0 64 127.0.0.1:9004 *:* users:(("clickhouse-serv",pid=1698,fd=39)) tcp LISTEN 0 64 127.0.0.1:9009 *:* users:(("clickhouse-serv",pid=1698,fd=38)) tcp LISTEN 0 64 127.0.0.1:8123 *:* users:(("clickhouse-serv",pid=1698,fd=36)) ``` **三台测试服务器主机名和对应的内网地址：** ``` tidb06 172.16.0.247 tidb05 172.16.0.246 tidb04 172.16.0.197 ``` **默认的配置文件：只允许本机访问** ``` [root@tidb06 ~]# grep listen_host /etc/clickhouse-server/config.xml ::1127.0.0.1 ``` **修改后的配置文件如下：** ``` [root@tidb06 ~]# grep listen_host /etc/clickhouse-server/config.xml <:: 允许任意IPv6地址访问 <0.0.0.0 允许任意IPv4地址访问 ``` **设置密码：** **下面的设置密码的方法兼容MySQL密码策略** ``` password_double_sha1_hex [root@tidb06 ~]# PASSWORD=$(base64 < /dev/urandom | head -c12); echo "$PASSWORD"; echo -n "$PASSWORD" | sha1sum | tr -d '-' | xxd -r -p | sha1sum | tr -d '-' j780UJy9D2tn c0952f7212b0161d07c6f45f00fdb73e17430f11 ``` **说明下:下面的演示都是基于这个密码** ## 二、users.xml配置文件划分好权限profile角色 ### 2.1、划分权限profile角色 **/etc/clickhouse-server/users.xml 主配置文件profiles标签里面提前划分好权限profile 角色：** ``` [root@tidb06 ~]# cat /etc/clickhouse-server/users.xml <?xml version="1.0"?> 100000000000random100000020000000111010020000100000000readonly10000 ``` ### 2.2、简单的对权限类型的介绍: **Permissions for queries：查询权限管理** 查询可以分为以下几种类型： 读：SELECT，SHOW，DESCRIBE，EXISTS 写：INSERT，OPTIMIZE。 DDL：CREATE，ALTER，RENAME，ATTACH，DETACH，DROP TRUNCATE。 设置：SET，USE。 KILL **以上的权限通过配置标签来控制。** **readonly ：只读权限参数介绍** readonly ：读权限、写权限和设置权限，由此标签控制，它有三种取值： 0，不进行任何限制（默认值）； 1，只拥有读权限（只能执行SELECT、EXISTS、SHOW和DESCRIBE）； 2，拥有读权限和设置权限（在读权限基础上，增加了SET查询）。 当设置readonly=1后，用户将无法在当前会话中更改readonly和allow_ddl设置；也可以通过约束来限制更改权限。 **allow_ddl：DDL权限说明** allow_ddl：DDL权限由此标签控制，它有两种取值： 当取值为0时，不允许DDL查询； 当取值为1时，允许DDL查询（默认值） 如果当前会话的allow_ddl = 0，则无法执行SET allow_ddl = 1 **注意：KILL QUERY可以在任何设置上执行，readonly和allow_ddl需要定义在用户profiles中。** ### 2.3、配置拥有管理库的权限： ``` test0081 ``` ## 三、用户权限举例 ### 3.1、配置近似超管用户权限 **样例一：配置dba用户拥有超管的权限，建表和删表的权限，建库和删除库的权限,以及创建账户和role角色的权限：** 允许从任意服务器访问 tidb06上的clickhourse库,用户权限配置文件内容如下 ``` [root@tidb06 ~]# cat /etc/clickhouse-server/users.d/dba_manage.xml c0952f7212b0161d07c6f45f00fdb73e17430f11::/0defaultdefaultdefaultsystemtest008db011 ``` **特别说明:** **只有拥有了 default和system库才能具有管理员的权限。但是拥有了这2个库还是不能直接创建库的,创建test008和db01库时，需要提前在本用户的权限配置文件中指定对即将创建库test08,db01的管理权限** ``` [root@tidb05 ~]# mysql -udba -h 172.16.0.247 --password=j780UJy9D2tn --port=9004 -e "create database test08" mysql: [Warning] Using a password on the command line interface can be insecure. ERROR 497 (00000) at line 1: Code: 497, e.displayText() = DB::Exception: dba: Not enough privileges. To execute this query it's necessary to have the grant CREATE DATABASE ON test08.* (version 20.8.3.18) [root@tidb05 ~]# mysql -udba -h 172.16.0.247 --password=j780UJy9D2tn --port=9004 -e "create database test008" mysql: [Warning] Using a password on the command line interface can be insecure. [root@tidb05 ~]# mysql -udba -h 172.16.0.247 --password=j780UJy9D2tn --port=9004 -e "show databases;" mysql: [Warning] Using a password on the command line interface can be insecure. +---------+ | name | +---------+ | default | | system | | test008 | +---------+ [root@tidb05 ~]# mysql -udba -h 172.16.0.247 --password=j780UJy9D2tn --port=9004 -e "create database db01" mysql: [Warning] Using a password on the command line interface can be insecure. ERROR 497 (00000) at line 1: Code: 497, e.displayText() = DB::Exception: dba: Not enough privileges. To execute this query it's necessary to have the grant CREATE DATABASE ON db01.* (version 20.8.3.18) [root@tidb05 ~]# [root@tidb05 ~]# [root@tidb05 ~]# mysql -udba -h 172.16.0.247 --password=j780UJy9D2tn --port=9004 -e "create database db01;show databases;" mysql: [Warning] Using a password on the command line interface can be insecure. +---------+ | name | +---------+ | db01 | | default | | system | | test008 | +---------+ [root@tidb05 ~]# [root@tidb04 ~]# clickhouse-client --user=dba -h 172.16.0.247 --password=j780UJy9D2tn --port=9000 -q "show databases;" db01 default system test008 ``` **使用DBA管理员用户登录库，在db01下创建表:** **指定库创建表:** ``` CREATE TABLE test_table( province String, province_name String, create_date date ) ENGINE = MergeTree(create_date, (province), 8192); create table t_order_mt(id UInt32,sku_id String,total_amount Decimal(16,2), create_time Datetime) engine =MergeTree partition by toYYYYMMDD(create_time) primary key (id) order by (id,sku_id); ``` **具体操作:** ``` tidb06 :) use db01; USE db01 Ok. 0 rows in set. Elapsed: 0.001 sec. tidb06 :) show tables; SHOW TABLES Ok. 0 rows in set. Elapsed: 0.002 sec. tidb06 :) create table t_order_mt(id UInt32,sku_id String,total_amount Decimal(16,2), create_time Datetime) engine =MergeTree partition by toYYYYMMDD(create_time) primary key (id) order by (id,sku_id); CREATE TABLE t_order_mt ( `id` UInt32, `sku_id` String, `total_amount` Decimal(16, 2), `create_time` Datetime ) ENGINE = MergeTree PARTITION BY toYYYYMMDD(create_time) PRIMARY KEY id ORDER BY (id, sku_id) Ok. 0 rows in set. Elapsed: 0.006 sec. ``` ``` tidb06 :) select database(); SELECT database() ┌─database()─┐ │ db01 │ └────────────┘ 1 rows in set. Elapsed: 0.002 sec. tidb06 :) CREATE TABLE test_table( province String, province_name String, create_date date ) ENGINE = MergeTree(create_date, (province), 8192); CREATE TABLE test_table ( `province` String, `province_name` String, `create_date` date ) ENGINE = MergeTree(create_date, province, 8192) Ok. 0 rows in set. Elapsed: 0.004 sec. tidb06 :) show tables; SHOW TABLES ┌─name───────┐ │ t_order_mt │ │ test_table │ └────────────┘ tidb06 :) SHOW CREATE test_table; SHOW CREATE TABLE test_table ┌─statement────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐ │ CREATE TABLE db01.test_table ( `province` String, `province_name` String, `create_date` Date ) ENGINE = MergeTree(create_date, province, 8192) │ └──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘ 1 rows in set. Elapsed: 0.001 sec. ``` **在test_table表里面插入数据:** ``` sql语法如下： INSERT INTO [db.]table [(c1, c2, c3)] VALUES (v11, v12, v13), (v21, v22, v23), 插入具体的sql: tidb06 :) INSERT INTO test_table (province, province_name, create_date) VALUES ('山西','太原市','2020-08-25'); INSERT INTO test_table (province, province_name, create_date) VALUES Ok. 1 rows in set. Elapsed: 0.002 sec. tidb06 :) select * from test_table; SELECT * FROM test_table ┌─province─┬─province_name─┬─create_date─┐ │ 山西 │ 太原市 │ 2020-08-25 │ └──────────┴───────────────┴─────────────┘ 1 rows in set. Elapsed: 0.002 sec. ``` ### 3.2、配置用户,允许特定IP对库进行DDL: **样例二：配置用户，允许特定的IP tidb05(172.16.0.246) 访问 clickhourse的某个库，拥有某个库DDL权限** ``` 01 ``` ``` **配置用户,允许特定IP对库进行DDL，这个此用户的配置文件内容如下:** ``` [root@tidb06 ~]# cat /etc/clickhouse-server/users.d/wujianwei_rw.xml c0952f7212b0161d07c6f45f00fdb73e17430f11172.16.0.246normal_2defaulttest0081 ``` **允许特定的IP tidb05(172.16.0.246) 访问tidb06上的clickhourse的test008库，拥有test008库DDL权限** **创建表：** ``` [root@tidb05 ~]# mysql -uwujianwei -h 172.16.0.247 --password=j780UJy9D2tn --port=9004 -e "use test008;CREATE TABLE test_table(province String,province_name String, create_date date) ENGINE=MergeTree(create_date,(province),8192);show tables;" mysql: [Warning] Using a password on the command line interface can be insecure. +------------+ | name | +------------+ | test_table | +------------+ ``` **给创建的的表insert一条数据：** ``` [root@tidb05 ~]# mysql -uwujianwei -h 172.16.0.247 --password=j780UJy9D2tn --port=9004 -e "use test008;INSERT INTO test_table (province, province_name, create_date) VALUES ('山西','太原市','2020-08-25');select * from test_table;" mysql: [Warning] Using a password on the command line interface can be insecure. +----------+---------------+-------------+ | province | province_name | create_date | +----------+---------------+-------------+ | 山西 | 太原市 | 2020-08-25 | +----------+---------------+-------------+ ``` ``` [root@tidb05 ~]# mysql -uwujianwei -h 172.16.0.247 --password=j780UJy9D2tn --port=9004 -e "use test008;CREATE TABLE test_table01(province String,province_name String, create_date date) ENGINE=MergeTree(create_date,(province),8192);show tables;" mysql: [Warning] Using a password on the command line interface can be insecure. +--------------+ | name | +--------------+ | test_table | | test_table01 | +--------------+ ``` **drop table:** ``` [root@tidb05 ~]# mysql -uwujianwei -h 172.16.0.247 --password=j780UJy9D2tn --port=9004 -e "use test008;drop TABLE test_table01;show tables;" mysql: [Warning] Using a password on the command line interface can be insecure. +------------+ | name | +------------+ | test_table | +------------+ ``` **truncate table: ** ``` [root@tidb05 ~]# mysql -uwujianwei -h 172.16.0.247 --password=j780UJy9D2tn --port=9004 -e "use test008;truncate table test_table;select * from test_table;" mysql: [Warning] Using a password on the command line interface can be insecure. ``` **从tidb04服务器登录库测试提示连接库失败:** ``` [root@tidb04 ~]# mysql -uwujianwei -h 172.16.0.247 --password=j780UJy9D2tn --port=9004 -e "show databases;" mysql: [Warning] Using a password on the command line interface can be insecure. ERROR 516 (00000): wujianwei: Authentication failed: password is incorrect or there is no user with such name [root@tidb04 ~]# ``` ### 3.3、配置用户，允许特定IP连接库进行增删改查 **样例三：配置用户，允许特定的IP tidb04(172.16.0.197) 访问 clickhourse的test001库进行增删改查** ``` 00 ``` ``` [root@tidb06 ~]# cat /etc/clickhouse-server/users.d/zhangsan_r.xml c0952f7212b0161d07c6f45f00fdb73e17430f11172.16.0.197normal_3defaulttest001 t_order_mt ``` **测试验证：** ``` [root@tidb04 ~]# mysql -uzhangsan -h 172.16.0.247 --password=j780UJy9D2tn --port=9004 -e "use test001;show tables; INSERT INTO test_table (province, province_name, create_date) VALUES ('山西','太原市','2020-08-25');select * from test_table;" mysql: [Warning] Using a password on the command line interface can be insecure. +--------------+ | name | +--------------+ | t_order_mt | | test_table | | test_table01 | | test_table02 | +--------------+ +----------+---------------+-------------+ | province | province_name | create_date | +----------+---------------+-------------+ | 山西 | 太原市 | 2020-08-25 | +----------+---------------+-------------+ ``` ``` [root@tidb04 ~]# mysql -uzhangsan -h 172.16.0.247 --password=j780UJy9D2tn --port=9004 -e "use test001;show tables; truncate table test_table;" mysql: [Warning] Using a password on the command line interface can be insecure. +--------------+ | name | +--------------+ | t_order_mt | | test_table | | test_table01 | | test_table02 | +--------------+ ERROR 392 (00000) at line 1: Code: 392, e.displayText() = DB::Exception: zhangsan: Cannot execute query. DDL queries are prohibited for the user (version 20.8.3.18) ```