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
- テーブルを作成します。
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);
- データを挿入します。
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');
- 作成者の権限で 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 行処理されました)
- SELECT の権限だけを与えたユーザーを作ります。
CREATE USER TestUser WITHOUT LOGIN;
GRANT SELECT ON Membership TO TestUser;
- 動的データマスキングが有効であることを確認します。
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 セキュリティ機能 (監査、暗号化、行レベルのセキュリティなど) を補完します。
データベース内のデリケートなデータの保護をより強化するために、セキュリティ機能と連携して動的データ マスクを使用することをお勧めします。
※ 本情報の内容 (添付文書、リンク先などを含む) は、作成日時点のものであり、予告なく変更される場合があります。あらかじめご了承ください。