컬럼 대칭키 암호화 작업(SQL 2008) ① 마스터 키 생성 ② 인증서 생성 초기 한번만 실행 ③ 대칭키 생성 ④ 대칭키 OPEN ⑤ 암/복호화 암/복호화시 ④⑤⑥ 세트로 실행 ⑥ 대칭키 CLOSE

① 마스터 키 생성 1. 명령문 2. 기타 -- 마스터 키 생성 USE TESTDB CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Pwd_Create_Master' -- ⓐ 데이터베이스의 마스터키를 암호화하는데 사용되는 암호입니다 2. 기타 -- 마스터 키 백업 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 TESTDB.sys.symmetric_keys

② 인증서 생성 1. 명령문 2. 기타 -- 인증서초기생성 CREATE CERTIFICATE Cert_JUMIN -- 인증서이름 ENCRYPTION BY PASSWORD = 'Pwd_Create_Cert' -- ⓒ인증서비밀번호 WITH SUBJECT = '주민번호인증서', -- 인증서제목 EXPIRY_DATE = '12/31/2050' -- 인증서유효기간('mm/dd/yyyy') 2. 기타 -- 인증서조회 SELECT * FROM TESTDB.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' -- ⓓ파일에서 검색한 개인키의 암호를 해독하는데 필요한 암호를 지정합니다 );

③ 대칭키 생성 1. 명령문 CREATE SYMMETRIC KEY Sym_KEY -- 대칭키이름 WITH ALGORITHM = AES_128, -- 암호알골리즘 선택 IDENTITY_VALUE = 'Data encryption key 03/29/2010', -- 사용자 형식에 맞게 수정 KEY_SOURCE = 'ORGA WHOLE FOODS SYMMETRIC KEY‘ -- 사용자 형식에 맞게 수정 ENCRYPTION BY CERTIFICATE Cert_JUMIN -- 대칭키를 암호화하는데 사용되는 인증서 이름을 지정합니다 -- 대칭키조회 SELECT * FROM TESTDB.sys.symmetric_keys 2. 암호 알고리즘 DES | TRIPLE_DES | TRIPLE_DES_3KEY | RC2 | RC4 | RC4_128 | DESX | AES_128 | AES_192 | AES_256 3. 기타 대칭키는 백업과 복원이 없다. 생성시에 IDENTITY_VALUE, KEY_SOURCE 항목을 지정하고 만들어야 추후 삭제 되었을때 대칭키를 다시 만들어도 이전 대칭키로 암호화 된 데이터를 복호화 할 수 있다.

④ 대칭키 OPEN 1. 명령문 2. 기타 OPEN SYMMETRIC KEY Sym_KEY -- 대칭키이름 DECRYPTION BY CERTIFICATE Cert_JUMIN -- 인증서이름 WITH PASSWORD = 'Pwd_Create_Cert' -- ⓒ인증서비밀번호(대/소문자 같아야 함.) 2. 기타 (암/복호화 할 때 항상 먼저 열어야 함.) -- 조회 SELECT * FROM TESTDB.sys.symmetric_keys

⑤ 암/복호화 1. 명령문 2. 암호화 컬럼 관련 -- 테이블 생성 CREATE TABLE 고객( 고객번호 INT IDENTITY PRIMARY KEY, 고객이름 VARCHAR(20) NOT NULL, 주민번호 VARBINARY(128) NOT NULL -- 대칭키가Open되어있지 않으면 NULL값으로 대체된다. ) GO -- 암호화 INSERT INTO 고객(고객이름, 주민번호) VALUES('지현명', EncryptByKey(Key_GUID('JUMIN_KEY'), ‘7905219925487')) --복호화 SELECT CONVERT(VARCHAR, DecryptbyKey(주민번호)) FROM 고객 2. 암호화 컬럼 관련 -- varbinary(128) 암호화 컬럼을 128byte로 지정할 경우 입력문자열 제한 1). ascii 1234567890123456789012345678901234567890123456789012345678901234567890 2). unicode 무궁화꽃이피었습니다무궁화꽃이피었습니다무궁화꽃이피었습니다무궁화꽃이

⑥ 대칭키 CLOSE 1. 명령문 IF EXISTS (SELECT * FROM TESTDB.sys.openkeys WHERE KEY_NAME = 'Sym_KEY') BEGIN CLOSE SYMMETRIC KEY Sym_KEY PRINT 'CLOSE' END ELSE PRINT 'NOTHING' 2. 기타 - 해당 세션에서 여러개의 대칭키를 사용하고 이를 모두 닫아야 한다면 CLOSE ALL SYMMETRIC KEYS

※ 참고 http://msdn.microsoft.com/en-us/library/bb326115.aspx http://msdn.microsoft.com/ko-kr/library/bb326115.aspx http://msdn.microsoft.com/ko-kr/library/ms366281.aspx