SQL Database v12 ではプレビュー機能として先行実装されていますが、SQL Server 2016 では行レベルセキュリティーの機能が使えるようになります。
詳細については Row-Level Security で。
ざっと眺めた感じでは、SQL Database と同様の動作になるようですね。
動作確認で使用するテーブルとユーザーを以下のクエリで作成します。
-- テーブルの作成とデータ投入 CREATE TABLE Sales( [OrderID] int primary key clustered, [SalesRep] nvarchar(50), [Product] nvarchar(200), [Qty] int) GO INSERT INTO Sales VALUES (1,N'ユーザーA', N'製品A',100) ,(2,N'ユーザーA', N'製品B',90) ,(3,N'ユーザーB', N'製品C',120) ,(4,N'ユーザーA', N'製品D',130) ,(5,N'ユーザーB', N'製品E',150) GO -- ユーザーの作成 CREATE USER ユーザーA WITHOUT LOGIN CREATE USER ユーザーB WITHOUT LOGIN CREATE USER マネージャー WITHOUT LOGIN GO -- 権限の付与 GRANT SELECT,INSERT,UPDATE,DELETE ON Sales TO ユーザーA GRANT SELECT,INSERT,UPDATE,DELETE ON Sales TO ユーザーB GRANT SELECT,INSERT,UPDATE,DELETE ON Sales TO マネージャー GRANT SHOWPLAN TO ユーザーA GRANT SHOWPLAN TO ユーザーB GRANT SHOWPLAN TO マネージャー
あとの流れは SQL Database と同じです。
必須ではありませんが、行レベルセキュリティ関連のオブジェクトを格納するためのスキーマを作成します。
CREATE SCHEMA rls GO
次にセキュリティ設定のための関数の作成とテーブルへのポリシーの割り当てを実施します。。
今回は、「マネージャー」というユーザーであればすべてのデータを閲覧することができるが、それ以外については SalesRep が自分のユーザー名のデータのみ表示可能という設定にしています。
CREATE FUNCTION rls.fn_SalesSecurytiPredicate(@SalesRep as nvarchar(50)) RETURNS TABLE WITH SCHEMABINDING RETURN SELECT 1 AS fn_SalesSecurytiPredicate_result WHERE @SalesRep = USER_NAME() OR USER_NAME() = N'マネージャー' GO CREATE SECURITY POLICY rls.SalesFilter ADD FILTER PREDICATE rls.fn_SalesSecurytiPredicate(SalesRep) ON dbo.Sales WITH (STATE = ON) GO
設定が終わったら、以下のクエリで動作を確認してみます。
-- 行レベルセキュリティを設定後にデータの確認 SELECT * FROM Sales -- ユーザーA で検索 EXECUTE AS USER = N'ユーザーA' SELECT USER_NAME() AS username, * FROM Sales REVERT -- ユーザーB で検索 EXECUTE AS USER = N'ユーザーB' SELECT USER_NAME() AS username, * FROM Sales REVERT -- マネージャーで検索 EXECUTE AS USER = N'マネージャー' SELECT USER_NAME() AS username, * FROM Sales REVERT
同じクエリを実行した結果、ユーザーによって取得されているデータが変わっているのが確認できます。
実行プランについては、SQL Database 同様にフィルター操作で制御されているようですね。
また、こちらも SQL Database と同様の動作ですが、INSERT についてはフィルターをすることができません。
以下のクエリでは、「ユーザー B」で「ユーザー A」のデータを INSERT していますが、INSERT 時には行レベルセキュリティのフィルターは動作しないため、「自分が参照できないデータの INSERT」が実行できてしまいます。
-- ユーザーB で ユーザーA のデータを INSRET EXECUTE AS USER = N'ユーザーB' INSERT INTO Sales VALUES (6,N'ユーザーA', N'製品A',100) SELECT USER_NAME() AS username, * FROM Sales REVERT GO
このような状態を回避したい場合には、Row-Level Security: Blocking unauthorized INSERTs で紹介されているように制約 (トリガーでもいいと思いますが) での制御が必要となってきます。
今回のテスト用クエリであれば、行レベルセキュリティで使用しているセキュリティー用の述語関数を使用して、以下のような制約を設定します。
-- RLS の述語関数を使用した制約用の関数を作成 CREATE FUNCTION rls.fn_SalesRepAccessPredicateScalar(@SalesRep nvarchar(50)) RETURNS bit AS BEGIN IF EXISTS(SELECT 1 FROM rls.fn_SalesSecurytiPredicate(@SalesRep)) RETURN 1 RETURN 0 END GO -- 制約の追加 ALTER TABLE Sales WITH NOCHECK ADD CONSTRAINT chk_blocking_Sales CHECK(rls.fn_SalesRepAccessPredicateScalar(SalesRep) = 1) GO
これで述語関数で許可されているデータのみ INSERT ができるようになります。
以下のようなクエリを実行した場合、「ユーザー B で ユーザー A のデータを挿入」は制約により失敗しますが、「ユーザー B でユーザー B のデータを挿入」は成功します。
-- ユーザーB でユーザーA のデータを INSERT EXECUTE AS USER = N'ユーザーB' INSERT INTO Sales VALUES (7,N'ユーザーA', N'製品A',100) INSERT INTO Sales VALUES (7,N'ユーザーB', N'製品A',100) SELECT USER_NAME() AS username, * FROM Sales REVERT
行レベルセキュリティはフィルター操作のため、インデックスによって、実行プランも変わってきますので Row-Level Security: Performance and common patterns も見ておくとよいかと。