Esercitazione: Firma di stored procedure tramite un certificato

Articolo
05/24/2023

Si applica a: SQL Server database SQL di Azure Istanza gestita di SQL di Azure

Questa esercitazione illustra la firma di stored procedure tramite un certificato generato da SQL Server.

Nota

Per eseguire il codice dell'esercitazione deve essere configurata la sicurezza a modalità mista e deve essere installato il database AdventureWorks2022 .

La firma di stored procedure tramite un certificato si rivela utile quando si desidera richiedere autorizzazioni per la stored procedure ma non concedere esplicitamente tali diritti all'utente. Nonostante sia possibile completare questa attività in altri modi, ad esempio mediante l'istruzione EXECUTE AS, l'utilizzo di un certificato consente di disporre di una traccia per individuare il chiamante originale della stored procedure. Ciò offre un elevato livello di controllo, in particolare durante operazioni DDL (Data Definition Language) o di sicurezza.

È possibile creare un certificato nel database master per concedere autorizzazioni a livello di server oppure in qualsiasi database utente per concedere autorizzazioni a livello di database. In questo scenario, un utente che non dispone di diritti per le tabelle di base deve accedere a una stored procedure nel database AdventureWorks2022 e si desidera controllare l'itinerario di accesso agli oggetti. Anziché utilizzare altri metodi delle catene di proprietà, verranno creati un account utente del server e del database senza alcun diritto per gli oggetti di base e un account utente del database con diritti per una tabella e una stored procedure. Sia la stored procedure che il secondo account utente del database verranno protetti con un certificato. Il secondo account del database disporrà dell'accesso a tutti gli oggetti e concederà l'accesso alla stored procedure al primo account utente del database.

In questo scenario, verranno innanzitutto creati un certificato del database, una stored procedure e un utente e quindi verrà testato il processo eseguendo la procedura seguente:

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

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

Per impostare il contesto iniziale dell'esempio, in SQL Server Management Studio aprire una nuova query ed eseguire il codice seguente per aprire il database AdventureWorks2022. Con questo codice, il contesto di database viene modificato in AdventureWorks2022 e viene creato un nuovo account di accesso al server e utente del database (TestCreditRatingUser) con una password.

USE AdventureWorks2022;  
GO  
-- Set up a login for the test user  
CREATE LOGIN TestCreditRatingUser  
   WITH PASSWORD = 'ASDECd2439587y'  
GO  
CREATE USER TestCreditRatingUser  
FOR LOGIN TestCreditRatingUser;  
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).

2. Creazione di un certificato

È possibile creare certificati nel server utilizzando il database master come contesto, un database utente o entrambi. Per la sicurezza del certificato sono disponibili più opzioni. Per altre informazioni sui certificati, vedere CREATE CERTIFICATE (Transact-SQL).

Eseguire questo codice per creare un certificato del database e proteggerlo tramite una password.

CREATE CERTIFICATE TestCreditRatingCer  
   ENCRYPTION BY PASSWORD = 'pGFD4bb925DGvbd2439587y'  
      WITH SUBJECT = 'Credit Rating Records Access',   
      EXPIRY_DATE = '12/31/2022';  -- Error 3701 will occur if this date is not in the future
GO

3. Creazione e firma di una stored procedure utilizzando il certificato

Utilizzare il codice seguente per creare una stored procedure che selezioni i dati dalla tabella Vendor nello schema di database Purchasing , limitando l'accesso alle sole aziende con posizione creditizia 1. Si noti che nella prima sezione della stored procedure viene visualizzato il contesto dell'account utente che esegue la stored procedure, al solo scopo di dimostrare i concetti. Non è necessario per soddisfare i requisiti.

CREATE PROCEDURE TestCreditRatingSP  
AS  
BEGIN  
   -- Show who is running the stored procedure  
   SELECT SYSTEM_USER 'system Login'  
   , USER AS 'Database Login'  
   , NAME AS 'Context'  
   , TYPE  
   , USAGE   
   FROM sys.user_token     
  
   -- Now get the data  
   SELECT AccountNumber, Name, CreditRating   
   FROM Purchasing.Vendor  
   WHERE CreditRating = 1  
END  
GO

Eseguire questo codice per firmare la stored procedure con il certificato del database, utilizzando una password.

ADD SIGNATURE TO TestCreditRatingSP   
   BY CERTIFICATE TestCreditRatingCer  
    WITH PASSWORD = 'pGFD4bb925DGvbd2439587y';  
GO

Per altre informazioni sulle stored procedure, vedere Stored procedure (motore di database).

Per altre informazioni sulla firma di stored procedure, vedere ADD SIGNATURE (Transact-SQL).

4. Creazione di un account del certificato utilizzando il certificato

Eseguire questo codice per creare un utente del database (TestCreditRatingcertificateAccount) dal certificato. Tale account non dispone di accesso server e controllerà l'accesso alle tabelle sottostanti.

USE AdventureWorks2022;  
GO  
CREATE USER TestCreditRatingcertificateAccount  
   FROM CERTIFICATE TestCreditRatingCer;  
GO

5. Concessione dei diritti per il database dell'account del certificato

Eseguire questo codice per concedere a TestCreditRatingcertificateAccount diritti per la tabella di base e la stored procedure.

GRANT SELECT   
   ON Purchasing.Vendor   
   TO TestCreditRatingcertificateAccount;  
GO  
  
GRANT EXECUTE   
   ON TestCreditRatingSP   
   TO TestCreditRatingcertificateAccount;  
GO

Per altre informazioni sulla concessione di autorizzazioni per gli oggetti, vedereGRANT (Transact-SQL).

6. Visualizzazione del contesto di accesso

Per visualizzare i diritti associati all'accesso alla stored procedure, eseguire il codice seguente per concedere i diritti per l'esecuzione della stored procedure all'utente TestCreditRatingUser .

GRANT EXECUTE   
   ON TestCreditRatingSP   
   TO TestCreditRatingUser;  
GO

Eseguire quindi il codice seguente per eseguire la stored procedure con l'account di accesso dbo utilizzato nel server. Osservare l'output delle informazioni sul contesto utente. L'account dbo verrà riportato come contesto con diritti propri e non tramite l'appartenenza a un gruppo.

EXECUTE TestCreditRatingSP;  
GO

Eseguire il codice seguente per utilizzare l'istruzione EXECUTE AS per eseguire la stored procedure tramite l'account TestCreditRatingUser In questo caso, il contesto utente sarà impostato sul contesto USER MAPPED TO CERTIFICATE. Si noti che questa opzione non è supportata in un database indipendente o in un database SQL di Azure o in Azure Synapse Analytics.

EXECUTE AS LOGIN = 'TestCreditRatingUser';  
GO  
EXECUTE TestCreditRatingSP;  
GO

Ciò illustra il livello di controllo disponibile grazie alla firma della stored procedure.

Nota

Usare EXECUTE AS per cambiare contesto all'interno di un database.

7. Reimpostazione dell'ambiente

Nel codice seguente viene utilizzata l'istruzione REVERT per ripristinare dbo come contesto dell'account corrente e quindi viene reimpostato l'ambiente.

REVERT;  
GO  
DROP PROCEDURE TestCreditRatingSP;  
GO  
DROP USER TestCreditRatingcertificateAccount;  
GO  
DROP USER TestCreditRatingUser;  
GO  
DROP LOGIN TestCreditRatingUser;  
GO  
DROP CERTIFICATE TestCreditRatingCer;  
GO

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

Esempio completo

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

/* Step 1 - Open the AdventureWorks2022 database */  
USE AdventureWorks2022;  
GO  
-- Set up a login for the test user  
CREATE LOGIN TestCreditRatingUser  
   WITH PASSWORD = 'ASDECd2439587y'  
GO  
CREATE USER TestCreditRatingUser  
FOR LOGIN TestCreditRatingUser;  
GO  
  
/* Step 2 - Create a certificate in the AdventureWorks2022 database */  
CREATE CERTIFICATE TestCreditRatingCer  
   ENCRYPTION BY PASSWORD = 'pGFD4bb925DGvbd2439587y'  
      WITH SUBJECT = 'Credit Rating Records Access',   
      EXPIRY_DATE = '12/31/2021';   -- Error 3701 will occur if this date is not in the future
GO  
  
/* Step 3 - Create a stored procedure and  
sign it using the certificate */  
CREATE PROCEDURE TestCreditRatingSP  
AS  
BEGIN  
   -- Shows who is running the stored procedure  
   SELECT SYSTEM_USER 'system Login'  
   , USER AS 'Database Login'  
   , NAME AS 'Context'  
   , TYPE  
   , USAGE   
   FROM sys.user_token;     
  
   -- Now get the data  
   SELECT AccountNumber, Name, CreditRating   
   FROM Purchasing.Vendor  
   WHERE CreditRating = 1;  
END  
GO  
  
ADD SIGNATURE TO TestCreditRatingSP   
   BY CERTIFICATE TestCreditRatingCer  
    WITH PASSWORD = 'pGFD4bb925DGvbd2439587y';  
GO  
  
/* Step 4 - Create a database user for the certificate.   
This user has the ownership chain associated with it. */  
USE AdventureWorks2022;  
GO  
CREATE USER TestCreditRatingcertificateAccount  
   FROM CERTIFICATE TestCreditRatingCer;  
GO  
  
/* Step 5 - Grant the user database rights */  
GRANT SELECT   
   ON Purchasing.Vendor   
   TO TestCreditRatingcertificateAccount;  
GO  
  
GRANT EXECUTE  
   ON TestCreditRatingSP   
   TO TestCreditRatingcertificateAccount;  
GO  
  
/* Step 6 - Test, using the EXECUTE AS statement */  
GRANT EXECUTE   
   ON TestCreditRatingSP   
   TO TestCreditRatingUser;  
GO  
  
-- Run the procedure as the dbo user, notice the output for the type  
EXEC TestCreditRatingSP;  
GO  
  
EXECUTE AS LOGIN = 'TestCreditRatingUser';  
GO  
EXEC TestCreditRatingSP;  
GO  
  
/* Step 7 - Clean up the example */  
REVERT;  
GO  
DROP PROCEDURE TestCreditRatingSP;  
GO  
DROP USER TestCreditRatingcertificateAccount;  
GO  
DROP USER TestCreditRatingUser;  
GO  
DROP LOGIN TestCreditRatingUser;  
GO  
DROP CERTIFICATE TestCreditRatingCer;  
GO

Vedi anche

Centro sicurezza per il motore di Database di SQL Server e il Database SQL di Azure

Condividi tramite