SQL Server 安全性管理策略：視圖、預存程序、角色、應用程式角色多層防護

SQL Server 提供多層次安全性機制：作業系統 → 伺服器登入 → 資料庫使用者 → 結構描述/物件權限。本文說明如何結合視圖、預存程序、角色、應用程式角色建構纵深防禦體系。

1. 視圖作為安全機制

• 視圖 = 虛擬資料表 = 查詢結果集，使用者僅能存取視圖暴露的欄位/列。
• 授予 SELECT/INSERT/UPDATE/DELETE 權限於視圖，不授予基底表權限。
• 行級安全：CREATE VIEW vw_Sales_DeptA AS SELECT * FROM Sales WHERE DeptID = 'A'; → 授予 DeptA 使用者 SELECT ON vw_Sales_DeptA。
• 欄位級安全：CREATE VIEW vw_Emp_Public AS SELECT EmpID, Name, Dept FROM Employee; → 隱藏薪資/身分證等敏感欄位。

2. 視圖 + 權限組合範例

-- 基底表 sales 含 payterms 欄位，使用者 A 無 SELECT 權限
CREATE VIEW vw_Sales_NoPayterms AS
SELECT SaleID, CustID, Amount, SaleDate FROM sales;  -- 排除 payterms

GRANT SELECT ON vw_Sales_NoPayterms TO UserA;
-- UserA 可執行 SELECT * FROM vw_Sales_NoPayterms，無需知曉基底表結構

3. 預存程序作為安全機制

• 使用者無視圖/表直接權限，僅授予 EXECUTE ON usp_GetSales。
• 預存程序內執行查詢/異動，利用擁有權鏈 存取基底表 (建立者與基底表擁有者相同)。
• 優點：邏輯封裝、參數驗證、稽核記錄、防止臨時 SQL 注入。

CREATE PROCEDURE usp_GetSales
AS
SELECT SaleID, CustID, Amount, SaleDate FROM sales;
GO
GRANT EXECUTE ON usp_GetSales TO UserA;

4. 角色基礎權限管理

• 伺服器角色：sysadmin, securityadmin, dbcreator 等，跨資料庫權限。
• 資料庫角色：db_datareader, db_datawriter, db_ddladmin 等固定角色；自訂角色 CREATE ROLE AppReaders; GRANT SELECT ON Schema::Sales TO AppReaders;。
• 應用程式角色：sp_setapprole 'AppRole', 'Password'，應用程式連線後啟用，完全取代使用者權限，防止使用者透過其他工具 (SSMS/Excel) 繞過應用邏輯存取資料。

5. 關鍵設定檢查清單

1. 確認驗證模式：混合模式 (需 SQL 登入) 或僅 Windows (較安全)。
2. 停用/重命名 sa、設定強密碼、啟用密碼原則。
3. 移除 BUILTIN\Administrators 自動 sysadmin (SQL 2008+ 預設不加入)。
4. guest 使用者：僅在 master/msdb 啟用，其他資料庫 REVOKE CONNECT。
5. public 角色：預設權限最小化，不授予額外權限。
6. 啟用稽核：登入失敗、結構變更、敏感資料存取 (SQL Server Audit / Extended Events)。
7. 定期權限審計：sys.server_permissions, sys.database_permissions、腳本輸出所有 GRANT/DENY。

小結

「最小權限原則」+「視圖/預存程序封裝」+「角色分層」+「應用程式角色隔離」= 纵深防禦。安全性非一次設定，需持續審計與演練。