MS-SQL 2005이상에서 사용이 가능한 데이타 암호화 복호화 방법입니다.
요즘 워낙 암호화가 많이 강조가 되다 보니 activeX형태로 나온것도 많고 다양한 소스들도 많이 나와 있습니다
그러나 DB자체적으로 암호화 복호화가 가능하다는것을 아는 사람은 그렇게 많지 않은것 같습니다
그래서 정리를 해봤습니다
AES 128비트 암호화 방법입니다
그외 방법들도 있지만 나머지는 공부하시면서 찾으시면 쉽게 찾을수 있을것입니다...
------------------------------------------------------------------------
--1.마스터 키 생성
------------------------------------------------------------------------
Create Master Key Encryption By PassWord = 'Pwd_Create_Master' -- ⓐ 데이터베이스의 마스터키를 암호화하는데 사용되는 암호
-- 마스터 키 백업
Open Master Key Decryption By PassWord = 'Pwd_Create_Master';
BackUp Master Key To File = 'C:\MasterKey' -- 생성된 파일을 잘 보관해 두어야 한다.(★)
Encryption By PassWord = 'Pwd_Backup_Master' -- ⓑ 파일의 마스터키를 암호화하는데 사용되는 암호입니다.
-- 마스터 키 삭제
Drop Master Key
-- 마스터 키 복원
Restore Master Key From File = 'C:\MasterKey' -- 백업해 두었던 마스터 키 파일
Decryption By PassWord = 'Pwd_Backup_Master' -- ⓑ
Encryption By PassWord = 'Pwd_Create_Master' -- ⓐ
-- 마스터키조회
Select * From sys.symmetric_keys
------------------------------------------------------------------------
--2.인증서 생성
------------------------------------------------------------------------
-- 인증서초기생성
Create Certificate Cert_JUMIN -- 인증서이름
Encryption By PassWord = 'Pwd_Create_Cert' -- ⓒ인증서비밀번호
With Subject = '주민번호인증서', -- 인증서제목
Start_Date = '01/01/2011', --시작일
Expiry_Date = '12/31/2050' -- 인증서유효기간('mm/dd/yyyy')
-- Drop Certificate Cert_JUMIN -- 인증서이름
-- 인증서조회
Select * From sys.certificates
-- 인증서백업
BackUp Certificate Cert_JUMIN To File = 'C:\CERT' -- 인증서, 생성된 파일을 잘 보관해 두어야 한다.(★)
With Private Key (
File = 'C:\PRI' , -- 개인키, 생성된 파일을 잘 보관해 두어야 한다.(★)
Encryption By PassWord = 'Pwd_Backup_Cert', -- ⓓ키를 백업파일에 작성하기 전에 개인키를 암호화하는데 사용되는 암호
Decryption By PassWord = 'Pwd_Create_Cert' -- ⓒ키를 백업하기전에 개인키의 암호를 해독하는데 사용되는 암호입니다
)
-- 인증서삭제
Drop Certificate Cert_JUMIN
-- 인증서복구
Create Certificate Cert_JUMIN
From File = 'C:\CERT'
With Private Key (
File = 'C:\PRI',
Encryption By PassWord = 'Pwd_Create_Cert', -- ⓒ개인키를 암호화 하는데 사용되는 암호를 지정합니다
Decryption By PassWord = 'Pwd_Backup_Cert' -- ⓓ파일에서 검색한 개인키의 암호를 해독하는데 필요한 암호를 지정합니다
);
------------------------------------------------------------------------
--3. 대칭키 생성
------------------------------------------------------------------------
Create Symmetric Key Sym_KEY -- 대칭키이름
With ALGORITHM = AES_128, -- 암호알골리즘 선택
Identity_Value = 'Data encryption key 2012-08-28', -- 사용자 형식에 맞게 수정
Key_Source = '예술경영 SYMMETRIC KEY' -- 사용자 형식에 맞게 수정
Encryption By Certificate Cert_JUMIN -- 대칭키를 암호화하는데 사용되는 인증서 이름을 지정합니다
--마스트키와 인증서를 복구한 후 대칭키가 삭제되었을시에는
--이전 생성된 대칭키와 동일하게 다시 생성하면 된다
--이때 ALGORITHM,Identity_Value,Key_Source 가 반드시 존재해야 하며 값은 대소문자를 구분한다
-- 대칭키조회
Select * From sys.symmetric_keys
drop symmetric key Sym_KEY
------------------------------------------------------------------------
--4. 대칭키 오픈
------------------------------------------------------------------------
If Exists (Select * From sys.openkeys Where Key_Name = 'Sym_KEY')
Begin
Print 'Already Open'
End
ELSE
Begin
Open Symmetric Key Sym_KEY -- 대칭키이름
Decryption By Certificate Cert_JUMIN -- 인증서이름
With PassWord = 'Pwd_Create_Cert' -- ⓒ인증서비밀번호(대/소분자 같아야 함., 한개일때는 없어도 됨)
Print 'Open'
End
--암/복호화 할 때 항상 먼저 열어야 함
Select * From sys.symmetric_keys
------------------------------------------------------------------------
--5. 암/복호화
------------------------------------------------------------------------
-- 테이블 생성
Create Table 고객( 고객번호 Int Identity Primary Key, 고객이름 VarBinary(128) Not Null,
주민번호 VarBinary(128) Not Null ) -- 대칭키가Open되어있지 않으면 NULL값으로 대체된다.
GO
-- 암호화
Insert Into 고객(고객이름, 주민번호) Values(EncryptByKey(Key_Guid('Sym_KEY'), '가치노을'), EncryptByKey(Key_Guid('Sym_KEY'), '9912311234567'))
GO
--복호화
Select Convert(VarChar, DecryptbyKey(주민번호)), * From 고객
Select * From 고객
GO
------------------------------------------------------------------------
--6. 대칭키 Close
------------------------------------------------------------------------
If Exists (Select * From sys.openkeys Where Key_Name = 'Sym_KEY')
Begin
Close Symmetric Key Sym_KEY
Print 'Close'
End
Else
Begin
Print 'Already Close'
End
------------------------------------------------------------------------
--7. 해당 세션에서 여러개의 대칭키를 사용하고 이를 모두 닫아야 한다면
------------------------------------------------------------------------
Close All Symmetric Keys
------------------------------------------------------------------------
