Azure SQLの行レベルセキュリティをロールで実装する

はじめに

2020/11時点の情報です。

参考

上記のサンプルコードがユーザ単位での実装で使い勝手が悪そうなので、ちょっと改変したコードを放流します。
さっくり使ったのでもっと美しい実装があるかも？

手順概要

2つのADユーザ(全てのデータ閲覧可能、)を利用して、検証します。

• 管理者（adminuser）・・・Azure SQLの管理者・Azure SQL上のデータは全て閲覧可能
• 利用者（readuser）・・・Azure SQL上のデータは自分のロール(sales1)のデータのみ閲覧可能

手順は以下の流れ

1. Azure SQLの準備
2. セキュリティポリシー設定

1. Azure SQLの準備

適当なDBを作成します。
クイック スタート:Azure SQL Database の単一データベースを作成する

AD管理者を設定します。
Azure AD 管理者をプロビジョニングする (SQL Database)

2. セキュリティポリシー設定

AD管理者でログインして、以下を実施(masterではなく、DBに対して実行)

--利用者をDBにアクセスできるようにする
CREATE USER [[e-mail]] FROM EXTERNAL PROVIDER
GO
--ロールに作成、利用者を追加
CREATE ROLE Sales1 AUTHORIZATION [dbo]
GO
EXEC sp_addrolemember 'Sales1' ,'[e-mail]';  

--テスト用テーブル作成

CREATE TABLE Sales  
    (  
    OrderID int,  
    SalesRep sysname,  
    Product varchar(10),  
    Qty int  
    );  

INSERT INTO Sales VALUES (1, 'Sales1', 'Valve', 5);
INSERT INTO Sales VALUES (2, 'Sales1', 'Wheel', 2);
INSERT INTO Sales VALUES (3, 'Sales1', 'Valve', 4);
INSERT INTO Sales VALUES (4, 'Sales2', 'Bracket', 2);
INSERT INTO Sales VALUES (5, 'Sales2', 'Wheel', 5);
INSERT INTO Sales VALUES (6, 'Sales2', 'Seat', 5);
-- 6件が表示
SELECT * FROM Sales;

-- ロールに権限付与
GRANT SELECT ON Sales TO Sales1


-- セキュリティポリシーの作成
CREATE SCHEMA Security;  
GO  
 
-- サンプルクエリを改変して、ロールをチェックするように
CREATE FUNCTION Security.fn_securitypredicate(@SalesRep AS sysname)  
    RETURNS TABLE  
WITH SCHEMABINDING  
AS  
    RETURN 
	SELECT 
		1
	AS fn_securitypredicate_result
	where 1=IIF(ORIGINAL_LOGIN( ) = '[e-mail]',1,ISNULL(IS_ROLEMEMBER(@SalesRep , ORIGINAL_LOGIN()),0))
 ; 

-- テーブルに適用
CREATE SECURITY POLICY SalesFilter  
ADD FILTER PREDICATE Security.fn_securitypredicate(SalesRep)
ON dbo.Sales  
WITH (STATE = ON);  

-- セキュリティポリシーへアクセス権を付与
GRANT SELECT ON security.fn_securitypredicate TO [Sales1];  

readuserでログインして結果確認