SQL Server 컬럼 암호화 방법 step by step

이번 글에서는 SQL Server에서 컬럼 암호화를 하기 위해 테스트한 전체 쿼리입니다. 아래 내용 중 일부 내용을 수정하여 적용할 수 있습니다.

 

sql server database encryption

/*
마스터키
*/
-- 마스터키생성
USE TESTDB;
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '암호1'	-- ⓐ데이터베이스의마스터키를암호화하는데사용되는암호입니다

-- 마스터키백업
USE TESTDB;
OPEN MASTER KEY DECRYPTION BY PASSWORD = '암호1';
BACKUP MASTER KEY TO FILE = 'C:\ExportedMasterKey' 	-- 생성된파일을잘보관해두어야한다.
	ENCRYPTION BY PASSWORD = '암호2' 	-- ⓑ파일의마스터키를암호화하는데사용되는암호입니다. 
GO

-- 마스터키삭제
DROP MASTER KEY 

-- 마스터키복원
RESTORE MASTER KEY FROM FILE = 'C:\ExportedMasterKey' 	-- 백업해두었던마스터키파일
DECRYPTION BY PASSWORD = '암호2'  	-- ⓑ
ENCRYPTION BY PASSWORD = '암호1'   	-- ⓐ

-- 마스터키조회
SELECT * FROM TESTDB.sys.symmetric_keys 


/*
인증서
*/
-- 인증서초기생성
USE TESTDB;
CREATE CERTIFICATE Cert_HAN			-- 인증서이름
	ENCRYPTION BY PASSWORD = '암호3' 	-- ⓒ인증서비밀번호
	WITH SUBJECT = '인증서_한',		-- 인증서제목
	EXPIRY_DATE = '20501231'		-- 인증서유효기간('mm/dd/yyyy', 'yyyymmdd')

-- 인증서조회
SELECT * FROM TESTDB.sys.certificates 

-- 인증서백업
BACKUP CERTIFICATE Cert_HAN TO FILE = 'C:\Cert_HAN' 	-- 인증서, 생성된파일을잘보관
GO
    
-- 인증서삭제 방법
DROP CERTIFICATE Cert_HAN


-- 인증서복구
CREATE CERTIFICATE Cert_HAN FROM FILE = 'C:\Cert_HAN' 
GO
	
/*
대칭키
*/
-- 대칭키생성
USE TESTDB;
CREATE SYMMETRIC KEY SymKEY_HAN WITH ALGORITHM = AES_128
	ENCRYPTION BY CERTIFICATE Cert_HAN; 		-- 대칭키를암호화하는데사용되는인증서이름을지정합니다
GO

-- 대칭키조회
SELECT * FROM sys.symmetric_keys

-- 대칭키오픈
IF EXISTS (SELECT * FROM TESTDB.sys.openkeys  WHERE KEY_NAME = 'SymKEY_HAN')
BEGIN
	PRINT 'ALREADY OPEN'
END
  ELSE
BEGIN
	OPEN SYMMETRIC KEY SymKEY_HAN			-- 대칭키이름
		DECRYPTION BY CERTIFICATE Cert_HAN 	-- 인증서이름
		WITH PASSWORD = '암호3'		-- ⓒ인증서비밀번호(대/소분자같아야함.)
	PRINT 'OPEN'
END



CREATE TABLE customer(
	ID INT IDENTITY PRIMARY KEY,
	NAME VARCHAR(20) NOT NULL,
	JUMIN_NUM VARBINARY(128)  NOT NULL --  대칭키가Open되어있지않으면NULL값으로대체된다.
)
GO
-- 암호화
INSERT INTO customer(NAME,JUMIN_NUM) VALUES('김한', EncryptByKey(Key_GUID('SymKEY_HAN'), '7005219925487')) 
GO

--그냥조회
SELECT * FROM customer
GO


--복호화
SELECT *, CONVERT(VARCHAR, DecryptbyKey(JUMIN_NUM))as 복호화된주민번호 FROM customer
GO

IF EXISTS (SELECT * FROM TESTDB.sys.openkeys  WHERE KEY_NAME = 'SymKEY_HAN')
BEGIN 
    CLOSE SYMMETRIC KEY SymKEY_HAN
    PRINT 'CLOSE'
END
  ELSE
BEGIN 
   PRINT 'ALREADY CLOSE'
END

-- 대칭키삭제
DROP SYMMETRIC KEY SymKEY_HAN