Encrypt specific Table.column

주민등록번호의 저장을 지양하고 있고, 저장을 하더라도 암호화 하도록 권고하고 있습니다.

그럼에도 불구하고 legacy system에서 주민등록번호가 반드시 필요한 경우가 있기 때문에... 주민등록번호를 저장해야 하는 경우가 많습니다... (어쩌라고...)

그 외에도 민감한 정보를 감추기 위해서 Db에 값을 저장할 때, 암호화 하여 저장해야 할 필요가 있습니다.

Db자체적으로 암호화를 하거나, Code에서 할 수 있는데,

Db에서 처리함으로써 취할 수 있는 장점은 다음과 같습니다.

1.     batch 처리 시에 business logic을 경유할 필요가 없음. (DB2DB 작업의 단순화)

2.     복수의 Application에서 암호/복호화 로직을 적용할 필요가 없음.

다만, ARIA, SEED 알고리즘의 적용은 기본적으로 지원하지 않습니다.

 

적용 방법

1.     Master key 생성 // 대칭키 설정

2.     Certificate 생성

3.     Symmetric 생성 // 알고리즘 설정

4.     데이터 CRU 간 Symmetric 적용

 

데이터 등록/갱신

-- Open the symmetric key with which to encrypt the data.

OPEN SYMMETRIC KEY {SymmetricName}

  DECRYPTION BY CERTIFICATE {CertificateName};

 

-- key {SymmetricName}. Save the result in column {EncColumn_Encrypted}.

UPDATE {TargetTable}

SET {EncColumn_Encrypted} = EncryptByKey(Key_GUID('{SymmetricName}'), {EncColumn});

 

데이터 조회

-- First, open the symmetric key with which to decrypt the data.

OPEN SYMMETRIC KEY {SymmetricName}

   DECRYPTION BY CERTIFICATE {CertificateName};

GO

 

SELECT {EncColumn}

        , {EncColumn_Encrypted}

        , CONVERT(nvarchar, DecryptByKey({EncColumn_Encrypted}))

FROM {TargetTable};

 

Object explorer를 통해서 certificate / symmetric을 확인할 수 있습니다.

SQL

괄호로 묶은 부분을 치환하여 실행하면 됩니다.

/* // setting info Master key password: {MasterkeyPassword} ex, 23987hxJKL95QYV4369#ghf0%lekjg5k3fd117r$$#1946kcj$n44ncjhdlj Certificate name: {CertificateName} ex, SSN Certificate subject: {CertificateSubject} ex, 'Social Security Number' Symmetric name: {SymmetricName} ex, SSN_Key Symmetric algorithm: {SymmetricAlgorithm} ex, DES, TRIPLE_DES, RC2, RC4, RC4_128, DESX, AES_128, AES_192, and AES_256. // table info Target table: {TargetTable} ex, User Target encrypt column: {EncColumn} ex, [Password] EncryptedColumn: {EncColumn_Encrypted} ex, [Password_Encrypted] EncryptedColumnType: {EncColumnType_Encrypted} ex, [varbinary(128)] */ --If there is no master key, create one now. IF NOT EXISTS (SELECT * FROM sys.symmetric_keys WHERE symmetric_key_id = 101) CREATE MASTER KEY ENCRYPTION BY PASSWORD = '{MasterkeyPassword}' GO CREATE CERTIFICATE {CertificateName} WITH SUBJECT = '{CertificateSubject}'; GO CREATE SYMMETRIC KEY {SymmetricName} WITH ALGORITHM = {SymmetricAlgorithm} ENCRYPTION BY CERTIFICATE {CertificateName}; GO -- Create a column in which to store the encrypted data. ALTER TABLE {TargetTable} ADD {EncColumn_Encrypted} {EncColumnType_Encrypted}; GO -- !! Update sequence -- Open the symmetric key with which to encrypt the data. OPEN SYMMETRIC KEY {SymmetricName} DECRYPTION BY CERTIFICATE {CertificateName}; -- Encrypt the value in column {EncColumn} with symmetric -- key {SymmetricName}. Save the result in column {EncColumn_Encrypted}. UPDATE {TargetTable} SET {EncColumn_Encrypted} = EncryptByKey(Key_GUID('{SymmetricName}'), {EncColumn}); GO -- !! Verify sequence -- Verify the encryption. -- First, open the symmetric key with which to decrypt the data. OPEN SYMMETRIC KEY {SymmetricName} DECRYPTION BY CERTIFICATE {CertificateName}; GO -- Now list the original value, the encrypted value, -- and the decrypted ciphertext. If the decryption worked, -- the original value will match the decrypted number. SELECT {EncColumn} , {EncColumn_Encrypted} , CONVERT(nvarchar, DecryptByKey({EncColumn_Encrypted})) FROM {TargetTable}; GO --CLOSE SYMMETRIC KEY {SymmetricName}

Entity framework를 사용하는 경우

SQL 구문에서 보이 듯 CRU 작업 전에 symmetric을 열어서 사용해야 합니다.

E.F. 사용하는 경우에는 EntityCommand를 통해서 이를 처리할 수 있으며, 자세한 내용은 References의 마지막 항목을 체크하시기 바랍니다.

References

http://msdn.microsoft.com/ko-kr/library/ms179331.aspx

http://blog.sqlauthority.com/2009/04/28/sql-server-introduction-to-sql-server-encryption-and-symmetric-key-encryption-tutorial-with-script/

https://social.msdn.microsoft.com/Forums/en-US/b182d848-3597-44cd-8943-13da6e6961cc/execute-custom-command-before-executing-any-command-on-entity-framework?forum=adodotnetentityframework