BigQuery で row-level permission を利用する方法（その２）

この記事では BigQuery の row-level permission を利用した、ユーザ単位で閲覧できる行を制限する方法について解説します。

単純にユーザによらない行単位で閲覧制限をする方法については、以下の記事を参照してください。

BigQuery で row-level permission を利用する方法（その１）

元記事

How do I give different users access to different rows without creating separate views in BigQuery?

CURRENT_USER 関数

BigQuery Other Functions

ユーザ単位でのアクセス制限は全て CURRENT_USER 関数の機能に依存しています。CURRENT_USER関数は現在クエリを実行しているユーザの email アドレスを返す関数です。試しに以下のクエリを Web UI で実行してみてください。あなたの email アドレスが表示されるはずです。

SELECT CURRENT_USER()

CURRENT_USER 関数を利用した Authorized View

下表のデータが格納された customers テーブルに対して、

customer:string	id:integer	allowed_viewer:string
c001	1	[e-mail]
c002	2	[e-mail]
c003	3	[e-mail]

次のクエリで Authorized View を作成します。

SELECT
  customer,
  id 
FROM
  [private.customers]
WHERE
  allowed_viewer = CURRENT_USER()

こうすると、[e-mail] というユーザに対しては、

customer:string	id:integer
c001	1
c003	3

という結果が、[e-mail] というユーザに対しては、

customer:string	id:integer
c002	2

というユーザごとに異なった結果が返りようになります。これがユーザ単位でのアクセス制限の基本形です。

グループ単位で閲覧制御をする

上記の方法では、1つの行につき、1つのユーザに対してしか閲覧を許可することができません。しかし、実運用上では複数ユーザやグループ単位でアクセス制御をしたいことが多々あります。これに対する解決策として最も単純なものは「allowed_viewer 列を REPEATED フィールドとする」ことですが、この方法ではデータの更新ができない BigQuery で後からユーザを追加したいというケースに対応できません。

より実用的な方法としては、新しくグループ情報を格納する access_control テーブルを作成し、このテーブルと JOIN することでより柔軟なアクセス制御を実現します。

例

アクセス制御したい private データセットのテーブル customers に対して、allowed_group 列を追加します。以降の説明では、下表のデータが入っているとします。

customer:string	id:integer	allowed_group:string
c001	1	admin
c002	2	sales
c003	3	engineers

次にグループ情報を管理する access_control テーブルを同じ private データセット内に作成します。データは以下のものとが入っているとします。

group:string	user_name:string
admin	[e-mail]
sales	[e-mail]
engineers	[e-mail]
engineers	[e-mail]

これら2つのテーブルを利用して、公開する public データセットに、以下のクエリを持つ public_customers という名前の Authorized View を作成します。

SELECT
  c.customer AS customer,
  c.id AS id
FROM
  [private.customers] c
INNER JOIN (
  SELECT
    group 
  FROM
    [private.access_control]
  WHERE
    CURRENT_USER() = user_name
) g
ON
  c.allowed_group = g.group

これで、[e-mail] で SELECT * FROM [public.public_customers] というクエリを実行すると、

customer:string	id:integer
c001	1
c003	3

が、[e-mail] だと

customer:string	id:integer
c002	2

が、[e-mail] だと

customer:string	id:integer
c003	3

という結果が返るようになります。これで、グループ単位でのアクセス制御が可能になります。

補足

グループが他のグループを含められるようにする

元記事では文章で簡単に触れられているだけの、グループが他のグループを含められるようにする方法について、自分なりに考えてみました。

group:string	user_name:string
admin	[e-mail]
sales	[e-mail]
engineers	admin
engineers	[e-mail]

という enginners グループに admin グループが含まれるデータに対して、以下の様なビューを作ります。access_control テーブルを self join しているところがポイントです。

SELECT
  c.customer AS customer,
  c.id AS id
FROM
  [private.customers] c
INNER JOIN (
  SELECT
    group
  FROM (
    SELECT
      group
    FROM
      [private.access_control]
    WHERE
      CURRENT_USER() = user_name
  ), (
    SELECT
      r.group AS group
    FROM
      [private.access_control] l
    LEFT OUTER JOIN EACH
      [private.access_control] r
    ON
      l.group = r.user_name
    WHERE
      CURRENT_USER() = l.user_name
  )
) g
ON
  c.allowed_group = g.group

もう少し綺麗／効率よく書けるよってツッコミ歓迎です。

access_control テーブルの更新について

元記事の方には書いてないですが、BigQuery は行の UPDATE/DELETE ができないので、access_control テーブルのデータの更新には、テーブルの再作成が必要になります。ですので、元データは別途何らかのファイルか、別のデータベースに入れておくようにしましょう。