SE の雑記

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

Row-Level Security で Block predicates がサポートされました

leave a comment

Preview ではありますが、Row-Level Security で Block predicates (ブロック述語) がサポートされるようになりました。
Public preview: Row-Level Security block predicates for SQL Database
New Row-Level Security functionality: Block predicates (preview)
New Row-Level Security functionality: Block predicates (preview)
CREATE SECURITY POLICY (Transact-SQL)
SQL Database v12 で使えるようになったかと思ったら、SQL Server 2016 CTP 2.4 でも使うことができるようですね。
# BOL では CTP 3.0 となっていましたが。
ざっくり試してみたいと思います。

今まで、RLS で WHERE 句により FILTER ができない INSERT の操作についての制御をするためには、トリガーや制約を使用して、操作を制限させる必要がありました。
今回追加されたブロック述語により、

  • { AFTER { INSERT | UPDATE } }
  • { BEFORE { UPDATE | DELETE } }

がサポートされるようになりました。
冒頭で紹介した、ドキュメントをもとにベースのオブジェクトを作成します。

F OBJECT_ID('Security.tenantPolicy') IS NOT NULL
	DROP SECURITY POLICY Security.tenantPolicy
IF OBJECT_ID('Security.tenantAccessPredicate') IS NOT NULL
	DROP FUNCTION Security.tenantAccessPredicate
IF OBJECT_ID('Sales') IS NOT NULL
	DROP TABLE Sales
IF USER_ID('AppUser') IS NOT NULL
	DROP USER AppUser
IF SCHEMA_ID('Security') IS NOT NULL
	DROP SCHEMA Security
-- Create sample table, where each row has a TenantId
CREATE TABLE Sales (
 OrderId int,
 Qty int,
 Product varchar(10),
 TenantId int
)
INSERT INTO Sales VALUES
 (1, 53, 'Valve', 1),
 (2, 71, 'Bracket', 2),
 (3, 60, 'Wheel', 2)
go
-- Create shared user for application to connect
CREATE USER AppUser WITHOUT LOGIN
go
-- Tenants will have both read and write access
GRANT SELECT, INSERT, UPDATE, DELETE ON Sales TO AppUser
--DENY UPDATE ON Sales(TenantId) TO AppUser -- never allowed to change TenantId
go
-- Enable RLS
CREATE SCHEMA Security
go
CREATE FUNCTION Security.tenantAccessPredicate(@TenantId int)
 RETURNS TABLE
 WITH SCHEMABINDING
AS
 RETURN SELECT 1 AS accessResult
 WHERE
 @TenantId = CONVERT(int, CONVERT(varbinary(4), CONTEXT_INFO()))
 OR
 CONVERT(int, CONVERT(varbinary(4), CONTEXT_INFO())) = 0
go

 
この状態でフィルターの効果を試してみます。
まずは、AFTERの設定を試してみます。

-- Note: We only need a block predicate AFTER INSERT, because
-- rows for BEFORE UPDATE and BEFORE DELETE are already filtered, and
-- AFTER UPDATE is unnecessary due to the column permission
CREATE SECURITY POLICY Security.tenantPolicy
 ADD FILTER PREDICATE Security.tenantAccessPredicate(TenantId) ON dbo.Sales
,ADD BLOCK PREDICATE Security.tenantAccessPredicate(TenantId) ON dbo.Sales AFTER UPDATE
,ADD BLOCK PREDICATE Security.tenantAccessPredicate(TenantId) ON dbo.Sales AFTER INSERT
go

 
このセキュリティポリシーが設定されている場合、以下のようなクエリーはエラーとすることができます。

-- Try it out by simulating queries as AppUser connected with TenantId = 2
EXECUTE AS USER = 'AppUser'
SET CONTEXT_INFO 2
go
SELECT * FROM Sales -- only rows for current tenant are visible
go
INSERT INTO Sales VALUES (4, 1000, 'Wheel', 1) -- blocked from inserting for wrong tenant!
UPDATE Sales SET TenantID = 0  WHERE OrderID = 2
REVERT
go
SET CONTEXT_INFO 0
SELECT * FROM Sales

 
AFTER の設定は、クエリの実行結果として、RLS の関数のルールが守られていないものについて、エラーとすることができるようになります。
image
BEFORE の設定だと以下のような形になります。

CREATE SECURITY POLICY Security.tenantPolicy
ADD BLOCK PREDICATE Security.tenantAccessPredicate(TenantId) ON dbo.Sales BEFORE UPDATE
,ADD BLOCK PREDICATE Security.tenantAccessPredicate(TenantId) ON dbo.Sales BEFORE DELETE
go
-- Try it out by simulating queries as AppUser connected with TenantId = 2
EXECUTE AS USER = 'AppUser'
SET CONTEXT_INFO 2
go
SELECT * FROM Sales -- only rows for current tenant are visible
go
INSERT INTO Sales VALUES (4, 1000, 'Wheel', 1) -- blocked from inserting for wrong tenant!
UPDATE Sales SET TenantID = 2 WHERE TenantID = 1
DELETE FROM Sales WHERE TenantId = 1
REVERT
go

 
BEFORE の設定をした場合、RLS の関数のルールが守られていないデータに対しての UPDATE / DELETE をしようとした場合にエラーとなります。
ADD FILTER のフィルターを BEFORE の BLOCK と合わせて設定している場合は、最初にフィルターがされてしまっているため、操作対象にならないのでエラーを発生させることができないので、BLOCK BEFORE だけでテストをすると動作がわかりやすいかと思います。
各設定については異なる述語を設定することができますので、以下のように DELETE だけを異なる条件に設定するというようなことも可能です。

-- Enable RLS
CREATE SCHEMA Security
go
CREATE FUNCTION Security.tenantAccessPredicate(@TenantId int)
 RETURNS TABLE
 WITH SCHEMABINDING
AS
 RETURN SELECT 1 AS accessResult
 WHERE
 @TenantId = CONVERT(int, CONVERT(varbinary(4), CONTEXT_INFO()))
 OR
 CONVERT(int, CONVERT(varbinary(4), CONTEXT_INFO())) = 0
go
CREATE FUNCTION Security.deletetenantAccessPredicate(@TenantId int)
 RETURNS TABLE
 WITH SCHEMABINDING
AS
 RETURN SELECT 1 AS accessResult
 WHERE
 CONVERT(int, CONVERT(varbinary(4), CONTEXT_INFO())) = 0
go
-- Note: We only need a block predicate AFTER INSERT, because
-- rows for BEFORE UPDATE and BEFORE DELETE are already filtered, and
-- AFTER UPDATE is unnecessary due to the column permission
CREATE SECURITY POLICY Security.tenantPolicy
ADD FILTER PREDICATE Security.tenantAccessPredicate(TenantId) ON dbo.Sales
,ADD BLOCK PREDICATE Security.tenantAccessPredicate(TenantId) ON dbo.Sales AFTER INSERT
,ADD BLOCK PREDICATE Security.deletetenantAccessPredicate(TenantId) ON dbo.Sales BEFORE DELETE
go

 
今回のアップデートにより、

  • ADD FILTER を設定することで、SELECT / UPDATE / DELETE を実行した場合に、関数に設定したルールに基づいたフィルターをかけることができる
  • ADD BLOCK AFTER を設定することで INSERT / UPDATE を実行した場合に、関数に基づいたルールにより、「ルールに一致しないデータを作り出すこと」をブロックすることができる
  • ADD BLOCK BEFORE を設定することで UPDATE / DELETE を実行した場合に、関数に基づいたルールにより、「ルールに一致しないデータを操作すること」をブロックすることができる
    # ADD FILTER が設定されている場合、フィルター側のルールで操作対象のデータとならないことが考えられる

というような操作ができるようになったようですね。

Share

Written by Masayuki.Ozawa

10月 13th, 2015 at 11:59 pm

Leave a Reply