41から始めました

文字通り41歳から始めたブログです。DB(MySQL)を使ってお仕事してるので、DB周りの話を中心に最近始めたこととかをTwitterのノリで書いています。なお、本サイトにおいて示されている見解は私個人の見解であり、所属団体や組織を代表するものではありません。

Percona Serverのマスキング試してみた

MySQLのマスキングをPerconaで使ってみる

MySQLのマスキングはEnterpriseEditionでしか使えないが、PerconaServerはそれを無償で使えるように!

やるじゃん、ぺるこな!

でも使えるのは8.0.17-18バージョンからなので、ごめんね5.7…。

とりあえず、この記事 をなぞりつつ、MySQLの公式ドキュメントを見ながら試してみた。

マスキング関数

まず、MySQLのマスキングについて公式ドキュメントを読む

  • ビューでマスキング済の結果を返す
  • ビューにマスキング関数を利用する
    • 今のところは関数を見る限りあまり日本向けではない

マスキング関数の種類

マスキング関数の種類以下の3パターンに分かれている。

(ネーミングは公式の英語からこんな感じで勝手につけたので正しくないかも)

1.データマスキング関数(Data Masking Functions=DMF)

  • 文字列引数に対してマスキング操作を実行し、マスクされた結果を返します。

2.ランダムデータ生成関数(Random Data Generation Functions=RDGF)

  • さまざまなタイプのデータに対してランダムな値を生成します。
    • 生成された値には、可能であれば、正当なデータと間違われることを避けるために、デモンストレーションまたはテスト値用に予約された特性があります。

3.ランダムデータディクショナリベース関数(Random Data Dictionary-Based Functions=RDDBF)

  • 用語の辞書を操作し、それらに基づいて生成およびマスキング操作を実行します。

マスキング関数の一覧(MySQL8.0.19時点)

関数名 関数タイプ 説明 構文 引数
mask_inner DMF 文字列の内部部分をマスクし、両端をそのままにして、結果を返します。
オプションのマスキング文字を指定できます。

マスキング文字はシングルバイト文字でなければなりません。
マルチバイト文字を使用しようとすると、エラーが発生します
mask_inner(str, margin1, margin2 [, mask_char]) str:マスクする文字列。

margin1:マスクされないままにする文字列の左端の文字数を指定する負でない整数。値が0の場合、マスクされていない左端の文字はありません。

margin2:マスクされないままにする文字列の右端の文字数を指定する負でない整数。
値が0の場合、右端の文字はマスクされません。

mask_char:(オプション)マスキングに使用する単一の文字。もし mask_charが指定されていない場合 デフォルトは 'X'です。
mask_outer DMF 文字列の左端と右端をマスクし、内部をマスクせずに、結果を返します。
オプションのマスキング文字を指定できます。

マスキング文字はシングルバイト文字でなければなりません。
マルチバイト文字を使用しようとすると、エラーが発生します
mask_outer(str, margin1, margin2 [, mask_char]) str:マスクする文字列。

margin1:マスクする文字列の左端の文字数を指定する非負の整数。
値が0の場合、左端の文字はマスクされません。

margin2:マスクする文字列の右端の文字数を指定する非負の整数。
値が0の場合、右端の文字はマスクされません。

mask_char:(オプション)マスキングに使用する単一の文字。
もし mask_charが指定されていない場合 デフォルトは 'X'です。
mask_pan DMF 支払いカードのプライマリアカウント番号をマスクし、最後の4桁以外をすべて'X'文字で置き換えた番号を返します 。 mask_pan(str) str:マスクする文字列。
文字列はプライマリアカウント番号に適した長さでなければなりませんが、それ以外の場合はチェックされません。
mask_pan_relaxed DMF 支払いカードのプライマリアカウント番号をマスクし、最初の6桁と最後の4桁を除くすべての'X'文字を文字に置き換えた番号を返します。
最初の6桁は、支払いカード発行者を示します。
mask_pan_relaxed(str) str:マスクする文字列。文字列は、プライマリアカウント番号に適した長さでなければなりませんが、それ以外の場合はチェックされません。
mask_ssn DMF 米国の社会保障番号をマスクし、最後の4桁以外のすべてを'X'文字に置き換えた番号を返します 。 mask_ssn(str)
gen_range RDGF 指定された範囲から選択された乱数を生成します。 gen_range(lower, upper) lower:範囲の下限を指定する整数。

upper:範囲の上限を指定する整数。下限より小さくてはいけません。
gen_rnd_email RDGF example.comドメイン 内のランダムな電子メールアドレスを生成します。 gen_rnd_email() なし
gen_rnd_pan RDGF ランダムな支払いカードのプライマリアカウント番号を生成します。
(※割り当てられない可能性が0じゃないので公開向きではないと公式には記載されている)
gen_rnd_pan(size) size:(オプション)結果のサイズを指定する整数。指定しない場合デフォルトは16です。指定する場合12〜19の範囲の整数である必要があります。
gen_rnd_ssn RDGF ランダムな米国社会保障番号をAAA-BB-CCCCAAABB形式で生成します。 gen_rnd_ssn() なし
gen_rnd_us_phone RDGF ランダムな米国の電話番号を1-555-AAA-BBBB形式で生成します。 gen_rnd_us_phone() なし
gen_blacklist RDDBF 1つの辞書に存在する用語を2番目の辞書の用語に置き換え、置換する用語を返します。
これにより、置換によって元の用語がマスクされます
gen_blacklist(str, dictionary_name, replacement_dictionary_name) str:置換する用語を示す文字列。

dictionary_name:置換する用語を含む辞書に名前を付ける文字列。

replacement_dictionary_name:置換語を選択する辞書を指定する文字列。
gen_dictionary RDDBF 辞書からランダムな用語を返します。 gen_dictionary(dictionary_name) dictionary_name:用語を選択する辞書を指定する文字列。
gen_dictionary_drop RDDBF 辞書を辞書レジストリから削除します。 gen_dictionary_drop(dictionary_name) dictionary_name:辞書レジストリから削除する辞書に名前を付ける文字列。
gen_dictionary_load RDDBF ファイルを辞書レジストリにロードし、辞書名の引数を必要とする他の関数で使用される名前を辞書に割り当てます。
この機能にはSUPER特権が必要です。
辞書をリロードするには、まずgen_dictionary_drop()で辞書をドロップし 、次にgen_dictionary_load()で再度ロードします。
gen_dictionary_load(dictionary_path, dictionary_name) dictionary_path:辞書ファイルのパス名を指定する文字列。

dictionary_name:辞書の名前を提供する文字列。

環境構築

では、遊んで見るために環境を構築してみるか!

Percona8 インストール in AWS EC2

Installing Percona Server for MySQL on Red Hat Enterprise Linux and CentOS参照

今回は作業時のPercona Serverの最新Verである8.0.18-9 を入れました。

[ec2-user@ip-xxx ~]$ sudo yum install https://repo.percona.com/yum/percona-release-latest.noarch.rpm
読み込んだプラグイン:extras_suggestions, langpacks, priorities, update-motd
percona-release-latest.noarch.rpm                                                                                                                                                    |  17 kB  00:00:00     
/var/tmp/yum-root-HSvVL4/percona-release-latest.noarch.rpm を調べています: percona-release-1.0-13.noarch
/var/tmp/yum-root-HSvVL4/percona-release-latest.noarch.rpm をインストール済みとして設定しています
依存性の解決をしています
--> トランザクションの確認を実行しています。
---> パッケージ percona-release.noarch 0:1.0-13 を インストール
--> 依存性解決を終了しました。
amzn2-core/2/x86_64                                                                                                                                                                  | 2.4 kB  00:00:00     
amzn2extra-docker/2/x86_64                                                                                                                                                           | 1.3 kB  00:00:00     

依存性を解決しました

============================================================================================================================================================================================================
 Package                                           アーキテクチャー                         バージョン                               リポジトリー                                                      容量
============================================================================================================================================================================================================
インストール中:
 percona-release                                   noarch                                   1.0-13                                   /percona-release-latest.noarch                                    20 k

トランザクションの要約
============================================================================================================================================================================================================
インストール  1 パッケージ

合計容量: 20 k
インストール容量: 20 k
Is this ok [y/d/N]: y
Downloading packages:
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
  インストール中          : percona-release-1.0-13.noarch                                                                                                                                               1/1 
* Enabling the Percona Original repository
<*> All done!
The percona-release package now contains a percona-release script that can enable additional repositories for our newer products.

For example, to enable the Percona Server 8.0 repository use:

  percona-release setup ps80

Note: To avoid conflicts with older product versions, the percona-release setup command may disable our original repository for some products.

For more information, please visit:
  https://www.percona.com/doc/percona-repo-config/percona-release.html

  検証中                  : percona-release-1.0-13.noarch                                                                                                                                               1/1 

インストール:
  percona-release.noarch 0:1.0-13                                                                                                                                                                           

完了しました!
[ec2-user@ip-xxx ~]$ sudo percona-release setup ps80
* Disabling all Percona Repositories
* Enabling the Percona Server 8.0 repository
* Enabling the Percona Tools repository
<*> All done!
[ec2-user@ip-xxx ~]$  sudo yum install percona-server-server
読み込んだプラグイン:extras_suggestions, langpacks, priorities, update-motd
ps-80-release-noarch                                                                                                                                                                 | 2.9 kB  00:00:00     
ps-80-release-x86_64                                                                                                                                                                 | 2.9 kB  00:00:00     
tools-release-noarch                                                                                                                                                                 | 2.9 kB  00:00:00     
tools-release-x86_64                                                                                                                                                                 | 2.9 kB  00:00:00     
(1/4): tools-release-noarch/2/primary_db                                                                                                                                             | 1.1 kB  00:00:00     
(2/4): ps-80-release-noarch/2/primary_db                                                                                                                                             | 1.1 kB  00:00:00     
(3/4): ps-80-release-x86_64/2/primary_db                                                                                                                                             |  63 kB  00:00:00     
(4/4): tools-release-x86_64/2/primary_db                                                                                                                                             |  54 kB  00:00:00     
依存性の解決をしています
--> トランザクションの確認を実行しています。
---> パッケージ percona-server-server.x86_64 0:8.0.18-9.1.el7 を インストール
--> 依存性の処理をしています: percona-server-shared のパッケージ: percona-server-server-8.0.18-9.1.el7.x86_64
--> 依存性の処理をしています: percona-server-client のパッケージ: percona-server-server-8.0.18-9.1.el7.x86_64
--> トランザクションの確認を実行しています。
---> パッケージ percona-server-client.x86_64 0:8.0.18-9.1.el7 を インストール
--> 依存性の処理をしています: libncurses.so.5()(64bit) のパッケージ: percona-server-client-8.0.18-9.1.el7.x86_64
--> 依存性の処理をしています: libtinfo.so.5()(64bit) のパッケージ: percona-server-client-8.0.18-9.1.el7.x86_64
---> パッケージ percona-server-shared.x86_64 0:8.0.18-9.1.el7 を インストール
--> 依存性の処理をしています: percona-server-shared-compat のパッケージ: percona-server-shared-8.0.18-9.1.el7.x86_64
--> トランザクションの確認を実行しています。
---> パッケージ mariadb-libs.x86_64 1:5.5.64-1.amzn2 を 不要
---> パッケージ ncurses-compat-libs.x86_64 0:6.0-8.20170212.amzn2.1.3 を インストール
---> パッケージ percona-server-shared-compat.x86_64 0:8.0.18-9.1.el7 を 非推奨
--> 依存性解決を終了しました。

依存性を解決しました

============================================================================================================================================================================================================
 Package                                                  アーキテクチャー                   バージョン                                              リポジトリー                                      容量
============================================================================================================================================================================================================
インストール中:
 percona-server-server                                    x86_64                             8.0.18-9.1.el7                                          ps-80-release-x86_64                              53 M
 percona-server-shared-compat                             x86_64                             8.0.18-9.1.el7                                          ps-80-release-x86_64                             1.2 M
     mariadb-libs.x86_64 1:5.5.64-1.amzn2 を入れ替えます
依存性関連でのインストールをします:
 ncurses-compat-libs                                      x86_64                             6.0-8.20170212.amzn2.1.3                                amzn2-core                                       308 k
 percona-server-client                                    x86_64                             8.0.18-9.1.el7                                          ps-80-release-x86_64                              12 M
 percona-server-shared                                    x86_64                             8.0.18-9.1.el7                                          ps-80-release-x86_64                             1.3 M

トランザクションの要約
============================================================================================================================================================================================================
インストール  2 パッケージ (+3 個の依存関係のパッケージ)

総ダウンロード容量: 68 M
Is this ok [y/d/N]: y
Downloading packages:
(1/5): ncurses-compat-libs-6.0-8.20170212.amzn2.1.3.x86_64.rpm                                                                                                                       | 308 kB  00:00:00     
warning: /var/cache/yum/x86_64/2/ps-80-release-x86_64/packages/percona-server-client-8.0.18-9.1.el7.x86_64.rpm: Header V4 RSA/SHA256 Signature, key ID 8507efa5: NOKEY    ] 3.6 MB/s |  24 MB  00:00:12 ETA 
percona-server-client-8.0.18-9.1.el7.x86_64.rpm の公開鍵がインストールされていません
(2/5): percona-server-client-8.0.18-9.1.el7.x86_64.rpm                                                                                                                               |  12 MB  00:00:04     
(3/5): percona-server-shared-8.0.18-9.1.el7.x86_64.rpm                                                                                                                               | 1.3 MB  00:00:00     
(4/5): percona-server-shared-compat-8.0.18-9.1.el7.x86_64.rpm                                                                                                                        | 1.2 MB  00:00:01     
(5/5): percona-server-server-8.0.18-9.1.el7.x86_64.rpm                                                                                                                               |  53 MB  00:00:18     
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
合計                                                                                                                                                                        3.7 MB/s |  68 MB  00:00:18     
file:///etc/pki/rpm-gpg/PERCONA-PACKAGING-KEY から鍵を取得中です。
Importing GPG key 0x8507EFA5:
 Userid     : "Percona MySQL Development Team (Packaging key) <mysql-dev@percona.com>"
 Fingerprint: 4d1b b29d 63d9 8e42 2b21 13b1 9334 a25f 8507 efa5
 Package    : percona-release-1.0-13.noarch (installed)
 From       : /etc/pki/rpm-gpg/PERCONA-PACKAGING-KEY
上記の処理を行います。よろしいでしょうか? [y/N]y
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
  インストール中          : percona-server-shared-compat-8.0.18-9.1.el7.x86_64                                                                                                                          1/6 
  インストール中          : percona-server-shared-8.0.18-9.1.el7.x86_64                                                                                                                                 2/6 
  インストール中          : ncurses-compat-libs-6.0-8.20170212.amzn2.1.3.x86_64                                                                                                                         3/6 
  インストール中          : percona-server-client-8.0.18-9.1.el7.x86_64                                                                                                                                 4/6 
  インストール中          : percona-server-server-8.0.18-9.1.el7.x86_64                                                                                                                                 5/6 
Percona Server is distributed with several useful UDF (User Defined Function) from Percona Toolkit.
Run the following commands to create these functions:
mysql -e "CREATE FUNCTION fnv1a_64 RETURNS INTEGER SONAME 'libfnv1a_udf.so'"
mysql -e "CREATE FUNCTION fnv_64 RETURNS INTEGER SONAME 'libfnv_udf.so'"
mysql -e "CREATE FUNCTION murmur_hash RETURNS INTEGER SONAME 'libmurmur_udf.so'"
See http://www.percona.com/doc/percona-server/8.0/management/udf_percona_toolkit.html for more details
  削除中                  : 1:mariadb-libs-5.5.64-1.amzn2.x86_64                                                                                                                                        6/6 
  検証中                  : percona-server-client-8.0.18-9.1.el7.x86_64                                                                                                                                 1/6 
  検証中                  : ncurses-compat-libs-6.0-8.20170212.amzn2.1.3.x86_64                                                                                                                         2/6 
  検証中                  : percona-server-server-8.0.18-9.1.el7.x86_64                                                                                                                                 3/6 
  検証中                  : percona-server-shared-8.0.18-9.1.el7.x86_64                                                                                                                                 4/6 
  検証中                  : percona-server-shared-compat-8.0.18-9.1.el7.x86_64                                                                                                                          5/6 
  検証中                  : 1:mariadb-libs-5.5.64-1.amzn2.x86_64                                                                                                                                        6/6 

インストール:
  percona-server-server.x86_64 0:8.0.18-9.1.el7                                                     percona-server-shared-compat.x86_64 0:8.0.18-9.1.el7                                                    

依存性関連をインストールしました:
  ncurses-compat-libs.x86_64 0:6.0-8.20170212.amzn2.1.3                    percona-server-client.x86_64 0:8.0.18-9.1.el7                    percona-server-shared.x86_64 0:8.0.18-9.1.el7                   

置換:
  mariadb-libs.x86_64 1:5.5.64-1.amzn2                                                                                                                                                                      

完了しました!

Percona Server起動

[ec2-user@ip-xxx ~]$ sudo service mysql start
Redirecting to /bin/systemctl start mysql.service
[ec2-user@ip-xxx ~]$ sudo service mysql status
Redirecting to /bin/systemctl status mysql.service
● mysqld.service - MySQL Server
   Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)
   Active: active (running) since 水 2020-01-15 02:26:06 UTC; 4s ago
     Docs: man:mysqld(8)
           http://dev.mysql.com/doc/refman/en/using-systemd.html
  Process: 3294 ExecStartPre=/usr/bin/mysqld_pre_systemd (code=exited, status=0/SUCCESS)
 Main PID: 3376 (mysqld)
   Status: "Server is operational"
   CGroup: /system.slice/mysqld.service
           └─3376 /usr/sbin/mysqld

 1月 15 02:26:00 ip-xxx.ap-northeast-1.compute.internal systemd[1]: Starting MySQL Server...
 1月 15 02:26:06 ip-xxx.ap-northeast-1.compute.internal systemd[1]: Started MySQL Server.

Percona Server ログイン

[ec2-user@ip-xxx ~]$ sudo cat /var/log/mysqld.log |grep generated
2020-01-15T02:26:03.644352Z 5 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: xxxxxx
[ec2-user@ip-xxx ~]$ mysql -uroot -pxxxxxx
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.18-9

Copyright (c) 2009-2019 Percona LLC and/or its affiliates
Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> 

root ユーザのパスワード変更

ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.

が出るのでパスワードを変更する

mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'passxxxxxx';
Query OK, 0 rows affected (0.00 sec)

マスキング設定

mysql>  INSTALL PLUGIN data_masking SONAME 'data_masking.so';
Query OK, 0 rows affected (0.00 sec)

辞書登録

gen_dictionaryで行う。

辞書と呼ばれるものにマスキング用文字列登録します。(再起動すると読み直しが必要)

というわけで、お試しファイルを落としてくる

[ec2-user@ip-xxx ~]$ wget https://raw.githubusercontent.com/philipperemy/name-dataset/master/names_dataset/first_names.all.txt
--2020-01-15 02:50:00--  https://raw.githubusercontent.com/philipperemy/name-dataset/master/names_dataset/first_names.all.txt
raw.githubusercontent.com (raw.githubusercontent.com) をDNSに問いあわせています... 151.101.108.133
raw.githubusercontent.com (raw.githubusercontent.com)|151.101.108.133|:443 に接続しています... 接続しました。
HTTP による接続要求を送信しました、応答を待っています... 200 OK
長さ: 1289773 (1.2M) [text/plain]
`first_names.all.txt' に保存中

100%[==================================================================================================================================================================>] 1,289,773   --.-K/s 時間 0.03s   

2020-01-15 02:50:01 (39.1 MB/s) - `first_names.all.txt' へ保存完了 [1289773/1289773]
[ec2-user@ip-xxx ~]$ wget https://raw.githubusercontent.com/philipperemy/name-dataset/master/names_dataset/last_names.all.txt
--2020-01-15 02:50:45--  https://raw.githubusercontent.com/philipperemy/name-dataset/master/names_dataset/last_names.all.txt
raw.githubusercontent.com (raw.githubusercontent.com) をDNSに問いあわせています... 151.101.108.133
raw.githubusercontent.com (raw.githubusercontent.com)|151.101.108.133|:443 に接続しています... 接続しました。
HTTP による接続要求を送信しました、応答を待っています... 200 OK
長さ: 786535 (768K) [text/plain]
`last_names.all.txt' に保存中

100%[==================================================================================================================================================================>] 786,535     --.-K/s 時間 0.04s   

2020-01-15 02:50:46 (18.1 MB/s) - `last_names.all.txt' へ保存完了 [786535/786535]

[ec2-user@ip-xxx ~]$ ll
合計 2032
-rw-rw-r-- 1 ec2-user ec2-user 1289773  1月 15 02:50 first_names.all.txt
-rw-rw-r-- 1 ec2-user ec2-user  786535  1月 15 02:50 last_names.all.txt
[ec2-user@ip-xxx ~]$ mv *.txt /tmp/
[ec2-user@ip-xxx ~]$ mysql -uroot -ppassxxxxxx
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 12
Server version: 8.0.18-9 Percona Server (GPL), Release 9, Revision 53e606f

Copyright (c) 2009-2019 Percona LLC and/or its affiliates
Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> select gen_dictionary_load('/tmp/first_names.all.txt', 'first_names');
+----------------------------------------------------------------+
| gen_dictionary_load('/tmp/first_names.all.txt', 'first_names') |
+----------------------------------------------------------------+
| Dictionary load success                                        |
+----------------------------------------------------------------+
1 row in set (0.03 sec)

mysql> select gen_dictionary_load('/tmp/last_names.all.txt', 'last_names');
+--------------------------------------------------------------+
| gen_dictionary_load('/tmp/last_names.all.txt', 'last_names') |
+--------------------------------------------------------------+
| Dictionary load success                                      |
+--------------------------------------------------------------+
1 row in set (0.01 sec)

テストテーブルの用意

[ec2-user@ip-xxx ~]$ wget https://github.com/datacharmer/test_db/archive/master.zip
--2020-01-15 03:00:43--  https://github.com/datacharmer/test_db/archive/master.zip
github.com (github.com) をDNSに問いあわせています... 52.69.186.44
github.com (github.com)|52.69.186.44|:443 に接続しています... 接続しました。
HTTP による接続要求を送信しました、応答を待っています... 302 Found
場所: https://codeload.github.com/datacharmer/test_db/zip/master [続く]
--2020-01-15 03:00:44--  https://codeload.github.com/datacharmer/test_db/zip/master
codeload.github.com (codeload.github.com) をDNSに問いあわせています... 52.68.31.213
codeload.github.com (codeload.github.com)|52.68.31.213|:443 に接続しています... 接続しました。
HTTP による接続要求を送信しました、応答を待っています... 200 OK
長さ: 特定できません [application/zip]
`master.zip' に保存中

    [          <=>                                                                                                                                                      ] 36,687,757  3.15MB/s 時間 11s    

2020-01-15 03:00:55 (3.15 MB/s) - `master.zip' へ保存終了 [36687757]

[ec2-user@ip-xxx ~]$ ll
合計 35828
-rw-rw-r-- 1 ec2-user ec2-user 36687757  1月 15 03:00 master.zip
[ec2-user@ip-xxx ~]$ unzip master.zip
Archive:  master.zip
0b66c2338736779e3b150c7d125b1012d95a961f
   creating: test_db-master/
  inflating: test_db-master/Changelog  
  inflating: test_db-master/README.md  
  inflating: test_db-master/employees.sql  
  inflating: test_db-master/employees_partitioned.sql  
  inflating: test_db-master/employees_partitioned_5.1.sql  
   creating: test_db-master/images/
  inflating: test_db-master/images/employees.gif  
  inflating: test_db-master/images/employees.jpg  
  inflating: test_db-master/images/employees.png  
  inflating: test_db-master/load_departments.dump  
  inflating: test_db-master/load_dept_emp.dump  
  inflating: test_db-master/load_dept_manager.dump  
  inflating: test_db-master/load_employees.dump  
  inflating: test_db-master/load_salaries1.dump  
  inflating: test_db-master/load_salaries2.dump  
  inflating: test_db-master/load_salaries3.dump  
  inflating: test_db-master/load_titles.dump  
  inflating: test_db-master/objects.sql  
   creating: test_db-master/sakila/
  inflating: test_db-master/sakila/README.md  
  inflating: test_db-master/sakila/sakila-mv-data.sql  
  inflating: test_db-master/sakila/sakila-mv-schema.sql  
  inflating: test_db-master/show_elapsed.sql  
  inflating: test_db-master/sql_test.sh  
  inflating: test_db-master/test_employees_md5.sql  
  inflating: test_db-master/test_employees_sha.sql  
[ec2-user@ip-xxx ~]$ ll
合計 35832
-rw-rw-r-- 1 ec2-user ec2-user 36687757  1月 15 03:00 master.zip
drwxrwxr-x 4 ec2-user ec2-user     4096  4月  9  2019 test_db-master
[ec2-user@ip-xxx ~]$ cd test_db-master/
[ec2-user@ip-xxx test_db-master]$ ll
合計 168336
-rw-rw-r-- 1 ec2-user ec2-user      964  4月  9  2019 Changelog
-rw-rw-r-- 1 ec2-user ec2-user     4325  4月  9  2019 README.md
-rw-rw-r-- 1 ec2-user ec2-user     4193  4月  9  2019 employees.sql
-rw-rw-r-- 1 ec2-user ec2-user     6276  4月  9  2019 employees_partitioned.sql
-rw-rw-r-- 1 ec2-user ec2-user     7948  4月  9  2019 employees_partitioned_5.1.sql
drwxrwxr-x 2 ec2-user ec2-user       69  4月  9  2019 images
-rw-rw-r-- 1 ec2-user ec2-user      250  4月  9  2019 load_departments.dump
-rw-rw-r-- 1 ec2-user ec2-user 14159880  4月  9  2019 load_dept_emp.dump
-rw-rw-r-- 1 ec2-user ec2-user     1090  4月  9  2019 load_dept_manager.dump
-rw-rw-r-- 1 ec2-user ec2-user 17722832  4月  9  2019 load_employees.dump
-rw-rw-r-- 1 ec2-user ec2-user 39806034  4月  9  2019 load_salaries1.dump
-rw-rw-r-- 1 ec2-user ec2-user 39805981  4月  9  2019 load_salaries2.dump
-rw-rw-r-- 1 ec2-user ec2-user 39080916  4月  9  2019 load_salaries3.dump
-rw-rw-r-- 1 ec2-user ec2-user 21708736  4月  9  2019 load_titles.dump
-rw-rw-r-- 1 ec2-user ec2-user     4568  4月  9  2019 objects.sql
drwxrwxr-x 2 ec2-user ec2-user       77  4月  9  2019 sakila
-rw-rw-r-- 1 ec2-user ec2-user      272  4月  9  2019 show_elapsed.sql
-rwxr-xr-x 1 ec2-user ec2-user     1800  4月  9  2019 sql_test.sh
-rw-rw-r-- 1 ec2-user ec2-user     4878  4月  9  2019 test_employees_md5.sql
-rw-rw-r-- 1 ec2-user ec2-user     4882  4月  9  2019 test_employees_sha.sql
[ec2-user@ip-xxx test_db-master]$ view employees.sql
[ec2-user@ip-xxx test_db-master]$ mysql -uroot -ppassxxxxxx < employees.sql
mysql: [Warning] Using a password on the command line interface can be insecure.
INFO
CREATING DATABASE STRUCTURE
INFO
storage engine: InnoDB
INFO
LOADING departments
INFO
LOADING employees
INFO
LOADING dept_emp
INFO
LOADING dept_manager
INFO
LOADING titles
INFO
LOADING salaries
data_load_time_diff
00:00:51
[ec2-user@ip-xxx test_db-master]$ mysql -uroot -ppassxxxxxx
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 14
Server version: 8.0.18-9 Percona Server (GPL), Release 9, Revision 53e606f

Copyright (c) 2009-2019 Percona LLC and/or its affiliates
Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| employees          |
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

mysql> use employees;
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> show tables;
+----------------------+
| Tables_in_employees  |
+----------------------+
| current_dept_emp     |
| departments          |
| dept_emp             |
| dept_emp_latest_date |
| dept_manager         |
| employees            |
| salaries             |
| titles               |
+----------------------+
8 rows in set (0.00 sec)

mysql>  show columns from employees;
+------------+---------------+------+-----+---------+-------+
| Field      | Type          | Null | Key | Default | Extra |
+------------+---------------+------+-----+---------+-------+
| emp_no     | int(11)       | NO   | PRI | NULL    |       |
| birth_date | date          | NO   |     | NULL    |       |
| first_name | varchar(14)   | NO   |     | NULL    |       |
| last_name  | varchar(16)   | NO   |     | NULL    |       |
| gender     | enum('M','F') | NO   |     | NULL    |       |
| hire_date  | date          | NO   |     | NULL    |       |
+------------+---------------+------+-----+---------+-------+
6 rows in set (0.01 sec)

マスキングビュー作成(GEN_RANGE,GEN_DICTIONARY)

mysql> CREATE VIEW deidentified_employees
    -> AS
    -> SELECT
    ->   gen_range(900000000, 999999999) as emp_no,
    ->   makedate(year(birth_date), 1) as birth_date,
    ->   gen_dictionary('first_names') as first_name,
    ->   gen_dictionary('last_names') as last_name,
    ->   gender,
    ->   makedate(year(hire_date), 1) as hire_date
    -> FROM employees;
ERROR 1305 (42000): FUNCTION employees.  gen_range does not exist

どうやらPerconaの記事のそれをそのまま使うとエラーになるみたい。(どっかに変な文字コードでも入ってるのかな?)

書き換える

mysql> CREATE VIEW deidentified_employees
    -> AS
    -> SELECT 
    ->     GEN_RANGE(900000000, 999999999) AS emp_no,
    ->     MAKEDATE(YEAR(birth_date), 1) AS birth_date,
    ->     GEN_DICTIONARY('first_names') AS first_name,
    ->     GEN_DICTIONARY('last_names') AS last_name,
    ->     gender,
    ->     MAKEDATE(YEAR(hire_date), 1) AS hire_date
    -> FROM
    ->     employees;
Query OK, 0 rows affected (0.01 sec)

マスキング結果確認

ここでマスキング関数でマスキングされるのはemp_no,first_name,last_namebirth_datehire_dateはMAKEDATE関数で置換表示してます。

元テーブル

mysql> SELECT * FROM employees LIMIT 10;
+--------+------------+------------+-----------+--------+------------+
| emp_no | birth_date | first_name | last_name | gender | hire_date  |
+--------+------------+------------+-----------+--------+------------+
|  10001 | 1953-09-02 | Georgi     | Facello   | M      | 1986-06-26 |
|  10002 | 1964-06-02 | Bezalel    | Simmel    | F      | 1985-11-21 |
|  10003 | 1959-12-03 | Parto      | Bamford   | M      | 1986-08-28 |
|  10004 | 1954-05-01 | Chirstian  | Koblick   | M      | 1986-12-01 |
|  10005 | 1955-01-21 | Kyoichi    | Maliniak  | M      | 1989-09-12 |
|  10006 | 1953-04-20 | Anneke     | Preusig   | F      | 1989-06-02 |
|  10007 | 1957-05-23 | Tzvetan    | Zielinski | F      | 1989-02-10 |
|  10008 | 1958-02-19 | Saniya     | Kalloufi  | M      | 1994-09-15 |
|  10009 | 1952-04-19 | Sumant     | Peac      | F      | 1985-02-18 |
|  10010 | 1963-06-01 | Duangkaew  | Piveteau  | F      | 1989-08-24 |
+--------+------------+------------+-----------+--------+------------+
10 rows in set (0.00 sec)

マスキングビュー

mysql> SELECT * FROM deidentified_employees LIMIT 10;
+-----------+------------+------------+-------------+--------+------------+
| emp_no    | birth_date | first_name | last_name   | gender | hire_date  |
+-----------+------------+------------+-------------+--------+------------+
| 912474868 | 1953-01-01 | sumio      | greff       | M      | 1986-01-01 |
| 998170886 | 1964-01-01 | haralambie | rauhuff     | F      | 1985-01-01 |
| 958103369 | 1959-01-01 | filippina  | angilletta  | M      | 1986-01-01 |
| 967871226 | 1954-01-01 | margarine  | munoz       | M      | 1986-01-01 |
| 907286604 | 1955-01-01 | ajahnay    | agel        | M      | 1989-01-01 |
| 914673574 | 1953-01-01 | kalibo     | manganiello | F      | 1989-01-01 |
| 906652311 | 1957-01-01 | shantai    | mitchael    | F      | 1989-01-01 |
| 998921306 | 1958-01-01 | rachela    | selbe       | M      | 1994-01-01 |
| 918607185 | 1952-01-01 | bradlay    | ehlke       | F      | 1985-01-01 |
| 904967645 | 1963-01-01 | shadiyah   | partyka     | F      | 1989-01-01 |
+-----------+------------+------------+-------------+--------+------------+
10 rows in set (0.00 sec)

マスキングビュー作成(MASK_INNER/MASK_OUTER)

文字列の中もしくは外側をマスキングする関数ですね

mysql> CREATE VIEW deidentified_salaries
    -> AS
    -> SELECT
    -> gen_range(900000000, 999999999) as emp_no,
    -> gen_range(40000, 80000) as salary,
    -> mask_inner(date_format(from_date, '%Y-%m-%d'), 4, 0) as from_date,
    -> mask_outer(date_format(to_date, '%Y-%m-%d'), 4, 2, '0') as to_date
    -> FROM salaries;
ERROR 1123 (HY000): Can't initialize function 'mask_outer'; Wrong argument type: MASK_OUTER(string, int, int, [char])

えー、こっちもそのままだと動かん…。(4つ目の引数を外してみる)

mysql> CREATE VIEW deidentified_salaries AS
    ->     SELECT 
    ->         GEN_RANGE(900000000, 999999999) AS emp_no,
    ->         GEN_RANGE(40000, 80000) AS salary,
    ->         MASK_INNER(DATE_FORMAT(from_date,'%Y-%m-%d'),4,0) AS from_date,
    ->         MASK_OUTER(DATE_FORMAT(to_date,  '%Y-%m-%d'),4,2) AS to_date
    ->     FROM
    ->         salaries;
Query OK, 0 rows affected (0.01 sec)

通った。

マスキング結果確認

from_dateでは前から4文字を除いてマスキング、to_dateでは前から4文字目の次の文字から2文字(-は含まない模様)を除きマスキング

元テーブル

mysql> SELECT * FROM salaries LIMIT 10;
+--------+--------+------------+------------+
| emp_no | salary | from_date  | to_date    |
+--------+--------+------------+------------+
|  10001 |  60117 | 1986-06-26 | 1987-06-26 |
|  10001 |  62102 | 1987-06-26 | 1988-06-25 |
|  10001 |  66074 | 1988-06-25 | 1989-06-25 |
|  10001 |  66596 | 1989-06-25 | 1990-06-25 |
|  10001 |  66961 | 1990-06-25 | 1991-06-25 |
|  10001 |  71046 | 1991-06-25 | 1992-06-24 |
|  10001 |  74333 | 1992-06-24 | 1993-06-24 |
|  10001 |  75286 | 1993-06-24 | 1994-06-24 |
|  10001 |  75994 | 1994-06-24 | 1995-06-24 |
|  10001 |  76884 | 1995-06-24 | 1996-06-23 |
+--------+--------+------------+------------+
10 rows in set (0.00 sec)

マスキングビュー

mysql> SELECT * FROM deidentified_salaries LIMIT 10;
+-----------+--------+------------+------------+
| emp_no    | salary | from_date  | to_date    |
+-----------+--------+------------+------------+
| 950823834 | 70949  | 1986XXXXXX | XXXX-06-XX |
| 987110465 | 62877  | 1987XXXXXX | XXXX-06-XX |
| 964944005 | 43451  | 1988XXXXXX | XXXX-06-XX |
| 990093942 | 65625  | 1989XXXXXX | XXXX-06-XX |
| 997508813 | 53442  | 1990XXXXXX | XXXX-06-XX |
| 935495362 | 65082  | 1991XXXXXX | XXXX-06-XX |
| 991618027 | 65280  | 1992XXXXXX | XXXX-06-XX |
| 961487920 | 60056  | 1993XXXXXX | XXXX-06-XX |
| 947195247 | 45183  | 1994XXXXXX | XXXX-06-XX |
| 976597378 | 62853  | 1995XXXXXX | XXXX-06-XX |
+-----------+--------+------------+------------+
10 rows in set (0.00 sec)

ちなみに

  • MySQL 8.0.19の時点で戻り値がlatin1になっている(それ以前はbinary)
    • 確認方法は SELECT CHARSET(gen_rnd_email());
    • 文字セットを特定のものにしたい場合はCONVERT関数を使う(CONVERT(gen_rnd_email() USING utf8mb4);

バグもしくはMySQL EEとは違ってるところ

  • mask_outer(a*****,a#####ってなるんじゃないの?)
mysql> SELECT mask_outer('abcdef', 0, 1, '*'), mask_outer('abcdef',0, 1, "#");
+---------------------------------+--------------------------------+
| mask_outer('abcdef', 0, 1, '*') | mask_outer('abcdef',0, 1, "#") |
+---------------------------------+--------------------------------+
| abcde*                          | abcde#                         |
+---------------------------------+--------------------------------+
1 row in set (0.00 sec)

mysql> SELECT mask_outer('abcdef', 0, 2, '*'), mask_outer('abcdef',0, 2, "#");
+---------------------------------+--------------------------------+
| mask_outer('abcdef', 0, 2, '*') | mask_outer('abcdef',0, 2, "#") |
+---------------------------------+--------------------------------+
| abcd**                          | abcd##                         |
+---------------------------------+--------------------------------+
1 row in set (0.00 sec)

mysql> SELECT mask_outer('abcdef', 0, 5, '*'), mask_outer('abcdef',0, 5, "#");
+---------------------------------+--------------------------------+
| mask_outer('abcdef', 0, 5, '*') | mask_outer('abcdef',0, 5, "#") |
+---------------------------------+--------------------------------+
| abcdef                          | abcdef                         |
+---------------------------------+--------------------------------+
1 row in set (0.00 sec)
  • gen_rnd_panに数字が入れられない
mysql> SELECT mask_pan(gen_rnd_pan(19));
ERROR 1123 (HY000): Can't initialize function 'gen_rnd_pan'; Wrong argument list: gen_rnd_pan()
  • gen_rangeがマイナス値入れるとバグる
mysql> SELECT gen_range(100, 200), gen_range(-1000, -800);
+---------------------+------------------------+
| gen_range(100, 200) | gen_range(-1000, -800) |
+---------------------+------------------------+
| 159                 | 4294966311             |
+---------------------+------------------------+
1 row in set (0.00 sec)

感想

  • マスキング便利(バグっぽいところあるけど、無料でこれらが気軽に使えるのは良い)
    • でも、2バイト文字は使えない…。
    • 早く2バイト対応版欲しい!
mysql> SELECT mask_inner('41からはじめたんですよね?', 1, 2);
+----------------------------------------------------------+
| mask_inner('41からはじめたんですよね?', 1, 2)            |
+----------------------------------------------------------+
| 4XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX�?                      |
+----------------------------------------------------------+
1 row in set (0.00 sec)
  • ビューで同じデータを加工した形で見せることができるので色々な使い方ができそう
  • 一方でPerconaServer AND 8.0なのでそうじゃない環境の場合どうするか?
    • 既存のDBがMySQLだったり、Percona8.0.16以前ならレプリケーションしてスレーブにPercona8環境を用意するのか?
    • Aurora使ってる場合はbinlog有効にして、Aurora→RDS MySQL→Percona on EC2みたいな構成で使えるかな?
    • 移行する予定があるなら、マスキング使う予定あればこの際Perconaにしちゃうとか?

MySQLも含めてマスキングがまだまだ使い勝手が微妙なところはあるものの、気軽に元データに手を入れずにマスキングできるのは本当に便利。 無いよりはあったほうが絶対に良いので、なんとか使う方法を模索してみるのもありでは。