如何限制資料庫使用者無法讀取某些欄位

2012-09-14

4072
0
SQL Server

本文將介紹當您想要限制資料庫使用者無法讀取某些欄位，可能的幾種作法。

【情境說明】

在論壇上有網友提到如何限制資料行的 select 權限，要達到這個目的方法至少有下列幾種：

DENY 資料行的 SELECT 權限。

拒絕所有對基礎資料表的 SELECT 權限，然後建立 View、Stored Procedure 或 Table-Valued Function 來自訂資料表要回傳的資料行。

本文就針對上述兩種方法進行介紹。

【前置作業】

首先我們先建立一個名稱為 NWUser 的 Database Engine Login 及資料庫使用者，程式碼如下：

USE [master]
GO
CREATE LOGIN [NWUser] WITH PASSWORD=N'NWUser', DEFAULT_DATABASE=[Northwind], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
USE [Northwind]
GO
CREATE USER [NWUser] FOR LOGIN [NWUser]
GO
USE [Northwind]
GO
ALTER ROLE [db_datareader] ADD MEMBER [NWUser]
GO
		

【DENY 資料行的 SELECT 權限】

下列的程式碼示範利用第五列的 DENY 敘述來拒絕 NWUser 存取 Region 資料表的 RegionID 資料行。

use Northwind
go
  
--拒絕NWUser查詢Region資料表的RegionID
deny select on Region(RegionID) to NWUser
  
--切換使用者身分
execute as login = 'NWUser'
go
  
--確認使用者身分
select USER_NAME() db_user,SUSER_NAME() login_name
  
--因為拒絕查詢RegionID欄位，因此SELECT ALL會出現沒有權限的訊息
select *
from Region
/*
訊息 230，層級 14，狀態 1，行 6
結構描述 'dbo'，資料庫 'Northwind'，物件 'Region' 的資料行 'RegionID' 沒有 SELECT 權限。
*/
  
select RegionDescription
from Region
  
/*
回傳 RegionDescription 欄位的內容
Eastern                                           
Western                                           
Northern                                          
Southern                                          
*/
  
--將執行內容切換回最後一個 EXECUTE AS 陳述式的呼叫者。
revert
go
		

【利用 View、Stored Procedure 或 Table-Valued Function 來自訂資料表要回傳的資料行】

除了上述利用 DENY 敘述來進行權限控管外，您可以利用下列的 T-SQL 來拒絕 NWUser 對 Region 資料表的 SELECT 權限。

use Northwind
go
  
--拒絕NWUser查詢Region資料表
deny select on Region to NWUser
  
--切換使用者身分
execute as login = 'NWUser'
go
  
--確認使用者身分
select USER_NAME() db_user,SUSER_NAME() login_name
  
--因為拒絕查詢Region資料表，因此SELECT時會出現沒有權限的訊息
select *
from Region
/*
訊息 230，層級 14，狀態 1，行 6
訊息 229，層級 14，狀態 5，行 6
結構描述 'dbo'，資料庫 'Northwind'，物件 'Region' 沒有 SELECT 權限。
*/
  
--將執行內容切換回最後一個 EXECUTE AS 陳述式的呼叫者。
revert
go
		

接著您可以建立 View 來回傳您想要提供給 NWUser 查詢的結果，接著再 GRANT NWUSER 對於 View 的 SELECT 權限，程式碼如下：

USE Northwind
GO
  
--若 View 已經存在則先移除
IF OBJECT_ID('dbo.v_GetRegion') IS NOT NULL
    DROP VIEW dbo.v_GetRegion
GO
  
--建立 View 並指定回傳 Region 資料表的 RegionDescription 資料行
CREATE VIEW dbo.v_GetRegion
AS
    SELECT RegionDescription
    FROM dbo.Region
  
GO
  
--拒絕NWUser查詢Region資料表
DENY SELECT ON Region TO NWUser
GO
  
--允許NWUser查詢v_GetRegion檢視
GRANT SELECT ON dbo.v_GetRegion TO NWUser
GO
  
  
--切換使用者身分
EXECUTE AS LOGIN = 'NWUser'
GO
  
--確認使用者身分
SELECT USER_NAME() db_user,SUSER_NAME() login_name
GO
  
--以 NWUSER 身分對 REGION 資料表進行 SELECT，將會出現權限下列的錯誤訊息：
/*
訊息 229，層級 14，狀態 5，行 3
結構描述 'dbo'，資料庫 'Northwind'，物件 'Region' 沒有 SELECT 權限。
*/
SELECT *
FROM REGION
  
GO
  
--以 NWUSER 身分對 v_GetRegion 檢視進行 SELECT，則可以正確回傳 Region 資料表的 RegionDescription資料行
/*
Eastern                                           
Western                                           
Northern                                          
Southern                                          
*/
  
SELECT *
FROM dbo.v_GetRegion
  
GO
  
--將執行內容切換回最後一個 EXECUTE AS 陳述式的呼叫者。
REVERT
GO
		

再者您也可以利用 Stored Procedure 來指定想要回傳的查詢結果，同樣的再針對 Stored Procedure 的 EXECUTE 權限 GRANT 給 NEUSER，程式碼如下：

USE Northwind
GO
  
--若 Stored Procedure 已經存在則先移除
IF OBJECT_ID('dbo.usp_GetRegion') IS NOT NULL
    DROP PROCEDURE dbo.usp_GetRegion
GO
  
--建立 Stored Procedure 並指定回傳 Region 資料表的 RegionDescription 資料行
CREATE PROCEDURE dbo.usp_GetRegion
AS
    SET NOCOUNT ON
  
    SELECT RegionDescription
    FROM dbo.Region
  
GO
  
--拒絕NWUser查詢Region資料表
DENY SELECT ON Region TO NWUser
GO
  
--允許NWUser執行usp_GetRegion預存程序
GRANT EXECUTE ON dbo.usp_GetRegion TO NWUser
GO
  
  
--切換使用者身分
EXECUTE AS LOGIN = 'NWUser'
GO
  
--確認使用者身分
SELECT USER_NAME() db_user,SUSER_NAME() login_name
GO
  
--以 NWUSER 身分對 REGION 資料表進行 SELECT，將會出現權限下列的錯誤訊息：
/*
訊息 229，層級 14，狀態 5，行 3
結構描述 'dbo'，資料庫 'Northwind'，物件 'Region' 沒有 SELECT 權限。
*/
SELECT *
FROM REGION
  
GO
  
--以 NWUSER 身分對執行 usp_GetRegion 預存程序，則可以正確回傳 Region 資料表的 RegionDescription資料行
/*
Eastern                                           
Western                                           
Northern                                          
Southern                                          
*/
  
EXECUTE dbo.usp_GetRegion
  
GO
  
--將執行內容切換回最後一個 EXECUTE AS 陳述式的呼叫者。
REVERT
GO
		

最後，您也可以透過建立 Table-Valued Function 來自訂對基礎資料表的查詢結果，由於 Table-Valued Function 概念上跟 Table 是相同的，因此在 GRANT 權限時，必須賦予 NWUser 對 Function 的 SELECT 權限。

USE Northwind
GO
  
--若 Table-Valued Function 已經存在則先移除
IF OBJECT_ID('dbo.fn_GetRegion','IF') IS NOT NULL
    DROP FUNCTION dbo.fn_GetRegion
GO
  
--建立 Table-Valued Function 並指定回傳 Region 資料表的 RegionDescription 資料行
CREATE FUNCTION dbo.fn_GetRegion()
RETURNS TABLE
AS
   RETURN (SELECT RegionDescription
    FROM dbo.Region)
GO
  
--拒絕NWUser查詢Region資料表
DENY SELECT ON Region TO NWUser
GO
  
--允許NWUser對fn_GetRegion function 進行 SELECT 動作
GRANT SELECT ON dbo.fn_GetRegion TO NWUser
GO
  
  
--切換使用者身分
EXECUTE AS LOGIN = 'NWUser'
GO
  
--確認使用者身分
SELECT USER_NAME() db_user,SUSER_NAME() login_name
GO
  
--以 NWUSER 身分對 REGION 資料表進行 SELECT，將會出現權限下列的錯誤訊息：
/*
訊息 229，層級 14，狀態 5，行 3
結構描述 'dbo'，資料庫 'Northwind'，物件 'Region' 沒有 SELECT 權限。
*/
SELECT *
FROM REGION
  
GO
  
--以 NWUSER 身分對SELECT fn_GetRegion Function，則可以正確回傳 Region 資料表的 RegionDescription資料行
/*
Eastern                                           
Western                                           
Northern                                          
Southern                                          
*/
  
SELECT *
FROM dbo.fn_GetRegion()
  
GO
  
--將執行內容切換回最後一個 EXECUTE AS 陳述式的呼叫者。
REVERT
GO
		

【結論】

上述四種方法都可以達到控管使用者對基礎資料表查詢的目的，但實務上基於維護方便以及安全性考量，比較建議利用 View、Stored Procedure 或是 Table-Valued Function 來對，而避免使用者直接存取基礎資料表。

【參考資料】

- DENY (Transact-SQL)
- GRANT (Transact-SQL)

回首頁

積沙成塔

CONNECT - SHARE - LEARN

如何限制資料庫使用者無法讀取某些欄位

標籤雲

系列文章

Award

Badge

參觀人數