BigQueryの行レベルのアクセスポリシーを試してみる

BigQueryの行レベルのアクセスポリシーを試してみる

Clock Icon2024.09.11

はじめに

データアナリティクス事業本部のkobayashiです。

BigQueryでは行レベルのアクセスコントロールを行うことが可能です。行レベルのアクセスコントロールではユーザーに基づいてレコードのフィルタリングを行えます。今回はこの機能を試してみたのでまとめます。

https://cloud.google.com/bigquery/docs/row-level-security-intro?hl=ja

BigQueryの行のレベルアクセスコントロール

行レベルのセキュリティは、BigQueryテーブル内の特定の行へのアクセスをユーザーの資格条件に基づいて制御する機能です。これにより、1つのテーブル内でもユーザーごとに閲覧可能なデータを細かく制限できます。

アクセスコントロールはテーブルに対して行レベルのアクセスポリシーを作成します。このポリシーは、特定のユーザーやグループに対して条件に合致する行のみを表示するフィルターとして機能し、他のレベルのアクセス制御(プロジェクト、データセット、テーブル、列レベル)と併せて使うことも可能です。

BigQueryの列レベルのアクセスコントロールを試してみる

下準備

それでは実際に行レベルのアクセスコントロールを試してみたいと思います。
対象となるテーブルはBigQuery一般公開データセットの Google トレンド データセット のinternational_top_termsテーブルを使います。

CREATE TABLE `{プロジェクトID}`.access_control.international_top_terms
AS
SELECT *
FROM `bigquery-public-data.google_trends.international_top_terms`
refresh_date region_name region_code score rank country_name country_code term week
2024-07-23 Hokkaido Prefecture JP-01 21 24 Japan JP 東京ガス 2024-07-07
2024-07-23 Hokkaido Prefecture JP-01 67 19 Japan JP アシックス 2024-07-07
2024-08-19 Hokkaido Prefecture JP-01 6 2 Japan JP 和田アキ子 2024-07-07
2024-07-27 Hokkaido Prefecture JP-01 32 16 Japan JP LIFE 2024-07-07
2024-08-08 Hokkaido Prefecture JP-01 19 17 Japan JP 日経平均 2024-07-07

行レベルのアクセスポリシーを作成

このテーブルに対して行レベルのアクセスポリシーを作成していきます。

CREATE ROW ACCESS POLICY country_jp_filter
    ON access_control.international_top_terms
    GRANT TO ('serviceAccount:bigquery-1@{ プロジェクト ID }.iam.gserviceaccount.com')
    FILTER USING (country_code = 'JP');

行レベルのアクセスポリシーはCREATE ROW ACCESS POLICY statementで作成します。

  • アクセスポリシー名: テーブルでユニークにする必要がある
  • GRANT TO: 行レベルのアクセスポリシーを適用するメンバーを指定する。指定できるのはuser,serviceAccount,group,domain,allAuthenticatedUsers,allUsersで、複数指定する場合はカンマ,で区切る
  • FILTER USING: SQLのWHERE句で使う表現(一部制限あり)を記述する

CREATE ROW ACCESS POLICY DDL ステートメント リファレンス

ではこの状態でクエリを実行してみます。

> SELECT country_name, country_code, count(*) as cnt
FROM access_control.international_top_terms
GROUP BY 1, 2;

| country_name | country_code | cnt |
| :--- | :--- | :--- |
| Japan | JP | 155147 |

行レベルのアクセスポリシーが適用されてcountry_code = 'JP'のレコードだけしか取得できなくなっています。

行レベルのアクセスポリシーはAllowリストなのでテーブルに行レベルのアクセスポリシーが設定されるとアクセスポリシーに設定されていないユーザーは一切レコードが取得できなくなるため管理者など全てのレコードを読み取る必要のあるユーザーにはそのポリシーを作成する必要があります。

CREATE ROW ACCESS POLICY admin_filter
    ON access_control.international_top_terms
    GRANT TO (
    'user:[email protected]',
    'serviceAccount:bigquery-admin@{ プロジェクト ID }.iam.gserviceaccount.com'
    )
    FILTER USING (TRUE);

複数のポリシーを組み合わせてみる

同じメンバーに別のポリシーを作成して動作を確認してみます。

CREATE ROW ACCESS POLICY score8090_filter
    ON access_control.international_top_terms
    GRANT TO ('serviceAccount:bigquery-1@{ プロジェクト ID }.iam.gserviceaccount.com')
    FILTER USING (score BETWEEN 80 AND 90);

ではこの状態でクエリを実行してみます。

> SELECT country_name, country_code, count(*) as cnt
FROM access_control.international_top_terms
GROUP BY 1, 2;

| country\_name | country\_code | cnt |
| :--- | :--- | :--- |
| Argentina | AR | 693 |
| Austria | AT | 261 |
| France | FR | 869 |
| Israel | IL | 194 |
| Romania | RO | 854 |
| Sweden | SE | 732 |
| Australia | AU | 250 |
| Japan | JP | 155147 |
| Brazil | BR | 969 |
| Canada | CA | 360 |
| Switzerland | CH | 397 |
...                         

このように同じメンバーに複数のポリシーを作成するといずれかのポリシーのFilter条件に一致するレコードが全て取得できます。

全ての条件に一致するレコードのみにアクセスさせたい場合は以下のように1つのポリシーに条件をまとめて記述して作成する必要があります。

CREATE ROW ACCESS POLICY and_filter
    ON access_control.international_top_terms
    GRANT TO ('serviceAccount:bigquery-2@{ プロジェクト ID }.iam.gserviceaccount.com')
    FILTER USING (country_code = 'JP' AND score BETWEEN 80 AND 90);

この状態でbigquery-2@{ プロジェクト ID }.iam.gserviceaccount.comとしてクエリを実行してみます。

> SELECT country_name, country_code, count(*) as cnt
FROM access_control.international_top_terms
GROUP BY 1, 2;

| country\_name | country\_code | cnt |
| :--- | :--- | :--- |
| Japan | JP | 1991 |                      

このように取得できるレコードが制限されていることがわかります。

行レベルのアクセスポリシー一覧

テーブルに作成されている行レベルのアクセスポリシーを確認するにはshellでbqコマンドを実行する必要があります。クエリでの取得はできないため注意が必要です。

$ bq ls --row_access_policies access_control.international_top_terms
          Id                          Filter Predicate                                                                    Grantees                                                     Creation Time    Last Modified Time  
 -------------------- ------------------------------------------------- ------------------------------------------------------------------------------------------------------------ ----------------- -------------------- 
  country_jp_filter    country_code = 'JP'                               serviceAccount:bigquery-1@{ プロジェクト ID }.iam.gserviceaccount.com                                         09 Sep 04:19:09   09 Sep 04:19:09     
  admin_filter         TRUE                                              user:[email protected], serviceAccount:bigquery-admin@{プ ロジェクト ID }.iam.gserviceaccount.com              09 Sep 04:20:11   09 Sep 04:20:11     
  score8090_filter     score BETWEEN 80 AND 90                           serviceAccount:bigquery-1@{ プロジェクト ID }.iam.gserviceaccount.com                                         09 Sep 04:21:01   09 Sep 04:21:01     
  and_filter           country_code = 'JP' AND score BETWEEN 80 AND 90   serviceAccount:bigquery-2@{ プロジェクト ID }.iam.gserviceaccount.com                                         09 Sep 04:23:26   09 Sep 04:23:26     

行レベルのアクセスポリシーの削除

行レベルのアクセスポリシーの削除をしてみます。

特定の行レベルのアクセスポリシーを削除するにはDROP ROWを実行します。

DROP ROW ACCESS POLICY us_filter ON access_control.international_top_terms;

テーブルの全ての行レベルのアクセスポリシーを削除するにはDROP ALL ROWを実行します。

DROP ALL ROW ACCESS POLICIES ON access_control.international_top_terms;

まとめ

BigQueryのアクセス制御の一つである行レベルのアクセスコントロールを試してみました。ポリシーをSQLで記述できアクセスできるレコードの制御は通常のSQLのWhere句とほぼ同じ表現が使えるので非常に設定しやすいです。

最後まで読んで頂いてありがとうございました。

Share this article

facebook logohatena logotwitter logo

© Classmethod, Inc. All rights reserved.