おはこんばんにちは、ビショップです。
最近PowerQuery for Excelってのが社内で話題です。私もなんか使い方を勉強してますけども、今回はその使い方ではなく、おそらく誰しもよく分からずに使ってしまっているであろう、データソースの「プライバシーレベル」について、設定によってどんな動きになるか調査・検証してみました。
プライバシーレベル自体は、データソースごとに設定するもので、そのデータソース自体がどの程度の機密性を持つかを指定するものだという認識で良いと思います。異なる2つのデータソース間で検索条件等をやり取りする場合に、設定したレベルによって、データの取り扱い方が変わります。レベルは3つ設定できますが、Microsoftのヘルプは相変わらずよく分からないので、実際に設定してどういった動きになるか見てみましょう。
設定できるプライバシーレベルは以下の3つです。
- プライベート
- 組織
- パブリック
データの取り扱いとは
ExcelにPowerQueryで、データソースAとデータソースBを設定しているとします。データソースAが例えばマスタで、その情報をキーにデータソースBのデータを抽出するクエリを作成したとします。その場合、データの絞り込みには2つの方法があります。
- データソースAのキーをデータソースBに渡して、データソースBで絞り込みを実行した結果をExcelに返す
- データソースBの全データをとりあえず持ってきて、Excel上でデータソースAのキーを使ってフィルタをかける
何が問題なのか
1の方法では、データソースBに検索条件付きのSQLを投げます。つまり、SQLのログがDBMS側に残ってしまいます。分かる人なら検索条件、抽出できてしまいますね。それが顧客情報に関するものだったりするとまずいですね。データの保存先によりますが、もしかしたら情報漏洩になったりするかもしれません。
2の方法では、1の方法と違い、ローカルに全データを持ってきてから処理します。重そうですね。でも余計な情報を外に漏らすことはなさそうです。
この制御というかリスク回避をプライバシーレベルの設定でなんとかするということのようです。
実際の動作
データソースAをパラメータとして扱い、データソースBを絞り込んで表示します。この時のデータソースAのプライバシーレベルによる動作の違いです。データソースAを入力パラメータ、データソースBをSQLServer内のトランザクションデータとし、SQLServerの実行SQLがどうなるか検証しました。
パブリック
「パブリック」に設定した入力パラメータは、データソースBに対して発行されるSQLに検索条件が付与されて実行されます。これは相手のプライバシーレベルは関係ありません。上記画像のようなパラメータの入力がされていた場合、SQLServer側で実行されるSQLはこうなります。
SELECT * FROM table WHERE customer = 'AAAA'
プライベート
「プライベート」に設定したデータソースは一番機密性の高いデータとして扱われ、他のデータソースに一切データ送信されません。SQL文は一切検索条件が付与されないシンプルなものが発行されます。データソースAをプライベートとした場合、どんな入力をしていたとしてもSQLServer側で実行されるSQLはこうなります。
SELECT * FROM table
組織
「組織」に設定したデータソースは、検索に行く相手データソースが「組織」だった場合とそれ以外だった場合で動作が変わります。相手も「組織」だった場合は、パブリックと同様の動作になり、このSQLが実行されます。
SELECT * FROM table WHERE customer = 'AAAA'
相手がパブリックだったりプライベートだったりした場合は、全件検索するSQLが実行されます。
SELECT * FROM table
実際どうすべき？
社内データしか扱わない、ということが分かっていれば「組織」でいいですね。でも基本は「プライベート」を案内する方がいいでしょう。ちゃんと理解してくれる人がいればちゃんと教えるんですが、こういうの理解してくれる人ってあんまりいないのが常ですよねぇ。
