【5分で流し読み】SQL Server Auditでできることを理解する

こんにちは。今週末は腹をくくってにしたSQL Serverの理解を深めることにしたいと思います。

第一弾はSQL Serverの監査機能の1つであるSQL Server Audit。

それではまいります。

SQL Server Auditとは

SQL Server Audit (データベース エンジン) – SQL Server | Microsoft Docs

  • SQL Server 2012で登場
  • SELECTなど、特定のアクショングループと呼ばれる
  • 監査ログはイベントログまたは監査ファイルへ書き込み可能
  • サーバーレベルの監査とデータベースレベルの監査がある
  • T-SQLまたはSSMSから構成できる

SQL Serverの監査関連機能の全体像

こちらに別途まとめています。

サーバーレベルとデータベースレベルの監査の違い

SQL Server 監査のアクション グループとアクション – SQL Server | Microsoft Docs

  • 監査できるアクショングループが異なる。違いは以下(上記Docsをもとに作成)
  • サーバーレベルで取得できてデータベースレベルで取得できないアクショングループはあるが、その逆はない模様。
  • ただし、データベース監査では、アクショングループの粒度以外にも、SELECTやUPDATEといった個別のアクション単位でも監査可能。加えて、監査対象も、データベース単位や、オブジェクト単位など細かく指定可能。

監査可能なアクショングループの差異

*各アクショングループの説明は上記公式Docsを参照ください。

アクショングループサーバーレベルデータベースレベル
APPLICATION_ROLE_CHANGE_PASSWORD_GROUP
AUDIT_CHANGE_GROUP
BACKUP_RESTORE_GROUP
BATCH_COMPLETED_GROUP
BATCH_STARTED_GROUP
DATABASE_CHANGE_GROUP
DATABASE_LOGOUT_GROUP
DATABASE_MIRRORING_LOGIN_GROUP
DATABASE_OBJECT_ACCESS_GROUP
DATABASE_OBJECT_CHANGE_GROUP
DATABASE_OBJECT_OWNERSHIP_CHANGE_GROUP
DATABASE_OBJECT_PERMISSION_CHANGE_GROUP
DATABASE_OPERATION_GROUP
DATABASE_OWNERSHIP_CHANGE_GROUP
DATABASE_PERMISSION_CHANGE_GROUP
DATABASE_PRINCIPAL_CHANGE_GROUP
DATABASE_PRINCIPAL_IMPERSONATION_GROUP
DATABASE_ROLE_MEMBER_CHANGE_GROUP
DBCC_GROUP
FAILED_DATABASE_AUTHENTICATION_GROUP
FAILED_LOGIN_GROUP
FULLTEXT_GROUP
LOGIN_CHANGE_PASSWORD_GROUP
LOGOUT_GROUP
SCHEMA_OBJECT_ACCESS_GROUP
SCHEMA_OBJECT_CHANGE_GROUP
SCHEMA_OBJECT_OWNERSHIP_CHANGE_GROUP
SCHEMA_OBJECT_PERMISSION_CHANGE_GROUP
SENSITIVE_BATCH_COMPLETED_GROUP
SENSITIVE_BATCH_STARTED_GROUP
SENSITIVE_SERVER_OBJECT_CHANGE_GROUP
SERVER_OBJECT_OWNERSHIP_CHANGE_GROUP
SERVER_OBJECT_PERMISSION_CHANGE_GROUP
SERVER_OPERATION_GROUP
SERVER_PERMISSION_CHANGE_GROUP
SERVER_PRINCIPAL_CHANGE_GROUP
SERVER_PRINCIPAL_IMPERSONATION_GROUP
SERVER_ROLE_MEMBER_CHANGE_GROUP
SERVER_STATE_CHANGE_GROUP
SUCCESSFUL_DATABASE_AUTHENTICATION_GROUP
SUCCESSFUL_LOGIN_GROUP
TRACE_CHANGE_GROUP
TRANSACTION_GROUP
USER_CHANGE_PASSWORD_GROUP
USER_DEFINED_AUDIT_GROUP
LEDGER_OPERATION_GROUP

関連する動的管理ビュー

SQL Server Audit (データベース エンジン) – SQL Server | Microsoft Docs

ここをみると、いくつか関連する動的管理ビューがあるとの説明が。ただ説明だけ読んでもどんな情報があるのか想像つかない笑 ので、実際にみてみました。

sys.dm_audit_actions

  • アクションのマスタのようなものっぽい。監査ログにはアクションはaction_idを使って記録されるようですが、そのaction_idの定義の詳細が確認できる模様。

sys.dm_server_audit_status

  • 監査オブジェクトの一覧と、そのステータス(STARTED=有効)、ログファイルサイズなどが確認できるようです。

sys.dm_audit_class_type_map

  • これも監査ログ内に出力されるclass_typeとclass_type_descをマッピングするためのマスタのようですね。

fn_get_audit_file

  • これは、SQL Server Auditファイルのログ内容をSQLで取得、表示するための関数のようですね。

sys.fn_get_audit_file (Transact-SQL) – SQL Server | Microsoft Docs

実際に構成してみた

それでは、サーバーレベル監査とデータベースレベル監査を構成する手順を確認しておきたいと思います。ちょっとややこしいと思ったのが、監査構成にあたっては、以下の2つのオブジェクトを作成した上で、それぞれ有効化する必要があるということ。

  • 監査オブジェクト(サーバレベル/データベースレベルで共通)
  • 監査仕様オブジェクト(サーバレベル/データベースレベルそれぞれの階層で作成)

監査の構成手順は以下を参考にしました。

サーバー監査およびサーバー監査の仕様を作成する – SQL Server | Microsoft Docs

サーバー監査およびデータベース監査の仕様を作成する – SQL Server | Microsoft Docs

監査オブジェクトの作成

SQL Serverのセキュリティ>監査を右クリックして、新規監査オブジェクトの作成を行います。

ここではログ転送の動作などを指定できます。

サーバーレベル監査の構成

次にサーバーレベル監査の有効化です。

SQL Server>セキュリティ>サーバー監査の仕様から、新規のサーバー監査オブジェクトを作成します。このとき、監査オブジェクトを指定する必要があるので、最初のステップで作成したオブジェクトを指定します。

このオブジェクトの中では、監査対象とするアクショングループを指定していきます。

構成したら、監査オブジェクトとサーバー監査仕様オブジェクトを右クリックして有効します。これで監査が開始します。

出力されたログは監査オブジェクトを右クリック>ログを表示から確認できます。

なお、ファイルの実体はバイナリ?になっていて直接参照することはできないようでした。

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

続いてデータベースレベル監査を構成します。

監査オブジェクトは既に作成しているので、監査仕様オブジェクトのみを追加で作成します。

今度は、個別データベース配下のセキュリティ>データベース監査仕様から新規作成します。

前のセクションで触れたように、データベースレベルの監査では、SELECTやINSERTといった個別のアクションも指定可能です。

構成したら有効化。

サーバーレベル監査と同じ方法でログを参照できました。

以上、SQL Server AuditでできることをDocsと簡単なハンズオンで確認してみました。

少しでも参考になりましたら幸いです。

おしまい

この記事を気に入っていただけたらシェアをお願いします!

コメントを残す

メールアドレスが公開されることはありません。 が付いている欄は必須項目です

ABOUT US
Yuu113
初めまして。ゆうたろうと申します。 兵庫県出身、東京でシステムエンジニアをしております。現在は主にデータ分析、機械学習を活用してビジネスモデリングに取り組んでいます。 日々学んだことや経験したことを整理していきたいと思い、ブログを始めました。旅行、カメラ、IT技術、江戸文化が大好きですので、これらについても記事にしていきたいと思っています。