EP.02 のアンチパターン AP5 で書いたとおり、監査ログは民主化の Day 0 から設定しないと、後から取り戻せない。本記事では、ログの 取得 → 保存 → 検知 → レビュー の 4 段を実装目線で整理します。
1. ログの取得（ ネイティブ機能）
| DWH | クエリ実行ログ | アクセス追跡 |
|---|---|---|
| `INFORMATION_SCHEMA.JOBS_*` | Cloud Audit Logs (Data Access) | |
| `ACCOUNT_USAGE.QUERY_HISTORY` | `ACCOUNT_USAGE.ACCESS_HISTORY` | |
| Redshift | `STL_QUERY` / `SVL_QUERY_REPORT` | Audit Logging () |
2. 長期保存 DWH に複製
ネイティブのログは 保持期間が短い ことが多い（BigQuery JOBS_BY_PROJECT は 180 日）。コンプライアンス上 3 年〜7 年保管 が必要なケースに備え、自社の DWH の専用スキーマに毎日 して長期保存します。
BigQuery: 毎日ジョブログを長期保存テーブルに UNION ALL
SQL
CREATE TABLE IF NOT EXISTS `audit.jobs_archive`PARTITION BY DATE(creation_time)OPTIONS ( partition_expiration_days = 2555 -- 7年保存)AS SELECT * FROM `region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT WHERE FALSE;
-- 日次バッチで前日分を appendINSERT INTO `audit.jobs_archive`SELECT *FROM `region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECTWHERE DATE(creation_time) = DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY);3. 検知ルール 5 セット
ルール 1: 高コストクエリ
前日 100 GB 超 / コスト $1超のクエリ
SQL
SELECT user_email, query, total_bytes_billed / 1e9 AS gb, creation_timeFROM `audit.jobs_archive`WHERE DATE(creation_time) = DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY) AND total_bytes_billed > 100 * 1e9;ルール 2: テーブル参照
PII を含むテーブルへの非ホワイトリストアクセス
SQL
WITH pii_tables AS ( SELECT table_id FROM `audit.pii_table_registry`)SELECT user_email, query, creation_timeFROM `audit.jobs_archive` j, UNNEST(j.referenced_tables) rtWHERE rt.table_id IN (SELECT table_id FROM pii_tables) AND user_email NOT IN (SELECT email FROM `audit.pii_authorized_users`) AND DATE(creation_time) = DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY);ルール 3: 営業時間外実行
深夜・早朝の対話的クエリ（バッチユーザは除外）
SQL
SELECT user_email, query, creation_timeFROM `audit.jobs_archive`WHERE DATE(creation_time) = DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY) AND EXTRACT(HOUR FROM creation_time AT TIME ZONE 'Asia/Tokyo') NOT BETWEEN 7 AND 22 AND user_email NOT IN (SELECT email FROM `audit.batch_service_accounts`);ルール 4: 退職者アクセス
HR システムと突合して退職済みユーザのアクセス検知
SQL
SELECT j.user_email, j.creation_time, hr.termination_dateFROM `audit.jobs_archive` jJOIN `hr.terminated_users` hr ON j.user_email = hr.emailWHERE j.creation_time > hr.termination_date;-- アカウント停止漏れの検知。退職翌日には DWH から弾く運用設計が前提ルール 5: 急激な行動変化
個人レベルでクエリ数が前月比 5 倍
SQL
WITH monthly AS ( SELECT user_email, DATE_TRUNC(DATE(creation_time), MONTH) AS month, COUNT(*) AS query_count FROM `audit.jobs_archive` GROUP BY 1, 2)SELECT cur.user_email, cur.query_count AS this_month, prev.query_count AS prev_month, cur.query_count / prev.query_count AS ratioFROM monthly curJOIN monthly prev ON cur.user_email = prev.user_email AND prev.month = DATE_SUB(cur.month, INTERVAL 1 MONTH)WHERE cur.month = DATE_TRUNC(CURRENT_DATE(), MONTH) AND cur.query_count >= 50 AND cur.query_count / prev.query_count >= 5.0;4. 月次レビュー会の進め方
アラートの対応で終わらず、月 1 回 30 分のレビュー会で 「異常事象 + 改善提案」を回すと、長期的に組織の感度が上がります。
| セクション | 時間 | 内容 |
|---|---|---|
| 先月の異常事象サマリー | 5分 | 5 ルールごとの件数、目立つケース |
| 代表ケース掘り下げ | 15分 | 1-2 件深掘りして「これは事故？正常？」を判断 |
| 改善アクション決定 | 10分 | ルールの調整、教育施策、ガードレール強化など |
観測すべき
- 監査ログのカバレッジ（DWH の全クエリのうち何 % が長期保存されているか）
- ルール 1-5 の月次件数推移（増加トレンドは要警戒）
- 異常クエリ検知から対応までのリードタイム
- 退職者アクセス（ルール 4）件数 = 0 を維持
次の話
EP.11 では、非エンジニアに教える『最小集合』。 を全部教えなくていい。何を教えて、何を教えないか。
この記事の感想を教えてください
あなたの 1 クリックで、本当にこの記事は更新されます。「もっと詳しく」「続編希望」が一定数集まった記事は、 ふくふくが 実際に内容を拡充したり続編記事を公開 します。 送信したリアクションはお使いのブラウザに記録され、再カウントされません。