DECRYPTBYKEY(Transact-SQL)

아티클
10/16/2024

적용 대상: SQL Server Azure SQL 데이터베이스 Azure SQL Managed Instance Azure Synapse Analytics

이 함수는 대칭 키를 사용하여 데이터의 암호를 해독합니다.

참고

이 구문은 Azure Synapse Analytics의 서버리스 SQL 풀에서 지원되지 않습니다.

Azure Synapse Analytics의 전용 SQL 풀의 경우 결과 집합 캐싱을 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 데이터 형식을 갖습니다.

반환 형식

최대 크기가 8,000바이트인 varbinary입니다. DECRYPTBYKEY는 데이터 암호화에 사용되는 대칭 키가 열려 있지 않거나 ciphertext가 NULL이면 NULL을 반환합니다.

설명

DECRYPTBYKEY는 대칭 키를 사용합니다. 데이터베이스는 이 대칭 키를 이미 열어 두어야 합니다. DECRYPTBYKEY는 동시에 여러 개의 키를 열어 둘 수 있습니다. 암호 텍스트를 해독하기 직전에 키를 열 필요는 없습니다.

대칭 암호화 및 암호 해독은 일반적으로 상대적으로 신속하게 작동하고 큰 데이터 볼륨과 관련된 작업에 적합합니다.

DECRYPTBYKEY 호출은 암호화 키를 포함하는 데이터베이스의 컨텍스트에서 발생해야 합니다. 이를 위해 데이터베이스에 상주하는 개체(예: 보기, 저장 프로시저, 함수)에서 DECRYPTBYKEY를 호출합니다.

사용 권한

대칭 키는 현재 세션에서 이미 열려 있어야 합니다. 자세한 내용은 대칭 키 열기(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!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

다음을 통해 공유