Tutorial: Cadeias de Propriedade e Mudança de Contexto

Aplica-se a:SQL Server Azure SQL Managed Instance

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

Observação

Para executar o código deste tutorial, deve ter tanto a segurança em Modo Misto configurada como a AdventureWorks2025 base de dados instalada. Para mais informações sobre segurança em Modo Misto, consulte Escolher um Modo de Autenticação.

Scenario

Neste cenário, dois utilizadores precisam de contas para aceder aos dados das ordens de compra armazenados na AdventureWorks2025 base de dados. Os requisitos são os seguintes:

A primeira conta (TestManagerUser) deve ser capaz de ver todos os detalhes em cada encomenda de compra.
A segunda conta (TestEmployeeUser) deve ser capaz de ver o número da encomenda de compra, data da encomenda, data de envio, números de identificação do produto e os artigos encomendados e recebidos por ordem de compra, por número de encomenda, para artigos onde foram recebidas remessas parciais.
Todas as outras contas devem manter as suas permissões atuais.
Para cumprir os requisitos deste cenário, o exemplo é dividido em quatro partes que demonstram os conceitos de cadeias de propriedade e troca de contexto:

Configurar o ambiente.
Criar um procedimento armazenado para aceder a dados por ordem de compra.
Aceder aos dados através do procedimento armazenado.
Restaurar o ambiente.

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

Pré-requisitos

Para concluir este tutorial, você precisa do SQL Server Management Studio, acesso a um servidor que esteja executando o SQL Server e um banco de dados AdventureWorks2025.

Instale o SQL Server Management Studio.
Instale SQL Server 2017 Developer Edition.
Baixe os bancos de dados de exemplo do AdventureWorks .

Para instruções sobre como restaurar uma base de dados no SQL Server Management Studio, consulte Restaurar uma base de dados.

1. Configurar o Ambiente

Utilize o SQL Server Management Studio e o código seguinte para abrir a AdventureWorks2025 base de dados, e utilize a CURRENT_USER instrução Transact-SQL para verificar se o utilizador dbo é apresentado como contexto.

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

Para mais informações sobre a declaração CURRENT_USER, veja CURRENT_USER (Transact-SQL).

Use este código como utilizador dbo para criar dois utilizadores no servidor e na AdventureWorks2025 base de dados.

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 mais informações sobre a instrução CRIAR UTILIZADOR, consulte CRIAR UTILIZADOR (Transact-SQL). Para mais informações sobre a instrução CRIAR LOGIN, consulte CRIAR LOGIN (Transact-SQL).

Use o seguinte código para alterar o proprietário do Purchasing esquema para a conta TestManagerUser. Isto permite que essa conta utilize todo o acesso a instruções da Linguagem de Manipulação de Dados (DML) (como SELECT e INSERT permissões) sobre os objetos que contém. É também concedida a TestManagerUser 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 mais informações sobre a declaração GRANT, consulte GRANT (Transact-SQL). Para mais informações sobre procedimentos armazenados, consulte Procedimentos Armazenados (Motor de Base de Dados). Para um cartaz com todas as permissões do Motor de Base de Dados, veja https://aka.ms/sql-permissions-poster.

2. Criar um procedimento armazenado para aceder a dados

Para mudar de contexto dentro de uma base de dados, use a instrução EXECUTE AS. EXECUTAR COMO requer permissões de IMPERSONATE.

Use a instrução EXECUTE AS no código seguinte para alterar o contexto para TestManagerUser e criar um procedimento armazenado que mostre apenas os dados exigidos por TestEmployeeUser. Para satisfazer os requisitos, o procedimento armazenado aceita uma variável para o número da ordem de compra e não apresenta 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 quaisquer objetos da base de dados. O código seguinte (ainda no TestManagerUser contexto) concede à conta de utilizador a capacidade de consultar informação da tabela base através do procedimento armazenado.

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

O procedimento armazenado faz parte do Purchasing esquema, mesmo que nenhum esquema tenha sido explicitamente especificado, porque TestManagerUser é atribuído por defeito ao Purchasing esquema. Pode usar a informação do catálogo do sistema para localizar objetos, como mostrado no código seguinte.

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 secção do exemplo concluída, o código muda o contexto de volta para dbo usando a REVERT instrução.

REVERT;  
GO

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

3. Aceder aos dados através do procedimento armazenado

TestEmployeeUser não tem permissões sobre os AdventureWorks2025 objetos da base de dados além do login e dos direitos atribuídos ao papel de base de dados pública. O código seguinte devolve um erro ao TestEmployeeUser tentar aceder às 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 é devolvido:

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 pelo procedimento armazenado criado na última secção são propriedade de TestManagerUser , em virtude da Purchasing propriedade do esquema, TestEmployeeUser podem aceder às tabelas base através do procedimento armazenado. O código seguinte, ainda usando o TestEmployeeUser contexto, passa a ordem de compra 952 como parâmetro.

EXEC Purchasing.usp_ShowWaitingItems 952  
GO

4. Reiniciar o Ambiente

O código seguinte usa o REVERT comando para devolver o contexto da conta corrente a dbo, e depois reinicia 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 secção mostra o código de exemplo completo.

Observação

Este código não inclui os dois erros esperados que demonstram a incapacidade de TestEmployeeUser selecionar a partir das tabelas base.

/*   
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

Ver também

Centro de Segurança para o Mecanismo de Banco de Dados do SQL Server e Azure SQL Database

Feedback

Esta página foi útil?

Last updated on 2025-11-19