DECRYPTBYKEY (Transact-SQL)

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

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

Transact-SQL 语法约定

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

此示例解密最初使用验证器加密的数据。

-- 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 在主数据库中执行时，不会解密该行；结果为 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)
加密层次结构
选择加密算法