컬럼 대칭키 암호화 작업(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') , 주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' -- ⓓ파일에서 검색한 개인키의 암호를 해독하는데 필요한 암호를 지정합니다 );

③ 대칭키 생성 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. 명령문 IF EXISTS (SELECT * FROM ORGA.sys.openkeys WHERE KEY_NAME = 'Sym_KEY') BEGIN PRINT 'ALREADY OPEN' END ELSE OPEN SYMMETRIC KEY Sym_KEY -- 대칭키이름 DECRYPTION BY CERTIFICATE Cert_ORGA_JUMIN -- 인증서이름 WITH PASSWORD = 'Pwd_Create_Cert ' -- ⓒ인증서비밀번호(대/소분자 같아야 함.) PRINT 'OPEN' 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 ‘ALREADY CLOSE' 2. 기타 - 해당 세션에서 여러개의 대칭키를 사용하고 이를 모두 닫아야 한다면 CLOSE ALL SYMMETRIC KEYS

주1) 인증서의 유효기간에 관련하여 ( 본 참고자료 3페이지 1번 항목) ※ 참고 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 시키고 비밀번호 변경하여 재 생성 한 후 기존 데이터를 복호화 하면 어떻게 될까?

인증서와 대칭키 생성 위치