Firma di stored procedure con un certificato
Data creazione: 12 dicembre 2006
In questa esercitazione viene illustrata la firma di stored procedure tramite un certificato generato da SQL Server.
[!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
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 protezione.
È 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 AdventureWorks 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:
- Configurare l'ambiente.
- Creare un certificato.
- Creare e firmare una stored procedure utilizzando il certificato.
- Creare un account del certificato utilizzando il certificato.
- Concedere i diritti per il database dell'account del certificato.
- Visualizzare il contesto di accesso.
- Reimpostare l'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
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 AdventureWorks. Con questo codice, il contesto di database viene modificato in AdventureWorks e viene creato un nuovo account di accesso server e utente del database (TestCreditRatingUser) con una password.
USE AdventureWorks;
GO
-- Set up a login for the test user
CREATE LOGIN TestCreditRatingUser
WITH PASSWORD = 'ASDECd2439587y'
GO
CREATE USER TestCreditRatingUser
FOR LOGIN TestCreditRatingUser;
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).
2. Creazione di un certificato
È possibile creare certificati nel server utilizzando il database master come contesto, un database utente o entrambi. Per la protezione del certificato sono disponibili più opzioni. Per ulteriori 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/05/2010';
GO
3. Creazione e firma di una stored procedure utilizzando il certificato
Utilizzare il codice seguente per creare una stored procedure in grado di selezionare i dati della tabella Vendor nello schema di database Purchasing limitando l'accesso alle aziende con posizione creditizia 1. Si noti che nella prima sezione della stored procedure viene visualizzato il contesto dell'account utente da cui viene eseguita, 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 ulteriori informazioni sulle stored procedure, vedere Stored procedure (Motore del database).
Per ulteriori informazioni sugli schemi di database, vedere Schemi.
Per ulteriori 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 AdventureWorks;
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 ulteriori informazioni sulla concessione di autorizzazioni per gli oggetti, vedere GRANT (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.
EXECUTE AS LOGIN = 'TestCreditRatingUser';
GO
EXECUTE TestCreditRatingSP;
GO
Ciò illustra il livello di controllo disponibile grazie alla firma della stored procedure.
[!NOTA] È possibile consentire a un utente di cambiare contesto all'interno di un database in due modi diversi: SETUSER o EXECUTE AS. Per ulteriori informazioni sul cambio di contesto, vedere Confronto tra EXECUTE AS e SETUSER.
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 ulteriori informazioni sull'istruzione REVERT, vedere REVERT (Transact-SQL).
Esempio completo
In questa sezione è riportato il codice completo dell'esempio.
/* Step 1 - Open the AdventureWorks database */
USE AdventureWorks;
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 AdventureWorks database */
CREATE CERTIFICATE TestCreditRatingCer
ENCRYPTION BY PASSWORD = 'pGFD4bb925DGvbd2439587y'
WITH SUBJECT = 'Credit Rating Records Access',
EXPIRY_DATE = '12/05/2010';
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 AdventureWorks;
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
Vedere anche
Altre risorse
Considerazioni relative alla protezione di SQL Server
Considerazioni relative alla protezione per database e applicazioni per database