Tutorial: Ownership Chains and Context SwitchingTutorial: Ownership Chains and Context Switching

In questa esercitazione viene utilizzato uno scenario per illustrare i concetti sulla sicurezza di SQL ServerSQL Server relativi a catene di proprietà e cambio di contesto utente.This tutorial uses a scenario to illustrate SQL ServerSQL Server security concepts involving ownership chains and user context switching.

Nota

Per eseguire il codice dell'esercitazione deve essere configurata la sicurezza a modalità mista e deve essere installato il database AdventureWorks2012AdventureWorks2012 .To run the code in this tutorial you must have both Mixed Mode security configured and the AdventureWorks2012AdventureWorks2012 database installed. Per altre informazioni sulla sicurezza in modalità mista, vedere Scegliere una modalità di autenticazione.For more information about Mixed Mode security, see Choose an Authentication Mode.

ScenarioScenario

In questo scenario due utenti necessitano degli account per accedere ai dati relativi agli ordini di acquisto archiviati nel database AdventureWorks2012AdventureWorks2012 .In this scenario, two users need accounts to access purchase order data stored in the AdventureWorks2012AdventureWorks2012 database. È necessario soddisfare i requisiti seguenti:The requirements are as follows:

  • Il primo account (TestManagerUser) deve essere in grado di visualizzare tutti i dettagli di ogni ordine di acquisto.The first account (TestManagerUser) must be able to see all details in every purchase order.

  • Il secondo account (TestEmployeeUser) deve essere in grado di visualizzare il numero dell'ordine di acquisto, la data dell'ordine, la data di spedizione, i numeri di serie dei prodotti e gli articoli ordinati e ricevuti per ordine di acquisto, in base al numero di ordine di acquisto, per gli articoli per i quali sono state eseguite spedizioni parziali.The second account (TestEmployeeUser) must be able to see the purchase order number, order date, shipping date, product ID numbers, and the ordered and received items per purchase order, by purchase order number, for items where partial shipments have been received.

  • Tutti gli altri account devono mantenere le autorizzazioni correnti.All other accounts must retain their current permissions.

Per soddisfare i requisiti di questo scenario, l'esempio è suddiviso in quattro parti in cui vengono illustrati i concetti delle catene di proprietà e dello scambio di contesto:To fulfill the requirements of this scenario, the example is broken into four parts that demonstrate the concepts of ownership chains and context switching:

  1. Configurazione dell'ambiente.Configuring the environment.

  2. Creazione di una stored procedure per l'accesso ai dati in base all'ordine di acquisto.Creating a stored procedure to access data by purchase order.

  3. Accesso ai dati tramite la stored procedure.Accessing the data through the stored procedure.

  4. Reimpostazione dell'ambiente.Resetting the environment.

Ogni blocco di codice dell'esempio è illustrato sulla stessa riga.Each code block in this example is explained in line. Per copiare l'esempio completo, vedere Esempio completo alla fine dell'esercitazione.To copy the complete example, see Complete Example at the end of this tutorial.

1. Configurazione dell'ambiente1. Configure the Environment

Utilizzare SQL Server Management StudioSQL Server Management Studio e il codice seguente per aprire il database AdventureWorks2012 e utilizzare l'istruzione Transact-SQLTransact-SQL CURRENT_USER per controllare che come contesto venga visualizzato l'utente dbo.Use SQL Server Management StudioSQL Server Management Studio and the following code to open the AdventureWorks2012 database, and use the CURRENT_USER Transact-SQLTransact-SQL statement to check that the dbo user is displayed as the context.

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

Per altre informazioni sull'istruzione CURRENT_USER, vedere CURRENT_USER (Transact-SQL).For more information about the CURRENT_USER statement, see CURRENT_USER (Transact-SQL).

Utilizzare questo codice come utente dbo per creare due utenti nel server e nel database AdventureWorks2012AdventureWorks2012.Use this code as the dbo user to create two users on the server and in the AdventureWorks2012AdventureWorks2012 database.

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   

Per altre informazioni sull'istruzione CREATE USER, vedere CREATE USER (Transact-SQL).For more information about the CREATE USER statement, see CREATE USER (Transact-SQL). Per altre informazioni sull'istruzione CREATE LOGIN, vedere CREATE LOGIN (Transact-SQL).For more information about the CREATE LOGIN statement, see CREATE LOGIN (Transact-SQL).

Utilizzare il codice seguente per modificare la proprietà dello schema Purchasing e impostarla sull'account TestManagerUser .Use the following code to change the ownership of the Purchasing schema to the TestManagerUser account. In questo modo si consente all'account di utilizzare l'accesso completo alle istruzioni DML (Data Manipulation Language), ad esempio autorizzazioni SELECT e INSERT , sull'oggetto contenuto.This allows that account to use all Data Manipulation Language (DML) statement access (such as SELECT and INSERT permissions) on the objects it contains. TestManagerUser viene inoltre concessa la possibilità di creare stored procedure.TestManagerUser is also granted the ability to create stored procedures.

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

Per altre informazioni sull'istruzione GRANT, vedere GRANT (Transact-SQL).For more information about the GRANT statement, see GRANT (Transact-SQL). Per altre informazioni sulle stored procedure, vedere Stored procedure (Motore di database).For more information about stored procedures, see Stored Procedures (Database Engine). Per un grafico di tutte le autorizzazioni del Motore di databaseDatabase Engine, vedere http://go.microsoft.com/fwlink/?LinkId=229142.For a poster of all Motore di databaseDatabase Engine permissions, see http://go.microsoft.com/fwlink/?LinkId=229142.

2. Creazione di una stored procedure per l'accesso ai dati2. Create a Stored Procedure to Access Data

Per cambiare contesto all'interno di un database, utilizzare l'istruzione EXECUTE AS.To switch context within a database, use the EXECUTE AS statement. EXECUTE AS richiede autorizzazioni IMPERSONATE.EXECUTE AS requires IMPERSONATE permissions.

Utilizzare l'istruzione EXECUTE AS del codice seguente per cambiare il contesto impostandolo su TestManagerUser e creare una stored procedure in grado di visualizzare soltanto i dati necessari per TestEmployeeUser.Use the EXECUTE AS statement in the following code to change the context to TestManagerUser and create a stored procedure showing only the data required by TestEmployeeUser. Per soddisfare i requisiti, la stored procedure accetta una variabile per il numero dell'ordine di acquisto e non visualizza informazioni finanziarie. La clausola WHERE limita inoltre i risultati alle spedizioni parziali.To satisfy the requirements, the stored procedure accepts one variable for the purchase order number and does not display financial information, and the WHERE clause limits the results to partial shipments.

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  

TestEmployeeUser non dispone attualmente dell'accesso a qualsiasi oggetto di database.Currently TestEmployeeUser does not have access to any database objects. Il codice seguente, ancora nel contesto TestManagerUser , concede all'account utente la possibilità di eseguire query sulle informazioni delle tabelle di base tramite la stored procedure.The following code (still in the TestManagerUser context) grants the user account the ability to query base-table information through the stored procedure.

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

La stored procedure appartiene allo schema Purchasing , nonostante non venga specificato esplicitamente uno schema, poiché TestManagerUser è assegnato per impostazione predefinita allo schema Purchasing .The stored procedure is part of the Purchasing schema, even though no schema was explicitly specified, because TestManagerUser is assigned by default to the Purchasing schema. È possibile utilizzare le informazioni del catalogo di sistema per individuare gli oggetti, come illustrato nel codice seguente.You can use system catalog information to locate objects, as shown in the following code.

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  

Al termine di questa sezione dell'esempio, nel codice il contesto viene cambiato e nuovamente impostato su dbo mediante l'istruzione REVERT.With this section of the example completed, the code switches context back to dbo using the REVERT statement.

REVERT;  
GO  

Per altre informazioni sull'istruzione REVERT, vedere REVERT (Transact-SQL).For more information about the REVERT statement, see REVERT (Transact-SQL).

3. Accesso ai dati tramite la stored procedure3. Access Data Through the Stored Procedure

TestEmployeeUser non dispone di autorizzazioni per gli oggetti di database AdventureWorks2012AdventureWorks2012 ad eccezione dell'accesso e dei diritti assegnati al ruolo di database public.TestEmployeeUser has no permissions on the AdventureWorks2012AdventureWorks2012 database objects other than a login and the rights assigned to the public database role. Quando TestEmployeeUser tenta di accedere alle tabelle di base, il codice seguente restituisce un errore.The following code returns an error when TestEmployeeUser attempts to access base tables.

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  

Poiché gli oggetti a cui fa riferimento la stored procedure creata nell'ultima sezione sono di proprietà di TestManagerUser in virtù della proprietà dello schema Purchasing , TestEmployeeUser può accedere alle tabelle di base tramite la stored procedure.Because the objects referenced by the stored procedure created in the last section are owned by TestManagerUser by virtue of the Purchasing schema ownership, TestEmployeeUser can access the base tables through the stored procedure. Nel codice seguente, in cui viene ancora utilizzato TestEmployeeUser come contesto, viene passato l'ordine di acquisto 952 come parametro.The following code, still using the TestEmployeeUser context, passes purchase order 952 as a parameter.

EXEC Purchasing.usp_ShowWaitingItems 952  
GO  

4. Reimpostazione dell'ambiente4. Reset the Environment

Nel codice seguente viene utilizzato il comando REVERT per ripristinare dbocome contesto dell'account corrente e quindi viene reimpostato l'ambiente.The following code uses the REVERT command to return the context of the current account to dbo, and then resets the environment.

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  

Esempio completoComplete Example

In questa sezione è riportato il codice completo dell'esempio.This section displays the complete example code.

Nota

Nel codice non sono inclusi i due errori previsti che dimostrano l'impossibilità per TestEmployeeUser di eseguire una selezione nelle tabelle di base.This code does not include the two expected errors that demonstrate the inability of TestEmployeeUser to select from base tables.

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

Vedere ancheSee Also

Centro di sicurezza per il motore di database di SQL Server e il database SQL di AzureSecurity Center for SQL Server Database Engine and Azure SQL Database