實作資料列層級安全性

已完成

數據列層級安全性 （RLS） 不會使用加密，而且會在資料庫層級運作，以根據群組成員資格或授權內容使用安全策略來限制對數據表的存取。 此功能相當於 WHERE 子句。

安全性原則會叫用內嵌資料表值函式，以保護對資料表中資料列的存取。

根據使用者的屬性，述詞會判斷該使用者是否具有相關資訊的存取權。 當您對資料表執行查詢時，安全性原則會套用述詞函式。 根據商務需求，RLS 可以像 WHERE CustomerId = 29 一樣簡單或一樣複雜。

資料列層級安全性支援兩種類型的安全性原則：

• 篩選述詞 - 限制違反述詞的資料存取。

  Access	定義
  選取	無法檢視已篩選的資料列。
  更新	無法更新已篩選的資料列。
  刪除	無法刪除已篩選的資料列。
  插入	不適用。

• 封鎖述詞 - 限制違反述詞的資料變更。

  Access	定義
  插入後	防止使用者插入具有違反述詞之值的資料列。
  更新後	防止使用者將資料列更新為違反述詞的值。
  更新前	防止使用者更新目前違反述詞的資料列。
  刪除前	如果資料列違反述詞，則會封鎖刪除作業。

因為存取控制是在資料庫層級設定並套用，所以應用程式變更會非常少 (如果有的話)。 此外，使用者可以直接存取資料表，並可查詢自己的資料。

資料列層級安全性會以三個主要步驟實作：

1. 建立您想要隔離存取權的使用者或群組。
2. 建立內嵌數據表值函式，以根據定義的述詞篩選結果。
3. 為資料表建立安全性原則，並指派先前建立的函式。

下列 T-SQL 命令示範如何在依租戶隔離使用者存取的情境中使用 RLS：

-- Create supporting objects for this example
CREATE TABLE [Sales] (SalesID INT, 
    ProductID INT, 
    TenantName NVARCHAR(10), 
    OrderQtd INT, 
    UnitPrice MONEY)
GO

INSERT INTO [Sales]  VALUES (1, 3, 'Tenant1', 5, 10.00);
INSERT INTO [Sales]  VALUES (2, 4, 'Tenant1', 2, 57.00);
INSERT INTO [Sales]  VALUES (3, 7, 'Tenant1', 4, 23.00);
INSERT INTO [Sales]  VALUES (4, 2, 'Tenant2', 2, 91.00);
INSERT INTO [Sales]  VALUES (5, 9, 'Tenant3', 5, 80.00);
INSERT INTO [Sales]  VALUES (6, 1, 'Tenant3', 5, 35.00);
INSERT INTO [Sales]  VALUES (7, 3, 'Tenant4', 8, 11.00);

-- View all the rows in the table  
SELECT * FROM Sales;

接下來，建立使用者，並授與他們 Sales 資料表的存取權。 在此範例中，每位使用者都會負責特定租用戶。 TenantAdmin 使用者擁有可以看到所有租用戶資料的存取權。

CREATE USER [TenantAdmin] WITH PASSWORD = '<strong password>'
GO
CREATE USER [Tenant1] WITH PASSWORD = '<strong password>'
GO
CREATE USER [Tenant2] WITH PASSWORD = '<strong password>'
GO
CREATE USER [Tenant3] WITH PASSWORD = '<strong password>'
GO
CREATE USER [Tenant4] WITH PASSWORD = '<strong password>'
GO

GRANT SELECT ON [Sales] TO [TenantAdmin]
GO
GRANT SELECT ON [Sales] TO [Tenant1]
GO
GRANT SELECT ON [Sales] TO [Tenant2]
GO
GRANT SELECT ON [Sales] TO [Tenant3]
GO
GRANT SELECT ON [Sales] TO [Tenant4]
GO

接下來，我們將建立新的結構描述、內嵌資料表值函式，並將新函式的存取權授與使用者。 WHERE @TenantName = USER_NAME() OR USER_NAME() = 'TenantAdmin' 述詞會評估執行查詢的使用者名稱是否符合 TenantName 資料行值。

CREATE SCHEMA sec;  
GO  

--Create the filter predicate

CREATE FUNCTION sec.tvf_SecurityPredicatebyTenant(@TenantName AS NVARCHAR(10))  
    RETURNS TABLE  
WITH SCHEMABINDING  
AS  
    RETURN	SELECT 1 AS result
			WHERE @TenantName = USER_NAME() OR USER_NAME() = 'TenantAdmin';  
GO

--Grant users access to inline table-valued function

GRANT SELECT ON sec.tvf_SecurityPredicatebyTenant TO [TenantAdmin]
GO
GRANT SELECT ON sec.tvf_SecurityPredicatebyTenant TO [Tenant1]
GO
GRANT SELECT ON sec.tvf_SecurityPredicatebyTenant TO [Tenant2]
GO
GRANT SELECT ON sec.tvf_SecurityPredicatebyTenant TO [Tenant3]
GO
GRANT SELECT ON sec.tvf_SecurityPredicatebyTenant TO [Tenant4]
GO

--Create security policy and add the filter predicate
CREATE SECURITY POLICY sec.SalesPolicy  
ADD FILTER PREDICATE sec.tvf_SecurityPredicatebyTenant(TenantName) ON [dbo].[Sales]
WITH (STATE = ON);  
GO

此時，我們已準備好測試存取權：

EXECUTE AS USER = 'TenantAdmin';  
SELECT * FROM dbo.Sales;
REVERT;  
  
EXECUTE AS USER = 'Tenant1';  
SELECT * FROM dbo.Sales;
REVERT;  
  
EXECUTE AS USER = 'Tenant2';  
SELECT * FROM dbo.Sales;
REVERT;

EXECUTE AS USER = 'Tenant3';  
SELECT * FROM dbo.Sales;
REVERT;

EXECUTE AS USER = 'Tenant4';  
SELECT * FROM dbo.Sales;
REVERT;

TenantAdmin 使用者應該會看到所有資料列。 Tenant1、Tenant2、Tenant3 和 Tenant4 用戶應該只會看到自己的數據列。

如果您使用 WITH (STATE = OFF); 更改安全性原則，您會發現使用者看得到所有資料列。

注意

如果攻擊者使用特製 WHERE 子句和除以零錯誤等方式撰寫查詢，在 WHERE 條件成立時強制將其列為例外狀況，就會有資訊外洩的風險。 這也稱為旁路攻擊。 使用數據列層級安全性時，限制使用者執行非計劃性查詢的能力是明智的。

使用案例

資料列層級安全性適用於許多案例，包括：

• 當您需要在資料列層級隔離部門存取時。
• 當您需要將客戶的資料存取限制為只能存取與其公司相關的資料時。
• 當您需要限制合規性用途的存取時。

最佳做法

以下是實作 RLS 時要考慮的一些最佳做法：

• 為述詞函式和安全策略建立個別的架構。
• 避免述詞函式中的類型轉換。
• 避免在述詞函式中使用過多的數據表聯結和遞歸。