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

DECRYPTBYKEY (Transact-SQL)

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

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

Соглашения о синтаксисе Transact-SQL

Примечание.

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

Синтаксис

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

Аргументы

ciphertext

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

@ciphertext

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

add_authenticator

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

authenticator

Данные, используемые в качестве основы для создания структуры проверки подлинности. Должен соответствовать значению, предоставленному ENCRYPTBYKEY. Authenticator — sysname.

@authenticator

Переменная, содержащая данные, из которых формируется структура проверки подлинности. Должен соответствовать значению, предоставленному ENCRYPTBYKEY. @authenticator — sysname.

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

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

Замечания

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

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

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

Разрешения

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

Примеры

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

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

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

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

В следующем примере показано, что 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 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

Связанный контент

  • Last updated on