DECRYPTBYKEY (Transact-SQL)

Статья
04/26/2023

Область применения: SQL Server База данных SQL Azure Управляемый экземпляр SQL Azure Azure Synapse Analytics

Эта функция расшифровывает данные с помощью симметричного ключа.

Примечание.

Этот синтаксис не поддерживается бессерверным пулом SQL в Azure Synapse Analytics.

Для выделенных пулов SQL в Azure Synapse Analytics кэширование результирующих наборов не следует использовать в сочетании с DECRYPTBYKEY. Если эту криптографическую функцию все же нужно использовать, убедитесь, что кэширование результирующих наборов отключено (на уровне сеанса или уровне базы данных) на момент выполнения.

Синтаксис

  
DecryptByKey ( { 'ciphertext' | @ciphertext }   
    [ , add_authenticator, { authenticator | @authenticator } ] )

Аргументы

ciphertext
Переменная типа varbinary, содержащая данные, зашифрованные с помощью ключа.

@ciphertext
Переменная типа varbinary, содержащая данные, зашифрованные с помощью ключа.

add_authenticator
Указывает, включен ли исходный процесс шифрования и зашифрована ли структура проверки подлинности вместе с данными в виде открытого текста. Значение должно соответствовать значению, переданному функции ENCRYPTBYKEY (Transact-SQL) во время шифрования данных. add_authenticator имеет тип данных int.

authenticator
Данные, используемые в качестве основы для создания структуры проверки подлинности. Значение должно соответствовать значению, заданному функции ENCRYPTBYKEY (Transact-SQL). authenticator имеет тип данных sysname.

@authenticator
Переменная, содержащая данные, из которых формируется структура проверки подлинности. Значение должно соответствовать значению, заданному функции ENCRYPTBYKEY (Transact-SQL). authenticator имеет тип данных sysname.

Типы возвращаемых данных

Переменная типа varbinary с максимальным размером 8000 байт. DECRYPTBYKEY возвращает NULL, если симметричный ключ, используемый для шифрования данных, не является открытым или если ciphertext имеет значение NULL.

Замечания

DECRYPTBYKEY использует симметричный ключ. Этот ключ уже должен быть открыт в базе данных. DECRYPTBYKEY допускает несколько одновременно открытых ключей. Открывать ключ непосредственно перед расшифровкой зашифрованного текста необязательно.

Симметричные шифрование и расшифровка обычно выполняются относительно быстро и хорошо подходят для операций, связанных с большими объемами данных.

Вызов DECRYPTBYKEY должен быть выполнен в контексте базы данных, содержащей ключ шифрования. Это можно гарантировать путем вызова DECRYPTBYKEY из объекта (например, представления, хранимой процедуры или функции), который находится в этой базе данных.

Разрешения

Симметричный ключ уже должен быть открыт в текущем сеансе. Дополнительные сведения см. в статье OPEN SYMMETRIC KEY (Transact-SQL).

Примеры

А. Декодирование с помощью симметричного ключа

В этом примере зашифрованный текст расшифровывается с помощью симметричного ключа.

-- 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. Декодирование с помощью симметричного ключа и цифровой подписи

В этом примере расшифровываются данные, изначально зашифрованные вместе со структурой проверки подлинности.

-- 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

В. Ошибка расшифровки не в контексте базы данных с помощью ключа

В следующем примере показано, что DECRYPTBYKEY должна выполняться в контексте базы данных, которая содержит ключ. Строку не удастся расшифровать, если DECRYPTBYKEY выполняется в базе данных Master; результатом является значение 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 VARBINARY(8000), @Val 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

См. также

ENCRYPTBYKEY (Transact-SQL)
CREATE SYMMETRIC KEY (Transact-SQL)
ALTER SYMMETRIC KEY (Transact-SQL)
DROP SYMMETRIC KEY (Transact-SQL)
Иерархия средств шифрования
Выбор алгоритма шифрования

Поделиться через