SE の雑記

SQL Server の情報をメインに Microsoft 製品の勉強内容を日々投稿

SQL Server 2016 CTP 2.0 の Row-Level Security を使ってみる

leave a comment

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

同じクエリを実行した結果、ユーザーによって取得されているデータが変わっているのが確認できます。

image

実行プランについては、SQL Database 同様にフィルター操作で制御されているようですね。

image

また、こちらも 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 も見ておくとよいかと。

Share

Written by Masayuki.Ozawa

6月 14th, 2015 at 4:35 pm

Posted in SQL Server

Tagged with ,

Leave a Reply