RiCo技術農場

Dynamic Data Masking(DDM)可以用來針對未經授權(UNMASK)使用者(sysadmin和db_owner角色除外)進行敏感資料遮罩，

主要目的在於防止不該存取資料的使用者檢視敏感機密資料，

但請注意這並非資料加密處理，如果你真的想保護你的資料，

建議你須搭配其他功能，如TDE、Always Encrypted和Row-Level Security，

現在我來簡單測試一些情況，看看資料遮罩是否如所預期發揮功效。

--建立資料表
CREATE TABLE dbo.DDM_Test
(
  ID INT PRIMARY KEY, 
  Name SYSNAME, 
  Salary INT,
  BirtyDay datetime,
  MyPicture BINARY(4),
  Email varchar(100)
);
GO
--新增資料
INSERT dbo.DDM_Test(ID, Name, Salary,BirtyDay,MyPicture,Email) VALUES
  (1,N'Rico',100000,'1980-01-01 11:11:11',CAST( 123456 AS BINARY(4) ),'rico@hsinglan.com'),
  (2,N'Sherry',75000,'1980-02-02 11:11:11',CAST( 789012 AS BINARY(4) ),'sherry@hsinglan.com'),
  (3,N'FiFi',70000,'2013-01-01 11:11:11',CAST( 345678 AS BINARY(4) ),'fifi@hsinglan.com');
GO

--授予該資料表查詢權限
GRANT SELECT ON dbo.DDM_Test TO webuser;

 

遮罩有四種類型

Default:

字串資料類型(或欄位大小少於 4 個字元)，將使用 XXXX。

數值資料類型，將使用 0000。

日期與時間資料類型，將使用 01.01.1900 00:00:00.0000000 。

二進位資料類型，將使用單一位元組的 ASCII 值 0 。

Email:只顯示第一個字元和常數後置詞 ".com"。

Random:指定範圍內隨機的值遮罩原始值。

Prtial:自訂遮罩字串。

--ID欄位使用隨機遮罩
ALTER TABLE dbo.DDM_Test ALTER COLUMN ID
ADD MASKED WITH(FUNCTION = 'random(1, 1)');--遮罩前2各字元
GO
--Name欄位使用自訂字串遮罩
ALTER TABLE dbo.DDM_Test ALTER COLUMN Name
ADD MASKED WITH (FUNCTION =  'partial(1,"@@-**",2)');--只顯示第1和最後2個字元
GO
--Salary欄位使用預設遮罩
ALTER TABLE dbo.DDM_Test ALTER COLUMN Salary
ADD MASKED WITH (FUNCTION = 'default()');
GO
--MyPicture欄位使用預設遮罩
ALTER TABLE dbo.DDM_Test ALTER COLUMN MyPicture
ADD MASKED WITH (FUNCTION = 'default()');
GO
--BirtyDay欄位使用預設遮罩
ALTER TABLE dbo.DDM_Test ALTER COLUMN BirtyDay
ADD MASKED WITH (FUNCTION ='default()');
GO
--Emaily欄位使用email遮罩
ALTER TABLE dbo.DDM_Test ALTER COLUMN Email
ADD MASKED WITH (FUNCTION =  'email()');
GO

--使用webuser(不具有UNMASK 權限) 查詢
EXECUTE AS USER = N'webuser';
GO
SELECT * FROM dbo.DDM_Test;
GO
REVERT;
--具有UNMASK 權限
SELECT * FROM dbo.DDM_Test;

--移除遮罩

ALTER TABLE dbo.DDM_Test ALTER COLUMN Email DROP MASKED;

 

測試避開遮罩可能性

Select into、insert into:需使用未經UNMASK授權使用者才有遮罩效果。

EXECUTE AS USER = N'webuser';
GO
DECLARE @test TABLE(
  ID INT, 
  Name SYSNAME, 
  Salary INT,
  BirtyDay datetime,
  MyPicture BINARY(4),
  Email varchar(100)
);
insert into @test select * from dbo.DDM_Test
select * from @test
REVERT;
GO
--查詢遮罩的資料行
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 and tbl.name in ('DDM_TestC','DDM_TestB')

 

資料型別轉換測試:持續有遮罩效果。

EXECUTE AS USER = N'webuser';
GO
SELECT cast(tbl.ID as varchar(10)) FROM dbo.DDM_Test as tbl;
GO
REVERT;

 

透過推斷猜測資料:雖然資料表面被遮罩了，但還是可以知道正確結果。

假設我想知道薪資大於80000有那些同事。

EXECUTE AS USER = N'webuser';
GO
--查詢薪資大於80000
SELECT * FROM dbo.DDM_Test
WHERE Salary > 80000;

雖然資料都被遮罩了，但名字顯示第1個和最後2個字元，

由於我知道所有同事英文名字，所以我透過以下查詢確認，依然可知道該名同事是誰

EXECUTE AS USER = N'webuser';
GO
--確認rico是否大於80000
SELECT * FROM dbo.DDM_Test
WHERE Salary > 80000 and Name = 'rico'

關於更多限制和安全注意事項請自行參考BOL。

 

Enjoy SQL Server 2016

 

參考

Dynamic Data Masking (DDM)

[SQL SERVER][Security]瞭解透明資料加密 (TDE)#簡介

[SQL SERVER][Security]瞭解透明資料加密 (TDE)#實做