未加星标

MySQL Datamasking using ProxySQL part 2

字体大小 | |
[数据库(mysql) 所属分类 数据库(mysql) | 发布者 店小二03 | 时间 2016 | 作者 红领巾 ] 0人收藏点击收藏
Inpart 1, we saw how to mask one column for a specific user.
MySQL Datamasking using ProxySQL   part 2

But if I want to mask other fields in the same table ? Is that feasible ? Easily ?

Yes and yes :wink:

The only necessarily steps are:

disable “apply” for the previous rule add a new rule load & save the rules

In the following example, I use the same table as in the previous article, but this time I want to also mask the last name, I decided to only show the first two character and use a fixed lenght.

ProxySQL> update mysql_query_rules set aaply=0 where rule_id=1;
ProxySQL> INSERT INTO mysql_query_rules (rule_id,active,username,match_pattern,replace_pattern,apply)
VALUES (2,1,'devel','^[sS][eE][lL][eE][cC][tT] (.*)lastname([ ,])(.*)',
"SELECT \1CONCAT(LEFT(lastname,2),REPEAT('X',10)) lastname\2\3",1);
ProxySQL> LOAD MYSQL QUERY RULES TO RUNTIME;

Et voilà, done !

Let’s test it:

mysql1 mysql> select firstname, cc_num, lastname from myapp.CUSTOMERS;
+-----------+------------------+--------------+
| firstname | cc_num | lastname |
+-----------+------------------+--------------+
| Frederic | XXXXXXXXXXXX5289 | DeXXXXXXXXXX |
| Dim0 | XXXXXXXXXXXX5289 | VaXXXXXXXXXX |
| Kenny | XXXXXXXXXXXX4589 | GrXXXXXXXXXX |
+-----------+------------------+--------------+

Wooohoooo it worked ! :wink:

With ProxySQL you can also easily check which rules have been hit and how many times:

ProxySQL> SELECT hits, mysql_query_rules.rule_id, match_digest, substr(match_pattern,1,20) "match", substr(replace_pattern,1,20) "replace", apply FROM mysql_query_rules NATURAL JOIN stats.stats_mysql_query_rules ORDER BY mysql_query_rules.rule_id;
+------+---------+--------------+----------------------+----------------------+-------+
| hits | rule_id | match_digest | match | replace | apply |
+------+---------+--------------+----------------------+----------------------+-------+
| 5 | 1 | NULL | ^[sS][eE][lL][eE][cC | SELECT \1CONCAT(REPE | 0 |
| 4 | 2 | NULL | ^[sS][eE][lL][eE][cC | SELECT \1CONCAT(LEFT | 1 |
| 0 | 90 | NULL | ^SELECT \*.*FROM.*CU | NULL | 0 |
+------+---------+--------------+----------------------+----------------------+-------+

So at the end, datamasking for MySQL is not so complicated.

Note: As Tom Krouper highlighted it to me by chat, it’s a VERY bad idea to store Credit Card numbers in clear into a database. This was just for the theory, but you have been warned !

follow me

Follow @lefred

本文数据库(mysql)相关术语:navicat for mysql mysql workbench mysql数据库 mysql 存储过程 mysql安装图解 mysql教程 mysql 管理工具

主题: SQLMySQLCURYTI
分页:12
转载请注明
本文标题:MySQL Datamasking using ProxySQL part 2
本站链接:http://www.codesec.net/view/480811.html
分享请点击:


1.凡CodeSecTeam转载的文章,均出自其它媒体或其他官网介绍,目的在于传递更多的信息,并不代表本站赞同其观点和其真实性负责;
2.转载的文章仅代表原创作者观点,与本站无关。其原创性以及文中陈述文字和内容未经本站证实,本站对该文以及其中全部或者部分内容、文字的真实性、完整性、及时性,不作出任何保证或承若;
3.如本站转载稿涉及版权等问题,请作者及时联系本站,我们会及时处理。
登录后可拥有收藏文章、关注作者等权限...
技术大类 技术大类 | 数据库(mysql) | 评论(0) | 阅读(27)