一、演示环境说明
服务器单机安装完成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
<!-- <listen_host>::</listen_host> -->
<!-- <listen_host>0.0.0.0</listen_host> -->
<listen_host>::1</listen_host>
<listen_host>127.0.0.1</listen_host>
修改后的配置文件如下：
[root@tidb06 ~]# grep listen_host /etc/clickhouse-server/config.xml
<<listen_host>::</listen_host> 允许任意IPv6地址访问
<<listen_host>0.0.0.0</listen_host> 允许任意IPv4地址访问
<!--listen_host>::1</listen_host> -->
<!--listen_host>127.0.0.1</listen_host> -->
设置密码： 下面的设置密码的方法兼容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"?>
<yandex>
<!-- Profiles of settings. -->
<profiles>
<!-- Default settings. -->
<default>
<max_memory_usage>10000000000</max_memory_usage>
<use_uncompressed_cache>0</use_uncompressed_cache>
<load_balancing>random</load_balancing>
<constraints>
<max_memory_usage>
<min>1000000</min>
<max>2000000</max>
</max_memory_usage>
<force_index_by_date>
<readonly/>
</force_index_by_date>
</constraints>
<readonly>0</readonly>
<allow_ddl>1</allow_ddl>
</default>
<!--只读,即使允许DDL-->
<normal_1>
<readonly>1</readonly>
<allow_ddl>1</allow_ddl>
</normal_1>
<!--允许增删改查,允许DDL-->
<normal_2>
<readonly>0</readonly>
<allow_ddl>1</allow_ddl>
</normal_2>
<!--只允许增删改查,不允许DDL-->
<normal_3>
<readonly>0</readonly>
<allow_ddl>0</allow_ddl>
</normal_3>
<!--读且能set,不允许DDL-->
<normal_4>
<readonly>2</readonly>
<allow_ddl>0</allow_ddl>
</normal_4>
<!--只读,不允许DDL-->
<normal_5>
<readonly>0</readonly>
<allow_ddl>0</allow_ddl>
</normal_5>
<readonly>
<readonly>0</readonly>
<max_memory_usage>100000000</max_memory_usage>
</readonly>
<test>
<readonly>readonly</readonly>
<max_memory_usage>10000</max_memory_usage>
</test>
</profiles>
</yandex>
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、配置拥有管理库的权限：
<allow_databases>
<database>test008</database>
</allow_databases>
<access_management>1</access_management>
三、用户权限举例
3.1、配置近似超管用户权限
样例一：配置dba用户拥有超管的权限，建表和删表的权限，建库和删除库的权限,以及创建账户和role角色的权限：
允许从任意服务器访问 tidb06上的clickhourse库,用户权限配置文件内容如下
[root@tidb06 ~]# cat /etc/clickhouse-server/users.d/dba_manage.xml
<yandex>
<users>
<dba>
<!--password_sha256_hex>737d7dfac3176d59cddacbba9bc8be3fe406d78769bc613a023092a37bc849e6</password_sha256_hex> -->
<password_double_sha1_hex>c0952f7212b0161d07c6f45f00fdb73e17430f11</password_double_sha1_hex>
<networks incl="networks" replace="replace">
<ip>::/0</ip>
</networks>
<profile>default</profile>
<quota>default</quota>
<allow_databases>
<database>default</database>
<database>system</database>
<database>test008</database>
<database>db01</database>
</allow_databases>
<access_management>1</access_management>
</dba>
</users>
</yandex>
特别说明: 只有拥有了 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权限 ``` <!--允许增删改查,允许DDL--> <normal_2> <readonly>0</readonly> <allow_ddl>1</allow_ddl> </normal_2>
配置用户,允许特定IP对库进行DDL，这个此用户的配置文件内容如下:
[root@tidb06 ~]# cat /etc/clickhouse-server/users.d/wujianwei_rw.xml
<yandex>
<users>
<wujianwei>
<!--password_sha256_hex>737d7dfac3176d59cddacbba9bc8be3fe406d78769bc613a023092a37bc849e6</password_sha256_hex> -->
<password_double_sha1_hex>c0952f7212b0161d07c6f45f00fdb73e17430f11</password_double_sha1_hex>
<networks incl="networks" replace="replace">
<!--ip>::/0</ip> -->
<ip>172.16.0.246</ip>
</networks>
<profile>normal_2</profile>
<quota>default</quota>
<allow_databases>
<database>test008</database>
</allow_databases>
<access_management>1</access_management>
</wujianwei>
</users>
</yandex>
允许特定的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库进行增删改查
<!--只允许增删改查,不允许DDL-->
<normal_3>
<readonly>0</readonly>
<allow_ddl>0</allow_ddl>
</normal_3>
[root@tidb06 ~]# cat /etc/clickhouse-server/users.d/zhangsan_r.xml
<yandex>
<users>
<zhangsan>
<!--password_sha256_hex>737d7dfac3176d59cddacbba9bc8be3fe406d78769bc613a023092a37bc849e6</password_sha256_hex> -->
<password_double_sha1_hex>c0952f7212b0161d07c6f45f00fdb73e17430f11</password_double_sha1_hex>
<networks incl="networks" replace="replace">
<!--ip>::/0</ip> -->
<ip>172.16.0.197</ip>
</networks>
<profile>normal_3</profile>
<quota>default</quota>
<allow_databases>
<database>test001</database>
</allow_databases>
<databases>
<test>
<!--t_order_mt>
<filter>id >= 500 </filter> 行级限制
<t_order_mt> -->
t_order_mt
</test>
</databases>
</zhangsan>
</users>
</yandex>
测试验证：
[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)
