【３分で流し読み】SQL Server / Azure SQLの行レベルのセキュリティ（RLS）でできることを理解する

こんばんは。今日は、SQL Server / Azure SQLの、行レベルのセキュリティ機能について少し調べる機会があったので、機能の概要を簡単にまとめておきたいと思います。

それではまいります。

行レベルのセキュリティとは

行レベルのセキュリティ – SQL Server | Microsoft Docs

• SQL Server 2016で追加された新機能
• データベーステーブル内のアクセスを行レベルで制御することができる（ある行は、ユーザAには見せるけど、ユーザBには見せない、など）
• 読み取り時のフィルター、書き込み制限の２通りの制御が行える
• アクセス管理のロジックをデータベース側で集中管理できるので、アプリケーションを簡素化できる。マルチテナントアプリケーションのシナリオなどに最適。
• セキュリティポリシーと、インラインテーブル値関数の形で定義した述語によって実装する（後述）

考慮事項

一番気になるのはパフォーマンスへの影響でしょうか。これについては、公式ドキュメントや公式ブログにいくつかプラクティスの言及があるのでまとめておきます。

「述語関数の書き方がカギ」と理解しました。

行レベルのセキュリティ – SQL Server | Microsoft Docs

• 述語関数でなるべく再帰関数を利用しない
• 述語関数で結合を多用しない

Row-Level Security: Performance and common patterns – Microsoft Tech Community

• 基本的にはビューと遜色ない
• 述語関数の中で参照するテーブルがあれば、適切なインデックスを貼っておく

Row-Level Security for Middle-Tier Apps – Using Disjunctions in the Predicate – Microsoft Tech Community

• 述語関数中での論理和(OR)は性能劣化を引き起こす可能性がある（SeekがScanに変わることに起因）

その他の制限やベストプラクティスについては以下にまとまっています。

行レベルのセキュリティ – SQL Server | Microsoft Docs

実際に使ってみた

公式Docにサンプルコードがあったので、これをなぞってみました。

行レベルのセキュリティ – SQL Server | Microsoft Docs

ユーザを作成

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  
    );

-- データ投入
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;

Sales.OrdersテーブルにSELECT権限を付与

GRANT SELECT ON Sales.Orders TO Manager;  
GRANT SELECT ON Sales.Orders TO SalesRep1;  
GRANT SELECT ON Sales.Orders TO SalesRep2; 
GO

新しいスキーマにインラインテーブル値関数を作成。ここでは、アクセス制御を行う条件を記述しています。

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

セキュリティポリシーを作成

CREATE SECURITY POLICY SalesFilter  
ADD FILTER PREDICATE Security.tvf_securitypredicate(SalesRep)
ON Sales.Orders
WITH (STATE = ON);  
GO

なるほど、このセキュリティポリシーの中で、どのテーブルの列に対してTVFの判定を行うかを定義しているのですね。また、読み取り制限を行いたい場合はFILTER、書き込み禁止の場合はBLOCKを指定します。

CREATE SECURITY POLICY (Transact-SQL) – SQL Server | Microsoft Docs

[ FILTER | BLOCK ]
対象のテーブルにバインドされている関数のセキュリティの述語の型。 FILTER 述語は、読み取り操作が可能な行を通知なしにフィルター処理します。 BLOCK 述語は、その述語関数に違反する書き込み操作を明示的に禁止します。

[ STATE = { ON | OFF } ]
セキュリティ ポリシーによるターゲット テーブルに対するセキュリティ述語の実施を有効または無効にします。 指定しないと、作成されているセキュリティ ポリシーは有効になります。

テーブル値関数に対してSELECT権限を付与。これで準備完了。

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;

なるほど、行レベルのセキュリティの動作について、イメージが深まりました。

ちなみに、今回は、テーブルにユーザ情報が保持されていたので、テーブル値関数の記述も比較的シンプルでしたが、そうでない場合は以下のようにルックアップテーブルを使って条件を記述することになりそうでした。

行レベルのセキュリティ – SQL Server | Microsoft Docs

以上、行レベルのセキュリティの概要についての簡単なまとめでした。

おしまい