SQL Server 2008 程式碼安全實戰（二）：所有權鏈與執行上下文進階

回顧與引入

上一篇介紹了預存程序加密與 EXECUTE AS 基礎概念。本文透過兩個實戰案例，深入剖析所有權鏈的限制與動態 SQL 的權限陷阱，並示範如何透過指定執行上下文破解權限難題。

案例一：TRUNCATE TABLE 觸發的權限斷層

場景建置

1. 建立測試預存程序 CPP_DEL_ALL_Tb_Demo，執行 DELETE dbo.tb_Demo。
2. 建立登入 TonyZhang，僅授與該預存程序 EXECUTE 權限，不給予 tb_Demo 表的任何直接權限。
3. 以 TonyZhang 登入執行 → 成功刪除資料。原因：所有權鏈生效（預存程序與表擁有者相同）。

修改為 TRUNCATE 後發生什麼？

ALTER PROCEDURE dbo.CPP_DEL_ALL_Tb_Demo
AS
TRUNCATE TABLE dbo.tb_Demo;  -- 非 DML，屬 DDL

再次以 TonyZhang 執行 → 失敗，錯誤訊息如下：

核心結論

所有權鏈僅涵蓋 SELECT、INSERT、UPDATE、DELETE 四大 DML 操作。TRUNCATE TABLE 屬 DDL，不在鏈條保護範圍內，故需呼叫者擁有 ALTER 表權限才能執行。

案例二：動態 SQL 打破所有權鏈

建立接受參數化表名的預存程序

CREATE PROCEDURE dbo.CPP_SEL_CountRowsFromAnyTable
    @SchemaAndTable NVARCHAR(255)
AS
EXEC ('SELECT COUNT(1) FROM ' + @SchemaAndTable);

授與 TonyZhang 執行權限，但不給予 目標表 SELECT 權限。

執行結果：失敗，提示無權限存取目標表。

根因分析

• 動態 SQL（EXEC('...')、sp_executesql）不參與所有權鏈。
• 執行動態 SQL 時，引擎會以呼叫者原始身分檢查目標物件權限。

解決方案：指定執行上下文

步驟 1：建立高權限代理帳號

USE master; GO
CREATE LOGIN JackWang WITH PASSWORD = '123b3b4';
USE TestDb2; GO
CREATE USER JackWang;
GRANT SELECT ON OBJECT::dbo.tb_Demo TO JackWang;  -- 僅給最小必要權限

步驟 2：修改預存程序加上 WITH EXECUTE AS

ALTER PROCEDURE dbo.CPP_SEL_CountRowsFromAnyTable
    @SchemaAndTable NVARCHAR(255)
WITH EXECUTE AS 'JackWang'  -- 指定執行身分
AS
EXEC ('SELECT COUNT(1) FROM ' + @SchemaAndTable);

步驟 3：驗證

仍以 TonyZhang 登入執行 → 成功。引擎內部切換為 JackWang 身分執行動態 SQL，權限檢查通過。

關鍵知識點總結

下一步預告

後續將介紹 SQL Server 2008 新增的 透明資料加密 (TDE)，實現「靜態資料加密」零代碼修改部署。

版權聲明：本文版權由邀月與博客園共同所有，轉載請註明出處。