[sql] 프로시저 호출 시 암호화/복호화 처리 방법

05 Dec 2023

데이터베이스에서 프로시저를 호출할 때, 암호화된 데이터를 사용하고 싶다면 어떻게 해야 할까요? 이 글에서는 SQL에서 프로시저 호출 시 암호화와 복호화를 처리하는 방법에 대해 알아보겠습니다.

1. 암호화된 데이터 저장

먼저, 암호화된 데이터를 저장하는 방법부터 알아보겠습니다. 데이터베이스에서는 대칭키 암호화와 공개키 암호화를 사용할 수 있습니다.

1.1. 대칭키 암호화

대칭키 암호화는 하나의 키로 암호화와 복호화를 모두 처리하는 방식입니다. 암호화된 데이터를 저장할 필드를 생성하고, 해당 필드에 암호화된 데이터를 저장합니다. 프로시저에서는 암호화된 데이터를 사용하기 전에 필요한 경우 복호화하여 사용할 수 있습니다.

예시:

-- 테이블 생성
CREATE TABLE users (
  id INT PRIMARY KEY,
  name VARCHAR(50),
  address VARBINARY(max)
)

-- 데이터 저장
INSERT INTO users (id, name, address)
VALUES (1, 'John Doe', ENCRYPTBYKEY(KEY_GUID('SymmetricKey001'), '1234 Main Street'))

-- 데이터 조회
SELECT id, name, CONVERT(VARCHAR(max), DECRYPTBYKEYAUTOASYMKEY(ASYMKEY_ID('AsymmetricKey001'), address)) AS address
FROM users

1.2. 공개키 암호화

공개키 암호화는 암호화와 복호화를 위해 서로 다른 키를 사용하는 방식입니다. 데이터를 암호화할 때는 공개키로 암호화하고, 필요한 경우 개인키를 사용하여 복호화할 수 있습니다.

예시:

-- 테이블 생성
CREATE TABLE users (
  id INT PRIMARY KEY,
  name VARCHAR(50),
  address VARBINARY(max)
)

-- 공개키 생성
CREATE ASYMMETRIC KEY AsymmetricKey001
WITH ALGORITHM = RSA_2048
ENCRYPTION BY PASSWORD = 'MyPassword'

-- 개인키를 사용하여 데이터 암호화
INSERT INTO users (id, name, address)
VALUES (1, 'John Doe', ENCRYPTBYASYMKEY(ASYMKEY_ID('AsymmetricKey001'), '1234 Main Street'))

-- 공개키로 데이터 복호화
SELECT id, name, CONVERT(VARCHAR(max), DECRYPTBYASYMKEY(ASYMKEY_ID('AsymmetricKey001'), address)) AS address
FROM users

2. 프로시저 호출 시 암호화/복호화

암호화된 데이터를 사용하는 프로시저를 만들 때는, 암호화된 데이터를 인자로 전달하고 필요한 경우 복호화하여 사용할 수 있습니다. 이를 위해 프로시저 내부에서 암호화된 데이터를 복호화하는 로직을 추가해야 합니다.

예시:

-- 암호화된 데이터를 사용하는 프로시저 생성
CREATE PROCEDURE GetUserInfoWithDecryption
  @userId INT
AS
BEGIN
  DECLARE @encryptedAddress VARBINARY(max)
  DECLARE @decryptedAddress VARCHAR(max)

  -- 암호화된 데이터 조회
  SELECT @encryptedAddress = address
  FROM users
  WHERE id = @userId

  -- 필요한 경우 데이터 복호화
  SET @decryptedAddress = CONVERT(VARCHAR(max), DECRYPTBYKEYAUTOASYMKEY(ASYMKEY_ID('AsymmetricKey001'), @encryptedAddress))

  -- 복호화된 데이터 사용
  SELECT id, name, @decryptedAddress AS address
  FROM users
  WHERE id = @userId
END

위의 예시에서는 GetUserInfoWithDecryption 프로시저를 생성하여, 암호화된 주소 데이터를 복호화하여 사용하고 있습니다.

3. 참고 자료

위에서 설명한 내용은 Microsoft SQL Server를 기준으로 작성된 것입니다. 다른 데이터베이스 시스템을 사용한다면 해당 시스템의 공식 문서나 참고 자료를 참고하는 것이 좋습니다.

Microsoft SQL Server 공식 문서: ENCRYPTBYKEY, ENCRYPTBYASYMKEY, DECRYPTBYKEYAUTOASYMKEY, DECRYPTBYKEY, DECRYPTBYASYMKEY