筆記 SQL Server 在安全性上各相關的加密保護資料方式,包含欄位加密、備份檔加密、透明資料加密以及 Always Encrypted 等加密方式。
說明
Backup Encryption
產生備份檔 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
保持資料庫 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
透明資料加密 (TDE) | learn.microsoft
實驗 🧪
驗證 TDE 加密資料庫檔案後,附加至其他資料庫伺服器 (預期失敗)
驗證 TDE 加密資料庫檔案後,備份檔還原 (預期失敗)
驗證還原憑證後,將加密資料庫檔案附加至其他資料庫伺服器 (預期成功)
驗證還原憑證後,將加密資料庫檔案備份並還原至其他資料庫伺服器 (預期成功)
❗ Master Key 的密碼不影響 Certificate 的還原使用
Dynamic Data Masking
對於資料庫資料表中的特定欄位進行遮罩,並以權限的方式控管是否可以檢視資料。
SQL Server | Enterprise | Standard |
---|---|---|
2019 | V | V |
2017 | V | V |
2016 | V | V |
Column Encryption
對於資料庫資料表中的特定欄位進行加密保護。
不同於 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
犧牲部分效能為代價,由應用程式端負責加解密的工作,資料庫只保存加密後的資料。
SQL Server | Enterprise | Standard |
---|---|---|
2019 | V | V |
2017 | V | V |
2016 | V | V |