SQL Server Encryption Solutions (資料庫加密解決方案)


  1. 說明
    1. Backup Encryption
      1. 實驗 🧪
    2. Transparent Data Encryption
      1. 實驗 🧪
    3. Dynamic Data Masking
    4. Column Encryption
    5. Always Encrypted
  2. 參考資料

筆記 SQL Server 在安全性上各相關的加密保護資料方式,包含欄位加密、備份檔加密、透明資料加密以及 Always Encrypted 等加密方式。

SQL Server Logo

說明

Backup Encryption

TL;DR

產生備份檔 BAK 時進行加密,沒有憑證者無法使用該備份檔還原資料庫。

SQL Server Enterprise Standard
2019 V V
2017 V V
2016 V V

在 Master Database 建立 Master Key,接著同樣於 Master Database 建立 Certificate,並以先前的 Master Key 加密該憑證。而在備份資料庫時,選擇以憑證對資料庫檔進行加密。

需要注意的是憑證必須妥善管理,否則若遺失憑證會無法還原資料庫備份檔;此外憑證逾期並不影響加密後資料庫備份檔的還原。

SQL Server Backup Encryption 資料庫備份的加密方式

實驗 🧪

❗ Master Key 的密碼是否影響 Certificate 的還原使用
❗ 還原到第二台伺服器後,再次備份,使用不同的 Master Key 是否能夠還原到第一台伺服器

Transparent Data Encryption

TL;DR

保持資料庫 MDF 與 LDF 的加密,缺少憑證者無法進行資料庫附加;受 TDE 保護的資料庫所產生的備份檔,還原也需要憑證。

SQL Server Enterprise Standard
2019 V V
2017 V
2016 V

在 Master Database 建立 Master Key,接著同樣於 Master Database 建立 Certificate,並以先前的 Master Key 加密該憑證。TDE 屬於 Database Level 的加密方式,因此選定資料庫後設定進行 TDE 加密:

USE Master;
ALTER DATABASE DatabaseName SET ENCRYPTION ON;
GO

取消 TDE 的方式:

USE Master;
ALTER DATABASE DatabaseName SET ENCRYPTION OFF;
GO

圖片來源:learn.microsoft

透明資料加密 (TDE) | learn.microsoft

實驗 🧪

驗證 TDE 加密資料庫檔案後,附加至其他資料庫伺服器 (預期失敗)
驗證 TDE 加密資料庫檔案後,備份檔還原 (預期失敗)
驗證還原憑證後,將加密資料庫檔案附加至其他資料庫伺服器 (預期成功)
驗證還原憑證後,將加密資料庫檔案備份並還原至其他資料庫伺服器 (預期成功)

❗ Master Key 的密碼不影響 Certificate 的還原使用

Dynamic Data Masking

TL;DR

對於資料庫資料表中的特定欄位進行遮罩,並以權限的方式控管是否可以檢視資料。

SQL Server Enterprise Standard
2019 V V
2017 V V
2016 V V

Dynamic Data Masking

Column Encryption

TL;DR

對於資料庫資料表中的特定欄位進行加密保護。

不同於 TDE 以及 Backup Encryption,Column Encryption 的 Master Key 以及 Certificate 是儲存於 User Database 而非 Master Database。

此外 Columne Encryption 不一定需要 Master Key 以及 Certificate 來加密 Symmetric Key,可以只接用 Password 的方式加密 Symmetric Key:

CREATE SYMMETRIC KEY SSN_Key
    WITH ALGORITHM = AES_256
    ENCRYPTION BY password = 'P@ssw0rd';
GO

ALTER TABLE [dbo].[Cases]
    ADD EncryptedText varbinary(512);
GO

OPEN SYMMETRIC KEY SSN_Key
   DECRYPTION BY PASSWORD = 'P@ssw0rd';

UPDATE [dbo].[Cases]
	SET EncryptedText = EncryptByKey(Key_GUID('SSN_Key'), fullName);
GO

SELECT EncryptedText, DECRYPTBYKEY(EncryptedText) FROM [dbo].[Cases]

如果要使用 Master Key 來加密 Certificate,在使用 Certificate 來加密 Symmetric Key 則以下列語法進行:

Use DatabaseName;

CREATE MASTER KEY ENCRYPTION BY
PASSWORD = 'P@ssw0rd';

CREATE CERTIFICATE CertificateEncrypt
   WITH SUBJECT = 'Certificate Subject';
GO

CREATE SYMMETRIC KEY SSN_Key
    WITH ALGORITHM = AES_256
    ENCRYPTION BY CERTIFICATE CertificateEncrypt;
GO

對 Client 端而言,每個連線的 Session 必須先 Open Symmetric Key 才取對資料進行解密讀取

OPEN SYMMETRIC KEY SSN_Key
   DECRYPTION BY PASSWORD = 'P@ssw0rd';

CLOSE SYMMETRIC KEY SSN_KEY;

而 Open Symmetric Key 需要對 Symmetric Key 有 View Definition 的權限。而如果 Symmetric Key 是以憑證加密的,則還需要對憑證擁有 Control 的權限。

GRANT VIEW DEFINITION ON SYMMETRIC KEY::SSN_Key TO [UserName];  

Encrypt a Column of Data | learn.microsoft

Always Encrypted

TL;DR

犧牲部分效能為代價,由應用程式端負責加解密的工作,資料庫只保存加密後的資料。

SQL Server Enterprise Standard
2019 V V
2017 V V
2016 V V

參考資料

利用透明資料加密(TDE)對整個資料庫加密