本文主要是介绍ClichHouse-003-用户权限样例，对大家解决编程问题具有一定的参考价值，需要的程序猿们随着小编来一起学习吧！
## 一、演示环境说明
服务器单机安装完成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
::1
127.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"?>
10000000000
0
random
1000000
2000000
0
1
1
1
0
1
0
0
2
0
0
0
0
100000000
readonly
10000
```
### 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、配置拥有管理库的权限：
```
test008
1
```
## 三、用户权限举例
### 3.1、配置近似超管用户权限
**样例一：配置dba用户拥有超管的权限，建表和删表的权限，建库和删除库的权限,以及创建账户和role角色的权限：**
允许从任意服务器访问 tidb06上的clickhourse库,用户权限配置文件内容如下
```
[root@tidb06 ~]# cat /etc/clickhouse-server/users.d/dba_manage.xml
c0952f7212b0161d07c6f45f00fdb73e17430f11
::/0
default
default
default
system
test008
db01
1
```
**特别说明:**
**只有拥有了 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权限**
```
0
1
```
```
**配置用户,允许特定IP对库进行DDL，这个此用户的配置文件内容如下:**
```
[root@tidb06 ~]# cat /etc/clickhouse-server/users.d/wujianwei_rw.xml
c0952f7212b0161d07c6f45f00fdb73e17430f11
172.16.0.246
normal_2
default
test008
1
```
**允许特定的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库进行增删改查**
```
0
0
```
```
[root@tidb06 ~]# cat /etc/clickhouse-server/users.d/zhangsan_r.xml
c0952f7212b0161d07c6f45f00fdb73e17430f11
172.16.0.197
normal_3
default
test001
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)
```
这篇关于ClichHouse-003-用户权限样例的文章就介绍到这儿，希望我们推荐的文章对大家有所帮助，也希望大家多多支持为之网！
