Program för flera klientorganisationer med elastiska databasverktyg och säkerhet på radnivå

  • Artikel

Gäller för:Azure SQL Database

Elastiska databasverktyg och säkerhet på radnivå (RLS) samarbetar för att möjliggöra skalning av datanivån för ett program med flera klientorganisationer med Azure SQL Database. Tillsammans hjälper dessa tekniker dig att skapa ett program som har en mycket skalbar datanivå. Datanivån stöder shards för flera klientorganisationer och använder ADO.NET SqlClient eller Entity Framework. Mer information finns i Designmönster för SaaS-program med flera klientorganisationer med Azure SQL Database.

  • Med elastiska databasverktyg kan utvecklare skala ut datanivån med standardmetoder för horisontell partitionering med hjälp av .NET-bibliotek och Azure-tjänstmallar. Genom att hantera shards med hjälp av Elastic Database-klientbiblioteket kan du automatisera och effektivisera många av de infrastrukturella uppgifter som vanligtvis är associerade med horisontell partitionering.
  • Säkerhet på radnivå gör det möjligt för utvecklare att lagra data på ett säkert sätt för flera klienter i samma databas. RLS-säkerhetsprinciper filtrerar bort rader som inte tillhör klientorganisationen som kör en fråga. Att centralisera filterlogik i databasen förenklar underhållet och minskar risken för ett säkerhetsfel. Alternativet att förlita sig på all klientkod för att framtvinga säkerhet är riskabelt.

Genom att använda dessa funktioner tillsammans kan ett program lagra data för flera klienter i samma sharddatabas. Det kostar mindre per klientorganisation när klientorganisationen delar en databas. Men samma program kan också erbjuda sina premiumklientorganisationer möjligheten att betala för sin egen dedikerade shard med en enda klientorganisation. En fördel med isolering med en enda klientorganisation är fastare prestandagarantier. I en databas med en enda klientorganisation finns det ingen annan klientorganisation som konkurrerar om resurser.

Målet är att använda databeroende routnings-API:er för elastiska databasbibliotek för att automatiskt ansluta varje specifik klientorganisation till rätt sharddatabas. Endast en shard innehåller ett visst TenantId-värde för den angivna klientorganisationen. TenantId är partitioneringsnyckeln. När anslutningen har upprättats säkerställer en RLS-säkerhetsprincip i databasen att den angivna klientorganisationen endast kan komma åt de datarader som innehåller dess TenantId.

Kommentar

Klientidentifieraren kan bestå av mer än en kolumn. För enkelhetens skull förutsätter vi informellt ett TenantId med en kolumn.

Blogging app architecture

Ladda ned exempelprojektet

Förutsättningar

Det här projektet utökar det som beskrivs i Elastic DB Tools for Azure SQL – Entity Framework Integration genom att lägga till stöd för sharddatabaser för flera klientorganisationer. Projektet skapar ett enkelt konsolprogram för att skapa bloggar och inlägg. Projektet innehåller fyra klienter, plus två sharddatabaser för flera klientorganisationer. Den här konfigurationen visas i föregående diagram.

Skapa och kör programmet. Den här körningen startar den elastiska databasverktygens shard map manager och utför följande tester:

  1. Med Entity Framework och LINQ skapar du en ny blogg och visar sedan alla bloggar för varje klientorganisation
  2. Visa alla bloggar för en klientorganisation med hjälp av ADO.NET SqlClient
  3. Försök att infoga en blogg för fel klientorganisation för att kontrollera att ett fel utlöses

Observera att eftersom RLS ännu inte har aktiverats i sharddatabaserna, visar var och en av dessa tester ett problem: klienter kan se bloggar som inte tillhör dem och programmet hindras inte från att infoga en blogg för fel klientorganisation. Resten av den här artikeln beskriver hur du löser dessa problem genom att framtvinga klientisolering med RLS. Det finns två steg:

  1. Programnivå: Ändra programkoden så att du alltid anger aktuellt TenantId i SESSION_CONTEXT när du har öppnat en anslutning. Exempelprojektet anger redan TenantId på det här sättet.
  2. Datanivå: Skapa en RLS-säkerhetsprincip i varje sharddatabas för att filtrera rader baserat på TenantId som lagras i SESSION_CONTEXT. Skapa en princip för var och en av dina sharddatabaser, annars filtreras inte rader i shards för flera klientorganisationer.

1. Programnivå: Ange TenantId i SESSION_CONTEXT

Först ansluter du till en sharddatabas med hjälp av de databeroende routnings-API:erna för klientbiblioteket för elastisk databas. Programmet måste fortfarande tala om för databasen vilket TenantId som använder anslutningen. TenantId anger för RLS-säkerhetsprincipen vilka rader som måste filtreras bort som tillhörande andra klienter. Lagra aktuellt TenantId i SESSION_CONTEXT för anslutningen.

Ett alternativ till SESSION_CONTEXT är att använda CONTEXT_INFO. Men SESSION_CONTEXT är ett bättre alternativ. SESSION_CONTEXT är enklare att använda returneras NULL som standard och stöder nyckel/värde-par.

Entity Framework

För program som använder Entity Framework är den enklaste metoden att ange SESSION_CONTEXT i ElasticScaleContext-åsidosättningen som beskrivs i Databeroende routning med EF DbContext. Skapa och kör en SqlCommand som anger TenantId i SESSION_CONTEXT till den shardingKey som angetts för anslutningen. Returnera sedan anslutningen som asynkron via databeroende routning. På så sätt behöver du bara skriva kod en gång för att ange SESSION_CONTEXT.

// 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;
    }
}
// ...

Nu anges SESSION_CONTEXT automatiskt med angivet TenantId när ElasticScaleContext anropas:

// 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

För program som använder ADO.NET SqlClient skapar du en omslutningsfunktion kring metoden ShardMap.Open Anslut ionForKey. Låt omslutningen automatiskt ange TenantId i SESSION_CONTEXT till aktuellt TenantId innan du returnerar en anslutning. För att säkerställa att SESSION_CONTEXT alltid har angetts bör du bara öppna anslutningar med hjälp av den här omslutningsfunktionen.

// 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. Datanivå: Skapa säkerhetsprincip på radnivå

Skapa en säkerhetsprincip för att filtrera de rader som varje klientorganisation kan komma åt

Nu när programmet ställer in SESSION_CONTEXT med aktuellt TenantId innan du frågar kan en RLS-säkerhetsprincip filtrera frågor och exkludera rader som har ett annat TenantId.

RLS implementeras i Transact-SQL. En användardefinierad funktion definierar åtkomstlogik och en säkerhetsprincip binder den här funktionen till valfritt antal tabeller. För det här projektet:

  1. Funktionen verifierar att programmet är anslutet till databasen och att TenantId som lagras i SESSION_CONTEXT matchar TenantId för en viss rad.

    • Programmet är anslutet i stället för någon annan SQL-användare.

  2. Med ett FILTER-predikat kan rader som uppfyller TenantId-filtret passera för SELECT-, UPDATE- och DELETE-frågor.

    • En BLOCK-predikat förhindrar att rader som inte klarar filtret blir INSERTed eller UPDATEd.
    • Om SESSION_CONTEXT inte har angetts returnerar funktionen NULL och inga rader visas eller kan infogas.

Om du vill aktivera RLS på alla shards kör du följande T-SQL med antingen Visual Studio (SSDT), SSMS eller PowerShell-skriptet som ingår i projektet. Eller om du använder Elastic Database-jobb kan du automatisera körningen av denna T-SQL på alla shards.

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

Dricks

I ett komplext projekt kan du behöva lägga till predikatet på hundratals tabeller, vilket kan vara omständligt. Det finns en lagrad hjälpprocedur som automatiskt genererar en säkerhetsprincip och lägger till ett predikat för alla tabeller i ett schema. Mer information finns i blogginlägget på Tillämpa säkerhet på radnivå på alla tabeller – hjälpskript (blogg).

Om du nu kör exempelprogrammet igen ser klientorganisationer endast rader som tillhör dem. Dessutom kan programmet inte infoga rader som tillhör andra klienter än den som för närvarande är ansluten till sharddatabasen. Appen kan inte heller uppdatera TenantId på några rader som den kan se. Om appen försöker göra något av detta genereras en DbUpdateException.

Om du lägger till en ny tabell senare ändrar du säkerhetsprincipen för att lägga till FILTER- och BLOCK-predikat i den nya tabellen.

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

Lägg till standardbegränsningar för att automatiskt fylla i TenantId för INSERTs

Du kan ange en standardbegränsning för varje tabell för att automatiskt fylla i TenantId med det värde som för närvarande lagras i SESSION_CONTEXT när rader infogas. Ett exempel följer.

-- 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

Nu behöver programmet inte ange ett TenantId när rader infogas:

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();
    }
});

Kommentar

Om du använder standardbegränsningar för ett Entity Framework-projekt rekommenderar vi att du INTE inkluderar kolumnen TenantId i din EF-datamodell. Den här rekommendationen beror på att Entity Framework-frågor automatiskt anger standardvärden som åsidosätter standardbegränsningarna som skapas i T-SQL som använder SESSION_CONTEXT. Om du till exempel vill använda standardbegränsningar i exempelprojektet bör du ta bort TenantId från DataClasses.cs (och köra Add-Migration i Package Manager-konsolen) och använda T-SQL för att säkerställa att fältet bara finns i databastabellerna. På så sätt anger EF automatiskt felaktiga standardvärden när data infogas.

(Valfritt) Aktivera en superanvändare för att komma åt alla rader

Vissa program kanske vill skapa en superanvändare som kan komma åt alla rader. En superanvändare kan aktivera rapportering för alla klienter på alla shards. Eller så kan en superanvändare utföra split-merge-åtgärder på shards som innebär att klientrader flyttas mellan databaser.

Om du vill aktivera en superanvändare skapar du en ny SQL-användare (superuser i det här exemplet) i varje sharddatabas. Ändra sedan säkerhetsprincipen med en ny predikatfunktion som gör att användaren kan komma åt alla rader. En sådan funktion ges härnäst.

-- 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

Underhåll

  • Lägga till nya shards: Kör T-SQL-skriptet för att aktivera RLS på nya shards, annars filtreras inte frågor på dessa shards.
  • Lägga till nya tabeller: Lägg till ett FILTER- och BLOCK-predikat för säkerhetsprincipen på alla shards när en ny tabell skapas. Annars filtreras inte frågor i den nya tabellen. Det här tillägget kan automatiseras med hjälp av en DDL-utlösare, enligt beskrivningen i Tillämpa säkerhet på radnivå automatiskt på nyligen skapade tabeller (blogg).

Sammanfattning

Elastiska databasverktyg och säkerhet på radnivå kan användas tillsammans för att skala ut ett programs datanivå med stöd för shards för både flera klientorganisationer och en klientorganisation. Shards för flera klientorganisationer kan användas för att lagra data mer effektivt. Den här effektiviteten uttalas där ett stort antal klienter bara har några rader med data. Shards med en klientorganisation kan stödja premiumklienter som har strängare prestanda- och isoleringskrav. Mer information finns i Säkerhetsreferens på radnivå.

Ytterligare resurser

Frågor och funktionsförfrågningar

Om du har frågor kan du kontakta oss på microsofts Q&A-frågesida för SQL Database. Och lägg till eventuella funktionsbegäranden i SQL Database-feedbackforumet.