Tutorial: Cadeias de propriedade e alternância de contexto

Este tutorial usa um cenário para ilustrar conceitos de segurança do SQL Server envolvendo cadeias de propriedade e alternância de contexto de usuário. Para obter mais informações sobre cadeias de propriedade, consulte Cadeias de propriedade. Para obter mais informações sobre alternância de contexto, consulte Alternância de contexto (Mecanismo de Banco de Dados).

Observação
Para executar o código nesse tutorial será necessário ter a segurança do Modo Misto configurada e o banco de dados AdventureWorks2008R2 instalado. Para obter mais informações sobre a segurança de Modo Misto, consulte Escolhendo um modo de autenticação.

Cenário

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

• A primeira conta (TestManagerUser) deve estar habilitada para ver 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 da ordem, 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.

1. Configure o ambiente

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

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

Para obter mais informações sobre como usar 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 AdventureWorks2008R2.

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 como usar 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. Como ela não inclui permissões DDL (Linguagem de Definição de Dados), TestManagerUser tem direitos explícitos nas tabelas PurchaseOrderHeader e PurchaseOrderDetail, bem como a capacidade de criar procedimentos armazenados.

/* Change owner of the Purchasing Schema to TestManagerUser */
ALTER AUTHORIZATION 
   ON SCHEMA::Purchasing 
   TO TestManagerUser;
GO

/* Grant permissions to TestManagerUser on these objects with GRANT option */
GRANT ALL 
   ON OBJECT::AdventureWorks.Purchasing.PurchaseOrderHeader
    TO TestManagerUser 
   WITH GRANT OPTION;
GO
GRANT ALL 
   ON OBJECT::AdventureWorks.Purchasing.PurchaseOrderDetail
    TO TestManagerUser WITH GRANT OPTION;
GO
/* Note: DML works fine with Schema owner, but not DDL. */
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 esquemas de bancos de dados, consulte Esquemas (Mecanismo de Banco de Dados). Para obter mais informações sobre procedimentos armazenados, consulte Procedimento armazenados (Mecanismo de Banco de Dados).

2. Crie um procedimento armazenado para acessar os dados

Existem duas maneiras de permitir que um usuário troque contextos dentro de um banco de dados: SETUSER ou EXECUTE AS. O uso da instrução SETUSER requer que o chamador tenha associação na função de servidor fixa sysadmin ou pertença à conta dbo. EXECUTE AS requer permissões PERSONIFIQUE. Para obter mais informações sobre esses conceitos, consulte EXECUTE AS versus SETUSER.

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

Para obter mais informações sobre catálogos de sistema, consulte Consultando o catálogo do sistema SQL Server.

Com esta seção de exemplo concluída, o código alterna o contexto de volta ao dbo que usa 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 AdventureWorks2008R2 a não ser um logon e os direitos atribuídos à função 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 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 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.

Observação
Este código não inclui os dois erros previstos que demonstram a incapacidade de o TestEmployeeUser fazer a seleção a partir de 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 AdventureWorks2008R2;
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 permissions to TestManagerUser on these objects with GRANT option */
GRANT ALL 
   ON OBJECT::AdventureWorks2008R2.Purchasing.PurchaseOrderHeader
    TO TestManagerUser 
   WITH GRANT OPTION;
GO
GRANT ALL 
   ON OBJECT::AdventureWorks2008R2.Purchasing.PurchaseOrderDetail
    TO TestManagerUser WITH GRANT OPTION;
GO
/* Note: DML works fine with Schema owner, but not DDL */
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 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

/* 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 a
   INNER JOIN sys.objects 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

Conceitos

Cadeias de propriedade

Segurança e proteção (Mecanismo de Banco de Dados)

Outros recursos

Alternância de contexto (Mecanismo de Banco de Dados)