はじめに
Verticaで監査を行う場合、下記2通りの方法があります。
– vertica.log に出力されている情報を参照する
– システムテーブルに格納されている情報を参照する
– システムテーブルに格納されている情報を参照する
vertica.logには実行された全てのクエリやVertica内部ジョブの実行履歴も出力されるため、定常的なモニタリングには適していないと考えられます。
本記事では、定常的なモニタリングを行うことを想定し、システムテーブルの内容を参照するために必要な内容を記載しています。
システムテーブルで取得可能な監査ログ内容
以下の表は、システムテーブルを参照することで監査可能な代表的な操作および項目を示しています。
取得対象操作
| 操作内容 | 取得可否 | スキーマ.テーブル名 | 列名 | 備考 |
|---|---|---|---|---|
| ログイン | ○ | v_monitor.user_sessions | session_start_timestamp | |
| ログオフ | ○ | v_monitor.user_sessions | session_end_timestamp | |
| 失敗ログオン | × | － | － | vertica.log には記録される |
| 成功DML | ○ | v_monitor.query_requests | request | |
| 失敗DML | × | － | － | vertica.log には記録される |
| 成功DDL | ○ | v_monitor.query_requests | request | |
| 失敗DDL | × | v_monitor.query_requests | request | 構文エラーは記録されないが、テーブル無しエラー等は記録される。vertica.log には記録される |
| 成功SELECT | ○ | v_monitor.query_requests | request | |
| 失敗SELECT | × | － | － | vertica.log には記録される |
取得対象項目
| 取得項目 | 取得可否 | スキーマ.テーブル名 | 列名 | 備考 |
|---|---|---|---|---|
| ログイン日時 | ○ | v_monitor.user_sessions | session_start_timestamp | |
| ログオフ日時 | ○ | v_monitor.user_sessions | session_end_timestamp | |
| SQL実行日時 | ○ | v_monitor.query_requests | session_start_timestamp | |
| OSユーザ | × | v_monitor.user_sessions | client_os_user_name | |
| DBユーザ | ○ | v_monitor.user_sessions | user_name | |
| マシン | ○ | v_monitor.user_sessions | node_name | |
| 端末 | ○ | v_monitor.user_sessions | client_hostname | |
| プログラム | × | v_monitor.user_sessions | client_type | "ADO.NET"、"vsql"、 "JDBC"、"ODBC"の タイプのみ取得可能 |
| オブジェクト | × | － | － | SQL文からオブジェクトを 読み取ることで代替 |
| SQL文 | ○ | v_monitor.query_requests | request | 最大64,000バイト |
| 処理件数 | ○ | v_monitor.query_profiles | processed_row_count |
Vertica監査に有用なシステムテーブル
以下の表は、監査に有用なシステムテーブルと項目を示しています。「有用度」列は、各項目の監査に対する有用度を示しています。「○」は有用、「△」は補足情報として有用であることを示しています。
以下のシステムーブルについては、基本的にスーパーユーザー以外(dbadmin以外)は、自ユーザーの監査情報しか参照できません。
自ユーザー以外の監査情報も参照したい場合は、pseudosuperuserロールの割当てが必要です。
v_monitor.user_sessionsシステムテーブル（ユーザーセッション履歴）
| カラム名 | データ型 | 有用度 | 説明 |
|---|---|---|---|
| authentication_method | varchar(128) | ||
| client_hostname | varchar(128) | ○ | クライアントホスト名 |
| client_label | varchar(64000) | △ | クライアントラベル。ODBC、JDBCのプロパティで任意文字列を埋め込める アプリ名称等を埋め込むことでアプリ処理 or その他処理の見分けが可能 |
| client_os | varchar(128) | ||
| client_os_user_name | varchar(128) | ◯ | クライアントのOSユーザ名 |
| client_pid | int | △ | クライアントのプロセスID。Vertica以外の監査ログと突き合わせできる可能性がある |
| client_type | varchar(128) | △ | クライアントタイプ |
| client_version | varchar(128) | ||
| is_active | boolean | ||
| node_name | varchar(128) | ○ | SQL実行サーバー名 |
| runtime_priority | varchar(128) | ||
| session_end_timestamp | timestamptz | ○ | セッション終了時刻。NULLの場合はセッション継続中であることを示す |
| session_id | varchar(128) | 他システムテーブルとの結合キーに使用 | |
| session_start_timestamp | timestamptz | ○ | セッション開始時刻 |
| ssl_state | varchar(128) | ||
| statement_id | int | ||
| transaction_id | int | ||
| user_name | varchar(128) | ○ | DBユーザー名 |
v_monitor.query_requestsシステムテーブル（ユーザーが発行したクエリ・リクエスト）
| カラム名 | データ型 | 有用度 | 説明 |
|---|---|---|---|
| end_timestamp | timestamptz | ○ | SQL実行終了時刻。NULLの場合はSQL実行中であることを示す |
| error_count | int | ||
| is_executing | boolean | ||
| memory_acquired_mb | float | ||
| node_name | varchar(128) | ||
| request | varchar(64000) | ○ | SQL文 |
| request_duration_ms | int | △ | SQL実行経過時間(ミリ秒)。実行中の場合は現在までの経過時間を示す |
| request_id | int | ||
| request_label | varchar(128) | ||
| request_type | varchar(128) | ||
| search_path | varchar(64000) | ||
| session_id | varchar(128) | 他システムテーブルとの結合キーに使用 | |
| start_timestamp | timestamptz | ○ | SQL実行開始時刻 |
| statement_id | int | 他システムテーブルとの結合キーに使用 | |
| success | boolean | △ | 実行されたSQLの成否。実行中断したSQLは失敗で記録。構文エラーは記録されない |
| transaction_id | int | 他システムテーブルとの結合キーに使用 | |
| user_name | varchar(128) |
query_profilesシステムテーブル（クエリ・プロファイル）
| カラム名 | データ型 | 有用度 | 説明 |
|---|---|---|---|
| error_code | int | ||
| identifier | varchar(128) | ||
| is_executing | boolean | ||
| node_name | varchar(128) | ||
| processed_row_count | int | ○ | SQL処理件数。実行中断したSQLは0件で記録される |
| query | varchar(64000) | ||
| query_duration_us | numeric(36,6) | ||
| query_search_path | varchar(64000) | ||
| query_start | varchar(63) | ||
| query_start_epoch | int | ||
| query_type | varchar(128) | △ | SQLのタイプ(DDL,LOAD,QUERY,SET,TRANSACTION,UTILITYの6種類の存在を確認) |
| reserved_extra_memory | int | ||
| schema_name | varchar(128) | ||
| session_id | varchar(128) | 他システムテーブルとの結合キーに使用 | |
| statement_id | int | 他システムテーブルとの結合キーに使用 | |
| table_name | varchar(128) | ||
| transaction_id | int | 他システムテーブルとの結合キーに使用 | |
| user_name | varchar(128) |
Vertica監査用SQL(サンプル)
上記のテーブルを利用した監査用のSQLのサンプルが以下記事に記載しております。実行されたSQLをシステムテーブルで確認する方法
http://vertica-tech.ashisuto.co.jp/executed-query/
監査ログ取得の留意事項
システムテーブルを参照して監査を行う場合、下記2点に留意する必要があります。
1. システムテーブル参照に伴うオーバーヘッド
2. システムテーブル・データの保存サイズや保存期間
2. システムテーブル・データの保存サイズや保存期間
システムテーブル参照に伴うオーバーヘッドについて
システムテーブル参照のオーバーヘッドは、サーバースペックやデータ参照量に依存します。システムへの影響を考慮する場合は、事前にテストすることを推奨します。
システムテーブル・データの保存サイズや保存期間
システムテーブルの情報は、データコレクター機能によって収集・管理されます。データコレクター機能については以下の記事をご参照ください。
システムテーブルの保存条件について
http://vertica-tech.ashisuto.co.jp/datacollector
