透過資料行和資料列層級安全性來管理授權

  • 6 分鐘

在本主題中，我們將逐步說明如何透過 Azure Synapse Analytics 內的資料行和資料列層級安全性來管理授權。 我們會先討論 Azure Synapse Analytics 中的資料行層級安全性，而後討論資料列層級安全性。

Azure Synapse Analytics 中的資料行層級安全性

一般而言，資料行層級安全性可簡化應用程式中的安全性設計和編碼。 其可讓您限制資料行存取，以便保護敏感性資料。 例如，如果您想確保特定使用者 'Leo' 只能存取資料表的特定資料行，因為他在特定部門中。 讓「Leo」只要存取針對其任職部門所指定之資料行的邏輯，是屬於資料庫層的邏輯，而不屬於應用程式層級資料層。 如果他需要從任何層存取資料，則資料庫應該在他每次嘗試從另一層存取資料時套用存取限制。 這麼做的原因是為了確保您的安全性很可靠且穩固，因為我們要減少整體安全性系統的介面區。 資料行層級安全性也可免除引進檢視的必要性，而您可在檢視中篩選出資料行，以對 'Leo' 強加存取限制

實作資料行層級安全性的方法是使用 GRANT T-SQL 陳述式。 SQL 和 Microsoft Entra ID 可利用此陳述式支援驗證。

顯示資料行層級安全性

語法

用於實作資料行層級安全性的語法如下所示：

GRANT <permission> [ ,...n ] ON
    [ OBJECT :: ][ schema_name ]. object_name [ ( column [ ,...n ] ) ] // specifying the column access
    TO <database_principal> [ ,...n ]
    [ WITH GRANT OPTION ]
    [ AS <database_principal> ]
<permission> ::=
    SELECT
  | UPDATE
<database_principal> ::=
      Database_user // specifying the database user
    | Database_role // specifying the database role 
    | Database_user_mapped_to_Windows_User
    | Database_user_mapped_to_Windows_Group

那麼，您何時會使用資料行層級安全性？ 假設您屬於金融服務公司，而且只允許帳戶管理員存取客戶的社會安全號碼、電話號碼或其他個人識別資訊。 請務必區分帳戶管理員與帳戶管理員之管理員的角色。

另一個使用案例可能與醫療保健產業相關。 假設您有特定的醫療保健服務提供者。 此醫療保健服務提供者只希望醫生和護士能夠存取醫療記錄。 帳務部門不應具備檢視此資料的存取權。 資料行層級安全性可能是所要使用的選項。

因此，如何區分資料行層級安全性與資料列層級安全性？ 讓我們研究看看。

Azure Synapse Analytics 中的資料列層級安全性

資料列層級安全性 (RLS) 可協助您建立群組成員資格或執行內容，不僅能控制資料庫資料表中的資料行，實際上還可控制資料列。 RLS 就像資料行層級安全性一樣，可簡單地協助進行應用程式安全性的設計和編碼。 不過，相較於著重資料行 (參數) 的資料行層級安全性，RLS 可協助您對資料列存取進行限制。 假設您的員工只能存取對部門而言很重要的資料列，您應該實作 RLS。 如果您想要限制僅與公司相關的客戶資料存取，則可實作 RLS。 資料列的存取限制是屬於資料庫層的邏輯，而不屬於應用程式層級資料層。 如果 'Leo' 需要從任何層存取資料，則資料庫應該在他每次嘗試從另一層存取資料時套用存取限制。 這麼做的原因是為了確保您的安全性很可靠且穩固，因為我們要減少整體安全性系統的介面區。

實作 RLS 的方法就是使用 CREATE SECURITY POLICY[!INCLUDEtsql] 陳述式。 述詞會建立為內嵌資料表值函式。 請務必了解，在 Azure Synapse 中只支援篩選述詞。 如果您需要使用封鎖述詞，則目前無法在 Azure Synapse 中找到支援。

RLS 的結構描述

關於篩選述詞的資料列層級安全性描述

Azure Synapse 中的 RLS 支援一種安全性述詞，也就是篩選述詞，而不是封鎖述詞。
篩選述詞的作用是以無訊息方式篩選讀取作業 (例如 SELECT、UPDATE、DELETE) 可用的資料列。

資料表中的資料列層級資料存取會受限制為內嵌資料表值函式，這是一個安全性述詞。 然後，由您需要的安全性原則叫用並強制執行此資料表值函式。 應用程式不會察覺從篩選述詞的結果集中篩選的資料列。 因此，如果所有資料列都經過篩選，就會傳回 null 集合。

當您使用篩選述詞時，其會在從基底資料表讀取資料時套用。 篩選述詞會影響所有 get 作業，例如 SELECT、DELETE、UPDATE。 您無法選取或刪除已篩選的資料列。 您不可能更新已篩選的資料列。 您可以做的是以之後會進行篩選的方式更新資料列。

使用案例

我們已提及 RLS 的一些使用案例。 另一個使用案例可能是您已建立多租用戶的應用程式，而您可在其中建立原則來強制執行租用戶資料列與其他租用戶資料列的邏輯劃分。 為了有效地實作此作業，強烈建議您將許多租用戶的資料儲存在單一資料表中。

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

當我們更正式地看待 RLS 時，RLS 引進了述詞型的存取控制。 RLS 可用於述詞存取控制的原因，在於其具彈性和集中式、並以述詞為基礎的評估。 篩選述詞可以中繼資料或任何您會視情況決定的其他準則為基礎。 述詞作為準則，以根據使用者屬性判斷使用者是否具有適當的資料存取權。 標籤型存取控制可透過述詞型存取控制來實作。

權限

如果您想要建立、改變或卸除安全性原則，您必須使用 ALTER ANY SECURITY POLICY 權限。 這是因為當您建立或卸除安全性原則時，需要結構描述的 ALTER 權限。

此外，您要新增的每個述詞都需要其他權限：

  • 目前作為述詞使用之內嵌資料表值函式的 SELECT 和 REFERENCES 權限。

  • 您打算繫結至原則之資料表的 REFERENCES 權限。

  • 目標資料表中做為引數使用之每個資料行的REFERENCES 權限。

在您設定安全性原則後，這些原則就會套用到所有使用者 (包括資料庫中的 dbo 使用者)。即使 DBO 使用者可改變或卸除安全性原則，還是可以稽核他們對安全性原則所做的變更。 如果您有以下特殊情況：高權限的使用者 (例如 sysadmin 或 db_owner) 需要查看所有資料列以進行疑難排解或驗證資料，您仍然必須撰寫安全性原則，才能允許這種情況。

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

最佳作法

當您想要實作 RLS 時，有一些最佳做法要考量。 建議您為 RLS 物件建立不同的結構描述。 此內容中的 RLS 物件會是述詞函式和安全性原則。 為何這是最佳做法？ 這有助於將這些特殊物件上所需的權限與目標資料表分開。 此外，多租用戶資料庫中可能需要劃分不同的原則和述詞函式。 不過，這並非每個案例的標準做法。

另一種要記住的最佳做法是，ALTER ANY SECURITY POLICY 權限應該只適用於高權限的使用者 (例如安全性原則管理員)。 安全性原則管理員不得要求其所保護資料表的 SELECT 權限。

為了避免潛在的執行階段錯誤，您應該在所撰寫的述詞函式中考慮類型轉換。 此外，您應該嘗試在述詞函式中避免遞迴。 這麼做的原因是為了避免效能降低。 雖然查詢最佳化工具會嘗試偵測直接遞迴，但不保證會尋找間接遞迴。 使用間接遞迴，表示第二個函式會呼叫述詞函式。

此外，也建議避免在述詞函式中使用過多的資料表聯結。 這會使效能最大化。

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