動態資料遮罩 - SQL Server | Microsoft Learn

link管理

链接快照平台

输入网页链接，自动生成快照
标签化管理网页链接

動態資料遮罩概觀

動態資料遮罩讓客戶能夠指定要顯示多少敏感性資料，藉此協助防止未經授權存取敏感性資料，同時盡可能減少對應用程式層的影響。可以在指定資料庫欄位上設定 DDM，以隱藏查詢結果集中的敏感性資料。使用 DDM 時，資料庫中的資料不會變更。對現有的應用程式使用 DDM 相當容易，因為遮罩規則已在查詢結果中套用。許多應用程式都不需要修改現有查詢，就能遮罩機密資料。

中央資料遮罩原則可直接作用於資料庫中的機密欄位上。

指定無法存取敏感性資料之特殊權限的使用者或角色。

DDM 的特色在於完整遮罩和部分遮罩功能，以及數值資料的隨機遮罩。

簡單的 Transact-SQL 命令可定義及管理遮罩。

動態資料遮罩的目的在於限制敏感性資料的曝光，防止不該存取資料的使用者檢視該資料。動態資料遮罩的目的不是為了防止資料庫使用者直接連接到資料庫，以及執行會讓敏感性資料片段曝光的全面查詢。動態資料遮罩是為了補充其他的 SQL Server 安全性功能 (稽核、加密、資料列層級安全性等等)，因此強烈建議與它們一起使用，以便更好地保護資料庫中的敏感性資料。

動態資料遮罩可在 SQL Server 2016 (13.x) 和 Azure SQL Database 中使用，且可使用 Transact-SQL 命令來進行設定。如需使用 Azure 入口網站設定動態資料遮罩的詳細資訊，請參閱開始使用 SQL 資料庫動態資料遮罩 (Azure 入口網站) 。

Microsoft Entra ID 先前稱為 Azure Active Directory (Azure AD)。

定義動態資料遮罩

您可以在資料庫中的資料行定義遮罩規則，以模糊該資料行中的資料。遮罩有五種類型。

請依據指定欄位的資料類型進行完整遮罩。

對於字串資料類型，請使用


   XXXX

，(或更少) - 如果欄位的大小少於 4 個字元 ( char 、 nchar 、 varchar 、 nvarchar 、 text 、 ntext )。
對於數值資料類型，請使用零值 ( bigint 、 bit 、 decimal 、 int 、 money numeric smallint 、 smallmoney 、 tinyint 、 float 、 real )。
對於日期與時間資料類型，請使用


   1900-01-01 00:00:00.0000000

( date 、 datetime2 、 datetime 、 datetimeoffset 、 smalldatetime 、 time )。
對於二進位資料類型，請使用單一位元組的 ASCII 值 0 ( binary 、 varbinary 、 image )。範例資料行定義語法：


   Phone# varchar(12) MASKED WITH (FUNCTION = 'default()') NULL

替代語法的範例：


   ALTER COLUMN Gender ADD MASKED WITH (FUNCTION = 'default()')

此遮罩方法會讓電子郵件地址的第一個字母和常數後置詞 ".com" 以形式為電子郵件地址形式來公開。


   [email protected]

。範例定義語法：


   Email varchar(100) MASKED WITH (FUNCTION = 'email()') NULL

替代語法的範例：


   ALTER COLUMN Email ADD MASKED WITH (FUNCTION = 'email()')

此隨機遮罩函數可用在任何數值類型，會以指定範圍內隨機的值遮罩原始值。範例定義語法：


   Account_Number bigint MASKED WITH (FUNCTION = 'random([start range], [end range])')

替代語法的範例：


   ALTER COLUMN [Month] ADD MASKED WITH (FUNCTION = 'random(1, 12)')

此遮罩方法會公開第一個及最後一個字母，並在中間新增自訂填補字串。


   prefix,[padding],suffix

如果原始的值過短而無法完成整個遮罩，則不會暴露部分的前置詞或後置詞。範例定義語法：


   FirstName varchar(100) MASKED WITH (FUNCTION = 'partial(prefix,[padding],suffix)') NULL

替代語法的範例：


   ALTER COLUMN [Phone Number] ADD MASKED WITH (FUNCTION = 'partial(1,"XXXXXXX",0)')

這會將


   555.123.1234

之類的電話號碼變成


   5XXXXXXX

。
其他範例：


   ALTER COLUMN [Phone Number] ADD MASKED WITH (FUNCTION = 'partial(5,"XXXXXXX",0)')

這會將


   555.123.1234

之類的電話號碼變成


   555.1XXXXXXX

。 適用於： SQL Server 2022 (16.x)

使用資料類型 datetime 、 datetime2 、 date 、 time 、 datetimeoffset 、 smalldatetime 所定義的資料行遮罩方法。它有助於遮蔽日子的


   year => datetime("Y")

、


   month=> datetime("M")

、


   day=>datetime("D")

、


   hour=>datetime("h")

、


   minute=>datetime("m")

或


   seconds=>datetime("s")

部分。如何遮蔽 datetime 值的年份的範例：


   ALTER COLUMN BirthDay ADD MASKED WITH (FUNCTION = 'datetime("Y")')

如何遮蔽 datetime 值的月份的範例：


   ALTER COLUMN BirthDay ADD MASKED WITH (FUNCTION = 'datetime("M")')

如何遮蔽 datetime 值的分鐘的範例：


   ALTER COLUMN BirthDay ADD MASKED WITH (FUNCTION = 'datetime("m")')

具有資料表 SELECT 權限的使用者可以檢視資料表資料。定義為已遮罩的資料行會顯示遮罩的資料。請將 UNMASK 權限授與使用者，使其能夠從已定義遮罩的資料行擷取未遮罩的資料。

系統管理使用者和角色一律可以透過 CONTROL 權限 (包括 ALTER ANY MASK 和 UNMASK 權限) 檢視未遮罩的資料。根據原廠設計，系統管理使用者或角色 (例如 sysadmin 或 db_owner) 具有資料庫的 CONTROL 權限，而且可以檢視未遮罩的資料。

您不需要任何特殊權限，只要有結構描述的標準 CREATE TABLE 和 ALTER 權限，就能建立含有動態資料遮罩的資料表。

新增、取代或移除資料行遮罩則需要資料表的 ALTER ANY MASK 權限和 ALTER 權限。將 ALTER ANY MASK 授與資訊安全人員是適當作法。

UNMASK 許可權不會影響元數據可見性：僅授與 UNMASK 並不會揭露任何元數據。 UNMASK 一律需要伴隨 SELECT 權限才能有任何作用。範例：在資料庫範圍上授與 UNMASK，並在個別數據表上授與 SELECT 時，使用者只能看到其可從中選取的個別數據表元數據，而不是任何其他數據表的結果。另請參閱中繼資料可見性組態。

最佳做法與常見使用案例

在資料行建立遮罩並不會防止該資料行更新。所以儘管使用者在查詢遮罩資料行時會收到遮罩的資料，相同的使用者還是可在具有寫入權限時更新資料。您仍應使用適當的存取控制原則來限制更新權限。

使用 SELECT INTO 或 INSERT INTO 將資料從遮罩的資料行複製到另一個資料表，會讓資料在目標資料表中也有遮罩 (假設資料是由不具 UNMASK 權限的使用者所匯出)。

執行 SQL Server 匯入與匯出時會套用動態資料遮罩。包含遮罩資料行的資料庫會導致匯出的資料檔案包含遮罩資料 (假設檔案是由不具 UNMASK 權限的使用者所匯出)，而匯入的資料庫則會包含靜態遮罩的資料。

查詢遮罩的資料行

使用 sys.masked_columns 檢視來查詢已套用遮罩函數的資料表-資料行。此檢視繼承自 sys.columns 檢視。它會傳回 sys.columns 檢視中的所有資料行，加上 is_masked 和 masking_function 資料行，指出資料行是否已遮罩，若已遮罩，則指出定義了哪個遮罩函數。此檢視只會顯示已套用遮罩函數的資料行。

SELECT c.name, tbl.name as table_name, c.is_masked, c.masking_function
FROM sys.masked_columns AS c
JOIN sys.tables AS tbl
    ON c.[object_id] = tbl.[object_id]
WHERE is_masked = 1;
在資料庫層級具有 CONTROL SERVER 或 CONTROL 權限的使用者可以檢視其原始格式的遮罩資料。 這包括系統管理員使用者或角色，例如系統管理員、db_owner等。
下列資料行類型無法定義遮罩規則：
加密資料行 (永遠加密)
FILESTREAM
COLUMN_SET 或屬於疏鬆資料行集的資料行。
在計算資料行上無法設定遮罩，但如果計算資料行相依於具有 MASK 的資料行，則計算資料行會傳回遮罩的資料。
有資料遮罩的資料行不能是 FULLTEXT 索引的索引鍵。
PolyBase 外部資料表中的資料行。
對於不具 UNMASK 權限的使用者，已遭取代的 READTEXT、 UPDATETEXT和 WRITETEXT 陳述式在為「動態資料遮罩」設定的資料行上無法正常作用。
新增動態資料遮罩會實作為基礎資料表上的結構描述變更，因此無法在具有相依性的資料行上執行 (例如，計算資料行參考的資料行)。 試著針對具有相依性的資料行新增動態資料遮罩會導致錯誤 ALTER TABLE ALTER COLUMN _columnname_ failed because one or more objects access this column。 若要暫時解決這項限制，您可以先移除相依性，並新增動態資料遮罩，然後重新建立相依性。 例如，如果相依性是基於相依於該資料行索引，則您可以卸除索引，並新增遮罩，然後重新建立相依索引。
每當您計畫參考定義了資料遮罩函式的資料行的運算式時，也會遮蔽該運算式。 不論用來遮蔽參考資料行的函式 (預設、電子郵件、隨機、自訂字串) 為何，產生的運算式一律會以預設函式加以遮蔽。
跨資料庫查詢跨越兩個不同的 Azure SQL 資料庫或裝載在不同 SQL Server 實例上的資料庫，且牽涉到 MASKED 數據行上任何類型的比較或聯結作業，都未提供正確的結果。 從遠端伺服器傳回的結果已採用 MASKED 格式，並且不適合在本端進行任何類型的比較或聯結作業。
在索引檢視中參考基礎表時，不支援動態資料遮罩。
安全性注意事項：使用推斷或暴力破解方法略過遮罩
動態資料遮罩的設計是要藉由限制應用程式所使用之預先定義查詢集的資料曝光，簡化應用程式開發。 雖然動態資料遮罩也可以用來避免在直接存取生產資料庫時意外洩露機密資料，您必須特別注意具有特定查詢權限的無特殊權限使用者，可以套用技術以存取實際的資料。 如果需要授與這類特定存取權，應該使用稽核來監視所有的資料庫活動，並減輕這種情況。
例如，請考慮資料庫主體具有足夠的權限對資料庫執行特定查詢，並嘗試猜測基礎資料，最終推斷實際的值。 假設我們在 [Employee].[Salary] 資料行上定義了遮罩，而這位使用者直接連接到資料庫並開始猜測值，最後推斷出 [Salary] 資料表中的 Employees 值：
SELECT ID, Name, Salary FROM Employees
WHERE Salary > 99999 and Salary < 100001;
這示範了動態資料遮罩不應該單獨用來完全保護機密資料不受對資料庫執行特定查詢的使用者存取。 它適於防止機密資料不慎洩露，但無法防止惡意推斷基礎資料。
請務必適當地管理資料庫的權限，並隨時遵守最小必要權限的原則。 此外，請記得啟用稽核來追蹤資料庫上發生的所有活動。
SQL Server 2022 中引進的細微權限
從 SQL Server 2022 (16.x) 開始，您可以防止未經授權存取敏感性的資料，並透過在不同的資料庫層級中對未經授權的使用者將它遮蔽起來以獲得控制權。 您可以將資料庫層級、架構層級、數據表層級或數據行層級的 UNMASK 許可權授與或撤銷使用者、資料庫角色、Microsoft Entra 身分識別，或Microsoft Entra 群組。 此增強功能可提供更細微的方式來控制及限制未經授權存取資料庫中所儲存的資料，並改善資料安全性管理。
建立動態資料遮罩
下列範例將建立有三種不同動態資料遮罩類型的資料表。 範例會填入資料表，並選擇顯示結果。
-- schema to contain user tables
CREATE SCHEMA Data;
-- table with masked columns
CREATE TABLE Data.Membership (
    MemberID INT IDENTITY(1, 1) NOT NULL PRIMARY KEY CLUSTERED,
    FirstName VARCHAR(100) MASKED WITH (FUNCTION = 'partial(1, "xxxxx", 1)') NULL,
    LastName VARCHAR(100) NOT NULL,
    Phone VARCHAR(12) MASKED WITH (FUNCTION = 'default()') NULL,
    Email VARCHAR(100) MASKED WITH (FUNCTION = 'email()') NOT NULL,
    DiscountCode SMALLINT MASKED WITH (FUNCTION = 'random(1, 100)') NULL
-- inserting sample data
INSERT INTO Data.Membership (FirstName, LastName, Phone, Email, DiscountCode)
VALUES
('Roberto', 'Tamburello', '555.123.4567', '[email protected]', 10),
('Janice', 'Galvin', '555.123.4568', '[email protected]', 5),
('Shakti', 'Menon', '555.123.4570', '[email protected]', 50),
('Zheng', 'Mu', '555.123.4569', '[email protected]', 40);
會建立新的使用者，並在資料表所在的結構描述上授與 SELECT 權限。 在 MaskingTestUser 檢視遮罩資料時執行了查詢。
CREATE USER MaskingTestUser WITHOUT LOGIN;
GRANT SELECT ON SCHEMA::Data TO MaskingTestUser;
-- impersonate for testing:
EXECUTE AS USER = 'MaskingTestUser';
SELECT * FROM Data.Membership;
REVERT;
結果會顯示遮罩，方法是將資料從：
1    Roberto     Tamburello    555.123.4567    [email protected]    10
1    Rxxxxxo     Tamburello    xxxx            [email protected]              91
              DiscountCode 中的數字在每一次查詢結果中都是隨機的。
在現有的資料行上新增或編輯遮罩
使用 ALTER TABLE 陳述式將遮罩加入資料表的現有資料行中，或在該資料行編輯遮罩。

下列範例會將遮罩函數新增至 LastName 資料行：
ALTER TABLE Data.Membership
ALTER COLUMN LastName ADD MASKED WITH (FUNCTION = 'partial(2,"xxxx",0)');
以下範例會變更 LastName 資料行的遮罩函數：
ALTER TABLE Data.Membership
ALTER COLUMN LastName VARCHAR(100) MASKED WITH (FUNCTION = 'default()');
授與權限以檢視未遮蔽的資料
授與 UNMASK 權限可讓 MaskingTestUser 看見未遮罩的資料。
GRANT UNMASK TO MaskingTestUser;
EXECUTE AS USER = 'MaskingTestUser';
SELECT * FROM Data.Membership;
REVERT;
-- Removing the UNMASK permission
REVOKE UNMASK TO MaskingTestUser;
捨棄動態資料遮罩
以下陳述式會卸除先前範例中在 LastName 資料行建立的遮罩：
ALTER TABLE Data.Membership
ALTER COLUMN LastName DROP MASKED;
細微權限範例
建立結構描述以包含使用者資料表：
CREATE SCHEMA Data;
建立具有遮罩資料行的資料表：
CREATE TABLE Data.Membership (
    MemberID INT IDENTITY(1, 1) NOT NULL PRIMARY KEY CLUSTERED,
    FirstName VARCHAR(100) MASKED WITH (FUNCTION = 'partial(1, "xxxxx", 1)') NULL,
    LastName VARCHAR(100) NOT NULL,
    Phone VARCHAR(12) MASKED WITH (FUNCTION = 'default()') NULL,
    Email VARCHAR(100) MASKED WITH (FUNCTION = 'email()') NOT NULL,
    DiscountCode SMALLINT MASKED WITH (FUNCTION = 'random(1, 100)') NULL,
    BirthDay DATETIME MASKED WITH (FUNCTION = 'default()') NULL
插入範例資料：
INSERT INTO Data.Membership (FirstName, LastName, Phone, Email, DiscountCode, BirthDay)
VALUES
('Roberto', 'Tamburello', '555.123.4567', '[email protected]', 10, '1985-01-25 03:25:05'),
('Janice', 'Galvin', '555.123.4568', '[email protected]', 5, '1990-05-14 11:30:00'),
('Shakti', 'Menon', '555.123.4570', '[email protected]', 50, '2004-02-29 14:20:10'),
('Zheng', 'Mu', '555.123.4569', '[email protected]', 40, '1990-03-01 06:00:00');
建立結構描述以包含服務資料表：
CREATE SCHEMA Service;
建立具有遮罩資料行的服務資料表：
CREATE TABLE Service.Feedback (
    MemberID INT IDENTITY(1, 1) NOT NULL PRIMARY KEY CLUSTERED,
    Feedback VARCHAR(100) MASKED WITH (FUNCTION = 'default()') NULL,
    Rating INT MASKED WITH (FUNCTION = 'default()'),
    Received_On DATETIME
插入範例資料：
INSERT INTO Service.Feedback(Feedback, Rating, Received_On)
VALUES
('Good', 4, '2022-01-25 11:25:05'),
('Excellent', 5, '2021-12-22 08:10:07'),
('Average', 3, '2021-09-15 09:00:00');
在資料庫中建立不同的使用者：
CREATE USER ServiceAttendant WITHOUT LOGIN;
CREATE USER ServiceLead WITHOUT LOGIN;
CREATE USER ServiceManager WITHOUT LOGIN;
CREATE USER ServiceHead WITHOUT LOGIN;
授與讀取權限給資料庫中的使用者：
ALTER ROLE db_datareader ADD MEMBER ServiceAttendant;
ALTER ROLE db_datareader ADD MEMBER ServiceLead;
ALTER ROLE db_datareader ADD MEMBER ServiceManager;
ALTER ROLE db_datareader ADD MEMBER ServiceHead;
授與不同的 UNMASK 權限給使用者：
--Grant column level UNMASK permission to ServiceAttendant
GRANT UNMASK ON Data.Membership(FirstName) TO ServiceAttendant;
-- Grant table level UNMASK permission to ServiceLead
GRANT UNMASK ON Data.Membership TO ServiceLead;
-- Grant schema level UNMASK permission to ServiceManager
GRANT UNMASK ON SCHEMA::Data TO ServiceManager;
GRANT UNMASK ON SCHEMA::Service TO ServiceManager;
--Grant database level UNMASK permission to ServiceHead;
GRANT UNMASK TO ServiceHead;
在使用者 ServiceAttendant 的內容下查詢資料：
EXECUTE AS USER = 'ServiceAttendant';
SELECT MemberID, FirstName, LastName, Phone, Email, BirthDay
FROM Data.Membership;
SELECT MemberID, Feedback, Rating
FROM Service.Feedback;
REVERT;
在使用者 ServiceLead 的內容下查詢資料：
EXECUTE AS USER = 'ServiceLead';
SELECT MemberID, FirstName, LastName, Phone, Email, BirthDay
FROM Data.Membership;
SELECT MemberID, Feedback, Rating
FROM Service.Feedback;
REVERT;
在使用者 ServiceManager 的內容下查詢資料：
EXECUTE AS USER = 'ServiceManager';
SELECT MemberID, FirstName, LastName, Phone, Email, BirthDay
FROM Data.Membership;
SELECT MemberID, Feedback, Rating
FROM Service.Feedback;
REVERT;
在使用者 ServiceHead 的內容下查詢資料
EXECUTE AS USER = 'ServiceHead';
SELECT MemberID, FirstName, LastName, Phone, Email, BirthDay
FROM Data.Membership;
SELECT MemberID, Feedback, Rating
FROM Service.Feedback;
REVERT;
若要撤銷 UNMASK 權限，請使用下列 T-SQL 陳述式：
REVOKE UNMASK ON Data.Membership(FirstName) FROM ServiceAttendant;
REVOKE UNMASK ON Data.Membership FROM ServiceLead;
REVOKE UNMASK ON SCHEMA::Data FROM ServiceManager;
REVOKE UNMASK ON SCHEMA::Service FROM ServiceManager;
REVOKE UNMASK FROM ServiceHead;
              CREATE TABLE （Transact-SQL）

              ALTER TABLE （Transact-SQL）

              column_definition （Transact-SQL）

              sys.masked_columns （Transact-SQL）

              開始使用 SQL 資料庫動態資料遮罩 (Azure 入口網站)

              Fabric 資料倉儲中的動態資料遮罩