Condividi tramite

DECRYPTBYKEY (Transact-SQL)

Si applica a:SQL ServerDatabase SQL di AzureIstanza gestita di SQL di AzureAzure Synapse AnalyticsDatabase SQL in Microsoft Fabric

Questa funzione usa una chiave simmetrica per decrittografare i dati.

Convenzioni relative alla 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 con 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 } ] )

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 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 fornito a ENCRYPTBYKEY. authenticator è sysname.

@authenticator

Variabile contenente i dati dai quali derivare un autenticatore. Deve corrispondere al valore fornito a ENCRYPTBYKEY. @authenticator è sysname.

Tipi restituiti

varbinary con un valore massimo di 8.000 byte. DECRYPTBYKEY restituisce NULL se la chiave simmetrica usata per la crittografia dei dati non è aperta o se il testo crittografato è NULL.

Osservazioni:

DECRYPTBYKEY usa una chiave simmetrica. Il database deve avere la chiave simmetrica già aperta. DECRYPTBYKEY consente l'apertura di più chiavi contemporaneamente. Non è necessario aprire la chiave immediatamente prima della decrittografia del testo crittografato.

La crittografia simmetrica e la decrittografia funzionano in genere rapidamente e funzionano bene per le operazioni che coinvolgono 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.

Esempi

R. Decrittografare usando 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. Decrittografare usando 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;

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 viene decrittografata quando DECRYPTBYKEY viene eseguita nel master database. Il risultato è 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!Password#';

CREATE CERTIFICATE TestEncryptionCertificate
    WITH SUBJECT = 'TestEncryption';

CREATE SYMMETRIC KEY TestEncryptSymmetricKey
    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 AS VARBINARY (8000), @Val AS VARCHAR (30);
SELECT @Val = '000-123-4567';

OPEN SYMMETRIC KEY TestEncryptSymmetricKey DECRYPTION BY CERTIFICATE TestEncryptionCertificate;

SELECT @var = EncryptByKey(Key_GUID('TestEncryptSymmetricKey'), @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 TestEncryptSymmetricKey REMOVE PROVIDER KEY;
DROP CERTIFICATE TestEncryptionCertificate;

USE [master];

DROP DATABASE TestingDecryptByKey;
GO

Contenuti correlati

  • Last updated on