Slide 1
Slide 1 text
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
Slide 2
Slide 2 text
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
Slide 3
Slide 3 text
Who are we ?
Copyright @ 2017 lefred & ProxySQL. All rights reserved.
3 / 39
Slide 4
Slide 4 text
René Cannaò
@rene_cannao
ProxySQL Founder
Copyright @ 2017 lefred & ProxySQL. All rights reserved.
4 / 39
Slide 5
Slide 5 text
Frédéric Descamps
@lefred
MySQL Evangelist
Managing MySQL since 3.23
devops believer
Copyright @ 2017 lefred & ProxySQL. All rights reserved.
5 / 39
Slide 6
Slide 6 text
What is ProxySQL ?
Copyright @ 2017 lefred & ProxySQL. All rights reserved.
6 / 39
Slide 7
Slide 7 text
What is ProxySQL ?
the MySQL data stargate
Copyright @ 2017 lefred & ProxySQL. All rights reserved.
7 / 39
Slide 8
Slide 8 text
Why using ProxySQL as datamasking solution?
Open Source & Free like in beer
Copyright @ 2017 lefred & ProxySQL. All rights reserved.
8 / 39
Slide 9
Slide 9 text
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
Slide 10
Slide 10 text
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
Slide 11
Slide 11 text
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
Slide 12
Slide 12 text
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
Slide 13
Slide 13 text
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
Slide 14
Slide 14 text
Rules
Avoid SELECT *
Copyright @ 2017 lefred & ProxySQL. All rights reserved.
14 / 39
Slide 15
Slide 15 text
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
Slide 16
Slide 16 text
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
Slide 17
Slide 17 text
Rules (2)
Mask the field
Copyright @ 2017 lefred & ProxySQL. All rights reserved.
17 / 39
Slide 18
Slide 18 text
Rules (2)
Mask the field
when the field is selected in the columns we need:
Copyright @ 2017 lefred & ProxySQL. All rights reserved.
18 / 39
Slide 19
Slide 19 text
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
Slide 20
Slide 20 text
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
Slide 21
Slide 21 text
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
Slide 22
Slide 22 text
Rules Overview
Too mask cc_num from table CUSTOMERS, 7 rules are needed:
Copyright @ 2017 lefred & ProxySQL. All rights reserved.
22 / 39
Slide 23
Slide 23 text
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
Slide 24
Slide 24 text
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
Slide 25
Slide 25 text
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
Slide 26
Slide 26 text
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
Slide 27
Slide 27 text
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
Slide 28
Slide 28 text
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
Slide 29
Slide 29 text
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
Slide 30
Slide 30 text
Limitations
supported in proxySQL >= 1.4.x
Copyright @ 2017 lefred & ProxySQL. All rights reserved.
30 / 39
Slide 31
Slide 31 text
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
Slide 32
Slide 32 text
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
Slide 33
Slide 33 text
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
Slide 34
Slide 34 text
Examples
Easy ones
SELECT * FROM CUSTOMERS;
SELECT rstname, lastname, cc_num FROM CUSTOMERS;
Copyright @ 2017 lefred & ProxySQL. All rights reserved.
34 / 39
Slide 35
Slide 35 text
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
Slide 36
Slide 36 text
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
Slide 37
Slide 37 text
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
Slide 38
Slide 38 text
We need you !
Copyright @ 2017 lefred & ProxySQL. All rights reserved.
38 / 39
Slide 39
Slide 39 text
Thank you !
Questions ?
Copyright @ 2017 lefred & ProxySQL. All rights reserved.
39 / 39