Go to Qiita Advent Calendar 2024 Top
LoginSignup
6
3

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 1 year has passed since last update.

@masahiro-yamaguchi(Masahiro Yamaguchi)

SQL Serverの動的データマスキングを使ってみた

Posted at

#動的データマスキングとは
データベースの中身には機密情報がたくさん格納されると思いますが、特権のないユーザに対して、機密情報をそのまま公開するとセキュリティ事故になりかねません。
なので、機密情報を公開できるユーザは特権をもつようなユーザのみに制限し、一般のユーザ/権限のないユーザにはデータをマスクして表示させたいという要求事項があったりします。

このデータのマスクですが、SQL Serverでは設定すれば、元のデータに変更を加えることなく、表示する際に自動で行ってくれる機能があります。それが「動的データマスキング機能」となります。

今回はこれについて調べてみて、検証も行いましたので、結果を残しておこうと思います。

※ちなみに、私が検証した環境はAzure Synapse Analytics SQLプールですが、基本動作はSQL Server（2016以降）もSQL Databaseも同じであると思います。

#設定方法
設定方法は、Azure Portal、Power Shellなどのツールを使って行う事が出来ますが、今回はT-SQLでの設定方法を記載します。

CREATE TABLE文のカラムを指定する箇所に記載したり、ALTER文で設定を行います。

CREATE TABLE文の時の設定例

CREATE TABLE Membership(
    MemberID        int IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED,
    FirstName       varchar(100) MASKED WITH (FUNCTION = 'partial(1, "xxxxx", 1)') NULL,
    LastName        varchar(100) NOT NULL,
    Phone           varchar(12) MASKED WITH (FUNCTION = 'default()') NULL,
    Email           varchar(100) MASKED WITH (FUNCTION = 'email()') NOT NULL,
    DiscountCode    smallint MASKED WITH (FUNCTION = 'random(1, 100)') NULL
);

ALTER文での設定例

ALTER TABLE Membership  
ALTER COLUMN LastName ADD MASKED WITH (FUNCTION = 'partial(2,"xxxx",0)');

#マスク方法の定義
動的データマスキングでは以下の4つの定義方法が準備されていますので、これを使って設定していくことになります。

##Default
###説明
動的データマスキングを指定するカラムの型によってマスクの方法が異なります。

文字型(char、nchar、varchar、nvarchar、text、ntext)
　 文字数の指定で4文字以上の指定の場合（例：varchar(12)）はXXXXを返し、4文字未満の場合はサイズに合わせてXを返す。

数値型(bigint、bit、decimal、int、money、numeric、smallint、smallmoney、tinyint、float、real)
　 0の値を返す。

日付/時刻データ型（date、datetime2、datetime、datetimeoffset、smalldatetime、time）
　 01.01.1900 00:00:00.0000000 の値を返します。

バイナリデータ型 (binary、varbinary、image)
　 ASCII 値0 のシングルバイトを返す。

###CREATE TABLE内での使用例

FirstName varchar(12) MASKED WITH (FUNCTION = 'default()') NULL`

###ALTER文での使用例

ALTER COLUMN Gender ADD MASKED WITH (FUNCTION = 'default()')

##Email
###説明
メールアドレスの最初の文字と最後の.comを返す。（最後が.co.jpや、.netの場合でも、.comとなる。）

###CREATE TABLE内での使用例

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

###ALTER文での使用例

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

##ランダム
###説明
指定した範囲内でランダムに数値を生成し、値を返す。
###CREATE TABLE内での使用例

--1から100の値をランダムで生成
Account_Number bigint MASKED WITH (FUNCTION = 'random(1, 100)')

###ALTER文での使用例

--1から12の値をランダムで生成
ALTER COLUMN [Month] ADD MASKED WITH (FUNCTION = 'random(1, 12)')

##カスタム文字列
###説明
最初と最後の文字を公開し、間にカスタムした埋め込み文字列を返す。また、マスク対象の文字列が短すぎる場合には、最初、最後の文字も公開されない。

###CREATE TABLE内での使用例

--最初の一文字と最後の一文字を公開、間の文字はXXXでマスク
FirstName varchar(100) MASKED WITH (FUNCTION = 'partial(1,"XXX",1)') NULL

###ALTER文での使用例

--最初の文字は公開せず、最後の4文字を公開。間の文字はXXXXXXX-にてマスク。
ALTER COLUMN [Phone Number] ADD MASKED WITH (FUNCTION = 'partial(0,"XXXXXXX-",4)')

#動作確認
実際に様々なシチュエーションで動作確認しました。
##1.テーブルの作成とマスクの確認
###1-1.テーブルの作成とデータの投入
動的データマスキングを使用したテーブルの作成と、作成したテーブルへのデータの投入を行います。

--テーブルの作成
CREATE TABLE 顧客(
	 顧客ID         int             NOT NULL
	,苗字           nvarchar(100)   NOT NULL
	,名前           nvarchar(100)   MASKED WITH (FUNCTION = 'partial(1, "xx", 0)') NOT NULL
	,電話番号       nvarchar(20)    MASKED WITH (FUNCTION = 'default()') NULL
	,Email          nvarchar(100)   MASKED WITH (FUNCTION = 'email()') NOT NULL
	,DidcountCode   smallint        MASKED WITH (FUNCTION = 'random(1, 100)') NULL
);

--データの投入
INSERT INTO 顧客(顧客ID,苗字,名前,電話番号,Email,DidcountCode) VALUES
 (1,N'山田'	,N'一太郎'	,'111-1111-1111'	,'ichiro-yamada@aaaa.co.jp'			,10);
INSERT INTO 顧客(顧客ID,苗字,名前,電話番号,Email,DidcountCode) VALUES
 (2,N'佐藤'	,N'次郎'		,'222-2222-2222'	,'jiro.saito@bbbb.com'			   ,5 );
INSERT INTO 顧客(顧客ID,苗字,名前,電話番号,Email,DidcountCode) VALUES
 (3,N'田中'	,N'三四郎'	,'333-3333-3333'	,'s.tanaka@cccc.org'				,50);
INSERT INTO 顧客(顧客ID,苗字,名前,電話番号,Email,DidcountCode) VALUES
 (4,N'佐々木',N'吾郎'		,'444-4444-4444'	,'gro@dddd.net'					    ,40);
INSERT INTO 顧客(顧客ID,苗字,名前,電話番号,Email,DidcountCode) VALUES
 (5,N'山口'	,N'むつみ'	,'555-5555-5555'	,'mutsumi-yamaguchi@aaaa.com'		,10);
INSERT INTO 顧客(顧客ID,苗字,名前,電話番号,Email,DidcountCode) VALUES
 (6,N'Parker'	,N'Peter'	,'666-6666-6666'	,'PPeter@zzzz.com'				  ,10);
INSERT INTO 顧客(顧客ID,苗字,名前,電話番号,Email,DidcountCode) VALUES
 (7,N'Tamburello'	,N'Roberto'	,'777-7777-7777'	,'RTamburello@contoso.com'	 ,10);

--投入データの確認
select * from 顧客 order by 1;

image.png

以下のSQLで動的データマスキングの設定情報を確認可能です。

--動的データマスキングの確認
SELECT c.name, tbl.name as table_name, c.is_masked, c.masking_function  
FROM sys.masked_columns AS c  
JOIN sys.tables AS tbl   
    ON c.[object_id] = tbl.[object_id]  
WHERE is_masked = 1;

image.png

###1-2.ユーザの作成と権限の付与
データをマスクして公開するためのユーザを作成します。

--ユーザを作成
CREATE USER MaskingTestUser WITHOUT LOGIN;

--dboスキーマに対する参照権限の付与
GRANT SELECT ON SCHEMA::dbo TO MaskingTestUser;

###1-3.ユーザの切り替えとマスクデータの確認
作成したユーザを利用して、データがマスキングされているか確認します。

--ユーザの切り替え
EXECUTE AS USER = 'MaskingTestUser'
--現在のユーザの確認（MaskingTestUser）
SELECT CURRENT_USER;

--データの確認（マスク状態）
select * from 顧客 order by 1;

結果は以下の通りマスクされている事が確認出来ます。
image.png

また、元のユーザに戻る場合は以下のSQLで戻ります。

--元のユーザへ切り替え
REVERT;

##2.既存のテーブルの動的データマスキングの追加/変更
既に作成されているテーブルにも動的データマスキングの設定は後から追加可能です。また、定義の変更も出来ます。いずれもALTER文で実施します。

###2-1.ALTER文で定義を変更
以下のALTER文で定義情報を変更します。

--新規でのマスク設定
ALTER TABLE 顧客
ALTER COLUMN 苗字 ADD MASKED WITH (FUNCTION = 'default()');  

--既存のマスクの変更
ALTER TABLE 顧客
ALTER COLUMN 電話番号 ADD MASKED WITH (FUNCTION = 'partial(0,"xxxxxx-",4)');  

--動的データマスキングの確認
SELECT c.name, tbl.name as table_name, c.is_masked, c.masking_function  
FROM sys.masked_columns AS c  
JOIN sys.tables AS tbl   
    ON c.[object_id] = tbl.[object_id]  
WHERE is_masked = 1;

image.png
苗字列の動的データマスキングが新たに追加され、電話番号列が変更されました。

###2-2.ユーザの切り替えとマスクデータの確認
ユーザを切り替えてデータがちゃんとマスクされるか確認します。

--ユーザの切り替え
EXECUTE AS USER = 'MaskingTestUser'
--現在のユーザの確認
SELECT CURRENT_USER;

--データの確認（マスク状態）
select * from 顧客 order by 1;

--元のユーザに切替
REVERT;

image.png
新しい定義でマスクされている事が確認出来ます。

##3.動的データマスキングの解除
UNMASK権限を付与することで、対象のユーザから動的データマスキングの解除が可能です。
###3-1.対象のユーザに権限を付与
以下のSQLで対象のユーザにUNMASK権限を付与します。

--ユーザにdboスキーマに対するUNMASK権限の付与
GRANT UNMASK ON SCHEMA::dbo TO MaskingTestUser;

###3-2.ユーザの切り替えとデータの確認
ユーザを切替てデータの確認を行います。（データがマスキングされていない事を確認）

--ユーザの切り替え
EXECUTE AS USER = 'MaskingTestUser'
--現在のユーザの確認
SELECT CURRENT_USER;

--データの確認（マスク状態）
select * from 顧客 order by 1;

--元のユーザへ切り替え
REVERT;

image.png
UNMASK権限により、データがマスキングされていません。

###3-3.UNMASK権限の剥奪
以下のSQLで付与した権限を剥奪します。

REVOKE UNMASK ON SCHEMA::dbo TO MaskingTestUser;

##4.動的データマスキングの削除
動的データマスキングを対象の列から削除する事も可能です。
###4-1.動的データマスキングのDROP
以下のSQLで苗字列から動的データマスキングを削除します。

--苗字列から動的マスキング機能を削除
ALTER TABLE 顧客
ALTER COLUMN 苗字 DROP MASKED;

###4-2.ユーザの切り替えとデータの確認（苗字列がマスキングされていない）
以下のSQLでユーザを切り替えて、動的データマスキングが苗字列から削除され、データがマスクされていない事を確認します。

--ユーザの切り替え
EXECUTE AS USER = 'MaskingTestUser'
--現在のユーザの確認
SELECT CURRENT_USER;

--データの確認（マスク状態）
select * from 顧客 order by 1;

--元のユーザへ切り替え
REVERT;

image.png

##5.動的データマスキングされたデータをInsertする
UNMASK権限を持っていないユーザで動的データマスキングされているデータを別にテーブルにインサートします。
結論から先に記述しますと、UNMASK権限を持っていなユーザが動的マスキングのデータをSELECTとして別のテーブルなどにINSERTすると、マスクされたデータのままINSERTされます。

###5-1.ユーザに権限を付与
ユーザに権限を付与します。

--CREATE TABLE/ALTER 権限の付与
GRANT CREATE TABLE ON DATABASE::"test1synapse1" TO MaskingTestUser;
GRANT ALTER ON SCHEMA::"dbo" TO MaskingTestUser;

###5-2.ユーザの切り替えとデータの確認（マスキングされている）
以下のSQLでユーザを切り替えて、現在のマスキングされているデータを確認します。

--ユーザの切り替え
EXECUTE AS USER = 'MaskingTestUser'
--現在のユーザの確認
SELECT CURRENT_USER;

--データの確認
select * from 顧客 order by 1;

以下の通り動的マスキングされた状態でSELECTされます。
image.png

###5-3.CTASによるデータのコピーとデータの確認
CTASを使って、テーブルのコピーを作成します。

--CTASで「顧客」テーブルを「顧客_COPY」テーブルへコピー
CREATE TABLE 顧客_COPY
WITH (
	DISTRIBUTION = HASH(顧客ID),
	HEAP
)
AS SELECT * FROM 顧客;

###5-4.コピーしたテーブルの確認
UNMASK権限を持たないユーザで先ほど作成したテーブルのデータを確認。

--コピーしたデータの確認
select * from 顧客_COPY;

image.png

データは動的マスキングされた状態の物となっています。
念の為、以下のSQLでCATSで動的マスキングの定義までコピーされていないか確認します。

--動的データマスキングの確認
SELECT c.name, tbl.name as table_name, c.is_masked, c.masking_function  
FROM sys.masked_columns AS c  
JOIN sys.tables AS tbl   
    ON c.[object_id] = tbl.[object_id]  
WHERE is_masked = 1 and tbl.name = '顧客_COPY';  

--元のユーザへ切り替え
REVERT;

image.png

結果は何も出力されません。CTASを使っても動的マスキングの定義まではCOPY出来ないようです。

###5-5.コピーしたテーブルの確認（UNMASK権限を持ったユーザ）
UNMASK権限を持っているユーザでコピーしたデータを確認してみます。

--データの確認
select * from 顧客_COPY order by 1;
select * from 顧客 order by 1;

image.png

上記の通り、UNMASK権限を持たないユーザが、動的データマスキングのテーブルをSELECTして、INSERTやCTASでデータをコピーしてもデータはマスクされた状態のまま、コピーされてしまいます。

##6.動的マスキングされたテーブルのINSERT、UPDATE
動的データマスキングが定義されているテーブルに対して、UNMASK権限を持たないユーザがデータをINSERTやUPDATEするとどうなるのか確認します。
こちらも結論から先に記載しますと、権限さえあれば、INSERTもUPDATEも問題なく実施できます。（ただしSELECTしてもデータはマスクされた状態で表示されます。）

###6-1.権限の付与
ユーザに対してINSERT、UPDATE、DELETEが可能なように権限を付与します。

--権限の付与
GRANT INSERT,UPDATE,DELETE ON SCHEMA::"dbo" TO MaskingTestUser;

###6-2.ユーザを切り替えてデータを更新
UNMASK権限を持たないユーザに切り替えて、動的データマスキングの設定されているテーブルへデータの挿入、更新、削除を実施します。

--ユーザの切り替え
EXECUTE AS USER = 'MaskingTestUser'
--現在のユーザの確認
SELECT CURRENT_USER;

--データの挿入
INSERT INTO 顧客(顧客ID,苗字,名前,電話番号,Email,DidcountCode) VALUES
 (8,N'山本'	,N'太郎'	,'888-8888-8888'	,'taroyamada@aaaaaa.com'	,20);

--データの更新
UPDATE 顧客 SET Email = 'Update@contoso.com' where 顧客ID = 7;

--データの削除
DELETE 顧客 WHERE 顧客ID=1;

image.png

特に問題なく行えます。

###6-3.データの確認
UNMASK権限を持たないユーザでも権限さえあれば、動的データマスキングが設定されているテーブルへの挿入、更新、削除が出来ました。
データの内容を確認します。

--データの確認
select * from 顧客 where 顧客ID in (1,7,8) order by 1;

--元のユーザへ切り替え
REVERT;

image.png

顧客ID=8のデータが、挿入され、顧客ID=7のデータが更新され、顧客ID=1のデータはDELETEされている事を確認しました。（ただし、マスクされているのでよくわかりません。）

###6-4.データの確認（UNMASK権限を持ったユーザ）
UNMASK権限を持ったユーザで再度データの確認をします。

--データの確認
select * from 顧客 where 顧客ID in (1,7,8) order by 1;

image.png

UNMASK権限を持っていないユーザでも、権限さえあれば、データの挿入、更新、削除は出来るようです。

#最後に
動的データマスキング機能について様々なシチュエーションの検証を今回は実施しました。アプリケーションから透過的に重要なデータをマスクする事が出来そうで使い勝手がいいと思います。

ただし、権限があるとデータの削除なども行えてしまえるので、権限の設計/設定も合わせて意識した方が良さそうです。

6
3
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
6
3

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?