行レベルのセキュリティを実装する
Row-Level Security (RLS) を使用すると、クエリを実行するユーザーの特性に基づいて、データベース テーブル内の行へのアクセスを制御できます。 テーブル全体へのアクセスを許可または拒否するテーブル レベルのアクセス許可とは異なり、RLS では行が動的にフィルター処理されるため、ユーザーはアクセスが許可されているデータのみが表示されます。
この機能は、複数のユーザーまたはテナントが同じテーブルを共有しているが、独自のデータのみを表示する必要がある場合に便利です。 営業担当者は自分の顧客レコードを確認し、マネージャーはチームのデータを確認し、地域のディレクターは自分の地域のすべてのデータを表示します。 フィルター処理は、アプリケーション コードの変更を必要とせずに自動的に行われます。
RLS コンポーネントについて
Row-Level セキュリティでは、セキュリティ述語とセキュリティ ポリシーという 2 つのコンポーネントが連携して使用されます。
セキュリティ述語は、各行に対して 1 (true) または 0 (false) を返すインライン テーブル値関数です。 現在のユーザー コンテキストと行の値を受け取り、そのユーザーに行を表示するかどうかを決定します。 これは、関数としてパッケージ化されたデータ アクセスのビジネス ロジックと考えてください。
セキュリティ ポリシーは、述語関数をテーブルにバインドし、フィルター処理の種類を指定します。 クエリ結果から承認されていない行をサイレント に除外する フィルター述語 を作成したり、未承認の挿入、更新、削除の操作を防止する 述語をブロック したりできます。
フィルター述語は、ユーザーがアクセスできない行を削除することで、 SELECT、 UPDATE、および DELETE ステートメントに影響します。 ユーザーはエラーを受け取りません。フィルター処理された結果セットだけが表示されます。 ブロック述語の動作は異なります。ユーザーが未承認の変更を試みるとエラーが発生します。
フィルター述語を作成する
まず、行アクセスを評価する述語関数を作成します。 この関数は、チェックする列値を表すパラメーターを受け取り、アクセスが許可されている場合は 1 行のテーブルを返します。
一般的なシナリオは、各行に TenantID 列があるマルチテナント アプリケーションです。
CREATE SCHEMA Security;
GO
CREATE FUNCTION Security.fn_TenantAccessPredicate(@TenantID int)
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN SELECT 1 AS fn_TenantAccessPredicate_Result
WHERE @TenantID = CAST(SESSION_CONTEXT(N'TenantID') AS int);
この関数は、行の TenantID がセッション コンテキストに格納されている値と一致するかどうかを確認します。 アプリケーションは、ユーザーが認証した後にこのコンテキストを設定します。
EXEC sp_set_session_context @key = N'TenantID', @value = 42;
セッション コンテキストではなくデータベース ユーザーに基づくシナリオでは、述語は現在のユーザーを参照できます。
CREATE FUNCTION Security.fn_SalesRepPredicate(@SalesRepID int)
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN SELECT 1 AS fn_SalesRepPredicate_Result
WHERE @SalesRepID = DATABASE_PRINCIPAL_ID()
OR IS_MEMBER('SalesManagers') = 1;
この述語を使用すると、営業担当者は自分のレコードを表示でき、 SalesManagers ロールのマネージャーはすべてのレコードを表示できます。
セキュリティ ポリシーを作成する
述語関数を定義したら、それらをテーブルに適用するセキュリティ ポリシーを作成します。
CREATE SECURITY POLICY TenantSecurityPolicy
ADD FILTER PREDICATE Security.fn_TenantAccessPredicate(TenantID)
ON dbo.Orders,
ADD FILTER PREDICATE Security.fn_TenantAccessPredicate(TenantID)
ON dbo.OrderDetails
WITH (STATE = ON);
このポリシーは、同じ述語関数を使用して Orders テーブルと OrderDetails テーブルの両方をフィルター処理します。 ユーザーがこれらのテーブルに対してクエリを実行すると、テナント コンテキストに一致する行のみが表示されます。
フィルター述語とブロック述語は、1 つのポリシーで組み合わせることができます。
CREATE SECURITY POLICY SalesSecurityPolicy
ADD FILTER PREDICATE Security.fn_SalesRepPredicate(SalesRepID)
ON dbo.CustomerAccounts,
ADD BLOCK PREDICATE Security.fn_SalesRepPredicate(SalesRepID)
ON dbo.CustomerAccounts AFTER INSERT,
ADD BLOCK PREDICATE Security.fn_SalesRepPredicate(SalesRepID)
ON dbo.CustomerAccounts AFTER UPDATE
WITH (STATE = ON);
ブロック述語を追加する理由 それらがないと、ユーザーは別の SalesRepID を持つ行を挿入し、作成したデータにアクセスできなくなる可能性があります。 ブロック述語を使用すると、ユーザーは表示できる行のみを挿入または更新できます。
階層型アクセス パターンを実装する
多くの組織では階層データ アクセスが必要です。マネージャーは、部下のデータを表示する必要があります。 RLS と管理階層テーブルを組み合わせることにより、これを実装できます。
階層を走査する関数を次に示します。
CREATE FUNCTION Security.fn_HierarchyPredicate(@OwnerID int)
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN
WITH EmployeeHierarchy AS (
SELECT EmployeeID, ManagerID
FROM dbo.Employees
WHERE EmployeeID = DATABASE_PRINCIPAL_ID()
UNION ALL
SELECT e.EmployeeID, e.ManagerID
FROM dbo.Employees e
INNER JOIN EmployeeHierarchy h ON e.ManagerID = h.EmployeeID
)
SELECT 1 AS fn_HierarchyPredicate_Result
WHERE @OwnerID IN (SELECT EmployeeID FROM EmployeeHierarchy);
この再帰的共通テーブル式 (CTE) は、現在のユーザーに報告する従業員の完全なチェーンを構築します。 述語を使用すると、その階層内の誰かが所有する任意の行にアクセスできます。
注
再帰述語は、大規模なデータセットのクエリ パフォーマンスに影響する可能性があります。 パフォーマンスを向上させるには、階層リレーションシップをキャッシュするか、再帰の深さを制限することを検討してください。
セキュリティ ポリシーの管理
セキュリティ ポリシーは、継続的なメンテナンスのために複数の管理操作をサポートします。 ポリシーは、削除せずに一時的に無効にすることができます。
ALTER SECURITY POLICY TenantSecurityPolicy
WITH (STATE = OFF);
既存のポリシーに新しい述語を追加できます。
ALTER SECURITY POLICY TenantSecurityPolicy
ADD FILTER PREDICATE Security.fn_TenantAccessPredicate(TenantID)
ON dbo.Shipments;
または、テーブルでフィルター処理が不要になった場合に述語を削除します。
ALTER SECURITY POLICY TenantSecurityPolicy
DROP FILTER PREDICATE ON dbo.Orders;
既存のセキュリティ ポリシーとその述語を表示するには:
SELECT p.name AS PolicyName,
p.is_enabled,
o.name AS TableName,
pred.predicate_definition
FROM sys.security_policies p
INNER JOIN sys.security_predicates pred ON p.object_id = pred.object_id
INNER JOIN sys.objects o ON pred.target_object_id = o.object_id;
ヒント
RLS の問題のトラブルシューティングを行うときは、セキュリティ ポリシーを一時的に無効にし、結果を比較します。 これは、RLS 述語またはその他のクエリ ロジックから予期しない結果が生じるかどうかを判断するのに役立ちます。
Row-Level セキュリティは、保護されたテーブルに対してクエリを実行するビュー、ストアド プロシージャ、およびその他のデータベース オブジェクトと透過的に連携します。 任意のパスを介してデータにアクセスするユーザーは、セキュリティ コンテキストに基づいて一貫してフィルター処理された結果を受け取ります。