SQL Server 安全模型分兩層:**伺服器層級登入** 與 **資料庫層級用戶/角色**。本文提供從建立登入到精細授權的標準化 T-SQL 腳本,涵蓋含參數化、最小權限原則、角色設計、審計追蹤的生產級最佳實踐。
核心概念速覽
| 層級 | 主體 | T-SQL 建立 | 權限範圍 |
| 伺服器 | Login (登入) | `CREATE LOGIN` | 連線伺服器、伺服器角色 (sysadmin 等) |
| 資料庫 | User (用戶) | `CREATE USER` | 資料庫物件權限 (SELECT/INSERT/EXEC 等) |
| 資料庫 | Role (角色) | `CREATE ROLE` | 權限集合,用戶加入角色繼承權限 |
標準化建立腳本 (參數化、冪等)
-- ===== 參數區 =====
DECLARE @LoginName SYSNAME = N'app_user';
DECLARE @Pwd NVARCHAR(128) = N'Str0ng!P@ssw0rd';
DECLARE @DBName SYSNAME = N'AppDB';
DECLARE @DefSchema SYSNAME = N'dbo';
-- ===== 1. 建立登入 (伺服器層) =====
IF NOT EXISTS (SELECT 1 FROM sys.server_principals WHERE name = @LoginName)
BEGIN
CREATE LOGIN @LoginName WITH PASSWORD = @Pwd,
DEFAULT_DATABASE = @DBName,
CHECK_POLICY = ON, CHECK_EXPIRATION = ON;
PRINT 'Login created: ' + @LoginName;
END
-- ===== 2. 建立資料庫用戶 (映射登入) =====
EXEC('USE [' + @DBName + '];
IF NOT EXISTS (SELECT 1 FROM sys.database_principals WHERE name = ''' + @LoginName + ''')
BEGIN
CREATE USER [' + @LoginName + '] FOR LOGIN [' + @LoginName + '] WITH DEFAULT_SCHEMA = [' + @DefSchema + '];
PRINT ''User created in '' + DB_NAME();
END');
-- ===== 3. 建立應用角色 (權限容器) =====
EXEC('USE [' + @DBName + '];
IF NOT EXISTS (SELECT 1 FROM sys.database_principals WHERE name = ''app_role'' AND type = ''R'')
BEGIN
CREATE ROLE app_role;
-- 核心業務權限
GRANT SELECT, INSERT, UPDATE, DELETE ON SCHEMA::' + @DefSchema + ' TO app_role;
-- 執行預存程序權限
GRANT EXECUTE ON SCHEMA::' + @DefSchema + ' TO app_role;
PRINT ''Role app_role created and granted.'';
END
-- 4. 用戶加入角色
ALTER ROLE app_role ADD MEMBER [' + @LoginName + '];
PRINT ''User added to role.'';');
-- ===== 5. 物件級精細授權範例 (如需突破角色) =====
-- GRANT SELECT ON dbo.SensitiveTable TO app_user; -- 列級:GRANT SELECT ON dbo.Tbl (Col1, Col2) TO app_user;
-- DENY SELECT ON dbo.AuditLog TO app_user; -- 明確拒絕優先於 GRANT
權限體系設計原則
- **最小權限**:應用帳號僅獲 `app_role` 定義的權限,嚴禁 `db_owner`/`db_ddladmin`
- **角色分層**:`app_readonly` (SELECT) → `app_readwrite` (DML) → `app_admin` (DDL/EXECUTE/SECURITYADMIN)
- **Schema 隔離**:業務模組分 Schema (`Sales`、`HR`、`Fin`),角色授權至 Schema 而非單表
- **拒絕優先**:`DENY` > `GRANT`,用於敏感表/欄位隔離
- **審計**:`CREATE SERVER AUDIT` + `DATABASE AUDIT SPECIFICATION` 記錄權限變更與敏感存取
常用權限速查
| 權限 | 適用對象 | 說明 |
| SELECT | Table/View/Table-Valued Function | 讀取資料 |
| INSERT/UPDATE/DELETE | Table/View | 修改資料 |
| EXECUTE | Stored Procedure/Scalar Function | 執行程式碼 |
| REFERENCES | Table | 建立外鍵引用 |
| ALTER | Table/View/Procedure | 修改結構/程式碼 |
| CONTROL | 所有 | 相當於擁有者權限 |
| VIEW DEFINITION | 所有 | 檢視元資料定義 |
遷移/同步登入 (保留 SID/密碼雜湊)
-- 來源伺服器執行 (產生建立登入腳本)
EXEC sp_help_revlogin; -- Microsoft 官方腳本輸出 CREATE LOGIN ... WITH PASSWORD = 0x... HASHED, SID = 0x...
-- 目標伺服器執行輸出腳本,即可同步 SID/密碼,避免孤立用戶
遵循「登入→用戶→角色→Schema 授權」四步走,配合參數化腳本與審計,構建可追溯、可複現、最小權限的資料庫安全基線。
copyright ©
萬盛學電腦網 all rights reserved