Partilhar via

Segurança a nível de linha

Aplica-se a:SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceAzure Synapse AnalyticsEndpoint de análise SQL no Microsoft FabricArmazém no Microsoft FabricBase de dados SQL no Microsoft Fabric

Gráfico decorativo de segurança ao nível da linha.

A segurança em nível de linha (RLS) permite que você use a associação ao grupo ou o contexto de execução para controlar o acesso a linhas em uma tabela de banco de dados.

A segurança em nível de linha simplifica o design e a codificação da segurança em seu aplicativo. A RLS ajuda a implementar restrições no acesso à linha de dados. Por exemplo, você pode garantir que os trabalhadores acessem apenas as linhas de dados pertinentes ao departamento. Outro exemplo é restringir o acesso dos clientes aos dados apenas aos dados relevantes para a sua empresa.

A lógica de restrição de acesso está localizada na camada de banco de dados em vez de longe dos dados em outra camada de aplicativo. O sistema de banco de dados aplica as restrições de acesso sempre que o acesso aos dados é tentado a partir de qualquer camada. Isto torna o seu sistema de segurança mais fiável e robusto, reduzindo a área de superfície do seu sistema de segurança.

Implemente a RLS usando a instrução Transact-SQL CREATE SECURITY POLICY e predicados criados como funções com valor de tabela embutido.

A segurança em nível de linha foi introduzida pela primeira vez no SQL Server 2016 (13.x).

Note

Este artigo é focado nas plataformas SQL Server e SQL do Azure. Para o Microsoft Fabric, consulte Segurança em nível de linha no Microsoft Fabric.

Description

A segurança em nível de linha (RLS) suporta dois tipos de predicados de segurança:

  • Os predicados de filtro filtram silenciosamente as linhas disponíveis para operações de leitura (SELECT, UPDATE, e DELETE).

  • Predicados de bloqueio bloqueiam explicitamente as operações de escrita (AFTER INSERT, AFTER UPDATE, BEFORE UPDATE, BEFORE DELETE) que violam o predicado.

O acesso a dados ao nível de linha em uma tabela é restrito por um predicado de segurança definido como uma função com valor de tabela em linha. A função é então invocada e imposta por uma política de segurança. Para predicados de filtro, o aplicativo não está ciente de linhas que são filtradas a partir do conjunto de resultados. Se todas as linhas forem filtradas, um conjunto nulo será retornado. Para predicados de bloco, quaisquer operações que violem o predicado falharão com um erro.

Os predicados de filtro são aplicados durante a leitura de dados da tabela base. Elas afetam todas as operações de obtenção de dados: SELECT, DELETE, e UPDATE. Os usuários não podem selecionar ou excluir linhas filtradas. O usuário não pode atualizar linhas filtradas. Mas, é possível atualizar as linhas de tal forma que elas serão filtradas depois. Os predicados de bloco afetam todas as operações de escrita.

  • AFTER INSERT e AFTER UPDATE predicados podem impedir que os usuários atualizem linhas para valores que violam o predicado.

  • BEFORE UPDATE Os predicados podem impedir que os utilizadores atualizem linhas que atualmente violam o predicado.

  • BEFORE DELETE predicados podem bloquear operações de exclusão.

Os predicados de filtro e bloco e as diretivas de segurança têm o seguinte comportamento:

  • Você pode definir uma função de predicado que se une a outra tabela e/ou invoca uma função. Se a política de segurança for criada com SCHEMABINDING = ON (o padrão), a associação ou função estará acessível a partir da consulta e funcionará conforme o esperado, sem verificações de permissão extras. Se a política de segurança for criada com SCHEMABINDING = OFF, os utilizadores precisarão de SELECT permissões nessas tabelas e funções adicionais para consultar a tabela de destino. Se a função de predicado invocar uma função de valor escalar CLR, a EXECUTE permissão será necessária adicionalmente.

  • Você pode emitir uma consulta em relação a uma tabela que tenha um predicado de segurança definido, mas desabilitado. As linhas filtradas ou bloqueadas não são afetadas.

  • Se um dbo usuário, um membro da db_owner função ou o proprietário da tabela consultar uma tabela que tenha uma diretiva de segurança definida e habilitada, as linhas serão filtradas ou bloqueadas conforme definido pela diretiva de segurança.

  • Tentativas de alterar o esquema de uma tabela vinculada por uma diretiva de segurança vinculada a esquema resulta em um erro. No entanto, as colunas não referenciadas pelo predicado podem ser alteradas.

  • Tentativas de adicionar um predicado em uma tabela que já tem um definido para a operação especificada resulta em um erro. Isto acontece quer o predicado esteja ativado ou não.

  • Tentativas de modificar uma função que é usada como um predicado em uma tabela dentro de uma diretiva de segurança vinculada ao esquema resulta em um erro.

  • A definição de várias políticas de segurança ativas que contêm predicados não sobrepostos é bem-sucedida.

Os predicados de filtro têm o seguinte comportamento:

  • Defina uma política de segurança que filtre as linhas de uma tabela. O aplicativo não tem conhecimento de quaisquer linhas que foram filtradas para operações de SELECT, UPDATE e DELETE. Incluindo situações em que todas as linhas são filtradas e removidas. A aplicação pode processar INSERT linhas, mesmo que estas sejam filtradas durante qualquer outra operação.

Os predicados de bloco têm o seguinte comportamento:

  • Os predicados de bloco para UPDATE são divididos em operações separadas para BEFORE e AFTER. Não pode, por exemplo, impedir que os utilizadores atualizem uma linha para ter um valor superior ao atual. Se esse tipo de lógica for necessário, você deve usar gatilhos com as tabelas intermediárias DELETED e INSERT para fazer referência aos valores antigos e novos juntos.

  • O otimizador não verificará um predicado de bloco AFTER UPDATE se as colunas utilizadas pela função predicado não tiverem sido alteradas. Por exemplo: Alice não deve ser capaz de alterar um salário para ser superior a 100.000. Alice pode alterar o endereço de um funcionário cujo salário já é superior a 100.000, desde que as colunas referenciadas no predicado não tenham sido alteradas.

  • Nenhuma alteração foi feita nas APIs em massa, incluindo BULK INSERT. Isso significa que os predicados AFTER INSERT de bloco aplicam-se às operações de inserção em massa exatamente como se aplicariam às operações de inserção regulares.

Casos de uso

Aqui estão exemplos de design de como a segurança em nível de linha (RLS) pode ser usada:

  • Um hospital pode criar uma política de segurança que permita aos enfermeiros visualizar linhas de dados apenas para os seus pacientes.

  • Um banco pode criar uma política para restringir o acesso a linhas de dados financeiros com base na divisão de negócios ou função de um funcionário na empresa.

  • Um aplicativo multilocatário pode criar uma política para impor uma separação lógica das linhas de dados de cada locatário das linhas de todos os outros locatários. A eficiência é alcançada pelo armazenamento de dados para muitos locatários em uma única tabela. Cada locatário pode ver apenas suas linhas de dados.

Os predicados do filtro RLS são funcionalmente equivalentes a acrescentar uma WHERE cláusula. O predicado pode ser tão sofisticado quanto as práticas comerciais ditam, ou a cláusula pode ser tão simples quanto WHERE TenantId = 42.

Em termos mais formais, a RLS introduz o controle de acesso baseado em predicados. Apresenta uma avaliação flexível, centralizada e baseada em predicados. O predicado pode ser baseado em metadados ou em qualquer outro critério que o administrador determine conforme apropriado. O predicado é usado como um critério para determinar se o usuário tem o acesso apropriado aos dados com base nos atributos do usuário. O controle de acesso baseado em rótulo pode ser implementado usando o controle de acesso baseado em predicados.

Permissions

Criar, alterar ou descartar políticas de segurança requer a ALTER ANY SECURITY POLICY permissão. Criar ou descartar uma política de segurança requer ALTER permissão no esquema.

Além disso, as seguintes permissões são necessárias para cada predicado adicionado:

  • SELECT e REFERENCES permissões sobre a função que está sendo usada como predicado.

  • REFERENCES permissão na tabela de destino sendo vinculada à política.

  • REFERENCES permissão em todas as colunas da tabela de destino usadas como argumentos.

As políticas de segurança aplicam-se a todos os utilizadores, incluindo os utilizadores dbo na base de dados. Os usuários do Dbo podem alterar ou descartar as políticas de segurança, no entanto, suas alterações nas políticas de segurança podem ser auditadas. Se usuários com privilégios elevados, como sysadmin ou db_owner, precisarem ver todas as linhas para solucionar problemas ou validar dados, a diretiva de segurança deverá ser escrita para permitir isso.

Se uma diretiva de segurança for criada com SCHEMABINDING = OFFo , para consultar a tabela de destino, os usuários deverão ter a SELECT permissão ou EXECUTE na função de predicado e quaisquer tabelas, exibições ou funções adicionais usadas dentro da função de predicado. Se uma diretiva de segurança for criada com SCHEMABINDING = ON (o padrão), essas verificações de permissão serão ignoradas quando os usuários consultarem a tabela de destino.

Melhores práticas

  • É altamente recomendável criar um esquema separado para os objetos RLS: funções de predicado e políticas de segurança. Isso ajuda a separar as permissões necessárias nesses objetos especiais das tabelas de destino. Pode ser necessária uma separação adicional para diferentes políticas e funções de predicado em bases de dados multilocatário, mas não deve ser considerada como um padrão em todos os casos.

  • A ALTER ANY SECURITY POLICY permissão destina-se a utilizadores altamente privilegiados (como um gestor de políticas de segurança). O gestor de políticas de segurança não necessita SELECT de permissão nas tabelas que protege.

  • Evite conversões de tipo em funções de predicados para evitar possíveis erros de tempo de execução.

  • Evite a recursão em funções de predicados sempre que possível para evitar a degradação do desempenho. O otimizador de consulta tentará detetar recursões diretas, mas não é garantido que encontre recursões indiretas. Uma recursão indireta ocorre quando uma segunda função chama a função predicado.

  • Evite o uso excessivo de junções de tabela em funções de predicados para maximizar o desempenho.

Evite a lógica de predicados que depende de opções SET específicas da sessão: Embora seja improvável que sejam usadas em aplicativos práticos, as funções de predicados cuja lógica depende de certas opções específicas SET da sessão podem vazar informações se os usuários forem capazes de executar consultas arbitrárias. Por exemplo, uma função de predicado que converte implicitamente uma cadeia de caracteres em datetime pode filtrar linhas diferentes com base na SET DATEFORMAT opção para a sessão atual. Em geral, as funções de predicados devem obedecer às seguintes regras:

Nota de segurança: ataques de canal lateral

Gestor de políticas de segurança maliciosas

É importante observar que um gerente de políticas de segurança mal-intencionado, com permissões suficientes para criar uma política de segurança em cima de uma coluna confidencial e com permissão para criar ou alterar funções com valor de tabela embutido, pode entrar em conluio com outro usuário que tenha permissões de seleção em uma tabela para executar a exfiltração de dados criando maliciosamente funções com valor de tabela embutidas projetadas para usar ataques de canal lateral para inferir dados. Tais ataques exigiriam conluio (ou permissões excessivas concedidas a um usuário mal-intencionado) e provavelmente exigiriam várias iterações de modificação da política (exigindo permissão para remover o predicado para quebrar a vinculação do esquema), modificando as funções com valor de tabela embutido e executando repetidamente instruções select na tabela de destino. Recomendamos que você limite as permissões conforme necessário e monitore qualquer atividade suspeita. Atividades como políticas em constante mudança e funções com valor de tabela em linha relacionadas à segurança em nível de linha devem ser monitoradas.

Consultas cuidadosamente elaboradas

É possível causar vazamento de informações usando consultas cuidadosamente criadas que usam erros para exfiltrar dados. Por exemplo, SELECT 1/(SALARY-100000) FROM PAYROLL WHERE NAME='John Doe'; informaria um usuário mal-intencionado de que o salário de John Doe é exatamente de US$ 100.000. Embora haja um predicado de segurança em vigor para impedir que um usuário mal-intencionado consulte diretamente o salário de outras pessoas, o usuário pode determinar quando a consulta retorna uma exceção de divisão por zero.

Compatibilidade entre funcionalidades

Regra geral, a segurança em nível de linha funcionará conforme o esperado em todos os aspetos das funcionalidades. No entanto, existem algumas exceções. Esta seção documenta várias notas e advertências sobre o uso da segurança em nível de linha com determinados outros recursos do SQL Server.

  • DBCC SHOW_STATISTICS Relata estatísticas sobre dados não filtrados e pode vazar informações protegidas por uma política de segurança. Por esse motivo, o acesso para exibir um objeto de estatísticas para uma tabela com uma diretiva de segurança em nível de linha é restrito. O usuário deve ser proprietário da tabela ou deve ser membro da sysadmin função de servidor fixa, da db_owner função de banco de dados fixa ou da db_ddladmin função de banco de dados fixa.

  • Fluxo de ficheiros: RLS é incompatível com Filestream.

  • PolyBase: A RLS tem suporte com tabelas externas no Azure Synapse e no SQL Server 2019 CU7 ou versões superiores.

  • Tabelas otimizadas para memória: A função de valor de tabela inline usada como um predicado de segurança em uma tabela otimizada para memória deve ser definida usando a opção WITH NATIVE_COMPILATION. Com essa opção, os recursos de idioma não suportados por tabelas otimizadas para memória serão banidos e o erro apropriado será emitido no momento da criação. Para obter mais informações, consulte Segurança ao nível da linha em Tabelas Otimizadas para Memória.

  • Visualizações indexadas: Em geral, as políticas de segurança podem ser criadas sobre visualizações, e visualizações podem ser criadas sobre tabelas vinculadas por políticas de segurança. No entanto, as vistas indexadas não podem ser criadas sobre tabelas que têm uma política de segurança, porque as pesquisas de linha através do índice ignorariam a política.

  • Alteração da Captura de Dados: A Captura de Alteração de Dados (CDC) pode fazer vazar linhas inteiras que devem ser filtradas para serem visualizadas por membros de db_owner ou usuários que são membros da função "delimitadora" especificada quando o CDC está ativado para uma tabela. Você pode definir explicitamente essa função para NULL permitir que todos os usuários acessem os dados de alteração. De facto, db_owner e os membros dessa função de controlo podem ver todas as alterações de dados na tabela, mesmo que haja uma política de segurança na tabela.

  • Controlo de Alterações: O controlo de alterações pode expor a chave primária de linhas que devem ser filtradas para utilizadores com permissões SELECT e VIEW CHANGE TRACKING. Os valores reais dos dados não são vazados; apenas o facto de a coluna A ter sido atualizada/inserida/eliminada para a linha com uma determinada chave primária. Isso é problemático se a chave primária contiver um elemento confidencial, como um número de segurança social. No entanto, na prática, isso CHANGETABLE é quase sempre juntado com a tabela original para obter os dados mais recentes.

  • Pesquisa de Texto Completo: É esperado um impacto no desempenho para consultas que usam as seguintes funções de Pesquisa de Texto Completo e Pesquisa Semântica, devido a uma junção extra introduzida para aplicar segurança em nível de linha e evitar vazar as chaves primárias de linhas que devem ser filtradas: CONTAINSTABLE, FREETEXTTABLE, semantickeyphrasetable, semanticsimilaritydetailstable, semanticsimilaritytable.

  • Índices Columnstore: A RLS é compatível com índices columnstore agrupados e não clusterizados. No entanto, como a segurança em nível de linha aplica uma função, é possível que o otimizador modifique o plano de consulta para que ele não use o modo de lote.

  • Vistas particionadas: Os predicados de bloco não podem ser definidos em modos de exibição particionados e os modos de exibição particionados não podem ser criados em cima de tabelas que usam predicados de bloco. Os predicados de filtro são compatíveis com vistas particionadas.

  • Tabelas temporais: As tabelas temporais são compatíveis com a SPI. No entanto, os predicados de segurança na tabela atual não são replicados automaticamente para a tabela de histórico. Para aplicar uma política de segurança às tabelas atual e de histórico, você deve adicionar individualmente um predicado de segurança em cada tabela.

Outras limitações:

  • O Microsoft Fabric e o Azure Synapse Analytics suportam apenas predicados de filtro. Atualmente, não há suporte para predicados de bloco no Microsoft Fabric e no Azure Synapse Analytics.

Examples

A. Cenário para usuários que se autenticam no banco de dados

Este exemplo cria três usuários e cria e preenche uma tabela com seis linhas. Em seguida, ele cria uma função com valor de tabela embutido e uma política de segurança para a tabela. O exemplo mostra como as instruções SQL SELECT são filtradas para os diferentes utilizadores.

Crie três contas de usuário que demonstrem diferentes recursos de acesso.

CREATE USER Manager WITHOUT LOGIN;
CREATE USER SalesRep1 WITHOUT LOGIN;
CREATE USER SalesRep2 WITHOUT LOGIN;
GO

Crie uma tabela para armazenar dados.

CREATE SCHEMA Sales
GO
CREATE TABLE Sales.Orders
    (
    OrderID int,
    SalesRep nvarchar(50),
    Product nvarchar(50),
    Quantity smallint
    );

Preencha a tabela com seis linhas de dados, mostrando três pedidos para cada representante de vendas.

INSERT INTO Sales.Orders  VALUES (1, 'SalesRep1', 'Valve', 5);
INSERT INTO Sales.Orders  VALUES (2, 'SalesRep1', 'Wheel', 2);
INSERT INTO Sales.Orders  VALUES (3, 'SalesRep1', 'Valve', 4);
INSERT INTO Sales.Orders  VALUES (4, 'SalesRep2', 'Bracket', 2);
INSERT INTO Sales.Orders  VALUES (5, 'SalesRep2', 'Wheel', 5);
INSERT INTO Sales.Orders  VALUES (6, 'SalesRep2', 'Seat', 5);
-- View the 6 rows in the table
SELECT * FROM Sales.Orders;

Conceda acesso de leitura na tabela a cada um dos usuários.

GRANT SELECT ON Sales.Orders TO Manager;
GRANT SELECT ON Sales.Orders TO SalesRep1;
GRANT SELECT ON Sales.Orders TO SalesRep2;
GO

Crie um novo esquema e uma função com valor de tabela embutido. A função retorna 1 quando uma linha na SalesRep coluna é a mesma que o usuário que executa a consulta (@SalesRep = USER_NAME()) ou se o usuário que executa a consulta é o usuário do Gerenciador (USER_NAME() = 'Manager'). Este exemplo de uma função definida pelo usuário com valor de tabela é útil para servir como um filtro para a diretiva de segurança criada na próxima etapa.

CREATE SCHEMA Security;
GO

CREATE FUNCTION Security.tvf_securitypredicate(@SalesRep AS nvarchar(50))
    RETURNS TABLE
WITH SCHEMABINDING
AS
    RETURN SELECT 1 AS tvf_securitypredicate_result
WHERE @SalesRep = USER_NAME() OR USER_NAME() = 'Manager';
GO

Crie uma política de segurança adicionando a função como um predicado de filtro. Para habilitar a política, defina o STATE como ON.

CREATE SECURITY POLICY SalesFilter
ADD FILTER PREDICATE Security.tvf_securitypredicate(SalesRep)
ON Sales.Orders
WITH (STATE = ON);
GO

Permitir SELECT permissões para a tvf_securitypredicate função:

GRANT SELECT ON Security.tvf_securitypredicate TO Manager;
GRANT SELECT ON Security.tvf_securitypredicate TO SalesRep1;
GRANT SELECT ON Security.tvf_securitypredicate TO SalesRep2;

Agora teste o predicado de filtragem, selecionando na tabela Sales.Orders como se fosse cada usuário.

EXECUTE AS USER = 'SalesRep1';
SELECT * FROM Sales.Orders;
REVERT;

EXECUTE AS USER = 'SalesRep2';
SELECT * FROM Sales.Orders;
REVERT;

EXECUTE AS USER = 'Manager';
SELECT * FROM Sales.Orders;
REVERT;

O gerente deve ver todas as seis linhas. Os Sales1 e Sales2 utilizadores só devem ver as suas próprias vendas.

Altere a política de segurança para desativá-la.

ALTER SECURITY POLICY SalesFilter
WITH (STATE = OFF);

Agora Sales1 e Sales2 os usuários podem ver todas as seis linhas.

Conecte-se ao banco de dados SQL para limpar recursos deste exercício de exemplo:

DROP USER SalesRep1;
DROP USER SalesRep2;
DROP USER Manager;

DROP SECURITY POLICY SalesFilter;
DROP TABLE Sales.Orders;
DROP FUNCTION Security.tvf_securitypredicate;
DROP SCHEMA Security;
DROP SCHEMA Sales;

B. Cenários para usar a Segurança em Nível de Linha em uma tabela externa do Azure Synapse

Este pequeno exemplo cria três usuários e uma tabela externa com seis linhas. Em seguida, ele cria uma função com valor de tabela embutido e uma política de segurança para a tabela externa. O exemplo mostra como as instruções de seleção são filtradas para vários utilizadores.

Prerequisites

  1. Você deve ter um pool SQL dedicado. Consulte Criar um pool SQL dedicado
  2. O servidor que hospeda seu pool SQL dedicado deve ser registrado com a ID do Microsoft Entra (anteriormente Azure Ative Directory) e você deve ter uma conta de armazenamento do Azure com Storage Blog Data Contributor permissões. Siga os passos para Utilizar pontos de extremidade de serviço de rede virtual e regras para servidores no SQL Database do Azure.
  3. Crie um sistema de arquivos para sua conta de Armazenamento do Azure. Use o Gerenciador de Armazenamento do Azure para exibir sua conta de armazenamento. Clique com o botão direito do mouse em contêineres e selecione Criar sistema de arquivos.

Depois de ter os pré-requisitos estabelecidos, crie três contas de usuário que demonstrem diferentes recursos de acesso.

--run in master
CREATE LOGIN Manager WITH PASSWORD = '<user_password>'
GO
CREATE LOGIN Sales1 WITH PASSWORD = '<user_password>'
GO
CREATE LOGIN Sales2 WITH PASSWORD = '<user_password>'
GO

--run in both the master database and in your dedicated SQL pool database
CREATE USER Manager FOR LOGIN Manager;
CREATE USER Sales1  FOR LOGIN Sales1;
CREATE USER Sales2  FOR LOGIN Sales2 ;

Crie uma tabela para armazenar dados.

CREATE TABLE Sales
    (
    OrderID int,
    SalesRep sysname,
    Product varchar(10),
    Qty int
    );

Preencha a tabela com seis linhas de dados, mostrando três pedidos para cada representante de vendas.

INSERT INTO Sales VALUES (1, 'Sales1', 'Valve', 5);
INSERT INTO Sales VALUES (2, 'Sales1', 'Wheel', 2);
INSERT INTO Sales VALUES (3, 'Sales1', 'Valve', 4);
INSERT INTO Sales VALUES (4, 'Sales2', 'Bracket', 2);
INSERT INTO Sales VALUES (5, 'Sales2', 'Wheel', 5);
INSERT INTO Sales VALUES (6, 'Sales2', 'Seat', 5);
-- View the 6 rows in the table
SELECT * FROM Sales;

Crie uma tabela externa do Azure Synapse a partir da Sales tabela que você criou.

CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<user_password>';

CREATE DATABASE SCOPED CREDENTIAL msi_cred WITH IDENTITY = 'Managed Service Identity';

CREATE EXTERNAL DATA SOURCE ext_datasource_with_abfss WITH (TYPE = hadoop, LOCATION = 'abfss://<file_system_name@storage_account>.dfs.core.windows.net', CREDENTIAL = msi_cred);

CREATE EXTERNAL FILE FORMAT MSIFormat  WITH (FORMAT_TYPE=DELIMITEDTEXT);

CREATE EXTERNAL TABLE Sales_ext WITH (LOCATION='<your_table_name>', DATA_SOURCE=ext_datasource_with_abfss, FILE_FORMAT=MSIFormat, REJECT_TYPE=Percentage, REJECT_SAMPLE_VALUE=100, REJECT_VALUE=100)
AS SELECT * FROM sales;

Conceda SELECT aos três utilizadores na tabela externa Sales_ext que criou.

GRANT SELECT ON Sales_ext TO Sales1;
GRANT SELECT ON Sales_ext TO Sales2;
GRANT SELECT ON Sales_ext TO Manager;

Crie um novo esquema e uma função com valor de tabela embutido, você pode ter concluído isso no exemplo A. A função retorna 1 quando uma linha na SalesRep coluna é a mesma que o usuário que executa a consulta (@SalesRep = USER_NAME()) ou se o usuário que executa a consulta é o Manager usuário (USER_NAME() = 'Manager').

CREATE SCHEMA Security;
GO

CREATE FUNCTION Security.fn_securitypredicate(@SalesRep AS sysname)
    RETURNS TABLE
WITH SCHEMABINDING
AS
    RETURN SELECT 1 AS fn_securitypredicate_result
WHERE @SalesRep = USER_NAME() OR USER_NAME() = 'Manager';

Crie uma política de segurança na tabela externa usando a função valor de tabela inline como um predicado de filtro. Para habilitar a política, defina o STATE como ON.

CREATE SECURITY POLICY SalesFilter_ext
ADD FILTER PREDICATE Security.fn_securitypredicate(SalesRep)
ON dbo.Sales_ext
WITH (STATE = ON);

Agora, teste o predicado de filtragem, selecionando a partir da Sales_ext tabela externa. Inicie sessão como cada utilizador, Sales1, Sales2e Manager. Execute o seguinte comando como cada usuário.

SELECT * FROM Sales_ext;

O Manager deve ver todas as seis linhas. Os Sales1 e Sales2 usuários só devem ver suas vendas.

Altere a política de segurança para desativá-la.

ALTER SECURITY POLICY SalesFilter_ext
WITH (STATE = OFF);

Agora, os utilizadores Sales1 e Sales2 podem ver todas as seis linhas.

Conecte-se ao banco de dados do Azure Synapse para limpar recursos deste exercício de exemplo:

DROP USER Sales1;
DROP USER Sales2;
DROP USER Manager;

DROP SECURITY POLICY SalesFilter_ext;
DROP TABLE Sales;
DROP EXTERNAL TABLE Sales_ext;
DROP EXTERNAL DATA SOURCE ext_datasource_with_abfss ;
DROP EXTERNAL FILE FORMAT MSIFormat;
DROP DATABASE SCOPED CREDENTIAL msi_cred;
DROP MASTER KEY;

Conecte-se ao banco de dados do master servidor lógico para limpar recursos:

DROP LOGIN Sales1;
DROP LOGIN Sales2;
DROP LOGIN Manager;

C. Cenário para usuários que se conectam ao banco de dados por meio de um aplicativo de camada intermediária

Note

Neste exemplo, a funcionalidade de predicados de bloqueio não é atualmente suportada para o Microsoft Fabric e o Azure Synapse; assim, a inserção de linhas com o ID de utilizador errado não é impedida.

Este exemplo mostra como um aplicativo de camada intermediária pode implementar filtragem de conexão, onde os usuários (ou locatários) do aplicativo compartilham o mesmo usuário do SQL Server (o aplicativo). O aplicativo define o ID de usuário do aplicativo atual em SESSION_CONTEXT depois de se conectar ao banco de dados e, em seguida, as políticas de segurança filtram de forma transparente as linhas que não devem estar visíveis para esse ID e também impedem que o usuário insira linhas para o ID de usuário errado. Nenhuma outra alteração no aplicativo é necessária.

Crie uma tabela para armazenar dados.

CREATE TABLE Sales (
    OrderId int,
    AppUserId int,
    Product varchar(10),
    Qty int
);

Preencha a tabela com seis linhas de dados, mostrando três ordens para cada usuário do aplicativo.

INSERT Sales VALUES
    (1, 1, 'Valve', 5),
    (2, 1, 'Wheel', 2),
    (3, 1, 'Valve', 4),
    (4, 2, 'Bracket', 2),
    (5, 2, 'Wheel', 5),
    (6, 2, 'Seat', 5);

Crie um usuário com privilégios baixos que o aplicativo usará para se conectar.

-- Without login only for demo
CREATE USER AppUser WITHOUT LOGIN;
GRANT SELECT, INSERT, UPDATE, DELETE ON Sales TO AppUser;

-- Never allow updates on this column
DENY UPDATE ON Sales(AppUserId) TO AppUser;

Crie um novo esquema e função de predicado, que usará o ID de usuário do aplicativo armazenado em SESSION_CONTEXT() para filtrar linhas.

CREATE SCHEMA Security;
GO

CREATE FUNCTION Security.fn_securitypredicate(@AppUserId int)
    RETURNS TABLE
    WITH SCHEMABINDING
AS
    RETURN SELECT 1 AS fn_securitypredicate_result
    WHERE
        DATABASE_PRINCIPAL_ID() = DATABASE_PRINCIPAL_ID('AppUser')
        AND CAST(SESSION_CONTEXT(N'UserId') AS int) = @AppUserId;
GO

Crie uma política de segurança que adicione essa função como um predicado de filtro e um predicado de bloco em Sales. O predicado de bloco só precisa de AFTER INSERT, porque BEFORE UPDATE e BEFORE DELETE já estão filtrados, e AFTER UPDATE é desnecessário porque a AppUserId coluna não pode ser atualizada para outros valores, devido à permissão de coluna definida anteriormente.

CREATE SECURITY POLICY Security.SalesFilter
    ADD FILTER PREDICATE Security.fn_securitypredicate(AppUserId)
        ON dbo.Sales,
    ADD BLOCK PREDICATE Security.fn_securitypredicate(AppUserId)
        ON dbo.Sales AFTER INSERT
    WITH (STATE = ON);

Agora podemos simular a filtragem de conexão ao selecionar na Sales tabela após definir diferentes IDs de utilizador em SESSION_CONTEXT(). Na prática, o aplicativo SESSION_CONTEXT() é responsável por definir o ID de usuário atual após abrir uma conexão. Definir o @read_only parâmetro para 1 impedir que o valor seja alterado novamente até que a conexão seja fechada (retornada ao pool de conexões).

EXECUTE AS USER = 'AppUser';
EXEC sp_set_session_context @key=N'UserId', @value=1;
SELECT * FROM Sales;
GO

/* Note: @read_only prevents the value from changing again until the connection is closed (returned to the connection pool)*/
EXEC sp_set_session_context @key=N'UserId', @value=2, @read_only=1;

SELECT * FROM Sales;
GO

INSERT INTO Sales VALUES (7, 1, 'Seat', 12); -- error: blocked from inserting row for the wrong user ID
GO

REVERT;
GO

Limpe os recursos do banco de dados.

DROP USER AppUser;

DROP SECURITY POLICY Security.SalesFilter;
DROP TABLE Sales;
DROP FUNCTION Security.fn_securitypredicate;
DROP SCHEMA Security;

D. Cenário para usar uma tabela de pesquisa para o predicado de segurança

Este exemplo usa uma tabela de pesquisa para o link entre o identificador de usuário e o valor que está sendo filtrado, em vez de precisar especificar o identificador de usuário na tabela de fatos. Ele cria três usuários e cria e preenche uma tabela de fatos, Sample.Salescom seis linhas e uma tabela de pesquisa com duas linhas. Em seguida, ele cria uma função embutida com valor de tabela que une a tabela de fatos à pesquisa para obter o identificador de usuário e cria uma política de segurança para a tabela. O exemplo mostra como as instruções SQL SELECT são filtradas para os diferentes utilizadores.

Crie três contas de usuário que demonstrem diferentes recursos de acesso.

CREATE USER Manager WITHOUT LOGIN;
CREATE USER Sales1 WITHOUT LOGIN;
CREATE USER Sales2 WITHOUT LOGIN;

Crie um Sample esquema e uma tabela de fatos, Sample.Sales, para armazenar dados.

CREATE SCHEMA Sample;
GO
CREATE TABLE Sample.Sales
    (
    OrderID int,
    Product varchar(10),
    Qty int
    );

Preencha Sample.Sales com seis linhas de dados.

INSERT INTO Sample.Sales VALUES (1, 'Valve', 5);
INSERT INTO Sample.Sales VALUES (2, 'Wheel', 2);
INSERT INTO Sample.Sales VALUES (3, 'Valve', 4);
INSERT INTO Sample.Sales VALUES (4, 'Bracket', 2);
INSERT INTO Sample.Sales VALUES (5, 'Wheel', 5);
INSERT INTO Sample.Sales VALUES (6, 'Seat', 5);
-- View the 6 rows in the table
SELECT * FROM Sample.Sales;

Crie uma tabela para armazenar os dados de pesquisa – neste caso, uma relação entre Salesrep e Product.

CREATE TABLE Sample.Lk_Salesman_Product
  ( Salesrep sysname,
    Product varchar(10)
  ) ;

Preencha a tabela de pesquisa com dados de exemplo, vinculando um Product a cada representante de vendas.

INSERT INTO Sample.Lk_Salesman_Product VALUES ('Sales1', 'Valve');
INSERT INTO Sample.Lk_Salesman_Product VALUES ('Sales2', 'Wheel');
-- View the 2 rows in the table
SELECT * FROM Sample.Lk_Salesman_Product;

Conceda acesso de leitura na tabela de fatos a cada um dos usuários.

GRANT SELECT ON Sample.Sales TO Manager;
GRANT SELECT ON Sample.Sales TO Sales1;
GRANT SELECT ON Sample.Sales TO Sales2;

Crie um novo esquema e uma função com valor de tabela embutido. A função retorna 1 quando um usuário consulta a tabela Sample.Sales de fatos e a SalesRep coluna da tabela Lk_Salesman_Product é a mesma que o usuário que executa a consulta (@SalesRep = USER_NAME()) quando unido à tabela de fatos na Product coluna, ou se o usuário que executa a consulta é o Manager usuário (USER_NAME() = 'Manager').

CREATE SCHEMA Security ;
GO
CREATE FUNCTION Security.fn_securitypredicate
         (@Product AS varchar(10))
RETURNS TABLE
WITH SCHEMABINDING
AS
           RETURN ( SELECT 1 as Result
                     FROM Sample.Sales f
            INNER JOIN Sample.Lk_Salesman_Product s
                     ON s.Product = f.Product
            WHERE ( f.product = @Product
                    AND s.SalesRep = USER_NAME() )
                 OR USER_NAME() = 'Manager'
                   ) ;

Crie uma política de segurança adicionando a função como um predicado de filtro. Para habilitar a política, defina o STATE como ON.

CREATE SECURITY POLICY SalesFilter
ADD FILTER PREDICATE Security.fn_securitypredicate(Product)
ON Sample.Sales
WITH (STATE = ON) ;

Permitir SELECT permissões para a fn_securitypredicate função:

GRANT SELECT ON Security.fn_securitypredicate TO Manager;
GRANT SELECT ON Security.fn_securitypredicate TO Sales1;
GRANT SELECT ON Security.fn_securitypredicate TO Sales2;

Agora teste o predicado de filtragem, selecionando na tabela Sample.Sales como se fosse cada usuário.

EXECUTE AS USER = 'Sales1';
SELECT * FROM Sample.Sales;
-- This will return just the rows for Product 'Valve' (as specified for 'Sales1' in the Lk_Salesman_Product table above)
REVERT;

EXECUTE AS USER = 'Sales2';
SELECT * FROM Sample.Sales;
-- This will return just the rows for Product 'Wheel' (as specified for 'Sales2' in the Lk_Salesman_Product table above)
REVERT;

EXECUTE AS USER = 'Manager';
SELECT * FROM Sample.Sales;
-- This will return all rows with no restrictions
REVERT;

O Manager deve ver todas as seis linhas. Os Sales1 e Sales2 utilizadores só devem ver as suas próprias vendas.

Altere a política de segurança para desativá-la.

ALTER SECURITY POLICY SalesFilter
WITH (STATE = OFF);

Agora Sales1 e Sales2 os usuários podem ver todas as seis linhas.

Conecte-se ao banco de dados SQL para limpar recursos deste exercício de exemplo:

DROP USER Sales1;
DROP USER Sales2;
DROP USER Manager;

DROP SECURITY POLICY SalesFilter;
DROP FUNCTION Security.fn_securitypredicate;
DROP TABLE Sample.Sales;
DROP TABLE Sample.Lk_Salesman_Product;
DROP SCHEMA Security;
DROP SCHEMA Sample;

E. Cenário de segurança em nível de linha no Microsoft Fabric

Podemos demonstrar segurança em nível de linha no Warehouse e o endpoint de análise SQL no Microsoft Fabric.

O exemplo seguinte cria tabelas de amostra que funcionarão com o Warehouse no Microsoft Fabric, mas, no ponto de extremidade de análise SQL, utiliza tabelas existentes. No ponto de extremidade de análise SQL, você não pode usar CREATE TABLE, mas pode usar CREATE SCHEMA, CREATE FUNCTIONe CREATE SECURITY POLICY.

Neste exemplo, primeiro crie um esquema sales, uma tabela sales.Orders.

CREATE SCHEMA sales;
GO

-- Create a table to store sales data
CREATE TABLE sales.Orders (
    SaleID INT,
    SalesRep VARCHAR(100),
    ProductName VARCHAR(50),
    SaleAmount DECIMAL(10, 2),
    SaleDate DATE
);

-- Insert sample data
INSERT INTO sales.Orders (SaleID, SalesRep, ProductName, SaleAmount, SaleDate)
VALUES
    (1, 'Sales1@contoso.com', 'Smartphone', 500.00, '2023-08-01'),
    (2, 'Sales2@contoso.com', 'Laptop', 1000.00, '2023-08-02'),
    (3, 'Sales1@contoso.com', 'Headphones', 120.00, '2023-08-03'),
    (4, 'Sales2@contoso.com', 'Tablet', 800.00, '2023-08-04'),
    (5, 'Sales1@contoso.com', 'Smartwatch', 300.00, '2023-08-05'),
    (6, 'Sales2@contoso.com', 'Gaming Console', 400.00, '2023-08-06'),
    (7, 'Sales1@contoso.com', 'TV', 700.00, '2023-08-07'),
    (8, 'Sales2@contoso.com', 'Wireless Earbuds', 150.00, '2023-08-08'),
    (9, 'Sales1@contoso.com', 'Fitness Tracker', 80.00, '2023-08-09'),
    (10, 'Sales2@contoso.com', 'Camera', 600.00, '2023-08-10');

Crie um Security esquema, uma função Security.tvf_securitypredicatee uma política SalesFilterde segurança.

-- Creating schema for Security
CREATE SCHEMA Security;
GO

-- Creating a function for the SalesRep evaluation
CREATE FUNCTION Security.tvf_securitypredicate(@SalesRep AS nvarchar(50))
    RETURNS TABLE
WITH SCHEMABINDING
AS
    RETURN SELECT 1 AS tvf_securitypredicate_result
WHERE @SalesRep = USER_NAME() OR USER_NAME() = 'manager@contoso.com';
GO

-- Using the function to create a Security Policy
CREATE SECURITY POLICY SalesFilter
ADD FILTER PREDICATE Security.tvf_securitypredicate(SalesRep)
ON sales.Orders
WITH (STATE = ON);
GO

Depois de aplicar a política de segurança e criar a função, os usuários Sales1@contoso.com e Sales2@contoso.com só será capaz de ver seus próprios dados na sales.Orders tabela, onde a coluna SalesRep é igual ao seu próprio nome de usuário retornado pela função interna USER_NAME. O utilizador manager@contoso.com do Fabric pode ver todos os dados na tabela sales.Orders.

Conteúdo relacionado

  • Last updated on