行レベルのセキュリティを実装する
行レベルのセキュリティ (RLS) は暗号化を使用せず、データベース レベルで動作し、グループ メンバーシップまたは認可コンテキストに基づくセキュリティ ポリシーを使ってテーブルへのアクセスを制限します。 これは、機能的には WHERE
句と同じものです。
セキュリティ ポリシーは、インライン テーブル値関数を呼び出して、テーブル内の行へのアクセスを保護します。
述語により、ユーザーの属性に応じて、そのユーザーが関連情報にアクセスできるかどうかが決定されます。 テーブルに対してクエリを実行すると、セキュリティ ポリシーによって述語関数が適用されます。 RLS は、ビジネス要件次第で、WHERE CustomerId = 29
のように簡単にすることも、必要なだけ複雑にすることもできます。
行レベルのセキュリティでは 2 種類のセキュリティ ポリシーがサポートされています。
フィルター述語 - 述語に違反するデータ アクセスを制限します。
Access 定義 SELECT フィルター処理された行は表示できません。 UPDATE フィルター処理された行は更新できません。 DELETE フィルター処理された行は削除できません。 INSERT 適用不可。 ブロック述語 - 述語に違反するデータ変更を制限します。
Access 定義 AFTER INSERT ユーザーが述語に違反する値を含む行を挿入できないようにします。 AFTER UPDATE ユーザーが述語に違反する値に行を更新できないようにします。 BEFORE UPDATE ユーザーが現在述語に違反している行を更新できないようにします。 BEFORE DELETE 行が述語に違反している場合、削除操作をブロックします。
アクセス制御はデータベース レベルで構成されて適用されるため、アプリケーションの変更は最小限になります (行われる場合)。 また、ユーザーはテーブルに直接アクセスして、自分のデータのクエリを実行できます。
行レベルのセキュリティは、主に 3 つのステップで実装されます。
- アクセスを分離するユーザーまたはグループを作成します。
- 定義された述語に基づいて結果をフィルター処理するインライン テーブル値関数を作成します。
- テーブルのセキュリティ ポリシーを作成し、上で作成した関数を割り当てます。
次の T-SQL コマンドは、ユーザー アクセスがテナントごとに分離されるシナリオで RLS を使用する方法を示したものです。
-- Create supporting objects for this example
CREATE TABLE [Sales] (SalesID INT,
ProductID INT,
TenantName NVARCHAR(10),
OrderQtd INT,
UnitPrice MONEY)
GO
INSERT INTO [Sales] VALUES (1, 3, 'Tenant1', 5, 10.00);
INSERT INTO [Sales] VALUES (2, 4, 'Tenant1', 2, 57.00);
INSERT INTO [Sales] VALUES (3, 7, 'Tenant1', 4, 23.00);
INSERT INTO [Sales] VALUES (4, 2, 'Tenant2', 2, 91.00);
INSERT INTO [Sales] VALUES (5, 9, 'Tenant3', 5, 80.00);
INSERT INTO [Sales] VALUES (6, 1, 'Tenant3', 5, 35.00);
INSERT INTO [Sales] VALUES (7, 3, 'Tenant4', 8, 11.00);
-- View all the rows in the table
SELECT * FROM Sales;
次に、ユーザーを作成し、Sales テーブルへのアクセス権を付与します。 この例では、各ユーザーが特定のテナントを担当します。 TenantAdmin ユーザーは、すべてのテナントのデータを表示するアクセス権を持っています。
CREATE USER [TenantAdmin] WITH PASSWORD = '<strong password>'
GO
CREATE USER [Tenant1] WITH PASSWORD = '<strong password>'
GO
CREATE USER [Tenant2] WITH PASSWORD = '<strong password>'
GO
CREATE USER [Tenant3] WITH PASSWORD = '<strong password>'
GO
CREATE USER [Tenant4] WITH PASSWORD = '<strong password>'
GO
GRANT SELECT ON [Sales] TO [TenantAdmin]
GO
GRANT SELECT ON [Sales] TO [Tenant1]
GO
GRANT SELECT ON [Sales] TO [Tenant2]
GO
GRANT SELECT ON [Sales] TO [Tenant3]
GO
GRANT SELECT ON [Sales] TO [Tenant4]
GO
次に、新しいスキーマとインライン テーブル値関数を作成し、ユーザーに新しい関数へのアクセス権を付与します。 WHERE @TenantName = USER_NAME() OR USER_NAME() = 'TenantAdmin'
述語は、クエリを実行しているユーザー名が TenantName 列の値と一致するかどうかを評価します。
CREATE SCHEMA sec;
GO
--Create the filter predicate
CREATE FUNCTION sec.tvf_SecurityPredicatebyTenant(@TenantName AS NVARCHAR(10))
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN SELECT 1 AS result
WHERE @TenantName = USER_NAME() OR USER_NAME() = 'TenantAdmin';
GO
--Grant users access to inline table-valued function
GRANT SELECT ON sec.tvf_SecurityPredicatebyTenant TO [TenantAdmin]
GO
GRANT SELECT ON sec.tvf_SecurityPredicatebyTenant TO [Tenant1]
GO
GRANT SELECT ON sec.tvf_SecurityPredicatebyTenant TO [Tenant2]
GO
GRANT SELECT ON sec.tvf_SecurityPredicatebyTenant TO [Tenant3]
GO
GRANT SELECT ON sec.tvf_SecurityPredicatebyTenant TO [Tenant4]
GO
--Create security policy and add the filter predicate
CREATE SECURITY POLICY sec.SalesPolicy
ADD FILTER PREDICATE sec.tvf_SecurityPredicatebyTenant(TenantName) ON [dbo].[Sales]
WITH (STATE = ON);
GO
この時点で、アクセスをテストする準備ができました。
EXECUTE AS USER = 'TenantAdmin';
SELECT * FROM dbo.Sales;
REVERT;
EXECUTE AS USER = 'Tenant1';
SELECT * FROM dbo.Sales;
REVERT;
EXECUTE AS USER = 'Tenant2';
SELECT * FROM dbo.Sales;
REVERT;
EXECUTE AS USER = 'Tenant3';
SELECT * FROM dbo.Sales;
REVERT;
EXECUTE AS USER = 'Tenant4';
SELECT * FROM dbo.Sales;
REVERT;
TenantAdmin ユーザーには、すべての行が表示される必要があります。 Tenant1、Tenant2、Tenant3、Tenant4 の各ユーザーには、自分の行のみが表示される必要があります。
セキュリティ ポリシーを WITH (STATE = OFF);
に変更すると、すべての行がユーザーに表示されるようになることがわかります。
Note
攻撃者が特別に細工された WHERE
句を含むクエリを記述し、たとえば WHERE
条件が true の場合のゼロ除算エラーで例外を強制すると、情報漏えいのリスクがあります。 これは "サイドチャネル攻撃" と呼ばれます。 行レベルのセキュリティを使うときは、アドホック クエリを実行するユーザーの能力を制限することをお勧めします。
使用事例
行レベルのセキュリティは、次のような多くのシナリオに最適です。
- 行レベルで部門アクセスを分離する必要がある場合。
- 顧客のデータ アクセスをその顧客の会社に関連するデータだけに制限する必要がある場合。
- コンプライアンス目的でアクセスを制限する必要がある場合。
ベスト プラクティス
RLS を実装するときに考慮すべきベスト プラクティスをいくつか次に示します。
- 述語関数とセキュリティ ポリシーに対して別のスキーマを作成することをお勧めします。
- 可能な限り、述語関数では型変換を行わないようにします。
- パフォーマンスを最大化するには、述語関数の中で必要以上にテーブル結合と再帰を使わないようにします。