一种基于proxysql的数据脱敏思路

• 2019 年 10 月 4 日
• 笔记

背景：我们这边给研发查数据的是通过phpmyadmin进行的，通常情况下研发人员查数据写法是 select * from db1.tb1 where id=xxxx 。

脱敏的思路：通过proxysql对 关于testdb.t_user 表的查询做改写。

实验环境：

数据库主机： 192.168.20.10:3306  

mysql账号：dba    密码： dba

proxysql版本不限，mysql版本不限

### 需要脱敏的原始SQL：

use testdb;  select * from t_user limit 0,25 ;

### 需要改写成如下效果：

select id,uid,nickname,insert(usr_mobile, 4, 4, 'xxxxx') as usr_mobile from t_user LIMIT 0,25;

下面开始开搞。。。。。。

登录进proxysql的6032管理端口，执行如下命令：

use main ;    insert into mysql_servers(hostgroup_id,hostname,port,weight,max_connections,max_replication_lag,comment)  values(100,'192.168.20.10',3306,1,1000,10,'testdb');  insert into mysql_users(username,password,active,default_hostgroup,transaction_persistent) values('dba','dba',1,100,1);    set mysql-default_charset='utf8mb4';  set mysql-query_retries_on_failure=0;  set mysql-ping_timeout_server=500;  set mysql-monitor_connect_timeout=1000;  set mysql-default_max_latency_ms=2000;  set mysql-monitor_replication_lag_interval=500;  set mysql-ping_interval_server_msec=3000;  set mysql-monitor_ping_interval=5000;  set mysql-connect_timeout_server_max=3000;    load mysql servers to runtime;  load mysql users to runtime;  load mysql variables to runtime;    save mysql servers to disk;  save mysql users to disk;  save mysql variables to disk;

然后，继续在这个管理端口下 开始配置改写规则：

use main;    select * from mysql_query_rules ;    delete from mysql_query_rules ;

### 注意： 这里对于同一个SQL，有3个规则去适配

1、表名带反引号 【根据统计，这种情况的SQL最多】

2、表名不带反引号

3、带库名，表名也带反引号

继续下面操作：

# 写入新的sql改写规则(看上去复杂，实际上就一个规则）  insert into mysql_query_rules  (rule_id,active,apply,log,destination_hostgroup,match_pattern,replace_pattern)  values   (1,1,1,1,100,"^(select.*?from) `t_user` (.*)$","select usr_id,usr_nick,insert(usr_password, 6, 8, 'xxxxxxxx') as usr_password,usr_email,insert(usr_mobile, 4, 4, 'xxxxx') as usr_mobile  from `t_user` 2 ;");    insert into mysql_query_rules  (rule_id,active,apply,log,destination_hostgroup,match_pattern,replace_pattern)  values   (2,1,1,1,100,"^(select.*?from) t_user (.*)$","select usr_id,usr_nick,insert(usr_password, 6, 8, 'xxxxxxxx') as usr_password,usr_email,insert(usr_mobile, 4, 4, 'xxxxx') as usr_mobile  from t_user 2 ;");    insert into mysql_query_rules  (rule_id,active,apply,log,destination_hostgroup,match_pattern,replace_pattern)  values   (3,1,1,1,100,"^(select.*?from) testdb.`t_user` (.*)$","select usr_id,usr_nick,insert(usr_password, 6, 8, 'xxxxxxxx') as usr_password,usr_email,insert(usr_mobile, 4, 4, 'xxxxx') as usr_mobile  from testdb.t_user 2 ;");    # 开启审计日志（pma只允许有查询操作的可能性）【这步设置实际上也可以不要，减少proxysql的日志量】  set mysql-eventslog_filename = '/var/lib/proxysql/audit.log' ;  -- 会生成  audit.log.0000xx这种命名格式的文件  INSERT INTO mysql_query_rules (rule_id, active, match_digest,destination_hostgroup,log,apply) VALUES (4,1,'^select',100,1,0);  INSERT INTO mysql_query_rules (rule_id, active, match_digest,destination_hostgroup,log,apply) VALUES (5,1,'^SELECT',100,1,0);    # 规则载入runtime ，并持久化到存储  LOAD MYSQL QUERY RULES TO RUNTIME;  SAVE MYSQL QUERY RULES TO DISK;    select rule_id,active,match_digest,match_pattern,destination_hostgroup,apply,log  from mysql_query_rules ;  +---------+--------+--------------+--------------------------------------------+-----------------------+-------+-----+  | rule_id | active | match_digest | match_pattern                              | destination_hostgroup | apply | log |  +---------+--------+--------------+--------------------------------------------+-----------------------+-------+-----+  | 1       | 1      | NULL         | ^(select.*?from) `t_user` (.*)$        | 100                   | 1     | 1   |  | 2       | 1      | NULL         | ^(select.*?from) t_user (.*)$          | 100                   | 1     | 1   |  | 3       | 1      | NULL         | ^(select.*?from) testdb.`t_user` (.*)$ | 100                   | 1     | 1   |  | 4       | 1      | ^select      | NULL                                       | 100                   | 0     | 1   |  | 5       | 1      | ^SELECT      | NULL                                       | 100                   | 0     | 1   |  +---------+--------+--------------+--------------------------------------------+-----------------------+-------+-----+  5 rows in set (0.00 sec)

连接 6033  测试规则是否生效

use testdb;  select * from t_user limit 0,25 ;  select * from `t_user` limit 0,25 ;  select * from testdb.`t_user` limit 0,25 ;    然后还可以use到其它库，测试些 select操作， 然后看下是否被记录到审计日志

审计日志的查看方法：

https://www.cnblogs.com/danhuangpai/p/9688075.html  邓总的博客，全是精华~~~

https://github.com/sysown/proxysql/wiki/Query-Logging 官方文档

参考文档：

http://www.cnblogs.com/f-ck-need-u/p/7684762.html

https://www.cnblogs.com/f-ck-need-u/p/9309760.html