따라서 정렬되야 하는 컬럼을 암호화하면 성능에 문제가 생길 수 있다. 또한 암호화는 데이터 크기도 증가시키기 때문에 물리적인 공간도 더 많이 사용한다.
데이터 암호화
MSSQL이 제공하는 암호화 방법은 다음과 같다.
방법/암호화 함수/복호화 함수
1. 대칭키 사용 암호화(Encrypt using symmetric key)/EncryptByKey/DecrpytByKey
2. 비대칭키 사용 암호화(Asymmetric key usage encryption)/EncryptByAsymKey/DecrpytByAsymKey
3. 인증서를 사용한 암호화(Encryption with Certificates)/EncrpytByCert/DecrpytByCert
4. HASHBYTES 함수를 사용한 암호화(Encryption using the HASHBYTES function)/HASHBYTES/없음(none)
대칭키로 암호화하려면 우선 암호화에 사용할 대칭 키를 만들어야한다.
암호를 사용해서 대칭키를 보호할 수도 있지만 인증서로 보호되는 대칭키를 만들수도 있다.
-- 대칭키 만들기
USE TEST
GO
CREATE SYMMETRIC KEY SECKEY
WITH ALGORITHM = AES_256
ENCRYPTION BY PASSWORD = 'defensepark11@'
GO
대칭 키를 만든 직후에는 대칭 키가 열려있지만 그렇지 않을 때는 명시적으로 열어야 하며 암호가 필요하다.
-- 대칭키 열기
OPEN SYMMETRIC KEY SECKEY
DECRYPTION BY PASSWORD = 'defensepark11@'
GO
--데이터 암호화 시키기
SELECT COL_1, EncryptByKey(Key_GUID('SECKEY'),COL_1) AS SEC_COL_1
FROM TMP_TBL
-- 테스트 테이블 생성
CREATE TABLE TMP_TBL (
COL_1 VARCHAR(50) NOT NULL,
COL_2 VARCHAR(50) NOT NULL,
COL_3 VARCHAR(50) NOT NULL
)
INSERT INTO TMP_TBL VALUES('호날두','호날두','호날두')
INSERT INTO TMP_TBL VALUES('즐라탄','즐라탄','즐라탄')
INSERT INTO TMP_TBL VALUES('메시','메시','메시')
--데이터 복호화 시키기
SELECT COL_1
, EncryptByKey(Key_GUID('SECKEY'),COL_1) AS ENC_COL1
, CONVERT(VARCHAR(50),DecryptByKey(EncryptByKey(Key_GUID('SECKEY'),COL_1))) COL_1
FROM TMP_TBL
암호화&복호화 결과는 다음과 같다.
HASHBYTES 함수를 사용한 암호화
- 암호화를 위해 키나 인증서가 필요하지 않다.
복호화가 불가능하다. (단방향 암호화)
암호화한 컬럼에 인덱스를 생성해서 성능 효과를 볼 수 있다.
(조회할 때 HASHBYTES로 암호화를 해서 조회하면 되니까...)
--암호화 시키기
SELECT COL_1, HASHBYTES('MD5',COL_1)
FROM TMP_TBL
결과는 다음과 같다.
해쉬바이트로 암호화를 시키면 복호화를 할 수는 없지만 조회할려는 데이터를 해쉬바이트로 암호화해서 이퀄조회로 찾으면 된다. 따라서 이건 인덱스를 만들 수 있다.
데이터베이스 암호화
MSSQL 2008부터는 엔터프라이즈 버전에서 새로운 데이터 암호화 방법을 제공한다.
응용프로그램 레벨에서 안건들이고 데이터를 암호화 할 수 있어서 투명한 데이터 암호화(TDE)라 불린다.
단 컬럼 단위의 암호화가 아닌 데이터베이스 전체를 암호화한다.
데이터베이스 암호화는 암호화 함수를 사용하지 않고 MSSQL이 자동으로 암호화한다.
데이터 페이지를 암호화하고 디스크에 기록하고, 데이터를 디스크에서 메모리로 읽어들일 때 복호화한다.
그리고 암호화된 데이터베이스는 다른 서버에 연결(Attach)하거나 복원(Restore)이 불가능하다.
암호화 설정 단계
1. 마스터키 생성
2. 마스터키로 보호되는 인증서 만들기
3. 인증서로 보호되는 DEK(데이터베이스 암호화 키) 만들기
4. 암호화를 사용하도록 데이터베이스 설정
1, 2번은 master DB에서 수행해야하고 3, 4번은 해당 DB에서 수행해야 한다.
1. 마스터키 생성
USE master
GO
CREATE MASTER KEY
ENCRYPTION BY PASSWORD = 'defensepark11@'
2. 마스터키로 보호되는 인증서 만들기
인증서는 DB 암호화 키를 보호하는데 사용되고 master db에 만들어야 한다.
CREATE CERTIFICATE TDECertificate
WITH SUBJECT = 'server level certificate for TDE'
3. 인증서로 보호되는 DEK(데이터베이스 암호화 키) 만들기
암호화하려고 하는 대상 DB에서 만들어야하고 DB 암호화 키를 보호하는데 사용된 인증서를 백업받아야한다고 경고메세지가 표시된다. 암호화된 DB를 백업한 후 나중에 복원할 때 인증서가 꼭 필요하기 때문이다.
USE TESTDB
GO
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_256
ENCRYPTION BY SERVER CERTIFICATE TDECertificate
GO
쿼리를 날리면 다음과 같이 뜬다.
경고: 데이터베이스 암호화 키를 암호화하는 데 사용된 인증서가 백업되지 않았습니다. 인증서와 인증서에 연결된 개인 키를 즉시 백업해야 합니다. 인증서를 사용할 수 없게 되거나 다른 서버에서 데이터베이스를 복원하거나 연결해야 할 경우 인증서와 개인 키의 백업본이 있어야 합니다. 그렇지 않으면 데이터베이스를 열 수 없습니다.
WARNING : The certificate used to encrypt the database encryption key was not backed up. You should back up the certificate and the private key associated with it immediately. If the certificate becomes unavailable or you need to restore or connect the database from another server, you must have a backup of the certificate and private key. Otherwise, you can not open the database.
4. 암호화를 사용하도록 데이터베이스 설정
ALTER DATABASE TESTDB
SET ENCRYPTION ON
GO
인증서 백업과 복원
암호화한 데이터베이스는 백업 파일도 암호화된다. 따라서 복원하려면 암호화 키를 보호하는 인증서가 있어야한다. 인증서가 없으면 데이터베이스를 복원할 수도 없고 데이터베이스를 분리해서 다른 서버에 연결할 수도 없다.
인증서 백업
BACKUP CERTIFICATE 문을 사용해서 인증서를 백업한다. 이때 개인 키도 같이 백업해야 이후에 인증서를 복원할 수 있다.
BACKUP CERTIFICATE TDECertificate
TO FILE = 'C:\SQLBACKUP\TDECertificate.cer'
WITH PRIVATE KEY (FILE = 'C:\SQLBACKUP\TDECertificate.pvk' ,
ENCRYPTION BY PASSWORD = 'defensepark11@')
GO
백업이 되면 폴더에 가서 확인할 수 있다.
인증서 복원
암호화된 데이터베이스를 복원할 새로운 서버에 백업한 인증서와 개인 키를 복사하고 그 백업 파일로부터 인증서를 만든다. master 데이터베이스에 마스터 키가 없으면 만들어야한다. CREATE MASTER KEY 문을 사용해서 대상 서버에서 마스터 키를 만들고, CREATE CERTIFICATE...FROM 문을 사용해서 복사한 인증서 파일로부터 인증서를 복원한다.
USE master
CREATE MASTER KEY
ENCRYPTION BY PASSWORD = 'defensepark11@'
GO
CREATE CERTIFICATE TDECertificate
FROM FILE = 'C:\SQLBACKUP\TDECertificate.cer'
WITH PRIVATE KEY (FILE = 'C:\SQLBACKUP\TDECertificate.pvk',
DECRYPTION BY PASSWORD = 'defensepark11@')
GO
데이터베이스 암호화 제거
-- 암호화 OFF
ALTER DATABASE TESTDB
SET ENCRYPTION OFF
GO
-- 데이터베이스 암호화 키 제거
USE TESTDB
GO
DROP DATABASE ENCRYPTION KEY
GO
-- 인증서 제거
USE master
GO
DROP CERTIFICATE TDECertificate
GO
-- 마스터 키 제거
DROP MASTER KEY
GO
-- 복구모델단순으로 변경
ALTER DATABASE TESTDB
SET RECOVERY SIMPLE
GO
-- 체크포인트 명령어를 몇번 실행시켜서 로그를 완전히 없앤다.
CHECKPOINT -- 여러번 수행
GO
-- 복구모델 원래대로 변경
ALTER DATABASE TESTDB
SET RECOVERY FULL
GO
MSSQL 감사(SQL SERVER Audits)
mssql 2008부터 제공되는 mssql 감사 기능은 트리거나 프로파일러와 같은 기능들의 단점을 극복해주는 가장 적절한 감사 기능이다. sql server 감사를 구성해 서버와 데이터베이스에서 발생하는 모든 동작을 추적하여 그 내역을 기록(감사 로그)할 수 있다. 무엇보다도 테이블이나 뷰에 대한 select와 프로시저에 대한 exec 동작도 감사 대상인 것이 장점이다.
보통 실시간 실행 배치를 만들어서 떠다니는 쿼리를 잡아서 로깅하거나 어플리케이션 단에서 로깅을 하도록 하는데 이건 좋은 것 같다.
-- 감사 만들기
USE master
GO
-- 디폴트로 만들기
CREATE SERVER AUDIT [Audit_test]
TO FILE (FILEPATH = 'C:\SQLLOG\Audit')
GO
/* 옵션 주기
USE master
GO
CREATE SERVER AUDIT Audit_test
TO FILE ( FILEPATH = 'C:\SQLLOG\Audit',
MAXSIZE = 512MB,
MAX_ROLLOVER_FILES = 100,
RESERVE_DISK_SPACE = OFF
) WITH (
QUEUE_DELAY = 1000,
ON_FAILURE = CONTINUE
)
GO
*/
-- 감사 활성화 하기
ALTER SERVER AUDIT Audit_test
WITH (STATE = ON)
GO
감사 사양(Audit Specifications) 만들기
실제 감사 정보를 남기려면 감사 사양을 만들어야 한다.
감사 사양은 서버 감사 사양과 데이터베이스 감사 사양으로 구분되고 서버 감사 사양은 서버 범위에서 발생하는 동작을, 데이터베이스 감사 사양은 데이터베이스 범위에서 발생하는 동작을 기록한다.
--데이터베이스 감사 사양 만들기
USE TESTDB
CREATE DATABASE AUDIT SPECIFICATION Audit_test_spec
FOR SERVER AUDIT Audit_test
ADD (SELECT ON OBJECT::dbo.TMP_TBL BY public)
GO
--감사 사양 활성화
ALTER DATABASE AUDIT SPECIFICATION Audit_test_spec
FOR SERVER AUDIT Audit_test
WITH (STATE = ON)
GO
--하나 셀렉트 해주고
SELECT TOP 1 *
FROM TMP_TBL
--기록된 감사 확인
SELECT *
FROM sys.fn_get_audit_file('C:\SQLLOG\Audit\Audit_test*',DEFAULT,DEFAULT);
--감사와 감사 사양 제거
USE TESTDB
GO
-- 감사 사양 비활성화
ALTER DATABASE AUDIT SPECIFICATION Audit_test_spec
WITH (STATE = OFF)
GO
-- 감사 사양 제거
DROP DATABASE AUDIT SPECIFICATION Audit_test_spec
GO
USE master
GO
-- 서버 감사 비활성화
ALTER SERVER AUDIT Audit_test
WITH (STATE = OFF)
GO
-- 서버 감사 제거
DROP SERVER AUDIT Audit_test
GO
감사 설정은 서버에 무리가 없다고 하는데 트래픽이 있는 곳에서는 무리가 있을 것으로 보이고.. 그래도 필요한 곳에만 감사를 걸어서 문제 발생 여지를 남기지 말자.
댓글 없음:
댓글 쓰기