🛡

【Looker】OAuth認証で利用者を監査する方法

2021/06/22に公開

Google Cloud

はじめに

皆さん、今日もLooker使ってますかー？ ^^
今回はOAuth認証^[1]を利用してLookerからBigQueryに発呼されたSQLを監査したいと思います。

SnowflakeおよびGoogle BigQuery接続では、オプションでOAuthを使用することができます。

実現したかったセキュリティ要件

Lookerを本番運用する上で下記のセキュリティ要件を検討しました。

Lookerへのログインを送信元IPで制御 (オフィスからのみ許可)
BigQueryへのSQLクエリを送信元IPで制御 (Lookerとオフィスからのみ許可)
LookerからBigQueryへのSQLクエリ内容の特定 (誰がいつどんなクエリを実行したか)

本投稿は上記3をOAuth認証とCloud Loggingの組み合わせで実現した際の備忘録になります。

※ 1はLookerのIP Allowlist^[2]で実現しています。
※ 2はGCPのVPC Service Controls^3で実現しています。

OAuth認証のデメリット

データベース接続をGCPサービスアカウントで認証しているとSQL実行者を特定出来ません。
OAuth認証に切り替えると個人の特定は可能になりますが、良いことばかりではありません。

主なデメリットは以下のポイントになります。

キャッシュがユーザごとになるため、他ユーザの実行したクエリキャッシュが効かなくなる。
永続的な派生テーブル（PDT）がサポートされていません。

以降は実施手順になります。

実行環境

Product	version
Looker	21.6.27
BigQuery	2021年6月9日時点
Cloud Logging	2021年6月9日時点

【構成図】

構成図

【補足】
・ LookerのAWS東京リージョンの接続元は構成図に記載のいずれかのIPアドレス^[3]になります。
・ VPC-SCにはLooker接続元IPとオフィスの送信元IPをアクセスレベルで許可しています。
・ BigQueryのSQLクエリログはデータアクセス監査ログ^[4]としてCloud Loggingに記録されます。

実施手順

下記手順でSQL実行したユーザを特定するためのLookを作成します。

OAuth認証の設定
BigQueryのデータセット作成
Cloud Loggingの集約シンク設定
LookMLの作成
Lookの作成

1. OAuth認証の設定

下記のLookerの公式ドキュメント通りに実施すれば設定することが出来ます。

以下、設定した内容になります。

【OAuth同意画面】

設定項目	値
ユーザの種類	内部
アプリ名	Looker
サポートメール	<自分のメールアドレス>
承認済みドメイン	looker.com
連絡先メールアドレス	<自分のメールアドレス>

【OAuthクライアントID】

設定項目	値
アプリケーションの種類	ウェブアプリケーション
名前	Looker
承認済みのJavaScript生成元	https://LookerのログインURL
承認済みのリダイレクトURI	https://LookerのログインURL/external_oauth/redirect

OAuth認証をセットし、接続テストでOKになれば設定完了です。

2. BigQueryのデータセット作成

GCPコンソールにログインし、BigQuery > SQLワークスペースを開きます。
Lookerが参照しているBigQueryプロジェクトでデータセットを作成を選択します。

東京リージョンでsecurity_logsというデータセットを作成します。(名前はなんでもOK)

データセットの作成

作成したデータセットに対して、Cloud Loggingのデータアクセス監査ログを出力します。

3. Cloud Loggingの集約シンク設定

集約シンク^[5]を設定し、Cloud LoggingのログをBigQueryのデータセットに出力します。

GCPコンソールのロギング > ログルータを開きます。
画面上部のシンクを作成するをクリックします。

シンク名とシンクの説明を好きな内容で入力します。

シンクの作成1

BigQueryデータセットを指定し、前手順で作成したデータセット(security_logs)を選択します。

シンクの作成2

包含フィルタにはBigQueryのログだけが出力されるように設定します。
(protoPayload.serviceName = bigquery.googleapis.com)

シンクの作成3

画面下部のシンクを作成を押すと作成され、一覧に表示されます。

集約シンクの作成完了

少し時間をおくとログが出力されてきます。
cloudaudit_googleapis_com_data_accessがデータアクセス監査ログ用のテーブルになります。
(cloudaudit_googleapis_com_activityは管理アクティビティ監査ログです)

監査ログ用のテーブル

監査ログの出力は以上です。

4. LookMLの作成

cloudaudit_googleapis_com_data_accessテーブルを利用して監査用グラフを作成します。

まず、Lookerにログインし、開発モードに切り替えます。
メニューのManage LookML Projectsを選択し
New LookML Projectで空のLookMLプロジェクトを作成します。

LookMLプロジェクトの作成

cloudaudit_googleapis_com_data_access.viewというViewファイルを作成します。

Viewファイルの作成

Viewファイルに以下の内容を書きます。

cloudaudit_googleapis_com_data_access.view

view: data_access {
  sql_table_name: `<プロジェクト名>.security_logs.cloudaudit_googleapis_com_data_access`
    ;;

  dimension: principal_email {
    type: string
    sql: ${TABLE}.protopayload_auditlog.authenticationInfo.principalEmail ;;
    label: "PrincipalEmail"
  }

  dimension: caller_ip {
    type: string
    sql: ${TABLE}.protopayload_auditlog.requestMetadata.callerIp ;;
    label: "CallerIP"
  }

  dimension: sql_query {
    type: string
    sql: ${TABLE}.protopayload_auditlog.servicedata_v1_bigquery.jobCompletedEvent.job.jobConfiguration.query.query ;;
    label: "SQLQuery"
  }

  dimension: resource_type {
    type: string
    hidden: yes
    sql: ${TABLE}.resource.type ;;
    label: "ResourceType"
  }

  dimension_group: timestamp {
    type: time
    timeframes: [
      raw,
      time,
      date,
      week,
      month,
      quarter,
      year
    ]
    sql: ${TABLE}.timestamp ;;
  }
}

【解説】
・ protopayload_auditlog.authenticationInfo.principalEmailは
　 BigQueryにSQLクエリを実行したGCPのIAMユーザのアカウントになります。
　 (誰が実行したのかを把握するために必要となります)
・ protopayload_auditlog.requestMetadata.callerIpは
　 BigQueryにSQLクエリを実行したIAMユーザの接続元グローバルIPになります。
　 (基本的にはLookerの接続元IPになります)
・ protopayload_auditlog.servicedata_v1_bigquery.jobCompletedEvent.
　 job.jobConfiguration.query.queryはLookerから発呼されたSQL文が記録されます。
　 (何を実行したのかを把握するために必要となります)
・ resource.typeはGCPのサービスリソース種別になります。
　 (今回はBigQueryに関するサービスに絞るためだけに利用します^[6])
・ timestampはBigQueryにSQLクエリを実行した日時になります。
　 (いつ実行されたのかを把握するために必要になります)

【補足】
・ Cloud Loggingのデータアクセス監査ログをBigQueryのテーブルに出力すると
　 8階層のネスト構造のフィールドが計485個のスキーマになります。
　その中から必要な値の含まれるフィールドを見つけるのが大変です。。
・以下が8階層のネストされたフィールド名の例です。
　 protopayload_auditlog.servicedata_v1_biggquery.datasetInsertRequest.
　 resource.acl.entries.viewName.projectId

次はcloudaudit_googleapis_com_data_access.modelというModelファイルを作成します。

Modelファイルの作成

Modelファイルに以下の内容を書きます。

cloudaudit_googleapis_com_data_access.model

connection: "<コネクション名>"
label: "Cloud Logging"

include: "/*.view.lkml"

explore: data_access {
  sql_always_where: ${resource_type} Like "bigquery%"
  AND ${sql_query} LIKE "%Looker%";;
  always_filter: {
    filters: {
      field: principal_email
      value: ""
    }
    filters: {
      field: caller_ip
      value: "54.250.91.57,13.112.30.110,54.92.76.241"
    }
    filters: {
      field: timestamp_date
      value: ""
    }
  }
}

【解説】
・ resource_typeにbigqueryから始まるリソース名で絞りたいため
　 sql_always_whereでLIKE検索を実施しています。
・ Lookerが発呼したSQL文の先頭には-- Looker Query Contextという文字列が含まれます。
　 Lookerを含む文字列をsql_always_whereでLIKE検索を実施しています。
・ Exploreで誰が、どこから、いつ実行したSQLクエリなのかフィルタで
　絞り込みができるようにalways_filterで3つのフィールドを設定しています。
・フィルタのデフォルト値としてLookerの接続元IPだけに絞るように設定しています。

上記内容でLookMLを保存、コミット後、Productionにマージます。

5. Lookの作成

先程のLookMLで生成されたExplore > Data Accessを開きます。

Exploreの表示

左側のフィールド名の中からTimestampDate.Time、PrincpalEmail、CallerIP、SQLQueryの順に表示に利用するフィールド名を選択します。

ビジュアリゼーションの表示形式を一番左の表グラフに指定してSQLを実行します。

SQLクエリ実行結果のログ一覧

上記のような表になればOKです。
こちらをLookとして保存して、ダッシュボードにプロットすれば可視化は完了ですね。

まとめ

さて、いかがでしたでしょうか？

以前投稿させて頂いた以下の内容とセットで利用することでより深いレベルで
Lookerの利用状況が可視化することができそうですね。

いざ、何かあった時はフォレンジックとして利用することもあるかなと思っています。
皆様にとって何かヒントになる内容が含まれていることを願っています^^

【おまけ】 GCP Audit Log Analysisとは

Looker BlocksにGCP Audit Log AnalysisというGCPの監査ログを可視化できるものがあります。

今回インストールしてみましたが、こちらではBigQueryに対するSQLクエリの実行ログを
いい感じに可視化することができなかったので、本記事を投稿させて頂きました^^

GCP Audit Log Analysis

脚注