こんにちは。今週末は腹をくくってにしたSQL Serverの理解を深めることにしたいと思います。
第一弾はSQL Serverの監査機能の１つであるSQL Server Audit。
それではまいります。
Contents
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
実際に構成してみた
それでは、サーバーレベル監査とデータベースレベル監査を構成する手順を確認しておきたいと思います。ちょっとややこしいと思ったのが、監査構成にあたっては、以下の２つのオブジェクトを作成した上で、それぞれ有効化する必要があるということ。
- 監査オブジェクト（サーバレベル/データベースレベルで共通）
- 監査仕様オブジェクト（サーバレベル/データベースレベルそれぞれの階層で作成）
監査の構成手順は以下を参考にしました。
サーバー監査およびサーバー監査の仕様を作成する – SQL Server | Microsoft Docs
サーバー監査およびデータベース監査の仕様を作成する – SQL Server | Microsoft Docs
監査オブジェクトの作成
SQL Serverのセキュリティ＞監査を右クリックして、新規監査オブジェクトの作成を行います。
ここではログ転送の動作などを指定できます。
サーバーレベル監査の構成
次にサーバーレベル監査の有効化です。
SQL Server＞セキュリティ＞サーバー監査の仕様から、新規のサーバー監査オブジェクトを作成します。このとき、監査オブジェクトを指定する必要があるので、最初のステップで作成したオブジェクトを指定します。
このオブジェクトの中では、監査対象とするアクショングループを指定していきます。
構成したら、監査オブジェクトとサーバー監査仕様オブジェクトを右クリックして有効します。これで監査が開始します。
出力されたログは監査オブジェクトを右クリック＞ログを表示から確認できます。
なお、ファイルの実体はバイナリ？になっていて直接参照することはできないようでした。
データベースレベル監査の構成
続いてデータベースレベル監査を構成します。
監査オブジェクトは既に作成しているので、監査仕様オブジェクトのみを追加で作成します。
今度は、個別データベース配下のセキュリティ＞データベース監査仕様から新規作成します。
前のセクションで触れたように、データベースレベルの監査では、SELECTやINSERTといった個別のアクションも指定可能です。
構成したら有効化。
サーバーレベル監査と同じ方法でログを参照できました。
以上、SQL Server AuditでできることをDocsと簡単なハンズオンで確認してみました。
少しでも参考になりましたら幸いです。
おしまい
コメントを残す