史丹利好熱

曾看過幾家廠商的動態資料遮罩(DDM)解決方案，有的透過AP伺服器與DB伺服器架設軟體或硬體的proxy來進行遮罩；有的則是資訊系統處理資料底層或畫面呈現時進行遮罩；趁晚上顧小孩睡不著，先筆記DB內部遮罩的作法。

 

1.先建立測試資料庫、資料表及3筆2016總統候選人的測試資料。

CREATE DATABASE dbDynamicDataMask
GO
USE dbDynamicDataMask
GO

CREATE TABLE Customers(
		ID varchar(11) primary key,
        Name nvarchar(10),
		Birthday date,
		Marriage char(1),
        Email varchar(50),                   
        Tel varchar(20),
        Salary numeric(13,2),
        CreditCard varchar(19))
GO

INSERT INTO Customers 
	VALUES ('A123456789', N'朱立倫', '19610607', 0, 'a01@company.com', '02-77203699', 3000000, '3567-5678-9012-3456')
	     , ('B123456789', N'蔡英文', '19560831', 1, 'b02@company.com', '03-77203699', 2000000, '4567-1234-5678-9012')
	     , ('C123456789', N'宋楚瑜', '19420316', 1, 'c03@company.com', '04-77203699', 1000000, '5567-7890-1234-5678')

查詢未遮罩前的資料表:

 

2.先針對4個欄位(生日Date、婚姻char、電話char及薪水numeric)測試一下預設遮罩。

ALTER TABLE Customers ALTER COLUMN Birthday ADD MASKED WITH(FUNCTION='default()')
ALTER TABLE Customers ALTER COLUMN Marriage ADD MASKED WITH(FUNCTION='default()')
ALTER TABLE Customers ALTER COLUMN Tel ADD MASKED WITH(FUNCTION='default()')
ALTER TABLE Customers ALTER COLUMN Salary ADD MASKED WITH(FUNCTION='default()')

建立一個帳號來測試Select，測試完畢後回到原本登入帳號:

CREATE USER u WITHOUT LOGIN
GRANT SELECT ON Customers TO u

EXEC AS USER = 'u'
SELECT * FROM Customers
REVERT

整理以下三種資料型態的預設遮罩值。

DataType	Mask Value
Date/Datetime	1900-01-01( 之前看TechNet文章是2000-01-01:可能版本差異)
char	x(up to 4)
numeric	0

 

3.測試特殊遮罩的方法:

可以用email()或是Partial來取姓名、信用卡或簽帳卡號，遮罩字元也可以依需要換成星號*或是圈○。

ALTER TABLE Customers ALTER COLUMN Email ADD MASKED WITH (FUNCTION = 'email()')
ALTER TABLE Customers ALTER COLUMN Name ADD MASKED WITH (FUNCTION = 'partial(1,"X",1)')  
ALTER TABLE Customers ALTER COLUMN CreditCard ADD MASKED WITH (FUNCTION = 'partial(4,"-xxxx-xxxx-",4)')

EXEC AS USER = 'u'
SELECT * FROM Customers
REVERT

4.測試看看各種轉換函數能不能破解

EXEC AS USER = 'u'
SELECT
	SUBSTRING(NAME,1,3) as NAME,
	CONVERT(DATE,Birthday,112) as Birthday,
	CAST(Salary AS numeric(10)) as Salary,
	LEFT(CreditCard,16) as CreditCard
FROM Customers
REVERT

破解不了(CTP2的問題解決了)

5.權限管理

如果想設置使用者u取消遮罩

GRANT UNMASK TO u
EXEC ('SELECT * FROM Customers') AS USER = 'u'

恢復遮罩

REVOKE UNMASK TO u

 

 

小結:

   控管人員權限上需要再想個好的應用方式，有的使用者單位因為有操作必要，不可以遮罩；有的單位像是CSR客服則需要遮罩，在應用程式端需要分成幾組帳號來存取資料庫。

 

參考:

SQL Server 2016 新功能搶先看 - 動態資料遮罩 / 多重 tempdb 資料檔

開始使用 SQL Database 動態資料遮罩 (Azure 入口網站)

PCI DSS

個人資料保護法