Segurança em nível de linha

Aplica-se a: SQL Server Banco de Dados SQL do Azure Instância Gerenciada de SQL do Azure Azure Synapse Analytics Ponto de extremidade de análises SQL no Microsoft Fabric Warehouse no Microsoft Fabric

A Segurança em Nível de Linha (RLS) permite que você use o contexto de execução ou associação de grupo 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 de segurança em seu aplicativo. O RLS ajuda a implementar restrições de acesso a linhas de dados. Por exemplo, você pode garantir que os funcionários acessem somente as linhas de dados que são relevantes aos seus departamentos. Outro exemplo é restringir o acesso a dados dos clientes apenas aos dados relevantes para sua empresa.

A lógica de restrição de acesso é localizado 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 toda vez que há tentativa de acesso a dados a partir de qualquer camada. Isso torna o sistema de segurança mais robusto e confiável, reduzindo a área de superfície do sistema de segurança.

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

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

Descrição

A RLS (Segurança em Nível de Linha) permite dois tipos de predicado de segurança:

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

• Os predicados de bloqueio bloqueiam explicitamente as operações de gravação (AFTER INSERT, AFTER UPDATE, BEFORE UPDATE, BEFORE DELETE) que violam o predicado.

O acesso aos dados no nível de linha em uma tabela é restrito por um predicado de segurança definido como uma função com valor de tabela embutida. A função é então invocada e imposta por uma política de segurança. Para predicados de filtro, não há nenhuma indicação ao aplicativo de que as linhas tenham sido filtradas no conjunto de resultados. Se todas as linhas forem filtradas, um conjunto de null será retornado. Para predicados de bloqueio, todas as operações que violem o predicado falharão com um erro.

Predicados de filtro são aplicados durante a leitura de dados da tabela base. Elas afetam todas as operações de obtenção: SELECT, DELETEe UPDATE. Os usuários não podem selecionar ou excluir linhas que são filtradas. Os usuários não podem atualizar as linhas que são filtradas. Mas, é possível atualizar linhas de forma que elas sejam filtradas posteriormente. Os predicados de bloqueio afetam todas as operações de gravação.

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

• Os predicados BEFORE UPDATE podem impedir que os usuários atualizem linhas que atualmente violem o predicado.

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

Os predicados de filtro e bloqueio, bem como as políticas de segurança têm o seguinte comportamento:

• Você poderia definir uma função de predicado que 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 junção ou função será acessível pela consulta e funcionará como o esperado, sem nenhuma verificação de permissão extra. Se a política de segurança for criada com SCHEMABINDING = OFF, os usuários precisarão ter permissões SELECT 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 permissão EXECUTE também será necessária.

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

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

• As tentativas de alterar o esquema de uma tabela associada por uma política de segurança associada ao esquema resultam em um erro. No entanto, as colunas não referenciadas pelo predicado podem ser alteradas.

• Tentar adicionar um predicado em uma tabela que já tenha um definido para a operação especificada resultará em um erro. Isso ocorrerá independentemente de o predicado estar habilitado ou não.

• As tentativas de modificar uma função que é usada como um predicado em uma tabela em uma política de segurança associada ao esquema resultarão em um erro.

• Definir múltiplas políticas de segurança ativas que contêm predicados não sobrepostos é uma ação que terá êxito.

Os predicados de filtro apresentam o seguinte comportamento:

• Defina uma política de segurança que filtra as linhas de uma tabela. O aplicativo não está ciente de todas as linhas filtradas para operações SELECT, UPDATE e DELETE. Incluindo situações em que todas as linhas são filtradas. O aplicativo pode INSERT linhas, mesmo que elas sejam filtradas durante qualquer outra operação.

Os predicados de bloqueio apresentam o seguinte comportamento:

• Os predicados de bloqueio para UPDATE são divididos em operações distintas para BEFORE e AFTER. Não é possível, por exemplo, impedir os usuários de atualizar uma linha para ter um valor maior que o atual. Se esse tipo de lógica for necessário, você deverá usar gatilhos com as tabelas intermediárias DELETED e INSERTED para fazer referência a valores novos e antigos juntos.

• O otimizador não verificará um predicado de bloqueio AFTER UPDATE se as colunas usadas pela função de predicado não foram alteradas. Por exemplo: Alice não deve conseguir alterar um salário para ser maior que 100.000. Alice pode alterar o endereço de um funcionário cujo salário já seja 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 de bloqueio AFTER INSERT serão aplicados às operações de inserção em massa, assim como seriam em operações de inserção regular.

Casos de uso

Estes são exemplos de design de como a RLS (Segurança em Nível de Linha) pode ser usada:

• Um hospital pode criar uma diretiva de segurança que permite a enfermeiras exibir linhas de dados somente para seus pacientes.

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

• Um aplicativo multilocatário pode criar uma política para impor uma separação lógica entre as linhas de dados de cada locatário e as linhas referentes a todos os outros locatários. Eficiência é obtida pelo armazenamento de dados para vários locatários em uma única tabela. Cada locatário pode ver somente as linhas com seus próprios dados.

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

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

Permissões

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

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

• Permissões SELECT e REFERENCES na função que está sendo usada como um predicado.

• Permissão REFERENCES na tabela de destino associada à política.

• Permissão REFERENCES em cada coluna da tabela de destino usada como argumentos.

Diretivas de segurança se aplicam a todos os usuários, incluindo usuários de dbo do banco de dados. Os usuários do dbo podem alterar ou descartar as políticas de segurança, mas suas alterações às políticas de segurança podem ser auditadas. Se os usuários com altos privilégios, como sysadmin ou db_owner precisarem ver todas as linhas para solucionar problemas ou validar dados, a política de segurança deverá ser escrita para permitir isso.

Se uma política de segurança for criada com SCHEMABINDING = OFF, para consultar a tabela de destino, os usuários deverão ter a permissão SELECT 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 política 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.

Práticas recomendadas

• É 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 que são exigidas por esses objetos especiais das tabelas de destino. Uma separação adicional de políticas e funções de predicado diferentes pode ser necessária em bancos de dados multilocatário, mas não como padrão para todos os casos.

• A permissão ALTER ANY SECURITY POLICY é destinada a usuários altamente privilegiados (como gerentes de políticas de segurança). O gerenciador de políticas de segurança não exige a permissão SELECT nas tabelas que protege.

• Evite conversões de tipo em funções de predicado para evitar possíveis erros de runtime.

• Evite a recursão no predicado funções sempre que possível para evitar a degradação do desempenho. O otimizador de consulta tentará detectar as recursões diretas, mas não é garantido que encontrará as recursões indiretas. A recursão indireta é onde uma segunda função chama a função de predicado.

• Evite usar junções de tabelas em excesso em funções de predicado, para maximizar o desempenho.

Evite a lógica de predicado que depende de opções SET específicas da sessão: embora seja improvável que elas sejam usadas em aplicações práticas, as funções de predicado cuja lógica depende de determinadas opções SET específicas da sessão poderão perder informações se os usuários conseguirem executar consultas arbitrárias. Por exemplo, uma função de predicado que implicitamente converte uma cadeia de caracteres em datetime poderia filtrar linhas diferentes com base na opção SET DATEFORMAT da sessão atual. Em geral, as funções de predicado devem obedecer as regras a seguir:

• As funções de predicado não devem converter implicitamente cadeias de caracteres em date, smalldatetime, datetime, datetime2 ou datetimeoffset, ou vice-versa, pois essas conversões são afetadas pelas opções SET DATEFORMAT (Transact-SQL) e SET LANGUAGE (Transact-SQL). Em vez disso, use a função CONVERT e especifique explicitamente o parâmetro de estilo.

• As funções de predicado não devem se basear no valor do primeiro dia da semana, pois esse valor é afetado pela opção SET DATEFIRST (Transact-SQL).

• As funções de predicado não devem se basear em expressões aritméticas ou de agregação que retornarão NULL se houver erro (como um estouro ou divisão por zero) porque esse comportamento é afetado pelas opções SET ANSI_WARNINGS (Transact-SQL), SET NUMERIC_ROUNDABORT (Transact-SQL) e SET ARITHABORT (Transact-SQL).

• As funções de predicado não devem comparar cadeias de caracteres concatenadas com NULL, pois esse comportamento é afetado pela opção SET CONCAT_NULL_YIELDS_NULL (Transact-SQL).

Observação de segurança: ataques de temporização

Gerente de política de segurança mal-intencionado

É importante observar que um gerente de política de segurança mal-intencionado, com permissões suficientes para criar uma política de segurança na parte superior de uma coluna confidencial, tendo permissão para criar ou alterar funções embutidas com valor de tabela, poderá conspirar com outro usuário que tenha permissões SELECT em uma tabela para realizar a exportação de dados, pela criação mal-intencionada de funções embutidas com valor de tabela, projetadas para usar ataques de temporização para inferir dados. Esses ataques exigiriam a colusão (ou excesso de permissões concedidas a um usuário mal-intencionado) e provavelmente demandariam várias iterações de modificação da política (exigindo permissão para remover o predicado, para que pudessem então quebrar a associação do esquema), modificando as funções com valor de tabela embutida e repetidamente executando instruções de seleção na tabela de destino. É recomendável limitar as permissões conforme necessário e o monitor para qualquer atividade suspeita. As atividade como políticas em constante mudança e as 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'; deixaria um usuário mal-intencionado saber que o salário de John Doe é exatamente US$ 100.000. Mesmo que 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 recursos

De modo geral, a segurança no nível de linha funcionará conforme o esperado entre os recursos. No entanto, existem algumas exceções. Esta seção documenta várias observações e limitações para o uso da segurança em nível de linha com determinados recursos do SQL Server.

• DBCC SHOW_STATISTICS relata estatísticas de dados não filtrados e pode perder informações que, de outra forma, estariam 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 política de segurança de nível de linha é restrito. O usuário deve ser proprietário da tabela ou ser um membro da função de servidor fixa sysadmin, da função de banco de dados fixa db_owner ou da função de banco de dados fixa db_ddladmin.

• Filestream: A RLS é incompatível com Filestream.

• PolyBase: a RLS é compatível com tabelas externas no Azure Synapse e no SQL Server 2019 CU7 ou posterior.

• Tabelas com Otimização de Memória: A função com valor de tabela embutida usada como um predicado de segurança em uma tabela com otimização de memória deve ser definida usando a opção WITH NATIVE_COMPILATION. Com essa opção, os recursos de linguagem não permitidos pelas tabelas com otimização de memória serão banidos e o erro apropriado será emitido no momento da criação. Para obter mais informações, confira Segurança em Nível de Linha nas tabelas com otimização de memória.

• Exibições indexadas: De modo geral, as políticas de segurança podem ser criadas sobre as exibições, e as exibições podem ser criadas sobre as tabelas que são associadas pelas políticas de segurança. No entanto, as exibições indexadas não podem ser criadas sobre as tabelas que têm uma política de segurança, pois as pesquisas de linha pelo índice contornariam a política.

• Captura de Dados de Alterações: a CDA (Captura de Dados de Alterações) pode perder linhas inteiras que devem ser filtradas para membros de db_owner ou para usuários que são membros da função de “portão” especificada quando a CDA é habilitada para uma tabela. Você pode configurar explicitamente essa função como NULL para permitir que todos os usuários acessem os dados de alteração. Na verdade, db_owner e os membros dessa função de portão poderão ver todas as alterações nos dados de uma tabela, mesmo se houver uma política de segurança na tabela.

• Controle de Alterações: o Controle de Alterações pode deixar vazar a chave primária de linhas que deve ser filtrada para usuários com as permissões SELECT e VIEW CHANGE TRACKING. Os valores de dados reais não vazam; apenas o fato de que a coluna A foi atualizada/inserida/excluída para a linha com uma determinada chave primária. Isso será um problema se a chave primária contiver um elemento confidencial, como um Número de Seguro Social. No entanto, na prática, esse CHANGETABLE é quase sempre unido à tabela original para obtenção de dados mais recentes.

• Pesquisa de Texto Completo: um impacto no desempenho é esperado em 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 a Segurança em Nível de Linha e evitar a perda das chaves primárias de linhas que devem ser filtradas: CONTAINSTABLE, FREETEXTTABLE, semantickeyphrasetable, semanticsimilaritydetailstable, semanticsimilaritytable.

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

• Exibições Particionadas: Os predicados de bloqueio não podem ser definidos em exibições particionadas, e as exibições particionadas não podem ser criadas sobre as tabelas que usam predicados de bloqueio. Os predicados de filtro são compatíveis com exibições particionadas.

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

Outras limitações:

• Somente predicados de filtro de suporte do Microsoft Fabric e do Azure Synapse Analytics. Atualmente, predicados de bloqueio não são compatíveis no Microsoft Fabric e no Azure Synapse Analytics.

Exemplos

R. 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 embutida e uma política de segurança para a tabela. O exemplo, em seguida, mostra como as instruções select são filtrados para os diversos usuários.

Crie três contas de usuário que demonstrem os 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 à tabela para cada usuário.

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 embutida. A função retorna 1 quando uma linha da coluna SalesRep é o mesmo que o usuário que executa a consulta (@SalesRep = USER_NAME()) ou se o usuário executando a consulta for o gerente (USER_NAME() = 'Manager'). Este exemplo de uma função com valor de tabela definida pelo usuário é útil para servir como um filtro para a política 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. O STATE deve ser definido como ON para habilitar a política.

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

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

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-o a partir da tabela Sales.Orders como 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 usuários Sales1 e Sales2 deverão ver apenas as próprias vendas.

Altere a política de segurança para desabilitar a política específica.

ALTER SECURITY POLICY SalesFilter
WITH (STATE = OFF);

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

Conectar-se ao banco de dados SQL para limpar recursos desse exercício de amostra:

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 de uso de Segurança em Nível de Linha em uma tabela externa do Azure Synapse

Esse 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 embutida e uma política de segurança para a tabela externa. O exemplo mostra como as instruções select são filtrados para os diversos usuários.

Pré-requisitos

1. Você deve ter um pool de SQL dedicado. Confira Criar um pool de SQL dedicado
2. O servidor que hospeda o pool de SQL dedicado precisa ser registrado no Microsoft Entra ID (antigo Azure Active Directory), e você precisa ter uma conta de armazenamento do Azure com permissões de Storage Blog Data Contributor. Use as etapas Usar pontos de extremidade de serviço da rede virtual e regras para os servidores no Banco de Dados SQL do Azure.
3. Crie um sistema de arquivos para a sua conta do Armazenamento do Azure. Use o Gerenciador de Armazenamento do Azure para ver sua conta de armazenamento. Clique com o botão direito do mouse em contêineres e selecione Criar sistema de arquivos.

Depois de preparar os pré-requisitos, crie três contas de usuário que demonstrarão 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 na tabela Sales 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 para os três usuários na tabela externa Sales_ext criada.

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

Crie um esquema e uma função com valor de tabela embutida; talvez você já tenha concluído isso no exemplo A. A função retorna 1 quando uma linha da coluna SalesRep é igual ao usuário que executa a consulta (@SalesRep = USER_NAME()) ou se o usuário que executa a consulta for o usuário Manager (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 com valor de tabela embutida como um predicado de filtro. O STATE deve ser definido como ON para habilitar a política.

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 tabela externa Sales_ext. Entre como cada usuário, Sales1, Sales2 e Manager. Execute o comando a seguir como cada usuário.

SELECT * FROM Sales_ext;

O Manager deve ver todas as seis linhas. Os usuários Sales1 e Sales2 deverão ver apenas as próprias vendas.

Altere a política de segurança para desabilitar a política específica.

ALTER SECURITY POLICY SalesFilter_ext
WITH (STATE = OFF);

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

Conectar-se ao banco de dados do Azure Synapse para limpar recursos desse exercício de amostra:

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;

Conectar-se ao banco de dados do servidor lógico master 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

Este exemplo mostra como um aplicativo de camada intermediária pode implementar a filtragem de conexão, onde os usuários do aplicativo (ou locatários) compartilham o mesmo usuário do SQL Server (o aplicativo). O aplicativo define a ID do 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 modo transparente as linhas que não devem ficar visíveis para essa ID, além de impedir o usuário de inserir linhas para a ID de usuário incorreta. Não é necessária nenhuma outra alteração de aplicativo.

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 pedidos 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 de privilégio baixo 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 a ID de usuário do aplicativo armazenada em SESSION_CONTEXT() para filtrar as 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 bloqueio em Sales. O predicado de bloqueio precisa apenas de AFTER INSERT, porque BEFORE UPDATE e BEFORE DELETE já foram filtrados, e AFTER UPDATE é desnecessário, pois a coluna AppUserId 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 você pode simular a filtragem de conexão selecionando na tabela Sales após a definição de IDs de usuário diferentes em SESSION_CONTEXT(). Na prática, o aplicativo é responsável por definir a ID do usuário atual em SESSION_CONTEXT() depois de abrir uma conexão. Definir o parâmetro @read_only como 1 impede 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 de 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, além de criar e preencher uma tabela de fatos, Sample.Sales, com seis linhas e uma tabela de pesquisa com duas linhas. Em seguida, cria uma função com valor de tabela embutida que une a tabela de fatos à pesquisa a fim de obter o identificador de usuário e uma política de segurança para a tabela. O exemplo, em seguida, mostra como as instruções select são filtrados para os diversos usuários.

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

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

Crie um esquema Sample e uma tabela de fatos, Sample.Sales, para manter os 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 manter 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 amostra, 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 à tabela de fatos para cada usuário.

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 embutida. A função retorna 1 quando um usuário consulta a tabela de fatos Sample.Sales, e a coluna SalesRep da tabela Lk_Salesman_Product é igual ao usuário que está executando a consulta (@SalesRep = USER_NAME()) quando ligada à tabela de fatos na coluna Product ou caso o usuário que executa a consulta seja o usuário Manager (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. O STATE deve ser definido como ON para habilitar a política.

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

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

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-o a partir da tabela Sample.Sales como 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 usuários Sales1 e Sales2 deverão ver apenas as próprias vendas.

Altere a política de segurança para desabilitar a política específica.

ALTER SECURITY POLICY SalesFilter
WITH (STATE = OFF);

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

Conectar-se ao banco de dados SQL para limpar recursos desse exercício de amostra:

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 o Warehouse de Segurança em Nível de Linha e o ponto de extremidade de análises SQL no Microsoft Fabric.

O exemplo a seguir cria tabelas de exemplo que funcionarão com o Warehouse no Microsoft Fabric, mas no ponto de extremidade de análises SQL usam tabelas existentes. No ponto de extremidade de análises SQL, você não pode usar CREATE TABLE, mas pode usar CREATE SCHEMA, CREATE FUNCTION e 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');

Criar um esquema Security, uma função Security.tvf_securitypredicate e uma política de segurança SalesFilter.

-- 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ó poderão ver os próprios dados na tabela sales.Orders, na qual a coluna SalesRep é igual ao próprio nome de usuário retornado pela função interna USER_NAME. O usuário manager@contoso.com do Fabric pode ver todos os dados na tabela sales.Orders.

Conteúdo relacionado

• CREATE SECURITY POLICY (Transact-SQL)
• ALTER SECURITY POLICY (Transact-SQL)
• DROP SECURITY POLICY (Transact-SQL)
• CREATE FUNCTION (Transact-SQL)
• SESSION_CONTEXT (Transact-SQL)
• sp_set_session_context (Transact-SQL)
• sys.security_policies (Transact-SQL)
• sys.security_predicates (Transact-SQL)
• Criar funções definidas pelo usuário (mecanismo de banco de dados)
• Permissões de objetos GRANT (Transact-SQL)