行レベルのセキュリティ
適用対象: 
SQL Server Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics Microsoft Fabric の AzureSQL 分析エンドポイント Microsoft Fabric のWarehouse SQL データベース
行レベルのセキュリティ (RLS) では、グループ メンバーシップや実行コンテキストを使用して、データベース テーブル内の行へのアクセスを制御することができます。
行レベルのセキュリティにより、アプリケーションでのセキュリティの設計やコーディングが簡略化されます。 RLS は、データ行アクセスに対して制限を実装するのに役立ちます。 たとえば、作業者が自分の部署に関連するデータ行にしかアクセスしないようにすることができます。 別の例として、顧客のデータ アクセスをその顧客の会社に関連するデータだけに制限することがあります。
アクセスの制限のロジックは、別のアプリケーション層のデータから離れてではなく、データベース層にあります。 任意の層からデータへのアクセスが試行されるたびに、データベース システムにはアクセス制限が適用されます。 これによりセキュリティ システムの外部からのアクセスが減り、そのシステムの信頼性と堅牢性が向上します。
CREATE SECURITY POLICY Transact-SQL ステートメントを使用して RLS を実装すると、インライン テーブル値関数として述語が作成されます。
行レベルのセキュリティは、SQL Server 2016 (13.x) に初めて導入されました。
Note
この記事では、SQL Server と Azure SQL プラットフォームに焦点を当てています。 Microsoft Fabric については、Microsoft Fabric の行レベルのセキュリティに関する説明を参照してください。
説明
行レベルのセキュリティ (RLS) で、次の 2 種類のセキュリティ述語がサポーされています。
フィルター述語は、読み取り操作 (
SELECT、UPDATE、およびDELETE) に可能な行を通知なしにフィルター処理します。BLOCK 述語は、述語関数に違反する書き込み操作 (
AFTER INSERT、AFTER UPDATE、BEFORE UPDATE、およびBEFORE DELETE) を明示的にブロックします。
テーブルの行レベルのデータへのアクセスは、インライン テーブル値関数として定義されたセキュリティ述語によって制限されます。 関数が呼び出され、セキュリティ ポリシーによって適用されます。 フィルター述語の場合、結果セットからフィルター処理されている行はアプリケーションによって認識されません。 すべての行がフィルター処理されると、null セットが返されます。 ブロック述語の場合、その述語に違反するすべての操作がエラーで失敗します。
フィルター述語は、ベース テーブルからのデータの読み取り中に適用されます。 SELECT、DELETE、UPDATE など、すべての取得操作に影響します。 ユーザーは、フィルター処理される行を選択または削除できません。 ユーザーは、フィルター処理される行を更新できません。 しかし、後でフィルター処理されるようにすれば行を更新できます。 ブロック述語はすべての書き込み操作に影響します。
AFTER INSERTとAFTER UPDATEの各述語は、ユーザーが行を述語に違反する値に更新できないようにします。BEFORE UPDATE述語は、ユーザーが現在述語に違反している行を更新できないようにします。BEFORE DELETE述語で削除操作を禁止できます。
フィルター述語とブロック述語およびセキュリティ ポリシーの動作は次のとおりです。
別のテーブルとの結合や関数の呼び出しを実行する述語関数を定義しても構いません。 セキュリティ ポリシーが
SCHEMABINDING = ON(既定) を使用して作成されている場合、結合または関数にはクエリからアクセスでき、それ以上のアクセス許可の確認をせずに、期待どおりに動作します。 セキュリティ ポリシーがSCHEMABINDING = OFFを使用して作成されている場合、ユーザーはターゲット テーブルをクエリするために、これらの追加のテーブルおよび関数に対するSELECT権限が必要になります。 述語関数で CLR スカラー値関数を呼び出す場合は、EXECUTE権限も必要になります。セキュリティ述語は定義されているが無効になっているテーブルに対してクエリを発行しても構いません。 フィルター処理またはブロックされている行には影響しません。
dboユーザー、db_ownerロールのメンバー、またはテーブルのオーナーが、セキュリティ ポリシーが定義され有効になっているテーブルに対してクエリを実行すると、セキュリティ ポリシーでの定義に従って行がフィルター処理またはブロックされます。スキーマ バインドされたセキュリティ ポリシーによってバインドされているテーブルのスキーマを変更しようとすると、エラーが発生します。 ただし、述語で参照されていない列は変更できます。
指定された操作に対する述語が既に定義されているテーブルに述語を追加しようとすると、エラーが発生します。 これは、述語が有効になっているかどうかを問わず発生します。
スキーマ バインドされたセキュリティ ポリシー内のテーブルで述語として使用されている関数を変更しようとすると、エラーが発生します。
重複しない述語が含まれる複数のアクティブなセキュリティ ポリシーを定義することはできます。
フィルター述語の動作は次のとおりです。
- テーブルの行をフィルター処理するセキュリティ ポリシーを定義します。 アプリケーションは、
SELECT、UPDATE、DELETE操作用にフィルター処理された行を認識しません。 これには、すべての行がフィルターで除去された状況も含まれます。アプリケーションでは、他の操作中にフィルター処理される場合でも、行のINSERTが可能です。
ブロック述語の動作は次のとおりです。
UPDATEのブロック述語は、BEFOREとAFTERの個別の操作に分けられています。 たとえば、ユーザーが行を更新して現在の値よりも大きい値を含めることを禁止することはできません。 このようなロジックが必要な場合は、DELETED および INSERTED 中間テーブルでトリガーを使用して、古い値と新しい値を一緒に参照する必要があります。述語関数で使用されている列が変更されていない場合、オプティマイザーは
AFTER UPDATEブロック述語をチェックしません。 例えば、Alice は、給与を変更して 100,000 より大きくすることはできません。 Alice は、述語内で参照される列が変更されていない場合に限り、給与が既に 100,000 を超えている従業員のアドレスを変更できます。BULK INSERTなどの Bulk API は変更されていません。 つまり、AFTER INSERTブロック述語は、通常の挿入操作と同様に一括挿入操作に適用されます。
ユース ケース
行レベル セキュリティ (RLS) をどのように使用するかの設計例を次に示します。
病院では、看護師が自分の患者のデータ行のみを見ることができるようなセキュリティ ポリシーを作成できます。
銀行では、従業員のビジネス部門や会社における役割に基づき、財務データの行へのアクセスを制限するポリシーを作成できます。
マルチ テナント アプリケーションでは、他のすべてのテナントの行から各テナントのデータ行を論理的に分離するポリシーを作成できます。 1 つのテーブルで多くのテナントのデータを保存することで効率性が高まります。 各テナントはそのデータ行のみ表示できます。
RLS フィルター述語は機能的には WHERE 句の追加と同等です。 述語はビジネス プラクティスの規定と同じくらいに洗練されたものであり、句は WHERE TenantId = 42と同じくらいに簡単です。
より形式的に表現すると、RLS はアクセス制御に基づく述語を採用しています。 柔軟で、集中管理された、述語ベースの評価を備えています。 述語は、管理者が適切に決定したメタデータや他の条件に基づくことができます。 述語は、ユーザーがその属性に基づいて適切にデータにアクセスできるかどうかを決定する条件として使用されます。 述語ベースのアクセス制御を使用することで、ラベルベースのアクセス制御を実装できます。
アクセス許可
セキュリティ ポリシーの作成、変更、削除には、ALTER ANY SECURITY POLICY アクセス許可が必要です。 セキュリティ ポリシーの作成や削除には、スキーマに対する ALTER アクセス許可が必要です。
また、追加される各述語に関しては次のアクセス許可も必要になります。
SELECTアクセス許可とREFERENCESアクセス許可 (述語として使用される関数)。REFERENCESアクセス許可 (ポリシーへのバインド対象となるテーブル)。REFERENCESアクセス許可 (対象テーブル内の、引数として使用されるすべての列)。
セキュリティ ポリシーは、データベースの dbo ユーザーを含めてすべてのユーザーに適用されます。 dbo ユーザーはセキュリティ ポリシーを変更したり削除したりできますが、セキュリティ ポリシーに加えた変更は監査することができます。 sysadmin や db_owner などの高い権限を持つユーザーがトラブルシューティングやデータ検証のためにすべての行を表示する必要がある場合は、これを許可するセキュリティ ポリシーを作成する必要があります。
SCHEMABINDING = OFF でセキュリティ ポリシーが作成された場合、ユーザーは、対象テーブルにクエリを実行するために、述語関数とその述語関数で使用される追加のテーブル、ビュー、または関数に対する SELECT アクセス許可または EXECUTE アクセス許可が必要です。 SCHEMABINDING = ON (既定) でセキュリティ ポリシーが作成された場合、ユーザーが対象テーブルに対してクエリを実行すると、これらの権限チェックは迂回されます。
ベスト プラクティス
RLS オブジェクト、述語関数、セキュリティ ポリシーに対して別のスキーマを作成することを強くお勧めします。 これにより、これらの特殊なオブジェクトに必要な権限をターゲット テーブルから分離できます。 マルチテナント データベースでは、さまざまなポリシーや述語関数をさらに分離する必要がある場合がありますが、すべてのケースで標準というわけではありません。
ALTER ANY SECURITY POLICY権限は、セキュリティ ポリシー マネージャーなどの高い権限を持つユーザーを対象としています。 セキュリティ ポリシー マネージャーには、保護しているテーブルではSELECT権限は必要とされません。潜在的なランタイム エラーを回避する述語関数では型変換しないようにします。
パフォーマンスの低下を避けるため、可能な場合は、述語関数では再帰しないようにします。 クエリ オプティマイザーでは直接再帰の検出を試みますが、間接再帰の検出は保証されません。 間接再帰では、2 番目の関数によって述語関数が呼び出されます。
パフォーマンスを最適化する述語関数で過剰にテーブルを結合しないようにします。
セッション固有の SET オプションに依存する述語ロジックは避けます。実際のアプリケーションで使用されることはほとんどありませんが、ロジックが特定のセッション固有の SET オプションに依存する述語関数では、ユーザーが任意のクエリを実行できる場合に情報が漏洩する可能性があります。 たとえば、文字列を datetime に暗黙的に変換する述語関数は、現在のセッションの SET DATEFORMAT オプションに基づいてさまざまな行をフィルター処理する可能性があります。 一般に、述語関数は次のルールに従う必要があります。
述語関数では、文字列を date、smalldatetime、datetime、datetime2、または datetimeoffset に暗黙的に変換しないようにする必要があります。逆の変換も同様です。これらの変換は、SET DATEFORMAT (Transact-SQL) オプションと SET LANGUAGE (Transact-SQL) オプションの影響を受けるためです。 代わりに、
CONVERT関数を使用し、スタイル パラメーターを明示的に指定します。述語関数は、週の最初の日の値に依存しないようにする必要があります。この値は SET DATEFIRST (Transact-SQL) オプションの影響を受けるためです。
述語関数は、エラー (オーバーフローやゼロ除算など) が発生した場合に
NULLを返す算術式や集計式に依存しないようにする必要があります。これは、この動作が SET ANSI_WARNINGS (Transact-SQL)、SET NUMERIC_ROUNDABORT (Transact-SQL)、SET ARITHABORT (Transact-SQL) の各オプションの影響を受けるためです。述語関数では、連結文字列を
NULLと比較しないようにする必要があります。この動作は、SET CONCAT_NULL_YIELDS_NULL (Transact-SQL) オプションの影響を受けるためです。
セキュリティに関する注意: サイドチャネル攻撃
悪意のあるセキュリティ ポリシー マネージャー
悪意のあるセキュリティ ポリシー マネージャーを監視することが重要です。これは、機密性の高い列にセキュリティ ポリシーを作成するのに十分なアクセス許可と、インライン テーブル値関数を作成したり変更したりするアクセス許可がそのセキュリティ ポリシー マネージャーにある場合、テーブルで選択アクセス許可を持つ別のユーザーと共謀し、サイドチャネル攻撃を使ってデータを推測するインライン テーブル値関数を作成して、悪意を持ってデータを流出させることも可能になるためです。 このような攻撃では、共謀 (または過剰な権限を悪意のあるユーザーに与えること) が必要で、ポリシーの変更が何度も必要になる可能性が高く (スキーマ バインドを解除するために述語を削除する権限を要求する)、インライン テーブル値関数が変更され、対象のテーブルに対して select ステートメントが繰り返し実行されることになります。 必要に応じてアクセス許可を制限し、疑わしいアクティビティを監視することをお勧めします。 行レベルのセキュリティに関連して、絶えず変化するポリシーやインライン テーブル値関数などのアクティビティを監視する必要があります。
慎重に作成されたクエリ
エラーを使用してデータを流出させる慎重に作成されたクエリを使用して、情報漏えいを引き起こすことが可能です。 たとえば、SELECT 1/(SALARY-100000) FROM PAYROLL WHERE NAME='John Doe'; で、悪意のあるユーザーに John doe さんの給与が正確に 100,000 ドルであることが知らされました。 悪意のあるユーザーが他のユーザーの給与を直接照会するような事態を防ぐためのセキュリティ述語がある場合でも、ゼロ除算の例外がクエリ結果として返されることで、悪意のあるユーザーによって知られてしまいます。
機能間の互換性
一般に、行レベルのセキュリティは機能間で予想どおりに機能します。 ただし、いくつかの例外があります。 ここでは、 SQL Server の他の特定の機能で行レベルのセキュリティを使用する場合の注意事項について説明します。
DBCC SHOW_STATISTICSからはフィルター処理されていないデータに対する統計がレポートされるため、セキュリティ ポリシーによって保護されていない場合に情報が漏洩する可能性があります。 このため、行レベルのセキュリティ ポリシーを持つテーブルの統計オブジェクトを表示するアクセス権は制限されます。 ユーザーはテーブルを所有しているか、sysadmin固定サーバー ロール、db_owner固定サーバー ロール、またはdb_ddladmin固定データベース ロールのメンバーであることが必要です。Filestream: RLS は Filestream と互換性がありません。
PolyBase: RLS は、Azure Synapse および SQL Server 2019 CU7 以降の外部テーブルでサポートされています。
メモリ最適化テーブル:メモリ最適化テーブルでセキュリティ述語として使用されるインライン テーブル値関数は、
WITH NATIVE_COMPILATIONオプションを使用して定義する必要があります。 このオプションを使用すると、メモリ最適化テーブルでサポートされていない言語機能が禁止され、作成時に該当するエラーが発行されます。 詳細については、「 メモリ最適化テーブルの行レベルのセキュリティ」を参照してください。インデックス付きビュー: 一般に、セキュリティ ポリシーはビューに対して作成でき、ビューはセキュリティ ポリシーによってバインドされたテーブルに作成できます。 ただし、インデックスによる行の参照はポリシーを回避するため、セキュリティ ポリシーが適用されたテーブルにインデックス付きビューを作成することはできません。
Change Data Capture: Change Data Capture では、
db_ownerのメンバーまたはテーブルで CDC が有効になっているときに指定された "ゲーティング" ロールのメンバーであるユーザーに、フィルター処理する必要があるすべての行が漏洩する可能性があります。 この関数を明示的にNULLに設定すると、すべてのユーザーが変更データにアクセスできるようになります。 実際には、db_ownerと、このゲーティング ロールのメンバーは、テーブルにセキュリティ ポリシーが存在する場合でも、テーブルのすべてのデータ変更を表示できます。Change Tracking: Change Tracking では、
SELECTとVIEW CHANGE TRACKINGの両方の権限を持つユーザーに、フィルター処理する必要がある行の主キーが漏洩する可能性があります。 実際のデータ値は漏洩しません。漏洩するのは、列 A が特定の主キーを持つ行に対して更新/挿入/削除されたという事実だけです。 これは、主キーに社会保障番号などの機密要素が含まれている場合に問題になります。 ただし、実際には、このCHANGETABLEは、最新のデータを取得するために、ほとんどの場合、元のテーブルと結合されます。フルテキスト検索: 次のフルテキスト検索関数やセマンティック検索関数を使用したクエリでは、行レベルのセキュリティを適用し、フィルター処理する必要のある行の主キーの漏洩を防ぐために余分な結合が発生するため、パフォーマンスが低下することが予想されます。
CONTAINSTABLE、FREETEXTTABLE、semantickeyphrasetable、semanticsimilaritydetailstable、semanticsimilaritytable列ストア インデックス: RLS は、クラスター化列ストア インデックスと非クラスター化列ストア インデックスの両方と互換性があります。 ただし、行レベル セキュリティによって関数が適用されるため、オプティマイザーではバッチ モードを使用しないようにクエリ プランが変更される可能性があります。
パーティション ビュー: パーティション ビューでブロック述語を定義することはできません。また、ブロック述語を使用するテーブルにパーティション ビューを作成することはできません。 フィルター述語はパーティション ビューと互換性があります。
テンポラル テーブル: テンポラル テーブルは RLS と互換性があります。 ただし、現在のテーブルのセキュリティ述語は、履歴テーブルに自動的にはレプリケートされません。 現在のテーブルと履歴テーブルの両方にセキュリティ ポリシーを適用するには、テーブルごとにセキュリティ述語を個別に追加する必要があります。
その他の制限事項:
- Microsoft Fabric と Azure Synapse Analytics は、フィルター述語のみをサポートします。 ブロック述語は、Microsoft Fabric と Azure Synapse Analytics では現在サポートされていません。
例
A. データベースに対して認証するユーザーのシナリオ
この例では、3 人のユーザーを作成し、6 行のテーブルを作成して設定します。 その後、インライン テーブル値関数とテーブルのセキュリティ ポリシーが作成されます。 さらにこの例では、select ステートメントがさまざまなユーザーに対してどのようにフィルター処理されるかが示されます。
異なるアクセス機能を示す 3 つのユーザー アカウントを作成します。
CREATE USER Manager WITHOUT LOGIN;
CREATE USER SalesRep1 WITHOUT LOGIN;
CREATE USER SalesRep2 WITHOUT LOGIN;
GO
データを保持するテーブルを作成します。
CREATE SCHEMA Sales
GO
CREATE TABLE Sales.Orders
(
OrderID int,
SalesRep nvarchar(50),
Product nvarchar(50),
Quantity smallint
);
そのテーブルに、各営業担当者の 3 つの注文を表示する、6 つのデータ行を設定します。
INSERT INTO Sales.Orders VALUES (1, 'SalesRep1', 'Valve', 5);
INSERT INTO Sales.Orders VALUES (2, 'SalesRep1', 'Wheel', 2);
INSERT INTO Sales.Orders VALUES (3, 'SalesRep1', 'Valve', 4);
INSERT INTO Sales.Orders VALUES (4, 'SalesRep2', 'Bracket', 2);
INSERT INTO Sales.Orders VALUES (5, 'SalesRep2', 'Wheel', 5);
INSERT INTO Sales.Orders VALUES (6, 'SalesRep2', 'Seat', 5);
-- View the 6 rows in the table
SELECT * FROM Sales.Orders;
各ユーザーに、テーブルに対する読み取りアクセス権を付与します。
GRANT SELECT ON Sales.Orders TO Manager;
GRANT SELECT ON Sales.Orders TO SalesRep1;
GRANT SELECT ON Sales.Orders TO SalesRep2;
GO
新しいスキーマと、インライン テーブル値関数を作成します。 SalesRep 列内の行がクエリを実行しているユーザーと同じである場合 (@SalesRep = USER_NAME())、またはクエリを実行しているユーザーがマネージャー ユーザーである場合 (USER_NAME() = 'Manager')、関数は 1 を返します このユーザー定義のテーブル値関数の例は、次の手順で作成したセキュリティ ポリシーのフィルターとして使用すると役立ちます。
CREATE SCHEMA Security;
GO
CREATE FUNCTION Security.tvf_securitypredicate(@SalesRep AS nvarchar(50))
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN SELECT 1 AS tvf_securitypredicate_result
WHERE @SalesRep = USER_NAME() OR USER_NAME() = 'Manager';
GO
フィルター述語として関数を追加するセキュリティ ポリシーを作成します。 ポリシーを有効にするには、STATE を ON に設定する必要があります。
CREATE SECURITY POLICY SalesFilter
ADD FILTER PREDICATE Security.tvf_securitypredicate(SalesRep)
ON Sales.Orders
WITH (STATE = ON);
GO
SELECT 関数に対して tvf_securitypredicate アクセス許可を許可します。
GRANT SELECT ON Security.tvf_securitypredicate TO Manager;
GRANT SELECT ON Security.tvf_securitypredicate TO SalesRep1;
GRANT SELECT ON Security.tvf_securitypredicate TO SalesRep2;
それでは、各ユーザーとして Sales.Orders テーブルから選択することで、フィルター述語をテストしてみましょう。
EXECUTE AS USER = 'SalesRep1';
SELECT * FROM Sales.Orders;
REVERT;
EXECUTE AS USER = 'SalesRep2';
SELECT * FROM Sales.Orders;
REVERT;
EXECUTE AS USER = 'Manager';
SELECT * FROM Sales.Orders;
REVERT;
マネージャーには、6 つの行すべてが表示されるはずです。 Sales1 と Sales2 のユーザーには、それぞれの売上のみ表示されます。
セキュリティ ポリシーを変更してポリシーを無効にします。
ALTER SECURITY POLICY SalesFilter
WITH (STATE = OFF);
これで、Sales1 と Sales2 のユーザーに 6 つの行すべてが表示されます。
このサンプル実行からリソースをクリーンアップするために SQL データベースに接続:
DROP USER SalesRep1;
DROP USER SalesRep2;
DROP USER Manager;
DROP SECURITY POLICY SalesFilter;
DROP TABLE Sales.Orders;
DROP FUNCTION Security.tvf_securitypredicate;
DROP SCHEMA Security;
DROP SCHEMA Sales;
B. Azure Synapse 外部テーブルに対して行レベルのセキュリティを使用する場合のシナリオ
この簡単な例では、3 人のユーザーと 6 行の外部テーブルを作成します。 その後、インライン テーブル値関数と外部テーブルのセキュリティ ポリシーが作成されます。 この例では、select ステートメントがさまざまなユーザーに対してどのようにフィルター処理されるかが示されます。
前提条件
- 専用 SQL プールが必要です。 専用 SQL プールの作成に関する記事を参照してください
- 専用 SQL プールをホストするサーバーは Microsoft Entra ID (以前の Azure Active Directory) に登録されている必要があり、
Storage Blog Data Contributorアクセス許可を持つ Azure ストレージ アカウントを持っている必要があります。 Azure SQL Database のサーバー用の仮想ネットワーク サービス エンドポイントと規則の使用のの手順に従います。 - 自分の Azure Storage アカウントにファイル システムを作成します。 Azure Storage Explorer を使用して自分のストレージ アカウントを表示します。 コンテナーを右クリックし、[ファイル システムの作成] を選択します。
前提条件を満たしたら、さまざまなアクセス機能を示す 3 つのユーザー アカウントを作成します。
--run in master
CREATE LOGIN Manager WITH PASSWORD = '<user_password>'
GO
CREATE LOGIN Sales1 WITH PASSWORD = '<user_password>'
GO
CREATE LOGIN Sales2 WITH PASSWORD = '<user_password>'
GO
--run in both the master database and in your dedicated SQL pool database
CREATE USER Manager FOR LOGIN Manager;
CREATE USER Sales1 FOR LOGIN Sales1;
CREATE USER Sales2 FOR LOGIN Sales2 ;
データを保持するテーブルを作成します。
CREATE TABLE Sales
(
OrderID int,
SalesRep sysname,
Product varchar(10),
Qty int
);
そのテーブルに、各営業担当者の 3 つの注文を表示する、6 つのデータ行を設定します。
INSERT INTO Sales VALUES (1, 'Sales1', 'Valve', 5);
INSERT INTO Sales VALUES (2, 'Sales1', 'Wheel', 2);
INSERT INTO Sales VALUES (3, 'Sales1', 'Valve', 4);
INSERT INTO Sales VALUES (4, 'Sales2', 'Bracket', 2);
INSERT INTO Sales VALUES (5, 'Sales2', 'Wheel', 5);
INSERT INTO Sales VALUES (6, 'Sales2', 'Seat', 5);
-- View the 6 rows in the table
SELECT * FROM Sales;
作成した Sales テーブルから Azure Synapse 外部テーブルを作成します。
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<user_password>';
CREATE DATABASE SCOPED CREDENTIAL msi_cred WITH IDENTITY = 'Managed Service Identity';
CREATE EXTERNAL DATA SOURCE ext_datasource_with_abfss WITH (TYPE = hadoop, LOCATION = 'abfss://<file_system_name@storage_account>.dfs.core.windows.net', CREDENTIAL = msi_cred);
CREATE EXTERNAL FILE FORMAT MSIFormat WITH (FORMAT_TYPE=DELIMITEDTEXT);
CREATE EXTERNAL TABLE Sales_ext WITH (LOCATION='<your_table_name>', DATA_SOURCE=ext_datasource_with_abfss, FILE_FORMAT=MSIFormat, REJECT_TYPE=Percentage, REJECT_SAMPLE_VALUE=100, REJECT_VALUE=100)
AS SELECT * FROM sales;
作成した 外部テーブル Sales_ext で、3 人のユーザーに SELECT を付与します。
GRANT SELECT ON Sales_ext TO Sales1;
GRANT SELECT ON Sales_ext TO Sales2;
GRANT SELECT ON Sales_ext TO Manager;
新しいスキーマとインライン テーブル値関数を作成します。後者は例 A で完了しているかもしれません。SalesRep 列内の行がクエリを実行しているユーザーと同じである場合 (@SalesRep = USER_NAME())、またはクエリを実行しているユーザーが Manager ユーザーである場合 (USER_NAME() = 'Manager')、関数は 1 を返します。
CREATE SCHEMA Security;
GO
CREATE FUNCTION Security.fn_securitypredicate(@SalesRep AS sysname)
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN SELECT 1 AS fn_securitypredicate_result
WHERE @SalesRep = USER_NAME() OR USER_NAME() = 'Manager';
インライン テーブル値関数をフィルター述語として使用して外部テーブル上にセキュリティ ポリシーを作成します。 ポリシーを有効にするには、STATE を ON に設定する必要があります。
CREATE SECURITY POLICY SalesFilter_ext
ADD FILTER PREDICATE Security.fn_securitypredicate(SalesRep)
ON dbo.Sales_ext
WITH (STATE = ON);
それでは、Sales_ext 外部テーブルから選択することで、フィルター述語をテストしましょう。 各ユーザー Sales1、Sales2、Manager としてサインインします。 次のコマンドを各ユーザーとして実行します。
SELECT * FROM Sales_ext;
Managerには、6 つの行すべてが表示されるはずです。 Sales1 と Sales2 のユーザーには、各自の売上のみ表示されます。
セキュリティ ポリシーを変更してポリシーを無効にします。
ALTER SECURITY POLICY SalesFilter_ext
WITH (STATE = OFF);
これで、Sales1 と Sales2 のユーザーに 6 つの行すべてが表示されます。
このサンプル実行からリソースをクリーンアップするために Azure Synapse データベースに接続します。
DROP USER Sales1;
DROP USER Sales2;
DROP USER Manager;
DROP SECURITY POLICY SalesFilter_ext;
DROP TABLE Sales;
DROP EXTERNAL TABLE Sales_ext;
DROP EXTERNAL DATA SOURCE ext_datasource_with_abfss ;
DROP EXTERNAL FILE FORMAT MSIFormat;
DROP DATABASE SCOPED CREDENTIAL msi_cred;
DROP MASTER KEY;
リソースをクリーンアップするために論理サーバー master データベースに接続します。
DROP LOGIN Sales1;
DROP LOGIN Sales2;
DROP LOGIN Manager;
C: 中間層アプリケーションからデータベースに接続するユーザーのシナリオ
Note
この例の場合、Microsoft Fabric と Azure Synapse では、ブロック述語機能が現在サポートされていないため、正しくないユーザー ID の行の挿入がブロックされません。
この例では、アプリケーション ユーザー (またはテナント) が同じ SQL Server ユーザー (アプリケーション) を共有している場合、中間層のアプリケーションが接続フィルタリングを実装する方法を示します。 アプリケーションは、データベースに接続した後、 SESSION_CONTEXT で現在のアプリケーション ユーザー ID を設定します。その後、セキュリティ ポリシーによって、この ID に対して表示すべきでない行が透過的にフィルター処理されます。また、ユーザーが間違ったユーザー ID の行を挿入できないようにします。 その他のアプリケーションの変更は必要ありません。
データを保持するテーブルを作成します。
CREATE TABLE Sales (
OrderId int,
AppUserId int,
Product varchar(10),
Qty int
);
そのテーブルに、各アプリケーション ユーザーの 3 つの注文を表示する、6 つのデータ行を設定します。
INSERT Sales VALUES
(1, 1, 'Valve', 5),
(2, 1, 'Wheel', 2),
(3, 1, 'Valve', 4),
(4, 2, 'Bracket', 2),
(5, 2, 'Wheel', 5),
(6, 2, 'Seat', 5);
アプリケーションが接続に使用する権限の低いユーザーを作成します。
-- Without login only for demo
CREATE USER AppUser WITHOUT LOGIN;
GRANT SELECT, INSERT, UPDATE, DELETE ON Sales TO AppUser;
-- Never allow updates on this column
DENY UPDATE ON Sales(AppUserId) TO AppUser;
SESSION_CONTEXT() に格納されたアプリケーション ユーザー ID を使用して行をフィルター処理する、新しいスキーマと述語関数を作成します。
CREATE SCHEMA Security;
GO
CREATE FUNCTION Security.fn_securitypredicate(@AppUserId int)
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN SELECT 1 AS fn_securitypredicate_result
WHERE
DATABASE_PRINCIPAL_ID() = DATABASE_PRINCIPAL_ID('AppUser')
AND CAST(SESSION_CONTEXT(N'UserId') AS int) = @AppUserId;
GO
Salesのフィルター述語およびブロック述語としてこの関数を追加するセキュリティ ポリシーを作成します。 BEFORE UPDATE と BEFORE DELETE は既にフィルター処理されているため、ブロック述語に必要なのは AFTER INSERT だけです。また、以前に設定した列アクセス許可セットにより、AppUserId 列は他の値に更新できないため、AFTER UPDATE は不要です。
CREATE SECURITY POLICY Security.SalesFilter
ADD FILTER PREDICATE Security.fn_securitypredicate(AppUserId)
ON dbo.Sales,
ADD BLOCK PREDICATE Security.fn_securitypredicate(AppUserId)
ON dbo.Sales AFTER INSERT
WITH (STATE = ON);
SESSION_CONTEXT() で異なるユーザー ID を設定した後、Sales テーブルから選択することで、接続フィルタリングをシミュレートできます。 実際には、アプリケーションが、接続を開いた後に SESSION_CONTEXT() で現在のユーザー ID を設定します。 @read_only パラメーターを 1 に 設定すると 、接続が閉じられる (接続プールに返される) まで値は変更されなくなります。
EXECUTE AS USER = 'AppUser';
EXEC sp_set_session_context @key=N'UserId', @value=1;
SELECT * FROM Sales;
GO
/* Note: @read_only prevents the value from changing again until the connection is closed (returned to the connection pool)*/
EXEC sp_set_session_context @key=N'UserId', @value=2, @read_only=1;
SELECT * FROM Sales;
GO
INSERT INTO Sales VALUES (7, 1, 'Seat', 12); -- error: blocked from inserting row for the wrong user ID
GO
REVERT;
GO
データベース リソースをクリーンアップします。
DROP USER AppUser;
DROP SECURITY POLICY Security.SalesFilter;
DROP TABLE Sales;
DROP FUNCTION Security.fn_securitypredicate;
DROP SCHEMA Security;
D. セキュリティ述語にルックアップ テーブルを使用する場合のシナリオ
この例では、ファクト テーブルでユーザー識別子を指定するのではなく、ユーザー識別子とフィルター処理される値の間のリンクにルックアップ テーブルを使用します。 3 人のユーザーが作成され、6 行のファクト テーブル Sample.Sales と 2 行のルックアップ テーブルが作成され、設定されます。 次に、インラインのテーブル値関数が作成されます。これにより、ファクト テーブルがルックアップが結合され、ユーザー識別子と、テーブルのセキュリティ ポリシーが取得されます。 さらにこの例では、select ステートメントがさまざまなユーザーに対してどのようにフィルター処理されるかが示されます。
異なるアクセス機能を示す 3 つのユーザー アカウントを作成します。
CREATE USER Manager WITHOUT LOGIN;
CREATE USER Sales1 WITHOUT LOGIN;
CREATE USER Sales2 WITHOUT LOGIN;
データを保持するためのSample スキーマとファクト テーブル Sample.Sales を作成します。
CREATE SCHEMA Sample;
GO
CREATE TABLE Sample.Sales
(
OrderID int,
Product varchar(10),
Qty int
);
6 行のデータで Sample.Sales を設定します。
INSERT INTO Sample.Sales VALUES (1, 'Valve', 5);
INSERT INTO Sample.Sales VALUES (2, 'Wheel', 2);
INSERT INTO Sample.Sales VALUES (3, 'Valve', 4);
INSERT INTO Sample.Sales VALUES (4, 'Bracket', 2);
INSERT INTO Sample.Sales VALUES (5, 'Wheel', 5);
INSERT INTO Sample.Sales VALUES (6, 'Seat', 5);
-- View the 6 rows in the table
SELECT * FROM Sample.Sales;
ルックアップ データ (この場合は Salesrep と Product のリレーションシップ) を保持するテーブルを作成します。
CREATE TABLE Sample.Lk_Salesman_Product
( Salesrep sysname,
Product varchar(10)
) ;
ルックアップ テーブルにサンプル データを入力し、1 つの Product を各営業担当者にリンクします。
INSERT INTO Sample.Lk_Salesman_Product VALUES ('Sales1', 'Valve');
INSERT INTO Sample.Lk_Salesman_Product VALUES ('Sales2', 'Wheel');
-- View the 2 rows in the table
SELECT * FROM Sample.Lk_Salesman_Product;
各ユーザーに、ファクト テーブルに対する読み取りアクセス権を付与します。
GRANT SELECT ON Sample.Sales TO Manager;
GRANT SELECT ON Sample.Sales TO Sales1;
GRANT SELECT ON Sample.Sales TO Sales2;
新しいスキーマと、インライン テーブル値関数を作成します。 この関数から 1 が返されるのは、ユーザーがファクト テーブル Sample.Sales にクエリを実行し、テーブル Lk_Salesman_Product の SalesRep 列が、ファクト テーブルの Product 列に結合されたときにクエリ (@SalesRep = USER_NAME()) を実行したユーザーと同じである場合、またはクエリを実行するユーザーが Manager ユーザー (USER_NAME() = 'Manager') である場合です。
CREATE SCHEMA Security ;
GO
CREATE FUNCTION Security.fn_securitypredicate
(@Product AS varchar(10))
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN ( SELECT 1 as Result
FROM Sample.Sales f
INNER JOIN Sample.Lk_Salesman_Product s
ON s.Product = f.Product
WHERE ( f.product = @Product
AND s.SalesRep = USER_NAME() )
OR USER_NAME() = 'Manager'
) ;
フィルター述語として関数を追加するセキュリティ ポリシーを作成します。 ポリシーを有効にするには、STATE を ON に設定する必要があります。
CREATE SECURITY POLICY SalesFilter
ADD FILTER PREDICATE Security.fn_securitypredicate(Product)
ON Sample.Sales
WITH (STATE = ON) ;
SELECT 関数に対して fn_securitypredicate アクセス許可を許可します。
GRANT SELECT ON Security.fn_securitypredicate TO Manager;
GRANT SELECT ON Security.fn_securitypredicate TO Sales1;
GRANT SELECT ON Security.fn_securitypredicate TO Sales2;
それでは、各ユーザーとして Sample.Sales テーブルから選択することで、フィルター述語をテストしてみましょう。
EXECUTE AS USER = 'Sales1';
SELECT * FROM Sample.Sales;
-- This will return just the rows for Product 'Valve' (as specified for 'Sales1' in the Lk_Salesman_Product table above)
REVERT;
EXECUTE AS USER = 'Sales2';
SELECT * FROM Sample.Sales;
-- This will return just the rows for Product 'Wheel' (as specified for 'Sales2' in the Lk_Salesman_Product table above)
REVERT;
EXECUTE AS USER = 'Manager';
SELECT * FROM Sample.Sales;
-- This will return all rows with no restrictions
REVERT;
Managerには、6 つの行すべてが表示されるはずです。 Sales1 と Sales2 のユーザーには、それぞれの売上のみ表示されます。
セキュリティ ポリシーを変更してポリシーを無効にします。
ALTER SECURITY POLICY SalesFilter
WITH (STATE = OFF);
これで、Sales1 と Sales2 のユーザーに 6 つの行すべてが表示されます。
このサンプル実行からリソースをクリーンアップするために SQL データベースに接続:
DROP USER Sales1;
DROP USER Sales2;
DROP USER Manager;
DROP SECURITY POLICY SalesFilter;
DROP FUNCTION Security.fn_securitypredicate;
DROP TABLE Sample.Sales;
DROP TABLE Sample.Lk_Salesman_Product;
DROP SCHEMA Security;
DROP SCHEMA Sample;
E. Microsoft Fabric の行レベルのセキュリティ シナリオ
Microsoft Fabric で行レベルセキュリティ ウェアハウスと SQL 分析エンドポイントを示すことができます。
次の例では、Microsoft Fabric のウェアハウスで動作するサンプル テーブルを作成しますが、SQL 分析エンドポイントでは既存のテーブルを使用します。 SQL 分析エンドポイントでは、CREATE TABLE は使用できませんが、CREATE SCHEMA、CREATE FUNCTION、CREATE SECURITY POLICY は使用できます。
この例では、最初にスキーマ sales、テーブル sales.Orders を作成します。
CREATE SCHEMA sales;
GO
-- Create a table to store sales data
CREATE TABLE sales.Orders (
SaleID INT,
SalesRep VARCHAR(100),
ProductName VARCHAR(50),
SaleAmount DECIMAL(10, 2),
SaleDate DATE
);
-- Insert sample data
INSERT INTO sales.Orders (SaleID, SalesRep, ProductName, SaleAmount, SaleDate)
VALUES
(1, 'Sales1@contoso.com', 'Smartphone', 500.00, '2023-08-01'),
(2, 'Sales2@contoso.com', 'Laptop', 1000.00, '2023-08-02'),
(3, 'Sales1@contoso.com', 'Headphones', 120.00, '2023-08-03'),
(4, 'Sales2@contoso.com', 'Tablet', 800.00, '2023-08-04'),
(5, 'Sales1@contoso.com', 'Smartwatch', 300.00, '2023-08-05'),
(6, 'Sales2@contoso.com', 'Gaming Console', 400.00, '2023-08-06'),
(7, 'Sales1@contoso.com', 'TV', 700.00, '2023-08-07'),
(8, 'Sales2@contoso.com', 'Wireless Earbuds', 150.00, '2023-08-08'),
(9, 'Sales1@contoso.com', 'Fitness Tracker', 80.00, '2023-08-09'),
(10, 'Sales2@contoso.com', 'Camera', 600.00, '2023-08-10');
Security スキーマ、関数 Security.tvf_securitypredicate、セキュリティ ポリシー SalesFilter を作成します。
-- Creating schema for Security
CREATE SCHEMA Security;
GO
-- Creating a function for the SalesRep evaluation
CREATE FUNCTION Security.tvf_securitypredicate(@SalesRep AS nvarchar(50))
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN SELECT 1 AS tvf_securitypredicate_result
WHERE @SalesRep = USER_NAME() OR USER_NAME() = 'manager@contoso.com';
GO
-- Using the function to create a Security Policy
CREATE SECURITY POLICY SalesFilter
ADD FILTER PREDICATE Security.tvf_securitypredicate(SalesRep)
ON sales.Orders
WITH (STATE = ON);
GO
セキュリティ ポリシーを適用して関数を作成すると、ユーザー Sales1@contoso.com と Sales2@contoso.com は sales.Orders テーブル内の独自のデータのみを表示できるようになります。この列 SalesRep は、組み込み関数 USER_NAME によって返される独自のユーザー名と等しくなります。 Fabric ユーザー manager@contoso.com は、sales.Orders テーブル内のすべてのデータを表示できます。
関連するコンテンツ
- CREATE SECURITY POLICY (Transact-SQL)
- ALTER SECURITY POLICY (Transact-SQL)
- DROP SECURITY POLICY (Transact-SQL)
- CREATE FUNCTION (Transact-SQL)
- SESSION_CONTEXT (Transact-SQL)
- sp_set_session_context (Transact-SQL)
- sys.security_policies (Transact-SQL)
- sys.security_predicates (Transact-SQL)
- ユーザー定義関数の作成 (データベース エンジン)
- GRANT オブジェクトのアクセス許可 (Transact-SQL)