ENCRYPTBYKEY (Transact-SQL)

Articolo
06/18/2013

Crittografa dati tramite una chiave simmetrica.

Icona di collegamento a un argomento Convenzioni della sintassi Transact-SQL

Sintassi

EncryptByKey ( key_GUID , { 'cleartext' | @cleartext }
    [, { add_authenticator | @add_authenticator }
     , { authenticator | @authenticator } ] )

Argomenti

key_GUID
GUID della chiave da utilizzare per crittografare la variabile cleartext. uniqueidentifier.
'cleartext'
Dati da crittografare tramite la chiave.
@cleartext
Variabile di tipo nvarchar, char, varchar, binary, varbinary o nchar contenente dati da crittografare con la chiave.
add_authenticator
Indica se un autenticatore verrà crittografato assieme alla variabile cleartext. Deve essere 1 se si utilizza un autenticatore. int.
@add\_authenticator
Indica se un autenticatore verrà crittografato assieme alla variabile cleartext. Deve essere 1 se si utilizza un autenticatore. int.
authenticator
Dati da cui derivare un autenticatore. sysname.
@authenticator
Variabile contenente i dati da cui derivare un autenticatore.

Tipi restituiti

varbinary con dimensioni massime pari a 8.000 byte.

Restituisce NULL se la chiave non è aperta, non esiste o se è una chiave RC4 deprecata e il database non è nel livello di compatibilità 110.

Osservazioni

EncryptByKey utilizza una chiave simmetrica. Tale chiave deve essere aperta. Se la chiave simmetrica è già aperta nella sessione corrente, non è necessario aprirla nuovamente nel contesto della query.

L'autenticatore consente di ridurre la sostituzione di valori interi dei campi crittografati. Si consideri, ad esempio, la tabella seguente contenente dati relativi alle retribuzioni.

Employee_ID	Standard_Title	Base_Pay
345	Copy Room Assistant	Fskj%7^edhn00
697	Chief Financial Officer	M0x8900f56543
694	Data Entry Supervisor	Cvc97824%^34f

Senza violare la crittografia, un utente malintenzionato è in grado di dedurre informazioni significative dal contesto in cui è archiviato il testo crittografato. Poiché la retribuzione del ruolo Chief Financial Officer è maggiore di quella del ruolo Copy Room Assistant, è necessario che il valore crittografato come M0x8900f56543 sia maggiore di quello crittografato come Fskj%7^edhn00. In questo caso, qualsiasi utente che dispone dell'autorizzazione ALTER per la tabella è potenzialmente in grado di aumentare la retribuzione del ruolo Copy Room Assistant sostituendo i dati nel campo Base_Pay corrispondente con una copia dei dati archiviati nel campo Base_Pay relativo al ruolo Chief Financial Officer. Questo attacco di sostituzione dell'intero valore è quindi in grado di agire nonostante la crittografia.

Gli attacchi basati sulla sostituzione dell'intero valore possono essere ostacolati tramite l'aggiunta di informazioni contestuali al testo normale prima di procedere alla sua crittografia. Tali informazioni vengono utilizzate per verificare che i dati in formato testo normale non siano stati spostati.

Se per la crittografia dei dati viene specificato un parametro di autenticazione, è necessario utilizzare lo stesso autenticatore per decrittografare i dati tramite DecryptByKey. Al momento dell'esecuzione della crittografia, un hash dell'autenticatore viene crittografato insieme al testo normale. Durante la fase di decrittografia, è necessario passare lo stesso autenticatore a DecryptByKey. Se gli autenticatori non corrispondono, la decrittografia non è possibile. Questo indica che il valore è stato spostato successivamente all'esecuzione della crittografia. È consigliabile utilizzare una colonna contenente un valore univoco e non modificabile come autenticatore. Se il valore dell'autenticatore viene modificato, si può perdere l'accesso ai dati.

La crittografia e decrittografia simmetriche sono operazioni relativamente veloci, ideali per operazioni basate su quantità elevate di dati.

Importante
L'utilizzo delle funzioni di crittografia di SQL Server con l'impostazione ANSI_PADDING OFF può provocare la perdita di dati a causa delle conversioni implicite. Per ulteriori informazioni relative ad ANSI_PADDING, vedere SET ANSI_PADDING (Transact-SQL).

Esempi

La funzionalità illustrata negli esempi seguenti si basa sulle chiavi e sui certificati creati in Procedura: Crittografia di una colonna di dati.

A.Crittografia di una stringa tramite una chiave simmetrica

Nell'esempio seguente viene aggiunta una colonna alla tabella Employee, quindi viene crittografato il valore del numero di previdenza sociale archiviato nella colonna NationalIDNumber.

USE AdventureWorks2012;
GO

-- Create a column in which to store the encrypted data.
ALTER TABLE HumanResources.Employee
    ADD EncryptedNationalIDNumber varbinary(128); 
GO

-- Open the symmetric key with which to encrypt the data.
OPEN SYMMETRIC KEY SSN_Key_01
   DECRYPTION BY CERTIFICATE HumanResources037;

-- Encrypt the value in column NationalIDNumber with symmetric key
-- SSN_Key_01. Save the result in column EncryptedNationalIDNumber.
UPDATE HumanResources.Employee
SET EncryptedNationalIDNumber
    = EncryptByKey(Key_GUID('SSN_Key_01'), NationalIDNumber);
GO

B.Crittografia di un record assieme a un valore di autenticazione

USE AdventureWorks2012;

-- Create a column in which to store the encrypted data.
ALTER TABLE Sales.CreditCard. 
    ADD CardNumber_Encrypted varbinary(128); 
GO

-- Open the symmetric key with which to encrypt the data.
OPEN SYMMETRIC KEY CreditCards_Key11
    DECRYPTION BY CERTIFICATE Sales09;

-- Encrypt the value in column CardNumber with symmetric 
-- key CreditCards_Key11.
-- Save the result in column CardNumber_Encrypted.  
UPDATE Sales.CreditCard
SET CardNumber_Encrypted = EncryptByKey(Key_GUID('CreditCards_Key11'), 
    CardNumber, 1, CONVERT( varbinary, CreditCardID) );
GO