ClichHouse-003-用户权限样例

2021/6/26 23:56:46

本文主要是介绍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-用户权限样例的文章就介绍到这儿,希望我们推荐的文章对大家有所帮助,也希望大家多多支持为之网!


扫一扫关注最新编程教程