一些特別注重資訊安全、個人資料的公司或產(chǎn)業(yè) (如: 金融、保險業(yè))，通常「測試用資料庫」的資料，會加上「遮蔽；去識別化」的功能，避免個資外洩。以往必須自己撰寫 SQL 語句或 Stored Procedure 來處理，且遇到不同的資料庫使用者，要給予不同瀏覽權(quán)限時，寫起來就更麻煩。現(xiàn)在 SQL Server 2016 已內(nèi)建「動態(tài)資料遮罩 (Dynamic Data Masking)」功能，除了資料可針對自訂邏輯來遮蔽，還可針對不同的資料庫使用者，給予不同的瀏覽權(quán)限。
--建立測試資料表 CREATE TABLE Employee( EmpNo int identity primary key, [Name] nvarchar(5), ID varchar(10), 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 ALTER COLUMN Tel ADD MASKED WITH(FUNCTION='default()') GO --測試 default 遮罩，在 bit 型別 (bit會全為零) ALTER TABLE Employee ALTER COLUMN Gender ADD MASKED WITH(FUNCTION='default()') GO --測試 default 遮罩，在 money 型別 ALTER TABLE Employee ALTER COLUMN Salary ADD MASKED WITH(FUNCTION='default()') GO --測試 default 遮罩，在 date 型別 ALTER TABLE Employee ALTER COLUMN Hireday ADD MASKED WITH(FUNCTION='default()') GO --測試 email 遮罩，在 varchar 型別 ALTER TABLE Employee ALTER COLUMN Email ADD MASKED WITH (FUNCTION = 'email()')
--建立一般使用者 user1 CREATE USER user1 WITHOUT LOGIN GRANT SELECT ON Employee TO user1 --以 sa (最高權(quán)限者) 瀏覽 (無遮罩) SELECT * FROM Employee (NOLOCK); --以 user1 瀏覽 (有遮罩) EXEC AS USER='user1' SELECT * FROM Employee (NOLOCK); REVERT
圖 1 使用者 sa 和 user1 看到的結(jié)果，前者無遮罩，後者有遮罩
--------------------------------------------
--測試自訂遮罩 ALTER TABLE Employee ALTER COLUMN [Name] ADD MASKED WITH (FUNCTION = 'partial(1,"X",1)') --Name 保留第一個和最後一個字，其他用 X 取代 ALTER TABLE Employee ALTER COLUMN CreditCard ADD MASKED WITH (FUNCTION = 'partial(0,"xxxx-xxxx-xxxx-",4)') --信用卡，只顯示最後四碼 ALTER TABLE Employee ALTER COLUMN ID ADD MASKED WITH (FUNCTION = 'partial(2,"________",0)') --ID 保留前兩個字，其他用 8 個底線符號取代 --測試亂數(shù)遮罩 ALTER TABLE Employee ALTER COLUMN Salary ADD MASKED WITH(FUNCTION = 'random(10000,100000)') --Salary 顯示介於 10000 到 100000 的亂數(shù)
圖 2 使用者 sa 和 user1 看到的結(jié)果，前者無遮罩，後者有遮罩
--------------------------------------------
--查詢有啟用 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 ALTER COLUMN CreditCard DROP MASKED
--測試是否已移除 Dynamic Data Masking 功能
exec('SELECT * FROM Employee (NOLOCK)') as user='user1'
--------------------------------------------
--------------------------------------------
參考書籍：
[1] Microsoft SQL Server 2016 資訊安全實戰(zhàn), ch6, 悅知出版社
http://www.delightpress.com.tw/book.aspx?book_id=SKUD00027
--------------------------------------------
相關(guān)文章：
[1] 動態(tài)資料遮罩
https://msdn.microsoft.com/zh-tw/library/mt130841.aspx
[2] SQL2016-動態(tài)資料遮罩
https://dotblogs-testslot.azurewebsites.net/ricochen/2016/12/02/222254
[3] sql server 里面的 dynamic Data Masking
http://www.rzrgm.cn/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/
--------------------------------------------
--------------------------------------------
浙公網(wǎng)安備 33010602011771號