Share via

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

  • Article

SQL Server 2016 新功能搶先看系列第三波，今天再帶大家搶先看其他新功能！

    1. SQL Server 2016 新功能搶先看 - Stretch Database / Managed Backup (內含 Demo 畫面)

    2. SQL Server 2016 新功能搶先看 - Live Query Statistics / JSON

本文將帶大家了解以下新功能：

動態資料遮罩

多重 tempdb 資料檔

 

動態資料遮罩

說明

動態資料遮罩（Dynamic Data Masking）是一項可以讓開發人員或未經授權的使用者查詢機敏資料時，事先定義好的遮罩來將資料模糊化，透過這項功能您再也不需要在測試階段自行撰寫程式或使用協力廠商的工具來產生或模糊化資料，就可以輕鬆保護機敏資料不外洩。

這項功能一開始是實作在 Microsoft Azure SQL Database，利用 Azure 管理入口網站就能輕鬆地為資料行或別名設定所要使用的遮罩功能。現在不需要上雲端也可以使用這項方便的功能，SQL Server 2016 已經內建動態資料遮罩，但使用方式稍微和 Azure SQL Database 有些不同，本文將對此做詳盡的介紹。

前置作業

在開始之前以下列 T-SQL 指令碼在 tempdb 資料庫建立測試資料。

USE tempdb

 

GO

 

IF EXISTS (SELECT * FROM sys.tables WHERE name = 'Customers')

    DROP TABLE Customers

GO

 

CREATE TABLE [dbo].[Customers](

        [Name] [nvarchar](5) NOT NULL,                  --姓名

        [ID] [varchar](10) NOT NULL,                         --身分證字號

        [Email] [varchar](50) NOT NULL,                   --電子郵件

        [Tel] [varchar](20) NOT NULL,               --電話

        [Address] [nvarchar](50) NOT NULL,    --地址

        [Salary] [money] NOT NULL,                          --收入

        [CreditCard] [varchar](19) NOT NULL,         --信用卡號

 CONSTRAINT [PK_Customers] PRIMARY KEY CLUSTERED

(

        [ID] ASC

)

)

 

GO

 

INSERT INTO [dbo].[Customers] ([Name],[ID],[Email],[Tel],[Address],[Salary],[CreditCard])

     VALUES (N'王小明','A123456789','a01@contoso.com','02-23452345',N'台北市大安區和平東路一號',1999999,'1234-5678-5678-1234')

            ,(N'陳大華','B123789456','b02@contoso.com','03-23451234',N'桃園市中山路100號',1480912,'1478-9654-1236-5478')

            ,(N'林中偉','C221987456','c03@contoso.com','04-98761234',N'台中市中正路168號3樓',2139458,'9632-1456-9874-5632')

GO

 

SELECT *

FROM [dbo].[Customers]

 

GO

上述指令碼執行結果如下圖，我們就用這 3 筆資料來測試 SQL Server 2016 如何使用動態資料遮罩來保護機敏資料。

遮罩功能

目前 SQL Server 2016 CTP2 支援的遮罩功能只有 3 種，比起 Azure SQL Database 所提供的動態資料遮罩功能明顯少了許多，兩者的遮罩功能比較如下表：

遮罩功能

SQL Server 2016 CTP2

Azure SQL Database

Default

V

V

Credit card

X

V

Social security number

X

V

Email

V

V

Random number

X

V

Custom

V

V

除了遮罩功能不同外，要在 SQL Server 2016 使用動態資料遮罩，目前只能利用 T-SQL 來設定，在設定前必須先執行下列 T-SQL 來啟用指定的追蹤旗標，否則 SSMS 會出現不認得 ADD MASKED 敘述的錯誤。

DBCC TRACEON(209,219,-1)

接著就可以下列 T-SQL 來建立動態資料遮罩：

ALTER TABLE [資料表名稱]

ALTER COLUMN [資料行名稱]

ADD MASKED WITH（FUNCTION='遮罩功能'）

有關遮罩功能的輸出效果，說明如下：

1. Default：依照資料行的資料類型而有不同的輸出效果。

           i. 字元字串類型的資料行會以【XXXX】來呈現。

         ii. 數值類型的資料行（bigint、bit、decimal、int, money、numeric、smallint、smallmoney、tinyint、float、real）會以【0】來呈現。

        iii. 日期及時間類型的資料行會以【01.01.2000 00:00:00.0000000】呈現。

2. Email：以資料行內容的第一個字元加上【XXX@XXXX.com】來呈現。

3. Custom：依照自己的需求自訂呈現格式，格式為【前面所要顯示的字元數,遮罩字串,後面所要顯示的字元數】。

範例

一、 下列 T-SQL 指令碼可為 Name 資料行建立 custom 遮罩，輸出時顯示最前面和最後面各一個字元，中間的字元以 O 取代。

ALTER TABLE [dbo].[Customers] ALTER COLUMN [Name] ADD MASKED WITH (FUNCTION = 'partial(1,"O",1)')

執行結果如下：

二、 下列 T-SQL 指令碼可為 ID 資料行建立 custom 遮罩，輸出時將中間 4 個字元以【xxxx】呈現，最前面和最後面的 3 個字元顯示原本的內容。

ALTER TABLE [dbo].[Customers] ALTER COLUMN [ID] ADD MASKED WITH (FUNCTION = 'partial(3,"xxxx",3)')

執行結果如下：

三、 下列 T-SQL 指令碼可為 Email 資料行建立 email 遮罩。

ALTER TABLE [dbo].[Customers] ALTER COLUMN [Email] ADD MASKED WITH (FUNCTION = 'email()')

執行結果如下：

四、 下列 T-SQL 指令碼可為 Tel 資料行建立 custom 遮罩，由於該資料行是 varchar 資料類型，因此會以【xxxx】來輸出。

ALTER TABLE [dbo].[Customers] ALTER COLUMN [Tel] ADD MASKED WITH (FUNCTION = 'default()')

執行結果如下：

五、 下列 T-SQL 指令碼可為 Address 資料行建立 custom 遮罩，輸出時顯示前面 5個字元的原始內容，其餘以【*****】取代。

ALTER TABLE [dbo].[Customers] ALTER COLUMN [Address] ADD MASKED WITH (FUNCTION = 'partial(5,"*****",0)')

執行結果如下：

六、 下列 T-SQL 指令碼可為 Salary 資料行建立 default 遮罩，由於該資料行是 money 資料類型，因此會以輸出 0。

ALTER TABLE [dbo].[Customers] ALTER COLUMN [Salary] ADD MASKED WITH (FUNCTION = 'default()')

執行結果如下：

七、 下列 T-SQL 指令碼可為 CrediteCard 資料行建立 Custom 遮罩，輸出時最後 4 個字元原始呈現，其餘前面的字元以【xxxx-xxxx-xxxx-】取代。

ALTER TABLE [dbo].[Customers] ALTER COLUMN [CreditCard] ADD MASKED WITH (FUNCTION = 'partial(0,"xxxx-xxxx-xxxx-",4)')

執行結果如下：

修改資料遮罩

若要修改資料行的資料遮罩類型，只需在以建立動態資料遮罩的敘述來建立新的遮罩規則即可，例如當您針對 CreditCard 資料行重複建立遮罩，則會以最後一次建立的遮罩為主，前面所設定的遮罩規則會被覆蓋。

--修改前的遮罩

ALTER TABLE [dbo].[Customers] ALTER COLUMN [CreditCard] ADD MASKED WITH (FUNCTION = 'partial(0,"xxxx-xxxx-xxxx-",4)')

--修改後的遮罩

ALTER TABLE [dbo].[Customers] ALTER COLUMN [CreditCard] ADD MASKED WITH (FUNCTION = 'partial(4,"-xxxx-xxxx-",4)')

執行結果如下：

如何不受動態資料遮罩所遮蔽

若您希望某些使用者在查詢啟用動態資料遮罩的資料行時能不受遮罩所影響，可以

賦予 UNMASK 的權限，即可讓使用者看到遮罩前的原始資料內容。

下列 T-SQL 指令碼示範建立資料庫使用者 U2 並賦予 UNMASK 權限，就可以讓該使用者查詢 Customers 資料表時看到原始資料內容，而不受動態資料遮罩所遮蔽。

CREATE USER U2 WITHOUT LOGIN

GO

GRANT SELECT ON [dbo].[Customers] TO U2

GO

GRANT UNMASK TO U2

GO

EXECUTE AS USER = 'U2';

SELECT * FROM [dbo].[Customers];

REVERT

GO

移除資料遮罩

移除動態資料遮罩所遮蔽的資料行，只需以 DROP MASKED 敘述即可移除遮罩：

ALTER TABLE [資料表名稱]

ALTER COLUMN [資料行名稱] DROP MASKED

限制

目前 SQL Server 2016 CTP2 的動態資料遮罩功能有下列限制，

1. 無法針對資料類型轉換的查詢進行遮罩。

例如下列 T-SQL 指令碼，第二個查詢語句因為將資料行轉型，可能造成機敏資料外洩。

EXECUTE AS USER = 'U'

SELECT * FROM [dbo].[Customers]

SELECT CAST(NAME AS NCHAR(10)),CAST(Salary AS varchar(max)) FROM [dbo].[Customers]

REVERT

GO

執行結果如下：

您可以透過權限設定（例如避免使用者直接存取基礎資料表的方式），來減少因為轉型所造成動態資料遮罩失效的情況發生。

2. 只支援可變長度的字串類型（例如 varchar、nvarchar）但不支援最大長度設定為 max。

延伸閱讀

  1. Dynamic Data Masking
  2. 保護機敏資料的幫手 - SQL Database 動態資料遮罩
  3. 保護機敏資料的幫手 - 再談 SQL Database 動態資料遮罩

多重 tempdb 資料檔

說明

一般情況下要降低 tempdb 配置爭用（allocation contention）的狀況，可以藉由調整 tempdb 資料檔的數量，來改良 tempdb 的並行（concurrency）性，建議原則如下（詳見KB 2154845）：

1. 邏輯處理器數目小於或等於 8，建議 tempdb 的資料檔數目與邏輯處理器相同。

2. 邏輯處理器數目大於 8，建議 tempdb 的資料檔案數目設定為8個。

3. 若爭用持續發生，則可增加資料檔案數目（邏輯處理器數目的4的倍數）直到爭用降低至可接受為止。

若未自行手動調整，預設情況下 tempdb 和其他資料庫的預設值相同，資料檔數目都只有一個，SQL Server 2016 直接將 KB 2154845 建議的最佳實務整合在安裝程式之中，不論是使用 GUI 或是命令列模式安裝 SQL Server 2016 都可以方便的在安裝過程中就設定最佳的tempdb資料檔數目。

使用 SQL Server 2016 安裝程式

當您使用 SQL Server 2016 安裝程式安裝 SQL Server 時，可以在【資料庫引擎組態】步驟切換到資料目錄頁籤，您會發現和以往 SQL Server 版本不同的地方，在各項資料和記錄檔路徑的下方，多出可用來設定【暫存資料庫檔案數目】的選項。

【暫存資料庫檔案數目】選項的預設值為 8，但安裝程式會自行判斷目前伺服器的 CPU 核心數目，若 CPU 核心數目小於 8，則預設的【暫存資料庫檔案數目】會與 CPU 核心數目相同，例如上圖所使用的伺服器 CPU 為雙核心，因此【暫存資料庫檔案數目】預設為 2。

您不能設定超過伺服器核心數目的暫存資料庫檔案，否則在按下一步時會發生如下圖的錯誤，提醒您 tempdb 的資料檔數目不能大於電腦的核心數目。

使用命令列模式

若您使用命令列的方式安裝 SQL Server 2016，可以利用新的參數（/SQLTEMPDBFILECOUNT）來指定 tempdb 資料檔的數目，該參數只接受整數（integer）值，當您設定的值不被接受或小於等於 0，則在安裝過程當中會被以和安裝程式相同的預設值所取代（亦即等於電腦的核心數目或 8），若您所設定的值大於 CPU 的核心數目則會造成安裝失敗。

查看 tempdb 的檔案

不論是使用安裝程式或命令列模式安裝 SQL Server 2016，當 tempdb 資料檔數目大於1時，主要資料檔依然會以 tempdb.mdf 來命名，次要資料檔則是為【tempdb_mssql_#.ndf】，其中#是一個唯一個編號，請勿自行修改這個由 SQL Server 2016 幫您產生的 tempdb 資料庫次要檔，否則在您移除執行個體時會造成該檔案未被正確刪除，另外也請勿使用這樣的資料檔命名規則在您的使用者資料庫。

下圖示範在 SQL Server Management Studio 中使用資料庫屬性視窗查看資料庫檔案。

另外，您也可以利用 T-SQL 查詢 sys.database_files 目錄檢視，一樣可以得 tempdb 的相關檔案結構。

備註

其他最佳化 tempdb 效能的指導方針，可參考【最佳化 tempdb 效能】一文。

參考資料

1. What's New in Database Engine

2. Install SQL Server 2016 from the Command Prompt

3. KB 2154845

4. 最佳化 tempdb 效能

Comments

  • Anonymous
    June 30, 2015
    SQL Server 2016 搶先看又來囉！

    回顧系列專文：　
    1. SQL Server 2016 新功能搶先看 - Stretch Database / Managed Backup
  • Anonymous
    July 06, 2015
    SQL Server 2016 搶先看， 這次要帶大家認識 Always Encrypted 新功能！

    回顧系列專文：　
    1. SQL Server 2016 新功能搶先看 - Stretch
  • Anonymous
    July 14, 2015
    SQL Server 2016 搶先看，這次要帶大家認識 Temporal Tables 新功能！
    回顧系列專文：　

    SQL Server 2016 新功能搶先看 - Stretch
  • Anonymous
    July 27, 2015
    The comment has been removed
  • Anonymous
    August 07, 2015
    The comment has been removed
  • Anonymous
    September 24, 2015
    The comment has been removed
  • Anonymous
    September 29, 2015
    The comment has been removed
  • Anonymous
    October 04, 2015
    SQL Server 2016 搶先看系列第十彈 ！

    本系列文已經介紹 SQL Server 2016 在安全性、混合雲應用、效能調校及開發等方面所推出的嶄新功能或改良， SQL Server