ENCRYPTBYKEY (Transact-SQL)

Articolo
05/30/2023

Si applica a:SQL Server database SQL di Azure Istanza gestita di SQL di Azure Azure Synapse Analytics

Crittografa dati tramite una chiave simmetrica.

Nota

Questa sintassi non è supportata da pool SQL serverless in Azure Synapse Analytics.

Sintassi

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

Nota

Per visualizzare la sintassi Transact-SQL per SQL Server 2014 (12.x) e versioni precedenti, vedere la documentazione delle versioni precedenti.

Argomenti

key_GUID
GUID della chiave da usare 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 i dati da crittografare con la chiave.

add_authenticator
Indica se un autenticatore verrà crittografato insieme al testo non crittografato. Deve essere 1 se si usa un autenticatore. int.

@add_authenticator
Indica se un autenticatore verrà crittografato insieme al testo non crittografato. Deve essere 1 se si usa 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 un valore massimo di 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 o superiore.

Restituisce NULL se il valore cleartext è NULL.

Osservazioni:

EncryptByKey usa 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 disponga dell'autorizzazione ALTER per la tabella è 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 sono usate per verificare che i dati in formato testo normale non siano stati spostati.

Se per la crittografia dei dati è specificato un parametro di autenticazione, è necessario usare 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 usare 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'uso 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 altre informazioni sull'impostazione 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.

R. 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 AdventureWorks2022;  
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 AdventureWorks2022;  
  
-- 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

Vedi anche

DECRYPTBYKEY (Transact-SQL)
CREATE SYMMETRIC KEY (Transact-SQL)
ALTER SYMMETRIC KEY (Transact-SQL)
DROP SYMMETRIC KEY (Transact-SQL)
Gerarchia di crittografia
HASHBYTES (Transact-SQL)