こんにちは、技術本部ML・データ部データ基盤ブロックの塩崎です。最近の気になる論文は、こちら1の動物病院での猫のストレスが音楽によって低減されるというものです。 さて、2年前にGCPの新米管理者になり色々と頑張っていますという内容のブログを公開しました。当時は対応が後手に回ってしまっていた内容でしたが、その後2年が経ったので、最近のGoogle Cloud管理者事情も紹介いたします。
この記事はGoogle Cloud Next'23 Tokyoの発表内容をブログにしたものです。イベント終了後にスライド公開が解禁されるため、終了し次第スライドも本記事に貼り付ける予定です。
前回のおさらい
まずは、前回に公開した記事を軽く振り返ります。2年前に以下の記事を公開しました。幸いなことにSNSで多くの反応を頂き、弊社だけでなく多くの会社が管理業務に苦労している事がわかりました。
どんな事件があったのかを軽く振り返ってみます。前回のブログを既に読んでいる方は次章まで読み飛ばしても問題ありません。
MyFisrt Projectが大量発生した事件
チュートリアル用途のプロジェクトであるMyFisrt Projectが大量に作られ、放置されていました。そのため、不要プロジェクトを削除した後にプロジェクト作成権限を限定的にする対応をとりました。
退職者の権限が残っていた事件
退職時のIAM権限削除が不十分であったため、退職者の権限が残っていました。Google Cloudのアカウント情報はMicrosoft Entra ID(旧Azure AD)とSAML連携されており、Entra ID側で無効化されていました。ですので、退職者がアクセスできていたわけではありませんが、望ましくない状況でした。kintoneに保存されている従業員マスタと突き合わせて権限を一括で削除するとともに、月次で棚卸しするようにしました。
Billing Accountが大量発生した事件
MyFisrt Projectを作成する時にBilling Accountも一緒に作成してしまうケースがありました。基本的にはMyFirst Projectと同様に対処しました。ですが、監査の都合によって消せないリソースやGoogle CloudのOrganization Admin権限でも操作不能なリソースがあったため、やや対応は煩雑でした。
教訓
この後手にまわった対応をした結果得られた教訓は、一度荒れてしまったら直すのは大変というものでした。そのために、荒れないようにするためのトラブルを未然に防ぐ活動も大事であると再認識しました。
トラブルの未然防止
ここからトラブルの未然防止のために行ったことを紹介します。まずは、Google Cloudに関する様々な情報をBigQueryに集約します。その後、トラブルに繋がりかねない「良くない臭い」をSQLで定義し、定期的にCloud Functionsからクエリを実行します。そして、臭いを検知したらSlackに通知する仕組みを作りました。
BigQueryに集約する理由
Google Cloudに関する情報をBigQueryに集約する理由について説明します。
連携機能が豊富 まず、BigQueryはGoogle Cloudの様々サービスと連携できるという点が挙げられます。この後に説明するサービスもBigQueryとの連携機能をデフォルトで持っている事が多いです。
パワフルな分析機能 次にパワフルな分析機能が挙げられます。BigQueryに集約しているデータ量は数TBにもなるため、ビッグデータ処理を念頭に作られてシステムでないと、分析できません。BigQueryは大量のコンピューティングリソースでパワフルな分析を行えます。
BigQueryが分析基盤の中核になっている 最後はBigQueryがGoogle Cloudにおける分析基盤の中核になっているという点です。BigQuery MLやBigQuery Studioなどの新サービス発表をみると、今後もBigQueryはますます便利になっていくであろうことが分かります。そのため、BigQueryにデータを集約することでBigQueryの成長に便乗できる可能性が高いです。
集約しているデータ紹介
どのようなデータをBigQueryに集約しているのかを紹介します。
Cloud Audit Logs
Cloud Audit Logsから監査ログを取得できます。監査ログには「いつ」「どこで」「誰が」「何をしたのか」が記録されています。監査ログは主に以下の4つからなるため、それぞれを説明します。
Admin Activity audit logs
リソースの構成やメタデータを変更する操作が記録されています。具体的にはGoogle Compute EngineでVMを作成したり、削除した時のログが一例です。
Data Access audit logs
リソースの構成やメタデータを読み取る操作が記録されています。先程のログは「変更」操作でしたが、こちらのログは「読み取り」操作のログです。BigQueryテーブルへのSELECTやGoogle Cloud Storageからのファイルダウンロードなどが記録されます。
System Event audit logs
Google Cloudのサービスによるリソース変更操作が記録されます。
Policy Denied audit logs
セキュリティポリシー違反な操作を拒否したログが記録されます。
これら4つのうちで特に上2つのログを良く参照します。
Cloud Asset Inventory
Cloud Asset InventoryはGoogle Cloud内にあるアセットを検索・分析できるサービスです。先程のCloud Audit Logsがトランザクションデータだとするなら、Cloud Asset Inventoryはマスタデータに相当します。
アセットとは主に以下の3つを指します。これらの情報を検索・分析できます。
Resource Google Cloudリソースのメタデータです。例えば、どのようなVMが稼働しているのか、CPU・Memoryなどのスペックはどの程度なのかという情報です。
Policies リソースに対して設定されたポリシーのメタデータです。主にIAM Policyなどに関するデータが格納されていると考えれば分かりやすいです。
Runtime information OS Inventory Managementなどのランタイムに関するメタデータです。
Cloud Billing
Cloud BillingはGoogle Cloudの請求情報を管理しています。以下の3つの情報をBigQueryにエクスポートできますが、基本的には一番目の情報だけで十分なことが多いです。
Standard usage cost Project ID、サービス、SKU、使用量、費用などが含まれる請求情報です。
Detailed usage cost 上記の情報に加えてリソースレベルの情報が含まれた請求情報です。Google Compute EngineのVMレベルでの費用分析などを行う時に利用します。
Pricing SKUごとの単価情報です。
kintone
kintoneで管理しているマスタ情報があるため、これらの情報もBigQueryに集約しています。
従業員マスタ 従業員の氏名やメールアドレス（Google CloudのIDとしても使われる）などが格納されています。退職フラグが立っている従業員を抜き出すことで退職者情報を作成できます。
Google Cloud管理者マスタ Google Cloudのプロジェクトと管理者や管理部署などの情報が格納されています。Google Cloud全体の管理者とは別にプロジェクトごとにも管理者を立てる分割統治をしているため、プロジェクト管理者に連絡するために利用します。
集約する方法
先程のデータをどのようにしてBigQueryに集約しているのかを紹介します。
Cloud Audit Logs
Cloud Audit Logsはログ情報をCloud Loggingに出力できるので、まずはそこに出力します。以下の設定でOrganization内の全ての監査ログがCloud Loggingに出力されます。
data "google_organization" "zozo-com" { domain = "zozo.com" } resource "google_organization_iam_audit_config" "zozo-com" { org_id = data.google_organization.zozo-com.org_id service = "allServices" audit_log_config { log_type = "ADMIN_READ" } audit_log_config { log_type = "DATA_READ" } audit_log_config { log_type = "DATA_WRITE" } }
次にCloud LoggingのLog Sink機能を使って、BigQueryにログを出力します。destinationには予め作成しておいたBigQueryのデータセットを指定します。
resource "google_logging_organization_sink" "audit_log_sink" { name = "audit_log_sink" org_id = data.google_organization.zozo-com.org_id destination = "bigquery.googleapis.com/${google_bigquery_dataset.audit_log.id}" include_children = true filter = "protoPayload.@type=\"type.googleapis.com/google.cloud.audit.AuditLog\"" }
このログはデータ量が大きいため、古いログを定期的にGoogle Cloud Storage Archiveに移動させています。Cloud SchedulerからCloud Functionsを定期的に起動してデータを移動させています。このテクニックの詳細については以下のQiitaにもまとめています。
この部分のアーキテクチャは以下のようになります。
Cloud Asset Inventory
Cloud Asset Inventoryのデータは gcloud asset export コマンドでBigQueryにエクスポートできます。
そのため、gcloudがインストールされたコンテナイメージを用意して、毎日Cloud SchedulerとCloud Runでコマンドを実行するようにしています。Cloud Run上で動いているコンテナはHTTPリクエストを受け取る必要があるので、goで簡単なHTTPサーバーを立てgcloudコマンドを起動しています。
このデータの容量は小さいので過去分のスナップショットを全て保存しています。
ソースコードの一部を抜粋します。
func main() { http.HandleFunc("/export_asset_inventory", scriptHandler) port := os.Getenv("PORT") if port == "" { port = "8080" log.Printf("Defaulting to port %s", port) } // Start HTTP server. log.Printf("Listening on port %s", port) if err := http.ListenAndServe(":"+port, nil); err != nil { log.Fatal(err) } } func scriptHandler(w http.ResponseWriter, r *http.Request) { if r.Method == "POST" { cmd := exec.CommandContext(r.Context(), "/bin/bash", "export_asset_inventory.sh") cmd.Stderr = os.Stderr out, err := cmd.Output() if err != nil { w.WriteHeader(500) } w.Write(out) } }
goから実行している export_asset_inventory.sh の内容も以下に示します。
#!/bin/bash set -eux # gcloud organization listコマンドで取得可能な12桁の数字 # ドメイン名ではないことに注意 organization_id=123456789012 current_date=$(TZ=-9 date +%Y%m%d) for content_type in resource iam-policy org-policy access-policy os-inventory do if [ $content_type == "resource" ]; then gcloud asset export --bigquery-dataset asset_inventory \ --bigquery-table "${content_type}_${current_date}" \ --output-bigquery-force \ --organization $organization_id \ --content-type resource \ --snapshot-time "$(TZ=-9 date +"%Y-%m-%dT%H:%M:%SZ")" else gcloud asset export --bigquery-dataset asset_inventory \ --bigquery-table "${content_type}_${current_date}" \ --output-bigquery-force \ --organization $organization_id \ --content-type $content_type \ --snapshot-time "$(TZ=-9 date +"%Y-%m-%dT%H:%M:%SZ")" \ --partition-key request_time fi done
Cloud Billing
Cloud BillingからBigQueryへのエクスポートはCLIやterraformでは設定できず、Webコンソールのみから設定できます。
Cloud Bilingの管理画面からBilling exportメニューを開くと、設定画面があります。保存先のBigQueryのデータセットは予め作成しておく必要があります。
kintone
kintoneからBigQueryへのデータエクスポートにはCloud FunctionsとBigQueryのRemote UDFを利用します。
詳しい方法は以下のテックブログ記事に記載しているので、こちらを参照下さい。
集約したデータを活用
ここからはBigQueryに集約したデータに対してどのようなクエリを実行しているのかを説明します。
BigQuery→Slack通知システム
クエリ紹介の前にBigQueryからSlackに通知しているシステムを紹介します。
以下の記事に書かれているデータクオリティモニタリングシステムをSlack通知に活用しています。定期的にBigQueryへクエリを実行して、特定の条件（結果の行数が1行以上など）にマッチした時、Slackへ通知させています。元々は記事に書いてある通りデータクオリティモニタリング用途で開発されましたが、汎用性が高かったので流用しています。
Google Cloud費用アラート
最初の活用事例として紹介するのはGoogle Cloud費用アラートです。Cloud Billingから出力されるデータを集計して通知しています。プロジェクト単位・サービス単位で日毎の費用をGROUP BYして、過去30日間の平均値を大きく上回った場合に通知しています。
クエリはシンプルなので省略し、代わりに通知の様子を紹介します。毎朝チェックが行われ、場合によっては以下のような通知がなされます。通知メッセージはテンプレートエンジンでカスタマイズ可能なので、柔軟な通知ができます。
ドメイン取得チェック
次にドメイン取得チェックについても紹介します。
ドメインのライフサイクルはサービスのそれよりも長くなることがあります。サービス終了後にドメインを更新せずに所有権を手放してしまうと問題になり、ネットニュースを賑わせることもあります。発生する問題の詳細についてはJPRSの注意喚起を参照下さい。
そのため、ZOZOではドメインを取得できる部署を一元化し、このような問題を防いでいます。Google Cloudでのドメイン取得は会社のルールで禁止しているために、ドメインが取得されていないかをチェックしています。取得されたドメイン情報はCloud Asset InventoryのResource情報の中に入っているので、以下のクエリでチェックできます。
select regexp_extract(name, r'//domains.googleapis.com/projects/([a-zA-Z0-9-]+)/') as project_id, json_value(resource.data, '$.domainName') as domain_name, from <resource> asset_type = 'domains.googleapis.com/Registration' and json_value(resource.data, '$.state') = 'ACTIVE'
なお、AWSでもドメイン取得を社内ルールで禁止しており、Service Control Policyを使ってより強固に禁止ルールを敷いています。
Google CloudのDENY Policyでドメイン取得を禁止できるようになりましたら、DENY Policyに移行したいと考えています。
社外ユーザーの権限チェック
Google Cloudは自組織以外が管理しているGoogle Workspaceユーザーにも権限付与ができ、組織間のコラボレーションが簡単にできます。しかし、無闇な他組織との共有は同時にガバナンスを低下させる原因にもなります。そのため、他組織と共有する際には事前申請制にしています。
権限情報はCloud Asset InventoryのIAM Policyに格納されています。以下のクエリで社外ユーザー(zozo.com以外のユーザー)に対して権限が付与されているかどうかをチェックしています。
-- 許可されたユーザー create temporary function is_exampt_member(member string) as ( member in ( 'user:hoge@example.com', 'user:fuga@example.com' ) ); select distinct member from ( select binding.members from <iam-policy>, unnest(iam_policy.bindings) as binding ), unnest(members) as member where not is_exampt_member(member) and ( starts_with(member, "user:") and not ends_with(member, "@zozo.com") or starts_with(member, "group:") and not ends_with(member, "@zozo.com") or starts_with(member, "domain:") and not ends_with(member, "zozo.com") )
以下のOrganization Policyを使うことでそもそも権限付与をさせないという、より強固な制限もできます。しかし、known issuesに書かれている運用が煩雑なため、権限付与の検知をしたら担当者に連絡をとって権限削除をしてもらう運用にしています。
社外Service Accountの権限チェック
先程の社外ユーザーの権限チェックのService Account版です。個人の権限は@の後ろ側のドメインだけで判断できるので簡単でしたが、Service Accountはドメインの単純な比較では判断できません。ドメインの一部にProject IDが含まれているので、そのIDが組織内に属しているかで判断します。
まずは、Cloud Asset InventoryのResource情報から自組織のProject ID一覧を作成します。
with project_ids_in_zozo as ( select json_value(resource.data, '$.projectId') as project_id from <resource> where asset_type = 'cloudresourcemanager.googleapis.com/Project' )
次にService Accountにはユーザー管理のものとGoogle管理のものがあるので、それらを判別する関数も作成します。
create temporary function serviceaccount_type(member string) as ( case when regexp_contains(member, r'^serviceAccount:.+\.svc\.id\.goog\[.+\]$') then "WorkloadIdentity" when regexp_contains(member, r'^serviceAccount:\d+(-compute)?@developer\.gserviceaccount\.com$') then "GCE default" when regexp_contains(member, r'^serviceAccount:.+@appspot\.gserviceaccount\.com$') then "AppEngine default" when regexp_contains(member, r'^serviceAccount:\d+@cloudbuild\.gserviceaccount\.com$') then "Cloud Build default" when regexp_contains(member, r'^serviceAccount:\d+@cloudservices\.gserviceaccount\.com$') then "Google API Service Agent" when regexp_contains(member, r'^serviceAccount:service-\d+@(cloud-ml|gae-api-prod)\.google.com\.iam\.gserviceaccount\.com$') then "Google API Service Agent" when regexp_contains(member, r'^serviceAccount:.+@gcp-sa-[a-z-]+\.iam\.gserviceaccount\.com$') then "Google API Service Agent" when regexp_contains(member, r'^serviceAccount:.+@bigquery-data-connectors\.iam\.gserviceaccount\.com$') then "Google API Service Agent" when regexp_contains(member, r'^serviceAccount:(project-|service-|service-org-)\d{9,}@[-a-z0-9]+\.iam\.gserviceaccount\.com$') then "Google API Service Agent" when regexp_contains(member, r'^serviceAccount:analytics-processing-dev@system\.gserviceaccount\.com$') then "Google Analytics Service Agent" when regexp_contains(member, r'^serviceAccount:firebase-.+@system\.gserviceaccount\.com$') then "Firebase Service Agent" when regexp_contains(member, r'^serviceAccount:.+@(crashlytics-bigquery-prod|fcm-bq-export-prod|performance-bq-export-prod|firebase-sa-management)\.iam\.gserviceaccount\.com$') then "Firebase Service Agent" when regexp_contains(member, r'^serviceAccount:backups@firebase-prod\.iam\.gserviceaccount\.com$') then "Firebase Service Agent" when regexp_contains(member, r'^serviceAccount:appsdev-apps-dev-script-auth@system\.gserviceaccount\.com$') then "AppScript Service Agent" when regexp_contains(member, r'^serviceAccount:billing-export-bigquery@system\.gserviceaccount\.com$') then "Billing export Service Agent" when regexp_contains(member, r'^serviceAccount:gapps-reports@system\.gserviceaccount\.com$') then "Google Workspace Service Agent" when regexp_contains(member, r'^serviceAccount:.+@[-a-z0-9]+\.iam\.gserviceaccount\.com$') then "User Managed" else "Unknown" end );
そして最後に、これらを組み合わせることで自組織外のService Account一覧を出力します。「自組織に所属していない」かつ「Google管理のService Accountではない」という条件で対象のService Accountを抽出しています。
with user_managed_service_accounts as ( select distinct member, regexp_extract(member, r'^serviceAccount:.+@([-a-z0-9]+)\.iam\.gserviceaccount\.com$') as project_id from ( select binding.members from <iam-policy>, unnest(iam_policy.bindings) as binding ), unnest(members) as member where starts_with(member, "serviceAccount:") and serviceaccount_type(member) = 'User Managed' ) select member from user_managed_service_accounts where project_id not in (select project_id from project_ids_in_zozo) and not exampted_service_accounts(member) -- 前節同様に許可されたService Accountかどうかを判断する関数を用意 order by member asc
退職者の権限チェック
退職者の権限が残っているかどうかをチェックするクエリを紹介します。
kintoneから取得した従業員マスタを使って退職フラグが立っている従業員を抽出し、退職者マスタを作成します。
with retired_employees as ( select json_value(row.mail_address) as mail_address, from unnest(( select json_query_array(<kintoneからデータ読み出しをするUDF>(従業員マスタのapp_id), "$") )) as row where json_value(row.leaving_date) is not null )
次にCloud Asset InventoryのIAM Policy情報とJOINすることで、退職者が持っているロール一覧を出します。
with iam_policies as ( select name, asset_type, role, regexp_extract(member, r'^.+:(.+?)(?:\?.+)?$') as member, from <iam_policy> unnest(iam_policy.bindings) as binding, unnest(binding.members) as member ), retired_employee_roles as ( select i.* from iam_policies as i join retired_employees as r on i.member = r.mail_address )
この時点で必要最低限のものは完成しているのですが、このまま使用するとどのリソースに対して付与されているロールなのかが分かりづらいです。そのため、以下のようにCloud Asset InventoryのResource情報ともJOINしてリソース情報を補います。
with retired_employee_roles_full as ( select resolve_project_id(retired_employee_roles.name, retired_employee_roles.asset_type, resource.resource.data) as project_id, -- 関数の説明は後述 resolve_resource_name(retired_employee_roles.name, retired_employee_roles.asset_type, resource.resource.data) as resource_name, retired_employee_roles.name as full_resource_name, retired_employee_roles.asset_type, retired_employee_roles.role, retired_employee_roles.member, from retired_employee_roles left join <resource> as resource using(name) )
ここで注意が必要な点として、Resource情報の中のどこにProject IDやリソース名が格納されているのかは asset_type によるという点です。そのために、以下のようなヘルパー関数を用意して、 resource_data などからProject IDとリソース名を抽出できるようにしました。
create temporary function lookup_project_id_from_number(project_number any type) as (( select json_value(resource.data, '$.projectId') from <resource> where name = concat('//cloudresourcemanager.googleapis.com/projects/', project_number) )); create temporary function resolve_project_id_or_number(full_name string, asset_type string, resource_data string) as ( case asset_type when 'cloudresourcemanager.googleapis.com/Organization' then "<Organization>" when 'cloudbilling.googleapis.com/BillingAccount' then '<Billing Account>' when 'cloudresourcemanager.googleapis.com/Project' then json_value(resource_data, '$.projectId') when 'bigquery.googleapis.com/Dataset' then regexp_extract(full_name, r'//bigquery.googleapis.com/projects/(.+)/datasets/.+') when 'storage.googleapis.com/Bucket' then json_value(resource_data, '$.projectNumber') when 'iap.googleapis.com/WebType' then regexp_extract(full_name, r'//iap.googleapis.com/projects/(.+)/iap_web/.+') when 'iam.googleapis.com/ServiceAccount' then regexp_extract(full_name, r'//iam.googleapis.com/projects/(.+)/serviceAccounts/.+') when 'compute.googleapis.com/Subnetwork' then regexp_extract(full_name, r'//compute.googleapis.com/projects/(.+)/regions/.+/subnetworks/.+') when 'cloudkms.googleapis.com/CryptoKey' then regexp_extract(full_name, r'//cloudkms.googleapis.com/projects/(.+)/locations/.+/keyRings/.+/cryptoKeys/.+') when 'bigquery.googleapis.com/Table' then json_value(resource_data, '$.tableReference.projectId') when 'orgpolicy.googleapis.com/Policy' then regexp_extract(full_name, r'//orgpolicy.googleapis.com/projects/(.+)/policies/.+') when 'cloudresourcemanager.googleapis.com/Folder' then "<Folder>" when 'iap.googleapis.com/Web' then regexp_extract(full_name, r'//iap.googleapis.com/projects/(.+)/.+') when 'iap.googleapis.com/WebService' then regexp_extract(full_name, r'//iap.googleapis.com/projects/(\d+)/.+') when 'iap.googleapis.com/TunnelInstance' then regexp_extract(full_name, r'//iap.googleapis.com/projects/(.+)/iap_tunnel/.+') when 'secretmanager.googleapis.com/Secret' then regexp_extract(full_name, r'//secretmanager.googleapis.com/projects/(.+)/secrets/.+') else "<Unknown asset_type>" end ); create temporary function resolve_project_id(full_name string, asset_type string, resource_data string) as ( if( regexp_contains(resolve_project_id_or_number(full_name, asset_type, resource_data), r'^\d+$'), lookup_project_id_from_number(resolve_project_id_or_number(full_name, asset_type, resource_data)), resolve_project_id_or_number(full_name, asset_type, resource_data) ) ); create temporary function resolve_resource_name(full_name string, asset_type string, resource_data string) as ( case asset_type when 'cloudresourcemanager.googleapis.com/Organization' then json_value(resource_data, '$.displayName') when 'cloudbilling.googleapis.com/BillingAccount' then json_value(resource_data, '$.displayName') when 'cloudresourcemanager.googleapis.com/Project' then json_value(resource_data, '$.projectId') when 'bigquery.googleapis.com/Dataset' then regexp_extract(full_name, r'//bigquery.googleapis.com/projects/.+/datasets/(.+)') when 'storage.googleapis.com/Bucket' then regexp_extract(full_name, r'//storage.googleapis.com/(.+)') when 'iap.googleapis.com/WebType' then regexp_extract(full_name, r'//iap.googleapis.com/projects/.+/iap_web/(.+)') when 'iam.googleapis.com/ServiceAccount' then json_value(resource_data, '$.email') when 'compute.googleapis.com/Subnetwork' then json_value(resource_data, '$.name') when 'cloudkms.googleapis.com/CryptoKey' then regexp_extract(full_name, r'//cloudkms.googleapis.com/projects/.+/locations/.+/keyRings/.+/cryptoKeys/(.+)') when 'bigquery.googleapis.com/Table' then concat(json_value(resource_data, '$.tableReference.datasetId'), ".", json_value(resource_data, '$.tableReference.tableId')) when 'orgpolicy.googleapis.com/Policy' then regexp_extract(full_name, r'//orgpolicy.googleapis.com/projects/.+/policies/(.+)') when 'cloudresourcemanager.googleapis.com/Folder' then json_value(resource_data, '$.displayName') when 'iap.googleapis.com/Web' then regexp_extract(full_name, r'//iap.googleapis.com/projects/.+/(.+)') when 'iap.googleapis.com/WebService' then regexp_extract(full_name, r'//iap.googleapis.com/projects/\d+/(.+)') when 'iap.googleapis.com/TunnelInstance' then regexp_extract(full_name, r'//iap.googleapis.com/projects/.+/iap_tunnel/(.+)') when 'secretmanager.googleapis.com/Secret' then regexp_extract(full_name, r'//secretmanager.googleapis.com/projects/.+/secrets/(.+)') else "<Unknown asset_type>" end );
さらに、kintoneからGoogle Cloud管理者マスタも取得してプロジェクト毎の管理者への連絡に使用します。
with gcp_admin as ( select json_value(row.project_id) AS project_id, array(select json_value(elem.code) from unnest(json_query_array(row.administrator, '$')) as elem) as administrator, from unnest(( select json_query_array(<kintoneからデータ読み出しをするUDF>(Google Cloud管理者マスタのapp_id), "$") )) as row ) select * except(administrator), array_to_string(administrator, ", ") as administrator, from retired_employee_roles_full left join gcp_admin using(project_id) order by project_id, resource_name
上記のクエリを定期的に実行し、退職者の権限が検知された場合には以下のようなSlack通知がなされます。
BigQuery VIEWの参照状況
ここからはOrganizationの管理というよりもBigQueryの管理業務の話を紹介します。
テーブルの修正や削除する際の影響範囲を調査するために、 INFORMATION_SCHEMA.JOBS_BY_* を参照することはよくあります。referenced_tables 列にそのジョブが参照しているテーブル情報が格納されています。しかし、この方法ではVIEWの参照状況を取得できません。
その代わりにデータアクセス監査ログから参照されているVIEWの情報を取得できるので、そのためのクエリを紹介します。監査ログの収集に関する章で説明したように古いログはGCS Archiveに移動しています。そのため、このクエリを日次で実行した結果を積み上げたデータマートを作成しています。
select protopayload_auditlog.authenticationInfo.principalEmail as email, resource.labels.project_id, protopayload_auditlog.servicedata_v1_bigquery.jobCompletedEvent.job.jobName.jobId as job_id, timestamp, protopayload_auditlog.servicedata_v1_bigquery.jobCompletedEvent.job.jobStatistics.referencedViews as referenced_views, protopayload_auditlog.servicedata_v1_bigquery.jobCompletedEvent.job.jobConfiguration.query.query, from <cloudaudit_googleapis_com_data_access> where protopayload_auditlog.serviceName = "bigquery.googleapis.com" and protopayload_auditlog.methodName = "jobservice.jobcompleted"
BigQueryを参照しているGoogle Sheets URLの調査
Connected Sheets機能を使うとGoogle SheetsからBigQueryに接続できます。この機能はとても便利ですが、クエリは発行されているけれども発行元のシートは不明という問題も起きやすいです。
INFORMATION_SCHEMA.JOBS_BY_* の job_id 列を見ると、シートからのクエリであることは分かりますが、シートURLまでは不明です。そのため、監査ログからクエリの発行元シートURLを取得します。
select protopayload_auditlog.authenticationInfo.principalEmail as email, resource.labels.project_id, REGEXP_EXTRACT(protopayload_auditlog.resourceName, r'^projects/[\w-]+/jobs/([\w-]+)$') as job_id, timestamp, "https://docs.google.com/spreadsheets/d/" || json_value(protopayload_auditlog.metadataJson, "$.firstPartyAppMetadata.sheetsMetadata.docId") as sheet_url, json_value(protopayload_auditlog.metadataJson, "$.jobInsertion.job.jobConfig.labels.sheets_trigger") as trigger, json_value(protopayload_auditlog.metadataJson, "$.jobInsertion.job.jobConfig.queryConfig.query") as query, from <cloudaudit_googleapis_com_data_access> where protopayload_auditlog.serviceName = "bigquery.googleapis.com" and json_value(protopayload_auditlog.metadataJson, "$.firstPartyAppMetadata.sheetsMetadata.docId") is not null
BigQueryテーブルの作成者調査
BigQueryのテーブルメタデータには作成者に関する情報が含まれていません。ですが、不要テーブルの削除やテーブルに格納されたデータの問い合わせなどの業務で作成者の情報が必要になることもあります。
そのため、監査ログからテーブルの作成者を取得します。
select protopayload_auditlog.authenticationInfo.principalEmail as email, split(protopayload_auditlog.resourceName, "/")[safe_offset(1)] as project_id, split(protopayload_auditlog.resourceName, "/")[safe_offset(3)] as dataset_id, split(protopayload_auditlog.resourceName, "/")[safe_offset(5)] as table_id, timestamp, from <cloudaudit_googleapis_com_activity> where protopayload_auditlog.serviceName = "bigquery.googleapis.com" and array_length(protopayload_auditlog.authorizationInfo) = 1 and protopayload_auditlog.authorizationInfo[safe_offset(0)].permission = "bigquery.tables.create" and regexp_contains(protopayload_auditlog.resourceName, r'projects/.+/datasets/.+/tables/.+')
監査ログに対するクエリのコツ
監査ログはカラム数が約1000個もあり、構造体や配列が何重にも入れ子になっています。そのため、ちょっとしたクエリを書くだけでも一苦労です。そのような監査ログに対するクエリを書くコツを紹介します。
1. 配列に慣れる
SQLの配列機能は独特です。Javaなどの言語に当たり前のようにあるforループがSQLには存在しないので、配列をイテレーションする方法が独特です。以下の公式ドキュメントに書かれているテクニックは基本的なものなので自然と暗記するまで通読しましょう。
2. CONTAINS_SUBSTR関数
この関数は第一引数にテーブルを渡すこともできる、少し特殊な関数です。その場合はテーブル内の全部の列が検索対象になります。構造体や配列の中身もトラバースして検索してくれるので、非構造化された監査ログ全体を対象にして検索できます。ただし、テーブル全体の検索は重い処理なので、探索的データ分析中の利用だけにとどめて定形バッチでは用いないほうが良いです。
3. JSON形式でダウンロードしてjqで開く
監査ログテーブルはNULLや空配列が多く格納されています。そのため、テーブル形式で見るとNULLが連続して見づらいことがあります。クエリ結果をJSON形式でダウンロードしてjqなどで開くと「比較的」読みやすくなります。
4. 自分でログを作る
調査対象の操作を自分自身で行い、どの操作でどのログが発行されるのかを調査すると、監査ログに対する理解が深まります。
5. 慣れ
監査ログに対するクエリを見ていると、 principalEmail などの頻繁に参照されるカラムが登場します。そのため、他人が書いたコードを解読し、頻発するイディオムに慣れましょう。
6. 挫折に負けない
この記事で紹介しているクエリは完成形だけを見ると難解なものが多いです。どのクエリも一気に書いたものではなく、試行錯誤や挫折をしながら作り上げたものですので、根気強さが大事です。
他にも監査ログで色々とやっています
この記事では紹介しきれませんでしたが、監査ログを使って他にも色々とチェックしています。いくつかの事例の概要だけ紹介します。
1. 未使用Service Accountチェック
Cloud Asset InventoryのResource情報からService Accountマスタを作成して、監査ログとLEFT JOINします。これによってアクティビティのないService Accountを洗い出しています。同様のことをService AccountのJSON Keyに対しても行っています。
2. 古いSDKのチェック
監査ログにUserAgent情報が格納されているため、これを活用して古い言語・古いSDKが使われていないか確認しています。
3. JOBS_BY_ORGANIZATION にquery列を追加
INFORMATION_SCHEMA.JOBS_BY_ORGANIZATION にはquery列が存在しないため、扱いづらいです。そこで、監査ログから生成したJob IDとクエリの対応表をJOINすることで擬似的にquery列を追加しています。
まとめ
ZOZOでは多くの社員がGoogle Cloudを利用しており、継続的な平和維持のための活動は必須です。一度荒れてしまうと元の状態に戻すことはとても大変です（経験談）。
BigQueryはサービス分析だけではなく、Google Cloudの利用状況を分析することにも使えます。そのためにGoogle Cloudに関する各種の情報をBigQueryに集約しました。そして、SQLでルールを定義し、違反があった場合にSlackへ通知するシステムを構築しました。
ZOZOでは、一緒に楽しく働く仲間を募集中です。ご興味のある方は下記採用ページをご覧ください！
