SQL Server 2016 [新機能] 動的なデータマスキングでクエリの変更が必要な場面は?


 

みなさん、こんにちは。

Microsoft SQL Server/Microsoft Azure SQL Database サポートチーム です。

SQL Server 2016 から動的なデータマスキングの機能が加わりました。

動的なデータマスキングの特徴として、クエリの結果に対してマスクが適用されるため、アプリケーション側でのクエリの変更が不要な場合が多い点にあります。

 

動的なデータ マスキング

https://msdn.microsoft.com/ja-jp/library/mt130841.aspx

動的データマスクは、クエリの結果にマスク ルールが適用されるため、既存のアプリケーションで簡単に使用できます。 多くのアプリケーションは、既存のクエリを変更せずに、デリケートなデータをマスクすることができます。

 

 

一方で意図した結果にならないということでお問い合わせを頂くケースも出てきました。

ここではアプリケーション側でのクエリの変更が必要な場面の例をご紹介したいと思います。

いずれも仕様に沿った結果ではありますが、ご参考頂ければ幸いです。

 

変更が必要な場面

  • カーソルで取得した値がマスクされる前の値であることを前提としている実装
    • カーソルで取得した値は既にマスクされているため、マスクされた後の値であることを前提とした実装に変更する必要があります。
  • 取得した値をクエリ内で置き換えた場合
    • 置き換えた値もマスク対象となるため、マスクされた値であることを前提とした実装が必要です。

 

以下は、上記の具体例です。


前準備

 

公開情報で紹介しているテーブルとデータを使用しています。

   動的なデータマスキング

   https://msdn.microsoft.com/ja-jp/library/mt130841.aspx

 

  1. テーブルを作成します。
CREATE TABLE Membership  

  (MemberID int IDENTITY PRIMARY KEY,  

   FirstName varchar(100) MASKED WITH (FUNCTION = 'partial(1,"XXXXXXX",0)') NULL,  

   LastName varchar(100) NOT NULL,  

   Phone# varchar(12) MASKED WITH (FUNCTION = 'default()') NULL,  

   Email varchar(100) MASKED WITH (FUNCTION = 'email()') NULL);  

 

  1. データを挿入します。
INSERT Membership (FirstName, LastName, Phone#, Email) VALUES   

   ('Roberto', 'Tamburello', '555.123.4567', 'RTamburello@contoso.com'),  

   ('Janice', 'Galvin', '555.123.4568', 'JGalvin@contoso.com.co'),  

   ('Zheng', 'Mu', '555.123.4569', 'ZMu@contoso.net');

 

  1. 作成者の権限で SELECT すると通常の結果を得られます。
SELECT * FROM Membership;  

出力結果

MemberID    FirstName                      LastName                       Phone#       Email 

----------- ------------------------------ ------------------------------ ------------ ------------------------------ 

1           Roberto                        Tamburello                     555.123.4567 RTamburello@contoso.com 

2           Janice                         Galvin                         555.123.4568 JGalvin@contoso.com.co 

3           Zheng                          Mu                             555.123.4569 ZMu@contoso.net 

(3 行処理されました)

 

  1. SELECT の権限だけを与えたユーザーを作ります。
CREATE USER TestUser WITHOUT LOGIN;  

GRANT SELECT ON Membership TO TestUser;

 

 

  1. 動的データマスキングが有効であることを確認します。
EXECUTE AS USER = 'TestUser';  

SELECT * FROM Membership;  

REVERT; 

出力結果

MemberID    FirstName                      LastName                       Phone#       Email 

----------- ------------------------------ ------------------------------ ------------ ------------------------------ 

1           RXXXXXXX                       Tamburello                     xxxx         RXXX@XXXX.com 

2           JXXXXXXX                       Galvin                         xxxx         JXXX@XXXX.com 

3           ZXXXXXXX                       Mu                             xxxx         ZXXX@XXXX.com 

(3 行処理されました)

 

 

  • 例1. カーソルを使った場合

出力結果で、2行目のデータが match となることを期待してしまうかもしれませんが、マスク後の値で比較されるので unmatch になります。

 

EXECUTE AS USER = 'TestUser';  

declare @Email nvarchar(200) 

declare Cur cursor for 

  SELECT TOP (1000) Email 

  FROM Membership 

open Cur 

fetch next from Cur 

into @Email 

while @@FETCH_STATUS = 0 

begin           

        if @Email = 'JGalvin@contoso.com.co' 

        begin 

                print @Email + N' mutch' 

        end 

        else 

                print @Email + N' unmatch' 

        fetch next from Cur 

        into @Email 

end 

close Cur 

deallocate Cur 

REVERT; 

出力結果

RXXX@XXXX.com unmatch 

JXXX@XXXX.com unmatch 

ZXXX@XXXX.com unmatch

 

  • 例2. 取得した値をクエリ内で置き換えた場合

 

比較はマスク前の値で行われていますが、置き換えられた値はマスクされます。

EXECUTE AS USER = 'TestUser';  

SELECT FirstName 

      ,Email 

          ,matching = 

                CASE [Email] 

                WHEN 'JGalvin@contoso.com.co' 

                  THEN N'match' 

                  ELSE N'unmatch', 

        END 

  FROM Membership; 

REVERT; 

出力結果

FirstName                      Email                          matching 

------------------------------ ------------------------------ -------- 

RXXXXXXX                       RXXX@XXXX.com                  uXXX@XX 

JXXXXXXX                       JXXX@XXXX.com                  mXXX@XX 

ZXXXXXXX                       ZXXX@XXXX.com                  uXXX@XX 

(3 行処理されました)

 

IIFでも同様です。

EXECUTE AS USER = 'TestUser';  

SELECT FirstName 

      ,Email 

      ,matching = 

            IIF ( [Email] = 'JGalvin@contoso.com.co' 

                  , N'match' 

                  , N'unmatch') 

       FROM Membership; 

REVERT; 

出力結果

FirstName                      Email                          matching 

------------------------------ ------------------------------ -------- 

RXXXXXXX                       RXXX@XXXX.com                  uXXX@XX 

JXXXXXXX                       JXXX@XXXX.com                  mXXX@XX 

ZXXXXXXX                       ZXXX@XXXX.com                  uXXX@XX 

(3 行処理されました)

 

 

  • 最後に

 

動的データマスキングは上手に使っていただくことで開発やメンテナンスのコストを大きく削減できる機能になります。

しかしながら、公開情報にもありますように単体でセキュリティを確保するような機能ではないため、適切な機能や実装と連携し、実際のご利用にあたっては、十分な検証の上、ご利用ください。

 

動的データ マスクの目的は、アクセスすべきではないユーザーがデータを閲覧することを防ぎ、デリケートなデータの公開を制限することにあります。

動的データ マスクは、ユーザーが直接データベースに接続し、徹底的なクエリを実行して、デリケートなデータの漏えいを防ぐことを目的としてはいません。

動的データ マスクは、その他の SQL Server セキュリティ機能 (監査、暗号化、行レベルのセキュリティなど) を補完します。

データベース内のデリケートなデータの保護をより強化するために、セキュリティ機能と連携して動的データ マスクを使用することをお勧めします。

 

※ 本情報の内容 (添付文書、リンク先などを含む) は、作成日時点のものであり、予告なく変更される場合があります。あらかじめご了承ください。

Comments (0)

Skip to main content