MySQL-Datamask-ProxySQL

MySQL datamasking using ProxySQL

前言

操作环境一览:

- 操作系统： CentOS7

- MySQL: 5.5

- ProxySQL: 1.4.9

- ProxySQL主机IP: 192.168.48.100

- MySQL主库IP: 192.168.48.120

场景

描述

一张带有信用卡信息(faked)等敏感信息的顾客表，
开发、测试用户并不真正需要信用卡号的等敏感信息。

需求

开发测试用户能够通过ProxySQL访问数据
开发测试用户能够访问所有列，但是带有敏感信息的需要隐藏
开发测试用户不能在特定表上执行SELECT *操作

顾客表示例：

+----+-----------+-------------+------------+------------------+----------+
| id | firstname | lastname    | cc_type    | cc_num           | cc_verif |
+----+-----------+-------------+------------+------------------+----------+
|  1 | Frederic  | Descamps    | mastercard | 5275653223285289 |      456 |
|  8 | Dim0      | Vanoverbeke | mastercard | 5345654523285289 |      123 |
| 15 | Kenny     |  Gryp       |  visa      | 4916066793184589 |      456 |
+----+-----------+-------------+------------+------------------+----------+

我们可以在后端mysql主库上(192.168.48.120)创建该测试顾客表:

创建账号

1	CREATE USER 'proxysql'@'192.168.48.120' IDENTIFIED BY '123456';

创建表

create database test;
create table customers
(
  id int(3) not null primary key,
  firstname varchar(20) not null,
  lastname varchar(20) not null,
  cc_type varchar(20) not null,
  cc_num varchar(50) not null,
  cc_verif int(3)
);

授权

1
2
3

GRANT ALL ON test.customers TO 'proxysql'@'192.168.48.120';

FLUSH PRIVILEGES;

ProxySQL

安装ProxySQL

1
2
3

#proxysql需要依赖一些perl库，所以使用yum安装
wget https://github.com/sysown/proxysql/releases/download/v1.4.9/proxysql-1.4.9-3-centos7.x86_64.rpm
yum install -y proxysql-1.4.9-3-centos7.x86_64.rpm

启动ProxySQL

/etc/init.d/proxysql start
#proxysql客户端监听在6033端口上，管理端监听6032端口
连接proxysql管理端进行配置：
mysql -uadmin -padmin -h127.0.0.1 -P6032
#默认的管理端账号密码都是admin，登录进去之后可以修改变量进行修改账号密码

添加后端的mysql主机

将mysql服务器ip换成你的mysql服务器ip

ProxySQL> INSERT INTO mysql_servers(hostgroup_id,hostname,port) 
          VALUES (1,'192.168.48.100',3306);

select * from mysql_servers;

MySQL [(none)]> select * from mysql_servers;
+--------------+-----------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| hostgroup_id | hostname  | port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
+--------------+-----------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| 1            | 192.168.48.120 | 3306 | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              |         |

添加可以访问后端主机的账号

在mysql主库(192.168.48.120)中添加账号proxysql及密码，以及授权

GRANT ALL ON *.* TO 'proxysql'@'192.168.48.120' IDENTIFIED BY '123456';
在proxysql服务器(192.168.48.100)中添加可以增删改查后端mysql服务器的账号

insert into mysql_users(username,password,default_hostgroup,transaction_persistent)values('proxysql','123456',1,1);

MySQL [(none)]> insert into mysql_users(username,password,default_hostgroup,transaction_persistent)values('proxysql','123456',1,1);
Query OK, 1 row affected (0.00 sec)

在proxysql主机的mysql_users表中添加刚才创建的账号，proxysql客户端需要使用这个账号来访问数据库。

default_hostgroup默认组设置为写组，也就是1
当读写分离的路由规则不符合时，会访问默认组的数据库
将刚才我们修改的数据加载至RUNTIME中(参考ProxySQL的多层配置结构)：

load mysql users to runtime;
load mysql servers to runtime;
save mysql users to disk;
save mysql servers to disk;

DataMasking

ProxySQL有查询重写(Query Rewrite)功能，如果你想要重写查询，你必匹配查询的原始语句(使用match_pattern)，因为原始查询语句需要被重写。

添加查询规则

ProxySQL> INSERT INTO mysql_query_rules (rule_id,active,username,match_pattern,error_msg)
          VALUES (90,1,'proxysql','^SELECT \*.*FROM.*customers',
          'Query not allowed due to sensitive information, please contact dba@myapp.com');
Let’s load it in runtime and test
ProxySQL> LOAD MYSQL QUERY RULES TO RUNTIME;

另开一个终端，以6033端口(数据端口)登录:

1	mysql -uproxysql -p123456 -h 192.168.48.100 -P 6033

执行SELECT *操作：

1 2	mysql> select * from test.customers; ERROR 1148 (42000): Query not allowed due to sensitive information, please contact dba@myapp.com

Yeah!我们根据配置的mysql_query_rules成功阻断了对customers表上的SELECT*操作.

我们再在管理连接中插入如下一条查询规则:

ProxySQL> INSERT INTO mysql_query_rules (rule_id,active,username,match_pattern,replace_pattern,apply)
          VALUES (1,1,'proxysql','^[sS][eE][lL][eE][cC][tT] (.*)cc_num([ ,])(.*)', 
                "SELECT \1CONCAT(REPEAT('X',12),RIGHT(cc_num,4)) cc_num\2\3",1);

ProxySQL> LOAD MYSQL QUERY RULES TO RUNTIME;
我们在数据连接中再测试一下:

mysql> select firstname, cc_num from test.customers;
+-----------+------------------+
| firstname | cc_num           |
+-----------+------------------+
| Frederic  | XXXXXXXXXXXX5289 |
| Dim0      | XXXXXXXXXXXX5289 |
| Kenny     | XXXXXXXXXXXX4589 |
+-----------+------------------+

WOOhoo!我们成功实现了只显示卡号后4位！

保存规则到磁盘

1	ProxySQL> SAVE MYSQL QUERY RULES TO DISK;

我们需要对更多的表和字段做更多的datamasking(例如姓名字段做隐藏等)，我们就需要编写更多的查询规则(mysql_query_rules),并在管理连接中添加到mysql_query_rules表中.