Aplicativos multilocatários com ferramentas de banco de dados elástico e segurança em nível de linha

Aplica-se a: Banco de Dados SQL do Azure

As ferramentas de banco de dados elástico e a RLS (segurança em nível de linha) cooperam para escalar a camada de dados de um aplicativo multilocatário com um Banco de Dados SQL do Azure. Juntas, essas tecnologias ajudam a criar um aplicativo que tem uma camada de dados altamente escalonável. A camada de dados é compatível com fragmentos de multilocatários e usa ADO.NET SqlClient ou Entity Framework. Para obter mais informações, confira Padrões de design para aplicativos SaaS multilocatário com o Banco de Dados SQL do Azure.

• Ferramentas de banco de dados elástico permitem que os desenvolvedores expandam a camada de dados com a fragmentação padrão usando bibliotecas .NET e modelos de serviço do Azure. Gerenciar fragmentos usando a Biblioteca Cliente do Banco de Dados Elástico ajuda a automatizar e simplificar muitas das tarefas infraestruturais normalmente associadas à fragmentação.
• A segurança em nível de linha permite que os desenvolvedores armazenem com segurança os dados para vários locatários no mesmo banco de dados. Políticas de segurança de RLS filtram linhas que não pertencem ao locatário executando uma consulta. Centralizar a lógica do filtro dentro do banco de dados simplifica a manutenção e reduz o risco de um erro de segurança. A alternativa de depender de todo o código de cliente para aplicar a segurança é arriscada.

Ao usar esses recursos em conjunto, um aplicativo pode armazenar dados para vários locatários no mesmo banco de dados de fragmentos. Custa menos por locatário quando os locatários compartilham um banco de dados. Embora o mesmo aplicativo também pode oferecer a seus locatários premium a opção de pagar para seus próprios fragmentos de banco de dados de único locatário dedicado. Um dos benefícios do isolamento de locatário único é uma maior garantia de desempenho. Em um banco de dados de locatário único, não há nenhum outro locatário competindo pelos recursos.

O objetivo é usar as APIs do roteamento dependente de dados da biblioteca de cliente do banco de dados elástico para conectar automaticamente a cada locatário especificado ao banco de dados de fragmento correto. Somente um fragmento contém o valor de TenantId específico para o locatário determinado. O TenantId é a chave de fragmentação. Depois que a conexão é estabelecida, uma política de segurança de RLS no banco de dados garante que o locatário especificado pode acessar somente as linhas de dados que contêm seu TenantId.

Baixar o projeto de exemplo

Pré-requisitos

• Usar o Visual Studio (2012 ou superior)
• Criar três bancos de dados no Banco de Dados SQL do Azure
• Baixar o projeto de exemplo: Ferramentas de Banco de Dados Elástico para o SQL do Azure - Fragmentos Multilocatários
  • Preencha as informações dos seus bancos de dados no início do Program.cs

Esse projeto expande o descrito em Ferramentas de Banco de Dados Elástico para o SQL do Azure - Integração com o Entity Framework adicionando suporte para bancos de dados de fragmentos multilocatários. O projeto cria um aplicativo de console simples para a criação de blogs e postagens. O projeto inclui quatro locatários, além de dois bancos de dados de fragmento de multilocatário. Essa configuração é ilustrada no diagrama anterior.

Crie e execute o aplicativo. Essa execução inicializa o gerenciador de mapas de fragmentos das ferramentas de banco de dados elástico e executa os seguintes testes:

1. Usando o Entity Framework e o LINQ, crie um novo blog e exiba todos os blogs para cada locatário
2. Usando o ADO.NET SqlClient, exiba todos os blogs para um locatário
3. Tente inserir um blog para o locatário errado para verificar se um erro será gerado

Observe que, como a RLS ainda não foi habilitada nos bancos de dados de fragmentos, cada um desses testes revela um problema: locatários podem ver blogs que não pertencem a eles e o aplicativo não é impedido de inserir um blog para o locatário errado. O restante deste artigo descreve como resolver esses problemas impondo o isolamento de locatários com RLS. Há duas etapas:

1. Camada de aplicativo: modifique o código do aplicativo para sempre definir a TenantId atual em SESSION_CONTEXT depois de abrir uma conexão. O projeto de exemplo já define o TenantId dessa maneira.
2. Camada de dados: crie uma política de segurança RLS em cada banco de dados de fragmentos para filtrar as linhas com base na TenantId armazenada em SESSION_CONTEXT. Crie uma política para cada um dos seus bancos de dados de fragmentos, caso contrário, linhas em fragmentos multilocatários não serão filtradas.

1. Camada de aplicativo: definir a TenantId em SESSION_CONTEXT

Primeiro, você se conecta a um banco de dados do fragmento usando as APIs de roteamentos dependentes de dados da biblioteca de cliente do banco de dados elástico. O aplicativo ainda deve informar ao banco de dados qual TenantId está usando a conexão. O TenantId informa à política de segurança de RLS quais linhas devem ser filtradas como pertencentes a outros locatários. Armazene o TenantId atual no SESSION_CONTEXT da conexão.

Uma alternativa para SESSION_CONTEXT é usar CONTEXT_INFO. Mas SESSION_CONTEXT é uma opção melhor. SESSION_CONTEXT é mais fácil de usar, ele retorna NULL por padrão e dá suporte a pares chave e valor.

Entity Framework

Para os aplicativos que usam o Entity Framework, a abordagem mais fácil é definir SESSION_CONTEXT na substituição de ElasticScaleContext descrita em Roteamento dependente de dados usando o EF DbContext. Crie e execute um SqlCommand que define o TenantId no SESSION_CONTEXT como o shardingKey especificado para a conexão. Em seguida, retorne a conexão agenciada por meio do roteamento dependente de dados. Dessa maneira, só é preciso gravar o código uma vez para definir 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;
    }
}
// ...

Agora, o SESSION_CONTEXT será automaticamente definido com a TenantId especificada sempre que ElasticScaleContext for chamado:

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

Para aplicativos que usam o ADO.NET SqlClient, crie uma função de wrapper em torno do método ShardMap.OpenConnectionForKey. Faça com que o wrapper defina automaticamente o TenantId no SESSION_CONTEXT como o TenantId atual antes de retornar uma conexão. Para garantir que SESSION_CONTEXT seja sempre definido, você só deve abrir conexões usando essa função de wrapper.

// 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. Camada de dados: crie a política de segurança no nível da linha

Criar uma política de segurança para filtrar as linhas que cada locatário pode acessar

Agora que o aplicativo está definindo SESSION_CONTEXT com a TenantId atual antes de consultar, uma política de segurança RLS pode filtrar as consultas e excluir as linhas que têm uma TenantId diferente.

O RLS é implementado em Transact-SQL. A função definida pelo usuário define a lógica de acesso e uma política de segurança associa essa função a qualquer quantidade de tabelas. Para este projeto:

1. A função verifica se o aplicativo está conectado ao banco de dados e se a 'TenantId' armazenada em SESSION_CONTEXT corresponde à TenantId de uma determinada linha.

   • O aplicativo está conectado, em vez de algum outro usuário do SQL.

2. Um predicado de FILTER permite que as linhas que atendem ao filtro TenantId passem pelas consultas SELECT, UPDATE e DELETE.

   • Um predicado BLOCK impede que as linhas que não passam no filtro sejam inseridas ou atualizadas.
   • Se SESSION_CONTEXT não tiver sido definido, a função retornará NULL e nenhuma linha estará visível ou poderá ser inserida.

Para habilitar a RLS em todos os fragmentos, execute o T-SQL a seguir usando o Visual Studio (SSDT), SSMS ou o script do PowerShell incluído no projeto. Ou, se você estiver usando Trabalhos do banco de dados elástico, você poderá automatizar a execução desse T-SQL em todos os fragmentos.

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

Agora, se você executar novamente o aplicativo de exemplo, os locatários verão apenas as linhas que pertencem a eles. Além disso, o aplicativo não pode inserir as linhas que pertencem aos locatários diferentes dos atualmente conectados ao banco de dados de fragmentos. Além disso, o aplicativo não pode atualizar a TenantId em todas as linhas que ele pode ver. Se o aplicativo tentar qualquer uma dessas operações, será gerada uma DbUpdateException.

Se você adicionar uma nova tabela posteriormente, altere com ALTER a política de segurança para adicionar predicados de FILTER e BLOCK à nova tabela.

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

Adicionar restrições padrão para preencher automaticamente o TenantId para INSERTs

Você pode colocar uma restrição padrão em cada tabela para preencher automaticamente a TenantId com o valor armazenado atualmente em SESSION_CONTEXT ao inserir as linhas. Há um exemplo a seguir.

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

Agora, o aplicativo não precisa especificar um TenantId ao inserir linhas:

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

(Opcional) Habilitar um superusuário para acessar todas as linhas

Alguns aplicativos podem querer criar um superusuário que pode acessar todas as linhas. Um superusuário pode permitir a emissão de relatórios em todos os locatários em todos os fragmentos. Ou um superusuário poderia executar operações de mesclagem/divisão em fragmentos que envolvem a movimentação de linhas de locatário entre bancos de dados.

Para habilitar um superusuário, crie um novo usuário do SQL (superuser neste exemplo) em cada banco de dados do fragmento. Em seguida, altere a política de segurança com uma nova função de predicado que permite que esse usuário acesse todas as linhas. Essa função é fornecida em seguida.

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

Manutenção

• Adicionando novos fragmentos: execute o script T-SQL para habilitar RLS em qualquer novo fragmento; do contrário, as consultas nesses fragmentos não serão filtradas.
• Adicionando novas tabelas: adicione um predicado de FILTER e BLOCK à política de segurança em todos os fragmentos sempre que uma nova tabela for criada. Caso contrário, consultas na nova tabela não serão filtrados. Essa adição pode ser automatizada usando um gatilho DDL, conforme é descrito em Aplicar Segurança em Nível de Linha automaticamente a tabelas recém-criadas (blog).

Resumo

Ferramentas de banco de dados elástico e segurança em nível de linha podem ser usadas em conjunto para escalar horizontalmente a camada de dados de um aplicativo com suporte para fragmentos multilocatários e de um locatário. Fragmentos multilocatários podem ser usados para armazenar dados com mais eficiência. Essa eficiência é pronunciada quando um grande número de locatários tiver apenas algumas linhas de dados. Fragmentos de locatário único podem dar suporte a locatários premium que têm requisitos de desempenho e de isolamento mais rígidos. Para obter mais informações, confira a referência à Segurança em Nível de Linha.

Recursos adicionais

• O que é um pool elástico do Azure?
• Escalando horizontalmente com o Banco de Dados SQL do Azure
• Padrões de design para aplicativos SaaS multilocatários com o Banco de Dados SQL do Azure
• Autenticação em aplicativos multilocatários usando o Microsoft Entra ID (antigo Azure Active Directory) e o OpenID Connect
• Aplicativo Tailspin Surveys

Perguntas e solicitações de recursos

Em caso de dúvidas, entre em contato conosco na página de perguntas e respostas da Microsoft sobre o banco de dados SQL. E adicione uma solicitação de recursos no Fórum de comentários do Banco de Dados SQL.