SE の雑記

我らが No.1 が Azure Update (2015.01.30) v12とかいろいろ で紹介していますが、SQL Database で Row-Level Security がプレビュー機能として使用できるようになりました。

ということで少しまとめてみたいと思います。

基本的な情報は、

• Preview: Azure SQL Database Row-Level Security 
• Row-Level Security Preview

を参照していただければと。

Row-Level Security (RLS) ですが、現状、SQL Database でのみ使用できる機能となっており、SQL Server には実装がされていません。

また、SQL Database の V12 でのみ使用できるため、11.x 系の SQL Database を使用しいている場合には利用することができません。

RLS は

• セキュリティ述語関数
  CREATE FUNCTION (Transact-SQL)
• セキュリティポリシー
  CREATE SECURITY POLICY (Azure SQL Database)

を使用してテーブルからのデータ取得に対してフィルターを設定するイメージになるかと思います。

作成の流れは以下のようになります。
# クエリのベースは冒頭で紹介したリンク先のものを使用しています。スキーマについては dbo のみで完結させています。

使用するためには最初にセキュリティ述語関数を作成します。

まずは V12 上にテストデータベースとテスト用のテーブルを作成します。
# v12 の場合はクラスター化インデックスなしでテーブルが作成できます。

CREATE DATABASE RLSTEST COLLATE Japanese_XJIS_100_CI_AS
(SERVICE_OBJECTIVE = 'basic')
CREATE TABLE Sales
    (
    OrderID int,
    SalesRep sysname,
    Product varchar(50),
    Qty int
    );
INSERT Sales VALUES
(1, 'Sales1', 'Valve', 5),
(2, 'Sales1', 'Wheel', 2),
(3, 'Sales1', 'Valve', 4),
(4, 'Sales2', 'Bracket', 2),
(5, 'Sales2', 'Wheel', 5),
(6, 'Sales2', 'Seat', 5);

次にセキュリティ述語関数を作成します。

CREATE FUNCTION fn_securitypredicate(@SalesRep AS sysname)
    RETURNS TABLE
WITH SCHEMABINDING
AS
	RETURN
	SELECT 1 AS fn_securitypredicate_result
	WHERE @SalesRep = USER_NAME()
	OR
	USER_NAME() = 'Manager';

セキュリティ述語関数はテーブルを返すテーブル値関数の必要がありますので、検索結果を返すようにしています。

この時、WHERE 句にはフィルターの条件を設定する必要があります。

次にセキュリティポリシーを作成します。

CREATE SECURITY POLICY SalesFilter
ADD FILTER PREDICATE dbo.fn_securitypredicate(SalesRep)
ON dbo.Sales
WITH (STATE = ON);

セキュリティポリシーでは

• どのテーブルに対して行レベルのセキュリティ (フィルター) をかけるか
• フィルターにはどのテーブル値関数を使用するか
• テーブルのどの項目を使用してフィルターをかけるか

を設定します。

上記のセキュリティポリシーでは、

• 「dbo.Sales」テーブルに
• 「dbo.fn_securitypredicate」テーブル値関数を使用して
• 「SalesRep」を使用してフィルターをかける

ということになります。

テーブル置換数では、

• USER_NAME 関数の結果が「Manager」の場合はすべて
• それ以外の場合は「SalesRep」が「ユーザー名」のレコード

のみが検索されるようになります。

行レベルセキュリティは管理者アカウントでも同様に動作が行われます。

そのため、セキュリティポリシーが設定されているテーブルでは管理者が全件取得をしようとした場合も以下のような結果になります。

それではデータベースにユーザーを作成して試してみたいと思います。

CREATE USER Manager WITHOUT LOGIN;
CREATE USER Sales1 WITHOUT LOGIN;
CREATE USER Sales2 WITHOUT LOGIN;
GRANT SELECT ON Sales TO Manager;
GRANT SELECT ON Sales TO Sales1;
GRANT SELECT ON Sales TO Sales2;
EXECUTE AS USER = 'Sales1';
SELECT * FROM Sales;
REVERT;
EXECUTE AS USER = 'Sales2';
SELECT * FROM Sales;
REVERT;
EXECUTE AS USER = 'Manager';
SELECT * FROM Sales;
REVERT;

EXECUTE AS USER で実行ユーザーを切り替えながら実行した結果がこちらになります。

「Sales1」「Sales2」 の場合は、「SalesRep」が自分のユーザーのもの、「Manager」の場合はすべてのレコードが検索されていることが確認できます。

この動作は「SELECT」だけでなく、「UPDATE」「DELETE」にも適用されます。

GRANT SELECT,UPDATE,DELETE,INSERT ON Sales TO Manager;
GRANT SELECT,UPDATE,DELETE,INSERT ON Sales TO Sales1;
GRANT SELECT,UPDATE,DELETE,INSERT ON Sales TO Sales2;
EXECUTE AS USER = 'Sales1';
UPDATE Sales SET Product='Master Eros' WHERE OrderID = 4
DELETE FROM Sales WHERE OrderID = 4
REVERT;
EXECUTE AS USER = 'Sales2';
UPDATE Sales SET Product='Master Eros' WHERE OrderID = 1
DELETE FROM Sales WHERE OrderID = 1
REVERT;
EXECUTE AS USER = 'Manager';
SELECT * FROM Sales
REVERT;

実行の結果がこちらになります。

これは、「Master Eros」(えろす師匠の別名) がデータとして格納できないということではなく、自分がアクセスを許可されていないデータに対して、変更 (UPDATE / DELETE) を実施しているため、操作ができずにデータが変更されていないため、実行前後で結果が変わっていない形になります。

ただし、このセキュリティ機能については「INSERT」については利用することができません。

INSERT については、「SalesRep」が自分の範囲外のデータについても INSERT できてしまうためこの辺についてはトリガーで生業が必要になってくるかと。

セキュリティポリシーについては OFF にすることもできますので、一時的に無効にすることも可能です。

SELECT * FROM Sales;
ALTER SECURITY POLICY SalesFilter WITH (STATE = OFF);
SELECT * FROM Sales;

この動作ですが、フィルターによって実装されているようです。

データサイズが大きくなってきた場合には処理のオーバーヘッドが気になってきますね。

# 検索した結果を返す際にフィルターをしているようですので、インデックス有無の影響はないのかな。

GRANT SHOWPLAN TO Sales1;
EXECUTE AS USER = 'Sales1';
SELECT * FROM Sales
REVERT;

行レベルのセキュリティは今まではビューで実装することがあったかと思いますが、関数ベースでテーブルに対して透過的に設定をすることができるのは面白いですね。