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 segurança em nível de linha (RLS) cooperam para permitir o dimensionamento da camada de dados de um aplicativo multilocatário com o Banco de Dados SQL do Azure. Juntas, essas tecnologias ajudam a criar um aplicativo que tem uma camada de dados altamente escalável. A camada de dados oferece suporte a fragmentos multilocatários e usa ADO.NET SqlClient ou Entity Framework. Para obter mais informações, consulte Padrões de design para aplicativos SaaS multilocatários com o Banco de Dados SQL do Azure.

• As ferramentas de banco de dados elástico permitem que os desenvolvedores dimensionem a camada de dados com práticas de fragmentação padrão, usando bibliotecas .NET e modelos de serviço do Azure. O gerenciamento de fragmentos usando a Biblioteca de Cliente do Banco de Dados Elástico ajuda a automatizar e simplificar muitas das tarefas de infraestrutura normalmente associadas à fragmentação.
• A segurança em nível de linha permite que os desenvolvedores armazenem com segurança dados para vários locatários no mesmo banco de dados. As políticas de segurança RLS filtram linhas que não pertencem ao locatário que executa 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 confiar em todo o código do cliente para impor a segurança é arriscada.

Usando esses recursos juntos, um aplicativo pode armazenar dados para vários locatários no mesmo banco de dados de estilhaços. Custa menos por inquilino quando os inquilinos partilham uma base de dados. No entanto, o mesmo aplicativo também pode oferecer aos seus inquilinos premium a opção de pagar por seu próprio fragmento dedicado de inquilino único. Uma das vantagens do isolamento de um único inquilino são as garantias de desempenho mais firmes. Em um banco de dados de locatário único, não há outro locatário competindo por recursos.

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

Faça o download do 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 projeto de exemplo: Elastic DB Tools for Azure SQL - Multi-Tenant Shards
  • Preencha as informações para as suas bases de dados no início do Programa.cs

Este projeto estende o descrito em Elastic DB Tools for Azure SQL - Entity Framework Integration adicionando suporte para bancos de dados de fragmentos multilocatário. O projeto constrói um aplicativo de console simples para criar blogs e posts. O projeto inclui quatro locatários, além de dois bancos de dados de fragmentos multilocatário. Esta configuração é ilustrada no diagrama anterior.

Compile e execute a aplicação. Essa execução inicializa o gerenciador de mapas de estilhaços 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 de cada locatário
2. Usando ADO.NET SqlClient, exibir todos os blogs para um locatário
3. Tente inserir um blog para o locatário errado para verificar se um erro foi gerado

Observe que, como a RLS ainda não foi habilitada nos bancos de dados de estilhaços, cada um desses testes revela um problema: os 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 do locatário com RLS. Existem dois passos:

1. Camada de aplicativo: modifique o código do aplicativo para sempre definir o TenantId atual no 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 estilhaços para filtrar linhas com base no TenantId armazenado no SESSION_CONTEXT. Crie uma política para cada um dos seus bancos de dados de estilhaços, caso contrário, as linhas em fragmentos multilocatários não serão filtradas.

1. Camada de aplicativo: defina TenantId no SESSION_CONTEXT

Primeiro, você se conecta a um banco de dados de estilhaços usando as APIs de roteamento dependentes de dados da biblioteca cliente de 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 RLS quais linhas devem ser filtradas como pertencentes a outros locatários. Armazene o TenantId atual no SESSION_CONTEXT da conexão.

Uma alternativa ao SESSION_CONTEXT é usar CONTEXT_INFO. Mas SESSION_CONTEXT é uma opção melhor. SESSION_CONTEXT é mais fácil de usar, retorna NULL por padrão e suporta pares chave-valor.

Entity Framework

Para aplicativos que usam o Entity Framework, a abordagem mais fácil é definir o SESSION_CONTEXT dentro da substituição ElasticScaleContext descrita em Roteamento dependente de dados usando EF DbContext. Crie e execute um SqlCommand que defina TenantId no SESSION_CONTEXT para o shardingKey especificado para a conexão. Em seguida, retorne a conexão intermediada por meio de roteamento dependente de dados. Dessa forma, você só precisa escrever código uma vez para definir o 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 é definido automaticamente com o TenantId especificado sempre que ElasticScaleContext é invocado:

// 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 ADO.NET SqlClient, crie uma função wrapper em torno do método ShardMap.OpenConnectionForKey. Faça com que o wrapper defina automaticamente TenantId no SESSION_CONTEXT para o TenantId atual antes de retornar uma conexão. Para garantir que SESSION_CONTEXT esteja sempre definida, 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 uma política de segurança em nível de linha

Crie 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 o TenantId atual antes de consultar, uma diretiva de segurança RLS pode filtrar consultas e excluir linhas que têm um TenantId diferente.

A RLS é implementada no Transact-SQL. Uma função definida pelo usuário define a lógica de acesso e uma diretiva de segurança vincula essa função a qualquer número de tabelas. Para este projeto:

1. A função verifica se o aplicativo está conectado ao banco de dados e se o TenantId armazenado no SESSION_CONTEXT corresponde ao TenantId de uma determinada linha.

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

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

   • Um predicado BLOCK impede que linhas que falham no filtro sejam INSERTed ou UPDATEd.
   • 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 seguinte T-SQL usando o Visual Studio (SSDT), o SSMS ou o script do PowerShell incluído no projeto. Ou, se você estiver usando trabalhos de banco de dados elástico, 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 o aplicativo de exemplo novamente, os locatários verão apenas as linhas que pertencem a eles. Além disso, o aplicativo não pode inserir linhas que pertencem a locatários diferentes daquele atualmente conectado ao banco de dados de estilhaços. Além disso, o aplicativo não pode atualizar o TenantId em nenhuma linha que ele possa ver. Se o aplicativo tentar fazer qualquer uma delas, um DbUpdateException será gerado.

Se você adicionar uma nova tabela posteriormente, ALTER a política de segurança para adicionar predicados FILTER e BLOCK na 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 TenantId para INSERTs

Você pode colocar uma restrição padrão em cada tabela para preencher automaticamente o TenantId com o valor atualmente armazenado em SESSION_CONTEXT ao inserir linhas. Segue-se um exemplo.

-- 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) Permitir que um superusuário acesse todas as linhas

Alguns aplicativos podem querer criar um superusuário que possa acessar todas as linhas. Um superusuário pode habilitar relatórios em todos os locatários em todos os fragmentos. Ou um superusuário pode executar operações de mesclagem dividida em fragmentos que envolvem mover linhas de locatário entre bancos de dados.

Para habilitar um superusuário, crie um novo usuário SQL (superuser neste exemplo) em cada banco de dados de estilhaços. 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. Tal função é dada 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 a RLS em quaisquer novos fragmentos, caso contrário, as consultas nesses fragmentos não serão filtradas.
• Adicionando novas tabelas: adicione um predicado FILTER e BLOCK à política de segurança em todos os fragmentos sempre que uma nova tabela for criada. Caso contrário, as consultas na nova tabela não serão filtradas. 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

As ferramentas de banco de dados elástico e a segurança em nível de linha podem ser usadas juntas para expandir a camada de dados de um aplicativo com suporte para fragmentos multilocatário e de locatário único. Os fragmentos multilocatários podem ser usados para armazenar dados de forma mais eficiente. Essa eficiência é pronunciada quando um grande número de locatários tem apenas algumas linhas de dados. Os fragmentos de inquilino único podem suportar inquilinos premium com requisitos de desempenho e isolamento mais rigorosos. Para obter mais informações, consulte Referência de segurança em nível de linha.

Recursos adicionais

• O que é um pool elástico do Azure?
• Aumentar horizontalmente com a Base de Dados SQL do Azure
• Padrões de Estrutura para Aplicações de SaaS Multi-inquilinos com a Base de Dados SQL do Azure
• Autenticação em aplicativos multilocatário, usando o Microsoft Entra ID (anteriormente Azure Ative Directory) e o OpenID Connect
• Aplicação Tailspin Surveys

Perguntas e solicitações de recursos

Para dúvidas, entre em contato conosco na página de perguntas e respostas da Microsoft para o Banco de dados SQL. E adicione quaisquer solicitações de recursos ao fórum de comentários do Banco de dados SQL.