Zenn
💻

SQL Server で監査を始める (Part.2: データベース監査)

2020/12/23に公開
SQL
SQL Server
audit
tech

この記事について

この記事は、Azure SQL & Synapse Analytics Advent Calendar 2020 22 日目の記事です。

SQL Server 2012 から、SQL Server の監査機能として新しく SQL Server Audit が登場しています。
今回は、SQL Server Audit のデータベースレベルの監査について解説していきます。

環境

今回の検証は、以下の環境で実施しています。OS などに関連する内容については、適宜読み替えてください。

  • OS: macOS Big Sur バージョン 11.1
  • Docker for Mac: v20.10.0
  • SQL Server: バージョン 15.0.4043.16

サンプル DB の準備

データベース監査を行うにあたって、サンプルの DB を用意します。
今回は、Microsoft 社が公式に提供している AdventureWorks2019 サンプルデータベースを作成します。

作成手順については、以下の記事を参考にしてください。

SQL Server Audit による監査について

SQL Server Audit での監査には、サーバーレベルのイベントを監視するサーバー監査と、データベースレベルのイベントを監視するデータベース監査の 2 種類が存在します。

監査のレベル 説明
サーバーレベル 管理の変更やログオンおよびログオフの操作などのサーバー操作に関するアクションが含まれる。
データベースレベル データ操作言語 (DML) とデータ定義言語 (DDL) の操作に関するアクションが含まれる。

データベースレベルの監査

データベースレベルの監査を始めるには、以下の 3 つを行う必要があります。

  • サーバー監査オブジェクトの作成
  • データベース監査仕様オブジェクトの作成
  • データベース監査の開始

サーバー監査オブジェクトの作成

サーバー監査オブジェクトの作成については、前回記事 を参照してください。
今回は、前回記事の内容からの続きとして、SampleServerAudit という名前のサーバー監査オブジェクトを作成しているものとします。

データベース監査仕様オブジェクトの作成

Transact-SQL で、作成済みのサーバー監査オブジェクトに紐づくデータベース監査仕様オブジェクトを作成します。
データベース監査仕様オブジェクトを新規作成するには、CREATE DATABASE AUDIT SPECIFICATION クエリを使用します。
クエリの構文については、CREATE DATABASE AUDIT SPECIFICATION を参照してください。
今回は、事前に作成済みの SampleServerAudit サーバー監査オブジェクトに紐づく、SampleDatabaseAuditSpec データベース監査オブジェクトを作成し、監査する監査アクションおよび監査アクショングループを設定していきます。
データベースレベルの監査アクショングループについては、データベース レベルの監査アクション グループ を参照してください。
データベースレベルの監査アクションについては、データベース レベルの監査アクション を参照してください。

USE AdventureWorks2019;
GO
CREATE DATABASE AUDIT SPECIFICATION SampleDatabaseAuditSpec
FOR SERVER AUDIT SampleServerAudit
    ADD (SELECT, UPDATE, INSERT, DELETE, EXECUTE ON Schema::Sales BY dbo),
    ADD (BATCH_STARTED_GROUP),
    ADD (DATABASE_PERMISSION_CHANGE_GROUP),
    ADD (SCHEMA_OBJECT_CHANGE_GROUP),
    ADD (SCHEMA_OBJECT_PERMISSION_CHANGE_GROUP)
WITH (STATE = ON)
GO

今回は、ADD の部分にて、以下の監査アクショングループを設定しました。

アクショングループ名 説明
SELECT SELECT が実行されるたびに発生します。
UPDATE UPDATE が実行されるたびに発生します。
INSERT INSERT が実行されるたびに発生します。
DELETE DELETE が実行されるたびに発生します。
EXECUTE EXECUTE が実行されるたびに発生します。
ON Schema::Sales ON class::securable の形式で、セキュリティ保護可能なリソースのクラス名、および監査アクションまたは監査アクショングループを適用するデータベース内のテーブル、ビュー、またはその他のセキュリティ保護可能なオブジェクトを指定できます。この例では、Sales スキーマ全体を選択していることになります。
BY dbo BY principal の形式で、監査アクション、または監査アクショングループを適用するデータベースプリンシパルの名前を指定できます。この例では、dbo データベースプリンシパルを指定していることになります。データベースプリンシパル全体を監査したい場合は、public を使用します。
BATCH_STARTED_GROUP バッチテキスト、ストアドプロシージャ、またはトランザクション管理操作の実行が開始されるたびに発生します。これは、実行前に発生し、クライアントから送信されたバッチまたはストアド プロシージャのテキスト全体を監査します。
DATABASE_PERMISSION_CHANGE_GROUP SQL Server の任意のユーザーによって、ステートメント権限に対して GRANT、REVOKE、または DENY が実行されるたびに、データベースに対する権限の許可などのデータベース限定のイベントに対して発生します。
SCHEMA_OBJECT_CHANGE_GROUP スキーマに対して CREATE、ALTER、または DROP の各操作が実行されたときに発生します。
SCHEMA_OBJECT_PERMISSION_CHANGE_GROUP スキーマオブジェクトに対して GRANT、DENY、または REVOKE が実行されるたびに発生します。

また、作成したデータベース監査仕様オブジェクトの内容は、sys.database_audit_specifications ビュー、および sys.database_audit_specification_details ビューで確認することができます。
ビューの詳細については、それぞれ database_audit_specifications (Transact-sql) と database_audit_specification_details (Transact-sql) で確認してください。

また、作成したデータベース監査仕様オブジェクトは、WITH (STATE = ON) で有効化しない限り、監査ログ取得の対象になりません。監査ログ取得時には必ず有効化されていることを確認してください。

USE AdventureWorks2019;
GO
SELECT * FROM sys.database_audit_specifications;
GO
SELECT * FROM sys.database_audit_specification_details;
GO

sys.database_audit_specifications ビューでデータベース監査仕様オブジェクトが作成されているかどうか、およびデータベース監査仕様オブジェクトが有効化されているかどうかを確認できます。
sys.database_audit_specification_details ビューにて、作成されているデータベース監査仕様オブジェクトに含まれている監査アクションおよび監査アクショングループを確認できます。

データベース監査の開始

サーバー監査を開始することで、データベース監査も同時に開始されます。

既に作成済みのサーバー監査オブジェクトを有効化/無効化するには、ALTER SERVER AUDIT クエリを使用します。
クエリの構文については、ALTER SERVER AUDIT (Transact-SQL) を参照してください。

USE master;
GO
ALTER SERVER AUDIT SampleServerAudit
    WITH (STATE = ON);
GO

サーバー監査の開始の詳細については、前回記事 を参照してください。

まとめ

今回は、SQL Server 監査 (SQL Server Audit) におけるデータベース監査について解説しました。
是非、データベース監査のアクション、および監査アクショングループの一覧を確認して、どのような情報がデータベースレベルの監査ログとして取得できるのか、確認してみてください。
(可能であれば、実際に試してみてください。)

参考情報

Discussion