萬盛學電腦網

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

SQL Server 2005 使用者權限精細控制:限制存取特定資料表與欄位

需求場景

建立登入 TestUser,僅允許存取 TestLog 資料庫中的 User 資料表,並可細化至欄位級 SELECT/INSERT/UPDATE/DELETE 權限。

操作步驟圖解

  1. 建立伺服器登入:
    SSMS → 「安全性」→「登入」→ 右鍵「新增登入」 → 輸入名稱、選「SQL Server 驗證」、設定密碼、預設資料庫選 TestLog。
    新增登入 一般頁籤設定
  2. 使用者對應:
    「使用者對應」頁籤 → 勾選 TestLog → 自動建立同名資料庫使用者、預設結構描述 dbo。
    使用者對應
  3. 設定資料表權限:
    展開 TestLog →「資料表」→ 右鍵 User →「屬性」→「權限」頁籤。
    資料表屬性
  4. 新增使用者與授權:
    點「搜尋」→「瀏覽」→ 選 TestUser → 確定。
    在權限格勾選 SELECT、INSERT、UPDATE、DELETE (依需求)。
    選擇使用者 權限列表
  5. 欄位級權限 (可選):
    點右下「欄位權限」→ 選特定欄位 → 勾選 SELECT 等。
    注意:若已授與表級 CONTROL 或 SELECT,欄位限制將失效。
  6. 驗證登入:以 TestUser 登入 SSMS,物件總管僅顯示 User 資料表。
    登入後僅見User表

關鍵注意事項

  1. 步驟 2 必須勾選目標資料庫,否則資料庫層級找不到使用者。
    未勾選資料庫導致找不到使用者
  2. 先確定建立登入/使用者,再設定物件權限。若在「安全性實體」中找不到新建使用者,通常是因未完成步驟 2 的「確定」。
  3. 刪除使用者層級:資料庫層刪除使用者 → 伺服器層登入仍保留;伺服器層刪除登入 → 會提示需先移除資料庫使用者。
    刪除提示
  4. 資料庫層「安全實體」提供更細粒度物件類型:結構描述、組件、非對稱金鑰、憑證、完整文字目錄等。
    安全實體物件類型

T-SQL 等效腳本 (自動化部署用)

-- 1. 建立登入
CREATE LOGIN TestUser WITH PASSWORD = 'StrongP@ssw0rd', DEFAULT_DATABASE = TestLog;

-- 2. 建立資料庫使用者
USE TestLog;
CREATE USER TestUser FOR LOGIN TestUser;

-- 3. 授與表級權限
GRANT SELECT, INSERT, UPDATE, DELETE ON dbo.User TO TestUser;

-- 4. (可選) 授與欄位級權限
GRANT SELECT ON dbo.User (Col1, Col2) TO TestUser;
DENY SELECT ON dbo.User (SensitiveCol) TO TestUser; -- 拒絕優先於授與
copyright © 萬盛學電腦網 all rights reserved