SQLServer の監査機能
SQLServer Audit
SQLServer の監査機能について
SQLServer における監査機能はかなり複雑なものに感じます。
なぜなら、世の中に情報が少なく、公式の MS ドキュメントが分かりにくいためです。
分かってしまえば、「あ～、そういうものか」と納得できるのですが、そこまでが大変でした。
そこで頭の中を整理する意味も含めて、記事にしたいと思います。
参考としたサイト
私が最初に読んだのは、以下の MS ドキュメントです。
一度、読んで頂ければわかると思いますが、全然わからないと思います。
私の場合、自分で環境を作って、SSMS（SQLServer Management Stadio）で色々試していくことで、SQLServer の監査機能の仕組みが理解できるようになりました。
SQLServer Audit の仕組み
アクショングループ
SQLServer の監査機能を理解するためには、アクショングループの概念を理解する必要があります。
アクショングループは、主にサーバーレベル、データベースレベルの2段階での設定が可能です。具体的には以下のようなものです。
全部で44個のアクショングループが存在しますが、名前から機能が推測しやすいものに絞って掲載しています。
サーバーレベルとデータベースレベル
上記の表を理解するためには、SQLServer におけるサーバーレベルとデータベースレベルで別々にユーザーが存在していることを理解しておく必要があります。
私は何も知らない状態で、いきなり監査機能を調べようとしていたので苦労しました。
SQLServer のユーザーやロールの仕様については、「RDS SQLServer のユーザーとロールの関係」で解説しておりますので、よかったら参照してみてください。
要点は以下の2点です。
- サーバーレベルでのみ実行可能なアクション（Logins に対する Create,Alter,Drop 等）が存在するということ
- データベースレベルで実行可能なアクション（Table に対する Create/Alter/Drop 等）は、サーバーレベルとデータベースレベルどちらでも設定可能であるということ
1つ目は感覚的に理解いただけると思います。2つ目のサーバーレベルとデータベースレベルどちらでも設定可能というのはこういうことです。
例えば、"SCHEMA_OBJECT_ACCESS_GROUP" を
- サーバーレベルで設定した場合、全てのデータベースに対して全スキーマへのアクセスを監査
- データベースレベルで設定した場合、設定したデータベースの中にある全スキーマへのアクセスを監査
ということになります。ほんと言葉で説明するのは難しいですね。
SQLServer Audit の設定方法
Audits と Specification の関係
SQLServer の監査機能の難しいところは、Audits と Specification という存在です。図で表してみます。
イメージとしては、Audits で監査ログを格納するファイルを定義して、Specification で監査対象とするアクショングループを定義します。
また、Specification はサーバーレベルとデータベースレベルの2種類が設定することができます。ただし、Audits と紐づけることができるのは1つずつのみです。
SQLServer の監査設定は、この Audits と Specification を ON にすることで、対応するアクショングループについて監査ログ取得を開始するようになります。
試しにやってみる
SERVER AUDIT（Audits）
以下の T-SQL で Audits は作成できます。
前提として、サーバーレベルの T-SQL 操作は主に master データベースで実行されます。
上記の T-SQL では監査ログファイルを生成するにあたって、2MB を超えたら新しいログファイルにスイッチするように設定しています。ファイル数の上限は 2147483647 に設定しています。
また、明示的に WITH (STATE = ON) を指定しない場合、デフォルトでは (STATE = OFF) の状態で作成されます。
SERVER AUDIT SPECIFICATION（Specification）
サーバーレベルの監査設定は以下で行います。
監査設定内容はシンプルにしました。ここで監査設定を有効化にしても sampple_audits が有効化されなければファイルに出力されません。
DATABASE AUDIT SPECIFICATION（Specification）
データベースレベルの監査設定は以下で行います。
データベースレベルの監査設定を行う際のポイントは、対象のデータベースに移動してから実施することです。
構文そのものはサーバーレベルのものと同様です。
SERVER AUDIT（Audits）を有効化
最後に、Audits を有効化します。
master データベースに移動してからでないと実行できません。
これで、監査ログの取得が開始されます。
監査設定ができているか、結果を SSMS で確認
SERVER AUDIT（Audits）
SERVER AUDIT SPECIFICATION（Specification）
DATABASE AUDIT SPECIFICATION（Specification）
監査設定の注意点
アクショングループの選定には注意を
SQLServer の監査設定で難しいのは、対象のアクションと監査設定が1対1ではないところです。
例えばですが、Oracle や PostgreSQL では1つづつのアクションに対して監査設定が行えます。
CREATE TABLE を監査、ALTER TABLE を監査、SELECT TABLE を監査といった具合です。
しかしながら、SQLServer ではアクショングループという単位で設定を行うことになります。
たとえば、"DATABASE_OBJECT_CHANGE_GROUP" を設定することで、CREATE TABLE / ALTER TABLE / DROP TABLE に対して監査設定が行えます。
CREATE TABLE だけ、というのが出来ません。
なお、DML に関しては SELECT のみ、DELETE のみといった設定が細かく設定可能です。
フィルター機能は Audits でのみ可能
監査ログは処理量に応じて膨大な量になります。そこで、監査ログを絞るということを一般的には行います。
SQLServer の監査機能では、Audits でのみ監査ログの絞り込みが行えます。
まずは、サーバーレベル、データベースレベルで監査対象のアクションを指定して、そこからさらに絞りこむ場合には、Audits でフィルター条件を指定します。
例えば、監視したいのが、特定のユーザーのみであった場合、Audits のフィルターで対象の principal に限定するよう指定するといった具合です。
フィルター条件は柔軟に指定できますので、きめ細かな監査設定が行うことができます。
意外と便利
最後に
実際に SQLServer の監査機能を触った感想ですが、Oracle や PostgreSQL と同様に細かく監査設定が行えることが分かりました。
MS ドキュメントは分かりづらかったですが、分かってしまうと、意外にも使いやすいのではないかなと個人的に思っています。
ただ、現時点（2020年7月時点）で、RDS SQLServer は監査ログの転送先が S3 のみであるため、監査ログの中身を見てアラートを発行する場合に、作りこみや何らかのツールとの連携が必要になってしまいます。
CloudWatch Logs に転送できるようにしてくれると助かりますね。
今回は以上でございます。
Icons made by Flat Icons from www.flaticon.com
