다음을 통해 공유

DECRYPTBYKEY(Transact-SQL)

적용 대상:SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceAzure Synapse AnalyticsMicrosoft Fabric의 SQL 데이터베이스

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

Transact-SQL 구문 표기 규칙

참고

이 구문은 Azure Synapse Analytics의 서버리스 SQL 풀에서 지원되지 않습니다. Azure Synapse Analytics의 전용 SQL 풀의 경우 결과 집합 캐싱을 함께 사용하면 DECRYPTBYKEY안 됩니다. 이 암호화 함수를 사용해야 하는 경우 실행 시 결과 집합 캐싱을 사용하지 않도록 설정(세션 수준 또는 데이터베이스 수준에서)해야 합니다.

구문

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

인수

ciphertext

키로 암호화된 데이터를 포함하는 varbinary 형식의 변수입니다.

@ciphertext

키로 암호화된 데이터를 포함하는 varbinary 형식의 변수입니다.

add_authenticator

원래 암호화 프로세스가 포함되고 암호화된 인증자가 일반 텍스트를 사용하는지 여부를 나타냅니다. 데이터 암호화 프로세스 중에 ENCRYPTBYKEY 에 전달된 값과 일치해야 합니다. add_authenticator는 int 데이터 형식을 갖습니다.

authenticator

인증자의 생성에 대한 기준으로 사용되는 데이터입니다. ENCRYPTBYKEY에 제공된 값과 일치해야 합니다. authenticator 는 sysname입니다.

@authenticator

인증자가 생성하는 데이터를 포함하는 변수입니다. ENCRYPTBYKEY에 제공된 값과 일치해야 합니다. @authenticatorsysname입니다.

반환 형식

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

설명

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

대칭 암호화 및 암호 해독은 일반적으로 빠르게 작동하며 대용량 데이터 볼륨과 관련된 작업에 적합합니다.

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

사용 권한

대칭 키는 현재 세션에서 이미 열려 있어야 합니다. 자세한 내용은 OPEN SYMMETRIC KEY를 참조하세요.

예제

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;

C. 키가 있는 데이터베이스의 컨텍스트에 없을 때 암호 해독 실패

다음 예제에서는 키를 포함하는 데이터베이스의 컨텍스트에서 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