Dynamic Data Masking is a new security feature introduced in SQL Server 2016 that limits the access of unauthorized users to sensitive data at the database layer.
動態數據屏蔽是SQL Server 2016中引入的新安全功能，可限制未經授權的用戶訪問數據庫層的敏感數據。
As an example of the need for such a feature is allowing the applications developers to access production data for troubleshooting purposes and preventing them from accessing the sensitive data at the same time, without affecting their troubleshooting process. Another example is the call center employee who will access the customer’s information to help him in his request, but the critical financial data, such as the bank account number or the credit card full number, will be masked to that person.
對此功能的需求的一個示例是，允許應用程序開發人員訪問生產數據以進行故障排除，并防止他們同時訪問敏感數據，而又不影響其故障排除過程。 另一個示例是呼叫中心員工，他將訪問客戶的信息以幫助他的請求，但是關鍵的財務數據（例如銀行帳號或信用卡全名）將被該人屏蔽。
sql防止注入。 Dynamic Data Masking, also known as DDM, is a very simple security feature that can be fully built using T-SQL commands which we are familiar with, easy to use and also flexible to design. This data protection method allows you to determine your “sensitive” data, by field in order to configure the suitable masking function to hide it from queries. This feature requires no coding effort from the application side or encrypting or applying any change to the real data stored in the disk.
動態數據屏蔽，也稱為DDM，是一種非常簡單的安全功能，可以使用我們熟悉，易于使用且設計靈活的T-SQL命令完全構建。 通過此數據保護方法，您可以按字段確定“敏感”數據，以便配置適當的屏蔽功能以使其對查詢隱藏。 此功能不需要應用程序側的編碼工作，也無需對磁盤中存儲的實際數據進行加密或應用任何更改。
Dynamic Data Masking masks the sensitive data “on the fly” to protect sensitive data from non-privileged users using built-in or customized masking functions, without preventing them from retrieving the unmasked data.
動態數據屏蔽可“動態”屏蔽敏感數據，以使用內置或自定義的屏蔽功能保護敏感數據免受非特權用戶的侵害，而不會阻止他們檢索未屏蔽的數據。
To implement DDM, first, you need to specify your sensitive data, the role to mask it and specify designated privileged users that have access to that sensitive data. The next step is to select and implement a masking function
屏蔽原理， 要實施DDM，首先，您需要指定敏感數據，屏蔽數據的角色并指定有權訪問該敏感數據的指定特權用戶。 下一步是選擇并實現屏蔽功能
There are four main types of masking functions that can be configured in Dynamic Data Masking, which we will introduce briefly here and use in the demo later.
在動態數據屏蔽中可以配置四種主要類型的屏蔽功能，我們將在此處簡要介紹這些功能，并在稍后的演示中使用。
The first type is the Default function that masks the data according to the field data type; if the field data type is binary, varbinary or image, a single byte of binary value 0 will be used to mask that field. For the date and time data types, the 01.01.1900 00:00:00.0000000 value will be used to mask that date field. If the data type of the masked field is one of the numeric data types, a zero value will be used to mask that field. For the string data types, XXXX value will be used to mask that field. If the field length is less than 4 characters, less number of Xs will be used to mask its value.
第一種是默認函數，它根據字段數據類型屏蔽數據。 如果字段數據類型是二進制，varbinary或image，則二進制值0的單個字節將用于屏蔽該字段。 對于日期和時間數據類型，將使用01.01.1900 00：00：00.0000000值屏蔽該日期字段。 如果被屏蔽字段的數據類型是數字數據類型之一，則將使用零值來屏蔽該字段。 對于字符串數據類型，XXXX值將用于屏蔽該字段。 如果字段長度小于4個字符，則將使用較少的Xs來掩蓋其值。
取消屏蔽。 The second masking method is the Email function that is used to mask the fields that store the email address. The Email function shows only the first character of the email address and mask the rest of the email, same as aXXX@XXXX.com.
第二種屏蔽方法是電子郵件功能，用于屏蔽存儲電子郵件地址的字段。 電子郵件功能僅顯示電子郵件地址的第一個字符，并掩蓋其余電子郵件，與aXXX@XXXX.com相同。
The Random masking function is used to mask any numeric data type by replacing the original value with a random value within the range specified in that function.
隨機掩碼功能用于通過用函數中指定范圍內的隨機值替換原始值來掩碼任何數字數據類型。
The last masking type is the Custom function, that allows you to define your own mask for the specified field by exposing the first and last letters defined by the prefix and suffix and add a padding that will be shown in the middle in the form of prefix, [padding value], suffix, taking into consideration that part of the prefix or the suffix will not be exposed if the field’s original value is too short to be masked.
保護地。 最后一種遮罩類型是“ 自定義”函數，它允許您通過暴露由前綴和后綴定義的首字母和尾字母，并添加將以前綴形式顯示在中間的填充來為指定字段定義自己的遮罩，[padding value]，后綴，并考慮到如果字段的原始值太短而無法掩蓋，則不會暴露部分前綴或后綴。
Like any feature in SQL Server, there are number of limitations for the Dynamic Data Masking feature, where you can’t define the DDM on an encrypted column, a column with FILESTREAM, COLUMN_SET or Sparse Column that is part of the Column_Set, Computed column or a key column in a FULLTEXT index. Also, if the column to be masked is part of an index or any type of dependencies, we should drop that dependency, configure the DDM on that column then create the dependency again. Also, Dynamic Data Masking will not prevent privileged users from altering the masked column or modifying the masked data, though.
像SQL Server中的任何功能一樣，動態數據屏蔽功能也有很多限制，您不能在加密列，包含FILESTREAM，COLUMN_SET或稀疏列的列（在Column_Set，Computed列中）中定義DDM。或FULLTEXT索引中的鍵列。 另外，如果要屏蔽的列是索引或任何類型的依賴項的一部分，我們應該刪除該依賴項，在該列上配置DDM，然后再次創建依賴項。 同樣，動態數據屏蔽也不會阻止特權用戶更改被屏蔽的列或修改被屏蔽的數據。
Let’s start our demo to understand how to configure the Dynamic Data Masking feature practically and how it works. Assume that we need to mask the employees’ critical data in order to prevent the developer who is responsible for developing and troubleshooting that system from viewing that sensitive data. First, we will create the Employee_Financial table where we will store the critical data:
讓我們開始演示，以了解如何實際配置動態數據屏蔽功能以及其工作方式。 假設我們需要掩蓋員工的關鍵數據，以防止負責對該系統進行開發和故障排除的開發人員查看該敏感數據。 首先，我們將創建Employee_Financial表，在其中存儲關鍵數據：
?
USE SQLShackDemo
GO
CREATE TABLE Employee_Financial (
Emp_ID INT IDENTITY(1, 1) PRIMARY KEY
,Emp_First_Name NVARCHAR(10) NOT NULL
,Emp_Last_Name NVARCHAR(10) NOT NULL
,Emp_Date_Of_Birth DATETIME NULL
,Emp_Salary INT NULL
,Emp_Email NVARCHAR(50) NULL
,Emp_Employment_Date DATETIME NULL
)被屏蔽了、 Once the table is created successfully, we will fill that table with 15 new records using the ApexSQL Generate – a SQL test data generator, where we customized the dates and salaries inserted fields to meet our requirements:
成功創建表后，我們將使用ApexSQL Generate – SQL測試數據生成器 ，用15條新記錄填充該表，在其中我們定制了插入日期和工資的字段以滿足我們的要求：
此屬性不可用于sql server7.0、 We will also create a test user that will have read only access on that table”
我們還將創建一個測試用戶，該用戶將對該表具有只讀訪問權限”
?
CREATE USER DDMUser WITHOUT LOGIN;??
GRANT SELECT ON Employee_Financial TO DDMUser;????If that user tries to retrieve all employees’ sensitive information from the Employee_Financial table:
如果該用戶嘗試從Employee_Financial表中檢索所有員工的敏感信息：
?
USE SQLShackDemo
GO
EXECUTE AS USER = 'DDMUser';??
SELECT * FROM Employee_Financial;??
REVERT;He will be able to see all critical information, which is a security disaster, as in the below result:
sql注入漏洞不會出現在哪個位置、 他將能夠看到所有關鍵信息，這是安全災難，如下所示：
To overcome this disaster, we will mask that critical information gradually and see how this user will see that data. We will mask the employees’ last name first using the Default masking function simply using ALTER TABLE … ALTER COLUMN T-SQL statement:
數據庫防止sql注入？ 為克服此災難，我們將逐漸掩蓋該關鍵信息，并查看該用戶將如何查看該數據。 我們將首先使用默認屏蔽功能首先使用ALTER TABLE…ALTER COLUMN T-SQL語句來屏蔽員工的姓氏：
?
ALTER TABLE Employee_Financial??
ALTER COLUMN EMP_Last_Name varchar(10) MASKED WITH (FUNCTION = 'default()');?? And again request that user to retrieve the employee data:
再次請求該用戶檢索員工數據：
?
EXECUTE AS USER = 'DDMUser';??
SELECT * FROM Employee_Financial;??
REVERT; You will see that the employees’ last name will be masked for that user, where he will see only XXXX values as in the following result set:
您會看到該雇員的姓氏將被屏蔽，在該用戶中，他只會看到XXXX值，如以下結果集中所示：
如何判斷和防止sql注入，
Just to remind you that nothing changed internally in the database itself, only the way this data represented for that user is changed, which is clear if I try to select the data using my admin user:
只是提醒您，數據庫本身內部沒有任何更改，只是更改了為該用戶表示的數據的方式，如果我嘗試使用管理員用戶選擇數據，這將很明顯：
The user still can see other critical information, so we will mask the employees’ emails using the EMAIL masking function:
用戶仍然可以看到其他關鍵信息，因此我們將使用EMAIL屏蔽功能來屏蔽員工的電子郵件：
?
ALTER TABLE Employee_Financial??
ALTER COLUMN EMP_Email nvarchar(50) MASKED WITH (FUNCTION = 'Email()');??And retrieve the data from the same table using the same user’s account:
并使用同一用戶的帳戶從同一表中檢索數據：
?
EXECUTE AS USER = 'DDMUser';??
SELECT * FROM Employee_Financial;??
REVERT;The user will see only the first letter of the employees’ email and the rest of XXXX@XXXX.COM values as clear in the below result set:
用戶將只看到員工電子郵件的第一個字母，其余XXXX@XXXX.COM值在以下結果集中清晰可見：
The employees’ salaries information can be replaced with no meaning values using the Random masking function, which will replace each salary with a random value between 1 and 9 as specified in the ALTER statement below:
可以使用“隨機屏蔽”功能將員工的薪水信息替換為無意義的值，該函數將使用以下ALTER語句中指定的1到9之間的隨機值替換每個薪水：
?
ALTER TABLE Employee_Financial??
ALTER COLUMN EMP_Salary int MASKED WITH (FUNCTION='random(1,9)');If this curious user tried to view the employees’ salaries using his account:
如果這個好奇的用戶嘗試使用他的帳戶查看員工的工資：
?
EXECUTE AS USER = 'DDMUser';??
SELECT * FROM Employee_Financial;??
REVERT;Unfortunately for him and fortunately for the security personnel, he will see only random values with no meanings as in the following result set:
對于他來說是不幸的，對于安全人員來說是幸運的，他將只能看到沒有意義的隨機值，如以下結果集中所示：
Again, the employee first name can be masked using a customized masking function that will display the first three letters, mask the following 4 letters with XXXX value then view the last three letters, unless the employee first name is smaller than the 3 to view and 4 to mask, which will be fully masked with XXXX value:
同樣，可以使用自定義的屏蔽功能屏蔽員工的名字，該功能將顯示前三個字母，用XXXX值屏蔽后面的4個字母，然后查看后三個字母，除非員工的名字小于3以查看和4遮罩，將使用XXXX值完全遮罩：
?
ALTER TABLE Employee_Financial??
ALTER COLUMN EMP_First_name nvarchar(10) MASKED WITH (FUNCTION= 'partial(3,"XXXX",3)');Running the same select statement using the same user account:
使用相同的用戶帳戶運行相同的select語句：
?
EXECUTE AS USER = 'DDMUser';??
SELECT * FROM Employee_Financial;??
REVERT;The employee first name will be masked, as described previously, which is clear in the below result set:
如前所述，員工的名字將被屏蔽，在以下結果集中很明顯：
As mentioned before in this article, no physical change performed on the data stored in that table. The users can be able to see the masked data if they granted UNMASK access on that database, which can be revoked too easily. The following statement will show us how the user will be able to see the masked data when granting him UNMASK access and how he will be prevented from viewing the same data once the UNMASK access revoked:
如本文前面所述，對該表中存儲的數據不進行任何物理更改。 如果用戶授予了該數據庫UNMASK訪問權限，則用戶可以看到被屏蔽的數據，而這很容易被撤銷。 下面的語句將向我們展示在授予用戶UNMASK訪問權限時用戶將如何查看被屏蔽的數據，以及在取消UNMASK訪問權限后如何阻止他查看相同的數據：
?
GRANT UNMASK TO DDMUser
GO
EXECUTE AS USER = 'DDMUser';??
SELECT * FROM Employee_Financial;??
REVERT;
GO
REVOKE UNMASK TO DDMUser
EXECUTE AS USER = 'DDMUser';??
SELECT * FROM Employee_Financial;??
REVERT; And the below result sets talk to show us the different access levels:
下面的結果集說明了我們不同的訪問級別：
A new system catalog view sys.masked_columns defined in SQL Server 2016, inherits sys.columns system view, can be used to retrieve information about the current Dynamic Data Masking configuration. Value 1 for the is_masked column indicates that this column is masked using a masking function identified in the masking_function column. The below T-SQL statement is used to retrieve the Dynamic Data Masking information by joining the sys.masked_columns view with the sys,tables view as follows:
SQL Server 2016中定義的新系統目錄視圖sys.masked_columns繼承了sys.columns系統視圖，可用于檢索有關當前動態數據掩碼配置的信息。 is_masked列的值1表示此列已使用masking_function列中標識的掩碼函數進行了掩碼。 下面的T-SQL語句用于通過將sys.masked_columns視圖與sys，tables視圖連接起來來檢索動態數據屏蔽信息，如下所示：
?
SELECT TBLS.name as TableName,MC.NAME ColumnName, MC.is_masked IsMasked, MC.masking_function MaskFunction??
FROM sys.masked_columns AS MC
JOIN sys.tables AS TBLS??
ON MC.object_id = TBLS.object_id??
WHERE is_masked = 1;?? The result in our case will be like:
在我們的案例中，結果將是：
We can easily drop the mask from the masked column using the same ALTER TABLE … ALTER COLUMN T-SQL statement as below:
我們可以使用以下相同的ALTER TABLE…ALTER COLUMN T-SQL語句輕松地從masked列中刪除掩碼：
?
ALTER TABLE Employee_Financial
ALTER COLUMN EMP_First_name DROP MASKED;Now the user can view the employees’ first name using his account after dropping the defined mask:
現在，用戶可以在刪除定義的掩碼后使用其帳戶查看員工的名字：
?
EXECUTE AS USER = 'DDMUser';??
SELECT * FROM Employee_Financial;??
REVERT;Enjoy it Mr. Curious:
盡情享受好奇先生：
Another advantage of Dynamic Data Masking is that, if the user tries to use SELECT INTO , INSERT INTO or SQL Server Import and Export wizard methods to copy the masked data to another table, he will be surprised that the result will be masked at the destination table.
動態數據屏蔽的另一個優點是，如果用戶嘗試使用SELECT INTO，INSERT INTO或SQL Server導入和導出向導方法將被屏蔽的數據復制到另一個表，他將驚訝地發現結果將在目標位置被屏蔽表。
Assume that there is an empty table called Curious with the same schema as the Employee_Financial table, and the DDMUser is granted SELECT, INSERT and UPDATE permissions on that table:
假設有一個名為Curious的空表，具有與Employee_Financial表相同的架構，并且DDMUser被授予對該表的SELECT，INSERT和UPDATE權限：
?
GRANT SELECT,INSERT, UPDATE ON Curious TO DDMUserIf he tries to copy the data from the Employee_Financial table to the Curious table and view it in the destination table:
如果他嘗試將數據從Employee_Financial表復制到Curious表并在目標表中查看它：
?
EXECUTE AS USER = 'DDMUser';
INSERT INTO [dbo].[Curious]([Emp_First_Name],[Emp_Last_Name],[Emp_Date_Of_Birth],[Emp_Salary],[Emp_Email],[Emp_Employment_Date])
SELECT [Emp_First_Name],[Emp_Last_Name],[Emp_Date_Of_Birth],[Emp_Salary],[Emp_Email],[Emp_Employment_Date] FROM Employee_FinancialSELECT * FROM CuriousThis user will certainly be surprised by the displayed result:
該用戶一定會對顯示的結果感到驚訝：
Dynamic Data Masking is a new security feature introduced in SQL Server 2016 that is used to protect sensitive data from being viewed by unauthorized users by masking the data on the result set without performing any change to the data stored in the disk. There are four types of masking functions that you can use to mask your critical data using various masking methods; built-in methods and your own customized methods. The DDM process is transparent from the application, where no coding effort should be performed at the application layer. You can choose the most suitable masking function for each field storing sensitive data in your test environment and replicate it to the production environment if you find that everything is working fine.
動態數據屏蔽是SQL Server 2016中引入的新安全功能，用于通過屏蔽結果集上的數據而不對磁盤中存儲的數據進行任何更改來保護敏感數據，防止未經授權的用戶查看敏感數據。 您可以使用四種類型的屏蔽功能，使用各種屏蔽方法來屏蔽關鍵數據。 內置方法和您自己的自定義方法。 DDM流程對應用程序是透明的，在應用程序層無需執行任何編碼工作。 您可以為測試環境中存儲敏感數據的每個字段選擇最合適的屏蔽功能，如果發現一切正常，則將其復制到生產環境中。
翻譯自: https://www.sqlshack.com/using-dynamic-data-masking-in-sql-server-2016-to-protect-sensitive-data/
版权声明：本站所有资料均为网友推荐收集整理而来,仅供学习和研究交流使用。
工作时间：8:00-18:00
客服电话
电子邮件
admin@qq.com
扫码二维码
获取最新动态
