Tutorial: Ownership Chains and Context Switching
Si applica a:
SQL ServerIstanza gestita di SQL di Azure
In questa esercitazione viene usato uno scenario per illustrare i concetti sulla sicurezza di SQL Server relativi a catene di proprietà e cambio di contesto utente.
Nota
Per eseguire il codice dell'esercitazione deve essere configurata la sicurezza a modalità mista e deve essere installato il database AdventureWorks2022 . Per altre informazioni sulla sicurezza in modalità mista, vedere Scegliere una modalità di autenticazione.
Scenario
In questo scenario due utenti necessitano degli account per accedere ai dati relativi agli ordini di acquisto archiviati nel database AdventureWorks2022 . È necessario soddisfare 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 dello scambio di contesto:
- Configurazione dell'ambiente.
- Creazione di una stored procedure per l'accesso ai dati in base all'ordine di acquisto.
- Accesso ai dati tramite la stored procedure.
- 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.
Prerequisiti
Per completare questa esercitazione, sono necessari SQL Server Management Studio, l'accesso a un server che esegue SQL Server e un database AdventureWorks2022.
- Installare SQL Server Management Studio.
- Installare SQL Server 2017 Developer Edition.
- Scaricare un database campione AdventureWorks.
Per istruzioni su come ripristinare un database in SQL Server Management Studio, vedere Ripristinare un database.
1. Configurazione dell'ambiente
Usare SQL Server Management Studio e il codice seguente per aprire il database AdventureWorks2022 e usare l'istruzione CURRENT_USER di Transact-SQL per controllare che come contesto venga visualizzato l'utente dbo.
USE AdventureWorks2022;
GO
SELECT CURRENT_USER AS 'Current User Name';
GO
Per altre 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 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
Per altre informazioni sull'istruzione CREATE USER, vedere CREATE USER (Transact-SQL). Per altre 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 . 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. TestManagerUser viene inoltre concessa la possibilità di creare stored procedure.
/* 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). Per altre informazioni sulle stored procedure, vedere Stored procedure (Motore di database). Per un grafico di tutte le autorizzazioni del motore di database, vedere https://aka.ms/sql-permissions-poster.
2. Creazione di una stored procedure per l'accesso ai dati
Per cambiare contesto all'interno di un database, utilizzare l'istruzione EXECUTE AS. EXECUTE AS richiede autorizzazioni IMPERSONATE.
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
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 altre informazioni sull'istruzione REVERT, vedere REVERT (Transact-SQL).
3. Accesso ai dati tramite la stored procedure
TestEmployeeUser non dispone di autorizzazioni per gli oggetti di database AdventureWorks2022 ad eccezione dell'accesso e dei diritti assegnati al ruolo di 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
L'errore restituito è il seguente:
Msg 229, Level 14, State 5, Line 6
The SELECT permission was denied on the object 'PurchaseOrderHeader', database 'AdventureWorks2022', schema 'Purchasing'.
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 dbocome 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 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
Vedi anche
Centro sicurezza per il motore di Database di SQL Server e il Database SQL di Azure
Commenti e suggerimenti
Presto disponibile: Nel corso del 2024 verranno gradualmente disattivati i problemi di GitHub come meccanismo di feedback per il contenuto e ciò verrà sostituito con un nuovo sistema di feedback. Per altre informazioni, vedere https://aka.ms/ContentUserFeedback.
Invia e visualizza il feedback per