MS SQL Server 基礎教程：資料庫安全性管理策略 — 視圖、預存程序、應用程式角色實戰

安全性管理策略總覽

SQL Server 提供多層次防護：OS → SQL Server → 資料庫 → 物件。善用視圖、預存程序、應用程式角色可構建深度防禦體系。

14.6.1 視圖作為安全機制：行級/列級存取控制

視圖是虛擬資料表，僅暴露定義查詢的結果集。授與視圖權限而不授與基底表權限，即可實現：

• **列級安全性**：視圖僅包含允許欄位 (如隱藏薪資、密碼欄)
• **行級安全性**：視圖 WHERE 條件過濾資料 (如各業務員僅看自己客戶)

範例：業務據點僅看自己的銷售資料

CREATE VIEW vw_Sales_ByOutlet
AS
SELECT * FROM sales
WHERE outlet_id = SUSER_SNAME();  -- 或對應應用程式傳入的據點代碼

14.6.3 視圖 + 權限組合：解決 SELECT * 權限困境

問題：使用者對 sales.payterms 無 SELECT 權限，若執行 SELECT * FROM sales 會報錯，但要求使用者逐一列出允許欄位不現實。

解法：建立排除敏感欄位的視圖，授與視圖 SELECT 權限：

CREATE VIEW vw_Sales_Safe
AS
SELECT sale_id, outlet_id, cust_id, amount, sale_date  -- 排除 payterms
FROM sales;

GRANT SELECT ON vw_Sales_Safe TO UserA;
-- UserA 即可執行 SELECT * FROM vw_Sales_Safe

14.6.4 預存程序作為安全機制：更深層封裝

使用者**完全無表/視圖權限**，僅擁有 SP EXECUTE 權限：

CREATE PROCEDURE usp_GetSalesSummary
AS
SELECT outlet_id, COUNT(*) AS cnt, SUM(amount) AS total
FROM sales
GROUP BY outlet_id;

GRANT EXECUTE ON usp_GetSalesSummary TO UserB;
-- UserB 執行 EXEC usp_GetSalesSummary 即可取得彙總資料

優勢對比

本章小結：安全性配置核心檢查清單

1. 確認認證模式 (Windows 整合優先)
2. 善用 guest 使用者與 public 角色 (最小權限原則)
3. 理解應用程式角色 (sp_setapprole) 在連線池環境下的可控性優勢
4. 敏感資料：視圖過濾欄位/列 → 預存程序封裝邏輯 → 僅授 EXEC 權限
5. 定期審核：sp_helprotect、fn_my_permissions、稽核日誌