Esercitazione: Firma di stored procedure tramite un certificato
Si applica a:
SQL ServerDatabase SQL di AzureIstanza 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
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