Inexpensive MySQL Datamasking with ProxySQL

Frederic Descamps (Community Manager, Oracle) and Rene Cannao (CEO, ProxySQL) gave this presentation on datamasking best practices in MySQL, MariaDB and Friends at FOSDEM 2017.

Talk abstract is as follows:

During this session we will cover the last development in ProxySQL to support regular expressions and how we can use this strong technique in correlation with the query rules to anonymize live data quickly and transparently.

We will explain the mechanism and how to generate these rules quickly. We show live demo with all challenges we got from the Community and we finish the session by an interactive brainstorm testing queries from the audience.

Transcript

1. Inexpensive Datamasking for MySQL with ProxySQL data anonymization for developers FOSDEM MySQL & Friends Devroom - February 2017 René Cannaò - ProxySQL Founder Frédéric Descamps - MySQL Community Manager - Oracle 1 / 39
2. Safe Harbor Statement The following is intended to outline our general product direction. It is intended for information purpose only, and may not be incorporated into any contract. It is not a commitment to deliver any material, code, or functionality, and should not be relied up in making purchasing decisions. The development, release and timing of any features or functionality described for Oracle's product remains at the sole discretion of Oracle. Copyright @ 2017 lefred & ProxySQL. All rights reserved. 2 / 39
3. Who are we ? Copyright @ 2017 lefred & ProxySQL. All rights reserved. 3 / 39
4. René Cannaò @rene_cannao ProxySQL Founder Copyright @ 2017 lefred & ProxySQL. All rights reserved. 4 / 39
5. Frédéric Descamps @lefred MySQL Evangelist Managing MySQL since 3.23 devops believer Copyright @ 2017 lefred & ProxySQL. All rights reserved. 5 / 39
6. What is ProxySQL ? Copyright @ 2017 lefred & ProxySQL. All rights reserved. 6 / 39
7. What is ProxySQL ? the MySQL data stargate Copyright @ 2017 lefred & ProxySQL. All rights reserved. 7 / 39
8. Why using ProxySQL as datamasking solution? Open Source & Free like in beer Copyright @ 2017 lefred & ProxySQL. All rights reserved. 8 / 39
9. Why using ProxySQL as datamasking solution? Open Source & Free like in beer Other solutions are expensive or not working Copyright @ 2017 lefred & ProxySQL. All rights reserved. 9 / 39
10. Why using ProxySQL as datamasking solution? Open Source & Free like in beer Other solutions are expensive or not working Not worse than the other solutions as currently none is perfect Copyright @ 2017 lefred & ProxySQL. All rights reserved. 10 / 39
11. Why using ProxySQL as datamasking solution? Open Source & Free like in beer Other solutions are expensive or not working Not worse than the other solutions as currently none is perfect the best solution would be to have this feature implemented in the server just after the handler API Copyright @ 2017 lefred & ProxySQL. All rights reserved. 11 / 39
12. The concept We use Regular Expressions to modify the client's SQL statement and replace the column(s) we want to hide by some characters. Only the defined users, in our example, we use a developer will have his statements modified. Copyright @ 2017 lefred & ProxySQL. All rights reserved. 12 / 39
13. Access don't forget to create a user. > insert into mysql_users (username, password, active, default_hostgroup) values ('devel','devel',1,1); Copyright @ 2017 lefred & ProxySQL. All rights reserved. 13 / 39
14. Rules Avoid SELECT * Copyright @ 2017 lefred & ProxySQL. All rights reserved. 14 / 39
15. Rules Avoid SELECT * we need to create some rules to block any SELECT * variant on the table Copyright @ 2017 lefred & ProxySQL. All rights reserved. 15 / 39
16. Rules Avoid SELECT * we need to create some rules to block any SELECT * variant on the table if the column is part of many tables, we need to do so for each of them Copyright @ 2017 lefred & ProxySQL. All rights reserved. 16 / 39
17. Rules (2) Mask the field Copyright @ 2017 lefred & ProxySQL. All rights reserved. 17 / 39
18. Rules (2) Mask the field when the field is selected in the columns we need: Copyright @ 2017 lefred & ProxySQL. All rights reserved. 18 / 39
19. Rules (2) Mask the field when the field is selected in the columns we need: to replace the columnn by showing the first 2 characters and a certain amount of Xs Copyright @ 2017 lefred & ProxySQL. All rights reserved. 19 / 39
20. Rules (2) Mask the field when the field is selected in the columns we need: to replace the columnn by showing the first 2 characters and a certain amount of Xs keep the column name Copyright @ 2017 lefred & ProxySQL. All rights reserved. 20 / 39
21. Rules (2) Mask the field when the field is selected in the columns we need: to replace the columnn by showing the first 2 characters and a certain amount of Xs keep the column name 5275653223285289 will become 52XXXXXXXXXX Copyright @ 2017 lefred & ProxySQL. All rights reserved. 21 / 39
22. Rules Overview Too mask cc_num from table CUSTOMERS, 7 rules are needed: Copyright @ 2017 lefred & ProxySQL. All rights reserved. 22 / 39
23. Rules Overview Too mask cc_num from table CUSTOMERS, 7 rules are needed: rule #1 rule_id: 1 active: 1 username: devel agIN: 0 match_pattern: `*cc_num*` re_modi ers: caseless,global agOUT: NULL replace_pattern: cc_num apply: 0 Copyright @ 2017 lefred & ProxySQL. All rights reserved. 23 / 39
24. rule #2 rule_id: 2 active: 1 username: devel agIN: 0 match_pattern: (\(?)(`?\w+`?\.)?cc_num(\)?)([ ,\n]) re_modi ers: caseless,global agOUT: NULL replace_pattern: \1CONCAT(LEFT(\2cc_num,2),REPEAT('X',10))\3 cc_num\4 apply: 0 Copyright @ 2017 lefred & ProxySQL. All rights reserved. 24 / 39
25. rule #3 rule_id: 3 active: 1 username: devel agIN: 0 match_pattern: \)(\)?) cc_num\s+(\w), re_modi ers: caseless,global agOUT: NULL replace_pattern: )\1 \2, apply: 1 Copyright @ 2017 lefred & ProxySQL. All rights reserved. 25 / 39
26. rule #4 rule_id: 4 active: 1 username: devel agIN: 0 match_pattern: \)(\)?) cc_num\s+(.*)\s+from re_modi ers: caseless,global agOUT: NULL replace_pattern: )\1 \2 from apply: 1 Copyright @ 2017 lefred & ProxySQL. All rights reserved. 26 / 39
27. rule #5 rule_id: 5 active: 1 username: devel match_pattern: ^SELECT\s+\*.*FROM.*CUSTOMERS re_modi ers: caseless,global error_msg: Query not allowed due to sensitive information, please contact dba@myapp.com apply: 0 Copyright @ 2017 lefred & ProxySQL. All rights reserved. 27 / 39
28. rule #6 rule_id: 6 active: 1 username: devel match_pattern: ^SELECT\s+CUSTOMERS\.\*.*FROM.*CUSTOMERS re_modi ers: caseless,global error_msg: Query not allowed due to sensitive information, please contact dba@myapp.com apply: 0 Copyright @ 2017 lefred & ProxySQL. All rights reserved. 28 / 39
29. rule #7 rule_id: 7 active: 1 username: devel match_pattern: ^SELECT\s+(\w+)\.\*.*FROM.*CUSTOMERS\s+(as\s+)?(\1) re_modi ers: caseless,global error_msg: Query not allowed due to sensitive information, please contact dba@myapp.com apply: 0 Copyright @ 2017 lefred & ProxySQL. All rights reserved. 29 / 39
30. Limitations supported in proxySQL >= 1.4.x Copyright @ 2017 lefred & ProxySQL. All rights reserved. 30 / 39
31. Limitations supported in proxySQL >= 1.4.x all fields with the same name will be masked whatever the name of the table is Copyright @ 2017 lefred & ProxySQL. All rights reserved. 31 / 39
32. Limitations supported in proxySQL >= 1.4.x all fields with the same name will be masked whatever the name of the table is the regexps can always be not sufficient Copyright @ 2017 lefred & ProxySQL. All rights reserved. 32 / 39
33. Make it easy This is not really easy isn't it ? You can use this small bash script (https://gist.github.com/lefred/c040fee7e9c60ff3ca80f1590c48572b) to generate them: # ./maskit.sh -c cc_num -t CUSTOMERS column: cc_num table: CUSTOMERS let's add the rules... Copyright @ 2017 lefred & ProxySQL. All rights reserved. 33 / 39
34. Examples Easy ones SELECT * FROM CUSTOMERS; SELECT rstname, lastname, cc_num FROM CUSTOMERS; Copyright @ 2017 lefred & ProxySQL. All rights reserved. 34 / 39
35. Examples (2) More difficult Thank you Thomas Adolph & Dipti Joshi for the suggestions select rstname, CONCAT(cc_num), lastname from myapp.CUSTOMERS; select rstname, cc_num, cc_num from myapp.CUSTOMERS; select rstname, `cc_num` from myapp.CUSTOMERS; select rstname, cc_num from myapp.CUSTOMERS; (*) (*) on two lines Copyright @ 2017 lefred & ProxySQL. All rights reserved. 35 / 39
36. Examples (3) select t1.cc_num from myapp.CUSTOMERS as t1; select rstname, cc_num as fred from CUSTOMERS; select rstname, cc_num fred from CUSTOMERS; select rstname, cc_num `as` from CUSTOMERS; select cc_num as `as`, rstname from CUSTOMERS; select `t1`.`cc_num` from myapp.CUSTOMERS as t1; Copyright @ 2017 lefred & ProxySQL. All rights reserved. 36 / 39
37. Examples (4) select cc_num fred, rstname from CUSTOMERS; select rstname, /* cc_num */, from myapp.CUSTOMERS; /* */ select rstname, cc_num from myapp.CUSTOMERS; select CUSTOMERS.* from myapp.CUSTOMERS; select a.* from myapp.CUSTOMERS a; Copyright @ 2017 lefred & ProxySQL. All rights reserved. 37 / 39
38. We need you ! Copyright @ 2017 lefred & ProxySQL. All rights reserved. 38 / 39
39. Thank you ! Questions ? Copyright @ 2017 lefred & ProxySQL. All rights reserved. 39 / 39