SQL Server 2016 的「動態資料遮罩 (Dynamic Data Masking)」

xuxiaohui9216

　　一些特別注重資訊安全、個人資料的公司或產業 (如: 金融、保險業)，通常「測試用資料庫」的資料，會加上「遮蔽；去識別化」的功能，避免個資外洩。以往必須自己撰寫 SQL 語句或 Stored Procedure 來處理，且遇到不同的資料庫使用者，要給予不同瀏覽權限時，寫起來就更麻煩。現在 SQL Server 2016 已內建「動態資料遮罩 (Dynamic Data Masking)」功能，除了資料可針對自訂邏輯來遮蔽，還可針對不同的資料庫使用者，給予不同的瀏覽權限。

　　

--建立測試資料表　　
CREATE TABLE Employee(

　　
  EmpNo int>　　
  [Name] nvarchar(5),

　　
>　　
  Hireday date,
　　
  Birthday date,
　　
  Age as datediff(year,Birthday,getdate())+1,
　　
  Gender bit,
　　
  Email varchar(50),
　　
  Tel varchar(20),
　　
  Salary money,
　　
  CreditCard varchar(20))
　　

　　
--新增測試資料
　　
INSERT Employee
　　
VALUES (N'王二','A123456789','19700101','19800101',0,'aaa@wizard.com','02-12345678',100500,'1234-5678-9012-3456')
　　
       ,(N'李二三','B123456789','19771010','19851010',1,'bbb@wizard.com','03-12345678',88000,'7890-1234-5678-9012')
　　
       ,(N'陳二三四','C123456789','19851231','19951231',1,'ccc@wizard.com','04-12345678',723456,'3456-7890-1234-5678')
　　


建立測試資料　　

--測試 default 遮罩，在 varchar 型別
　　
ALTER TABLE Employee>　　
GO
　　
--測試 default 遮罩，在 bit 型別 (bit會全為零)

　　
ALTER TABLE Employee>　　
GO
　　
--測試 default 遮罩，在 money 型別

　　
ALTER TABLE Employee>　　
GO
　　
--測試 default 遮罩，在 date 型別

　　
ALTER TABLE Employee>　　
GO
　　
--測試 email 遮罩，在 varchar 型別

　　
ALTER TABLE Employee>　　

　　

--建立一般使用者 user1　　
CREATE USER user1 WITHOUT LOGIN
　　
GRANT SELECT ON Employee TO user1
　　

　　
--以 sa (最高權限者) 瀏覽 (無遮罩)
　　
SELECT * FROM Employee (NOLOCK);
　　

　　
--以 user1 瀏覽 (有遮罩)
　　
EXEC AS USER='user1'
　　
SELECT * FROM Employee (NOLOCK);
　　
REVERT
　　

　　圖 1　使用者 sa 和 user1 看到的結果，前者無遮罩，後者有遮罩
　　--------------------------------------------
　　

--測試自訂遮罩
　　
ALTER TABLE Employee>
　　
ALTER TABLE Employee>
　　
ALTER TABLE Employee>　　

　　
--測試亂數遮罩

　　
ALTER TABLE Employee>　　

　　圖 2　使用者 sa 和 user1 看到的結果，前者無遮罩，後者有遮罩
　　--------------------------------------------
　　--查詢有啟用 Dynamic Data Masking 功能的資料表和欄位
　　

SELECT m.name, t.name, c.user_type_id, m.is_masked, m.masking_function　　

FROM sys.masked_columns (NOLOCK) m　　

JOIN sys.tables (NOLOCK) t ON m.[object_id]=t.[object_id]　　
JOIN sys.columns (NOLOCK) c ON m.[object_id]=c.[object_id] AND m.name=c.name
　　
WHERE m.is_masked=1;
　　

　　--------------------------------------------
　　--希望某個使用者 user2 瀏覽時，不受遮罩影響
　　GRANT UNMASK TO user2
　　--------------------------------------------
　　--針對某個資料表或欄位，移除 Dynamic Data Masking 功能

　　ALTER TABLE Employee>　　--測試是否已移除 Dynamic Data Masking 功能
　　exec('SELECT * FROM Employee (NOLOCK)') as user='user1'
　　--------------------------------------------
　　--------------------------------------------
　　參考書籍：

[1] Microsoft SQL Server 2016 資訊安全實戰, ch6, 悅知出版社

　　http://www.delightpress.com.tw/book.aspx?book_id=SKUD00027
　　--------------------------------------------
　　相關文章：

[1] 動態資料遮罩

　　https://msdn.microsoft.com/zh-tw/library/mt130841.aspx

[2] SQL2016-動態資料遮罩

　　https://dotblogs-testslot.azurewebsites.net/ricochen/2016/12/02/222254

[3] sql server 里面的 dynamic Data Masking

　　http://www.cnblogs.com/Gin-23333/p/5412028.html

[4] Dynamic Data Masking筆記(SQL Server 2016新功能)

　　https://dotblogs.com.tw/stanley14/2016/03/19/090752

[5] CP2-新功能-Dynamic Data Masking

　　https://dotblogs.com.tw/colinlin/2015/06/16/151581

[6] SQL Server 2016 Security Roadmap Session Notes

　　https://www.brentozar.com/archive/2015/05/sql-server-2016-security-roadmap-session-notes-msignite/
　　--------------------------------------------
　　--------------------------------------------