Freigeben über

Azure SQL Database で Row-Level Security を使用してより安全性の高い中間層アプリケーションを構築する

  • Artikel

このポストは、3 月 2 日に投稿された Building More Secure Middle-Tier Applications with Azure SQL Database using Row-Level Security の翻訳です。

Azure SQL Database で、プログラミング可能な機能である Row-Level Security (RLS) が新たにプレビューとしてリリースされました。この機能は最新バージョン (英語) の SQL Database (V12) のデータベースすべてで使用できます。Row-Level Security では、データベース テーブルの行単位で細かくアクセスを制御できます。これにより、複数のユーザーで同じテーブルを共有する際の不正アクセスを防止し、またマルチテナント アプリケーションに接続フィルターを実装することができます。この記事では、Azure SQL Database で RLS を使用した安全性の高いマルチテナントの中間層アプリケーションを構築するうえでの基礎を説明します。

ここからは、顧客企業が自社の売上を追跡できる 3 階層の e コマース SaaS ソリューションを例にして考えていきます。顧客は中間層アプリケーションを通じてのみ売上データにアクセス可能であり、この例のソリューションでは効率を考慮しデータ階層にマルチテナント アーキテクチャを採用しています。このため、すべてのテナント (顧客) の売上データは同じデータベース、同じテーブルに格納されます。

dbo.Sales

OrderId

SKU

Price

TenantId

1

Book001

10

1

2

Movie001

15

2

3

Movie002

12

2

 各テナントからはそれぞれの売上データしか見ることができないようにする必要があります。それには、たとえば、Tenant 1 がログインしている間はこのテーブルに対してアプリケーションのクエリが発行されるたびに手動で「WHERE TenantId = 1」というステートメントを追加するという方法があります。しかし、このようにアプリケーションの中でフィルタリング ロジックによってクエリを書き換えるという方法はミスが発生しやすく、またコードベースを変更する際のメンテナンスも困難です。RLS を使用すれば、データベース自体にフィルタリング ロジックを集約できるため、アプリケーションの開発を進めながらもメンテナンスが簡単で、ミスも発生しにくくなります。

手順 1: CONTEXT_INFO にアプリケーション ユーザーの情報を格納する

アプリケーションで変更する必要があるのは 1 か所だけです。この例のアプリケーションでは接続プールを使用し、すべてのアプリケーション ユーザーが同一のデータベースにログインしています。このため、RLS がどの行をフィルタリングして除外するかを判断できるように、アプリケーションはデータベースに対してどのテナントがログイン中であるかを示す必要があります。これには、SQL Database V12 で新たに導入された、接続範囲を限定するグローバル変数の CONTEXT_INFO を使用して対応します。アプリケーションが CONTEXT_INFO に TenantId を格納できるように、SSMS や SSDT を使用して T-SQL で次のようなストアド プロシージャを作成します。

 CREATE PROCEDURE sp_setContextInfoAsTenantId(@TenantId int)
AS
SET CONTEXT_INFO @TenantId 
GO

アプリケーションは接続が確立された後で、ログインしているユーザーの TenantId でこのストアド プロシージャを実行するだけです。

手順 2: アクセス ロジックを叙述関数に記述する

次に、行へのアクセス ロジックを実装するインラインのテーブル値関数を作成します。この叙述関数は、他のテーブルを組み合わせたり、階層的なアクセス制御を強制適用するために複数のパラメーターを確認したりする場合は複雑になることがありますが、この例のアプリケーションの場合は非常にシンプルです。ここでは、ベスト プラクティスとして叙述関数とセキュリティ ポリシーを別のスキーマに配置します。

 CREATE SCHEMA rls
GO
 
CREATE FUNCTION rls.fn_tenantAccessPredicate(@TenantId int)
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN SELECT 1 AS fn_accessResult 
WHERE DATABASE_PRINCIPAL_ID() = DATABASE_PRINCIPAL_ID('AppUser') -- the shared application login
AND CONVERT(int, CONVERT(varbinary(4), CONTEXT_INFO())) = @TenantId
GO

ユーザーが Sales テーブルにクエリを実行するとき、この関数はすべての行に対して動作し、TenantId が CONTEXT_INFO の現在の値と一致しない行 (または、このアプリケーション以外で SQL にログインして直接データベースに接続している場合) は除外されます。クエリ オプティマイザーは、上記の WHERE 述語を Sales テーブルに対する SELECT、UPDATE、DELETE の各クエリに追加したときと同様にこの関数を扱います。

手順 3: セキュリティ ポリシーで関数をテーブルにバインドする

最後に、この関数を Sales テーブルをフィルタリングする叙述関数としてバインドするセキュリティ ポリシーを作成します。

 CREATE SECURITY POLICY rls.tenantAccessPolicy
ADD FILTER PREDICATE rls.fn_tenantAccessPredicate(TenantId) ON dbo.Sales
GO

以上で完了です。これで、CONTEXT_INFO の現在の値に基づいて Sales テーブルに対するクエリが自動的にフィルタリングされるようになりました。これを確認するには、ストアド プロシージャで CONTEXT_INFO を設定してアプリケーションをシミュレートしてみます。

 EXECUTE sp_setContextInfoAsTenantId 1
GO
SELECT * FROM Sales
GO

上の SELECT クエリでは Tenant 1 の行のみが返されます (この場合は Book001 のみ)。

ベスト プラクティスと次のステップ

CONTEXT_INFO を使用してアプリケーション ユーザーの情報をデータベースに渡すシナリオは、お客様のアプリケーションのユーザーが何らかの T-SQL (SQL インジェクションなど) を実行して CONTEXT_INFO の値を変更したり、他のテナントに行を挿入したりできないことを前提としています。今回のバージョンの RLS には挿入操作のブロック機能は組み込まれていません。しかし、トリガーを使用したり、制約を確認したり (これについては後日の投稿で取り扱います)、ストアド プロシージャで挿入操作を制御することで、同等の機能を実装することができます。

また、関数で扱うテーブル数が多くなると叙述関数がより複雑になり、関数のクエリのパフォーマンスが低下するのでご注意ください。

RLS の詳細については、オンラインのドキュメント (英語) をご確認ください。また、ご意見やご不明な点がありましたら、この記事の下のコメント欄までお寄せください。皆様からのフィードバックは今後の開発に活用させていただきます。

デモ スクリプトの全文: デモ スクリプト (RLS-Middle-Tier-Apps-Demo.sql) は こちらのページ (英語) からダウンロードできます。

Comments

  • Anonymous
    March 09, 2015
    ポストをありがとう

  • Anonymous
    March 10, 2015
    Well can you provide some links regarding the same.