情報セキュリティやガバナンスが重視される中で、PII（Personally Identifiable Information：個人識別可能情報）を正しく検出・保護することが求められています。
本記事では、SQLとメタデータを活用した個人情報カラムの自動検出テクニックを紹介します。
PII自動検出のアプローチ
PIIの検出には以下の2つの手法を組み合わせるのが効果的です：
- メタデータ分析：列名やデータ型から推測
- サンプルデータ分析：正規表現を使って実データから判定
SQLだけでも、この2段階の分析をある程度自動化可能です。
1. メタデータ分析：列名からPII候補を抽出
PostgreSQLでは information_schema.columns を使って、全テーブル・列の情報にアクセスできます。
-- PIIらしき列名を検索（name, email, phoneなどを含む） SELECT table_schema, table_name, column_name, data_type FROM information_schema.columns WHERE column_name ~* '(name|email|mail|phone|tel|address|zip|ssn|card)' ORDER BY table_schema, table_name; このクエリで、名前・メール・電話番号などのキーワードを含む列が抽出されます。
注意点： 意図しない列（例：company_name など）も含まれるため、後述の「データパターン分析」と組み合わせて信頼度を上げます。
2. データパターン分析：サンプルデータから正規表現で検出
列名だけでは判断が難しい場合、サンプルデータの値パターンをチェックします。
例1：メールアドレスの判定
SELECT email_column FROM users WHERE email_column ~ '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$' LIMIT 10; 例2：電話番号の判定
SELECT phone_column FROM customers WHERE phone_column ~ '^0\d{1,4}-\d{1,4}-\d{4}$' -- 日本の固定電話フォーマット OR phone_column ~ '^0[789]0-\d{4}-\d{4}$' -- 携帯番号 LIMIT 10; 例3：郵便番号（日本）
SELECT zip_code FROM addresses WHERE zip_code ~ '^\d{3}-\d{4}$' LIMIT 10; これにより、実際に個人情報に該当する値が含まれているかを自動チェックできます。
3. PIIスキャンを自動化するビューの構築
テーブル単位で、PIIらしき列をスキャンできるSQLテンプレート関数やビューの作成も有効です。
例：全テーブルに対するPIIサンプル値の横断チェック（PostgreSQL専用）
DO $$ DECLARE r RECORD; query TEXT; BEGIN FOR r IN SELECT table_schema, table_name, column_name FROM information_schema.columns WHERE column_name ~* '(email|phone|name|zip)' AND data_type IN ('character varying', 'text') LOOP query := format( 'SELECT DISTINCT %I FROM %I.%I WHERE %I ~ %L LIMIT 5;', r.column_name, r.table_schema, r.table_name, r.column_name, CASE WHEN r.column_name ~* 'email' THEN '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\\.[A-Za-z]{2,}$' WHEN r.column_name ~* 'phone' THEN '^0\\d{1,4}-\\d{1,4}-\\d{4}$' WHEN r.column_name ~* 'zip' THEN '^\\d{3}-\\d{4}$' ELSE '' END ); RAISE NOTICE 'PII check SQL: %', query; EXECUTE query; END LOOP; END $$; このスクリプトは、PIIらしきカラムに対して自動でパターン検査SQLを生成し、RAISE NOTICE で出力。拡張すればログテーブルへの記録やレポート出力も可能です。
4. 注意点とベストプラクティス
- 誤検出のリスクあり：完全自動化よりも「候補抽出＋人による確認」が理想
- 運用への組み込み：DWHロード前の検査ステップやCI/CDでのスキーマ検証に組み込むと有効
- バージョン管理：PII検出結果を時系列で保存することで、データ設計変更時の追跡が可能
まとめ
PII検出はセキュリティとコンプライアンスの第一歩です。SQLによる自動化により、次のような運用が可能になります：
- 列名・型ベースでの高速な候補抽出
- データパターンによる正確な識別
- ビュー・スクリプトによる継続的なスキャン
「どこにPIIが存在するか」を正確に把握することで、漏洩防止・マスキング設計・データ共有時のリスク低減が可能になります。
