動態資料遮罩透過.NET Strings.Left("AAAAA",1)+ PadRight(8,’*’)+ Strings.Right("AAAAA",1)也可輕鬆達到，
但如果還要針對使用者進行顯示權限處理就會費工一點，
現在使用SQL2016可以幫你大幅簡化應用程式中安全性的設計。
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
參考
