Exercício – Gerenciar a autorização por meio da segurança em nível de linha e coluna

  • 9 minutos

Neste exercício, são mostrados exemplos de como você pode gerenciar a autorização por meio da segurança em nível de linha e coluna.

Um exemplo de segurança em nível de coluna

O exemplo a seguir mostra como restringir TestUser de acessar a coluna SSN da tabela Membership:

Crie a tabela Membership com a coluna SSN usada para armazenar números de seguro social:

CREATE TABLE Membership
  (MemberID int IDENTITY,
   FirstName varchar(100) NULL,
   SSN char(9) NOT NULL,
   LastName varchar(100) NOT NULL,
   Phone varchar(12) NULL,
   Email varchar(100) NULL);

Permita que TestUser acesse todas as colunas, exceto a coluna SSN, que tem os dados confidenciais:

GRANT SELECT ON Membership(MemberID, FirstName, LastName, Phone, Email) TO TestUser;

As consultas executadas como TestUser falharão se elas incluírem a coluna SSN:

SELECT * FROM Membership;

-- Msg 230, Level 14, State 1, Line 12
-- The SELECT permission was denied on the column 'SSN' of the object 'Membership', database 'CLS_TestDW', schema 'dbo'.

Um exemplo de segurança em nível de linha

Esse cenário fornece um exemplo 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

  • É necessário ter um pool de SQL. Confira Criar um pool de SQL do Synapse
  • O servidor que hospeda o pool de SQL precisa ser registrado no AAD, e você precisa ter uma conta de armazenamento do Azure com permissões de Colaborador do Blog de Armazenamento. Siga as etapas descritas aqui.
  • Crie um sistema de arquivos para a sua conta do Armazenamento do Azure. Use o Gerenciador de Armazenamento 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 funcionalidades 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 master and your 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 de Vendas recém-criada.

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 é o usuário Gerente (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 estado 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. Faça login como cada usuário: Vendas1, Vendas2 e Gerente. Execute o comando a seguir como cada usuário.

SELECT * FROM Sales_ext;

O gerente deve ver todas as seis linhas. Os usuários Vendas1 e Vendas2 deverão ver apenas suas 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 Vendas1 e Vendas2 podem ver todas as seis linhas.

Conectar-se ao banco de dados do Azure Synapse para limpar recursos

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 com o mestre lógico para limpar os recursos.

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