こんにちは。ECプラットフォーム部の廣瀬です。

先日公開したテックブログ「データベースの秘密情報取扱いルールに関する取り組みのご紹介」では、データベースに保存している秘密情報の取扱いルールについてご紹介しました。そこでは、秘密情報の取扱いフローの策定として、次の5つのフローの整備を行いました。

新しく追加されるデータの取扱い
既存データで秘密情報に該当する項目の洗い出し
秘密情報にアクセスできるアカウントの制限
権限のないアカウントからのアクセス制限
権限保持者の大幅な削減による運用負荷増への対処

本記事では、これらのフローで策定された内容をSQL Serverで実装する場合の、具体的な対応内容について紹介します。

1. 秘密情報カラムへのアクセスの制限

秘密情報カラムへのアクセスを制限するためには、以下の2つの要件をSQL Serverの機能で実現させる必要があります。

秘密情報カラムにアクセスできるアカウントの制限
秘密情報カラムのマスク化

SQL Severには動的なデータマスキングという機能があります。この機能を使用することで、権限を制限したアカウントが該当のデータにアクセスした場合は、自動的にデータをマスクした状態で返すことができます。

上：権限があるアカウントでアクセス / 下：権限がないアカウントでアクセス

権限が制限されたアカウントでは自動的にデータがマスクされ、秘密情報を保護でき、前述の要件を満たせます。しかし、この機能に関してはSQL Server 2016以降でしか使用できません。弊社ではSQL Server 2016以降の環境もありますが、それより前のバージョンも利用しています。そのため、全環境でこの機能を利用することはできません。

そこで、動的なデータマスキングの代替案として次のような対応を行いました。

ロールの活用
秘密情報カラムに対するSELECT権限のはく奪
1. 権限のないアカウントで秘密情報を参照できなくするために、カラム単位でSELECT権限をはく奪（DENY）
2. 秘密情報をSELECTしているVIEWの参照権限をはく奪（DENY）
動的なデータマスキングを使用しない秘密情報カラムのマスク化

1. ロールの活用

権限のはく奪については、各ログイン/ユーザーに対して個別に設定を行うのではなく、SQL Serverのユーザー定義ロールを活用し、秘密情報へのアクセスを制限するロールを作成しています。作成したロールに対して秘密情報カラムのSELECT権限をはく奪し、そのロールにログイン/ユーザーを参加させます。こうすることで、複数のユーザーに対する秘密情報へのアクセス制限を効率的に実施できます。

策定したルールの「3. 秘密情報にアクセスできるアカウントの制限」では、秘密情報が閲覧不可能なアカウントと閲覧可能なアカウントの2種類を発行していると述べました。これらの権限の設定にもロールを活用し、柔軟に設定を管理できるようにしています。

2. 秘密情報カラムに対するSELECT権限のはく奪

1. 権限のないアカウントで秘密情報を参照できなくするために、カラム単位でSELECT権限をはく奪（DENY）

SQL Serverのテーブルのアクセス権は、カラム単位で制御できます。なお、SQL Serverのアクセス権の設定の詳細については、権限の階層やMicrosoft SQL Server Permissions Postersをご参照ください。

カラム単位でSELECT権限をはく奪（DENY）する場合、次のクエリを実行します。

DENY SELECT ON OBJECT::テーブル名(列名) TO ユーザー名

権限のはく奪を行うと、アクセス権のないユーザーで該当カラムを取得するSELECTを実行した際、エラーが発生します。

2. 秘密情報をSELECTしているVIEWの参照権限をはく奪（DENY）

権限をはく奪したテーブルをVIEW経由で参照している場合の考慮も必要です。VIEWの中ではく奪したカラムをSELECTしている際には、VIEWに対してSELECT権限を持っていると、ベーステーブルで権限がはく奪されていてもSELECTができてしまいます。

そのため、VIEW経由でもアクセスを制限する場合には、「SELECTをはく奪したカラムを参照しているVIEW」に対しても権限をはく奪する必要があります。この設定を行うために、次の2つの情報を組み合わせます。

1. SELECTをDENYしたカラムのリスト作成

データベースのオブジェクトに設定している権限については、sys.database_permissionsから取得できます。この情報から、権限の制御を行うロールに設定されているDENYの情報を取得し、「どのテーブルのどのカラムに対してアクセスが制限されているか」のリストを作成します。

2. VIEWが参照しているテーブルのカラムのリスト作成

VIEWが参照しているテーブルとカラムはsql_dependencies/sys.sql_expression_dependencies/sys.dm_sql_referenced_entitiesのような、依存関係を管理しているシステムVIEWから確認できます。この情報から、「VIEWで参照しているテーブルとカラム」のリストを作成します。

これらの情報を組み合わせることで、「DENYしたカラムを参照しているVIEW」を把握できます。この情報を基にしてVIEWに対してもDENYを設定することで、VIEWに秘密情報を含むカラムが使用されている場合でもアクセスの制限が可能となります。

3. 動的なデータマスキングを使用しない秘密情報カラムのマスク化

ここまでの内容で「秘密情報へのアクセスの制限」を実現できました。秘密情報へのアクセスを制限できたのであれば、「データへのアクセス制限については、これで完了なのでは」と思われるかもしれません。しかし、ここまでの作業で完了としてしまうとSELECTをDENYしたテーブルの参照時に、

SELECT * FROM テーブル名

というようなクエリを実行した際にエラーとなってしまいます。ルールの策定時に「エンジニアの運用負荷をできるだけ上げずに、秘密情報の閲覧可能者をできるだけ限定することが重要だと考えています」と述べました。ここで、「*」による検索ができない状態で、秘密情報カラムを含むテーブルのデータ調査を行う場合に必要な作業について考えてみます。

SELECTがDENYされているカラムにアクセスした場合、エラーメッセージにアクセス拒否されたカラム名が出力されます。このとき、エンジニアは以下の2つの作業を実施します。

「*」ではなく、テーブルの全カラムのリストを使用してSELECTを実行
エラーメッセージに出力されたカラムをSELECTのリストから除外してクエリを実行

これでは、エンジニアの運用負荷が増加してしまいます。動的なデータマスキングが使用できる環境であれば、カラムのアクセス制御はDENYではなくMASKとなるため、「*」による検索が可能です。ただ、この機能を使用できない環境が存在しているため、今回はそれ以外の方法で実現する必要があります。

そこで今回は「各テーブルに対応したVIEWを作成し、テーブルに秘密情報カラムが存在する場合は、該当のカラムをマスクする」という方法を採用しました。サンプルの情報を使用して、基本的な実装方法を説明します。

CREATE TABLE [Membership]　(
    [MemberID] [int] IDENTITY(1,1) NOT NULL,
    [FirstName] [varchar](100) NULL,
    [LastName] [varchar](100) NULL,
    [Phone] [varchar](12) NULL,
    [Email] [varchar](100) NULL,
PRIMARY KEY CLUSTERED ([MemberID] ASC)
)

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')

DENY SELECT ON OBJECT::MemberShip(Email) TO TestUser

Membershipというテーブルを作成し、TestUserはEmailカラムへのSELECT権限をはく奪しています。そのため、TestUserで次のクエリを実行すると、エラーが発生します。

SELECT * FROM Membership

エラーメッセージを元にクエリを修正する必要があり、このままではエンジニアの運用負荷が増加します。そこで、Membershipテーブルに対応したVIEWの作成を行います。

CREATE VIEW V_Membership
AS
    SELECT
        [MemberID],
        [FirstName],
        [LastName],
        [Phone],
        'xxxx@xxxx.com' AS [Email]
    FROM
        [Membership]
GO
GRANT SELECT ON OBJECT::V_Membership TO TestUser

このVIEWでは、秘密情報カラムについては、マスクした状態の固定値が返されます。実際にVIEWを検索すると次のような情報が取得されます。

SELECT * FROM V_Membership

この方法では、ベースとなるテーブルの代わりにVIEWを検索する必要があります。ただこの方法であれば、「各テーブルに秘密情報カラムが存在しているか」を意識することなくクエリを書けます。このようなVIEWを秘密情報カラムの存在有無に関わらず、全テーブルに対して作成しています。そしてデータの確認はテーブルを直接SELECTするのではなく、VIEWを使用するというルールにしています。これにより、動的なデータマスキング機能に近い体験をエンジニアへ提供しています。

なお、この対応で作成したVIEWについては、後述のメンテナンスによって再作成される場合があります。そのため、VIEWが一時的にDROPされる可能性を考慮しなくてはなりません。もしアプリケーションがこのVIEWを参照していると一時的なエラー発生は避けられません。そのため、今回の対応で作成したVIEWは「エンジニアがデータを確認するためにのみ使用しアプリケーションでは使用しない」というルールで運用しています。

2. 秘密情報のメンテナンス

秘密情報に該当するデータは、サービスの成長に合わせて追加/削除される可能性があります。このような秘密情報の変化に対応するため、マスクされたVIEWのメンテナンスを自動で実施しています。秘密情報の設定状況が変化した場合、データ参照用のVIEWにも変化の内容を反映させる必要があります。単純な実装としては、定期的な全VIEWの再作成が考えられます。ただ、今回ご紹介する実装では変更が発生するVIEWを最小限に抑えるため、設定の変更が必要なVIEWのみ再作成を行っています。

設定が変化し、再作成が必要となるのは次のようなケースが考えられます。

テーブルの定義変更（カラム追加）
- 最新の状態をVIEWに反映
テーブルの作成/削除
- 参照用のVIEWの作成/削除
テーブル内の秘密情報カラム（カラムのDENY）の増減
- 新しくDENYが設定されたカラムをマスク化
- DENYが取り消し（REVOKE）されたカラムを実データ化

1. テーブルの定義変更（カラム追加）

SQL Serverでは、テーブルに変更が行われるとsys.objectsの「modify_date」が変更されるので、この値を使用して直近でテーブルに対して変更が行われたかを確認しています。

2. テーブルの作成/削除

VIEWのメンテナンスを自動化するためには、テーブルの新規作成/削除にも対応する必要があります。新規に作成されたテーブルがあれば対応するVIEWを作成し、テーブルが削除されたのであれば、該当するVIEWを削除します。

この判断については、以下の2種類の比較により実施できます。

テーブルは存在するがVIEWは存在しない
VIEWは存在するがテーブルは存在しない

このような比較についてはセット演算子を使用することで実現できます。テーブル/VIEWの一覧についてはsys.objectsから取得でるので、この情報とセット演算子を利用することで、テーブル/VIEWの存在の不一致を検出できます。次の例では、VIEWは存在するがテーブルは存在しないデータを取得しています。

3. テーブル内の秘密情報カラム（カラムのDENY）の増減

新しく追加されたカラムが秘密情報に該当する場合（DENY）と、今まで秘密情報としていたカラムが秘密情報ではなくなった場合（REVOKE）は、VIEWのマスクの状態に反映する必要があります。前述のとおり、データベースのオブジェクトに設定している権限については、sys.database_permissionsから、VIEWが参照しているテーブルとカラムはsql_dependencies/sys.sql_expression_dependencies/sys.dm_sql_referenced_entitiesから取得できます。

これらの情報から、以下2点のリストを作成し、両者の比較を行うことでDENY設定の変化を検知しています。

テーブルのカラムに対するDENY設定状況
VIEWのカラムのマスク化の設定状況

以上3つの処理で変更が検知されたテーブルにのみVIEWの再作成を行うことで、VIEWの差分更新を実現しています。

また、データベース上の秘密情報カラムを管理するための仕組みづくりも進めています。秘密情報の管理用テーブルに秘密情報と判断したカラムを登録することで、アクセス制限に使用しているロールに自動的にDENYの設定が行われ、それがVIEWの設定にも反映されるような実装となる予定です。

3. リンクサーバー経由のアクセスの考慮

一般的な環境であれば、ここまでの内容で秘密情報のアクセス制限が完了すると思われます。弊社では複数のSQL Serverを組み合わせて利用するためリンクサーバーを使用している環境があります。リンクサーバーを使用している場合は、リンクサーバー経由でのアクセス制限についても考慮する必要があり、実施した対応についてご紹介します。

1. 設定方法

次の画像は、リンクサーバーの接続を作成する際に設定するセキュリティ設定です。

リンクサーバーを設定する際は、この画像のような設定で接続を作成された方もいらっしゃると思います。「上記一覧で定義されていないログインの接続方法」として「このセキュリティコンテキストを使用する」に、リンクサーバーで接続する先のログインの情報を入力しています。

このような設定が行われていると、リンクサーバー経由で別のサーバーにアクセスした場合、「リモートログイン」に指定したログインの権限で接続されます。

そのため設定しているログインの権限によっては、「自分で接続した場合は秘密情報にアクセスできないが、リンクサーバー経由ならアクセスできる」という状態になり得ます。

リンクサーバー経由でアクセスした場合も秘密情報へのアクセスを適切に制限するために「ローカルサーバーのログインとリモートサーバーのログインのマッピング」機能を活用しました。リンクサーバー経由のアクセスを制限するログインについては、マッピングに次のような設定を行っています。

「ローカルログイン」には、エンジニアがSQL Serverにアクセスする際のログインを設定します。「リモートユーザー」には、接続先のSQL Serverには存在しないログインを指定します。これにより、マッピングに設定されたログインは存在しないログインにマッピングされるため、リンクサーバー経由のデータアクセスができません。この仕組みによって特定のログインに対するリンクサーバー経由のアクセス制限を実現しています。この方法は、既存のリンクサーバーに対してアクセスを制限する必要がある場合に、影響を抑えつつ制限をかけたいときに有効です。

2. 注意点

アプリケーションから発行するクエリもリンクサーバーを使用している場合は注意が必要です。「アプリケーションから発行するクエリでは秘密情報カラムのSELECTを許可したいが、個人が手動で秘密情報カラムをSELECTするのは制限したい」といった要求がある場合、以下の実装も考えられます。

アプリケーションからSQL Serverに接続するための専用ログインを作成し、秘密情報カラムへのアクセス制限は許可
開発者専用のログインを作成し、秘密情報カラムへのアクセス権限をはく奪（DENY）
リンクサーバーの「上記一覧で定義されていないログインの接続方法」として「ログインの現在のセキュリティコンテキストを使用する」にチェック

この場合、確かにやりたいことは実現できるのですが、「意図しない結果が返ってくる」リスクがあるため解説します。

リンクサーバーに接続するクエリを実行する際、「sp_columns_100_rowset」というシステムストアドプロシージャが事前に実行されることがあります。実行される条件としては、クエリの初回実行時など、コンパイルして実行プランを生成する必要がある場合です。このストアドプロシージャは「接続先ログインの権限で取得可能なカラムリスト」を取得します。そして「取得可能なカラムリスト」は実行プランに反映されキャッシュされます。キャッシュされた実行プランは異なるログインであっても再利用されるため、以下の挙動になる場合があります。

秘密情報カラムへのアクセスが制限されたログインで、リンクサーバーを使ったSELECTクエリを実行し、プランがキャッシュされる
アプリケーションから同一のクエリが実行された際、キャッシュ済みのプラン（秘密情報カラムがレコードセットから除外されるプラン）で実行される
本来は取得されるべき秘密情報カラムがアプリケーションで取得できない

このように意図しない結果が返ってくる可能性があります。そのため、「秘密情報カラムへのアクセス権限を持っているログイン」と「制限されているログイン」の両者が同一のリンクサーバーを使用できる状況は避けた方が安心です。

まとめ

先日公開したテックブログ「データベースの秘密情報取扱いルールに関する取り組みのご紹介」でご紹介した内容をSQL Serverで実装する場合の、具体的な対応内容について紹介しました。特に、バージョンの制約で動的なデータマスキングが使用できない環境下においても、開発者の利便性低下を最小限に抑えながら秘密情報カラムをマスク化する方法について説明しました。本記事の内容がSQL Serverのセキュリティ向上を目指す方の参考になれば幸いです。