資料列層級安全性

發行項
09/16/2024

適用於：SQL Server Azure SQL 資料庫 Azure SQL 受控執行個體 Azure Synapse Analytics Microsoft Fabric 中的 SQL 分析端點 Microsoft Fabric 中的倉儲

資料列層級安全性的裝飾圖形。

資料列層級安全性 (RLS) 讓您能夠使用群組成員資格或執行內容，對資料庫資料表中資料列的存取。

資料列層級安全性簡化您的應用程式中安全性的設計和編碼。 RLS 可協助您在資料列存取上進行實作限制。例如，您可以確保員工只能存取與其部門相關的資料列。另一個範例是將客戶的資料存取限制為只能存取與其公司相關的資料。

存取限制邏輯是位於資料庫層，而不是離開這些資料，到另一個應用程式層。資料庫系統會在每次於任何層嘗試存取該資料時套用存取限制。這可藉由縮小安全性系統的接觸區，讓安全性系統更加可靠和健全。

使用 CREATE SECURITY POLICY Transact-SQL 陳述式，以及作為內嵌資料表值函式建立的述詞來實作 RLS。

SQL Server 2016 (13.x) 首次引進資料列層級安全性。

注意

本文著重於 SQL Server 和 Azure SQL 平台。關於 Microsoft Fabric，請參閱 Microsoft Fabric 中的資料列層級安全性。

描述

資料列層級安全性 (RLS) 支援兩種類型的安全性述詞：

篩選述詞以無訊息方式篩選讀取作業 (SELECT、UPDATE 及 DELETE) 可用的資料列。
封鎖述詞會明確封鎖違反述詞的寫入作業 (AFTER INSERT、AFTER UPDATE、BEFORE UPDATE、BEFORE DELETE)。

定義為內嵌資料表值函數的安全性述詞，會限制資料表中資料列層級資料的存取權。然後叫用函式並強制執行安全性原則。針對篩選述詞，應用程式不會知道從結果集篩選的資料列。若所有資料列皆經過篩選，則會傳回 Null 集合。對 Block 述詞而言，任何違反述詞的作業都將會失敗並產生錯誤。

從基底資料表中讀取資料時會套用篩選述詞。它們會影響所有取得作業：SELECT、DELETE 及 UPDATE。使用者無法選取或刪除已篩選的資料列。使用者無法更新已篩選的資料列。但是，可以更新資料列使它們在之後會被篩選。 Block 述詞會影響所有寫入作業。

AFTER INSERT 和 AFTER UPDATE 述詞可以防止使用者將資料列更新為違反述詞的值。
BEFORE UPDATE 述詞可以防止使用者將資料列更新為目前違反述詞的值。
BEFORE DELETE 述詞可以封鎖刪除作業。

篩選器述詞、Block 述詞及安全性原則皆具有下列行為：

您可以定義與另一個資料表聯結和/或叫用函數的述詞函數。若在 SCHEMABINDING = ON (預設值) 的情況下建立安全性原則，則聯結或函數可從查詢存取並如預期般運作，而不需任何額外的權限檢查。若在 SCHEMABINDING = OFF 的情況下建立安全性原則，則使用者需要有這些額外資料表與函式的 SELECT 權限，才能查詢目標資料表。若述詞函式叫用 CLR 純量值函式，則還需要 EXECUTE 權限。
您可以對己定義但停用安全性述詞的資料表發出查詢。不會影響任何已篩選或封鎖的資料列。
若 dbo 使用者、db_owner 角色的成員或資料表擁有者對已定義且啟用安全性原則的資料表進行查詢，資料列便會依安全性原則所定義而受到篩選或封鎖。
若您嘗試變更結構描述繫結安全性原則所繫結資料表的結構描述，將會導致錯誤。不過，您可以改變述詞不參考的資料行。
若嘗試在已替指定作業定義述詞的資料表上加入述詞，會導致錯誤。不論是否已啟用述詞都會發生。
嘗試修改在已繫結結構描述之安全性原則內的資料表上作為述詞的函式會導致錯誤。
定義包含非重疊的述詞的多個作用中的安全性原則，就會成功。

篩選器述詞具有下列行為：

定義篩選資料表的資料列的安全性原則。應用程式不會知道針對 SELECT、UPDATE 和 DELETE 作業篩選的任何資料列。包括已篩選出所有資料列的情況。應用程式可以 INSERT 資料列，即使它們會在任何其他作業期間進行篩選。

封鎖述詞具有下列行為：

UPDATE 的封鎖述詞會針對 BEFORE 和 AFTER 分割成個別的作業。比方說，您無法避免使用者將資料列更新為具有高於目前的值。若需要此種邏輯，您必須搭配 DELETED 及 INSERTED 中繼資料表使用觸發程序，以同時參考舊值與新值。
若述詞函式所使用的資料行皆未變更，最佳化工具便不會檢查 AFTER UPDATE 封鎖述詞。例如：Alice 不應該能將薪資變更為大於 100,000。 Alice 可以變更薪資已經大於 100,000 之員工的地址，先決條件是述詞中所參考的資料行未變更。
連同 BULK INSERT 在內的大量 API 皆未變更。這表示封鎖述詞 AFTER INSERT 套用至大量插入作業，就如同一般的插入作業。

使用案例

以下是如何使用資料列層級安全性 (RLS) 的設計範例：

醫院可以建立安全性原則，讓護士僅能檢視其病患的資料列。
銀行可以根據員工的業務部門或在公司內的角色建立一個原則，來限制財務資料列的存取權。
多租用戶應用程式可以建立一個原則來強制執行邏輯分離每個租用戶的資料列與每個其他租用戶的資料列。透過單一資料表中的多個租用戶資料的儲存體，可達到效率。每個租用戶只能看到它的資料列。

RLS 篩選述詞的功能等同於附加 WHERE 子句。述詞可以像商務作法命令一樣為複雜，或子句可以像 WHERE TenantId = 42一樣簡單。

在更正式的用語，RLS 將介紹述詞型的存取控制。它具備彈性且集中的述詞型評估。述詞能以中繼資料 (或任何其他系統管理員判斷為適當的準則) 為基礎。述詞作為準則，以根據使用者屬性判斷使用者是否具有適當的資料存取權。標籤型存取控制可透過述詞型存取控制來實作。

權限

建立、改變或卸除安全性原則需要 ALTER ANY SECURITY POLICY 權限。建立或卸除安全性原則需要對結構描述的 ALTER 權限。

此外，每個加入的述詞還需要下列權限：

正做為述詞使用之函式的 SELECT 和 REFERENCES 權限。
正繫結至原則之目標資料表的 REFERENCES 權限。
目標資料表中做為引數使用之每個資料行的 REFERENCES 權限。

安全性原則套用到所有使用者，包括資料庫中的 dbo 使用者。 Dbo 使用者可以改變或卸除安全性原則，不過可稽核的安全性原則變更。若高特殊權限的使用者 (如系統管理員或 db_owner) 需要查看所有資料列，以進行疑難排解或驗證資料，則安全性原則必須撰寫為允許這麼做。

如果以 SCHEMABINDING = OFF 來建立安全性原則，則為了查詢目標資料表，使用者必須對述詞函式和述詞函式內使用的任何其他資料表、檢視或函式具有 SELECT 或 EXECUTE 權限。如果以 SCHEMABINDING = ON (預設值) 來建立安全性原則，則當使用者查詢目標資料表時，會略過這些權限檢查。

最佳作法

強烈建議為 RLS 物件 (述詞函式與安全性原則) 建立個別的結構描述。這有助於將這些特殊物件上所需的權限與目標資料表分開。多組織用戶共享資料庫中可能需要不同原則與述詞函式的其他分隔，但這並非每個案例的標準情況。
ALTER ANY SECURITY POLICY 權限旨在供高權限使用者 (例如安全性原則管理員) 使用。安全性原則管理員不需要其所保護資料表的 SELECT 權限。
請避免在述詞函式中進行型別轉換，以避免可能的執行階段錯誤。
盡量避免在述詞函數中使用遞迴，以免效能下降。查詢最佳化工具會嘗試偵測直接遞迴，但是不一定能夠找出間接遞迴。間接遞迴是指有第二個函式呼叫述詞函式。
請避免在述詞函式中使用過多的資料表聯結，將效能最大化。

避免述詞邏輯取決於工作階段特定的 SET 選項：雖然不太可能會用於實際的應用程式中，但若述詞函式的邏輯取決於特定工作階段特定的 SET 選項且使用者能夠執行任何查詢，則可能導致資訊外洩。例如，若述詞函式能以隱含方式將字串轉換成 datetime，則可根據目前工作階段的 SET DATEFORMAT 選項來篩選不同的資料列。一般情況下，述詞函數應遵守下列規則︰

述詞函式不應以隱含方式將字元字串轉換為以下格式：date、smalldatetime、datetime、datetime2 或 datetimeoffset，反之亦然，因為這些轉換會受到 SET DATEFORMAT (Transact-SQL) 和 SET LANGUAGE (Transact-SQL) 選項影響。請改用 CONVERT 函式，並明確指定樣式參數。
述詞函式不應依賴每週第一天的值，因為此值會受到 SET DATEFIRST (Transact-SQL) 選項影響。
述詞函式不應依賴算術或彙總運算式傳回 NULL，其可能會發生錯誤 (如溢位或除以零)，因為此行為受到 SET ANSI_WARNINGS (Transact-SQL)、SET NUMERIC_ROUNDABORT (Transact-SQL) 及 SET ARITHABORT (Transact-SQL) 選項影響。
述詞函式不應比較串連的字串與 NULL，因為此行為受到 SET CONCAT_NULL_YIELDS_NULL (Transact-SQL) 選項影響。

安全性注意事項：旁路攻擊

惡意的安全性原則管理員

請注意，具有足夠權限可在敏感性資料行上建立安全性原則，以及有權建立或改變內嵌資料表值函式的惡意安全性原則管理員，可以與具有資料表選取權限的其他使用者共謀，藉由惡意建立設計成使用旁路攻擊推斷資料的內嵌資料表值函式，來洩漏資料。這類攻擊需要共謀 (或授與惡意使用者過多權限)，並可能需要反覆修改原則 (需要移除述詞權限才能中斷結構描述繫結)、修改內嵌資料表值函式，並在目標資料表上重複執行 select 陳述式。我們建議您視需要限制權限，並監視任何可疑活動。應監視的活動如：經常變更有關資料列層級安全性的原則和內嵌資料表值函式。

精巧的查詢

使用利用錯誤外流資料的特製查詢，可能會造成資訊外洩。例如，SELECT 1/(SALARY-100000) FROM PAYROLL WHERE NAME='John Doe'; 可讓惡意使用者知道 John Doe 的確切薪資為 100,000 美元。即使有安全性述詞用來防止惡意使用者直接查詢其他人的薪資，當查詢傳回除以零的例外狀況時，使用者仍可以決定。

跨功能的相容性

一般情況下，資料列層級安全性將在所有功能下正常運作。但仍有一些例外狀況。本節說明搭配 SQL Server 的特定其他功能使用資料列層級安全性時，數個需要注意的事項及警告。

DBCC SHOW_STATISTICS 回報關於未篩選資料的統計資料，並可能導致洩漏安全性原則所保護的資訊。因此，檢視具有資料列層級安全性之資料表的統計資料物件是受到限制的。使用者必須擁有該資料表，或該使用者必須是 sysadmin 固定伺服器角色、db_owner 固定資料庫角色或 db_ddladmin 固定資料庫角色的成員。
Filestream： RLS 與 Filestream 不相容。
PolyBase：Azure Synapse 和 SQL Server 2019 CU7 或更新版本中的外部資料表支援 RLS。
經記憶體最佳化的資料表： 在經記憶體最佳化的資料表上作為安全性述詞的內嵌資料表值函式必須使用 WITH NATIVE_COMPILATION 選項來定義。使用此選項將會禁止記憶體最佳化資料表不支援的語言功能，並會在建立階段發出適當的錯誤。如需詳細資訊，請參閱經記憶體最佳化的資料表中的資料列層級安全性。
索引檢視表： 一般而言，您可以在檢視上建立安全性原則，並可以在由安全性原則所繫結的資料表上建立檢視。不過，透過索引的資料列查閱會略過原則，因此您無法在具備安全性原則的資料表上建立索引檢視表。
異動資料擷取： 異動資料擷取 (CDC) 可能會導致洩漏整個資料列，其中該資料列應篩選至 db_owner 的成員，或篩選至啟用資料表的 CDC 時所指定「控制」角色成員的使用者。您可以明確地將此函式設定為 NULL，讓所有使用者都能存取異動資料。實際上，db_owner 及此控制角色的成員皆可以看到資料表上所有的資料變更，即使資料表上具有安全性原則亦然。
變更追蹤：變更追蹤可能會導致洩漏資料列的主索引鍵，其中該資料列應同時使 SELECT 及 VIEW CHANGE TRACKING 權限篩選至使用者。實際資料值不會遭到洩漏；僅為資料列的資料行 A 已利用某個主索引鍵進行更新/插入/刪除。若主索引鍵包含像是身分證號碼等機密的項目，便會造成問題。不過，在實務上，此 CHANGETABLE 幾乎一律會與原始資料表聯結，以取得最新的資料。
全文檢索搜尋：由於引進額外聯結以套用資料列層級安全性及避免洩漏應篩選之資料列的主索引鍵，因此使用下列全文檢索搜尋及語意搜尋函式時，應會對查詢的效能造成衝擊：CONTAINSTABLE、FREETEXTTABLE、semantickeyphrasetable、semanticsimilaritydetailstable、semanticsimilaritytable。
資料行存放區索引： RLS 適用於叢集與非叢集資料行存放區索引。不過，由於資料列層級安全性套用函式，因此最佳化工具可以在不使用批次模式的情況下修改查詢計劃。
資料分割檢視：封鎖述詞不能在資料分割檢視上定義，且不能在使用封鎖述詞的資料表上建立資料分割檢視。篩選述詞則與資料分割檢視相容。
時態表： 時態表與 RLS 相容。不過，目前資料表上的安全性述詞不會自動複製到歷程記錄資料表。若要將安全性原則套用至目前和歷程記錄資料表，您必須個別在每個資料表上新增安全性述詞。

其他限制：

Microsoft Fabric 和 Azure Synapse Analytics 僅支援篩選述詞。 Microsoft Fabric 和 Azure Synapse Analytics 目前不支援封鎖述詞。

範例

A. 向資料庫驗證的使用者案例

此範例會建立三個使用者，然後建立資料表並將六個資料列填入。然後，它為資料表建立內嵌資料表值函式和安全性原則。此範例接著示範如何針對不同使用者篩選 Select 陳述式。

建立三個使用者帳戶，示範不同的存取功能。

CREATE USER Manager WITHOUT LOGIN;
CREATE USER SalesRep1 WITHOUT LOGIN;
CREATE USER SalesRep2 WITHOUT LOGIN;
GO

建立資料表來持有資料。

CREATE SCHEMA Sales
GO
CREATE TABLE Sales.Orders
    (
    OrderID int,
    SalesRep nvarchar(50),
    Product nvarchar(50),
    Quantity smallint
    );

在資料表中填入六個資料列，每個銷售代表各顯示三個訂單。

INSERT INTO Sales.Orders  VALUES (1, 'SalesRep1', 'Valve', 5);
INSERT INTO Sales.Orders  VALUES (2, 'SalesRep1', 'Wheel', 2);
INSERT INTO Sales.Orders  VALUES (3, 'SalesRep1', 'Valve', 4);
INSERT INTO Sales.Orders  VALUES (4, 'SalesRep2', 'Bracket', 2);
INSERT INTO Sales.Orders  VALUES (5, 'SalesRep2', 'Wheel', 5);
INSERT INTO Sales.Orders  VALUES (6, 'SalesRep2', 'Seat', 5);
-- View the 6 rows in the table
SELECT * FROM Sales.Orders;

授與讀取權限給每個使用者資料表。

GRANT SELECT ON Sales.Orders TO Manager;
GRANT SELECT ON Sales.Orders TO SalesRep1;
GRANT SELECT ON Sales.Orders TO SalesRep2;
GO

建立新的結構描述，以及內嵌資料表值函式。當 SalesRep 資料行中的資料列與執行查詢的使用者相同 (@SalesRep = USER_NAME())，或執行查詢的使用者為管理員使用者 (USER_NAME() = 'Manager') 時，此函式會傳回 1。這個由使用者定義的資料表值函式範例，有助於作為在下一個步驟中所建立安全性原則的篩選。

CREATE SCHEMA Security;
GO

CREATE FUNCTION Security.tvf_securitypredicate(@SalesRep AS nvarchar(50))
    RETURNS TABLE
WITH SCHEMABINDING
AS
    RETURN SELECT 1 AS tvf_securitypredicate_result
WHERE @SalesRep = USER_NAME() OR USER_NAME() = 'Manager';
GO

建立依照篩選器述詞中加入函式的安全性原則。若要啟用此原則，STATE 必須設為 ON。

CREATE SECURITY POLICY SalesFilter
ADD FILTER PREDICATE Security.tvf_securitypredicate(SalesRep)
ON Sales.Orders
WITH (STATE = ON);
GO

允許 SELECT 函式的 tvf_securitypredicate 權限：

GRANT SELECT ON Security.tvf_securitypredicate TO Manager;
GRANT SELECT ON Security.tvf_securitypredicate TO SalesRep1;
GRANT SELECT ON Security.tvf_securitypredicate TO SalesRep2;

現在請從每個使用者的 Sales.Orders 資料表進行選取，藉此測試篩選述詞。

EXECUTE AS USER = 'SalesRep1';
SELECT * FROM Sales.Orders;
REVERT;

EXECUTE AS USER = 'SalesRep2';
SELECT * FROM Sales.Orders;
REVERT;

EXECUTE AS USER = 'Manager';
SELECT * FROM Sales.Orders;
REVERT;

管理員應該會看到所有六個資料列。 Sales1 和 Sales2 使用者只能看到他們自己的銷售。

變更安全性原則，以停用原則。

ALTER SECURITY POLICY SalesFilter
WITH (STATE = OFF);

現在，Sales1 和 Sales2 使用者可以看到所有六個資料列。

從此範例練習中連線到 SQL 資料庫以清除資源：

DROP USER SalesRep1;
DROP USER SalesRep2;
DROP USER Manager;

DROP SECURITY POLICY SalesFilter;
DROP TABLE Sales.Orders;
DROP FUNCTION Security.tvf_securitypredicate;
DROP SCHEMA Security;
DROP SCHEMA Sales;

B. 在 Azure Synapse 外部資料表上使用資料列層級安全性的案例

此範例會建立三個使用者，以及包含六個資料列的外部資料表。它接著為該外部資料表建立內嵌資料表值函式和安全性原則。此範例示範如何針對不同的使用者篩選 select 陳述式。

先決條件

您必須要有專用 SQL 集區。請參閱建立專用 SQL 集區
裝載專用 SQL 集區的伺服器必須向 Microsoft Entra ID (前稱為 Azure Active Directory) 註冊，且您必須擁有具備 Storage Blog Data Contributor 權限的 Azure 儲存體帳戶。請依照步驟對 Azure SQL Database 中的伺服器使用虛擬網路服務端點和規則。
為 Azure 儲存體帳戶建立檔案系統。使用 Azure 儲存體總管來檢視儲存體帳戶。以滑鼠右鍵按一下容器，並選取 [建立檔案系統]。

在具備所有先決條件後，請建立三個使用者帳戶來示範不同存取功能。

--run in master
CREATE LOGIN Manager WITH PASSWORD = '<user_password>'
GO
CREATE LOGIN Sales1 WITH PASSWORD = '<user_password>'
GO
CREATE LOGIN Sales2 WITH PASSWORD = '<user_password>'
GO

--run in both the master database and in your dedicated SQL pool database
CREATE USER Manager FOR LOGIN Manager;
CREATE USER Sales1  FOR LOGIN Sales1;
CREATE USER Sales2  FOR LOGIN Sales2 ;

建立資料表來持有資料。

CREATE TABLE Sales
    (
    OrderID int,
    SalesRep sysname,
    Product varchar(10),
    Qty int
    );

在資料表中填入六個資料列，每個銷售代表各顯示三個訂單。

INSERT INTO Sales VALUES (1, 'Sales1', 'Valve', 5);
INSERT INTO Sales VALUES (2, 'Sales1', 'Wheel', 2);
INSERT INTO Sales VALUES (3, 'Sales1', 'Valve', 4);
INSERT INTO Sales VALUES (4, 'Sales2', 'Bracket', 2);
INSERT INTO Sales VALUES (5, 'Sales2', 'Wheel', 5);
INSERT INTO Sales VALUES (6, 'Sales2', 'Seat', 5);
-- View the 6 rows in the table
SELECT * FROM Sales;

從您剛建立的 Sales 資料表建立 Azure Synapse 外部資料表。

CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<user_password>';

CREATE DATABASE SCOPED CREDENTIAL msi_cred WITH IDENTITY = 'Managed Service Identity';

CREATE EXTERNAL DATA SOURCE ext_datasource_with_abfss WITH (TYPE = hadoop, LOCATION = 'abfss://<file_system_name@storage_account>.dfs.core.windows.net', CREDENTIAL = msi_cred);

CREATE EXTERNAL FILE FORMAT MSIFormat  WITH (FORMAT_TYPE=DELIMITEDTEXT);

CREATE EXTERNAL TABLE Sales_ext WITH (LOCATION='<your_table_name>', DATA_SOURCE=ext_datasource_with_abfss, FILE_FORMAT=MSIFormat, REJECT_TYPE=Percentage, REJECT_SAMPLE_VALUE=100, REJECT_VALUE=100)
AS SELECT * FROM sales;

在您建立的外部資料表 Sales_ext 上為三個使用者授與 SELECT。

GRANT SELECT ON Sales_ext TO Sales1;
GRANT SELECT ON Sales_ext TO Sales2;
GRANT SELECT ON Sales_ext TO Manager;

建立新的結構描述，以及內嵌的資料表值函式；您可能已在範例 A 中完成這項操作。如果 1 資料行中的資料列與執行查詢 (SalesRep) 的使用者相同，或執行查詢的使用者是 @SalesRep = USER_NAME() 使用者 (Manager)，此函式會傳回 USER_NAME() = 'Manager'。

CREATE SCHEMA Security;
GO

CREATE FUNCTION Security.fn_securitypredicate(@SalesRep AS sysname)
    RETURNS TABLE
WITH SCHEMABINDING
AS
    RETURN SELECT 1 AS fn_securitypredicate_result
WHERE @SalesRep = USER_NAME() OR USER_NAME() = 'Manager';

使用內嵌資料表值函式作為篩選述詞，在外部資料表上建立安全性原則。若要啟用此原則，STATE 必須設為 ON。

CREATE SECURITY POLICY SalesFilter_ext
ADD FILTER PREDICATE Security.fn_securitypredicate(SalesRep)
ON dbo.Sales_ext
WITH (STATE = ON);

現在請從 Sales_ext 外部資料表進行選取，藉此測試篩選述詞。以每個使用者、Sales1、Sales2 和 Manager 的身分登入。使用每個使用者執行下列命令。

SELECT * FROM Sales_ext;

Manager 應該會看到所有六個資料列。 Sales1 和 Sales2 使用者應該只能看到他們的銷售。

變更安全性原則，以停用原則。

ALTER SECURITY POLICY SalesFilter_ext
WITH (STATE = OFF);

現在 Sales1 和 Sales2 使用者可以看到所有六個資料列。

從此範例練習中連線到 Azure Synapse 資料庫以清除資源：

DROP USER Sales1;
DROP USER Sales2;
DROP USER Manager;

DROP SECURITY POLICY SalesFilter_ext;
DROP TABLE Sales;
DROP EXTERNAL TABLE Sales_ext;
DROP EXTERNAL DATA SOURCE ext_datasource_with_abfss ;
DROP EXTERNAL FILE FORMAT MSIFormat;
DROP DATABASE SCOPED CREDENTIAL msi_cred;
DROP MASTER KEY;

連線到邏輯伺服器的 master 資料庫以清除資源：

DROP LOGIN Sales1;
DROP LOGIN Sales2;
DROP LOGIN Manager;

C. 透過中介層應用程式連接到資料庫的使用者案例

注意

在此範例中，Microsoft Fabric 和 Azure Synapse 目前不支援封鎖述詞功能，因此插入錯誤使用者識別碼的資料列不會遭到封鎖。

此範例示範中介層應用程式如何實作連線篩選，其中應用程式使用者 (或租用戶) 共用相同的 SQL Server 使用者 (應用程式)。應用程式在連接到資料庫之後，會在 SESSION_CONTEXT 中設定目前的應用程式使用者識別碼，然後安全性原則會明確地篩選此識別碼不應該看到的資料列，並同時避免使用者插入錯誤使用者識別碼的資料列。不需要任何其他的應用程式變更。

建立資料表來持有資料。

CREATE TABLE Sales (
    OrderId int,
    AppUserId int,
    Product varchar(10),
    Qty int
);

在資料表中填入六個資料列，每位應用程式使用者各顯示三個訂單。

INSERT Sales VALUES
    (1, 1, 'Valve', 5),
    (2, 1, 'Wheel', 2),
    (3, 1, 'Valve', 4),
    (4, 2, 'Bracket', 2),
    (5, 2, 'Wheel', 5),
    (6, 2, 'Seat', 5);

建立應用程式將用來連接的低權限使用者。

-- Without login only for demo
CREATE USER AppUser WITHOUT LOGIN;
GRANT SELECT, INSERT, UPDATE, DELETE ON Sales TO AppUser;

-- Never allow updates on this column
DENY UPDATE ON Sales(AppUserId) TO AppUser;

建立新的結構描述和述詞函式，這將會使用儲存在 SESSION_CONTEXT() 的應用程式使用者識別碼，以篩選資料列。

CREATE SCHEMA Security;
GO

CREATE FUNCTION Security.fn_securitypredicate(@AppUserId int)
    RETURNS TABLE
    WITH SCHEMABINDING
AS
    RETURN SELECT 1 AS fn_securitypredicate_result
    WHERE
        DATABASE_PRINCIPAL_ID() = DATABASE_PRINCIPAL_ID('AppUser')
        AND CAST(SESSION_CONTEXT(N'UserId') AS int) = @AppUserId;
GO

建立安全性原則，作為 Sales上的篩選述詞及 Block 篩選述詞新增此函數。 AFTER INSERT 由於 BEFORE UPDATE 及 BEFORE DELETE 皆已經過篩選，因此封鎖述詞只需要，且因為先前設定的資料行權限，AFTER UPDATE 資料行無法更新為其他值，所以不需要 AppUserId。

CREATE SECURITY POLICY Security.SalesFilter
    ADD FILTER PREDICATE Security.fn_securitypredicate(AppUserId)
        ON dbo.Sales,
    ADD BLOCK PREDICATE Security.fn_securitypredicate(AppUserId)
        ON dbo.Sales AFTER INSERT
    WITH (STATE = ON);

在 Sales 中設定不同的使用者識別碼之後，現在我們可以從資料表中選取 SESSION_CONTEXT()，以模擬連線篩選。在實務上，開啟連線後，應用程式負責在 SESSION_CONTEXT() 中設定目前的使用者識別碼。將 @read_only 參數設定為 1 可防止值再次變更，直到連線關閉為止 (退回至連線集區)。

EXECUTE AS USER = 'AppUser';
EXEC sp_set_session_context @key=N'UserId', @value=1;
SELECT * FROM Sales;
GO

/* Note: @read_only prevents the value from changing again until the connection is closed (returned to the connection pool)*/
EXEC sp_set_session_context @key=N'UserId', @value=2, @read_only=1;

SELECT * FROM Sales;
GO

INSERT INTO Sales VALUES (7, 1, 'Seat', 12); -- error: blocked from inserting row for the wrong user ID
GO

REVERT;
GO

清除資料庫資源。

DROP USER AppUser;

DROP SECURITY POLICY Security.SalesFilter;
DROP TABLE Sales;
DROP FUNCTION Security.fn_securitypredicate;
DROP SCHEMA Security;

D. 針對安全性述詞使用查閱資料表的情節

本範例會針對使用者識別碼與所篩選值之間的連結使用查閱資料表，而不需要在事實資料表中指定使用者識別碼。範例會建立三個使用者，並建立事實資料表 Sample.Sales 和查閱資料表，前者會填入六個資料列，而後者會填入兩個資料列。其會接著建立內嵌資料表值函式，將事實資料表聯結至查閱資料表以取得使用者識別碼，以及資料表的安全性原則。此範例接著示範如何針對不同使用者篩選 Select 陳述式。

建立三個使用者帳戶，示範不同的存取功能。

CREATE USER Manager WITHOUT LOGIN;
CREATE USER Sales1 WITHOUT LOGIN;
CREATE USER Sales2 WITHOUT LOGIN;

建立 Sample 結構描述和事實資料表 Sample.Sales 來保存資料。

CREATE SCHEMA Sample;
GO
CREATE TABLE Sample.Sales
    (
    OrderID int,
    Product varchar(10),
    Qty int
    );

為 Sample.Sales 填入六個資料列。

INSERT INTO Sample.Sales VALUES (1, 'Valve', 5);
INSERT INTO Sample.Sales VALUES (2, 'Wheel', 2);
INSERT INTO Sample.Sales VALUES (3, 'Valve', 4);
INSERT INTO Sample.Sales VALUES (4, 'Bracket', 2);
INSERT INTO Sample.Sales VALUES (5, 'Wheel', 5);
INSERT INTO Sample.Sales VALUES (6, 'Seat', 5);
-- View the 6 rows in the table
SELECT * FROM Sample.Sales;

建立資料表來保存查閱資料 – 在此案例中為 Salesrep 與 Product 之間的關聯。

CREATE TABLE Sample.Lk_Salesman_Product
  ( Salesrep sysname,
    Product varchar(10)
  ) ;

使用範例資料填入查閱資料表，並將一個 Product 連結至每個銷售代表。

INSERT INTO Sample.Lk_Salesman_Product VALUES ('Sales1', 'Valve');
INSERT INTO Sample.Lk_Salesman_Product VALUES ('Sales2', 'Wheel');
-- View the 2 rows in the table
SELECT * FROM Sample.Lk_Salesman_Product;

將事實資料表的讀取存取權授與給每位使用者。

GRANT SELECT ON Sample.Sales TO Manager;
GRANT SELECT ON Sample.Sales TO Sales1;
GRANT SELECT ON Sample.Sales TO Sales2;

建立新的結構描述以及內嵌資料表值函式。當使用者查詢事實資料表 1，且資料表 Sample.Sales 的 SalesRep 資料行在聯結至 Lk_Salesman_Product 資料行上的事實資料表時，與執行查詢 (@SalesRep = USER_NAME()) 的使用者相同，或者如果執行查詢的使用者為 Product 使用者 (Manager)，則此函式會傳回 USER_NAME() = 'Manager'。

CREATE SCHEMA Security ;
GO
CREATE FUNCTION Security.fn_securitypredicate
         (@Product AS varchar(10))
RETURNS TABLE
WITH SCHEMABINDING
AS
           RETURN ( SELECT 1 as Result
                     FROM Sample.Sales f
            INNER JOIN Sample.Lk_Salesman_Product s
                     ON s.Product = f.Product
            WHERE ( f.product = @Product
                    AND s.SalesRep = USER_NAME() )
                 OR USER_NAME() = 'Manager'
                   ) ;

建立依照篩選器述詞中加入函式的安全性原則。若要啟用此原則，STATE 必須設為 ON。

CREATE SECURITY POLICY SalesFilter
ADD FILTER PREDICATE Security.fn_securitypredicate(Product)
ON Sample.Sales
WITH (STATE = ON) ;

允許 SELECT 函式的 fn_securitypredicate 權限：

GRANT SELECT ON Security.fn_securitypredicate TO Manager;
GRANT SELECT ON Security.fn_securitypredicate TO Sales1;
GRANT SELECT ON Security.fn_securitypredicate TO Sales2;

現在請從每個使用者的 Sample.Sales 資料表進行選取，藉此測試篩選述詞。

EXECUTE AS USER = 'Sales1';
SELECT * FROM Sample.Sales;
-- This will return just the rows for Product 'Valve' (as specified for 'Sales1' in the Lk_Salesman_Product table above)
REVERT;

EXECUTE AS USER = 'Sales2';
SELECT * FROM Sample.Sales;
-- This will return just the rows for Product 'Wheel' (as specified for 'Sales2' in the Lk_Salesman_Product table above)
REVERT;

EXECUTE AS USER = 'Manager';
SELECT * FROM Sample.Sales;
-- This will return all rows with no restrictions
REVERT;

Manager 應該會看到所有六個資料列。 Sales1 和 Sales2 使用者只能看到他們自己的銷售。

變更安全性原則，以停用原則。

ALTER SECURITY POLICY SalesFilter
WITH (STATE = OFF);

現在，Sales1 和 Sales2 使用者可以看到所有六個資料列。

從此範例練習中連線到 SQL 資料庫以清除資源：

DROP USER Sales1;
DROP USER Sales2;
DROP USER Manager;

DROP SECURITY POLICY SalesFilter;
DROP FUNCTION Security.fn_securitypredicate;
DROP TABLE Sample.Sales;
DROP TABLE Sample.Lk_Salesman_Product;
DROP SCHEMA Security;
DROP SCHEMA Sample;

E. Microsoft Fabric 中的資料列層級安全性案例

我們可以示範 Microsoft Fabric 中的資料列層級安全性 Warehouse 和 SQL 分析端點。

下列範例會建立範例資料表，這些資料表將配合 Microsoft Fabric 中的 Warehouse 使用，但在 SQL 分析端點中，會使用現有資料表。在 SQL 分析端點中，您無法使用 CREATE TABLE，但可以使用 CREATE SCHEMA、CREATE FUNCTION 和 CREATE SECURITY POLICY。

在此範例中，請先建立結構描述 sales、資料表 sales.Orders。

CREATE SCHEMA sales;
GO

-- Create a table to store sales data
CREATE TABLE sales.Orders (
    SaleID INT,
    SalesRep VARCHAR(100),
    ProductName VARCHAR(50),
    SaleAmount DECIMAL(10, 2),
    SaleDate DATE
);

-- Insert sample data
INSERT INTO sales.Orders (SaleID, SalesRep, ProductName, SaleAmount, SaleDate)
VALUES
    (1, 'Sales1@contoso.com', 'Smartphone', 500.00, '2023-08-01'),
    (2, 'Sales2@contoso.com', 'Laptop', 1000.00, '2023-08-02'),
    (3, 'Sales1@contoso.com', 'Headphones', 120.00, '2023-08-03'),
    (4, 'Sales2@contoso.com', 'Tablet', 800.00, '2023-08-04'),
    (5, 'Sales1@contoso.com', 'Smartwatch', 300.00, '2023-08-05'),
    (6, 'Sales2@contoso.com', 'Gaming Console', 400.00, '2023-08-06'),
    (7, 'Sales1@contoso.com', 'TV', 700.00, '2023-08-07'),
    (8, 'Sales2@contoso.com', 'Wireless Earbuds', 150.00, '2023-08-08'),
    (9, 'Sales1@contoso.com', 'Fitness Tracker', 80.00, '2023-08-09'),
    (10, 'Sales2@contoso.com', 'Camera', 600.00, '2023-08-10');

建立 Security 結構描述、函式 Security.tvf_securitypredicate 和安全原則 SalesFilter。

-- Creating schema for Security
CREATE SCHEMA Security;
GO

-- Creating a function for the SalesRep evaluation
CREATE FUNCTION Security.tvf_securitypredicate(@SalesRep AS nvarchar(50))
    RETURNS TABLE
WITH SCHEMABINDING
AS
    RETURN SELECT 1 AS tvf_securitypredicate_result
WHERE @SalesRep = USER_NAME() OR USER_NAME() = 'manager@contoso.com';
GO

-- Using the function to create a Security Policy
CREATE SECURITY POLICY SalesFilter
ADD FILTER PREDICATE Security.tvf_securitypredicate(SalesRep)
ON sales.Orders
WITH (STATE = ON);
GO

套用安全性原則並建立函式之後，使用者 Sales1@contoso.com 和 Sales2@contoso.com 將只能看到 sales.Orders 資料表中其自己的資料，其中資料行 SalesRep 等於內建函數 USER_NAME() 所傳回的其本身使用者名稱。 Fabric 用戶 manager@contoso.com 能夠查看 sales.Orders 資料表中的所有資料。

共用方式為

資料列層級安全性

描述

使用案例

權限

最佳作法

安全性注意事項：旁路攻擊

惡意的安全性原則管理員

精巧的查詢

跨功能的相容性

範例

A. 向資料庫驗證的使用者案例

B. 在 Azure Synapse 外部資料表上使用資料列層級安全性的案例

先決條件

C. 透過中介層應用程式連接到資料庫的使用者案例

D. 針對安全性述詞使用查閱資料表的情節

E. Microsoft Fabric 中的資料列層級安全性案例

意見反應

其他資源

共用方式為

資料列層級安全性

描述

使用案例

權限

最佳作法

安全性注意事項：旁路攻擊

惡意的安全性原則管理員

精巧的查詢

跨功能的相容性

範例

A. 向資料庫驗證的使用者案例

B. 在 Azure Synapse 外部資料表上使用資料列層級安全性的案例

先決條件

C. 透過中介層應用程式連接到資料庫的使用者案例

D. 針對安全性述詞使用查閱資料表的情節

E. Microsoft Fabric 中的資料列層級安全性案例

相關內容

意見反應

其他資源