萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> SQL Server 教學

建構 SQL Server 安全大門:驗證、授權與角色架構實戰設計

核心概念區隔

  • 驗證:確認使用者身分 (登入時發生)。
  • 授權:決定使用者能做什麼 (存取資料/執行命令時發生)。

一、驗證模式選擇策略

SQL Server 驗證

  • 優點:Enterprise Manager 設定簡單、跨網域/工作群組無相依。
  • 缺點:僅對單一伺服器有效,多伺服器環境管理惡夢;每資料庫需個別授權,腳本維護成本高;不適用超過 25 用戶或權限頻變環境。

Windows NT/2000 驗證 (首選)

  • 域控制器核發 存取權杖,含使用者 SID 與群組 SID 陣列。
  • SQL Server 僅依據 SID 授權,不在乎權杖由誰核發 (SQL 2000/7.0/Win2K/NT 皆同)。
  • 優勢:單一認證來源、Kerberos/NTLMv2、密碼原則、群組授權、憑證委派、前端免存密碼。

二、Web 環境驗證架構

典型錯誤:Web 應用內嵌 sa 或單一 SQL 登入連線字串,導致無法稽核、無法區分使用者、權限過度集中。

IIS 5.0/4.0 四種驗證選項

結論:Web 環境應優先採用 NT 整合驗證,配合 IIS 代理,實現端到端身分傳遞與稽核。

三、全域群組架構設計

應用導向群組 (每應用一組)

會計系統範例:DataEntryOperators、AccountingManagers、ReportWriters、Accountants、Auditors、FinanceManagers。命名慣例:AppName_Role。

基礎設施群組 (跨應用共用)

  • SQL Server Administrators → sysadmin
  • SQL Server Users → 僅登入權限,預設 DB = master,無資料庫存取
  • SQL Server Denied Users → 明確拒絕登入 (拒絕優先)
  • SQL Server DB Creators → dbcreator
  • SQL Server Security Operators → securityadmin
  • SQL Server Database Security Operators → db_securityadmin
  • SQL Server Developers → db_owner (開發/測試環境)
  • DB_Name Users → 個別資料庫存取群組

群組授權實作 (需 Query Analyzer + 系統預存程序)

-- 伺服器角色授權
EXEC sp_addsrvrolemember 'DOMAIN\SQL Server Administrators', 'sysadmin';
EXEC sp_addsrvrolemember 'DOMAIN\SQL Server DB Creators', 'dbcreator';
EXEC sp_addsrvrolemember 'DOMAIN\SQL Server Security Operators', 'securityadmin';

-- Model 資料庫預設角色 (新資料庫自動繼承)
EXEC sp_addrolemember 'db_securityadmin', 'DOMAIN\SQL Server Security Operators';
EXEC sp_addrolemember 'db_owner', 'DOMAIN\SQL Server Developers';

⚠️ Enterprise Manager 無法 直接授權未登入的 NT 群組,必須用 T-SQL。

四、資料庫層授權策略

  • 權限授予 資料庫角色,而非直接授予群組/使用者 → 未來切換驗證模式無需重設權限。
  • sp_grantdbaccess 授權群組存取資料庫 (無對應 sp_denydbaccess)。
  • 拒絕資料庫存取:建立 DB_Name Denied Users 群組 → 授權存取 → 加入 db_denydatareader、db_denydatawriter 角色。
  • 物件權限:GRANT SELECT/EXEC ON Schema.Object TO Role。

結語

「驗證用 Windows/AD 群組,授權用資料庫角色」是可擴充、可稽核、可維運的企業級 SQL Server 安全架構基石。配合 Model 資料庫模板化新資料庫權限,實現「一次設計、處處生效」。

關鍵字: SQL Server 安全架構、驗證模式、Windows 整合驗證、IIS、NTLM、Kerberos、全域群組、資料庫角色、sp_addsrvrolemember、sp_addrolemember、Model 資料庫、最小權限

copyright © 萬盛學電腦網 all rights reserved
方法機制適用場景注意事項
匿名 NT 帳戶每站/虛擬目錄建立專用匿名帳戶,授予適當 SQL 權限公開唯讀網站需妥善管理匿名帳戶密碼
Basic 驗證瀏覽器彈窗輸入帳密,IIS 驗證 NT/網域帳戶,傳遞身分到 SQL需識別使用者的內部網站密碼明文傳輸,必須搭配 SSL
NT 整合驗證 (NTLM/Kerberos)IE 自動送出登入電腦身分,IIS 代理存取 SQL內部網域環境、單一登入 (SSO)需 IE 3.0+、信任關系正確
用戶端憑證對應個人數位憑證映射到 NT 帳戶,取代 Challenge/Response高安全需求、PKI 環境需簽發/管理憑證、IIS 對應工具設定