はじめに
Snowflakeのログに関して、監査対応するにはどうしたらよいか調査しました。
Account_usageスキーマとInformation_schema
ログについて調べるとAccount_usageスキーマとInformation_schemaという似たような2種類のスキーマに出会います。私はこの二つを混同していたので違いをおさえておくのをおすすめします。
どちらもsnowflakeによってデフォルトで提供されており読み取り専用です。
Account_usageスキーマ
・Snowflakeデータベース配下にある
・実行履歴が反映されるのに時差がある
・過去365日分の履歴を保持
・アカウントレベルの権限がないと触れない（要権限付与）
・ありとあらゆるデータをVIEWに保持しており、後述のInformation_schemaの完全版のようなイメージ（Snowflakeイベントに参加した際に聞きました）
・監査ログなど重要なデータを取得したい場合はこちらをselectするのがよい
Information_schema
・各DBに自動で作成される
・実行履歴が即時反映される
・過去14日分の履歴を保持
・Account_usageスキーマのVIEWの一部のようなイメージ
・開発作業中に実行履歴を確認する場合などはこちらをselectするのがよい
→ 今回は監査ログ想定で調査をはじめたので、Account_usageスキーマを参照する方法を調べました。
ログの保管方法
監査に対応するには既定の保管日数↑では足りない可能性があるので、別で保管しておく必要があります。
現時点で思いつくのは以下の2通り
・Account_usageスキーマから抽出→ファイルに出力
・Account_usageスキーマから抽出→個別のテーブルにinsert
上記をsnowflakeのtaskや外部ツールを使用して日次で保存する運用がシンプルで良い。
個人的には、snowflakeのストレージコストは安価ですし、大量のログがあってもSQLで検索ができるので個別のテーブルにinsertするのが良いなと思いました。
監査ログに使えそうなVIEW
Account_usageスキーマ内にはたくさんのVIEWがあるのでその中から監査ログに使用できそうなものをリストアップしてみました。
| No | VIEW名 | 内容 |
|---|---|---|
| 1 | login_history | ログインユーザーがどのipアドレスからアクセスしたか、ログイン成功/失敗時のエラー内容などがわかる。 |
| 2 | query_history | 誰が、いつ、何のテーブルに、どのウェアハウスでどんなクエリしたかがわかる。どのカラムに対してかまではわからないがクエリのテキスト(SQL)から推測できそう。 |
| 3 | access_history | 誰が、いつ、何のテーブルの、どのカラムをクエリしたかわかる。配列型の項目があるので単にファイルとして見るにはわかりづらそう。 |
| 4 | task_history | taskの履歴。 taskがある場合に使用。 |
| 5 | copy_history | copyの履歴。query_historyにもcopyの履歴は残るがより細かい情報が載っている。 |
query_historyとaccess_historyは内容が重複しているところがあるのでどちらかに絞ってもいいかもしれません。
もしくは、snowflakeのテーブルにinsertする場合であれば保管コストはそんなに気にならないと思うので、どっちもとっておいて後々必要かどうか判断するのもありです。
Account_usageスキーマを参照するには
・Account_usageスキーマを参照できる権限を付与する
→参照方法を付与する方法は
・IMPORTED PRIVILEDGEを付与する
・Snowflakeデータベースロールを付与する
私の場合は、これまで経験してきた設計内容に近しかった「IMPORTED PRIVILEDGEを付与」を採用しました。
詳しくは↓
他のロールに対する SNOWFLAKE データベース使用の有効化：
https://docs.snowflake.com/ja/sql-reference/account-usage#enabling-the-snowflake-database-usage-for-other-roles
IMPORTED PRIVILEDGEとは：
https://docs.snowflake.com/ja/user-guide/security-access-control-privileges#database-privileges
注意事項
・Account_usageスキーマは、履歴が即時反映ではないのでselectするタイミングは考慮する
例：前日23:59までの分のログを翌日の夜間に取得するなど
まとめ
・監査ログなど重要なものはAccount_usageスキーマを、開発時の履歴の確認であればInformation_schemaを参照
・デフォルトのデータ保持期間が短いので監査対応の場合はファイルまたはテーブルに移しておくのが良い
・必要な内容や粒度によってログの種類を選択
調べてみてると、Account_usageスキーマには山ほどVIEWがあり、その中で山ほど項目があります汗
何が必要か/使えそうかは組織によっても異なると思いますし、こんなデータもあるんだ～という発見もあったので一度眺めてみることをおすすめします！
参考
・Account_usageスキーマ
https://docs.snowflake.com/ja/sql-reference/account-usage
・Information_schema
https://docs.snowflake.com/ja/sql-reference/info-schema
・Account_usageスキーマVIEW一覧
https://docs.snowflake.com/ja/sql-reference/account-usage#account-usage-views