摘要

在SQL Server安全系列專題月報分享中，我們已經(jīng)分享了：如何使用對稱密鑰實現(xiàn)SQL Server列加密技術(shù)、使用非對稱密鑰加密方式實現(xiàn)SQL Server列加密、使用混合密鑰實現(xiàn)SQL Server列加密技術(shù)、列加密技術(shù)帶來的查詢性能問題以及相應解決方案和行級別安全解決方案這五篇文章，文章詳情可以參見往期月報。本期月報我們分享使用SQL Server 2016 dynamic data masking實現(xiàn)隱私數(shù)據(jù)列的打碼技術(shù)最佳實踐。

問題引入

在平日的生活中，我們或多或少都經(jīng)歷過廣告推銷、電話詐騙，不厭其煩，甚至更為嚴重到銀行卡號泄漏、身份證號泄漏等更為嚴重的情況。這個時候，于是我們就在想有沒有技術(shù)手段來盡量或最大限度的保護我們隱私數(shù)據(jù)安全呢？答案是肯定的，SQL Server 2016版本首次引入了dynamic data masking來實現(xiàn)隱私數(shù)據(jù)列的打碼技術(shù)，讓我們一起來看看如何實現(xiàn)類似于手機號、身份證號、駕照號等隱私數(shù)據(jù)打碼技術(shù)。

原理分析

數(shù)據(jù)列打碼技術(shù)的本身我們并不陌生，就是將一些比較私密的數(shù)據(jù)信息隱藏起來，僅開放給有較高權(quán)限的用戶查看完整數(shù)據(jù)。打碼技術(shù)本身并不會對數(shù)據(jù)做任何的加密、解密等操作。嚴格意義上講，數(shù)據(jù)打碼不是一個完整的數(shù)據(jù)安全解決方案，但是它可以作為安全策略中重要的一環(huán)來有效避免用戶隱私數(shù)據(jù)列的泄漏。讓我們一起來看看在SQL Server 2016 dynamic data masking是如何實現(xiàn)的。

實現(xiàn)方法

創(chuàng)建測試數(shù)據(jù)庫

為了測試方便，我們專門創(chuàng)建了測試數(shù)據(jù)庫TestDb。

--Step 1 - Create MSSQL sample database USE master GO IF DB_ID('TestDb') IS NULLCREATE DATABASE [TestDb]; GO

創(chuàng)建測試表

首先，我們創(chuàng)建一張常規(guī)表CustomerInfo，來存放客戶信息，其中，CustomerPhone列為用戶隱私數(shù)據(jù)，存放了用戶的手機號碼。

--Step 2 - Create Test Table, init records USE [TestDb] GO IF OBJECT_ID('dbo.CustomerInfo', 'U') IS NOT NULLDROP TABLE dbo.CustomerInfo CREATE TABLE dbo.CustomerInfo ( CustomerId INT IDENTITY(10000,1) NOT NULL PRIMARY KEY, CustomerName VARCHAR(100) NOT NULL, CustomerPhone CHAR(11) NOT NULL );-- Init Table INSERT INTO dbo.CustomerInfo VALUES ('CustomerA','13402872514') ,('CustomerB','13880674722') ,('CustomerC','13487759293') GO

創(chuàng)建測試用戶

為了方便觀察和檢查測試效果，我們創(chuàng)建一個測試賬號DemoUser。

-- Step3: Create a DemoUser to test USE [TestDb] GO CREATE USER DemoUser WITHOUT LOGIN; GRANT SELECT ON dbo.CustomerInfo TO DemoUser; GOEXECUTE AS USER = 'DemoUser'; -- Verify data SELECT * FROM dbo.CustomerInfoREVERT

常規(guī)情況下，測試賬號，可以清清楚楚，明明白白看到用戶所有數(shù)據(jù)，包含客戶手機號這種關(guān)鍵的隱私數(shù)據(jù)。如果，這個用戶有不軌之心是非常容易將這些信息泄漏、導出的，安全風險較大

客戶手機號打碼

于是，我們想，如果能夠?qū)⒖蛻綦[私數(shù)據(jù)，比如，電話號碼（身份證號碼、銀行卡號等）打碼的話，那么測試賬號就無法查看到用戶完整的數(shù)據(jù)信息了。打碼方法如下：

-- Step4: Alter phone column add data mask USE [TestDb] GOALTER TABLE dbo.CustomerInfo ALTER COLUMN CustomerPhone ADD MASKED WITH(FUNCTION='partial(3, "****", 4)');

由于CustomerPhone是11位數(shù)字，我們使用partial方法打碼隱藏中間四位，打碼符號使用星號，保留前三位和后四位數(shù)數(shù)字即可。

查詢打碼列

打碼完畢，我們使用系統(tǒng)試圖查看打碼列和打碼函數(shù)：

-- Step5. Query system view to check data mask SELECTdb_name() as database_name, SCHEMA_NAME(schema_id) AS schema_name, tbl.name as table_name, c.name as column_name, c.is_masked, c.masking_function FROM sys.masked_columns AS c WITH (NOLOCK)INNER JOIN sys.tables AS tbl WITH(NOLOCK)ON c.[object_id] = tbl.[object_id] WHERE c.is_masked = 1AND tbl.name = 'CustomerInfo';

從結(jié)果可以看到我們已經(jīng)將表TestDb.dbo.CustomerInfo中字段CustomerPhone打碼，打碼函數(shù)為partial(3, "**", 4)，結(jié)果展示如下所示：

測試用戶查看數(shù)據(jù)

打碼完畢后，再次使用DemoUser測試賬號查看打碼后的數(shù)據(jù)：

-- Step6: Demo user to query and verify data USE [TestDb] GO EXECUTE AS USER = 'DemoUser'; -- Verify data SELECT * FROM dbo.CustomerInfoREVERT

從查詢結(jié)果展示來看，客戶手機號碼列中間四位已經(jīng)成功打碼了，測試賬號已經(jīng)無法獲取到完整的客戶電話號碼了。

修改打碼符號

有時候，有的人會說，我不喜歡星號，能否換個打碼姿勢，我更喜歡使用字母X。只要你喜歡，隨便切換，方法如下：

-- Step7: What if I want to change the mask sign from * to X USE [TestDb] GOALTER TABLE dbo.CustomerInfo ALTER COLUMN CustomerPhone CHAR(11) MASKED WITH(FUNCTION='partial(3, "XXXX", 4)');

現(xiàn)在打碼符號變成了X，展示如下：

新增隱私打碼列

現(xiàn)在我們需要增加一個新的列，用來存放用戶email地址，也請同時打碼。非常簡單，新增列的時候使用email打碼函數(shù)即可，如下所示：

-- Step8: and I want to add a new email mask column ALTER TABLE dbo.CustomerInfo ADD Email varchar(100) MASKED WITH (FUNCTION = 'email()') NOT NULL DEFAULT('demo.user@test.com')

查詢打碼列特定值

有的人可能會問，手機號碼被打碼了，這個列會影響我的WHERE語句查詢嗎？當然不會，因為data mask技術(shù)本身并沒有對數(shù)據(jù)做任何修改，只是在展示的時候，打碼隱藏掉部分信息而已。

-- Step9: Demo user to query the specified phone customer info USE [TestDb] GO EXECUTE AS USER = 'DemoUser'; -- Verify data SELECT * FROM dbo.CustomerInfo WHERE CustomerPhone = '13880674722'REVERT

查詢結(jié)果展示，手機號碼和email地址始終被打碼。

拷貝存在打碼列的表

我們說data mask技術(shù)并沒有加密、修改數(shù)據(jù)本身。到目前為止，測試賬號DemoUser已經(jīng)無法獲取到客人的關(guān)鍵隱私數(shù)據(jù)了，那么他能夠?qū)⒂脩魯?shù)據(jù)Copy、導出嗎？讓我們做一個簡單的測試，DemoUser將表CustomerInfo復制到一個新表CustomerInfo_copied中：

-- Step10: Ops, if I copy a new table from the data masked table, I can't get the unmasked data now. USE [TestDb] GO GRANT CREATE TABLE TO DemoUser; GRANT ALTER ON SCHEMA::dbo TO DemoUser; EXECUTE AS USER = 'DemoUser'; -- Verify data SELECT * INTO dbo.CustomerInfo_copied FROM dbo.CustomerInfoREVERTGRANT SELECT ON dbo.CustomerInfo_copied TO DemoUser; EXECUTE AS USER = 'DemoUser'; SELECT * FROM dbo.CustomerInfo_copied REVERT

DemoUser復制了客戶信息數(shù)據(jù)到新表后，查看新表中的數(shù)據(jù)，依然是被打碼的，測試用戶無法導出、復制客人的隱私數(shù)據(jù)。達到了安全策略保護客戶隱私數(shù)據(jù)的目的，展示結(jié)果如下：

我想要在無碼的世界

如果有一天DemoUser成了高權(quán)限用戶，確實需要查看客戶隱私數(shù)據(jù)列，這個時候，我們可以給予測試賬號unmask的權(quán)限，他就可以看到完整的客戶數(shù)據(jù)了。方法如下：

-- Step 11: But, how can demo user to query the unmasked data? USE TestDB GOGRANT UNMASK TO DemoUser; EXECUTE AS USER = 'DemoUser'; SELECT * FROM dbo.CustomerInfo; REVERT; -- Removing the UNMASK permission REVOKE UNMASK TO DemoUser;

此時，DemoUser查詢到的數(shù)據(jù)，是非常完整的客人數(shù)據(jù)。

刪掉打碼

刪除打碼，讓所有用戶回歸無碼的世界。

-- Step 12: all the demos have been done, it's time to drop the mask. USE TestDB GO ALTER TABLE dbo.CustomerInfo ALTER COLUMN CustomerPhone DROP MASKED;

最后總結(jié)

本期月報我們分享了使用SQL Server 2016引入的新特性dynamic data masking實現(xiàn)客戶數(shù)據(jù)打碼技術(shù)，防止未授權(quán)用戶查看、導出用戶關(guān)鍵隱私數(shù)據(jù)，最大限度保證用戶數(shù)據(jù)安全性。

原文鏈接
本文為云棲社區(qū)原創(chuàng)內(nèi)容，未經(jīng)允許不得轉(zhuǎn)載。

總結(jié)

以上是生活随笔為你收集整理的MSSQL - 最佳实践 - 如何打码隐私数据列的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯，歡迎將生活随笔推薦給好友。