DECRYPTBYKEY (Transact-SQL)

  • Articolo

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

Questa funzione usa una chiave simmetrica per decrittografare i dati.

Convenzioni di sintassi Transact-SQL

Nota

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

Per i pool SQL dedicati in Azure Synapse Analytics, la memorizzazione nella cache dei set di risultati non deve essere usata insieme a DECRYPTBYKEY. Se questa funzione di crittografia deve essere usata, assicurarsi di avere disabilitato la memorizzazione nella cache dei set di risultati (a livello di sessione o a livello di database) al momento dell'esecuzione.

Sintassi

  
DecryptByKey ( { 'ciphertext' | @ciphertext }   
    [ , 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

ciphertext
Variabile di tipo varbinary contenente dati crittografati con la chiave.

@ciphertext
Variabile di tipo varbinary contenente dati crittografati con la chiave.

add_authenticator
Indica se il processo di crittografia originale includeva e crittografava un autenticatore insieme al testo non crittografato. Deve corrispondere al valore passato a ENCRYPTBYKEY (Transact-SQL) durante il processo di crittografia dei dati. add_authenticator ha un tipo di dati int.

authenticator
Dati usati come base per la generazione dell'autenticatore. Deve corrispondere al valore specificato per ENCRYPTBYKEY (Transact-SQL). authenticator ha un tipo di dati sysname.

@authenticator
Variabile contenente i dati dai quali derivare un autenticatore. Deve corrispondere al valore specificato per ENCRYPTBYKEY (Transact-SQL). @authenticator ha un tipo di dati sysname.

Tipi restituiti

varbinary con un valore massimo di 8.000 byte. DECRYPTBYKEY restituisce NULL se la chiave simmetrica usata per crittografare i dati non è aperta o se ciphertext è NULL.

Osservazioni:

DECRYPTBYKEY usa una chiave simmetrica. Il database deve avere la chiave simmetrica già aperta. DECRYPTBYKEY consente di avere più chiavi aperte contemporaneamente. Non è necessario aprire la chiave subito prima di decrittografare il testo crittografato.

La crittografia e la decrittografia simmetriche in genere operano in modo relativamente rapido e funzionano anche per le operazioni che includono volumi di dati di grandi dimensioni.

La chiamata a DECRYPTBYKEY deve essere eseguita nel contesto del database contenente la chiave di crittografia. Assicurarsi che ciò avvenga chiamando DECRYPTBYKEY da un oggetto (ad esempio una vista, una stored procedure o una funzione) che si trovi nel database.

Autorizzazioni

La chiave simmetrica deve essere già aperta nella sessione corrente. Per altre informazioni, vedere OPEN SYMMETRIC KEY (Transact-SQL).

Esempi

R. Decrittografia di dati tramite una chiave simmetrica

In questo esempio viene decrittografato il testo crittografato con una chiave simmetrica.

-- 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, EncryptedNationalID   
    AS 'Encrypted ID Number',  
    CONVERT(nvarchar, DecryptByKey(EncryptedNationalID))   
    AS 'Decrypted ID Number'  
    FROM HumanResources.Employee;  
GO

B. Decrittografia di dati tramite una chiave simmetrica e un hash di autenticazione

In questo esempio vengono decrittografati i dati originariamente crittografati insieme a un autenticatore.

-- 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('SHA1', CONVERT(varbinary, CreditCardID))))   
    AS 'Decrypted card number' FROM Sales.CreditCard;  
GO

C. Non è possibile eseguire la decrittografia al di fuori del contesto del database con la chiave

L'esempio seguente illustra che è necessario eseguire DECRYPTBYKEY nel contesto del database che contiene la chiave. La riga non verrà decrittografata quando si esegue DECRYPTBYKEY nel database master; il risultato sarà NULL.

-- Create the database
CREATE DATABASE TestingDecryptByKey
GO

USE [TestingDecryptByKey]

-- Create the table and view
CREATE TABLE TestingDecryptByKey.dbo.Test(val VARBINARY(8000) NOT NULL);
GO
CREATE VIEW dbo.TestView AS SELECT CAST(DecryptByKey(val) AS VARCHAR(30)) AS DecryptedVal FROM TestingDecryptByKey.dbo.Test;
GO

-- Create the key, and certificate
USE TestingDecryptByKey;
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'ItIsreallyLong1AndSecured!Passsword#';
CREATE CERTIFICATE TestEncryptionCertificate WITH SUBJECT = 'TestEncryption';
CREATE SYMMETRIC KEY TestEncryptSymmmetricKey WITH ALGORITHM = AES_256, IDENTITY_VALUE = 'It is place for test',
KEY_SOURCE = 'It is source for test' ENCRYPTION BY CERTIFICATE TestEncryptionCertificate;

-- Insert rows into the table
DECLARE @var VARBINARY(8000), @Val VARCHAR(30);
SELECT @Val = '000-123-4567';
OPEN SYMMETRIC KEY TestEncryptSymmmetricKey DECRYPTION BY CERTIFICATE TestEncryptionCertificate;
SELECT @var = EncryptByKey(Key_GUID('TestEncryptSymmmetricKey'), @Val);
SELECT CAST(DecryptByKey(@var) AS VARCHAR(30)), @Val;
INSERT INTO dbo.Test VALUES(@var);
GO

-- Switch to master
USE [Master];
GO

-- Results show the date inserted
SELECT DecryptedVal FROM TestingDecryptByKey.dbo.TestView;

-- Results are NULL because we are not in the context of the TestingDecryptByKey Database
SELECT CAST(DecryptByKey(val) AS VARCHAR(30)) AS DecryptedVal FROM TestingDecryptByKey.dbo.Test;
GO

-- Clean up resources
USE TestingDecryptByKey;

DROP SYMMETRIC KEY TestEncryptSymmmetricKey REMOVE PROVIDER KEY;
DROP CERTIFICATE TestEncryptionCertificate;

Use [Master]
DROP DATABASE TestingDecryptByKey;
GO

Vedi anche

ENCRYPTBYKEY (Transact-SQL)
CREATE SYMMETRIC KEY (Transact-SQL)
ALTER SYMMETRIC KEY (Transact-SQL)
DROP SYMMETRIC KEY (Transact-SQL)
Gerarchia di crittografia
Scelta di un algoritmo di crittografia