動態資料遮罩Dynamic Data Masking

適用於: 是SQL Server是Azure SQL Database是Azure Synapse Analytics (SQL DW)否平行處理資料倉儲APPLIES TO: YesSQL Server YesAzure SQL Database YesAzure Synapse Analytics (SQL DW) NoParallel Data Warehouse


動態資料遮罩 (DDM) 會對不具權限的使用者遮罩機密資料,從而限制其曝光。Dynamic data masking (DDM) limits sensitive data exposure by masking it to non-privileged users. 它可用來大幅簡化您的應用程式中安全性的設計和編碼。It can be used to greatly simplify the design and coding of security in your application.

動態資料遮罩讓客戶能夠指定要顯示多少敏感性資料,藉此協助防止未經授權存取敏感性資料,同時盡可能減少對應用程式層的影響。Dynamic data masking helps prevent unauthorized access to sensitive data by enabling customers to specify how much sensitive data to reveal with minimal impact on the application layer. 可以在指定資料庫欄位上設定 DDM,以隱藏查詢結果集中的敏感性資料。DDM can be configured on designated database fields to hide sensitive data in the result sets of queries. 使用 DDM 時,資料庫中的資料不會變更。With DDM the data in the database is not changed. 在現有應用程式使用動態資料遮罩相當容易,原因是遮罩規則已套用到查詢結果中。Dynamic data masking is easy to use with existing applications, since masking rules are applied in the query results. 許多應用程式都不需要修改現有查詢,就能遮罩機密資料。Many applications can mask sensitive data without modifying existing queries.

  • 中央資料遮罩原則可直接作用於資料庫中的機密欄位上。A central data masking policy acts directly on sensitive fields in the database.
  • 指定無法存取敏感性資料之特殊權限的使用者或角色。Designate privileged users or roles that do have access to the sensitive data.
  • DDM 的特色在於完整遮罩和部分遮罩功能,以及數值資料的隨機遮罩。DDM features full masking and partial masking functions, and a random mask for numeric data.
  • 簡單的 Transact-SQLTransact-SQL 命令可定義和管理遮罩。Simple Transact-SQLTransact-SQL commands define and manage masks.

例如,話務中心支援人員可以從來電者的社會安全號碼或信用卡號碼其中幾個數字加以識別。As an example, a call center support person may identify callers by several digits of their social security number or credit card number. 社會安全號碼或信用卡號碼都不應該完全透露給支援人員。Social security numbers or credit card numbers should not be fully exposed to the support person. 可以定義遮罩規則,以針對任何查詢的結果集中任何社會安全號碼或信用卡號碼的末四碼以外的所有數字進行遮罩處理。A masking rule can be defined that masks all but the last four digits of any social security number or credit card number in the result set of any query. 另一個例子是,開發人員可以使用適當的資料遮罩保護個人識別資訊 (PII) 資料,為疑難排解目的查詢生產環境,而不會違反法務遵循規定。For another example, by using the appropriate data mask to protect personally identifiable information (PII) data, a developer can query production environments for troubleshooting purposes without violating compliance regulations.

動態資料遮罩的目的在於限制機密限制的曝光,防止不該存取資料的使用者檢視該資料。The purpose of dynamic data masking is to limit exposure of sensitive data, preventing users who should not have access to the data from viewing it. 動態資料遮罩並不是用來防止資料庫使用者直接連接到資料庫,以及執行會讓機密資料片段曝光的全面查詢。Dynamic data masking does not aim to prevent database users from connecting directly to the database and running exhaustive queries that expose pieces of the sensitive data. 動態資料遮罩旨在補足其他 SQL ServerSQL Server 安全性功能 (稽核、加密、資料列層級安全性...),強烈建議您額外搭配這些功能使用此功能,讓資料庫中的敏感性資料獲得更妥善的保護。Dynamic data masking is complementary to other SQL ServerSQL Server security features (auditing, encryption, row level security...) and it is highly recommended to use this feature in conjunction with them in addition in order to better protect the sensitive data in the database.

動態資料遮罩提供於 SQL Server 2016 (13.x)SQL Server 2016 (13.x)Azure SQL DatabaseAzure SQL Database,並且使用 Transact-SQLTransact-SQL 命令來設定。Dynamic data masking is available in SQL Server 2016 (13.x)SQL Server 2016 (13.x) and Azure SQL DatabaseAzure SQL Database, and is configured by using Transact-SQLTransact-SQL commands. 如需使用 Azure 入口網站設定動態資料遮罩的詳細資訊,請參閱開始使用 SQL 資料庫動態資料遮罩 (Azure 入口網站)For more information about configuring dynamic data masking by using the Azure portal, see Get started with SQL Database Dynamic Data Masking (Azure portal).

定義動態資料遮罩Defining a Dynamic Data Mask

您可以在資料庫中的資料行定義遮罩規則,以模糊該資料行中的資料。A masking rule may be defined on a column in a table, in order to obfuscate the data in that column. 遮罩有四種類型。Four types of masks are available.

函式Function 描述Description 範例Examples
預設Default 請依據指定欄位的資料類型進行完整遮罩。Full masking according to the data types of the designated fields.

對於字串資料類型,請使用 XXXX,或在欄位大小少於 4 個字元時使用較少的 X (charncharvarcharnvarchartextntext)。For string data types, use XXXX or fewer Xs if the size of the field is less than 4 characters (char, nchar, varchar, nvarchar, text, ntext).

對於數值資料類型,請使用零值 (bigintbitdecimalintmoney numeric smallintsmallmoneytinyintfloatreal)。For numeric data types use a zero value (bigint, bit, decimal, int, money, numeric, smallint, smallmoney, tinyint, float, real).

對於日期與時間資料類型,請使用 01.01.1900 00:00:00.0000000 (datedatetime2datetimedatetimeoffsetsmalldatetimetime)。For date and time data types use 01.01.1900 00:00:00.0000000 (date, datetime2, datetime, datetimeoffset, smalldatetime, time).

對於二進位資料類型,請使用單一位元組的 ASCII 值 0 (binaryvarbinaryimage)。For binary data types use a single byte of ASCII value 0 (binary, varbinary, image).
範例資料行定義語法: Phone# varchar(12) MASKED WITH (FUNCTION = 'default()') NULLExample column definition syntax: Phone# varchar(12) MASKED WITH (FUNCTION = 'default()') NULL

替代語法的範例:ALTER COLUMN Gender ADD MASKED WITH (FUNCTION = 'default()')Example of alter syntax: ALTER COLUMN Gender ADD MASKED WITH (FUNCTION = 'default()')
電子郵件Email 此遮罩方法會讓電子郵件地址的第一個字母和常數後置詞 ".com" 以形式為電子郵件地址形式來公開。Masking method that exposes the first letter of an email address and the constant suffix ".com", in the form of an email address. aXXX@XXXX.com第 1 課:建立 Windows Azure 儲存體物件{2}。aXXX@XXXX.com. 範例定義語法: Email varchar(100) MASKED WITH (FUNCTION = 'email()') NULLExample definition syntax: Email varchar(100) MASKED WITH (FUNCTION = 'email()') NULL

替代語法的範例:ALTER COLUMN Email ADD MASKED WITH (FUNCTION = 'email()')Example of alter syntax: ALTER COLUMN Email ADD MASKED WITH (FUNCTION = 'email()')
隨機Random 此隨機遮罩函數可用在任何數值類型,會以指定範圍內隨機的值遮罩原始值。A random masking function for use on any numeric type to mask the original value with a random value within a specified range. 範例定義語法: Account_Number bigint MASKED WITH (FUNCTION = 'random([start range], [end range])')Example definition syntax: Account_Number bigint MASKED WITH (FUNCTION = 'random([start range], [end range])')

替代語法的範例:ALTER COLUMN [Month] ADD MASKED WITH (FUNCTION = 'random(1, 12)')Example of alter syntax: ALTER COLUMN [Month] ADD MASKED WITH (FUNCTION = 'random(1, 12)')
自訂字串Custom String 此遮罩方法會公開第一個及最後一個字母,並在中間新增自訂填補字串。Masking method that exposes the first and last letters and adds a custom padding string in the middle. prefix,[padding],suffix

注意:如果原始的值過短,而無法完成整個遮罩,一部分的前置詞或後置詞就不會曝光。Note: If the original value is too short to complete the entire mask, part of the prefix or suffix will not be exposed.
範例定義語法: FirstName varchar(100) MASKED WITH (FUNCTION = 'partial(prefix,[padding],suffix)') NULLExample definition syntax: FirstName varchar(100) MASKED WITH (FUNCTION = 'partial(prefix,[padding],suffix)') NULL

替代語法的範例:ALTER COLUMN [Phone Number] ADD MASKED WITH (FUNCTION = 'partial(1,"XXXXXXX",0)')Example of alter syntax: ALTER COLUMN [Phone Number] ADD MASKED WITH (FUNCTION = 'partial(1,"XXXXXXX",0)')

其他範例:Additional examples:


ALTER COLUMN [Social Security Number] ADD MASKED WITH (FUNCTION = 'partial(0,"XXX-XX-",4)')


您不需要任何特殊權限,只要有結構描述的標準 CREATE TABLEALTER 權限,就能建立含有動態資料遮罩的資料表。You do not need any special permission to create a table with a dynamic data mask, only the standard CREATE TABLE and ALTER on schema permissions.

新增、取代或移除資料行遮罩則需要資料表的 ALTER ANY MASK 權限和 ALTER 權限。Adding, replacing, or removing the mask of a column, requires the ALTER ANY MASK permission and ALTER permission on the table. ALTER ANY MASK 授與資訊安全人員是適當作法。It is appropriate to grant ALTER ANY MASK to a security officer.

具有資料表 SELECT 權限的使用者可以檢視資料表資料。Users with SELECT permission on a table can view the table data. 定義為已遮罩的資料行會顯示遮罩的資料。Columns that are defined as masked, will display the masked data. 請將 UNMASK 權限授與使用者,使其能夠從已定義遮罩的資料行擷取未遮罩的資料。Grant the UNMASK permission to a user to enable them to retrieve unmasked data from the columns for which masking is defined.

資料庫的 CONTROL 權限同時包括 ALTER ANY MASKUNMASK 權限。The CONTROL permission on the database includes both the ALTER ANY MASK and UNMASK permission.

最佳做法與常見使用案例Best Practices and Common Use Cases

  • 在資料行建立遮罩並不會防止該資料行更新。Creating a mask on a column does not prevent updates to that column. 所以儘管使用者在查詢遮罩資料行時會收到遮罩的資料,相同的使用者還是可在具有寫入權限時更新資料。So although users receive masked data when querying the masked column, the same users can update the data if they have write permissions. 您仍應使用適當的存取控制原則來限制更新權限。A proper access control policy should still be used to limit update permissions.

  • 使用 SELECT INTOINSERT INTO 將資料從遮罩的資料行複製到另一個資料表,會讓資料在目標資料表中也有遮罩。Using SELECT INTO or INSERT INTO to copy data from a masked column into another table results in masked data in the target table.

  • 執行 SQL ServerSQL Server 匯入與匯出時會套用動態資料遮罩。Dynamic Data Masking is applied when running SQL ServerSQL Server Import and Export. 包含遮罩資料行的資料庫會導致匯出的資料檔案包含遮罩資料 (假設檔案是由不具 UNMASK 權限的使用者所匯出),而匯入的資料庫則會包含靜態遮罩的資料。A database containing masked columns will result in an exported data file with masked data (assuming it is exported by a user without UNMASK privileges), and the imported database will contain statically masked data.

查詢遮罩的資料行Querying for Masked Columns

使用 sys.masked_columns 檢視來查詢已套用遮罩函數的資料表資料行。Use the sys.masked_columns view to query for table-columns that have a masking function applied to them. 此檢視繼承自 sys.columns 檢視。This view inherits from the sys.columns view. 它會傳回 sys.columns 檢視中的所有資料行,加上 is_maskedmasking_function 資料行,指出資料行是否已遮罩,若已遮罩,則指出定義了哪個遮罩函數。It returns all columns in the sys.columns view, plus the is_masked and masking_function columns, indicating if the column is masked, and if so, what masking function is defined. 此檢視只會顯示已套用遮罩函數的資料行。This view only shows the columns on which there is a masking function applied.

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;  

限制事項Limitations and Restrictions

下列資料行類型無法定義遮罩規則:A masking rule cannot be defined for the following column types:

  • 加密資料行 (永遠加密)Encrypted columns (Always Encrypted)


  • COLUMN_SET 或屬於疏鬆資料行集的資料行。COLUMN_SET or a sparse column that is part of a column set.

  • 在計算資料行上無法設定遮罩,但如果計算資料行相依於具有 MASK 的資料行,計算資料行就會傳回遮罩的資料。A mask cannot be configured on a computed column, but if the computed column depends on a column with a MASK, then the computed column will return masked data.

  • 有資料遮罩的資料行不能是 FULLTEXT 索引的索引鍵。A column with data masking cannot be a key for a FULLTEXT index.

對於不具 UNMASK 權限的使用者,已遭取代的 READTEXTUPDATETEXTWRITETEXT 陳述式在為動態資料遮罩設定的資料行上無法正常作用。For users without the UNMASK permission, the deprecated READTEXT, UPDATETEXT, and WRITETEXT statements do not function properly on a column configured for Dynamic Data Masking.

新增動態資料遮罩會實作為基礎資料表上的結構描述變更,因此無法在具有相依性的資料行上執行。Adding a dynamic data mask is implemented as a schema change on the underlying table, and therefore cannot be performed on a column with dependencies. 若要暫時解決這項限制,您可以先移除相依性,並新增動態資料遮罩,然後重新建立相依性。To work around this restriction, you can first remove the dependency, then add the dynamic data mask and then re-create the dependency. 例如,如果相依性是基於相依於該資料行索引,則您可以卸除索引,並新增遮罩,然後重新建立相依索引。For example, if the dependency is due to an index dependent on that column, you can drop the index, then add the mask, and then re-create the dependent index.

安全性注意事項︰使用推斷或暴力破解方法略過遮罩Security Note: Bypassing masking using inference or brute-force techniques

動態資料遮罩的設計是要藉由限制應用程式所使用之預先定義查詢集的資料曝光,簡化應用程式開發。Dynamic Data Masking is designed to simplify application development by limiting data exposure in a set of pre-defined queries used by the application. 雖然動態資料遮罩也可以用來避免在直接存取生產資料庫時意外洩露機密資料,您必須特別注意具有特定查詢權限的無特殊權限使用者,可以套用技術以存取實際的資料。While Dynamic Data Masking can also be useful to prevent accidental exposure of sensitive data when accessing a production database directly, it is important to note that unprivileged users with ad-hoc query permissions can apply techniques to gain access to the actual data. 如果需要授與這類特定存取權,應該使用稽核來監視所有的資料庫活動,並減輕這種情況。If there is a need to grant such ad-hoc access, Auditing should be used to monitor all database activity and mitigate this scenario.

例如,請考慮資料庫主體具有足夠的權限對資料庫執行特定查詢,並嘗試猜測基礎資料,最終推斷實際的值。As an example, consider a database principal that has sufficient privileges to run ad-hoc queries on the database, and tries to 'guess' the underlying data and ultimately infer the actual values. 假設我們在 [Employee].[Salary] 資料行上定義了遮罩,而這位使用者直接連接到資料庫並開始猜測值,最後推斷一組員工的 [Salary] 值︰Assume that we have a mask defined on the [Employee].[Salary] column, and this user connects directly to the database and starts guessing values, eventually inferring the [Salary] value of a set of Employees:

SELECT ID, Name, Salary FROM Employees
WHERE Salary > 99999 and Salary < 100001;
IdId 名稱Name SalarySalary
6254362543 Jane DoeJane Doe 00
9124591245 John SmithJohn Smith 00

此示範了動態資料遮罩不應該用來作為隔離的措施,完全保護機密資料不受對資料庫執行特定查詢的使用者存取。This demonstrates that Dynamic Data Masking should not be used as an isolated measure to fully secure sensitive data from users running ad-hoc queries on the database. 它適合防止機密資料不慎洩露,但無法防止惡意推斷基礎資料。It is appropriate for preventing accidental sensitive data exposure, but will not protect against malicious intent to infer the underlying data.

務必要正確管理資料庫的權限,並隨時遵守最小必要權限的原則。It is important to properly manage the permissions on the database, and to always follow the minimal required permissions principle. 此外,請記得啟用稽核來追蹤資料庫上發生的所有活動。Also, remember to have Auditing enabled to track all activities taking place on the database.


建立動態資料遮罩Creating a Dynamic Data Mask

下列範例將建立有三種不同動態資料遮罩類型的資料表。The following example creates a table with three different types of dynamic data masks. 範例會填入資料表,並選擇顯示結果。The example populates the table, and selects to show the result.

CREATE TABLE Membership  
   FirstName varchar(100) MASKED WITH (FUNCTION = 'partial(1,"XXXXXXX",0)') NULL,  
   LastName varchar(100) NOT NULL,  
   Phone varchar(12) MASKED WITH (FUNCTION = 'default()') NULL,  
   Email varchar(100) MASKED WITH (FUNCTION = 'email()') NULL);  
INSERT Membership (FirstName, LastName, Phone, Email) VALUES   
('Roberto', 'Tamburello', '555.123.4567', 'RTamburello@contoso.com'),  
('Janice', 'Galvin', '555.123.4568', 'JGalvin@contoso.com.co'),  
('Zheng', 'Mu', '555.123.4569', 'ZMu@contoso.net');  
SELECT * FROM Membership;  

新的使用者已建立,並已授與資料表的 SELECT 權限。A new user is created and granted SELECT permission on the table. TestUser 檢視遮罩資料時執行了查詢。Queries executed as the TestUser view masked data.

GRANT SELECT ON Membership TO TestUser;  
EXECUTE AS USER = 'TestUser';  
SELECT * FROM Membership;  

結果會顯示遮罩,方法是將資料從The result demonstrates the masks by changing the data from

1 Roberto Tamburello 555.123.4567 RTamburello@contoso.com


1 RXXXXXXX Tamburello xxxx RXXX@XXXX.com

在現有資料行上新增或編輯遮罩Adding or Editing a Mask on an Existing Column

使用 ALTER TABLE 陳述式將遮罩加入資料表的現有資料行中,或在該資料行編輯遮罩。Use the ALTER TABLE statement to add a mask to an existing column in the table, or to edit the mask on that column.
以下範例會將遮罩函數加入 LastName 資料行中:The following example adds a masking function to th LastName column:

ALTER TABLE Membership  
ALTER COLUMN LastName ADD MASKED WITH (FUNCTION = 'partial(2,"XXX",0)');  

以下範例會變更 LastName 資料行的遮罩函數:The following example changes a masking function on the LastName column:

ALTER TABLE Membership  
ALTER COLUMN LastName varchar(100) MASKED WITH (FUNCTION = 'default()');  

授與權限以檢視未遮罩的資料Granting Permissions to View Unmasked Data

授與 UNMASK 權限可讓 TestUser 看見未遮罩的資料。Granting the UNMASK permission allows TestUser to see the data unmasked.

EXECUTE AS USER = 'TestUser';  
SELECT * FROM Membership;  
-- Removing the UNMASK permission  

卸除動態資料遮罩Dropping a Dynamic Data Mask

以下陳述式會卸除先前範例中在 LastName 資料行建立的遮罩:The following statement drops the mask on the LastName column created in the previous example:

ALTER TABLE Membership   

另請參閱See Also

column_definition (Transact-SQL) column_definition (Transact-SQL)
sys.masked_columns (Transact-SQL) sys.masked_columns (Transact-SQL)
開始使用 SQL 資料庫動態資料遮罩 (Azure 入口網站)Get started with SQL Database Dynamic Data Masking (Azure portal)