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

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

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

[BigQuery で row-level permission を利用する方法（その１）](http://qiita.com/hakobera/items/1d814a6e666823f17125)

## 元記事

[How do I give different users access to different rows without creating separate views in BigQuery?](http://stackoverflow.com/questions/29683423/how-do-i-give-different-users-access-to-different-rows-without-creating-separate)

## CURRENT_USER 関数

[BigQuery Other Functions](https://cloud.google.com/bigquery/query-reference#otherfunctions)

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

```sql
SELECT CURRENT_USER()
```

## CURRENT_USER 関数を利用した Authorized View

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

| customer:string | id:integer | allowed_viewer:string |
|-----------------|------------|-------------------|
| c001            | 1          | me@example.com    |
| c002            | 2          | you@example.com   |
| c003            | 3          | me@example.com    |

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

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

こうすると、`me@example.com` というユーザに対しては、

| customer:string | id:integer |
|-----------------|------------|
| c001            | 1          |
| c003            | 3          |

という結果が、`you@example.com` というユーザに対しては、

| 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        | admin@example.com     |
| sales        | sales@example.com     |
| engineers    | admin@example.com     |
| engineers    | engineer@example.com  |

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

```sql
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
```

これで、`admin@example.com` で `SELECT * FROM [public.public_customers] ` というクエリを実行すると、

| customer:string | id:integer |
|-----------------|------------|
| c001            | 1          |
| c003            | 3          |

が、`sales@example.com` だと

| customer:string | id:integer |
|-----------------|------------|
| c002            | 2          |

が、`engineer@example.com` だと

| customer:string | id:integer |
|-----------------|------------|
| c003            | 3          |

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

## 補足

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

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

| group:string | user_name:string      |
|--------------|-----------------------|
| admin        | admin@example.com     |
| sales        | sales@example.com     |
| engineers    | admin                 |
| engineers    | engineer@example.com  |

という `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` テーブルのデータの更新には、テーブルの再作成が必要になります。ですので、元データは別途何らかのファイルか、別のデータベースに入れておくようにしましょう。