暗号化されたDBで並び替えを行いたいときの設計について

Question

前提・実現したいこと

現在開発しているシステムにて、年齢を扱っている機能があります。個人情報であるためこの値は暗号化を施す必要があるといわれ、値を暗号化してDBに格納するように設計しました。
ただ、テーブル内に格納されている暗号化された年齢を抽出する際にORDER BY句を利用したソートをしたいと考えております。
このときのソート順は格納されている暗号文そのままではなく、平文での昇順・降順で並び替えたいと考えています。
しかし単純にORDER BY句を利用すると当然暗号文で並び替えられてしまい思った動きになりません。

社内の別のシステムでどういった方法で平文での並び替えを実現しているのかと質問したところ、以下の方法で行っていると回答がありました。

1.「ソート用年齢値」テーブルのようなものを追加する
2.DBへのデータ格納時、年齢にある値を加算・乗算した値を「ソート用年齢値」テーブルに格納する
3.年齢抽出時、「ソート用年齢値」テーブルの値をSELECT句で減算・除算した値をORDER BY句でソートして暗号化された年齢が入っているテーブルと結合して抽出する

この方法を聞いたときに以下の疑問が生まれました

そもそも暗号化はある文字列に計算式をかませて難しい文字列を作り出しているものなので、上記の方法とやっていることは根本的には変わらない。
元の値を判断しづらくするためにわざわざ暗号化をしているにも変わらず、別のテーブルに解読しやすい「ソート用年齢値」なんてものがあったら意味がないのではないか

まとめるとお聞きしたい点は以下の二つになります

Ｑ１.上記の方法（ソート用の別テーブルを作る方法）は問題がないのか。一般的な方法なのか
Ｑ２.暗号化した値を平文でソートしたいとき、ほかにどのような方法が考えられるか

どうかよろしくお願いいたします。

補足情報（FW/ツールのバージョンなど）

値の暗号化はDBの機能（SQL ServerでいえばAlways Encrypt等）ではなく、個別に製作したライブラリに含まれる関数で行い、その値をDBに格納しています。

Answer 1

Ｑ１.上記の方法（ソート用の別テーブルを作る方法）は問題がないのか。一般的な方法なのか

一般的かどうかは分かりませんが、暗号化する意味が無いと思います。

Ｑ２.暗号化した値を平文でソートしたいとき、ほかにどのような方法が考えられるか

実際に、暗号化項目を検索対象とする要件がありました。
取った手段は、復号処理を関数としたファンクションインデックスを用いました。
DBMSに精通している人であれば、インデックス内容の解析も可能だと思われますが、それ以上の案が無くて。

インデックスを用いればソートも可能です。

追記

抽出した結果をソートするだけの要件であれば、SQL内で復号したものをソートすれば良いので、ファンクションインデックスは不要ですね。

Comments

[koronatail]

回答ありがとうございます。やはり暗号化の意味が薄くなってしまいますかね・・・。暗号化の目的から今一度メンバーと話し合ってみようかとおもいます。ファンクションインデックスを利用したことが無いのですがやはり性能はある程度犠牲にするものなのでしょうか。

[sazi]

データ更新時にインデックスも更新されるので、通常のインデックスより更新の負荷は多少上がるでしょうが、参照時の負荷は無いと思われます。
インデックスの作成やデータの照会において性能的には通常のインデックスより劣る感じですが、有る無しでは段違いです。

[akirafudo6]

そもそも、年齢って暗号化する必要があるのかな？個人を特定できなければいいのだから、氏名、住所（字町名）、保険番号、住記コード、社員番号
ぐらいじゃないかな？何を暗号化して、keyにするものにはどのようなものがあるか？とか十分に検討すべきではないでしょうか？
或いは、Ｄｂへ接続するためのユーザー、パスワードをどのように管理するかとか、パスワードも暗号化するとして、どの方式で暗号化するかとか？
暗号を解くためのｋｅｙはどうやって管理するかとかｓ？
接続するユーザーパスワードも、更新権限、参照権限、ＤＭＬ操作可能権限、ＤＤＬ操作可能権限をのように与えるとか？或いは、利用できる端末
をどのように制御するとか（例：opensslみたいな）など、検討すべきだと
思います。

Answer 2

以前似た疑問を持ち、質問しました。
回答としては少しずれるのですが、面白いので読み物として参考まで。
暗号化すべき情報とは？

Comments

[koronatail]

回答ありがとうございます。ソートという話ではないですが結局のところ知りたかったことはこういうことかもしれません。参考にさせていただきます。

Answer 3

関係外の人から画面を後ろから覗かれたときとか、そういうことを考慮してということであれば
しておくことに越したことはないかと思います。
（ソートできる画面で元のままになってたら意味はないですが。）

私はそれ以上に必要な状況が想定できなかったので、
このような対策であれば現状の方法で良いのではと思います。

Comments

[koronatail]

回答ありがとうございます。もともとは何らかの方法でデータベースにアクセスされたときを考えて云々という話が発端になっていました。特筆して問題のあるやり方というわけではないのですね。

Answer 4

Ｑ１.上記の方法（ソート用の別テーブルを作る方法）は問題がないのか。一般的な方法なのか

一般的かどうかは微妙なところですが、やり方としてはいいのではと思います。
ソースを見れば、元の年齢が分かってしまいますが、それはほかの暗号化されたテーブルも同じことです。

個人的には、年齢だけわかっても他が暗号化されているのだから問題はないのでは？と思います。
（まぁ、例えば１２０歳の人が世界に一人しかいなくて、１２０歳のデータがあったら、その人のデータはDB内に存在している！ということがばれてしまうことを是とするのであれば、ですが）

Ｑ２.暗号化した値を平文でソートしたいとき、ほかにどのような方法が考えられるか

独自実装ということなので、この方針でできるかわかりかねますが、こういった方法もあります。

AES_ENCRYPTで暗号化したテーブルをORDER BYで正常に並び替える
https://qiita.com/Go-Noji/items/a233595832201b2ec5e9

ただ、おそらくメモリ上に展開してソートということだと思いますので、レコード数によっては厳しいような気がしますが…。

Comments

[koronatail]

回答ありがとうございます。付随する情報が暗号化されているのであれば、個人を特定できない（難しい）情報であればある程度許容できるという感じでしょうか。プロジェクトが盲目的に暗号化を求められているので平文で持つと言ったら怒られてしまいそうですがひとまずはこの方法で行くという方向でメンバーと相談してみようかなと思います。

Answer 5

集計のできないデータなんて集積する意味があるのでしょうか？
平文で持たないなら取得するのをやめてよい案件だと思います。

Comments

[koronatail]

回答ありがとうございます。例では年齢を出したのでイメージと異なってしまうかもしれませんが、たとえば銀行の口座番号、マイナンバー、保険証の番号などを利用するシステムで「機微な情報だから暗号化してほしい」「画面に出すときはソートしたい」という二つの要求が出た場合、「それは二律背反なのでできません」と答えるものなのでしょうか

[maisumakun]

そういうコード番号のたぐいを、単票データの表示を越えて、たとえばソートしたりする要件自体、ほぼないと思います。

[sazi]

暗号化項目（例えば電話番号）に対して検索するというのは実際に経験のある要件でした。
ソートではありませんが結局インデックスが無いと性能が出せないものでしたが。