Transparent Data Encryption (TDE) 透明資料加密
資料庫安全防護機制常見之方式有:防火牆、權限控管、資料加密等等,除上述之外尚可利用TDE之保護機制,來對資料進行防護,以避免資料庫之「實體檔案」因任何原因被攜出時意外導致資料外流。
@摘要說明
1.使用者可自定義之部分為資料庫層級(DataBase Level),如第4點架構圖。
2.2008R2,Enterprise以上版本(AWS:2012以上;Azure:支援,細節請參文件)
3.加解密使用Certificate(Symmetric key)/EKM(Asymmetric key)
4.加密架構示意圖如下:
@測試案例
環境 | OS | Windows Server 2008 Enterprise Windows Server 2012 Enterprise Windows Server 2016 Enterprise Windows Server 2019 DataCenter |
DB | SQL Server 2008 R2 Enterprise SQL Server 2012 Enterprise SQL Server 2014 Enterprise SQL Server 2016 Enterprise |
|
情境 | 1. 不同的 OS 與 DB 之間相互備份&還原 (基本上版本由低至高) 2. 以不同方式(關聯與加密)產出憑證與金鑰 |
(避免篇幅過長,僅列出一種案例組合供參)
/*建立 Database Master key*/
USE [master]
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'MasterKey'
GO
/*建立測試用憑證*/
--ENCRYPTION : 若無指定,預設將以 Database Master key 進行加密
--EXPIRY_DATE : 若無給值,預設為一年
CREATE CERTIFICATE TestDbCert WITH
SUBJECT = 'TestDbCert',
EXPIRY_DATE = '2019/12/31'
GO
/*建立測試DB*/
CREATE DATABASE TestDB;
GO
/*使用測試憑證建立資料庫加密金鑰*/
USE TestDB;
GO
CREATE DATABASE ENCRYPTION KEY WITH
ALGORITHM = AES_256
ENCRYPTION BY SERVER CERTIFICATE TestDbCert;
GO
/*啟用資料庫加密*/
ALTER DATABASE TestDB
SET ENCRYPTION ON
GO
/*檢視金鑰*/
SELECT * FROM [master].sys.symmetric_keys WHERE NAME='##MS_DatabaseMasterKey##'
/*檢視憑證*/
SELECT * FROM [master].sys.certificates WHERE NAME='TestDbCert'
/*檢視資料庫加密狀態*/
SELECT
DB_NAME(database_id) [dbname]
,(CASE encryption_state
WHEN 1 THEN 'Unencrypted'
WHEN 2 THEN 'Encryption in progress'
WHEN 3 THEN 'Encrypted'
WHEN 4 THEN 'Key change in progress'
WHEN 5 THEN 'Decryption in progress'
WHEN 6 THEN 'Protection change in progress'
WHEN 0 THEN 'No database encryption key present, no encryption'
ELSE 'Undefine' END) encryption_state
,create_date
,encryptor_type
FROM sys.dm_database_encryption_keys
GO
/*備份資料庫*/
BACKUP DATABASE TestDB
TO DISK = N'D:\TDE\BK\DB\TestDB.bak'
WITH
NOFORMAT
,NOINIT
,NAME = N'TestDB-Full Backup'
,SKIP
,NOREWIND
,NOUNLOAD
,COMPRESSION
,STATS = 10
GO
至此已完成測試準備,
接著以不同Instance之資料庫進行資料庫還原測試。
1. 直接附加資料庫檔案(.mdf):使用SQL staetment與SSMS
CREATE DATABASE [AttachTestDB] ON
(FILENAME = N'D:\TDE\BK\DB\TestDB.mdf'),
(FILENAME = N'D:\TDE\BK\DB\TestDB.LDF')
FOR ATTACH;
GO
2. 使用備份檔案(.bak)進行還原:使用SQL staetment與SSMS
RESTORE DATABASE [RestoreTestDB]
FROM DISK = N'D:\TDE\BK\DB\TestDB.Bak'
WITH
FILE = 1
,NOUNLOAD
,NORECOVERY
,REPLACE
,STATS = 10
GO
由以上結果確認,在沒有對應的憑證之前,資料庫檔案(.mdf、.bak)已確實受到保護。
@使用憑證正確還原資料庫
/*從來源備份 Master Key*/
USE [master]
GO
BACKUP MASTER KEY
TO FILE = 'D:\TDE\BK\KEY\MASTER_KEY'
ENCRYPTION BY PASSWORD = 'MASTER_KEY'
GO
/*從來源備份憑證,並以私鑰防護*/
BACKUP CERTIFICATE TestDbCert
TO FILE = 'D:\TDE\BK\CERTIFICATE\TestDbCert'
WITH PRIVATE KEY (
FILE='D:\TDE\BK\KEY\TestDbCert_Private_Key',
ENCRYPTION BY PASSWORD='Tp@12341234'
)
GO
/*=====於另一Instance還原DB=====*/
USE [master]
GO
/*匯入Database Master key*/
--Force :無論是否解析成功都強制覆蓋,若無指定此參數,將會先進行解析,若已存在相同KEY則會告知已存在相同KEY不進行匯入
RESTORE MASTER KEY FROM FILE = 'D:\TDE\BK\KEY\MASTER_KEY'
DECRYPTION BY PASSWORD = 'MASTER_KEY' --以原本的password解開
ENCRYPTION BY PASSWORD = 'MASTER_KEY_NEW' --加上新的password
Force
GO
/*將 Database Master key 與 Service Master Key 重新關聯,需先進行此動作才能順利匯入憑證*/
OPEN MASTER KEY DECRYPTION BY PASSWORD = 'MASTER_KEY_NEW'
ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY
CLOSE MASTER KEY
GO
/*使用私鑰匯入憑證*/
CREATE CERTIFICATE FromTestDbCert
FROM FILE = 'D:\TDE\BK\CERTIFICATE\TestDbCert'
WITH PRIVATE KEY (
FILE = 'D:\TDE\BK\KEY\TestDbCert_Private_Key',
DECRYPTION BY PASSWORD = 'Tp@12341234'
)
GO
/*還原1:附加資料庫檔案*/
CREATE DATABASE [AttachTestDB] ON
(FILENAME = N'D:\TDE\BK\DB\TestDB.mdf'),
(FILENAME = N'D:\TDE\BK\DB\TestDB_log.LDF')
FOR ATTACH;
GO
/*還原2:使用備份檔案還原*/
RESTORE DATABASE [RestoreTestDB]
FROM DISK = N'D:\TDE\BK\DB\TestDB.bak'
WITH
FILE = 1
,MOVE N'TestDB' TO N'D:\MSSQL\DATA\TestDB.mdf'
,MOVE N'TestDB_log' TO N'D:\MSSQL\DATA\TestDB_log.ldf'
,NOUNLOAD
,STATS = 5
GO
--成功還原TestDB至其他Instance
/*===== The End =====*/
@效能測試
為增大結果數據差距幅度之可能性,以較低硬體配備進行,
不以單一運行模式進行,以較貼近實際整體運行狀況為基礎,提高頻率來做模擬測試
測試環境 (AP Server) |
OS | Windows Server 2019 Stand |
CPU | Intel Core i5 3300 | |
RAM | 8G | |
STORAGE | 256G SSD | |
測試環境 (DB Server) |
規格與 AP Server 相同 DB : SQL Server 2106 Enterprise |
|
測試情境 |
使用人數 | 使用者3000人 (起始100,10分鐘內逐步增加至3000) |
持續時間 | 30分鐘 |
|
使用者行為/週期 | 新增30筆;讀取200筆;更新10筆 / sec Max Loading : 新增90,000筆;讀取600,000;更新30,000筆 / 每秒 |
測試結果 |
||||
環境 |
項目 |
未啟用TDE | 啟用TDE | 效能差異% ((啟用-未啟用)/未啟用)*100% |
AP Server |
平均回應時間(秒) Avg.Response Time(sec) |
1.96s | 2.05s | 4.59% |
DB Server | CPU 平均使用率 |
37% | 41% | 10.81% |
MEMORY 平均使用率 |
63% | 72% | 14.28% | |
DISK IO 平均使用率 平均回應時間 |
5% 1433/ms |
6% 1467/ms |
20% 2.37% |
測試結果分析:
1.平均回應時間無明顯增加,Client端使用者幾乎沒有影響
2.DB Server資源耗用增加比例主要在CPU與MEMORY上,因以較低規格硬體設備加高頻率使用者行為進行測試,故真正增加的資源耗用幅度比例應較測試結果來的要低,須再依系統實際運行模式進行估算。
結論:以一般系統來評估,增加的資源消耗應屬可接受範圍。
@心得摘要
1.以Instance為基礎,Database為主要個體對應。
2.憑證與金鑰以何方式建置、存放於哪並無固定,皆可獨立拆分,完全取決於規劃設計
3.啟用加密之影響:
01.不影響原程式存取資料
02.資料庫檔案(.mdf、.ldf、.bak)未見明顯增加,故無需大幅擴增儲存空間
03.整體效能影響約5-10%
04.只要有一個資料庫啟用加密,該Instance之tempdb將會自動啟動加密,多少會對其他資料庫效能造成影響
05.此機制非於資料層進行加密,故不受資料量大小影響
4.須妥善保管憑證與金鑰,若原Instance毀損加上遺失憑證/金鑰,將無法再存取該資料庫相關資料。
(每次安裝新 Instance 之 Service Master Key 皆是唯一)
5.無法僅針對部分資料加密(同資料庫無法拆分)
6.FILESTREAM(FILETABLE)不在保護範圍內
7.ALGORITHM 依各 OS 版本之支援度而定
@延伸應用
1.使用憑證對機敏性資料進行加解密
2.利用憑證授權來控管使用者帳號
3.異地備援之安全性防護
4.雲端資料同步之安全防護
@參考資料