SQL Server 2005 執行內容切換：EXECUTE AS 實戰與所有權鏈機制

前言

SQL Server 2005 引入 EXECUTE AS 陳述式，取代 SQL 2000 受限的 SETUSER，支援批次、預存程序、觸發器、函數層級的執行內容切換，配合所有權鏈機制，實現細粒度權限委派與安全性提升。

核心概念：所有權鏈

• 當使用者執行預存程序，SQL Server 比較程序擁有者與參考物件擁有者
• 若擁有者相同 → 不檢查參考物件權限（鏈條完整）
• 若擁有者不同 → 需要呼叫者具備參考物件的明確權限
• 預設：dbo 擁有的物件形成天然所有權鏈

EXECUTE AS 語法與模式

1. 模組定義時指定（CREATE/ALTER 時）

CREATE PROCEDURE usp_InsertData
WITH EXECUTE AS { CALLER | SELF | OWNER | 'user_name' }
AS ...

2. 陳述式層級切換（批次中動態）

EXECUTE AS USER = 'TargetUser'  -- 或 LOGIN = 'TargetLogin'
-- 此處權限為 TargetUser
REVERT  -- 返回上一層執行內容（支援巢狀）

完整實戰範例：低權限使用者透過高權限程序寫入受保護表

環境建立

-- 清理舊物件
IF OBJECT_ID('usp_InsertMyTable','P') IS NOT NULL DROP PROCEDURE usp_InsertMyTable
IF OBJECT_ID('TableOwnerSchema.MyTable','U') IS NOT NULL DROP TABLE TableOwnerSchema.MyTable
IF EXISTS (SELECT * FROM sys.schemas WHERE name = 'TableOwnerSchema') DROP SCHEMA TableOwnerSchema
IF EXISTS (SELECT * FROM sys.database_principals WHERE name = 'BaseUser') DROP USER BaseUser
IF EXISTS (SELECT * FROM sys.server_principals WHERE name = 'BaseUser') DROP LOGIN BaseUser
IF EXISTS (SELECT * FROM sys.database_principals WHERE name = 'TableOwner') DROP USER TableOwner
IF EXISTS (SELECT * FROM sys.server_principals WHERE name = 'TableOwner') DROP LOGIN TableOwner
GO

-- 建立登入與使用者（關閉密碼策略方便測試）
CREATE LOGIN BaseUser WITH PASSWORD = 'baseuser', DEFAULT_DATABASE = [master], CHECK_EXPIRATION = OFF, CHECK_POLICY = OFF
CREATE USER BaseUser FOR LOGIN BaseUser
CREATE LOGIN TableOwner WITH PASSWORD = 'tableowner', DEFAULT_DATABASE = [master], CHECK_EXPIRATION = OFF, CHECK_POLICY = OFF
CREATE USER TableOwner FOR LOGIN TableOwner
GO

-- 建立 Schema，擁有者為 TableOwner
CREATE SCHEMA TableOwnerSchema AUTHORIZATION TableOwner
GO

-- 啟用登入
ALTER LOGIN TableOwner ENABLE
ALTER LOGIN BaseUser ENABLE
GO

-- 授予 TableOwner 建表權限
GRANT CREATE TABLE TO TableOwner
GO

以 TableOwner 身分建立資料表

EXECUTE AS USER = 'TableOwner'
GO
SELECT SESSION_USER  -- 確認目前為 TableOwner
GO

CREATE TABLE TableOwnerSchema.MyTable (Field1 INT)
GO

REVERT
GO
SELECT SESSION_USER  -- 返回原始登入
GO

建立 EXECUTE AS 'TableOwner' 的預存程序

CREATE PROCEDURE usp_InsertMyTable
WITH EXECUTE AS 'TableOwner'
AS
BEGIN
    INSERT INTO TableOwnerSchema.MyTable (Field1) VALUES (8)
END
GO

授權 BaseUser 執行程序

GRANT EXECUTE ON usp_InsertMyTable TO BaseUser
GO

測試：BaseUser 成功寫入無直接權限的表

EXECUTE AS USER = 'BaseUser'
GO
EXEC usp_InsertMyTable  -- 成功！執行內容為 TableOwner
GO
REVERT
GO

對比：移除 EXECUTE AS 後 BaseUser 失敗

ALTER PROCEDURE usp_InsertMyTable
AS
BEGIN
    INSERT INTO TableOwnerSchema.MyTable (Field1) VALUES (8)
END
GO

EXECUTE AS USER = 'BaseUser'
GO
EXEC usp_InsertMyTable  -- 失敗：BaseUser 無 INSERT 權限
-- 訊息 229，層級 14，狀態 5：INSERT 權限被拒絕
GO
REVERT
GO

進階應用：TRUNCATE TABLE 權限委派

TRUNCATE TABLE 無法直接 GRANT，傳統做法需給 ALTER 權限（過大）。

CREATE PROCEDURE usp_TruncateTarget
WITH EXECUTE AS 'TableOwner'
AS
    TRUNCATE TABLE TableOwnerSchema.MyTable
GO

GRANT EXECUTE ON usp_TruncateTarget TO BaseUser
-- BaseUser 現在可透過程序截斷表，無需 ALTER 權限

關鍵權限需求

最佳實務與注意事項

• 最小權限原則： 僅委派必要操作，避免使用 EXECUTE AS 'dbo' 或 sa
• 稽核追蹤： SESSION_USER, ORIGINAL_LOGIN() 可區分原始與當前身分
• 巢狀切換： 多層 EXECUTE AS 需配對 REVERT，建議在 TRY/CATCH 中確保 REVERT 執行
• 模組簽署替代方案： 使用憑證/非對稱金鑰簽署模組，授予簽署者權限，比 EXECUTE AS 更安全、可稽核
• 跨資料庫： 需啟用 TRUSTWORTHY 或使用模組簽署

結語

EXECUTE AS 是 SQL Server 2005 權限模型的重要進化，解決了「低權限使用者需執行高權限操作」的經典難題。配合所有權鏈、模組簽署、最小權限原則，能構建安全、可維護的資料庫存取架構。記住：能用所有權鏈解決就不用 EXECUTE AS，能用模組簽署就不用 EXECUTE AS 'dbo'。

1. 上一篇文章： SQL Server 2005 資料表分區技術深度解析與實戰
2. 下一篇文章： SQL Server 2005 tempdb 變革深度解析：效能優化與監控新功能

關於SqlServer

SQL Server 千萬級分頁存儲過程：KEYSET 分頁與動態 SQL 實現高效能翻頁

面對千萬列資料表，傳統 ROW_NUMBER() OVER

SQL Server 2016 Tempdb 重大增強：安裝精靈自動配置、統一區分配與同步成長

安裝精靈內建 Tempdb 最佳化配置 SQL Server

ARP 攻擊原理深度解析與完整防禦方案

ARP 協議基礎與攻擊原理 ARP（Address Reso

PowerBuilder 6.0 實戰教學 (2-5)：建立第二個視窗物件與資料視窗控制項綁定

學習目標 完成第二個視窗 TELEPHONE-WINDOW2