Tutorial: Cadeias de propriedade e comutação de contexto

Este tutorial usa um cenário para ilustrar os conceitos de segurança do SQL Server envolvendo cadeias de propriedade e alternância de contexto do usuário.

Cenário

Nesse cenário, dois usuários precisam de contas para acessar os dados do pedido de compra armazenados no banco de dados AdventureWorks2012 . Os requisitos são os seguintes:

• A primeira conta (TestManagerUser) deve ser capaz de ver todos os detalhes em cada ordem de compra.

• A segunda conta (TestEmployeeUser) deve ser capaz de ver o número do pedido de compra, a data do pedido, a data de envio, os números de ID do produto e os itens ordenados e recebidos por ordem de compra, pelo número da ordem de compra, para itens em que as remessas parciais foram recebidas.

• Todas as outras contas devem manter suas permissões atuais.

Para atender aos requisitos desse cenário, o exemplo é dividido em quatro partes que demonstram os conceitos de cadeias de propriedade e alternância de contexto:

1. Configurando o ambiente.

2. Criando um procedimento armazenado para acessar dados por ordem de compra.

3. Acessando os dados por meio do procedimento armazenado.

4. Redefinindo o ambiente.

Cada bloco de código neste exemplo é explicado na linha. Para copiar o exemplo completo, consulte Exemplo Completo no final deste tutorial.

1. Configurar o ambiente

Use SQL Server Management Studio e o código a seguir para abrir o banco de dados AdventureWorks2012 e use a instrução CURRENT_USER Transact-SQL para verificar se o usuário dbo é exibido como o contexto.

USE AdventureWorks2012;  
GO  
SELECT CURRENT_USER AS 'Current User Name';  
GO  

Para obter mais informações sobre a instrução CURRENT_USER, consulte CURRENT_USER (Transact-SQL).

Use esse código como o usuário dbo para criar dois usuários no servidor e no banco de dados AdventureWorks2012 .

CREATE LOGIN TestManagerUser   
    WITH PASSWORD = '340$Uuxwp7Mcxo7Khx';  
GO  
CREATE USER TestManagerUser   
   FOR LOGIN TestManagerUser  
   WITH DEFAULT_SCHEMA = Purchasing;  
GO   
  
CREATE LOGIN TestEmployeeUser  
    WITH PASSWORD = '340$Uuxwp7Mcxo7Khy';  
GO  
CREATE USER TestEmployeeUser   
   FOR LOGIN TestEmployeeUser;  
GO   

Para obter mais informações sobre a instrução CREATE USER, consulte CREATE USER (Transact-SQL). Para obter mais informações sobre a instrução CREATE LOGIN, consulte CREATE LOGIN (Transact-SQL).

Use o código a seguir para alterar a propriedade do Purchasing esquema para a TestManagerUser conta. Isso permite que essa conta tenha acesso total para usar as instruções de linguagem de manipulação de dados (DML), como as permissões SELECT e INSERT, nos objetos que ela contém. TestManagerUser também é concedida a capacidade de criar procedimentos armazenados.

/* Change owner of the Purchasing Schema to TestManagerUser */  
ALTER AUTHORIZATION   
   ON SCHEMA::Purchasing   
   TO TestManagerUser;  
GO  
  
GRANT CREATE PROCEDURE   
   TO TestManagerUser   
   WITH GRANT OPTION;  
GO  

Para obter mais informações sobre a documentação do GRANT, consulte GRANT (Transact-SQL). Para obter mais informações sobre procedimentos armazenados, veja Procedimentos armazenados (Mecanismo de Banco de Dados). Para obter um pôster de todas as permissões do Mecanismo de Banco de Dados, consulte https://github.com/microsoft/sql-server-samples/blob/master/samples/features/security/permissions-posters/Microsoft_SQL_Server_2017_and_Azure_SQL_Database_permissions_infographic.pdf.

2. Criar um procedimento armazenado para acessar dados

Para alternar o contexto em um banco de dados, use a instrução EXECUTE AS. EXECUTE AS requer permissões IMPERSONATE.

Use a instrução EXECUTE AS no código a seguir para mudar o contexto para TestManagerUser e criar um procedimento armazenado que mostra apenas os dados necessários por TestEmployeeUser. Para atender aos requisitos, o procedimento armazenado aceita uma variável para o número do pedido de compra e não exibe informações financeiras, e a cláusula WHERE limita os resultados a remessas parciais.

EXECUTE AS LOGIN = 'TestManagerUser'  
GO  
SELECT CURRENT_USER AS 'Current User Name';  
GO  
  
/* Note: The user that calls the EXECUTE AS statement must have IMPERSONATE permissions on the target principal */  
CREATE PROCEDURE usp_ShowWaitingItems @ProductID int  
AS  
BEGIN   
   SELECT a.PurchaseOrderID, a.OrderDate, a.ShipDate  
      , b.ProductID, b.OrderQty, b.ReceivedQty  
   FROM Purchasing.PurchaseOrderHeader a  
      INNER JOIN Purchasing.PurchaseOrderDetail b  
         ON a.PurchaseOrderID = b.PurchaseOrderID  
   WHERE b.OrderQty > b.ReceivedQty  
      AND @ProductID = b.ProductID  
   ORDER BY b.ProductID ASC  
END  
GO  

Atualmente TestEmployeeUser , não tem acesso a nenhum objeto de banco de dados. O seguinte código (ainda no contexto TestManagerUser) concede à conta de usuário a capacidade de consultar informações de tabela base por meio do procedimento armazenado.

GRANT EXECUTE  
   ON OBJECT::Purchasing.usp_ShowWaitingItems  
   TO TestEmployeeUser;  
GO  

O procedimento armazenado faz parte do Purchasing esquema, embora nenhum esquema tenha sido especificado explicitamente, porque TestManagerUser é atribuído por padrão ao Purchasing esquema. Você pode usar informações do catálogo do sistema para localizar objetos, conforme mostrado no código a seguir.

SELECT a.name AS 'Schema'  
   , b.name AS 'Object Name'  
   , b.type AS 'Object Type'  
FROM sys.schemas a  
   INNER JOIN sys.objects b  
      ON a.schema_id = b.schema_id   
WHERE b.name = 'usp_ShowWaitingItems';  
GO  

Com esta seção do exemplo concluída, o código alterna o contexto de volta para dbo usando a instrução REVERT.

REVERT;  
GO  

Para obter mais informações sobre a instrução REVERT, consulte REVERT (Transact-SQL).

3. Acessar dados por meio do procedimento armazenado

TestEmployeeUser não tem nenhuma permissão nos objetos do banco de dados AdventureWorks2012 além de um login e os direitos atribuídos à função pública de banco de dados. O código a seguir retorna um erro quando TestEmployeeUser tentar acessar tabelas base.

EXECUTE AS LOGIN = 'TestEmployeeUser'  
GO  
SELECT CURRENT_USER AS 'Current User Name';  
GO  
/* This won't work */  
SELECT *  
FROM Purchasing.PurchaseOrderHeader;  
GO  
SELECT *  
FROM Purchasing.PurchaseOrderDetail;  
GO  

Como os objetos referenciados pelo procedimento armazenado criado na última seção pertencem a TestManagerUser em virtude da propriedade do esquema Purchasing, TestEmployeeUser pode acessar as tabelas base por meio do procedimento armazenado. O código a seguir, ainda usando o TestEmployeeUser contexto, passa a ordem de compra 952 como um parâmetro.

EXEC Purchasing.usp_ShowWaitingItems 952  
GO  

4. Redefinir o ambiente

O código a seguir usa o comando REVERT para retornar o contexto da conta atual para dbo, e em seguida redefine o ambiente.

REVERT;  
GO  
ALTER AUTHORIZATION   
ON SCHEMA::Purchasing TO dbo;  
GO  
DROP PROCEDURE Purchasing.usp_ShowWaitingItems;  
GO  
DROP USER TestEmployeeUser;  
GO  
DROP USER TestManagerUser;  
GO  
DROP LOGIN TestEmployeeUser;  
GO  
DROP LOGIN TestManagerUser;  
GO  

Exemplo completo

Esta seção exibe o código de exemplo completo.

/*   
Script:       UserContextTutorial.sql  
Author:       Microsoft  
Last Updated: Books Online  
Conditions:   Execute as DBO or sysadmin in the AdventureWorks database  
Section 1:    Configure the Environment   
*/  
USE AdventureWorks2012;  
GO  
SELECT CURRENT_USER AS 'Current User Name';  
GO  
/* Create server and database users */  
CREATE LOGIN TestManagerUser   
    WITH PASSWORD = '340$Uuxwp7Mcxo7Khx';  
  
GO  
  
CREATE USER TestManagerUser   
   FOR LOGIN TestManagerUser  
   WITH DEFAULT_SCHEMA = Purchasing;  
GO   
  
CREATE LOGIN TestEmployeeUser  
    WITH PASSWORD = '340$Uuxwp7Mcxo7Khy';  
GO  
CREATE USER TestEmployeeUser   
   FOR LOGIN TestEmployeeUser;  
GO   
  
/* Change owner of the Purchasing Schema to TestManagerUser */  
ALTER AUTHORIZATION   
   ON SCHEMA::Purchasing   
   TO TestManagerUser;  
GO  
  
GRANT CREATE PROCEDURE   
   TO TestManagerUser   
   WITH GRANT OPTION;  
GO  
  
/*   
Section 2: Switch Context and Create Objects  
*/  
EXECUTE AS LOGIN = 'TestManagerUser';  
GO  
SELECT CURRENT_USER AS 'Current User Name';  
GO  
  
/* Note: The user that calls the EXECUTE AS statement must have IMPERSONATE permissions on the target principal */  
CREATE PROCEDURE usp_ShowWaitingItems @ProductID int  
AS  
BEGIN   
   SELECT a.PurchaseOrderID, a.OrderDate, a.ShipDate  
      , b.ProductID, b.OrderQty, b.ReceivedQty  
   FROM Purchasing.PurchaseOrderHeader AS a  
      INNER JOIN Purchasing.PurchaseOrderDetail AS b  
         ON a.PurchaseOrderID = b.PurchaseOrderID  
   WHERE b.OrderQty > b.ReceivedQty  
      AND @ProductID = b.ProductID  
   ORDER BY b.ProductID ASC  
END;  
GO  
  
/* Give the employee the ability to run the procedure */  
GRANT EXECUTE   
   ON OBJECT::Purchasing.usp_ShowWaitingItems  
   TO TestEmployeeUser;  
GO   
  
/* Notice that the stored procedure is located in the Purchasing   
schema. This also demonstrates system catalogs */  
SELECT a.name AS 'Schema'  
   , b.name AS 'Object Name'  
   , b.type AS 'Object Type'  
FROM sys.schemas AS a  
   INNER JOIN sys.objects AS b  
      ON a.schema_id = b.schema_id   
WHERE b.name = 'usp_ShowWaitingItems';  
GO  
  
/* Go back to being the dbo user */  
REVERT;  
GO  
  
/*  
Section 3: Switch Context and Observe Security   
*/  
EXECUTE AS LOGIN = 'TestEmployeeUser';  
GO  
SELECT CURRENT_USER AS 'Current User Name';  
GO  
EXEC Purchasing.usp_ShowWaitingItems 952;  
GO  
  
/*   
Section 4: Clean Up Example  
*/  
REVERT;  
GO  
ALTER AUTHORIZATION   
ON SCHEMA::Purchasing TO dbo;  
GO  
DROP PROCEDURE Purchasing.usp_ShowWaitingItems;  
GO  
DROP USER TestEmployeeUser;  
GO  
DROP USER TestManagerUser;  
GO  
DROP LOGIN TestEmployeeUser;  
GO  
DROP LOGIN TestManagerUser;  
GO  

Consulte Também

Central de Segurança do Mecanismo de Banco de Dados do SQL Server e Banco de Dados Azure SQL