如何設定 User 只能 SELECT 某個 Table 或 View 的部分欄位

本文將介紹如何設定 User 只能 SELECT 某個 Table 或 View 的部分欄位。

今天在論壇上看到有網友在問有關 SQL Server 權限設定的問題,才讓我注意到原來權限設定可以細到針對欄位去做控制,用了 SQL Server 這麼久居然不知道可以這樣做,趕快來做一下筆記。

要設定某個 Table 或 View 中部分欄位你可以利用 SQL Server Management Studio 的 Object Explorer 來設定或是利用 GRANTDENY 的 T-SQL 敘述來設定。

本文以 Northwind 資料庫為例,您可以從 【 Northwind > Security > Users > 選擇要設定權限的使用者(本文以 NWUser 來示範)後按滑鼠右鍵 > Properties 】。

 

image

 

於 Database User 視窗中點選【 Securables > Search > 使用預設值 Specific objects 】按 OK 。

 

image

 

在 Select Objects 視窗中點選 Object Types ,本文以 Tables 來示範,因此於 Select Object Types 視窗中勾選 Tables 後按 OK 。

 

image

 

假設要設定的權限是 Region 資料表,於 Enter the object name to select 文字方塊中輸入 Region 後按 OK ,會出現 Multiple Objects Found 視窗中勾選 dbo.Region 再按 OK 。

 

image

 

點選 【Region > Permissions for dbo.Region > Select > Column Permissions > 勾選 Grant RegionDescription > 勾選 Deny RegionID】 後按 OK ,如此一來 NWUser 將只有對 Region 的 RegionDescription 欄位有 SELECT 權限,但沒有對 RegionID 進行 SELECT 的權限。

 

image

 

上述動作您也可以利用 T-SQL 來完成,指令碼如下:

 

   1:  use [Northwind]
   2:  GO
   3:  DENY SELECT ON [dbo].[Region] ([RegionID]) TO [NWUser]
   4:  GO
   5:  use [Northwind]
   6:  GO
   7:  GRANT SELECT ON [dbo].[Region] ([RegionDescription]) TO [NWUser] AS [dbo]
   8:  GO
   9:  use [Northwind]
  10:  GO
  11:  GRANT SELECT ON [dbo].[Region] ([RegionDescription]) TO [NWUser] AS [dbo]
  12:  GO
  13:  use [Northwind]
  14:  GO
  15:  DENY SELECT ON [dbo].[Region] ([RegionID]) TO [NWUser]
  16:  GO

 

我們利用 NWUser 使用者來測試上面所設定的權限,由下圖可見,若 NWUser 嘗試 SELECT 所有 Region 資料表中的所有欄位,將出現沒有 SELECT RegionID 權限的錯誤訊息,若只 SELECT RegionDescription 欄位則可正常存取,表示我們的設定正確無誤。

 

image

 

【參考資料】