一种基于proxysql的数据脱敏思路
背景：我们这边给研发查数据的是通过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 删除
评论
登录后参与评论
推荐阅读
相关产品与服务
云数据库 SQL Server
腾讯云数据库 SQL Server （TencentDB for SQL Server）是业界最常用的商用数据库之一，对基于 Windows 架构的应用程序具有完美的支持。TencentDB for SQL Server 拥有微软正版授权，可持续为用户提供最新的功能，避免未授权使用软件的风险。具有即开即用、稳定可靠、安全运行、弹性扩缩等特点。