行レベルセキュリティ（Row-Level Security）について

行レベルセキュリティ(Row-Level Security-RLS)についてSQL ServerやSQL Databaseで利用可能ですが、Synapse SQLプールでも利用可能なので今回調査と検証を行いました。なのでメモ程度に。

#行レベルセキュリティ（Row-Level Security-RLS）とは
行レベルセキュリティ（Row-Level Security）はデータの読み込み時にテーブルの行レベルでアクセスを制御する機能です。これを実現するために、CREATE FUNCTIONとCREATE SECURITY POLICYを使用して、データ取得時にフィルターをすることになります。

#RLSの使いどころ
RLSの使いどころは以下のようなシチュエーションとなります。

• BIツールなどを使用する際に、所属の部署に関連するデータにしかアクセスできないようにする。
• マルチテナントのシステムにおいて、顧客のデータへのアクセスをその顧客に関連するデータだけに制限する。

アクセス制御のロジックがデータベースで行われるため、アプリケーションでセキュリティの設計やコーディングを簡素化でき、データベースそのものでアクセス制御を行うため、外部からのアクセスの観点からシステム全体のセキュリティに対する信頼性と堅牢性が向上するとされています。

#RLSが使えるSQL Server群
RLSが使えるSQL Server群はSynapse SQLプールだけでなく、以下のSQL Serverベースのデータベースで使用可能です。

• SQL Server (SQL Server 2016 (13.x)以降
• SQL Database
• Synapse Analytics

#2種類のセキュリティ
RLSには2種類のセキュリティ述語が存在しています。

• フィルター述語
  読み取り操作 (SELECT、UPDATE、DELETE) が可能な行をフィルターする。（エラーの通知なし）

• ブロック述語
  違反する書き込み操作 (AFTER INSERT、AFTER UPDATE、BEFORE UPDATE、BEFORE DELETE) を明示的に禁止します。（エラーの通知）※Synapse Analytics SQLプールではサポートされていません。

#動作確認
今回はマルチテナント環境を想定し動作検証しました。データベースのユーザ名とテナント名が同一の場合を想定し、各テナントのユーザは自分のテナントの情報しか見れず、管理者（AdminUser）はすべての情報が見れるように設定します。

##①テーブルの作成
まずは以下のコマンドでTenantテーブルを作成しデータを挿入します。

--テーブルの作成
CREATE TABLE Tenant (
    TenantId	bigint,			--PRIMARY KEY,
    Name		varchar(255),	--UNIQUE,
    Status		varchar(64),	--'active', 'suspended', 'disabled'
    Tier		varchar(64)		--'gold', 'silver', 'bronze'
);

--データの投入
INSERT INTO Tenant VALUES(1,'Tenant1','active','gold');
INSERT INTO Tenant VALUES(2,'Tenant2','active','gold');

SELECT * FROM Tenant ORDER BY 1;

次に以下のコマンドでTenantUserテーブルを作成データを挿入します。

--テーブルの作成
CREATE TABLE TenantUser (
	UserId		bigint, --PRIMARY KEY
	TenantId	bigint,	--
	Email		varchar(255),
	GivenName	varchar(255),
	FamilyName varchar(255)
);

--データの投入
INSERT INTO TenantUser VALUES(1,1,'[e-mail]','User 1','Tenant 1');
INSERT INTO TenantUser VALUES(2,1,'[e-mail]','User 2','Tenant 2');
INSERT INTO TenantUser VALUES(3,1,'[e-mail]','User 3','Tenant 2');

INSERT INTO TenantUser VALUES(1,2,'[e-mail]','User 1','Tenant 2');
INSERT INTO TenantUser VALUES(2,2,'[e-mail]','User 2','Tenant 2');
INSERT INTO TenantUser VALUES(3,2,'[e-mail]','User 3','Tenant 2');

SELECT * FROM TenantUser ORDER BY 2,1;

##②ユーザの作成
次に以下のコマンドでユーザを作成し、先ほど作成いたテーブルに権限を付与します。

--ユーザの作成
CREATE USER AdminUser WITHOUT LOGIN;
CREATE USER Tenant1 WITHOUT LOGIN;
CREATE USER Tenant2 WITHOUT LOGIN;

--各ユーザへの権限の付与
GRANT SELECT ON Tenant TO AdminUser;
GRANT SELECT ON Tenant TO Tenant1;
GRANT SELECT ON Tenant TO Tenant2;

GRANT SELECT ON TenantUser TO AdminUser;
GRANT SELECT ON TenantUser TO Tenant1;
GRANT SELECT ON TenantUser TO Tenant2;

##③スキーマ作成の作成
述語関数やセキュリティポリシーを作る際には、RLSを設定するオブジェクトのスキーマとは別のスキーマで作成することがベストプラクティスとなります。このため、別のスキーマを作成します。

--スキーマ作成
CREATE SCHEMA Security; 

##④述語関数の作成
以下のコマンドで、Tenantテーブル用、TenantUserテーブル用それぞれの述語関数を作成します。
Tenantテーブル用ではユーザ名と引数でわたされるNameの値を比較し、一致した場合は1を返します。また、AdminUserの場合はすべて1を返します。
TenantUserテーブル用ではTenantテーブルからユーザ名（Name）に紐づくTenantIdを取得し、引数で渡されるTenantIdと一致すれば1を返す関数となります。また、AdminUserの場合は全て1を返します。
※「1を返す」=「表示可能な行」のようなイメージです。

--Tenantテーブル用
CREATE FUNCTION Security.fn_secpredicate_tenant(@Name AS sysname)  
RETURNS TABLE  
WITH SCHEMABINDING  
AS  
    RETURN
	SELECT
		1 AS fn_secpredicate_tenant_result
	WHERE
		@Name = USER_NAME() OR
		USER_NAME() = 'AdminUser'
;

--TenantUserテーブル用
CREATE FUNCTION Security.fn_secpredicate_tenantuser(@TenantId AS bigint)  
RETURNS TABLE  
WITH SCHEMABINDING  
AS  
    RETURN
	SELECT
		1 AS fn_secpredicate_tenantuser_result
	WHERE
		@TenantId = (SELECT TenantId FROM dbo.Tenant where name = USER_NAME()) OR
		USER_NAME() = 'AdminUser'
;

##⑤セキュリティポリシーの作成
先ほど作成した述語関数に対応するセキュリティポリシーをそれぞれテーブルに紐づけていきます。

CREATE SECURITY POLICY Security.Tenant_Filter  
ADD FILTER PREDICATE Security.fn_secpredicate_tenant(Name) ON dbo.Tenant,
ADD FILTER PREDICATE Security.fn_secpredicate_tenantuser(TenantId) ON dbo.TenantUser
WITH (STATE = ON);

##⑥述語関数に対する権限を付与
以下のコマンドで各ユーザに対して述語関数の権限を付与します。

GRANT SELECT ON security.fn_secpredicate_tenant TO AdminUser;
GRANT SELECT ON security.fn_secpredicate_tenant TO Tenant1;
GRANT SELECT ON security.fn_secpredicate_tenant TO Tenant2;

GRANT SELECT ON security.fn_secpredicate_tenantuser TO AdminUser;
GRANT SELECT ON security.fn_secpredicate_tenantuser TO Tenant1;
GRANT SELECT ON security.fn_secpredicate_tenantuser TO Tenant2;

##⑦検証
それぞれのユーザで各テーブルを参照します。

###AdminUser
管理者ユーザの為、すべてのデータを見ることが可能です。

EXECUTE AS USER = 'AdminUser';
select * from Tenant;
select * from TenantUser;
REVERT;

###Tenant1
Tenant1ユーザではTenant1の情報のみ見れます。

EXECUTE AS USER = 'Tenant1';
select * from Tenant order by 1;
select * from TenantUser order by 2,1;
REVERT;

###Tenant2
Tenant2ユーザではTenant2の情報のみ見れます。

EXECUTE AS USER = 'Tenant2';
select * from Tenant order by 1;
select * from TenantUser order by 2,1;
REVERT;

#さいごに
検証した環境の削除方法も記載しておきます。

DROP USER AdminUser;
DROP USER Tenant1;
DROP USER Tenant2;

DROP SECURITY POLICY Security.Tenant_Filter;
DROP FUNCTION Security.fn_secpredicate_tenant;
DROP FUNCTION Security.fn_secpredicate_tenantuser;
DROP TABLE Tenant;
DROP TABLE TenantUser;
DROP SCHEMA Security;