DECRYPTBYKEY (Transact-SQL)

项目
04/26/2023

适用于：SQL Server Azure SQL 数据库 Azure SQL 托管实例 Azure Synapse Analytics

此函数使用对称密钥解密数据。

注意

Azure Synapse Analytics 中的无服务器 SQL 池不支持此语法。

对于 Azure Synapse Analytics 中的专用 SQL 池，不应将结果集缓存与 DECRYPTBYKEY 结合使用。如果必须使用此加密函数，请确保在执行时（在会话级别或数据库级别）禁用结果集缓存。

语法

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

注意

若要查看 SQL Server 2014 (12.x) 及更早版本的 Transact-SQL 语法，请参阅早期版本文档。

参数

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（最大大小为 8,000 个字节）。如果用于数据加密的对称密钥未打开，或者如果 ciphertext 为 NULL，则 DECRYPTBYKEY 返回 NULL。

注解

DECRYPTBYKEY 使用对称密钥。该数据库必须已打开此对称密钥。 DECRYPTBYKEY 将允许同时打开多个密钥。在密文解密之前，不必立即打开密钥。

对称加密和解密的运行速度通常较快，并且对涉及大量数据的操作而言，运行良好。

DECRYPTBYKEY 调用必须在包含加密密钥的数据库上下文中发生。可通过从驻留在数据库中的视图、存储过程或函数等对象调用 DECRYPTBYKEY 来确保这一点。

权限

该对称密钥必须已经在当前会话中打开。有关详细信息，请参阅 OPEN SYMMETRIC KEY (Transact-SQL)。

示例

A. 使用对称密钥进行解密

此示例使用对称密钥解密已加密文本。

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

C. 不在具有密钥的数据库上下文中时，无法解密

以下示例展示了必须在包含密钥的数据库上下文中执行 DECRYPTBYKEY。 DECRYPTBYKEY 在主数据库中执行时，不会解密该行；结果为 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

另请参阅

ENCRYPTBYKEY (Transact-SQL)
CREATE SYMMETRIC KEY (Transact-SQL)
ALTER SYMMETRIC KEY (Transact-SQL)
DROP SYMMETRIC KEY (Transact-SQL)
加密层次结构
 选择加密算法