Colin Lin (紫鴛) の 技術分享

回歸初心

2015-06-16

[SQL 2016]-新功能-Dynamic Data Masking

SQL Server 2016在安全性上做了相當多的改良，過去一直被詬病的Data Masking也在這此的版本中亮相了。
原則上在設定或使用上都不算複雜，只要權限配置好就能輕易的實現Data Masking的功能。
不過...... 小小的缺失仍會讓Data Masking不攻自破......

先前提到一個SQL Server 2016 CP2中針對資料安全性的新功能, 是Always Encrypted, 一個將資料先行保護起來, 僅允許具有足夠權限的人才能取出, 並且需要配合相關的條件才能實現. 在實做上來說, 要在即有的環境很快的導入並不容易.

而另一個SQL Server 2016 CP2的新功能, 也是針對資料安全性做了些增強, 也是很多資料庫管理人員一直期許的功能, 資料遮罩. 這個功能在其他牌子似乎已經有相似的功能, 在SQL Server過去的環境, 也多半只能運用View來實現, 對於管理上, 或是權限管理上, 並不輕鬆.

這次的資料遮罩功能, 可以說是還不錯用的功能, 當然才一開始, 並不期待完全符合每個人的想法, 也相信有些洞是可以被鑽的. 至少, 對於多數的使用者, 這樣的功能已經可以做到資料保護了.

來看一下官方怎麼說明這個功能.

Dynamic Data Masking

https://msdn.microsoft.com/zh-tw/library/mt130841%28v=sql.130%29.aspx

這個功能裡, 目前支援三種遮罩型態 (老實說除了自訂, 其他的個人覺得有待改進), 而特別是下default的預設函式, 那可是不知道怎麼形容的……. 不說了, 來看實例吧~~~

PS. 這句話怎麼看都怪!!

Use XXXX or fewer Xs if the size of the field is less than 4 characters for string data types (nchar and nvarchar. The max size is not supported.

留個證據~~

image

 

/**************************************************
建立測試資料庫: DynamicDataMasking
資料庫檔案位置: 預設路徑
**************************************************/
CREATE DATABASE DynamicDataMasking
ON PRIMARY 
(NAME = N'DynamicDataMasking_data', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\DynamicDataMasking_data.mdf')
 LOG ON 
(NAME = N'DynamicDataMasking_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\DynamicDataMasking_log.ldf');
GO

/**************************************************
建立測試資料表: DynamicDataMasking
並寫入幾筆資料進行測試用
**************************************************/
USE DynamicDataMasking;
GO

CREATE TABLE DemoCustomer
(
	name			nvarchar(10),
	Id				varchar(10),
	CellPhone		varchar(10),
	Email			varchar(50),
	Address			nvarchar(50),
	Payment			money,
	CreditNumber	varchar(19)
	CONSTRAINT [PK_DemoCustomer] PRIMARY KEY CLUSTERED
	(
		[ID] ASC
	)
);

INSERT INTO DemoCustomer
VALUES
	('王小明', 'A123456789', '0911223344', 'abc@google.com', '台北市羅斯福路一段1號', 123000, '1234-5678-2234-5678'),
	('林小華', 'B287654321', '0922998877', 'xyz@microsoft.com', '台中市中山路2號3樓', 500000, '4321-4321-5678-9876'),
	('陳小美', 'C135798642', '0933669911', 'qwert@yahoo.com.tw', '高雄市英雄路5號之1', 987654, '8765-2345-0987-1570');

取出資料來做基礎檢視.

image

 

/**************************************************
由於需要運用其他使用者帳戶來進行測試, 
因此建立一個使用者來模擬一般使用者帳戶只有SELECT權限
**************************************************/
CREATE USER TestUser WITHOUT LOGIN;
GRANT SELECT ON DemoCustomer TO TestUser;

/**************************************************
後續的查詢測試, 沒特別提及的, 就是套用上述建立的帳號來進行查詢
**************************************************/
EXECUTE AS USER = 'TestUser';
SELECT * FROM DemoCustomer;
REVERT;

依照官方文件中的說明, 要啟用Dynamic Data Masking的話, 要先啟用Trace Flag 209, 219. 雖然下列方式可以啟用全域Trace, 但是若是經常性重開系統的話, 那麼就放到SQL Server啟動參數裡吧!!

--啟用 DynamicDataMasking
DBCC TRACEON(209,219,-1)

接下來就看一連串的處理.

首先, 先來看最常運用的 (似乎是前幾年說個資的重要, 然後去很多地方看到名字都變成這樣), 也就是姓名的中間字變圈圈. 這是就可以使用自訂的方式, 使用partial來指定字元.

ALTER TABLE DemoCustomer
ALTER COLUMN name 
ADD MASKED WITH (FUNCTION='partial(1,"O",1)')

image

 

所以從上述的語法, 可以知道partial就是指定 (前面留幾個字, 中間放什麼字, 最後留幾個字). 因此就照著再來變動一下Id, 前後各留3碼, 中間放xxxx (四個, 加起來就是10碼)

ALTER TABLE DemoCustomer
ALTER COLUMN Id 
ADD MASKED WITH (FUNCTION='partial(3,"xxxx",3)')

image

 

而使用預設函式的話, 在CellPhone的欄位上, 先前指定的是varchar, 因此依照函式定義, 就是給4個x.

ALTER TABLE DemoCustomer
ALTER COLUMN CellPhone 
ADD MASKED WITH (FUNCTION='default()')

image

 

再來也是預設函式能用的, 就是mask mail address, 不過個人覺得, 最前面只留一碼, 識別度似乎低了些, 但是這個倒是有機會使用partial的方式來自己製作.

ALTER TABLE DemoCustomer
ALTER COLUMN Email 
ADD MASKED WITH (FUNCTION='email()')

image

 

很多時候會見到地址最後被mask起來, 這個的話也可以透過partial的方式, 把後方的參數指定為0碼即可.

ALTER TABLE DemoCustomer
ALTER COLUMN Address 
ADD MASKED WITH (FUNCTION='partial(5,"********",0)')

image

 

再來看一個使用預設函式default, 這次放到數值欄位, 就會轉成0的顯示方式. 有好也有壞, 有時可能使用亂數指定值, 在一些基礎運算上比較不會出錯誤.

ALTER TABLE DemoCustomer
ALTER COLUMN Payment 
ADD MASKED WITH (FUNCTION='default()')

image

 

最後這個欄位用的還是partial, 沒什麼特別的, 主要想用來講下一個動作.

ALTER TABLE DemoCustomer
ALTER COLUMN CreditNumber 
ADD MASKED WITH (FUNCTION='partial(0,"xxxx-xxxx-xxxx-",4)')

image

 

如果已經指定了欄位的mask方式, 事後想要變更怎麼辦? 那就再執行一次, 把想要變更的方式再指定一次就可以了, 會以最後一次下的語法為主.

ALTER TABLE DemoCustomer
ALTER COLUMN CreditNumber 
ADD MASKED WITH (FUNCTION='partial(4,"-xxxx-xxxx-",4)')

image

 

好啦, 做到這裡, 大致已經知道, 如果透過SQL System Administrator權限, 是可以很大方的看到所有資料, 而一般使用者則是僅可以見到被mask掉的資料. 若是你的老闆, 或是他部門應該有權限見到資料全貌的人員, 怎麼辦呢? 最快的方式絕對不是給予Administrator權限, 而是給予UNMASK的權限即可. 下列範例就是指定使用者能夠在被mask掉的資料中, 看到資料原貌.

CREATE USER UnmaskUser WITHOUT LOGIN;
GRANT SELECT ON DemoCustomer TO UnmaskUser;
GRANT UNMASK TO UnmaskUser;

EXECUTE AS USER = 'UnmaskUser';
SELECT * FROM DemoCustomer;
REVERT;

image

 

但是在SQL Server 2016 CP2的版本中, 還有是些小缺憾. 在資料型別能被進行mask的並不是所有型別, 例如在字串型別, 只支援在varchar、nvarchar上. 因此若是對於資料庫熟悉些的人員, "有機會" 可以進行型別轉換, 一旦轉到不支援mask的型別, 就可以把資料轉成非mask的面貌~~

EXECUTE AS USER = 'TestUser';
SELECT * FROM DemoCustomer;

SELECT 
	CAST(name AS char(10)),
	CONVERT(char(10), id)
FROM DemoCustomer;
REVERT;

image

 

當然也有人會有疑問, 就是在範例中, 資料是預載好的, 那麼新加入的資料會不會套用mask? 如果一般使用者看到的資料是mask的, 那麼寫入時會不會發生錯誤呢? 而自己寫入的會不會被mask呢? 答案很簡單, 看下例就會明白的.

CREATE USER InsertUser WITHOUT LOGIN;
GRANT SELECT, INSERT ON DemoCustomer TO InsertUser;

EXECUTE AS USER = 'InsertUser';
SELECT * FROM DemoCustomer;
INSERT INTO DemoCustomer
VALUES ('李小傑', 'D112233445', '0944987654', 'asdf@sina.com.cn', '台東市和平路100號', 34567, '1111-2222-3333-4444');
SELECT * FROM DemoCustomer;
REVERT;

image

 

若是要移除已經建立的mask, 那就下DROP MASKED的條件在ALTER COLUMN之下. 以本範例來說, 全數移除的話可以執行下列語法.

ALTER TABLE DemoCustomer ALTER COLUMN name DROP MASKED
ALTER TABLE DemoCustomer ALTER COLUMN Id DROP MASKED
ALTER TABLE DemoCustomer ALTER COLUMN CellPhone DROP MASKED
ALTER TABLE DemoCustomer ALTER COLUMN Email DROP MASKED
ALTER TABLE DemoCustomer ALTER COLUMN Address DROP MASKED
ALTER TABLE DemoCustomer ALTER COLUMN Payment DROP MASKED
ALTER TABLE DemoCustomer ALTER COLUMN CreditNumber DROP MASKED

系列文章

標籤雲