💻

SQL Server で監査を始める (Part.1: サーバー監査)

2020/12/20に公開

SQL

SQL Server

audit

tech

この記事について

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

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

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

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

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

サーバーレベルの監査

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

サーバー監査オブジェクトの作成
サーバー監査仕様オブジェクトの作成
サーバー監査の開始

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

Transact-SQL で、新しいサーバー監査オブジェクトを作成します。
サーバー監査オブジェクトを新規作成するには、CREATE SERVER AUDIT クエリを使用します。
クエリの構文については、CREATE SERVER AUDIT (Transact-SQL) を参照してください。
今回は、SampleServerAudit という名前のサーバー監査オブジェクトを作成し、/tmp/mssql ディレクトリ以下にファイルを作成します。ファイルサイズの上限は 128 MB とし、ファイルのローテーションが無制限で行われるようにします。

USE master;
GO
CREATE SERVER AUDIT SampleServerAudit
    TO FILE (FILEPATH = '/tmp/mssql', MAXSIZE = 128 MB, MAX_ROLLOVER_FILES = UNLIMITED);
GO

作成されたサーバー監査オブジェクトの内容は、sys.server_audits ビューで確認することができます。
ビューの詳細については、server_audits (Transact-sql) で確認してください。

USE master;
GO
SELECT * FROM sys.server_audits;
GO

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

Transact-SQL で、先ほど作成したサーバー監査オブジェクトに紐づくサーバー監査仕様オブジェクトを作成し、有効化します。
サーバー監査仕様オブジェクトを新規作成するには、CREATE SERVER AUDIT SPECIFICATION クエリを使用します。
クエリの構文については、CREATE SERVER AUDIT SPECIFICATION (Transact-SQL) を参照してください。
今回は、先ほど作成した SampleServerAudit サーバー監査オブジェクトに紐づく、 SampleServerAuditSpec サーバー監査仕様オブジェクトを作成し、監査するアクショングループを設定していきます。
サーバーレベルの監査アクショングループについては、サーバーレベルの監査アクショングループを参照してください。

USE master;
GO
CREATE SERVER AUDIT SPECIFICATION SampleServerAuditSpec  
FOR SERVER AUDIT SampleServerAudit
    ADD (DATABASE_CHANGE_GROUP),
    ADD (DATABASE_OBJECT_CHANGE_GROUP),
    ADD (DATABASE_OBJECT_PERMISSION_CHANGE_GROUP),
    ADD (FAILED_LOGIN_GROUP)
WITH (STATE = ON)
GO

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

アクショングループ名	説明
DATABASE_CHANGE_GROUP	データベースが作成、変更、または削除されるときに発生します。このイベントは、任意のデータベースが作成、変更、または削除されるたびに発生します。
DATABASE_OBJECT_CHANGE_GROUP	スキーマなどのデータベースオブジェクトで、CREATE、ALTER、または DROP ステートメントが実行されたときに発生します。このイベントは、任意のデータベースオブジェクトが作成、変更、または削除されるたびに発生します。注:非常に大量の監査レコードが生成される可能性があります。
DATABASE_OBJECT_PERMISSION_CHANGE_GROUP	アセンブリやスキーマなどのデータベースオブジェクトに対して GRANT、REVOKE、または DENY が実行された場合に発生します。このイベントは、サーバー上の任意のデータベースの任意のオブジェクト権限の変更に対して発生します。
FAILED_LOGIN_GROUP	プリンシパルが SQL Server へのログインを試みて失敗したことを示します。このクラスのイベントは、新しい接続によって生じることも、接続プールから再利用された接続によって生じることもあります。この監査は、Azure SQL Database には適用されません。

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

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

USE master;
GO
SELECT * FROM sys.server_audit_specifications;
GO
SELECT * FROM sys.server_audit_specification_details;
GO

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

サーバー監査の開始

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

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

クエリを実行し、サーバー監査オブジェクトの状態が STATE = ON となることで、サーバー監査オブジェクトが有効になり、サーバー監査が開始されます。
CREATE SERVER AUDIT クエリで指定した、/tmp/mssql ディレクトリ以下に、拡張子が .sqlaudit であるサーバー監査ログのファイルが新規作成されているのを確認してください。

ls -al /tmp/mssql
total 24
drwxr-xr-x 3 mssql root    96 Dec 20 13:34 .
drwxrwxrwt 1 root  root  4096 Dec 20 12:51 ..
-rw-r----- 1 mssql root 20480 Dec 20 13:30 SampleServerAudit_258588FD-362B-4F72-AF4E-13C8480F6181_0_132529446186800000.sqlaudit

サーバー監査ファイルに書き込まれたデータの確認

SQL Server のサーバー監査で作成された監査ファイル (.sqlaudit) の情報は、sys.fn_get_audit_file システム関数で確認することができます。
構文については、sys.fn_get_audit_file を参照してください。

先ほどの例で作成された SampleServerAudit_258588FD-362B-4F72-AF4E-13C8480F6181_0_132529446186800000.sqlaudit ファイルの情報を確認する場合、クエリは以下のようになります。

USE master;
GO
SELECT * FROM sys.fn_get_audit_file ('/tmp/mssql/SampleServerAudit_258588FD-362B-4F72-AF4E-13C8480F6181_0_132529446186800000.sqlaudit', default, default);
GO

このように、ファイル内に出力されたサーバー監査の情報を確認することが可能です。
例えば、この状態で、データベースのログイン失敗を試してみてください。ログイン失敗の情報が、監査ログに書き込まれていることを確認できるはずです。

まとめ

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

次回は、もう 1 つのデータベース監査について解説しようと思います。

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