資料庫加密：九成的人沒搞懂的加密層級，一次搞懂4種資料加密層級！

這次拍了一個跟加密相關的影片，內容不是困難的(應該吧)，但我相信可能超過9成的人都不清楚。 今天內容確實跟技術相關，但沒有太技術性的細節，主要是觀念上的解說。

其實當初我在研究資料庫加密的時候，就有發現有些觀念滿重要的，但很多資訊人員、開發人員都不知道，甚至是資安人員也不清楚。這個是拍這部影片的一個原因。還有另一個原因呢，當初有被詢問到一個需求，是有其他單位的人來問我，有沒有辦法用一個工具，直接找到整個公司裡面所有用到的加密演算法都是甚麼。當然我是回答沒有。因為加密其實有傳輸加密、靜態加密，還有很多不同層級的加密，不同服務的加密，所以不會這麼容易就可以盤點到。

本文章請搭配 Youtube 影片一起服用。

這篇文章目的是提供給想要自行操作影片中建立資料與查詢語法的人。

也透過這篇文章，補充一點影片中沒提到但是滿重要的內容。這部影片目的是專注於解釋加密有不同層級而已，並且加密的方式與層級，也會與效能、架構設計、叢集、模糊查詢、金鑰遺失的復原等等，許多其他事情都有關聯性，這些都是在影片當中沒有去多談及。所以呀，每種層級的加密，其實並沒有哪一種是絕對的好與壞的。

影片連結：

註： 這次這部影片其實想了很久，包含到底是要不要更完整的說明，還有要不要解釋查詢語法，還有 BitLocker 要使用本機還是隨身碟。種種考量之下，其實影片最後版本是選擇了用側重在觀念的方式，所以沒有帶大家從建立資料開始，也沒有更多的展示資料庫表格以及說明。也是考量到了時間的緣故，真的要多講一的話，可能就會超過一個小時，有點不符合自己當初想拍的短片介紹性質。

另外就是測試工具跟順序剛好出了不少小問題，導致錄製過程頻頻出錯，後來也是錄到有點懶了XD，有點小失誤、不流暢、不連貫的小地方，也就勉強接受帶過了，相信至少主要想表達的觀念跟示範都還是意思有到。也請大家多多見諒。

下載 SQL Server

影片使用的資料庫是 SQL Server

官方下載頁：

https://www.microsoft.com/sql-server/sql-server-downloads

安裝選項

  • 選 Developer
  • 選 Basic 安裝（最快）
  • Authentication：
    • Windows Authentication（最簡單）

下載 SSMS

影片使用的資料庫連線工具是 SQL Server Management Studio

https://learn.microsoft.com/zh-tw/ssms/install/install

SSMS連線與測試

開啟 SSMS 工具，輸入 localhost 按連接即可

如下圖所示：

打開一個查詢視窗

點選上方工具列的新增查詢：

新增查詢（NewQuery）

然後執行：

SELECT SYSTEM_USER;
SELECT IS_SRVROLEMEMBER('sysadmin');Code language: JavaScript (javascript)

如果結果是：

  • DESKTOP-8FTHF3K\\xxx
  • 1

👉 確認指令執行正常，也驗證你目前權限就是 SysAdmin（最高權限）

建立資料庫

輸入並執行以下語法建立資料庫

後續重新連接時，記得進入後要先 USE EncryptionDemo;

CREATE DATABASE EncryptionDemo;
GO
USE EncryptionDemo;
GOCode language: PHP (php)

檔案層級加密：TDE資料庫透明資料加密

建立TDEEncryptionUsers資料表格

CREATE TABLE TDEEncryptionUsers (
    ID INT IDENTITY PRIMARY KEY,
    Name NVARCHAR(50),
    Phone NVARCHAR(50)
);

寫入TDEEncryptionUsers資料

INSERT INTO TDEEncryptionUsers (Name, Phone)
VALUES ('Alice', '0912345678'),
       ('Bob',   '0987654321');Code language: JavaScript (javascript)

查詢TDEEncryptionUsers資料

SELECT * FROM TDEEncryptionUsers;

TDE 開啟

USE master;
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'TDEMasterKey!123';

CREATE CERTIFICATE TDECert
WITH SUBJECT = 'TDE Certificate';

USE EncryptionDemo;
GO
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_256
ENCRYPTION BY SERVER CERTIFICATE TDECert;

ALTER DATABASE EncryptionDemo
SET ENCRYPTION ON;Code language: PHP (php)

TDE 關閉

ALTER DATABASE EncryptionDemo
SET ENCRYPTION OFF;

資料庫檔案路徑確認

SELECT
    name,
    physical_name
FROM sys.database_files;Code language: CSS (css)
C:\\Program Files\\Microsoft SQL Server\\MSSQL17.MSSQLSERVER\\MSSQL\\DATA\\EncryptionDemo.mdf
C:\\Program Files\\Microsoft SQL Server\\MSSQL17.MSSQLSERVER\\MSSQL\\DATA\\EncryptionDemo_log.ldfCode language: CSS (css)

資料庫檔案鎖定解除

因為資料庫檔案無法直接複製，需要SET OFFLINE

Set OFFLINE

ALTER DATABASE EncryptionDemo
SET OFFLINE WITH ROLLBACK IMMEDIATE;

Set ONLINE

ALTER DATABASE EncryptionDemo SET ONLINE;

欄位層級加密

建立ColumnEncryptedUsers資料表格

CREATE TABLE ColumnEncryptedUsers (
    ID INT IDENTITY PRIMARY KEY,
    Name NVARCHAR(50),
    Salary VARBINARY(MAX)
);

建立金鑰（只做一次）

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'MasterKey!123';

CREATE SYMMETRIC KEY ColumnKey
WITH ALGORITHM = AES_256
ENCRYPTION BY PASSWORD = 'ColumnPassword123!';Code language: JavaScript (javascript)

寫入資料（加密）

OPEN SYMMETRIC KEY ColumnKey
DECRYPTION BY PASSWORD = 'ColumnPassword123!';

INSERT INTO ColumnEncryptedUsers (Name, Salary)
VALUES (
  'Carol',
  EncryptByKey(
    Key_GUID('ColumnKey'),
    CONVERT(VARBINARY(4), 90000)
  )
);

CLOSE SYMMETRIC KEY ColumnKey;Code language: JavaScript (javascript)

查詢（沒金鑰，看亂碼）

SELECT * FROM ColumnEncryptedUsers;

查詢（解密）

OPEN SYMMETRIC KEY ColumnKey
DECRYPTION BY PASSWORD = 'ColumnPassword123!';

SELECT Name,
       CONVERT(INT, DecryptByKey(Salary)) AS Salary
FROM ColumnEncryptedUsers;

CLOSE SYMMETRIC KEY ColumnKey;Code language: PHP (php)

應用層加密

建立AppEncryptedUsers資料表格

CREATE TABLE AppEncryptedUsers (
    ID INT IDENTITY PRIMARY KEY,
    Name NVARCHAR(50),
    Secret VARBINARY(MAX)
);

👉 這張表不用寫入資料，資料會透過 Web 寫進來

Python Web（最小可 Demo 版）

在 windows 上安裝 Python 之後

安裝所許的依賴項

pip install flask cryptography pyodbc

撰寫一個 web-python.py，然後執行即可

from flask import Flask, request, render_template_string
from cryptography.fernet import Fernet
import pyodbc

app = Flask(__name__)

# 產生應用層金鑰（示範用，正式請存安全地方）
key = Fernet.generate_key()
cipher = Fernet(key)

conn = pyodbc.connect(
    "DRIVER={ODBC Driver 17 for SQL Server};"
    "SERVER=localhost;"
    "DATABASE=EncryptionDemo;"
    "Trusted_Connection=yes;"
)

@app.route("/", methods=["GET", "POST"])
def index():
    decrypted = None

    if request.method == "POST":
        name = request.form["name"]
        secret = request.form["secret"]

        encrypted = cipher.encrypt(secret.encode())

        cursor = conn.cursor()
        cursor.execute(
            "INSERT INTO AppEncryptedUsers (Name, SecretData) VALUES (?, ?)",
            name, encrypted
        )
        conn.commit()

        decrypted = secret

    return render_template_string("""
        <h2>Application Level Encryption Demo</h2>
        <form method="post">
            Name: <input name="name"><br>
            Secret: <input name="secret"><br>
            <button type="submit">Submit</button>
        </form>
        {% if decrypted %}
        <p><b>Web 看到的明文：</b>{{ decrypted }}</p>
        {% endif %}
    """, decrypted=decrypted)

app.run(debug=True)Code language: PHP (php)

資料寫入後，在資料庫可以用以下語法查詢（會看到加密內容）

SELECT * FROM AppEncryptedUsers;

如果有關閉 web 重新啟動的話

需要清除資料庫內容（加密方式關係，必須要清除，因為每次的 key 不同)

USE EncryptionDemo;
GO

DELETE FROM AppEncryptedUsers;Code language: PHP (php)
Tags: ,

發佈留言