[security] 데이터 암호화 및 복호화

 

• Student 테이블 생성

 

 

 

1. 현재 세션에서 사용할 데이터베이스를 지정(MASTER로 지정함)

Use MASTER
GO

 

2. 마스터키 생성

- 데이터베이스를 위한 마스터 키를 생성하고 지정된 비밀번호로 암호화

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'str0ngP$$word'
GO

 

3. 인증서 생성

- SECert라는 인증서를 생성

CREATE CERTIFICATE SECert WITH SUBJECT = 'SQL BACKUP - SE'

 

4. 대칭 키 생성

- AES_256 알고리즘을 사용하여 DEMOSEKEY라는 대칭 키를 생성하고 앞서 생성한 SECert로 암호화합니다.

CREATE SYMMETRIC KEY DEMOSEKEY 
    WITH ALGORITHM = AES_256
    ENCRYPTION BY CERTIFICATE SECert

 

5. 대칭키 열기 

- CERTIFICATE SECert를 사용하여 대칭 키 DEMOSEKEY를 복호화할 수 있도록 엽니다.

OPEN SYMMETRIC KEY DEMOSEKEY
    DECRYPTION BY CERTIFICATE SECert;

 

 

6. 암호화된 데이터 삽입

INSERT INTO STUDENT (fullname, mobileno, passportno)
   VALUES ('SE', ENCRYPTBYKEY(key_guid('DEMOSEKEY'),'82'), ENCRYPTBYKEY(key_guid('DEMOSEKEY'),'MV774'));

 

 

7. 데이터 선택 및 복호화

- 이 SELECT 문은 STUDENT 테이블에서 모든 행을 검색하고 대칭 키 DEMOSEKEY를 사용하여 암호화된 열 (passportno 및 mobileno)을 복호화합니다. 결과 집합에서 passport_EN 및 mobile_EN으로 표시됩니다.

 

SELECT *, 
CAST(DECRYPTBYKEY(passportno) AS VARCHAR(MAX)) AS passport_EN,
CAST(DECRYPTBYKEY(mobileno) AS VARCHAR(MAX)) AS mobile_EN 
FROM STUDENT;

 

 

 

- 전체 코드

Use MASTER
GO

SELECT * FROM SYS.certificates

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'str0ngP$$word'
GO

CREATE certificate SECert WITH subject = 'SQL BACKUP - SE'
GO

CREATE SYMMETRIC KEY DEMOSEKEY 
    WITH algorithm = AES_256
    ENCRYPTION BY CERTIFICATE SECert


OPEN SYMMETRIC KEY DEMOSEKEY
    decryption by certificate SECert;


SELECT * from STUDENT;


INSERT INTO STUDENT (fullname, mobileno, passportno)
   VALUES ('SE', ENCRYPTBYKEY(key_guid('DEMOSEKEY'),'82'), ENCRYPTBYKEY(key_guid('DEMOSEKEY'),'MV774'));


SELECT *, 
CAST(DECRYPTBYKEY(passportno) AS VARCHAR(MAX)) AS possport_EN,
CAST(DECRYPTBYKEY(mobileno) AS VARCHAR(MAX)) AS mobile_EN 
from STUDENT