Tutorial: Ownership Chains and Context Switching

Aplica-se a: SQL Server Instância Gerenciada de SQL do Azure

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

Cenário

Neste cenário, dois usuários precisam de contas para acessar dados de ordem de compra armazenados no banco de dados AdventureWorks2022 . Os requisitos são os seguintes:

• A primeira conta (TestManagerUser) deve estar habilitada para que você veja todos os detalhes em cada ordem de compra.
• A segunda conta (TestEmployeeUser) deve estar habilitada para ver o número da ordem de compra, a data do pedido, a data de envio, os números de ID do produto e os itens solicitados e recebidos por ordem de compra, por número de ordem de compra, para itens cujos envios parciais foram recebidos.
• Todas as outras contas devem reter suas permissões atuais.
  Para atender aos requisitos deste cenário, o exemplo é dividido em quatro partes que demonstram os conceitos de cadeia 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 pelo procedimento armazenado.
4. Redefinindo o ambiente.

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

Pré-requisitos

Para concluir este tutorial, você precisará do SQL Server Management Studio, bem como acesso a um servidor que executa o SQL Server e um banco de dados do AdventureWorks2022.

• Instalar o SQL Server Management Studio.
• Instalar o SQL Server 2017 Developer Edition.
• Baixar Bancos de dados de exemplo do AdventureWorks.

Para obter instruções sobre como restaurar um banco de dados no SQL Server Management Studio, veja Restaurar um banco de dados.

1. Configure o ambiente

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

USE AdventureWorks2022;  
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 AdventureWorks2022.

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 esquema Purchasing para a conta TestManagerUser . Isso permite que a conta use todos os acessos da instrução DML (Linguagem de Manipulação de Dados) (como as permissões SELECT e INSERT ) nos objetos que ela contém. TestManagerUser também tem 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 instrução GRANT, consulte GRANT (Transact-SQL). Para obter mais informações sobre procedimentos armazenados, veja Procedimentos armazenados (Mecanismo de Banco de Dados). Para obter um cartaz de todas as permissões do Mecanismo de Banco de Dados, consulte https://aka.ms/sql-permissions-poster.

2. Crie um procedimento armazenado para acessar os dados

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

Use a instrução EXECUTE AS no código a seguir para alterar o contexto para TestManagerUser e criar um procedimento armazenado exibindo somente os dados requeridos por TestEmployeeUser. Para atender a esses requisitos, o procedimento armazenado aceita uma variável como número da ordem de compra e não exibe informações financeiras, e a cláusula WHERE limita os resultados a envios 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 qualquer objeto de banco de dados. O código a seguir (ainda no contexto TestManagerUser ) concede ao usuário da conta a capacidade de consultar informações na tabela base, pelo procedimento armazenado).

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

O procedimento armazenado faz parte do esquema Purchasing , embora nenhum esquema tenha sido explicitamente especificado devido ao TestManagerUser ser atribuído por padrão ao esquema Purchasing . Você pode usar informações de catálogo de sistema para localizar objetos, como 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 essa seção de exemplo concluída, o código muda o contexto de volta ao dbo usando a instrução REVERT.

REVERT;  
GO  

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

3. Acesse dados pelo procedimento armazenado

TestEmployeeUser não tem permissões nos objetos de banco de dados AdventureWorks2022 , a não ser um logon e os direitos atribuídos à função de banco de dados pública. 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  

O erro que é retornado:

Msg 229, Level 14, State 5, Line 6
The SELECT permission was denied on the object 'PurchaseOrderHeader', database 'AdventureWorks2022', schema 'Purchasing'.

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

EXEC Purchasing.usp_ShowWaitingItems 952  
GO  

4. Redefina o ambiente

O código a seguir usa o comando REVERT para retornar o contexto da conta atual ao dboe, 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 AdventureWorks2022;  
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  

Confira também

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