LoginSignup
2
1
@zumax

【Databricks】row filters と column masksを試してみた

Posted at

背景・目的

2023年9月に、Filter sensitive data with row filters and column masks (Public Preview)が発表されており、今更ですがキャッチアップのために試してみます。

まとめ

2024/1/29時点で、Public Previewです。

Row FilterとColumn Masksは、下記の特徴があります。

特徴 概要
行フィルタ ・UDFを書いて、フィルターポリシーを定義
・ALTER TABLEステートメントを使用してテーブルに適用
・各テーブルには、行フィルターを1つだけ含めることが可能
列マスク ・表の列にマスキング関数を適用できる
・列マスキングはSQL UDFとして記述する
・クエリ実行時に評価される
・ターゲットカラムの各参照をマスキング関数の結果に置換できる
・各テーブル列に１つのマスキング関数を適用できる
マッピングテーブル ・行レベルのセキュリティでは、マップングテーブルを定義することを検討する
・元の表のどのデータ行が特定のユーザ、またはグループがアクセスできるかをエンコードする包括的なマッピングテーブル
制限事項 下記についてサポートされない。
・12.2 LTS以前のランタイム
・DLTのマテビューと、ストリーミングテーブル
・Python、Scala UDF
・Delta Sharing
・タイムトラベル
・テーブルのサンプリング

概要

Filter sensitive table data using row filters and column masksを元に整理します。

行フィルターと列マスク、マッピングテーブルを使用してテーブル内の機密データをフィルターする。

What is the difference between these filters and dynamic views?

動的ビューは、1 つ以上のソース表の抽象化された読み取り専用 ビュー です。 ユーザーは、ソース表に直接アクセスしなくても、動的ビューにアクセスできます。 動的ビューを作成すると、新しいテーブル名が定義されますが、その名前は、ソース テーブル、または同じスキーマに存在する他のテーブルおよびビューの名前と一致してはなりません。

動的ビューとの違いについて

  • あたらしいViewを定義する

一方、行フィルターまたは列マスクをターゲット表に関連付けると、新しい表名を導入することなく、対応するロジックが表自体に直接適用されます。 後続のクエリーは、元の名前を使用してターゲットテーブルを直接参照し続けることができます。

  • 行フィルターと列マスクは、新しい表を導入することない

動的ビューと行フィルターおよび列マスクの両方で、複雑なロジックをテーブルに適用し、クエリー ランタイムでフィルター処理の決定を処理できます。

  • 両方とも、複雑なロジックをテーブルに適用し、クエリ、ランタイムでフィルター処理の決定を処理できる

フィルターやマスクなどの変換ロジックを読み取り専用テーブルに適用する必要がある場合、およびユーザーが別の名前を使用して動的ビューを参照できる場合は、動的ビューを使用します。 特定のデータに対して式をフィルター処理またはコンピュートする一方で、ユーザーが元の名前を使用してテーブルにアクセスできるようにする場合は、行フィルターと列マスクを使用します。

  • 特に、使い分けについて書いていない。。

Column filter

What are row filters?

行フィルターを使用すると、テーブルにフィルターを適用して、後続のクエリーがフィルター述語が true と評価された行のみを返すようにすることができます。 行フィルターは、SQL ユーザー定義関数 (UDF) として実装されます。

  • 行フィルターは、UDFで定義する

行フィルターを作成するには、まず SQL UDF を記述してフィルター ポリシーを定義し、 ALTER TABLE ステートメントを使用してテーブルに適用します。 または、最初の CREATE TABLE ステートメントでテーブルの行フィルターを指定することもできます。 各テーブルには、行フィルターを 1 つだけ含めることができます。 行フィルターは、0 個以上の入力パラメーターを受け入れ、各入力パラメーターは対応するテーブルの 1 つの列にバインドされます。

  • UDFを書いて、フィルターポリシーを定義する
  • ALTER TABLEステートメントを使用してテーブルに適用する
  • 各テーブルには、行フィルターを1つだけ含めることが可能
  • 行フィルターは、0個以上のパラメーターを受け、各入力パラメーターは対応するテーブルの1つの列にバインドされる。

Column masks

What are column masks?

列マスクを使用すると、表の列にマスキング関数を適用できます。 マスキング関数はクエリー ランタイムで評価され、ターゲットカラムの各参照をマスキング関数の結果に置き換えます。 ほとんどのユースケースでは、列マスクは、呼び出し元のユーザーの ID に基づいて、元の列の値を返すか、編集するかを決定します。 列マスクは、SQL UDF として記述される式です。

  • 表の列にマスキング関数を適用できる
  • 列マスキングはSQL UDFとして記述する
  • クエリ実行時に評価される
  • ターゲットカラムの各参照をマスキング関数の結果に置換できる

各テーブル列には、オプションで 1 つのマスキング関数を適用できます。 マスキング関数は、列のマスクされていない値を入力として受け取り、その結果としてマスクされた値を返します。 マスク関数の戻り値は、マスクされる列と同じ型である必要があります。 マスキング関数は、追加の列を入力パラメーターとして受け取り、マスキングロジックで使用することもできます。

  • 各テーブル列に１つのマスキング関数を適用できる
  • マスキング関数は、列のマスクされていない値を受け取り、その結果としてマスキングされた値を返す
    • 戻り値は、マスクされるカラムと同じ型である必要がある
    • 追加の列の入力パラメータとして受け取り、マスキングロジックで使用することもできる

列マスクを適用するには、関数を作成し、 ALTER TABLE ステートメントを使用してテーブル列に適用します。 または、テーブルの作成時にマスキング機能を適用することもできます。

  • ALTER TABLE　または、CREATE TABLEで適用する。

Use mapping tables to create an access-control list

行レベルのセキュリティを実現するには、マッピング テーブル (またはアクセス制御リスト) を定義することを検討してください。 各マッピング・テーブルは、元の表のどのデータ行が特定のユーザーまたはグループがアクセスできるかをエンコードする包括的なマッピング・テーブルです。 マッピング テーブルは、直接結合によってファクト テーブルと簡単に統合できるので便利です。
この方法論は、カスタム要件を持つ多くのユースケースに対処するのに有益であることが証明されています。 例は次のとおりです。

  • ログインしたユーザーに基づいて制限を課し、特定のユーザーグループに対して異なるルールに対応します。
  • 組織構造などの複雑な階層を作成するには、多様なルールセットが必要です。
  • 外部ソース システムから複雑なセキュリティ モデルをレプリケートする。

このようにマッピング テーブルを採用することで、これらの困難なシナリオに効果的に取り組み、行レベルおよび列レベルの堅牢なセキュリティ実装を確保できます。

  • 行レベルのセキュリティでは、マップングテーブルを定義することを検討する
  • 元の表のどのデータ行が特定のユーザ、またはグループがアクセスできるかをエンコードする包括的なマッピングテーブル
  • ユースケース例
    • ログインしたユーザに基づき、制限を課す
    • 特定のグループに対して異なるルールに対応する
    • 組織構造が複雑な場合
    • 外部ソースシステムから複雑なセキュリティモデルをレプリケートする

サポート

  • SQL ワークロード用の Databricks SQL と Databricks ノートブックがサポートされています。
  • MODIFY 権限を持つユーザーによるDatabricks Machine Learning コマンドがサポートされています。フィルターとマスクは、UPDATE および DELETE によって読み取られるデータに適用され、書き込まれるデータ (INSERT データを含む) には適用されません。
  • サポートされている形式: Delta および Parquet. Parquet は、マネージ テーブルまたは外部テーブルでのみサポートされます。
  • 列マスクまたは行フィルターを使用するテーブルのビューがサポートされています
  • Delta Lake チェンジデータフィードは、スキーマがターゲットテーブルに適用される可能性のある行フィルターおよび列マスクと互換性がある限りサポートされます。
  • 外部テーブルがサポートされています。

制限事項

12.2 LTS より前のDatabricks Runtime バージョンでは、行フィルターまたは列マスクはサポートされていません。これらのランタイムは安全に失敗するため、サポートされていないバージョンのランタイムからテーブルにアクセスしようとすると、データは返されません。
Delta Live Tables マテリアライズドビューとストリーミングテーブルは、行フィルターまたは列マスクをサポートしていません。
Python または Scala UDF は、行フィルターまたは列マスク関数として直接サポートされていません。 ただし、定義がカタログに永続的に格納されている (つまり、セッションに対して一時的ではない) 限り、SQL UDF でこれらを参照できます。
Delta Sharing は、行レベルのセキュリティまたは列マスクでは機能しません。
タイムトラベルは、行レベルのセキュリティまたは列マスクでは機能しません。
テーブルのサンプリングは、行レベルのセキュリティまたは列マスクでは機能しません。
ポリシーを持つテーブル内のファイルへのパスベースのアクセスは、現在サポートされていません。
元のポリシーに戻る循環依存関係を持つ行フィルター ポリシーまたは列マスク ポリシーはサポートされていません。
MERGE また、浅いクローンはサポートされていません。

下記ではサポートされない

  • 12.2 LTS以前のランタイム
  • DLTのマテビューと、ストリーミングテーブル
  • Python、Scala UDF
  • Delta Sharing
  • タイムトラベル
  • テーブルのサンプリング

実践

Row filter

上限に合致した場合の確認

  1. ナビゲーションペインで「SQL Editor」をクリックします。

  2. 下記のクエリを実行し、事前に内容を確認します。

    DESC test.retail.products;
===
col_name	data_type	comment
product_id	int	
product_name	varchar(100)	
price	decimal(10,2)	
stock	int	

SELECT * FROM test.retail.products;
====
product_id	product_name	price	stock
103	ラズベリー	91.74	145
104	マンゴー	132.79	77
105	ブルーベリー	140.19	186
106	アボカド	206.82	114
107	パパイア	229.05	49
108	プラム	251.57	193
109	アボカド	154.91	83
110	ブルーベリー	119.88	44
111	ラズベリー	96.07	104
112	パパイア	150.68	58
113	梨	157.68	11
114	ブルーベリー	259.92	26
115	プラム	214.20	72
116	グレープフルーツ	70.72	186
117	オレンジ	253.94	25
118	イチゴ	95.68	168
119	キウイ	220.74	25
120	スイカ	267.77	100
121	アボカド	196.17	100
122	ラズベリー	197.52	76
101	りんご	100.00	50
102	バナナ	80.00	100

SELECT COUNT(1) FROM test.retail.products;
===
count(1)
22

  3. Functionを作成します。

    • adminグループに所属するユーザは、Product_idが103のものを表示します。
    CREATE OR REPLACE FUNCTION product_filter(product_id int)
RETURN IF(IS_ACCOUNT_GROUP_MEMBER('admin'), product_id=103, product_id=104);


DESCRIBE FUNCTION product_filter;
===
function_desc
Function: test.retail.product_filter
Type:     SCALAR
Input:    product_id INT
Returns:  BOOLEAN

  4. 行フィルターとしてテーブルに設定します。

    ALTER TABLE test.retail.products SET ROW FILTER product_filter ON (product_id);

  5. adminグループに所属するユーザで検索してみます。product_idが103のレコードのみ表示されました。

    SELECT * FROM test.retail.products;
===
product_id	product_name	price	stock
103	ラズベリー	91.74	145

条件に合致しない場合の確認

  1. analysisグループに所属するユーザで検索します。product_idが104のレコードのみ表示されました。 
    SELECT * FROM test.retail.products;
===
product_id	product_name	price	stock
104	マンゴー	132.79	77

全てのレコードを表示するには？

  1. 上限に合致した場合、第一引数を「true」とします。

    CREATE OR REPLACE FUNCTION product_filter(product_id int)
RETURN IF(IS_ACCOUNT_GROUP_MEMBER('admin'), true, product_id=110);

  2. adminグループのユーザで実行します。全て取得できました。

    SELECT * FROM test.retail.products;
===
product_id	product_name	price	stock
103	ラズベリー	91.74	145
104	マンゴー	132.79	77
105	ブルーベリー	140.19	186
106	アボカド	206.82	114
107	パパイア	229.05	49
108	プラム	251.57	193
109	アボカド	154.91	83
110	ブルーベリー	119.88	44
111	ラズベリー	96.07	104
112	パパイア	150.68	58
113	梨	157.68	11
114	ブルーベリー	259.92	26
115	プラム	214.20	72
116	グレープフルーツ	70.72	186
117	オレンジ	253.94	25
118	イチゴ	95.68	168
119	キウイ	220.74	25
120	スイカ	267.77	100
121	アボカド	196.17	100
122	ラズベリー	197.52	76
101	りんご	100.00	50
102	バナナ	80.00	100

  3. analysisグループに所属するユーザで実行します。product_id=110のレコードのみ取得できました。

    SELECT * FROM test.retail.products;
====
product_id	product_name	price	stock
110	ブルーベリー	119.88	44

Column masks

  1. ナビゲーションペインで「SQL Editor」をクリックします。

  2. 下記のクエリを実行し、事前に内容を確認します。

    SELECT * FROM test.retail.customers;
===
customer_id	name	address	phone
1	山田太郎	東京都渋谷区...	090-XXX-XXX
2	鈴木花子	大阪府北区...	080-YYY-YYYY

SELECT COUNT(1) FROM test.retail.customers;
===
count(1)
2

  3. Functionを作成します。

    CREATE FUNCTION name_mask(name STRING)
  RETURN CASE WHEN IS_ACCOUNT_GROUP_MEMBER('admin') THEN name ELSE '〇〇 △△△' END;


DESCRIBE FUNCTION name_mask;
===
function_desc
Function: test.default.name_mask
Type:     SCALAR
Input:    name STRING
Returns:  STRING

  4. カラムにマスクとして設定します。

    ALTER TABLE test.retail.products SET ROW FILTER product_filter ON (product_id);

合致する場合の表示

  1. adminグループに所属しているユーザで、SELECTします。確認できました。 
    SELECT * FROM test.retail.customers;
===
customer_id	name	address	phone
1	山田太郎	東京都渋谷区...	090-XXX-XXX
2	鈴木花子	大阪府北区...	080-YYY-YYYY

合致しない場合の表示

  1. analysisグループに所属するユーザで実行します。nameがマスクされました。 
    SELECT * FROM test.retail.customers;
===
customer_id	name	address	phone
1	〇〇 △△△	東京都渋谷区...	090-XXX-XXX
2	〇〇 △△△	大阪府北区...	080-YYY-YYYY
===

複数のカラムをマスク

上記のnameの他にもaddressもマスクします。

  1. Functionを作成します。

    CREATE FUNCTION address_mask(address STRING)
  RETURN CASE WHEN IS_ACCOUNT_GROUP_MEMBER('admin') THEN address ELSE 'XXXXX' END;

  2. カラムにマスクとして設定します。

    ALTER TABLE test.retail.customers ALTER COLUMN address SET MASK address_mask;

  3. analysisグループに所属するユーザで実行します。nameに加えて、addressもマスクされました。

    SELECT * FROM test.retail.customers;
===
customer_id	name	address	phone
1	〇〇 △△△	XXXXX	090-XXX-XXX
2	〇〇 △△△	XXXXX	080-YYY-YYYY

Mapping table

  1. マッピングテーブルを作成します。

    CREATE TABLE test.retail.valid_user(user_name string);

DESC test.retail.valid_user;
===
col_name	data_type	comment
user_name	string

  2. マッピングテーブルにレコードを追加します。

    INSERT INTO test.retail.valid_user VALUES('XXX');

  3. Function を作成します。

    CREATE FUNCTION row_filter()
  RETURN EXISTS(
    SELECT 1 FROM test.retail.valid_user v
    WHERE v.user_name = CURRENT_USER()
);

DESC FUNCTION row_filter;
===
function_desc
Function: test.retail.row_filter
Type:     SCALAR
Input:    ()
Returns:  BOOLEAN

  4. Filterを設定します。

    ALTER TABLE test.retail.customers SET ROW FILTER row_filter ON ();

  5. SELECTします。0件でした。

    SELECT * FROM customers;
===

  6. ユーザを登録します。ユーザIDは伏せています。

    INSERT INTO test.retail.valid_user VALUES('***');

  7. SELECTします。表示されました。

    SELECT * FROM customers;
===
customer_id	name	address	phone
1	山田太郎	東京都渋谷区...	090-XXX-XXX
2	鈴木花子	大阪府北区...	080-YYY-YYYY

考察

今回、row filterとcolumn masksを使用しました。以前、試した「【Databricks】Dynamic Viewでの列・行レベルアクセスを試してみた」のDynamic Viewと比較して、Viewをわざわざ作成しなくても済むのは良いですね。

参考

2
1
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
2
1