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

保持热爱奔赴山海

发布于 2019-09-17 14:50:38

1.2K0

发布于 2019-09-17 14:50:38

文章被收录于专栏：数据库相关

背景：我们这边给研发查数据的是通过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

本文参与腾讯云自媒体同步曝光计划，分享自作者个人站点/博客。

原始发表：2019/06/22 ，如有侵权请联系 cloudcommunity@tencent.com 删除

https