SQL Server 預存程序/檢視/函數/觸發器 加密與解密原理深度解析 (含完整程式碼)

加密機制簡介

SQL Server 內建 WITH ENCRYPTION 選項，將物件定義以對稱加密儲存於 sys.sysobjvalues.imageval。解密原理利用 XOR 運算：
明文 = 密文 XOR 加密函數(參考明文) XOR 參考明文。
此方法從 SQL 2000 至 2019 均適用。

一、批次加密預存程序：sp_EncryptObject

USE master;
GO
IF OBJECT_ID('sp_EncryptObject') IS NOT NULL DROP PROCEDURE sp_EncryptObject;
GO
CREATE PROCEDURE sp_EncryptObject
    @Object SYSNAME = 'All'
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @sql NVARCHAR(MAX), @crlf NCHAR(2) = NCHAR(13) + NCHAR(10),
            @replace NVARCHAR(10), @type NVARCHAR(10);

    DECLARE cur CURSOR LOCAL FAST_FORWARD FOR
    SELECT QUOTENAME(SCHEMA_NAME(o.schema_id)) + '.' + QUOTENAME(o.name),
           m.definition, o.type
    FROM sys.sql_modules m
    JOIN sys.objects o ON o.object_id = m.object_id
    WHERE o.is_ms_shipped = 0
      AND m.definition IS NOT NULL
      AND (o.name = @Object OR @Object = 'All')
      AND o.name <> 'sp_EncryptObject'
      AND o.type IN ('P','V','TR','FN','IF','TF')
    ORDER BY CASE o.type WHEN 'V' THEN 1 WHEN 'TR' THEN 2 WHEN 'FN' THEN 3 WHEN 'IF' THEN 3 WHEN 'TF' THEN 3 ELSE 4 END;

    OPEN cur;
    FETCH NEXT FROM cur INTO @Object, @sql, @type;
    WHILE @@FETCH_STATUS = 0
    BEGIN
        BEGIN TRY
            -- 判斷觸發器使用 FOR 還是 AS
            IF @type = 'TR' SET @replace = 'FOR '; ELSE SET @replace = 'AS';

            -- 注入 WITH ENCRYPTION
            IF PATINDEX('%' + @crlf + @replace + @crlf + '%', @sql) > 0
                SET @sql = REPLACE(@sql, @crlf + @replace + @crlf, @crlf + 'WITH ENCRYPTION' + @crlf + @replace + @crlf);
            ELSE IF PATINDEX('%' + @replace + '%', @sql) > 0
                SET @sql = REPLACE(@sql, @replace, 'WITH ENCRYPTION ' + @replace);

            SET @sql = REPLACE(@sql, 'CREATE ' + 
                CASE @type WHEN 'P' THEN 'PROCEDURE' WHEN 'V' THEN 'VIEW' WHEN 'TR' THEN 'TRIGGER' ELSE 'FUNCTION' END,
                'ALTER ' + CASE @type WHEN 'P' THEN 'PROCEDURE' WHEN 'V' THEN 'VIEW' WHEN 'TR' THEN 'TRIGGER' ELSE 'FUNCTION' END);

            BEGIN TRANSACTION;
            EXEC sp_executesql @sql;
            PRINT N'已加密: ' + @Object;
            COMMIT TRANSACTION;
        END TRY
        BEGIN CATCH
            ROLLBACK TRANSACTION;
            PRINT N'加密失敗: ' + @Object + N' - ' + ERROR_MESSAGE();
        END CATCH
        FETCH NEXT FROM cur INTO @Object, @sql, @type;
    END
    CLOSE cur; DEALLOCATE cur;
END;
GO
EXEC sp_ms_marksystemobject 'sp_EncryptObject'; -- 標記為系統物件，可跨資料庫呼叫

二、單一物件解密預存程序：sp_DecryptObject (需 DAC 連線)

USE master;
GO
IF OBJECT_ID('sp_DecryptObject') IS NOT NULL DROP PROCEDURE sp_DecryptObject;
GO
CREATE PROCEDURE sp_DecryptObject
    @Object SYSNAME,
    @MaxLength INT = 4000
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @objid INT = OBJECT_ID(@Object);
    IF @objid IS NULL RETURN;

    -- 取得加密後的 imageval
    DECLARE @encrypted NVARCHAR(MAX) = (
        SELECT TOP 1 CAST(imageval AS NVARCHAR(MAX))
        FROM sys.sysobjvalues
        WHERE objid = @objid AND valclass = 1
    );

    -- 建立臨時同名物件 (含 WITH ENCRYPTION) 取得參考明文
    DECLARE @tmpSql NVARCHAR(MAX) = N'CREATE PROCEDURE ' + @Object + N' WITH ENCRYPTION AS SELECT 1;';
    BEGIN TRANSACTION;
    EXEC sp_executesql @tmpSql;
    DECLARE @reference NVARCHAR(MAX) = (
        SELECT TOP 1 CAST(imageval AS NVARCHAR(MAX))
        FROM sys.sysobjvalues
        WHERE objid = @objid AND valclass = 1
    );
    ROLLBACK TRANSACTION; -- 不真正建立

    -- XOR 解密
    DECLARE @decrypted NVARCHAR(MAX) = N'', @i INT = 1, @len INT = DATALENGTH(@encrypted)/2;
    WHILE @i <= @len
    BEGIN
        SET @decrypted += NCHAR(
            UNICODE(SUBSTRING(@encrypted, @i, 1)) ^
            UNICODE(SUBSTRING(@reference, @i, 1)) ^
            UNICODE(N'-')  -- 參考明文為重複 '-'
        );
        SET @i += 1;
    END

    -- 格式化輸出
    PRINT @decrypted;
END;
GO
EXEC sp_ms_marksystemobject 'sp_DecryptObject';

三、使用方式

1. 在 SSMS 查詢視窗以 DAC 連線：
   連線伺服器名稱 輸入 admin:YourInstance (或 sqlcmd -S admin:Instance)。
2. 執行加密：EXEC sp_EncryptObject 'All'; 或 EXEC sp_EncryptObject 'MyProc';
3. 驗證：SELECT definition FROM sys.sql_modules WHERE object_id = OBJECT_ID('MyProc'); 應回傳 NULL。
4. 執行解密：EXEC sp_DecryptObject 'MyProc'; (長定義可加 @MaxLength=20000)。

安全性警示

• WITH ENCRYPTION 非軍事級加密，僅防誤改/簡易隱藏，專業工具可輕易解密。
• 生產環境敏感邏輯建議：CLR 組件、Always Encrypted、或應用層加密。
• 解密需 sysadmin 權限與 DAC 連線，請嚴格控管。