BigQueryの行レベルセキュリティを活用してユーザ別データ表示を実践してみた

こんにちはkiyonoエンジニアです。
絶賛減量中です。

ユーザーによっては、自社のデータは見えるようにしたい、自部署のデータは見えるようにして他部署に見えないようにしたい等のケースがあるかと思います。

例えば以下のようなデータがあって、A社のユーザには他社のユーザ情報を見えないようにしてほしいといったケースです。

user	company
user1	A社
user2	A社
user3	B社
user4	C社

方法として、会社ごとにviewを作成して、Looker Studio等のBIツールで会社ごとにレポートを作成しても制限可能かと思います。しかし、社数が50~100のようにかなりの数ある場合、50以上のviewを作成し、viewを作成するとデータソース分のレポートを作成して…となるとかなり大変ですよね…

また、レポートを改修する際に、全社分のレポート修正が必要となると…気が遠くなりますよね…

この方法の厄介な部分は、社数分それぞれデータソースを作成しないといけないという点です。

そんな際に試していただきたいのが、BigQueryの行レベルセキュリティです。BigQueryの設定をするだけで簡単に一つのデータソースで、ユーザごとにデータを出しわけできます。具体的な方法は後述します。

行レベルセキュリティの設定方法
1. 設定に関する補足
2. 行レベルセキュリティに関する補足
終わりに

行レベルセキュリティの設定方法

設定方法について解説します。

-- スキーマ作成
CREATE SCHEMA bq_row_security
OPTIONS(
description = 'BigQuery行レベルセキュリティ',
location = 'asia-northeast1');


-- テーブルの作成
CREATE TABLE IF NOT EXISTS bq_row_security.user_table(
userSTRING,
companySTRING
);


-- テストデータの挿入
INSERT INTO bq_row_security.user_table(user, company)VALUES
('user1', 'A社'),
('user2', 'A社'),
('user3', 'B社'),
('user4', 'C社');


-- 行レベルセキュリティポリシーの作成
-- グループA
CREATE ROW ACCESS POLICY user_access_policy_group ON bq_row_security.user_table
GRANT TO("group:AAA@kiyono.com")
FILTER USING(company = 'A社');


-- 行レベルセキュリティポリシーの作成
-- グループB
CREATE ROW ACCESS POLICY user_access_policy_group ON bq_row_security.user_table
GRANT TO("group:BBB@kiyono.com")
FILTER USING(company = 'B社');


-- 行レベルセキュリティポリシーの作成
-- グループC
CREATE ROW ACCESS POLICY user_access_policy_group ON bq_row_security.user_table
GRANT TO("group:CCC@kiyono.com")
FILTER USING(company = 'C社');

上記が検証のためのサンプルコードになります。

「行レベルセキュリティポリシーの作成」の上部は検証のためのデータ準備で、行レベルセキュリティポリシーの作成が今回の行いたいことのサンプルコードになります。

サンプルコードのようにグループに対して権限付与できるため、部署毎や会社毎のような権限制御が可能になります。

実際に上記コマンドをBigQuery Studioで実行した上で、Looker Studio上でどのように見えるか確認してみましょう。著者が「group:AAA@kiyono.com」に属する場合に以下のように、A社のデータのみ表示されていることがわかります。ぜひご自身の環境下でお試しくださいませ。

設定に関する補足

上記サンプルコード各グループアカウントに対してBigQueryのジョブユーザと閲覧権限をあらかじめ付与しておく必要があります。
上記サンプルコードを実行する際は、BigQueryの処理を行うリージョンを「

asia-northeast1」に設定しておく必要があります。
- BigQuery Studioで展開 > クエリ設定 > 詳細オプション > より自動ロケーション選択のチェックを外す > リージョン選択の順で処理を行うリージョンを指定できます。
可視化のために使用するBIはLooker Studioをご使用願います。（その他BIツールは未検証）