Esercitazione: Firma di Stored Procedure con un certificato

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

Annotazioni

Per eseguire il codice in questa esercitazione, è necessario avere sia la sicurezza in modalità mista configurata che il database AdventureWorks2012 installato. Sceneggiatura

La firma di stored procedure con un certificato è utile quando si desidera richiedere autorizzazioni per la stored procedure, ma non si desidera concedere esplicitamente a un utente tali diritti. Sebbene sia possibile eseguire questa attività in altri modi, ad esempio usando l'istruzione EXECUTE AS, l'uso di un certificato consente di usare una traccia per trovare il chiamante originale della stored procedure. Ciò offre un livello elevato di controllo, in particolare durante le operazioni DDL (Data Definition Language) o sicurezza.

È possibile creare un certificato nel database master per consentire le autorizzazioni a livello di server oppure creare un certificato in qualsiasi database utente per consentire autorizzazioni a livello di database. In questo scenario, un utente senza diritti per le tabelle di base deve accedere a una stored procedure nel database AdventureWorks2012 e si vuole controllare la traccia di accesso agli oggetti. Anziché usare altri metodi della catena di proprietà, si creerà un account utente del server e del database senza diritti 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 di database avrà accesso a tutti gli oggetti e concederà l'accesso alla stored procedure al primo account utente del database.

In questo scenario si creerà innanzitutto un certificato di database, una stored procedure e un utente e quindi si eseguirà il test del processo seguendo questa procedura:

Configurare l'ambiente.
Crea un certificato.
Creare e firmare una procedura archiviata usando il certificato.
Creare un account certificato usando il certificato.
Concedere i diritti del database dell'account certificato.
Visualizzare il contesto di accesso.
Reimpostare l'ambiente.

Ogni blocco di codice in questo esempio viene illustrato in riga. Per copiare l'esempio completo, vedere Esempio completo alla fine di questa esercitazione.

1. Configurare l'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 AdventureWorks2012 . Questo codice modifica il contesto del database a AdventureWorks2012 per creare nuove credenziali di accesso del server e un nuovo account utente del database (TestCreditRatingUser), utilizzando una 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 ulteriori informazioni sull'istruzione CREATE USER, vedere CREATE USER (Transact-SQL). Per altre informazioni sull'istruzione CREATE LOGIN, vedere CREATE LOGIN (Transact-SQL).

2. Creare un certificato

È possibile creare certificati nel server usando il database master come contesto, usando un database utente o entrambi. Sono disponibili più opzioni per proteggere il certificato. Per altre informazioni sui certificati, vedere CREATE CERTIFICATE (Transact-SQL).

Eseguire questo codice per creare un certificato di database e proteggerlo usando una password.

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

3. Creare e firmare una stored procedure utilizzando il certificato

Usare il codice seguente per creare una stored procedure che seleziona i dati dalla Vendor tabella nello schema del Purchasing database, limitando l'accesso solo alle società con una classificazione di credito pari a 1. Si noti che la prima sezione della stored procedure visualizza il contesto dell'account utente che esegue la stored procedure, il cui scopo è dimostrare solo i concetti. Non è necessario 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 usando 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. Creare un account certificato usando il certificato

Eseguire questo codice per creare un utente del database (TestCreditRatingcertificateAccount) dal certificato. Questo account non ha accesso al server e infine controllerà l'accesso alle tabelle sottostanti.

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

5. Concedere i diritti di database dell'account certificato

Eseguire questo codice per concedere TestCreditRatingcertificateAccount i diritti alla tabella di base e alla stored procedure.

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

Per altre informazioni sulla concessione delle autorizzazioni agli oggetti, vedere GRANT (Transact-SQL).

6. Visualizzare il contesto di accesso

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

GRANT EXECUTE   
   ON TestCreditRatingSP   
   TO TestCreditRatingUser;  
GO

Quindi, eseguire il codice seguente per eseguire la procedura memorizzata come account di accesso dbo usato nel server. Osservare l'output delle informazioni sul contesto utente. L'account dbo verrà visualizzato come contesto con i propri diritti e non tramite l'appartenenza a un gruppo.

EXECUTE TestCreditRatingSP;  
GO

Esegui il codice seguente per utilizzare l'istruzione EXECUTE AS per diventare l'account TestCreditRatingUser ed eseguire la procedura memorizzata. Questa volta si noterà che il contesto utente è impostato sul contesto UTENTE MAPPATO A CERTIFICATO.

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

Viene visualizzato il controllo disponibile perché è stata firmata la stored procedure.

Annotazioni

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

7. Reimpostare l'ambiente

Il codice seguente usa l'istruzione REVERT per restituire il contesto dell'account corrente a dbo e reimposta 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 viene visualizzato il codice di esempio completo.

/* 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 anche

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

Last updated on 2017-06-14

Condividi tramite