弾力性データベースツールと行レベルのセキュリティを使用したマルチテナントアプリケーション

[アーティクル]
03/29/2023

エラスティックデータベースツールと行レベルのセキュリティ (RLS) を組み合わせることで、Azure SQL Database を使用するマルチテナントアプリケーションのデータ層のスケーリングが可能になります。これらのテクノロジの連携によって、データ層のスケーラビリティを高めたアプリケーションを効率よく作成することができます。データ層では、マルチテナントシャードがサポートされ、ADO.NET SqlClient または Entity Framework が使用されます。詳細については、「Azure SQL Database を使用するマルチテナント SaaS アプリケーションの設計パターン」をご覧ください。

エラスティックデータベースツールを使用すると、開発者は .NET ライブラリと Azure のサービステンプレートを使用することにより、標準的なシャーディング手法に従ってデータ層をスケールアウトすることができます。 Elastic Database クライアントライブラリを使用してシャードを管理することは、一般的にシャーディングに関連するインフラストラクチャタスクの多くを自動化および効率化するうえで役立ちます。
行レベルセキュリティを使用すると、開発者は、同じデータベースに複数のテナントのデータを安全に格納することができます。クエリを実行しているテナントに属さない行は、RLS セキュリティポリシーによって除外されます。フィルターのロジックをデータベース内に集約することで、メンテナンスが簡素化され、セキュリティエラーのリスクが軽減されます。これをクライアントコードだけで行ってセキュリティを確保しようとするとリスクが伴います。

これらの機能を組み合わせて使用することで、アプリケーションは、複数のテナントのデータを同じシャードデータベースに格納することができます。テナントでデータベースを共有すると、テナントあたりのコストが小さくなります。しかも同じアプリケーションで、専用のシングルテナントシャードを利用できる有料オプションを Premium テナント向けに提供することができます。シングルテナントとして分離させることの利点の 1 つとして、より確実なパフォーマンス保証が可能になることが挙げられます。シングルテナントデータベースでは、他のテナントとリソースの奪い合いになることがありません。

達成目標は、エラスティックデータベースクライアントライブラリのデータ依存ルーティング API を使用してそれぞれのテナントが正しいシャードデータベースに自動的に接続することです。指定されたテナントの特定の TenantId 値を含んでいるシャードは 1 つだけです。 TenantId は "シャーディングキー" です。接続の確立後、データベース内にある RLS のセキュリティポリシーによって、指定されたテナントからのアクセスが、その TenantId を含んだデータ行に制限されます。

Note

テナント ID は、複数の列で構成される場合があります。この記事では便宜上、単一列の TenantId を前提として説明します。

Blogging app architecture

サンプルプロジェクトのダウンロード

前提条件

Visual Studio (2012 以降) を使用します。
Azure SQL Database に 3 つのデータベースを作成する
サンプルプロジェクトをダウンロードします:Elastic DB Tools for Azure SQL - Multi-Tenant Shards
- Program.cs

このプロジェクトでは、 Azure SQL の弾力性 DB ツールの Entity Framework 統合に関するページで説明したプロジェクトを、マルチテナントシャードデータベースのサポートを追加して拡張します。このプロジェクトでは、ブログや記事を作成するための単純なコンソールアプリケーションを作成します。プロジェクトには、4 つのテナントのほか、2 つのマルチテナントシャードデータベースが含まれます。この構成は、上の図に示されています。

アプリケーションをビルドして実行します。この実行により、エラスティックデータベースツールのシャードマップマネージャーが起動され、次のテストが実行されます。

Entity Framework と LINQ を使用して、新しいブログを作成し、各テナントのすべてのブログを表示します。
ADO.NET SqlClient を使用して、テナントのすべてのブログを表示します。
不適切なテナントへのブログの挿入を試みて、エラーがスローされることを確認します。

RLS はシャードデータベースでまだ有効になっていないため、これらの各テストで問題点が明らかになります。テナントは、そのテナントに属さないブログを表示できます。また、アプリケーションは、不適切なテナントにブログを挿入できます。この記事の残りの部分では、RLS によるテナントの分離を適用してこれらの問題を解決する方法について説明します。次の 2 つの手順があります。

アプリケーション層: アプリケーションコードを変更し、接続を開いた後で、常に現在の TenantId を SESSION_CONTEXT に設定します。サンプルプロジェクトでは、あらかじめそのように TenantId が設定されています。
データ層: 各シャードデータベースで、SESSION_CONTEXT に格納されている TenantId に基づいて行をフィルター処理するための RLS セキュリティポリシーを作成します。ポリシーは、それぞれのシャードデータベースに対して作成する必要があります。そうでないと、マルチテナントシャード内の行がフィルター処理されません。

1. アプリケーション層: TenantId を SESSION_CONTEXT に設定する

まず、エラスティックデータベースクライアントライブラリのデータ依存ルーティング API を使用してシャードデータベースに接続します。その場合でも、接続に使用している TenantId をアプリケーションからデータベースに伝える必要があります。 RLS セキュリティポリシーは、他のテナントに属している除外すべき行を TenantId によって判別します。接続の SESSION_CONTEXT に現在の TenantId を格納します。

SESSION_CONTEXT の代わりに CONTEXT_INFO を使用できます。ただし、SESSION_CONTEXT の方が適しています。 SESSION_CONTEXT の方が使いやすいためです。これは、既定で NULL を返すほか、キーと値のペアをサポートします。

Entity Framework

Entity Framework を使用するアプリケーションの場合、最も簡単な方法は、「EF DbContext を使用したデータ依存ルーティング」で説明されている ElasticScaleContext オーバーライドの中で SESSION_CONTEXT を設定する方法です。 SESSION_CONTEXT の TenantId をその接続に指定されている shardingKey に設定する SqlCommand を作成して実行します。その後、データ依存ルーティングで仲介された接続を返します。この方法では、SESSION_CONTEXT を設定するコードを 1 回記述するだけで済みます。

// ElasticScaleContext.cs
// Constructor for data-dependent routing.
// This call opens a validated connection that is routed to the
// proper shard by the shard map manager.
// Note that the base class constructor call fails for an open connection
// if migrations need to be done and SQL credentials are used.
// This is the reason for the separation of constructors.
// ...
public ElasticScaleContext(ShardMap shardMap, T shardingKey, string connectionStr)
    : base(
        OpenDDRConnection(shardMap, shardingKey, connectionStr),
        true)  // contextOwnsConnection
{
}

public static SqlConnection OpenDDRConnection(
    ShardMap shardMap,
    T shardingKey,
    string connectionStr)
{
    // No initialization.
    Database.SetInitializer<ElasticScaleContext<T>>(null);

    // Ask shard map to broker a validated connection for the given key.
    SqlConnection conn = null;
    try
    {
        conn = shardMap.OpenConnectionForKey(
            shardingKey,
            connectionStr,
            ConnectionOptions.Validate);

        // Set TenantId in SESSION_CONTEXT to shardingKey
        // to enable Row-Level Security filtering.
        SqlCommand cmd = conn.CreateCommand();
        cmd.CommandText =
            @"exec sp_set_session_context
                @key=N'TenantId', @value=@shardingKey";
        cmd.Parameters.AddWithValue("@shardingKey", shardingKey);
        cmd.ExecuteNonQuery();

        return conn;
    }
    catch (Exception)
    {
        if (conn != null)
        {
            conn.Dispose();
        }
        throw;
    }
}
// ...

これで、ElasticScaleContext が呼び出されるたびに、SESSION_CONTEXT が指定された TenantId に自動的に設定されます。

// Program.cs
SqlDatabaseUtils.SqlRetryPolicy.ExecuteAction(() =>
{
    using (var db = new ElasticScaleContext<int>(
        sharding.ShardMap, tenantId, connStrBldr.ConnectionString))
    {
        var query = from b in db.Blogs
                    orderby b.Name
                    select b;

        Console.WriteLine("All blogs for TenantId {0}:", tenantId);
        foreach (var item in query)
        {
            Console.WriteLine(item.Name);
        }
    }
});

ADO.NET SqlClient

ADO.NET SqlClient を使用するアプリケーションの場合、ShardMap.OpenConnectionForKey メソッドのラッパー関数を作成します。 SESSION_CONTEXT 内の TenantId を自動的に最新の TenantId に設定した後で接続を返すようなラッパーを作成しましょう。 SESSION_CONTEXT が常に設定されることを確認するには、このラッパー関数を使用して接続を開くだけです。

// Program.cs
// Wrapper function for ShardMap.OpenConnectionForKey() that
// automatically sets SESSION_CONTEXT with the correct
// tenantId before returning a connection.
// As a best practice, you should only open connections using this method
// to ensure that SESSION_CONTEXT is always set before executing a query.
// ...
public static SqlConnection OpenConnectionForTenant(
    ShardMap shardMap, int tenantId, string connectionStr)
{
    SqlConnection conn = null;
    try
    {
        // Ask shard map to broker a validated connection for the given key.
        conn = shardMap.OpenConnectionForKey(
            tenantId, connectionStr, ConnectionOptions.Validate);

        // Set TenantId in SESSION_CONTEXT to shardingKey
        // to enable Row-Level Security filtering.
        SqlCommand cmd = conn.CreateCommand();
        cmd.CommandText =
            @"exec sp_set_session_context
                @key=N'TenantId', @value=@shardingKey";
        cmd.Parameters.AddWithValue("@shardingKey", tenantId);
        cmd.ExecuteNonQuery();

        return conn;
    }
    catch (Exception)
    {
        if (conn != null)
        {
            conn.Dispose();
        }
        throw;
    }
}

// ...

// Example query via ADO.NET SqlClient.
// If row-level security is enabled, only Tenant 4's blogs are listed.
SqlDatabaseUtils.SqlRetryPolicy.ExecuteAction(() =>
{
    using (SqlConnection conn = OpenConnectionForTenant(
        sharding.ShardMap, tenantId4, connStrBldr.ConnectionString))
    {
        SqlCommand cmd = conn.CreateCommand();
        cmd.CommandText = @"SELECT * FROM Blogs";

        Console.WriteLine(@"--
All blogs for TenantId {0} (using ADO.NET SqlClient):", tenantId4);

        SqlDataReader reader = cmd.ExecuteReader();
        while (reader.Read())
        {
            Console.WriteLine("{0}", reader["Name"]);
        }
    }
});

2.データ層:行レベルのセキュリティポリシーを作成する

各テナントがアクセスできる行をフィルター選択するセキュリティポリシーを作成する

アプリケーションでクエリを実行する前に SESSION_CONTEXT が現在の TenantId に設定されるようになったため、RLS セキュリティポリシーを使ってクエリをフィルター選択して、別の TenantId を持つ行を除外できます。

RLS は Transact-SQL で実装されています。ユーザー定義の関数ではアクセスロジックを定義し、セキュリティポリシーではこの関数を任意の数のテーブルにバインドします。このプロジェクトでは、次のことを行います。

この関数は、アプリケーションがデータベースに接続されていることと、SESSION_CONTEXT に格納されている TenantId が特定の行の TenantId と一致することを確認します。
- 接続されているのは、他の SQL ユーザーではなく、アプリケーションです。
FILTER 述語を使用すると、TenantId のフィルターに該当する行は、SELECT、UPDATE、DELETE のクエリにパススルーできます。
- BLOCK 述語は、フィルターに該当しない行を INSERT または UPDATE の対象から除外します。
- SESSION_CONTEXT を設定していない場合は、関数から NULL が返され、行を表示または挿入することはできません。

すべてのシャードで RLS を有効にするには、Visual Studio (SSDT)、SSMS、またはプロジェクトに含まれる PowerShell スクリプトを使用して、次の T-SQL を実行します。また、Elastic Database ジョブを使用している場合は、すべてのシャードでこの T-SQL の実行を自動化できます。

CREATE SCHEMA rls; -- Separate schema to organize RLS objects.
GO

CREATE FUNCTION rls.fn_tenantAccessPredicate(@TenantId int)
    RETURNS TABLE
    WITH SCHEMABINDING
AS
    RETURN SELECT 1 AS fn_accessResult
        -- Use the user in your application's connection string.
        -- Here we use 'dbo' only for demo purposes!
        WHERE DATABASE_PRINCIPAL_ID() = DATABASE_PRINCIPAL_ID('dbo')
        AND CAST(SESSION_CONTEXT(N'TenantId') AS int) = @TenantId;
GO

CREATE SECURITY POLICY rls.tenantAccessPolicy
    ADD FILTER PREDICATE rls.fn_tenantAccessPredicate(TenantId) ON dbo.Blogs,
    ADD BLOCK  PREDICATE rls.fn_tenantAccessPredicate(TenantId) ON dbo.Blogs,
    ADD FILTER PREDICATE rls.fn_tenantAccessPredicate(TenantId) ON dbo.Posts,
    ADD BLOCK  PREDICATE rls.fn_tenantAccessPredicate(TenantId) ON dbo.Posts;
GO

ヒント

複雑なプロジェクトでは、何百ものテーブルに述語を追加することが必要になる場合がありますが、これは冗長になる可能性があります。セキュリティポリシーを自動的に生成して、スキーマ内のすべてのテーブルに述語を追加するヘルパーストアドプロシージャが存在します。詳細については、ヘルパースクリプトを使用してすべてのテーブルに行レベルのセキュリティを適用する方法に関するブログ記事を参照してください。

ここでサンプルアプリケーションを再び実行すると、そのテナントに属している行だけが表示されます。さらに、アプリケーションは、シャードデータベースに現在接続されているテナント以外のテナントに属する行を挿入できません。また、アプリから見える行であっても TenantId は一切更新できません。アプリケーションがどちらかの操作を実行しようとすると、DbUpdateException が発生します。

後で新しいテーブルを追加する場合は、セキュリティポリシーを変更して、新しいテーブルに FILTER 述語と BLOCK 述語を追加します。

ALTER SECURITY POLICY rls.tenantAccessPolicy
    ADD FILTER PREDICATE rls.fn_tenantAccessPredicate(TenantId) ON dbo.MyNewTable,
    ADD BLOCK  PREDICATE rls.fn_tenantAccessPredicate(TenantId) ON dbo.MyNewTable;
GO

挿入用に TenantId を自動的に設定する既定の制約を追加する

各テーブルに既定の制約を設定し、行を挿入するときに SESSION_CONTEXT に現在格納されている値を持つ TenantId を自動的に設定できます。例を次に示します。

-- Create default constraints to auto-populate TenantId with the
-- value of SESSION_CONTEXT for inserts.
ALTER TABLE Blogs
    ADD CONSTRAINT df_TenantId_Blogs
    DEFAULT CAST(SESSION_CONTEXT(N'TenantId') AS int) FOR TenantId;
GO

ALTER TABLE Posts
    ADD CONSTRAINT df_TenantId_Posts
    DEFAULT CAST(SESSION_CONTEXT(N'TenantId') AS int) FOR TenantId;
GO

これで、アプリケーションが行を挿入するときに TenantId を指定する必要はありません。

SqlDatabaseUtils.SqlRetryPolicy.ExecuteAction(() =>
{
    using (var db = new ElasticScaleContext<int>(
        sharding.ShardMap, tenantId, connStrBldr.ConnectionString))
    {
        // The default constraint sets TenantId automatically!
        var blog = new Blog { Name = name };
        db.Blogs.Add(blog);
        db.SaveChanges();
    }
});

Note

Entity Framework プロジェクトで既定の制約を使用する場合は、Entity Framework データモデルに TenantId 列を "含めない" ことをお勧めします。これは、Entity Framework のクエリでは既定値が自動的に指定され、T-SQL で作成された SESSION_CONTEXT を使用する既定の制約がオーバーライドされるためです。たとえば、サンプルプロジェクトで既定の制約を使用するには、DataClasses.cs から TenantId を削除し (さらにパッケージマネージャーコンソールで Add-Migration を実行し)、T-SQL を使ってフィールドがデータベーステーブルにのみ存在することを確認します。これにより、データを挿入するときに Entity Framework によって不適切な既定値が自動的に指定されなくなります。

(省略可能) "スーパーユーザー" がすべての行にアクセスできるようにする

一部のアプリケーションでは、すべての行にアクセスできる "スーパーユーザー" の作成が必要となる場合があります。スーパーユーザーであれば、すべてのシャード上のテナントの境界を越えたレポート作成が可能です。また、データベース間のテナント行の移動を伴う分割/マージ操作をシャードに対して実行することも、スーパーユーザーであれば可能です。

スーパーユーザーを有効にするには、各シャードデータベースに新しい SQL ユーザー (この例では superuser) を作成します。その後、このユーザーがすべての行にアクセスできるようにする新しいの述語関数を使用して、セキュリティポリシーを変更します。そのような関数の例を次に示します。

-- New predicate function that adds superuser logic.
CREATE FUNCTION rls.fn_tenantAccessPredicateWithSuperUser(@TenantId int)
    RETURNS TABLE
    WITH SCHEMABINDING
AS
    RETURN SELECT 1 AS fn_accessResult
        WHERE
        (
            DATABASE_PRINCIPAL_ID() = DATABASE_PRINCIPAL_ID('dbo') -- Replace 'dbo'.
            AND CAST(SESSION_CONTEXT(N'TenantId') AS int) = @TenantId
        )
        OR
        (
            DATABASE_PRINCIPAL_ID() = DATABASE_PRINCIPAL_ID('superuser')
        );
GO

-- Atomically swap in the new predicate function on each table.
ALTER SECURITY POLICY rls.tenantAccessPolicy
    ALTER FILTER PREDICATE rls.fn_tenantAccessPredicateWithSuperUser(TenantId) ON dbo.Blogs,
    ALTER BLOCK  PREDICATE rls.fn_tenantAccessPredicateWithSuperUser(TenantId) ON dbo.Blogs,
    ALTER FILTER PREDICATE rls.fn_tenantAccessPredicateWithSuperUser(TenantId) ON dbo.Posts,
    ALTER BLOCK  PREDICATE rls.fn_tenantAccessPredicateWithSuperUser(TenantId) ON dbo.Posts;
GO

メンテナンス

新しいシャードの追加:すべての新しいシャードで RLS を有効にするための T-SQL スクリプトを実行します。この操作を行わないと、これらのシャードに対するクエリはフィルター処理されません。
新しいテーブルの追加:新しいテーブルを作成するたびに、すべてのシャードのセキュリティポリシーに FILTER 述語と BLOCK 述語を追加します。そうしないと、新しいテーブルに対するクエリはフィルター処理されません。この追加は、DDL トリガーを使用して自動化できます。詳細については、新しく作成したテーブルに自動的に行レベルのセキュリティを適用する方法に関するブログ記事を参照してください。

まとめ

エラスティックデータベースツールと行レベルのセキュリティを組み合わせて使用すると、アプリケーションのデータ層をスケールアウトして、マルチテナントのシャードと単一テナントのシャードの両方をサポートできます。マルチテナントのシャードは、データをより効率的に格納するために使用できます。その効果は、多数のテナントにデータが数行しかない場合に発揮されます。より厳しいパフォーマンス要件と分離要件を持つプレミアムテナントには、シングルテナントシャードで対応できます。詳細については、「行レベルのセキュリティ」をご覧ください。

その他のリソース

質問と機能に関する要望

ご質問がある場合は、SQL Database に関する Microsoft Q&A 質問ページからお問い合わせください。また、機能に関するご要望は、SQL Database フィードバックフォーラムまでお寄せください。

次の方法で共有

弾力性データベース ツールと行レベルのセキュリティを使用したマルチテナント アプリケーション

サンプル プロジェクトのダウンロード

前提条件

1. アプリケーション層: TenantId を SESSION_CONTEXT に設定する

Entity Framework

ADO.NET SqlClient

2.データ層:行レベルのセキュリティ ポリシーを作成する

各テナントがアクセスできる行をフィルター選択するセキュリティ ポリシーを作成する

挿入用に TenantId を自動的に設定する既定の制約を追加する

(省略可能) "スーパーユーザー" がすべての行にアクセスできるようにする

メンテナンス

まとめ

その他のリソース

質問と機能に関する要望

フィードバック

その他のリソース

弾力性データベースツールと行レベルのセキュリティを使用したマルチテナントアプリケーション

サンプルプロジェクトのダウンロード

2.データ層:行レベルのセキュリティポリシーを作成する

各テナントがアクセスできる行をフィルター選択するセキュリティポリシーを作成する