Esercitazione: Firma di stored procedure tramite un certificatoTutorial: Signing Stored Procedures with a Certificate

In questa esercitazione viene illustrata la firma di stored procedure tramite un certificato generato da SQL ServerSQL Server.This tutorial illustrates signing stored procedures using a certificate generated by SQL ServerSQL Server.

Nota

Per eseguire il codice dell'esercitazione deve essere configurata la sicurezza a modalità mista e deve essere installato il database AdventureWorks2012AdventureWorks2012 .To run the code in this tutorial you must have both Mixed Mode security configured and the AdventureWorks2012AdventureWorks2012 database installed. ScenarioScenario

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.Signing stored procedures using a certificate is useful when you want to require permissions on the stored procedure but you do not want to explicitly grant a user those rights. 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.Although you can accomplish this task in other ways, such as using the EXECUTE AS statement, using a certificate allows you to use a trace to find the original caller of the stored procedure. Ciò offre un elevato livello di controllo, in particolare durante operazioni DDL (Data Definition Language) o di sicurezza.This provides a high level of auditing, especially during security or Data Definition Language (DDL) operations.

È 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.You can create a certificate in the master database to allow server-level permissions, or you can create a certificate in any user databases to allow database-level permissions. In questo scenario, un utente che non dispone di diritti per le tabelle di base deve accedere a una stored procedure nel database AdventureWorks2012AdventureWorks2012 e si desidera controllare l'itinerario di accesso agli oggetti.In this scenario, a user with no rights to base tables must access a stored procedure in the AdventureWorks2012AdventureWorks2012 database, and you want to audit the object access trail. 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.Rather than using other ownership chain methods, you will create a server and database user account with no rights to the base objects, and a database user account with rights to a table and a stored procedure. Sia la stored procedure che il secondo account utente del database verranno protetti con un certificato.Both the stored procedure and the second database user account will be secured with a certificate. 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.The second database account will have access to all objects, and grant access to the stored procedure to the first database user account.

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:In this scenario you will first create a database certificate, a stored procedure, and a user, and then you will test the process following these steps:

  1. Configurare l'ambiente.Configure the environment.

  2. Creare un certificato.Create a certificate.

  3. Creare e firmare una stored procedure utilizzando il certificato.Create and sign a stored procedure using the certificate.

  4. Creare un account del certificato utilizzando il certificato.Create a certificate account using the certificate.

  5. Concedere i diritti per il database dell'account del certificato.Grant the certificate account database rights.

  6. Visualizzare il contesto di accesso.Display the access context.

  7. Reimpostare l'ambiente.Reset the environment.

Ogni blocco di codice dell'esempio è illustrato sulla stessa riga.Each code block in this example is explained in line. Per copiare l'esempio completo, vedere Esempio completo alla fine dell'esercitazione.To copy the complete example, see Complete Example at the end of this tutorial.

1. Configurazione dell'ambiente1. Configure the Environment

Per impostare il contesto iniziale dell'esempio, in SQL Server Management StudioSQL Server Management Studio aprire una nuova query ed eseguire il codice seguente per aprire il database AdventureWorks2012AdventureWorks2012.To set the initial context of the example, in SQL Server Management StudioSQL Server Management Studio open a new Query and run the following code to open the AdventureWorks2012AdventureWorks2012 database. Con questo codice, il contesto di database viene modificato in AdventureWorks2012 e viene creato un nuovo account di accesso al server e utente del database (TestCreditRatingUser) con una password.This code changes the database context to AdventureWorks2012 and creates a new server login and database user account (TestCreditRatingUser), using a password.

USE AdventureWorks2012;  
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).For more information on the CREATE USER statement, see CREATE USER (Transact-SQL). Per altre informazioni sull'istruzione CREATE LOGIN, vedere CREATE LOGIN (Transact-SQL).For more information on the CREATE LOGIN statement, see CREATE LOGIN (Transact-SQL).

2. Creazione di un certificato2. Create a Certificate

È possibile creare certificati nel server utilizzando il database master come contesto, un database utente o entrambi.You can create certificates in the server using the master database as the context, using a user database, or both. Per la sicurezza del certificato sono disponibili più opzioni.There are multiple options for securing the certificate. Per altre informazioni sui certificati, vedere CREATE CERTIFICATE (Transact-SQL).For more information on certificates, see CREATE CERTIFICATE (Transact-SQL).

Eseguire questo codice per creare un certificato del database e proteggerlo tramite una password.Run this code to create a database certificate and secure it using a password.

CREATE CERTIFICATE TestCreditRatingCer  
   ENCRYPTION BY PASSWORD = 'pGFD4bb925DGvbd2439587y'  
      WITH SUBJECT = 'Credit Rating Records Access',   
      EXPIRY_DATE = '12/05/2014';  
GO  

3. Creazione e firma di una stored procedure utilizzando il certificato3. Create and Sign a Stored Procedure Using the Certificate

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.Use the following code to create a stored procedure that selects data from the Vendor table in the Purchasing database schema, restricting access to only the companies with a credit rating of 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.Note that the first section of the stored procedure displays the context of the user account running the stored procedure, which is to demonstrate the concepts only. Non è necessario per soddisfare i requisiti.It is not required to satisfy the requirements.

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.Run this code to sign the stored procedure with the database certificate, using a password.

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

Per altre informazioni sulle stored procedure, vedere Stored procedure (motore di database).For more information on stored procedures, see Stored Procedures (Database Engine).

Per altre informazioni sulla firma di stored procedure, vedere ADD SIGNATURE (Transact-SQL).For more information on signing stored procedures, see ADD SIGNATURE (Transact-SQL).

4. Creazione di un account del certificato utilizzando il certificato4. Create a Certificate Account Using the Certificate

Eseguire questo codice per creare un utente del database (TestCreditRatingcertificateAccount) dal certificato.Run this code to create a database user (TestCreditRatingcertificateAccount) from the certificate. Tale account non dispone di accesso server e controllerà l'accesso alle tabelle sottostanti.This account has no server login, and will ultimately control access to the underlying tables.

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

5. Concessione dei diritti per il database dell'account del certificato5. Grant the Certificate Account Database Rights

Eseguire questo codice per concedere a TestCreditRatingcertificateAccount diritti per la tabella di base e la stored procedure.Run this code to grant TestCreditRatingcertificateAccount rights to the base table and the 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, vedere GRANT (Transact-SQL).For more information on granting permissions to objects, see GRANT (Transact-SQL).

6. Visualizzazione del contesto di accesso6. Display the Access Context

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.To display the rights associated with the stored procedure access, run the following code to grant the rights to run the stored procedure to the TestCreditRatingUser user.

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.Next, run the following code to run the stored procedure as the dbo login you used on the server. Osservare l'output delle informazioni sul contesto utente.Observe the output of the user context information. L'account dbo verrà riportato come contesto con diritti propri e non tramite l'appartenenza a un gruppo.It will show the dbo account as the context with its own rights and not through a group membership.

EXECUTE TestCreditRatingSP;  
GO  

Eseguire il codice seguente per utilizzare l'istruzione EXECUTE AS per eseguire la stored procedure tramite l'account TestCreditRatingUserRun the following code to use the EXECUTE AS statement to become the TestCreditRatingUser account and run the stored procedure. In questo caso, il contesto utente sarà impostato sul contesto USER MAPPED TO CERTIFICATE.This time you will see the user context is set to the USER MAPPED TO CERTIFICATE context.

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

Ciò illustra il livello di controllo disponibile grazie alla firma della stored procedure.This shows you the auditing available because you signed the stored procedure.

Nota

Usare EXECUTE AS per cambiare contesto all'interno di un database.Use EXECUTE AS to switch contexts within a database.

7. Reimpostazione dell'ambiente7. Reset the Environment

Nel codice seguente viene utilizzata l'istruzione REVERT per ripristinare dbo come contesto dell'account corrente e quindi viene reimpostato l'ambiente.The following code uses the REVERT statement to return the context of the current account to dbo, and resets the environment.

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).For more information about the REVERT statement, see REVERT (Transact-SQL).

Esempio completoComplete Example

In questa sezione è riportato il codice completo dell'esempio.This section displays the complete example code.

/* Step 1 - Open the AdventureWorks2012 database */  
USE AdventureWorks2012;  
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 AdventureWorks2012 database */  
CREATE CERTIFICATE TestCreditRatingCer  
   ENCRYPTION BY PASSWORD = 'pGFD4bb925DGvbd2439587y'  
      WITH SUBJECT = 'Credit Rating Records Access',   
      EXPIRY_DATE = '12/05/2014';  
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 AdventureWorks2012;  
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 ancheSee Also

Centro di sicurezza per il motore di database di SQL Server e il database SQL di AzureSecurity Center for SQL Server Database Engine and Azure SQL Database