Cloud Audit Logsのデータアクセス監査ログから, BigQueryへのデータアクセスを可視化する方法

広告事業本部でリードデータエンジニアをしている大窄直樹 (おおさこ)です. 今回は, BigQueryの各データへのアクセスを可視化する方法について執筆します.

背景/課題
BigQueryのアクセスログの調査
- BigQueryのINFOMATION_SCHEMA
- Cloud Audit Logs
実装
まとめ

背景/課題

弊社では, データ基盤をBigQueryに作成しています. 年々利用用途, 及び利用者, アクセス方法が増えてきて管理しづらくなってきたという課題があります.

具体的には, 誰が, どのデータにアクセスして, どのように使われているかわからない状態になり, 仕様変更の際誰に確認したら良いかわからず, 保守がしづらくなってきました.

この課題を解決するために, BigQueryへのデータアクセスを可視化することで誰が, どのデータにアクセスしているかを把握することを試みます.

BigQueryのアクセスログの調査

BigQueryのアクセスログは, 大別してBigQueryのINFOMATION_SCHEMAとCloud Audit Logsの2種類あります.

BigQueryのINFOMATION_SCHEMA

BigQueryのINFOMATION_SCHEMAは, BigQueryオブジェクトに関するメタデータ情報を提供するシステム定義の読み取り専用ビューです. 具体的には, 誰がどのようなSQLを実行したかや, 各テーブルのDDLといったデータが保管されています.

例えば, 下記のクエリを実行すると直近30日間に誰がどのようなクエリを実行したかをみることができます. (regionは, 適したものに変更してください)

SELECT
  user_email, 
  query
FROM
  `region-asia-northeast1`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
WHERE
  DATE(creation_time) BETWEEN DATE_ADD(CURRENT_DATE('Asia/Tokyo'), INTERVAL -30 DAY ) AND CURRENT_DATE('Asia/Tokyo')
GROUP BY ALL

一見, INFOMATION_SCHEMAの情報を用いることで, データ基盤へのアクセスを可視化できるように見えますがダメでした. INFOMATION_SCHEMAには, GoogleCloudの自プロジェクトからのメタデータのみで, 他のプロジェクトアクセスからのログが残っていませんでした.

Cloud Audit Logs

Cloud Audit Logsは, Google Cloudの監査ログ/アクセスログを管理するサービスです. 具体的には, 下記のようなログが保管されています.

管理アクティビティ監査ログ
データアクセス監査ログ
システムイベント監査ログ
ポリシー拒否監査ログ

この中の, データアクセス監査ログにBigQueryへのアクセスログが保管されています. またこのログは, Loggingのログエクスプローラを用いることで閲覧することができます. 実際にログエクスプローラで実行した画面が下記図です.

({$project_id}は, 適したものに変更してください)

resource.type = ("bigquery_project" OR "bigquery_dataset")
logName="projects/{$project_id}/logs/cloudaudit.googleapis.com%2Fdata_access"

このログは, INFOMATION_SCHEMAと異なり, 他のGoogleCloudのプロジェクトからのアクセスも記載されています. したがって, この今回はこのCloud Audit Logsのデータアクセス監査ログを用いて可視化を行なっていきます.

実装

Cloud Audit Logsのデータアクセス監査ログをログルーターを用いてBigQueryに転送

Loggingのログルーターの"シンクを作成"をクリック

"ログルーティングシンクの作成"画面で入力下記図のように設定してください.
"シンクに含めるログの選択"の項目には, 下記のように設定してください. ({$project_id}は, 適したものに変更してください)

resource.type = ("bigquery_project" OR "bigquery_dataset")
logName="projects/{$project_id}/logs/cloudaudit.googleapis.com%2Fdata_access"

上記を設定してから数分すると, 設定したデータセット(今回の場合はtest_osako)に"cloudaudit_googleapis_com_data_access"というテーブルが作成されます.

BigQueryアクセスログを見やすくするViewを作成

下記のViewを作成します. このViewにより, 誰が, どのプロジェクトから, どのデータへアクセスしたかがわかるようになります.

カラム意味は下記です.

principalEmail
- 誰がアクセスしたか?
principalProjectName
- どのプロジェクトからのアクセスか?
referenceProjectName
- どのプロジェクトへのアクセスか?
referenceDatasetName
- どのデータセットへのアクセスか?
referenceTableName
- どのテーブルへのアクセスか?

SELECT
  DATE(timestamp) AS date,
  protopayload_auditlog.authenticationInfo.principalEmail AS principalEmail,
  REGEXP_EXTRACT(ifnull (JSON_EXTRACT_SCALAR(protopayload_auditlog.metadataJson,"$.tableDataChange.jobName"),
      JSON_EXTRACT_SCALAR(protopayload_auditlog.metadataJson,"$.tableDataRead.jobName")), r'projects/([^/]+)/') AS principalProjectName,
  REGEXP_EXTRACT(protopayload_auditlog.resourceName, r'projects/([^/]+)/') AS referenceProjectName,
  REGEXP_EXTRACT(protopayload_auditlog.resourceName, r'datasets/([^/]+)/') AS referenceDatasetName,
  REGEXP_EXTRACT(protopayload_auditlog.resourceName, r'tables/([^/@]+)') AS referenceTableName,
FROM
  -- 適したのものに変更してください
  test_osako.cloudaudit_googleapis_com_data_access
WHERE
  -- 直近200日だけ参照
  DATE(timestamp) BETWEEN DATE_SUB(CURRENT_DATE(), INTERVAL 200 DAY) AND CURRENT_DATE() 
  -- jobを可視化したいわけではないためめ省く
  AND protopayload_auditlog.resourceName NOT LIKE "%/jobs/%"
  -- Cloud Billingに関するサービスアカウント. ユーザーではないので省く
  AND protopayload_auditlog.authenticationInfo.principalEmail != "billing-export-bigquery@system.gserviceaccount.com"
GROUP BY
  ALL
ORDER BY
  1 DESC,
  2,
  3,
  4,
  5,
  6