安全な UDFs¶

このトピックでは、安全な UDFs （ユーザー定義関数）を使用するための概念と構文（例を含む）について説明します。

このトピックの内容：

概要
プッシュダウン
安全な UDFsの作成
安全な UDFsとの対話
安全なベストプラクティス UDFs

概要¶

安全な UDFs を使用する理由は何ですか？¶

一部の SQL UDFs の内部最適化では、ベーステーブルの基になるデータへのアクセスが必要です。このアクセスにより、 UDF のユーザーから隠されたデータが、プログラムによる方法を介して間接的に公開される可能性があります。セキュリティで保護された UDFs はこれらの最適化を利用しないため、ユーザーが基になるデータに間接的にアクセスすることもありません。

また、 UDF 定義またはテキストとも呼ばれる UDF の作成に使用される SQL 式または JavaScript コードは、次のコマンドおよびインターフェイスでユーザーに表示されます。

SHOW FUNCTIONS および SHOW USER FUNCTIONS コマンド
GET_DDL ユーティリティ関数
FUNCTIONS 情報スキーマビュー
クエリプロファイル（ウェブインターフェイス内）

For security or privacy reasons, you might not wish to expose the underlying tables or algorithmic details for a UDF. With secure UDFs, the definition and details are visible only to authorized users (i.e. users who are granted the role that owns the UDF).

安全な UDF をいつ使用すればよいですか？¶

SQL UDFs は、データプライバシーのために特に指定されている場合に、安全であると定義される必要があります（基になるテーブルのすべてのユーザーに公開されるべきではない機密データへのアクセスを制限するため）。

安全な UDFs は、ユーザーが基礎となるデータ表現を理解する必要のないクエリデータを簡素化するために作成されたクエリなど、クエリの利便性のために定義された SQL UDFs には使用しないでください。これは、Snowflakeクエリオプティマイザーが安全な UDFs を評価するときに、通常の UDFs に使用される最適化をバイパスするためです。これにより、安全な UDFs のクエリパフォーマンスが低下する可能性があります。

ちなみに

安全な UDF を使用するかどうかを決定するときは、 UDF の目的を考慮し、データのプライバシー/セキュリティとクエリパフォーマンスのトレードオフを検討する必要があります。

また、データの機密性が高く、ある型のオブジェクト（ UDFsなど）を介したアクセスを保護する必要があると判断した場合、他の型のオブジェクト（ビューなど）を介したアクセスも保護することを必ず検討する必要があります。

例えば、安全な UDFs が特定のテーブルへのアクセスのみを許可する場合、同じテーブルへのアクセスを許可するビューもすべて安全である必要があります。

通常の UDF では、データがどのように公開される可能性がありますか？¶

ユーザーは、通常の UDF のセキュリティを回避する方法について疑問に思うかもしれません。限られた/特定の状況では、ユーザーには直接表示されない行に関する情報を推測できる場合があります。詳細については、プッシュダウン（このトピック）をご参照ください。

プッシュダウン¶

間接的にデータを公開する方法を理解することにより、 プッシュダウン の概念を理解できます。

プッシュダウンとは何ですか？¶

クエリ処理中に不要な行をできるだけ早くフィルタリングするプッシュダウンにより、パフォーマンスは向上します。プッシュダウンにより、メモリ消費も削減できます。ただし、プッシュダウンにより、機密データが間接的に公開される可能性があります。

次のクエリを検討してください。

SELECT col1
  FROM tab1
  WHERE location = 'New York';

次にクエリ処理の例を示します。

テーブルからすべての行をメモリに読み込みます（ FROM 句を実行）。
メモリ内の行をスキャンし、 New York に一致しない行を除外します（ WHERE 句を実行）。
メモリに残っている行から col1 を選択します（ SELECT リストを実行）。

これは「最初にロードし、後でフィルタリングする」戦略と考えることができます。簡単ではありますが、非効率的です。

通常、できるだけ早くフィルタリングする方が効率的です。早期フィルタリングは、「フィルターをクエリプランに深くプッシュする」、または単に「プッシュダウン」と呼ばれます。

このクエリ例では、 WHERE 句に一致しないレコードをロードしないようにテーブルスキャンコードに指示する方が効率的です。これはフィルタリング時間を節約しません（すべての行の場所を1回読み取る必要あり）が、処理する行が少ないため、かなりのメモリを節約し、後続の処理時間を短縮できます。

場合によっては、データをさらに効率的に処理できます。例えば、データが州ごとに分割されていると仮定します（ニューヨークのすべてのデータが1つのマイクロパーティションにあり、フロリダのすべてのデータが別のマイクロパーティションにあるなど）。このシナリオでは：

Snowflakeは、すべての行をメモリに保存する必要はありません。
Snowflakeはすべての行を読み取る必要はありません。

これを「プッシュダウン」の別の形式として大まかに定義します。

「フィルターをプッシュダウンする」という原則は、広範囲のクエリに適用されます。多くの場合、最も選択的である（最も多くのデータを除外する）フィルターが最も深く（最も早く実行）プッシュされ、残りのクエリが行う必要のある作業が削減されます。

プッシュダウンは、クラスタリング（データのソート/並べ替え）などの他の手法と組み合わせることで、読み取り、ロード、処理が必要な関係のないデータ量を削減できます。

UDFs データを保護するにはどうすればよいですか？¶

データの保護に使用される適切なフィルターが適用される前に、一般的なフィルターを実行できるオプティマイザーがフィルターの順序を変更すると、基礎となる詳細が公開される可能性があります。したがって、解決策としては、最適化が安全でない場合には、オプティマイザーが特定の型のフィルターをプッシュダウンしないようにすることです（より一般的には、フィルタープッシュダウンを含むがこれに限定されない特定の型の最適化をオプティマイザーが使用できないようにする）。

UDF を「安全」と宣言すると、オプティマイザーは特定のフィルターをプッシュダウンしません（より一般的には、特定の最適化を使用しない）。ただし、特定の型の最適化を防止すると、パフォーマンスに影響を与える可能性があります。

プッシュダウンによる間接的なデータ露出の例¶

次の例は、プッシュダウンが間接的にクエリに関する基本的な詳細を公開する方法の例を示しています。この例ではビューに焦点を当てていますが、 UDFs にも同じ原則が適用されます。

患者に関する情報を保存するテーブルがあるとします。

CREATE TABLE patients
  (patient_ID INTEGER,
   category VARCHAR,      -- 'PhysicalHealth' or 'MentalHealth'
   diagnosis VARCHAR
   );

INSERT INTO patients (patient_ID, category, diagnosis) VALUES
  (1, 'Mental', 'paranoia'),
  (2, 'Physical', 'lung cancer');

2つのビューがあり、1つは精神的健康情報を示し、もう1つは身体的健康情報を示します。

CREATE VIEW mental_health_view AS
  SELECT * FROM patients WHERE category = 'MentalHealth';

CREATE VIEW physical_health_view AS
  SELECT * FROM patients WHERE category = 'PhysicalHealth';

ほとんどのユーザーはテーブルに直接アクセスできません。代わりに、ユーザーには次の2つのロールのいずれかが割り当てられます。

mental_health_view から読み取る権限を持つ MentalHealth 。
physical_health_view から読み取る権限を持つ PhysicalHealth 。

ここで、身体的健康データのみの権限を持つ医師が、現在精神衛生上の患者がテーブルにいるかどうかを知りたいと仮定します。医師は次のようなクエリを作成できます。

SELECT * FROM physical_health_view
  WHERE 1/IFF(category = 'MentalHealth', 0, 1) = 1;

このクエリは次と同等です：

SELECT * FROM patients
  WHERE
    category = 'PhysicalHealth' AND
    1/IFF(category = 'MentalHealth', 0, 1) = 1;

Snowflakeでは、このクエリを処理するために使用する（少なくとも）2つの方法があります。

方法1：
1. 患者テーブルのすべての行を読み取ります。
2. ビューのセキュリティフィルターを適用します（カテゴリが PhysicalHealth ではない行を除外）。
3. クエリに WHERE 句を適用します（ WHERE 1/IFF(category = 'MentalHealth', 0, 1) = 1 に基づくフィルター）。
方法2ではフィルターの順序を変更するため、クエリは次のように実行されます。
1. 患者テーブルのすべての行を読み取ります。
2. クエリに WHERE 句を適用します（ WHERE 1/IFF(category = 'MentalHealth', 0, 1) = 1 に基づくフィルター）。
3. ビューのセキュリティフィルターを適用します（カテゴリが PhysicalHealth ではない行を除外）。

論理的には、これら2つのシーケンスは同等に見えます。同じ行セットを返します。ただし、これら2つのフィルターの選択の程度によっては、1つの処理順序が速くなる場合があり、Snowflakeのクエリプランナーは、より高速に実行されるプランを選択する場合があります。

オプティマイザーが2番目のプランを選択するとします。このプランでは、句 WHERE 1/IFF(category = 'MentalHealth', 0, 1) = 1 がセキュリティフィルターの前に実行されます。患者テーブルに category = 'MentalHealth' の行がある場合、 IFF 関数はその行に対して0を返し、句は事実上 WHERE 1/0 = 1 になるため、ステートメントはゼロ除算エラーを引き起こします。 physical_health_view 権限を持つユーザーには、精神的健康に問題のある人の行は表示されませんが、精神的健康カテゴリに少なくとも1人は存在すると推測できます。

この手法は、基礎となる詳細を常に公開するとは限りません。ビュー（または UDFs）の記述方法と同様に、クエリプランナーが行う選択に大きく依存します。ただし、この例は、ユーザーが直接表示できない行に関する情報を推測できることを示しています。

安全な UDFsの作成¶

安全な UDFs は、関数の標準 DDL で SECURE キーワードを使用して定義されます。

安全な機能を作成するには、 CREATE FUNCTION コマンドで SECURE キーワードを指定します。
既存の関数を安全な関数に変換したり、通常の関数に戻したりするには、 SECURE キーワードを設定/設定解除します。
ALTER FUNCTION コマンド。

安全な UDFsとの対話¶

安全な関数の定義の表示¶

安全な関数の定義は、許可されたユーザー、つまり機能を所有するロールを付与されたユーザーにのみ公開されます。権限のないユーザーが次のコマンドまたはインターフェイスのいずれかを使用する場合、関数定義は次のいずれにも表示されません。

SHOW FUNCTIONS コマンド
GET_DDL ユーティリティ関数
FUNCTIONS 情報スキーマビュー

関数が安全かどうかを判断する¶

FUNCTIONS 情報スキーマビューの IS_SECURE 列は、関数が安全かどうかを識別します。例：

select is_secure from information_schema.functions where function_name = 'MYFUNCTION';

クエリプロファイルでのセキュリティ機能の詳細の表示¶

安全な関数の内部は、クエリプロファイル（ウェブインターフェイス内）で公開されません。非所有者は所有者のクエリプロファイルにアクセスできるため、これは安全な関数の所有者にも当てはまります。

安全なベストプラクティス UDFs¶

安全な UDFs は、関数によってフィルタリングされたテーブルの行のデータがユーザーに表示される可能性を防ぎます。ただし、 UDFs を慎重に構築しないと、データ所有者が基礎となるデータに関する情報を誤って公開してしまう可能性はまだあります。このセクションでは、回避すべき潜在的な落とし穴について説明します。

シーケンス生成列¶

代理キーを生成する一般的な方法は、シーケンス列または自動インクリメント列を使用することです。これらのキーが、基礎となるデータのすべてにアクセスできないユーザーに公開されている場合、ユーザーは基礎となるデータ分布の詳細を推測できる可能性があります。

例えば、 ID 列を公開する関数 get_widgets_function() があるとします。シーケンスから ID が生成される場合、 get_widgets_function() のユーザーは、ユーザーがアクセスできる2つのウィジェットの作成タイムスタンプの間に作成されたウィジェットの総数を推測できます。次のクエリと結果について考えてみてください：

select * from table(get_widgets_function()) order by created_on;

------+-----------------------+-------+-------+-------------------------------+
  ID  |         NAME          | COLOR | PRICE |          CREATED_ON           |
------+-----------------------+-------+-------+-------------------------------+
...
 315  | Small round widget    | Red   | 1     | 2017-01-07 15:22:14.810 -0700 |
 1455 | Small cylinder widget | Blue  | 2     | 2017-01-15 03:00:12.106 -0700 |
...

結果に基づいて、ユーザーは1139ウィジェット（1455 - 315）が1月7日から1月15日の間に作成されたと疑う可能性があります。この情報が機密性が高すぎて関数のユーザーに公開できない場合、次のいずれかの方法を使用できます。

シーケンス生成列を関数の一部として公開しない。
シーケンスによって生成された値の代わりに、ランダム化された識別子（ UUID_STRING によって生成など）を使用する。
プログラムで識別子を難読化する。

スキャンデータサイズ¶

安全な関数を含むクエリの場合、Snowflakeはスキャンされたデータの量（バイトまたはマイクロパーティションのいずれか）またはデータの合計量を公開しません。これは、データのサブセットにのみアクセスできるユーザーから情報を保護するためです。ただし、ユーザーはクエリのパフォーマンス特性に基づいて、基礎となるデータの量について観察することができます。例えば、2倍の長さで実行されるクエリは、2倍のデータを処理する可能性があります。そのような観察結果はせいぜい概算ですが、場合によっては、このレベルの情報でさえ公開されることは望ましくありません。

このような場合、ベースデータの関数をユーザーに公開するのではなく、ユーザー/ロールごとにデータを具体化するのが最適です。 widgets テーブルの場合、ウィジェットにアクセスできる各ロールに対してテーブルが作成され、そのロールからアクセス可能なウィジェットのみが含まれ、ロールにはそのテーブルへのアクセスが許可されます。これは、単一の機能を使用するよりもはるかに複雑ですが、非常に高いセキュリティの状況では、これが必要になる場合があります。