Crittografia di una colonna di dati
Si applica a:SQL ServerDatabase SQL di AzureIstanza gestita di SQL di AzureAzure Synapse Analytics
Questo articolo descrive come crittografare una colonna di dati tramite la crittografia simmetrica in SQL Server con Transact-SQL. È chiamata a volte crittografia a livello di colonna o crittografia a livello di cella.
Gli esempi di codice Transact-SQL in questo articolo usano il AdventureWorks2022
database di esempio o AdventureWorksDW2022
, che è possibile scaricare dalla home page degli esempi di Microsoft SQL Server e dei progetti della community.
Sicurezza
Autorizzazioni
Le autorizzazioni seguenti sono necessarie per eseguire i passaggi riportati di seguito:
- Autorizzazione
CONTROL
per il database. - Autorizzazione
CREATE CERTIFICATE
per il database. Solo gli account di accesso di Windows e di SQL Server e i ruoli applicazione possono disporre di certificati. I gruppi e i ruoli non possono disporre di certificati. - Autorizzazione
ALTER
per la tabella. - È necessario disporre di un'autorizzazione per la chiave e che non venga negata l'autorizzazione
VIEW DEFINITION
.
Creare la chiave master del database
Per usare gli esempi seguenti è necessaria una chiave master di database. Se il database non dispone già di una chiave master del database, crearne una. A tal fine, connettersi al database ed eseguire lo script seguente. Assicurarsi di usare una password complessa.
Copiare e incollare l'esempio riportato di seguito nell'intervallo di query connesso al database di esempio AdventureWorks
. Seleziona Execute.
CREATE MASTER KEY ENCRYPTION BY
PASSWORD = '<complex password>';
Creare sempre una copia di backup della chiave master del database. Per ulteriori informazioni sulle chiavi master del database, vedere CREATE MASTER KEY (Transact-SQL).
Esempio: Crittografare con la crittografia simmetrica e l'autenticatore
In Esplora oggetti connettersi a un'istanza del motore di database.
Sulla barra Standard selezionare Nuova query.
Copiare e incollare l'esempio riportato di seguito nell'intervallo di query connesso al database di esempio
AdventureWorks
. Seleziona Execute.CREATE CERTIFICATE Sales09 WITH SUBJECT = 'Customer Credit Card Numbers'; GO CREATE SYMMETRIC KEY CreditCards_Key11 WITH ALGORITHM = AES_256 ENCRYPTION BY CERTIFICATE Sales09; GO -- Create a column in which to store the encrypted data. ALTER TABLE Sales.CreditCard ADD CardNumber_Encrypted varbinary(160); 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 using the -- symmetric key CreditCards_Key11. -- Save the result in column CardNumber_Encrypted. UPDATE Sales.CreditCard SET CardNumber_Encrypted = EncryptByKey(Key_GUID('CreditCards_Key11') , CardNumber, 1, HASHBYTES('SHA2_256', CONVERT( varbinary , CreditCardID))); GO -- Verify the encryption. -- First, open the symmetric key with which to decrypt the data. OPEN SYMMETRIC KEY CreditCards_Key11 DECRYPTION BY CERTIFICATE Sales09; GO -- Now list the original card number, the encrypted card number, -- and the decrypted ciphertext. If the decryption worked, -- the original number will match the decrypted number. SELECT CardNumber, CardNumber_Encrypted AS 'Encrypted card number', CONVERT(nvarchar, DecryptByKey(CardNumber_Encrypted, 1 , HASHBYTES('SHA2_256', CONVERT(varbinary, CreditCardID)))) AS 'Decrypted card number' FROM Sales.CreditCard; GO
Crittografare con la crittografica simmetrica semplice
In Esplora oggetti connettersi a un'istanza del motore di database.
Sulla barra Standard selezionare Nuova query.
Copiare e incollare l'esempio riportato di seguito nell'intervallo di query connesso al database di esempio
AdventureWorks
. Seleziona Execute.CREATE CERTIFICATE HumanResources037 WITH SUBJECT = 'Employee Social Security Numbers'; GO CREATE SYMMETRIC KEY SSN_Key_01 WITH ALGORITHM = AES_256 ENCRYPTION BY CERTIFICATE HumanResources037; GO 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 -- Verify the encryption. -- First, open the symmetric key with which to decrypt the data. OPEN SYMMETRIC KEY SSN_Key_01 DECRYPTION BY CERTIFICATE HumanResources037; GO -- Now list the original ID, the encrypted ID, and the -- decrypted ciphertext. If the decryption worked, the original -- and the decrypted ID will match. SELECT NationalIDNumber, EncryptedNationalIDNumber AS 'Encrypted ID Number', CONVERT(nvarchar, DecryptByKey(EncryptedNationalIDNumber)) AS 'Decrypted ID Number' FROM HumanResources.Employee; GO
Passaggi successivi
Commenti e suggerimenti
https://aka.ms/ContentUserFeedback.
Presto disponibile: Nel corso del 2024 verranno gradualmente disattivati i problemi di GitHub come meccanismo di feedback per il contenuto e ciò verrà sostituito con un nuovo sistema di feedback. Per altre informazioni, vedereInvia e visualizza il feedback per