萬盛學電腦網

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

SQL Server 建立登入、用戶、角色與權限授予完整實戰指南

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` 記錄權限變更與敏感存取

常用權限速查

遷移/同步登入 (保留 SID/密碼雜湊)

-- 來源伺服器執行 (產生建立登入腳本)
EXEC sp_help_revlogin;  -- Microsoft 官方腳本輸出 CREATE LOGIN ... WITH PASSWORD = 0x... HASHED, SID = 0x...

-- 目標伺服器執行輸出腳本,即可同步 SID/密碼,避免孤立用戶

遵循「登入→用戶→角色→Schema 授權」四步走,配合參數化腳本與審計,構建可追溯、可複現、最小權限的資料庫安全基線。

copyright © 萬盛學電腦網 all rights reserved
權限適用對象說明
SELECTTable/View/Table-Valued Function讀取資料
INSERT/UPDATE/DELETETable/View修改資料
EXECUTEStored Procedure/Scalar Function執行程式碼
REFERENCESTable建立外鍵引用
ALTERTable/View/Procedure修改結構/程式碼
CONTROL所有相當於擁有者權限
VIEW DEFINITION所有檢視元資料定義