More than 5 years have passed since last update.

MySQLで個人情報をマスキング

MySQL

Posted at 2015-09-03

#はじめに
　実際に最も近いデータを使ってプログラムの試験を行いたいが、個人情報漏えいとかにならないように対策したい（特に社内監査でひかかからないようにしたい）、といったことがあり、その際の対処をまとめました。ご参考になりましたら幸いです。

#環境
　MySQL 5.6 (Windows)

#方針
本番環境に蓄積された生の個人情報を元データとして以下の対処を行う¹。

本人が特定できないレベルまでデータ加工する
試験結果の検証のため、ある程度の情報は残す（氏名全体を黒塗りとかしない）

ねんのため、個人情報保護法も確認。

第二条　この法律において**「個人情報」とは**、生存する個人に関する情報であって、当該情報に含まれる氏名、生年月日その他の記述等により特定の個人を識別することができるもの（他の情報と容易に照合することができ、それにより特定の個人を識別することができることとなるものを含む。）をいう。
引用元：個人情報の保護に関する法律

具体的な対処としては、①氏名やメールアドレスは最初の一文字分くらいを残して、それ以外をマスキング。②電話番号関連は市内局番や090とかを残しても試験結果の検証であまり役に立たないので、下二桁分くらいを残してそれ以外をマスキング。③個人を識別（特定）できない情報はマスキングしない、という三点で行きたいと思います。

#対処
この記事を書くにあたり「なんちゃって個人情報」でダミーデータを作成しました。

##ビフォー

※あきらかに個人を識別できるのでアウトな感じです。

##アフター

※マスキングした結果、試験データとして良さげになりました。

##流し込んだSQL

masking_testDB.meibo.sql

#「名前」をマスキング
SET @UNMASKED_LEN=1; #マスクしない文字数
SET @TBL_NAME='testDB.meibo'; #テーブル名
SET @COL_NAME='name'; #カラム名
SET @UME_MOJI='X'; #埋め文字（「X」「0」など適当に）
SET @STMT=CONCAT("UPDATE ",@TBL_NAME,
  " SET ",@COL_NAME,"=CONCAT(LEFT(",@COL_NAME,",",@UNMASKED_LEN,
  "),REPEAT('",@UME_MOJI,"',CHAR_LENGTH(",@COL_NAME,")-",@UNMASKED_LEN,"));");
SELECT @STMT; #組み立てたSQLを表示
PREPARE stmt FROM @STMT;
EXECUTE stmt;

#「ふりがな」をマスキング
SET @UNMASKED_LEN=2; #マスクしない文字数
SET @TBL_NAME='testDB.meibo'; #テーブル名
SET @COL_NAME='furigana'; #カラム名
SET @UME_MOJI='X'; #埋め文字（「X」「0」など適当に）
SET @STMT=CONCAT("UPDATE ",@TBL_NAME,
  " SET ",@COL_NAME,"=CONCAT(LEFT(",@COL_NAME,",",@UNMASKED_LEN,
  "),REPEAT('",@UME_MOJI,"',CHAR_LENGTH(",@COL_NAME,")-",@UNMASKED_LEN,"));");
SELECT @STMT; #組み立てたSQLを表示
PREPARE stmt FROM @STMT;
EXECUTE stmt;

#「メールアドレス」をマスキング
SET @UNMASKED_LEN=3; #マスクしない文字数
SET @TBL_NAME='testDB.meibo'; #テーブル名
SET @COL_NAME='mail'; #カラム名
SET @UME_MOJI='x'; #埋め文字（「X」「0」など適当に）
SET @STMT=CONCAT("UPDATE ",@TBL_NAME,
  " SET ",@COL_NAME,"=CONCAT(LEFT(",@COL_NAME,",",@UNMASKED_LEN,
  "),REPEAT('",@UME_MOJI,"',CHAR_LENGTH(",@COL_NAME,")-",@UNMASKED_LEN,"));");
SELECT @STMT; #組み立てたSQLを表示
PREPARE stmt FROM @STMT;
EXECUTE stmt;

#「電話番号」をマスキング（下二桁残す）
SET @UNMASKED_LEN=2; #マスクしない文字数
SET @TBL_NAME='testDB.meibo'; #テーブル名
SET @COL_NAME='tel'; #カラム名
SET @UME_MOJI='1'; #埋め文字（「X」「0」など適当に）
SET @STMT=CONCAT("UPDATE ",@TBL_NAME,
  " SET ",@COL_NAME,"=CONCAT(REPEAT('",@UME_MOJI,"',CHAR_LENGTH(",@COL_NAME,
  ")-",@UNMASKED_LEN,"),RIGHT(",@COL_NAME,",",@UNMASKED_LEN,"));");
SELECT @STMT; #組み立てたSQLを表示
PREPARE stmt FROM @STMT;
EXECUTE stmt;

#「携帯電話番号」をマスキング（下二桁残す）
SET @UNMASKED_LEN=2; #マスクしない文字数
SET @TBL_NAME='testDB.meibo'; #テーブル名
SET @COL_NAME='keitai'; #カラム名
SET @UME_MOJI='1'; #埋め文字（「X」「0」など適当に）
SET @STMT=CONCAT("UPDATE ",@TBL_NAME,
  " SET ",@COL_NAME,"=CONCAT(REPEAT('",@UME_MOJI,"',CHAR_LENGTH(",@COL_NAME,
  ")-",@UNMASKED_LEN,"),RIGHT(",@COL_NAME,",",@UNMASKED_LEN,"));");
SELECT @STMT; #組み立てたSQLを表示
PREPARE stmt FROM @STMT;
EXECUTE stmt;

#後始末
DROP PREPARE stmt;

##SQLの説明
　一見複雑そうですが中身は簡単です。
　大まかな流れとしては、特定のカラムについて、元の値の文字数を取得し、右側もしくは左側のN文字分を残して、残りの文字数分に「X」などの適当な文字で埋めて更新（UPDATE）するSQLを組み立てる。そして、

PREPARE stmt FROM @STMT;
EXECUTE stmt;

でSQLを実行する、という流れを五個のカラムに対して行っています。

以下の四行は、ご自分の環境に合わせて適宜書き換えてください。

SET @UNMASKED_LEN=?;
SET @TBL_NAME='????';
SET @COL_NAME='????';
SET @UME_MOJI='?';

なお、

SELECT @STMT;

で、組み立てたSQLを結果表示させています。ある程度慣れたら（ご自分の環境での運用が固まってきたら）、組み立てたSQLをメモ帳などにストックしておいてそこから実行する、というやり方もありです。

#注意事項

くれぐれも本番環境のデータをUPDATEしないよう注意してください。
また、本記事では件数も少なくDB構造も単純なもので試しましたので処理は一瞬で終わりますが、実際のDBは件数も多くIndexを作成してあったりして意外と処理時間がかかると思います。
もっと本格的に個人情報をマスキングしたい！というときは市販のソフトウェアを使ったほうがよいかもしれません（執筆者は残念ながらその経験はありませんが機能豊富で便利そうです）。

なお、元データを加工せず、表示上だけ変えるという方法もあるが、試験用にSQLを入れ替えたりするのは数も多いし面倒。そして試験環境だと試験実施者・検証者が直接DBのぞいたら意味ない、ということで却下。 ↩

You get articles that match your needs
You can efficiently read back useful information
You can use dark theme

What you can do with signing up