SESSION_CONTEXTを使用した行レベルセキュリティ（Row-Level Security）やってみた

前回、行レベルセキュリティ（Row-Level Security）について調査と検証を行いました。
[行レベルセキュリティ（Row-Level Security）について](https://qiita.com/masahiro-yamaguchi/items/ac4a0793b543ddcc2061)

今回はその続きです。

#SESSION_CONTEXT
SESSION_CONTEXTを使うと、セッション内でコンテキストで指定されているキー(key)の値(value)をユーザは読み取ることができます。

```SQL
--セッションコンテキストにKey(TEST)とvalue(AAAAAAA)を指定
EXEC sp_set_session_context @key=N'TEST', @value='AAAAAAA';

--セッションコンテキストの表示
SELECT SESSION_CONTEXT(N'TEST');
```
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/697647/594a26c4-62b2-2c93-e2bc-4fba54f58b5a.png)


今回はこれの仕組みを利用してRLSの設定を行います。

#検証
今回はマルチテナント環境を想定し動作検証しました。アプリケーションはセッションコンテキストにTenatIdを指定することを想定します。RLSを使って各テーブルのデータはセッションコンテキストに紐づくTenantIdのデータしか参照できないようにします。

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

```SQL
--テーブルの作成
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;
```
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/697647/46ee8cab-8a76-b314-c15a-172a5f87e71e.png)

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

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

--データの投入
INSERT INTO TenantUser VALUES(1,1,'aaaa@aaaa.com','User 1','Tenant 1');
INSERT INTO TenantUser VALUES(2,1,'bbbb@aaaa.com','User 2','Tenant 2');
INSERT INTO TenantUser VALUES(3,1,'cccc@aaaa.com','User 3','Tenant 2');

INSERT INTO TenantUser VALUES(1,2,'xxxx@bbbb.com','User 1','Tenant 2');
INSERT INTO TenantUser VALUES(2,2,'yyyy@bbbb.com','User 2','Tenant 2');
INSERT INTO TenantUser VALUES(3,2,'zzzz@bbbb.com','User 3','Tenant 2');

SELECT * FROM TenantUser ORDER BY 2,1;
```
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/697647/cc597f1a-3b6d-6966-a76e-7da3812d393b.png)

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

```SQL
--ユーザの作成
CREATE USER AdminUser WITHOUT LOGIN;
CREATE USER AppUser WITHOUT LOGIN;

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

GRANT SELECT ON TenantUser TO AdminUser;
GRANT SELECT ON TenantUser TO AppUser;
```

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

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

##④述語関数の作成
以下のコマンドで述語関数を作成します。ユーザがデータベースに存在しているか確認しているとともに、引数で渡される'TenantId'とセッションコンテキストの'TenantId'を比較し一致しているのか確認しています。一致すれば`1`を返すような関数となります。AdminUserの場合は`TenantId`に関係なく全て`1`を返します。
※「1を返す」=「表示可能な行」のようなイメージです。

```SQL
CREATE FUNCTION Security.fn_securitypredicate(@TenantId AS sysname)  
RETURNS TABLE  
WITH SCHEMABINDING  
AS  
    RETURN
    SELECT
        1 AS fn_secpredicate_tenant_result
    WHERE
        DATABASE_PRINCIPAL_ID() = DATABASE_PRINCIPAL_ID(USER_NAME()) AND
        (
			CAST(SESSION_CONTEXT(N'TenantId') AS bigint) = @TenantId
			OR USER_NAME() = 'AdminUser'
		)
;
```

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

```SQL
CREATE SECURITY POLICY Security.TenantFilter  
ADD FILTER PREDICATE Security.fn_securitypredicate(TenantId) ON dbo.Tenant,
ADD FILTER PREDICATE Security.fn_securitypredicate(TenantId) ON dbo.TenantUser
WITH (STATE = ON);
```

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

```SQL
GRANT SELECT ON security.fn_securitypredicate TO AdminUser;
GRANT SELECT ON security.fn_securitypredicate TO AppUser;
```

#⑦検証
動作確認してみます。
AdminUser→すべてのデータを表示
AppUser→セッションコンテキストに指定した`key:TenandId`、`value:任意の数字`にて表示させるデータ制御

##AdminUser
```SQL
EXECUTE AS USER = 'AdminUser';  

SELECT SESSION_CONTEXT(N'TenantId') ;
SELECT * FROM Tenant;
SELECT * FROM TenantUser;

REVERT;
```
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/697647/0042e823-296c-737e-1e40-3ebb31b24dd2.png)
全てのデータが表示されています。

##AppUser(key:TenantId,value:1)
```SQL
EXECUTE AS USER = 'AppUser';  
EXEC sp_set_session_context @key=N'TenantId', @value=1;  

SELECT SESSION_CONTEXT(N'TenantId') ;
SELECT * FROM Tenant;
SELECT * FROM TenantUser;

REVERT;
```
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/697647/7e04cf30-0d99-e109-70ce-427a28cacce8.png)
Tenant1のデータのみ表示されています。

##AppUser(key:TenantId,value:2)
```SQL
EXECUTE AS USER = 'AppUser';  
EXEC sp_set_session_context @key=N'TenantId', @value=2;  

SELECT SESSION_CONTEXT(N'TenantId') ;
SELECT * FROM Tenant;
SELECT * FROM TenantUser;

REVERT;
```
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/697647/43a6e129-372b-c302-6975-12192fbf38b3.png)
Tenant2のデータのみ表示されます。

##AppUser(key:TenantId,value:3)--存在しないテナント
```SQL
EXECUTE AS USER = 'AppUser';  
EXEC sp_set_session_context @key=N'TenantId', @value=3;  

SELECT SESSION_CONTEXT(N'TenantId') ;
SELECT * FROM Tenant;
SELECT * FROM TenantUser;

REVERT;
```
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/697647/f40e2ae3-7481-4bfe-9e74-4f8644cb8ab6.png)
存在しないTenantIdを指定したので、何も表示されません。

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

```SQL
DROP USER AdminUser;
DROP USER AppUser;

DROP SECURITY POLICY Security.TenantFilter;
DROP FUNCTION Security.fn_securitypredicate;
DROP SCHEMA Security;
DROP TABLE Tenant;
DROP TABLE TenantUser;
```