컬럼 대칭키 암호화 작업 (SQL 2008)

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

2. ① 마스터 키 생성 1. 명령문 -- 마스터 키 생성 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

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

4. ③ 대칭키 생성 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항목을 지정하고 만들어야 추후 삭제 되었을때 대칭키를 다시 만들어도 이전 대칭키로 암호화 된 데이터를 복호화 할 수 있습니다. 생성 쿼리 꼭 보관해 주셔야 합니다.

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

6. ⑤ 암/복호화 1. 명령문 -- 테이블 생성 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')) GO --복호화 SELECT CONVERT(VARCHAR, DecryptbyKey(주민번호)) FROM 고객 GO 2. 암호화 컬럼 관련 -- varbinary(128) 암호화 컬럼을 128byte로 지정할경우입력문자열제한 1). ascii 1234567890123456789012345678901234567890123456789012345678901234567890 2). unicode 무궁화꽃이피었습니다무궁화꽃이피었습니다무궁화꽃이피었습니다무궁화꽃이

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

8. ※ 참고 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 주1)인증서의 유효기간에 관련하여 ( 본 참고자료 3페이지 1번 항목) 대칭키 생성 할 때 인증서 기반으로 생성했는데 만약 인증서의 유효기간이 지나면 대칭키 사용은 어떻게 될까요? 대칭키 생성 할 때만 인증서를 사용했기 때문에 인증서의 유효기간이 지났다고 해서 이미 생성된 대칭키 사용(암/복호화) 영향을 주지 않습니다. 만약, 대칭키가 잘못되어 다시 생성 할 때(대칭키는 백업/복구가 없음) 유효기간이 지난 인증서를 사용 할 수 없음으로 인증서 생성 쿼리에서 유효기간만 늘려서 다시 생성 하시면 됩니다. 인증서 DROP 할 때 이미 생성된 인증서를 사용한 대칭키가 있다면 먼저 대칭키를 DROP하고 나서 인증서를 DROP해야 합니다. 추가로 해보면 재미있을 만한 내용... 만약, 마스터키/인증서/ 대칭키 DROP 시키고 비밀번호 변경하여 재 생성 한 후 기존 데이터를 복호화 하면 어떻게 될까?

9. 인증서와 대칭키 생성 위치