SQL Server 2008 預存程序加密與安全內容 (EXECUTE AS) 完整指南

前言

預存程序是資料庫應用的核心邏輯載體。本文探討兩大安全機制：

1. WITH ENCRYPTION：隱藏程式碼定義，防止直接檢視。
2. EXECUTE AS 子句：控制執行時的安全內容，實現最小權限原則。

一、預存程序加密 (WITH ENCRYPTION)

-- 建立測試表
USE testDb2; GO
CREATE TABLE dbo.tb_demo (id int NOT NULL, submitdate datetime NULL, commment nvarchar(200) NULL); GO
INSERT tb_demo SELECT 1024, GETDATE(), REPLICATE('A',100); WAITFOR DELAY '00:00:04';
INSERT tb_demo SELECT 1024, GETDATE(), REPLICATE('B',50); GO

-- 未加密程序
CREATE PROCEDURE CPP_test_Original AS SELECT * FROM tb_demo; GO

-- 加密程序
CREATE PROCEDURE CPP_test_Encryption WITH ENCRYPTION AS EXEC CPP_test_Original; GO

注意：加密前**務必備份原始碼**；加密僅隱藏定義，程序仍可被 EXECUTE、ALTER、DROP。

二、安全內容 (EXECUTE AS)

預設 EXECUTE AS CALLER 需呼叫者擁有底層物件權限。透過指定執行內容，僅需授與程序執行權限即可。

EXECUTE AS 選項對照

實戰範例：以低權限帳號執行 TRUNCATE (需較高權限)

-- 1. 建立刪除程序
CREATE PROCEDURE dbo.CPP_DEL_ALL_Tb_Demo AS DELETE dbo.tb_demo; GO

-- 2. 建立低權限登入/使用者 TonyZhang，僅授 EXEC 權限
USE master; GO
CREATE LOGIN TonyZhang WITH PASSWORD = '123b3b4'; GO
USE testDb2; GO
CREATE USER TonyZhang; GO
GRANT EXEC ON dbo.CPP_DEL_ALL_Tb_Demo TO TonyZhang; GO

-- TonyZhang 執行 → 成功 (DELETE 屬所有權鏈涵蓋範圍)
-- 若改為 TRUNCATE → 失敗 (TRUNCATE 不在所有權鏈內)

-- 3. 建立動態 SQL 程序，需跨資料表查詢
CREATE PROCEDURE dbo.CPP_SEL_CountRowsFromAnyTable @SchemaAndTable nvarchar(255)
AS EXEC ('SELECT COUNT(1) FROM ' + @SchemaAndTable); GO
GRANT EXEC ON dbo.CPP_SEL_CountRowsFromAnyTable TO TonyZhang; GO
-- TonyZhang 執行 → 失敗 (動態 SQL 不走所有權鏈，需目標表 SELECT 權限)

-- 4. 建立高權限帳號 JackWang，授目標表 SELECT
CREATE LOGIN JackWang WITH PASSWORD = '123b3b4'; GO
CREATE USER JackWang; GRANT SELECT ON dbo.tb_demo TO JackWang; GO

-- 5. 修改程序執行內容為 JackWang
ALTER PROCEDURE dbo.CPP_SEL_CountRowsFromAnyTable @SchemaAndTable nvarchar(255)
WITH EXECUTE AS 'JackWang'
AS EXEC ('SELECT COUNT(1) FROM ' + @SchemaAndTable); GO

-- TonyZhang 再次執行 → 成功 (以 JackWang 身分存取 tb_demo)

小結

1. WITH ENCRYPTION 保護智慧財產，但非絕對安全 (可逆向工程)，部署前備份原始碼。
2. EXECUTE AS 能精細控制權限，解決動態 SQL、TRUNCATE 等所有權鏈不涵蓋的場景。
3. 所有權鏈僅涵蓋 SELECT/INSERT/UPDATE/DELETE，**不包含 TRUNCATE、DDL、動態 SQL**。