前回、行レベルセキュリティ（Row-Level Security）について調査と検証を行いました。
行レベルセキュリティ（Row-Level Security）について
今回はその続きです。
SESSION_CONTEXT
SESSION_CONTEXTを使うと、セッション内でコンテキストで指定されているキー(key)の値(value)をユーザは読み取ることができます。
--セッションコンテキストにKey(TEST)とvalue(AAAAAAA)を指定
EXEC sp_set_session_context @key=N'TEST', @value='AAAAAAA';
--セッションコンテキストの表示
SELECT SESSION_CONTEXT(N'TEST');
今回はこれの仕組みを利用してRLSの設定を行います。
検証
今回はマルチテナント環境を想定し動作検証しました。アプリケーションはセッションコンテキストにTenatIdを指定することを想定します。RLSを使って各テーブルのデータはセッションコンテキストに紐づくTenantIdのデータしか参照できないようにします。
①テーブルの作成
まずは以下のコマンドで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,'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;
②ユーザの作成
次に以下のコマンドでユーザを作成し、先ほど作成いたテーブルに権限を付与します。
--ユーザの作成
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を設定するオブジェクトのスキーマとは別のスキーマで作成することがベストプラクティスとなります。このため、別のスキーマを作成します。
--スキーマ作成
CREATE SCHEMA Security;
④述語関数の作成
以下のコマンドで述語関数を作成します。ユーザがデータベースに存在しているか確認しているとともに、引数で渡される'TenantId'とセッションコンテキストの'TenantId'を比較し一致しているのか確認しています。一致すれば1を返すような関数となります。AdminUserの場合はTenantIdに関係なく全て1を返します。
※「1を返す」=「表示可能な行」のようなイメージです。
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'
)
;
⑤セキュリティポリシーの作成
先ほど作成した述語関数に対応するセキュリティポリシーをそれぞれのテーブルに追加します。
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);
⑥述語関数に対する権限を付与
以下のコマンドで各ユーザに対して述語関数の権限を付与します。
GRANT SELECT ON security.fn_securitypredicate TO AdminUser;
GRANT SELECT ON security.fn_securitypredicate TO AppUser;
⑦検証
動作確認してみます。
AdminUser→すべてのデータを表示
AppUser→セッションコンテキストに指定したkey:TenandId、value:任意の数字にて表示させるデータ制御
AdminUser
EXECUTE AS USER = 'AdminUser';
SELECT SESSION_CONTEXT(N'TenantId') ;
SELECT * FROM Tenant;
SELECT * FROM TenantUser;
REVERT;
全てのデータが表示されています。
AppUser(key:TenantId,value:1)
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;
Tenant1のデータのみ表示されています。
AppUser(key:TenantId,value:2)
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;
Tenant2のデータのみ表示されます。
AppUser(key:TenantId,value:3)--存在しないテナント
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;
存在しないTenantIdを指定したので、何も表示されません。
さいごに
検証した環境の削除方法も記載しておきます。
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;