Catene di proprietà e cambio di contesto

Data creazione: 12 dicembre 2006

In questa esercitazione viene utilizzato uno scenario per illustrare i concetti della protezione di SQL Server relativi alle catene di proprietà e al cambio di contesto utente. Per ulteriori informazioni sulle catene di proprietà, vedere Catene di proprietà. Per ulteriori informazioni sul cambio di contesto, vedere Cambi di contesto.

[!NOTA] Per eseguire il codice dell'esercitazione deve essere configurata la protezione a modalità mista e deve essere installato il database AdventureWorks. Per ulteriori informazioni sulla protezione a modalità mista, vedere Modalità di autenticazione. Per ulteriori informazioni sull'installazione del database AdventureWorks, vedere Installazione dei database di esempio AdventureWorks e degli esempi.

Scenario

In questo scenario, due utenti necessitano di account per accedere ai dati relativi agli ordini di acquisto archiviati nel database AdventureWorks. Devono essere soddisfatti i requisiti seguenti:

• Il primo account (TestManagerUser) deve essere in grado di visualizzare tutti i dettagli di ogni ordine di acquisto.
• 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.
• Tutti gli altri account devono mantenere le autorizzazioni correnti.

Per soddisfare i requisiti di questo scenario, l'esempio è suddiviso in quattro parti in cui vengono illustrati i concetti delle catene di proprietà e del cambio di contesto:

1. Configurazione dell'ambiente.
2. Creazione di una stored procedure per l'accesso ai dati in base all'ordine di acquisto.
3. Accesso ai dati tramite la stored procedure.
4. Reimpostazione dell'ambiente.

Ogni blocco di codice dell'esempio è illustrato sulla stessa riga. Per copiare l'esempio completo, vedere Esempio completo alla fine dell'esercitazione.

1. Configurazione dell'ambiente

Utilizzare SQL Server Management Studio e il codice seguente per aprire il database AdventureWorks e utilizzare l'istruzione Transact-SQL CURRENT_USER per controllare che come contesto venga visualizzato l'utente dbo.

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

Per ulteriori informazioni sull'istruzione CURRENT_USER, vedere CURRENT_USER (Transact-SQL).

Utilizzare questo codice come utente dbo per creare due utenti nel server e nel database AdventureWorks.

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 ulteriori informazioni sull'istruzione CREATE USER, vedere CREATE USER (Transact-SQL). Per ulteriori informazioni sull'istruzione CREATE LOGIN, vedere CREATE LOGIN (Transact-SQL).

Utilizzare il codice seguente per modificare la proprietà dello schema Purchasing e impostarla sull'account TestManagerUser. Ciò consente all'account di utilizzare l'accesso completo alle istruzioni DML (Data Manipulation Language), ad esempio autorizzazioni SELECT e INSERT, sull'oggetto contenuto. Poiché non sono incluse autorizzazioni di tipo DDL (Data Definition Language), a TestManagerUser vengono concessi esplicitamente i diritti per le tabelle PurchaseOrderHeader e PurchaseOrderDetail, nonché la possibilità di creare stored procedure.

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

Per ulteriori informazioni sull'istruzione GRANT, vedere GRANT (Transact-SQL). Per ulteriori informazioni sugli schemi di database, vedere Schemi. Per ulteriori informazioni sulle stored procedure, vedere Stored procedure (Motore del database).

2. Creazione di una stored procedure per l'accesso ai dati

È possibile consentire a un utente di cambiare contesto all'interno di un database in due modi diversi: SETUSER o EXECUTE AS. Per utilizzare l'istruzione SETUSER, il chiamante deve essere un membro del ruolo predefinito del server sysadmin o l'account dbo. EXECUTE AS richiede autorizzazioni IMPERSONATE. Per ulteriori informazioni su questi concetti, vedere Confronto tra EXECUTE AS e SETUSER.

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. 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.

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. 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.

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. È possibile utilizzare le informazioni del catalogo di sistema per individuare gli oggetti, come illustrato nel codice seguente.

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

Per ulteriori informazioni sui cataloghi di sistema, vedere Esecuzione di query nel catalogo di sistema di SQL Server.

Al termine di questa sezione dell'esempio, nel codice il contesto viene cambiato e nuovamente impostato su dbo mediante l'istruzione REVERT.

REVERT;
GO

Per ulteriori informazioni sull'istruzione REVERT, vedere REVERT (Transact-SQL).

3. Accesso ai dati tramite la stored procedure

TestEmployeeUser non dispone di autorizzazioni per gli oggetti del database AdventureWorks ad eccezione dell'accesso e dei diritti assegnati al ruolo del database public. Quando TestEmployeeUser tenta di accedere alle tabelle di base, il codice seguente restituisce un errore.

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. Nel codice seguente, in cui viene ancora utilizzato TestEmployeeUser come contesto, viene passato l'ordine di acquisto 952 come parametro.

EXEC Purchasing.usp_ShowWaitingItems 952
GO

4. Reimpostazione dell'ambiente

Nel codice seguente viene utilizzato il comando REVERT per ripristinare dbo come contesto dell'account corrente e quindi viene reimpostato l'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

Esempio completo

In questa sezione è riportato il codice completo dell'esempio.

[!NOTA] Nel codice non sono inclusi i due errori previsti che dimostrano l'impossibilità per TestEmployeeUser di eseguire una selezione nelle tabelle di 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 AdventureWorks;
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::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

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

Vedere anche

Altre risorse

Considerazioni relative alla protezione di SQL Server
Considerazioni relative alla protezione per database e applicazioni per database

Guida in linea e informazioni

Assistenza su SQL Server 2005