我使用Mariadb MaxScale，在MySQL中尝试对个人信息进行数据脱敏

2 年 ago

韵, 科

4 minutes

你为什么要做这种事情？

アプリケーションで対応する方法もあるとは思うけど、運用で度々変更を求められることが想定されたので、設定値変更くらいで変えられる仕組みがよかった

暫時試試看

由于使用的服务器是Amazon Linux 1，按照官方网站的安装步骤进行操作时出现错误，无法将其安装到EC2中。

$ cat /etc/system-release
Amazon Linux AMI release 2018.03

$ curl -sS https://downloads.mariadb.com/MariaDB/mariadb_repo_setup | sudo bash
[error] Detected RHEL or compatible but version () is not supported.
[error] The MariaDB Repository supports these Linux OSs, on x86-64 only:
    * RHEL/CentOS 6, 7, & 8
    * Ubuntu 16.04 LTS (xenial), 18.04 LTS (bionic), & 20.04 LTS (focal)
    * Debian 8 (jessie), 9 (stretch), & 10 (buster)
    * SLES 12 & 15
[error] See https://mariadb.com/kb/en/mariadb/mariadb-package-repository-setup-and-usage/#platform-support

因为似乎有提供Docker镜像，所以我试着使用它。

先决条件是在EC2上安装Docker，获取图像，复制所创建的配置，然后运行Docker并执行SQL，结果成功了。

$ sudo yum update -y
$ sudo yum install -y docker
$ sudo service docker start
Starting cgconfig service:                                 [  OK  ]
Starting Docker:                                           [  OK  ]
$ sudo usermod -a -G docker ec2-user
$ docker pull mariadb/maxscale:latest
$ docker run -d -p 4008:4008 --name mxs -v /etc/maxscale.cnf:/etc/maxscale.cnf -v /etc/maxscale.cnf.d/masking_rules.json:/etc/maxscale.cnf.d/masking_rules.json mariadb/maxscale:latest
$ docker ps -a
CONTAINER ID        IMAGE                     COMMAND                  CREATED             STATUS              PORTS                    NAMES
XXXXXXXXXXXX        mariadb/maxscale:latest   "docker-entrypoint.s…"   7 minutes ago       Up 7 minutes        0.0.0.0:4008->4008/tcp   mxs

maxscale.cnf文件的样子如下所示

# MaxScale documentation on GitHub:
# https://github.com/mariadb-corporation/MaxScale/blob/2.1/Documentation/Documentation-Contents.md
# Global parameters
#
# Complete list of configuration options:
# https://github.com/mariadb-corporation/MaxScale/blob/2.1/Documentation/Getting-Started/Configuration-Guide.md
[maxscale]
threads=auto

# Server definitions
#
# Set the address of the server to the network
# address of a MySQL server.
#
[server1]
type=server
address=XX.XX.XX.XXX
port=3306
protocol=MySQLBackend

# Monitor for the servers
#
# This will keep MaxScale aware of the state of the servers.
# MySQL Monitor documentation:
# https://github.com/mariadb-corporation/MaxScale/blob/2.1/Documentation/Monitors/MySQL-Monitor.md
[MySQL-Monitor]
type=monitor
module=mysqlmon
servers=server1
user=XXXXXXXXX
password=XXXXXXXXX
monitor_interval=10000
failcount=3

[Masking]
type=filter
module=masking
rules=/etc/maxscale.cnf.d/masking_rules.json　　←こいつにマスキング周りのルールを書く形でした
warn_type_mismatch=always
large_payload=ignore

# Service definitions
#
# Service Definition for a read-only service and
# a read/write splitting service.
#
# ReadConnRoute documentation:
# https://github.com/mariadb-corporation/MaxScale/blob/2.1/Documentation/Routers/ReadConnRoute.md
[Read-Only-Service]
type=service
router=readconnroute
servers=server1
user=XXXXXXXXX
password=XXXXXXXXX
router_options=slave
filters=Masking

# This service enables the use of the MaxAdmin interface
# MaxScale administration guide:
# https://github.com/mariadb-corporation/MaxScale/blob/2.1/Documentation/Reference/MaxAdmin.md
[MaxAdmin-Service]
type=service
router=cli
# Listener definitions for the services
#
# These listeners represent the ports the
# services will listen on.
#
[Read-Only-Listener]
type=listener
service=Read-Only-Service
protocol=MySQLClient
port=4008
address=0.0.0.0
[MaxAdmin-Listener]
type=listener
service=MaxAdmin-Service
protocol=maxscaled
socket=default

/etc/maxscale.cnf.d/masking_rules.json中的遮罩规则暂时如下。

{
    "rules": [
        {
            "replace": {
                "database": "sample",
                "table": "user",
                "column": "user_name"
            },
            "with": {
                "fill": "X"
            }
        },
        {
            "replace": {
                "database": "sample",
                "table": "user",
                "column": "mail_address"
            },
            "with": {
                "fill": "@"
            }
        }
    ]
}

真实的结果

直通

$ mysql -u XXXXXXXX -p -h XX.XX.XX.XXX
Enter password: 
mysql> use sample
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed

mysql> select id,user_name,mail_address from user limit 3;
+----+---------------------+-----------------+
| id | user_name           | mail_address    |
+----+---------------------+-----------------+
|  1 | 山田太郎１            | yamada@test.com |
|  2 | スズキ 一太郎         | suzuki@test.com |
|  3 | 佐藤 二郎            | sato@test.com   |
+----+---------------------+-----------------+
3 rows in set (0.00 sec)

通过MaxScale进行连接

$ mysql -u XXXXXXXX -p -h 172.17.0.2 -P 4008
Enter password: 
mysql> use sample
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed

mysql> select id,user_name,mail_address from user limit 3;
+----+---------------------+-----------------+
| id | user_name           | mail_address    |
+----+---------------------+-----------------+
|  1 | XXXXXXXXXXXXXXX     | @@@@@@@@@@@@@@@ |
|  2 | XXXXXXXXXXXXXXXX    | @@@@@@@@@@@@@@@ |
|  3 | XXXXXXXXXXXXXXXXXXX | @@@@@@@@@@@@@@@ |
+----+---------------------+-----------------+
3 rows in set (0.00 sec)

使用后的感想

因为已经能够完全遮蔽，所以下一步可以尝试多个遮蔽规则的操作
如果使用正则表达式对电话号码和地址进行遮蔽，遮蔽本身应该是可行的吧
只要不是“希望部分字符串保持原样”的任性要求的话ｗ

请参考下面的网站。

Mariadb MaxScale的周边

mariadb/maxscale – Docker Hub

其他的遮罩工具

ユーザーローカル個人情報匿名化フィルター
mysqlデータマスキング – mysql – Program QA

掩码表达式的相关部分。

日本の住所かどうか判定する正規表現メモ – Qiita