サービスアカウントで BigQuery にアクセスするときに、どのロールでどんな操作が可能なのか確認してみた。

サービスアカウントで BigQuery にアクセスするときに、どのロールでどんな操作が可能なのか確認してみた。

#Python Client for Google BigQuery
#Google BigQuery
#Google Cloud (GCP)
みかみ

みかみ

facebook logohatena logotwitter logo
Clock Icon2020.05.16

この記事は公開されてから1年以上経過しています。情報が古い可能性がありますので、ご注意ください。

こんにちは、みかみです。

やりたいこと

  • BigQuery の事前定義ロールにはどんな種類があるか知りたい
  • 各ロールでどんな操作ができるのか知りたい
  • BigQuery Python クライアントライブラリを使用する場合に、各ロールで実行可能な処理を確認したい

目次

GCP の権限管理

GCE や GCS、BigQuery などの GCP の各リソースへのアクセス権限はロール（役割）の単位で管理され、ユーザーやサービスアカウントなどに必要なロールを付与することで、各リソースに対する操作を制御できます。

必要な権限を持つカスタムロールを作成することも可能ですが、操作ごとに必要な権限を付与した事前定義ロールも準備されています。

アプリケーションなどからクライアントライブラリを使って API 経由で GCP にアクセスする場合は、サービスアカウントを使用します。

サービスアカウントの権限は、サービスアカウントに付与したロールで制御可能です。

ロールはユーザーやサービスアカウントなどの操作元に付与する以外に、BigQuery データセットなどのリソース側に付与することもできますが、今回はサービスアカウント側に各ロールを付与し、Python クライアントライブラリ経由でロールごとにどんな操作が可能なのか確認します。

BigQuery の事前定義ロールを付与したサービスアカウントを作成

BigQuery 関連では以下の定義済みロールがあります。

  • BigQuery 管理者
  • BigQuery データ編集者
  • BigQuery データオーナー
  • BigQuery データ閲覧者
  • BigQuery ジョブユーザー
  • BigQuery メタデータ閲覧者
  • BigQuery 読み取りセッション ユーザー
  • BigQuery ユーザー
  • BigQuery リソース管理者（ベータ版）
  • BigQuery Connection 管理者（ベータ版）
  • BigQuery Connection ユーザー（ベータ版）

上記の各ロールを付与したサービスアカウントを作成しました。

なお、サービスアカウントへのロール付与は、gcloud CLI でも実行できます。

GCP管理画面からのUI操作では BigQuery Connection 管理者と BigQuery Connection ユーザーのロールが選択できなかったので（ベータ版のためでしょうか？）、以下の gcloud CLI で付与しました。

gcloud projects add-iam-policy-binding cm-da-mikami-yuki-258308 \
        --member serviceAccount:bq-connection-admin@cm-da-mikami-yuki-258308.iam.gserviceaccount.com \
        --role roles/bigquery.connectionAdmin

gcloud projects add-iam-policy-binding cm-da-mikami-yuki-258308 \
        --member serviceAccount:bq-connection-user@cm-da-mikami-yuki-258308.iam.gserviceaccount.com \
        --role roles/bigquery.connectionUser

Python クライアントライブラリ経由で BigQuery を操作

BigQuery Python クライアントライブラリを使用して、データセットやテーブルなどのメタデータの参照や編集、テーブルデータの参照や編集、ジョブの参照や実行の操作が可能です。

プロジェクト関連操作

以下で、プロジェク関連の操作を確認してみます。

  • list_projects ： プロジェクト一覧参照

以下の Python コードを、各ロールを付与したサービスアカウントで実行してみました。

from google.cloud import bigquery
from google.oauth2 import service_account
import argparse
import os.path
from pprint import pprint

parser = argparse.ArgumentParser(description='project')
parser.add_argument('file', help='account key file')
args = parser.parse_args()
key_path = os.path.join(os.path.dirname(os.path.abspath(__file__)), args.file)
credentials = service_account.Credentials.from_service_account_file(
    key_path,
    scopes=["https://www.googleapis.com/auth/cloud-platform"],
)
client = bigquery.Client(
    credentials=credentials,
    project=credentials.project_id,
)

# get
projects = client.list_projects()
if projects:
    for obj in projects:
        print('-------->')
        pprint(vars(obj))
else:
    print("get projects failed...")

結果、BigQuery Connection 管理者と BigQuery Connection ユーザーを除くすべてのロールで、実行したサービスアカウントが所属するプロジェクトの情報を参照できました。

(test_role) [ec2-user@ip-10-0-43-239 test_role]$ python project.py keys/bq-admin.json
-------->
{'friendly_name': 'cm-da-mikami-yuki',
 'numeric_id': '797147019523',
 'project_id': 'cm-da-mikami-yuki-258308'}

データセット関連操作

Python クライアントライブラリのデータセット関連操作は以下です。

  • list_datasets ： データセット一覧参照
  • get_dataset ： データセット情報参照
  • create_dataset ： データセット作成
  • update_dataset ： データセット更新
  • delete_dataset ： データセット削除

以下の Python コードを、各サービスアカウントで実行しました。

（省略）
# get
datasets = client.list_datasets(credentials.project_id)
if datasets:
    for obj in datasets:
        print('-------->')
        pprint(vars(obj))
        dataset = client.get_dataset(obj.reference)
        print('\t-------->')
        pprint(vars(dataset))
else:
    print("get datasets failed...")

# create
dataset_id = '{}.{}_new'.format(credentials.project_id, dataset.dataset_id)
dataset = bigquery.Dataset(dataset_id)
dataset.location = "asia-northeast1"
dataset = client.create_dataset(dataset)
print("Created dataset {}.{}".format(dataset.project, dataset.dataset_id))

# update
dataset.description = 'ロール確認用'
dataset = client.update_dataset(dataset, ['description'])
print("Updated dataset description: {}".format(dataset.description))

# delete
client.delete_dataset(dataset)
print("Deleted dataset {}.{}".format(dataset.project, dataset.dataset_id))

(test_role) [ec2-user@ip-10-0-43-239 test_role]$ python dataset.py keys/bq-admin.json
-------->
{'_properties': {'datasetReference': {'datasetId': 'airflow_test',
                                      'projectId': 'cm-da-mikami-yuki-258308'},
                 'id': 'cm-da-mikami-yuki-258308:airflow_test',
                 'kind': 'bigquery#dataset',
                 'location': 'US'}}
        -------->
{'_properties': {'access': [{'role': 'WRITER',
                             'specialGroup': 'projectWriters'},
                            {'role': 'OWNER', 'specialGroup': 'projectOwners'},
                            {'role': 'OWNER',
                             'userByEmail': 'gcp-da-user@classmethod.jp'},
                            {'role': 'READER',
                             'specialGroup': 'projectReaders'}],
                 'creationTime': '1584102236040',
                 'datasetReference': {'datasetId': 'airflow_test',
                                      'projectId': 'cm-da-mikami-yuki-258308'},
                 'etag': 'edTJ2rusjiEw4s1O+6YhcQ==',
                 'id': 'cm-da-mikami-yuki-258308:airflow_test',
                 'kind': 'bigquery#dataset',
                 'lastModifiedTime': '1584102236040',
                 'location': 'US',
                 'selfLink': 'https://bigquery.googleapis.com/bigquery/v2/projects/cm-da-mikami-yuki-258308/datasets/airflow_test'}}
（省略）
Created dataset cm-da-mikami-yuki-258308.test_dataset_option_all_new
Updated dataset description: ロール確認用
Deleted dataset cm-da-mikami-yuki-258308.test_dataset_option_all_new

BigQuery 管理者、BigQuery データオーナー、BigQuery データ編集者、BigQuery ユーザーのロールでは全ての操作が可能で、BigQuery データ閲覧者と BigQuery メタデータ閲覧者のロールでは参照操作のみが可能でした。

テーブル関連操作

以下、Python クライアントライブラリのテーブル関連操作を確認しました。

  • list_tables ： テーブル一覧参照
  • get_table ： テーブル情報参照
  • create_table ： テーブル作成
  • update_table ： テーブル更新
  • delete_table ： テーブル削除

Python コードは以下です。

（省略）
# get
dataset_id = '{}.dataset_1'.format(credentials.project_id)
dataset = client.get_dataset(dataset_id)
tables = client.list_tables(dataset)
if tables:
    for obj in tables:
        print('-------->')
        pprint(vars(obj))
        table = client.get_table(obj.reference)
        print('\t-------->')
        pprint(vars(table))
        if table.table_type == 'TABLE' and table.num_rows > 0:
            base_reference = obj.reference
            base_table = table
else:
    print("get tables failed...")

# create
table_id = "{}.{}.{}_new".format(base_table.project, base_table.dataset_id, base_table.table_id)
table = bigquery.Table(table_id, schema=base_table.schema)
table = client.create_table(table)
print("Created table {}.{}.{}".format(table.project, table.dataset_id, table.table_id))

# update
table.description = 'ロール確認用'
table = client.update_table(table, ['description'])
print("Updated table description: {}".format(table.description))

# delete
client.delete_table(table)
print("Deleted table {}.{}.{}".format(table.project, table.dataset_id, table.table_id))

(test_role) [ec2-user@ip-10-0-43-239 test_role]$ python table.py keys/bq-admin.json
-------->
{'_properties': {'creationTime': '1589454521978',
                 'id': 'cm-da-mikami-yuki-258308:dataset_1.data_sample',
                 'kind': 'bigquery#table',
                 'tableReference': {'datasetId': 'dataset_1',
                                    'projectId': 'cm-da-mikami-yuki-258308',
                                    'tableId': 'data_sample'},
                 'type': 'TABLE'}}
        -------->
{'_properties': {'creationTime': '1589454521978',
                 'etag': 'ryho1wwFzRd/BvpbS0t3Ng==',
                 'id': 'cm-da-mikami-yuki-258308:dataset_1.data_sample',
                 'kind': 'bigquery#table',
                 'lastModifiedTime': '1589454522041',
                 'location': 'asia-northeast1',
                 'numBytes': '0',
                 'numLongTermBytes': '0',
                 'numRows': '0',
                 'schema': {'fields': [{'mode': 'NULLABLE',
                                        'name': 'col_1',
                                        'type': 'INTEGER'},
                                       {'mode': 'NULLABLE',
                                        'name': 'col_2',
                                        'type': 'STRING'}]},
                 'selfLink': 'https://bigquery.googleapis.com/bigquery/v2/projects/cm-da-mikami-yuki-258308/datasets/dataset_1/tables/data_sample',
                 'tableReference': {'datasetId': 'dataset_1',
                                    'projectId': 'cm-da-mikami-yuki-258308',
                                    'tableId': 'data_sample'},
                 'type': 'TABLE'}}
（省略）
Created table cm-da-mikami-yuki-258308.dataset_1.table_dogs_copy_new
Updated table description: ロール確認用
Deleted table cm-da-mikami-yuki-258308.dataset_1.table_dogs_copy_new

データセット同様、BigQuery 管理者、BigQuery データオーナー、BigQuery データ編集者では全ての操作が可能、BigQuery データ閲覧者と BigQuery メタデータ閲覧者のロールでは参照操作のみ可能でした。

BigQuery ユーザーのロールでは、データセットでは全ての操作が可能でしたが、テーブルでは一覧参照以外の操作はできませんでした。

テーブルデータ関連操作

以下の Python クライアントライブラリの実行結果を確認しました。

  • list_partitions ： パーティション参照
  • list_rows ： テーブルデータ参照
  • insert_rows ： テーブルデータ追加

まずはパーティションを参照してみます。

（省略）
# get
table_id = '{}.dataset_1.pos_partition_loadtime_with_filter'.format(credentials.project_id)
table = client.get_table(table_id)
partitions = client.list_partitions(table)
print(partitions)

(test_role) [ec2-user@ip-10-0-43-239 test_role]$ python partition.py keys/bq-admin.json
['20200412', '20200413', '20200414']

BigQuery 管理者、BigQuery データオーナー、BigQuery データ編集者、BigQuery データ閲覧者で参照が可能ですが、他のロールでは参照できませんでした。

続いてテーブルに格納済みのデータの参照と追加を実行してみます。

（省略）
# get
table_id = '{}.dataset_1.table_dogs_copy'.format(credentials.project_id)
table = client.get_table(table_id)
rows = client.list_rows(table)
if rows:
    for obj in rows:
        print('-------->')
        print(obj)
else:
    print("get rows failed...")

# insert
val = [(obj[0]+1, obj[1])]
ret = client.insert_rows(table, val)
print("Inserted row {}".format(val))

(test_role) [ec2-user@ip-10-0-43-239 test_role]$ python row.py keys/bq-admin.json
-------->
Row((1, 'シェパード'), {'id': 0, 'name': 1})
-------->
Row((2, 'シベリアンハスキー'), {'id': 0, 'name': 1})
-------->
Row((3, '秋田犬'), {'id': 0, 'name': 1})
Inserted row [(4, '秋田犬')]

BigQuery 管理者、BigQuery データオーナー、BigQuery データ編集者では参照と追加、両方の操作が可能で、BigQuery データ閲覧者では参照のみ可能でした。

ルーティン関連操作

UDF やストアドプロシージャ関連の Python クライアントライブラリ操作です。

  • list_routines ： ルーティン一覧参照
  • get_routine ： ルーティン情報参照
  • create_routine ： ルーティン作成
  • update_routine ： ルーティン更新
  • delete_routine ： ルーティン削除

以下の Python コードを実行します。

（省略）
# get
dataset_id = '{}.dataset_1'.format(credentials.project_id)
dataset = client.get_dataset(dataset_id)
routines = client.list_routines(dataset)
if routines:
    for obj in routines:
        print('-------->')
        pprint(vars(obj))
        routine = client.get_routine(obj.reference)
        print('\t-------->')
        pprint(vars(routine))
        if routine.type_ == 'PROCEDURE':
            base_routine = routine
else:
    print("get routines failed...")

# create
routine_id = "{}.{}.{}_new".format(base_routine.project, base_routine.dataset_id, base_routine.routine_id)
routine = bigquery.Routine(routine_id)
routine.type_ = base_routine.type_
routine.body = base_routine.body
routine = client.create_routine(routine)
print("Created routine {}.{}.{}".format(routine.project, routine.dataset_id, routine.routine_id))

# update
routine.description = 'ロール確認用'
routine = client.update_routine(routine, ['description', 'type_'])
print("Updated routine description: {}".format(routine.description))

# delete
client.delete_routine(routine)
print("Deleted routine {}.{}.{}".format(routine.project, routine.dataset_id, routine.routine_id))

(test_role) [ec2-user@ip-10-0-43-239 test_role]$ python routine.py keys/bq-admin.json
-------->
{'_properties': {'creationTime': '1586493860018',
                 'etag': 'VEQYz7nQPL0ZPytgfdaOIA==',
                 'language': 'SQL',
                 'lastModifiedTime': '1586493860018',
                 'routineReference': {'datasetId': 'dataset_1',
                                      'projectId': 'cm-da-mikami-yuki-258308',
                                      'routineId': 'addFourAndDivideAny'},
                 'routineType': 'SCALAR_FUNCTION'}}
        -------->
{'_properties': {'arguments': [{'argumentKind': 'ANY_TYPE', 'name': 'x'},
                               {'argumentKind': 'ANY_TYPE', 'name': 'y'}],
                 'creationTime': '1586493860018',
                 'definitionBody': '(x + 4) / y',
                 'etag': 'VEQYz7nQPL0ZPytgfdaOIA==',
                 'language': 'SQL',
                 'lastModifiedTime': '1586493860018',
                 'routineReference': {'datasetId': 'dataset_1',
                                      'projectId': 'cm-da-mikami-yuki-258308',
                                      'routineId': 'addFourAndDivideAny'},
                 'routineType': 'SCALAR_FUNCTION'}}
（省略）
Created routine cm-da-mikami-yuki-258308.dataset_1.test_procedure_description_new
Updated routine description: ロール確認用
Deleted routine cm-da-mikami-yuki-258308.dataset_1.test_procedure_description_new

テーブル同様、BigQuery 管理者、BigQuery データオーナー、BigQuery データ編集者では全ての操作が可能、BigQuery データ閲覧者と BigQuery メタデータ閲覧者のロールでは参照操作のみ可能で、BigQuery ユーザーでは一覧参照操作のみ可能でした。

モデル関連操作

BigQuery ML では、BigQuery に SQL でモデルを作成して機械学習を実行できます。

以下の Python クライアントライブラリで、機械学習モデルの参照や更新、削除ができます。

  • list_models ： モデル一覧参照
  • get_model ： モデル情報参照
  • update_model ： モデル更新
  • delete_model ： モデル削除

Python コードは以下です。

（省略）
# get
dataset_id = '{}.bqml_tutorial'.format(credentials.project_id)
dataset = client.get_dataset(dataset_id)
models = client.list_models(dataset)
if models:
    for obj in models:
        print('-------->')
        pprint(vars(obj))
        model = client.get_model(obj.reference)
        print('\t-------->')
        pprint(vars(model))
else:
    print("get models failed...")

# update
model.description = 'ロール確認用'
model = client.update_model(model, ['description'])
print("Updated model description: {}".format(model.description))

# delete
client.delete_model(model)
print("Deleted model {}.{}.{}".format(model.project, model.dataset_id, model.model_id))

-------->
{'_properties': {'creationTime': '1589450016579',
                 'lastModifiedTime': '1589450016668',
                 'modelReference': {'datasetId': 'bqml_tutorial',
                                    'modelId': 'sample_model',
                                    'projectId': 'cm-da-mikami-yuki-258308'},
                 'modelType': 'LOGISTIC_REGRESSION'},
 '_proto': model_reference {
  project_id: "cm-da-mikami-yuki-258308"
  dataset_id: "bqml_tutorial"
  model_id: "sample_model"
}
creation_time: 1589450016579
last_modified_time: 1589450016668
model_type: LOGISTIC_REGRESSION
}
	-------->
{'_properties': {'creationTime': '1589450016579',
                 'etag': '3EY1057CKbfKYR3NtbhqpQ==',
                 'featureColumns': [{'name': 'os',
                                     'type': {'typeKind': 'STRING'}},
                                    {'name': 'is_mobile',
                                     'type': {'typeKind': 'BOOL'}},
                                    {'name': 'country',
                                     'type': {'typeKind': 'STRING'}},
                                    {'name': 'pageviews',
                                     'type': {'typeKind': 'INT64'}}],
                 'labelColumns': [{'name': 'predicted_label',
                                   'type': {'typeKind': 'INT64'}}],
                 'lastModifiedTime': '1589450016668',
                 'location': 'US',
                 'modelReference': {'datasetId': 'bqml_tutorial',
                                    'modelId': 'sample_model',
                                    'projectId': 'cm-da-mikami-yuki-258308'},
                 'modelType': 'LOGISTIC_REGRESSION',
                 'trainingRuns': [{'dataSplitResult': {'evaluationTable': {'datasetId': '_abeb5e381f230e69d687b164f0208db0ec2cfb0d',
                                                                           'projectId': 'cm-da-mikami-yuki-258308',
                                                                           'tableId': 'anon503af443_3ef9_4415_b78c_adedeb795abf_imported_data_split_eval_data'},
                                                       'trainingTable': {'datasetId': '_abeb5e381f230e69d687b164f0208db0ec2cfb0d',
                                                                         'projectId': 'cm-da-mikami-yuki-258308',
                                                                         'tableId': 'anon503af443_3ef9_4415_b78c_adedeb795abf_imported_data_split_training_data'}},
（省略）
location: "US"
}
Updated model description: ロール確認用
Deleted model cm-da-mikami-yuki-258308.bqml_tutorial.sample_model

こちらもテーブル同様、BigQuery 管理者、BigQuery データオーナー、BigQuery データ編集者では全ての操作が可能、BigQuery データ閲覧者と BigQuery メタデータ閲覧者のロールでは参照操作のみ可能で、BigQuery ユーザーでは一覧参照操作のみ可能でした。

ジョブ関連操作

最後にジョブ関連の操作です。

BigQuery では、データロードやエクスポート、SQL クエリ実行などの処理時間が長くなる可能性がある操作は、ジョブとして非同期で実行してくれます。

以下の Python クライアントライブラリが実行できるか確認しました。

  • list_jobs ： ジョブ一覧参照
  • query ： SQL クエリ実行
  • load_table_from_file ： ファイルデータロード
  • load_table_from_uri ： GCS データロード
  • copy_table ： テーブルコピー
  • extract_table ： テーブルデータを GCS にエクスポート

Python コードは以下です。

（省略）
# get
jobs = client.list_jobs(max_results=5)
if jobs:
    for obj in jobs:
        print('-------->')
        pprint(vars(obj))
else:
    print("get jobs failed...")

# exec query
query = (
    'SELECT name FROM `cm-da-mikami-yuki-258308.dataset_1.table_dogs`'
    'WHERE id = 3 ')
query_job = client.query(query)
rows = query_job.result()
for row in rows:
    print(row.name)
print("Selected data")


table_id = '{}.dataset_1.data_sample'.format(credentials.project_id)
table = bigquery.Table(table_id)

# load data
job_config = bigquery.LoadJobConfig()
job_config.source_format = bigquery.SourceFormat.CSV
job_config.skip_leading_rows = 1
job_config.autodetect = True
# from url
uri = "gs://test-mikami/data_sample.csv"
job = client.load_table_from_uri(uri, table, job_config=job_config)
print("\tStarting job {}".format(job.job_id))
job.result()
print("table: {} Loaded from uri.".format(table.table_id))
# from file
filename = os.path.join(os.path.dirname(os.path.abspath(__file__)), 'data_sample.csv')
table_id = '{}.dataset_1.data_sample'.format(credentials.project_id)
with open(filename, "rb") as source_file:
    job = client.load_table_from_file(source_file, table, job_config=job_config)
    print("\tStarting job {}".format(job.job_id))
job.result()
print("table: {} Loaded from file.".format(table.table_id))

# table copy
table_id_cp = '{}.dataset_1.{}_copy'.format(credentials.project_id, table.table_id)
table_cp = bigquery.Table(table_id_cp)
job = client.copy_table(table, table_cp)
print("\tStarting job {}".format(job.job_id))
job.result()
print("table: {} Copied.".format(table_cp.table_id))

# data extract
source_table_id = '{}.dataset_1.table_dogs'.format(credentials.project_id)
source_table = client.get_table(source_table_id)
destination_uris = ['gs://test-mikami/extract_{}'.format(source_table.table_id)]
job = client.extract_table(source_table.reference, destination_uris)
print("\tStarting job {}".format(job.job_id))
job.result()
print("extracted data to {}".format(destination_uris))

# delete tables
client.delete_table(table)
print("Deleted table {}.{}.{}".format(table.project, table.dataset_id, table.table_id))
client.delete_table(table_cp)
print("Deleted table {}.{}.{}".format(table_cp.project, table_cp.dataset_id, table_cp.table_id))

(test_role) [ec2-user@ip-10-0-43-239 test_role]$ python job.py keys/bq-admin.json
-------->
{'_client': <google.cloud.bigquery.client.Client object at 0x7f669527f890>,
 '_completion_lock': <unlocked _thread.lock object at 0x7f66952752a0>,
 '_configuration': <google.cloud.bigquery.job.ExtractJobConfig object at 0x7f66952af590>,
 '_done_callbacks': [],
 '_exception': Forbidden('Access Denied: BigQuery BigQuery: Permission denied while writing data.'),
 '_polling_thread': None,
 '_properties': {'configuration': {'extract': {'destinationUri': 'gs://test-mikami/extract_table_dogs',
                                               'destinationUris': ['gs://test-mikami/extract_table_dogs'],
                                               'sourceTable': {'datasetId': 'dataset_1',
                                                               'projectId': 'cm-da-mikami-yuki-258308',
                                                               'tableId': 'table_dogs'}},
                                   'jobType': 'EXTRACT'},
                 'errorResult': {'location': '/bigstore/test-mikami/extract_table_dogs',
                                 'message': 'Access Denied: BigQuery BigQuery: '
                                            'Permission denied while writing '
                                            'data.',
                                 'reason': 'accessDenied'},
                 'id': 'cm-da-mikami-yuki-258308:asia-northeast1.555c70e3-dc52-44d4-aab6-d88abeffe170',
                 'jobReference': {'jobId': '555c70e3-dc52-44d4-aab6-d88abeffe170',
                                  'location': 'asia-northeast1',
                                  'projectId': 'cm-da-mikami-yuki-258308'},
                 'kind': 'bigquery#job',
                 'state': 'DONE',
                 'statistics': {'creationTime': 1589544545275.0,
                                'endTime': 1589544546191.0,
                                'reservation_id': 'default-pipeline',
                                'startTime': 1589544545508.0},
                 'status': {'errorResult': {'location': '/bigstore/test-mikami/extract_table_dogs',
                                            'message': 'Access Denied: '
                                                       'BigQuery BigQuery: '
                                                       'Permission denied '
                                                       'while writing data.',
                                            'reason': 'accessDenied'},
                            'errors': [{'location': '/bigstore/test-mikami/extract_table_dogs',
                                        'message': 'Access Denied: BigQuery '
                                                   'BigQuery: Permission '
                                                   'denied while writing data.',
                                        'reason': 'accessDenied'}],
                            'state': 'DONE'},
                 'user_email': 'bq-admin@cm-da-mikami-yuki-258308.iam.gserviceaccount.com'},
 '_result': None,
 '_result_set': True,
 '_retry': <google.api_core.retry.Retry object at 0x7f66952ca690>,
 'destination_uris': ['gs://test-mikami/extract_table_dogs'],
 'source': TableReference(DatasetReference('cm-da-mikami-yuki-258308', 'dataset_1'), 'table_dogs')}
（省略）
秋田犬
Selected data
        Starting job 4459e4d4-e530-4039-96d2-e8932b542065
Traceback (most recent call last):
  File "job.py", line 58, in <module>
    job.result()
  File "/home/ec2-user/test_role/lib64/python3.7/site-packages/google/cloud/bigquery/job.py", line 818, in result
    return super(_AsyncJob, self).result(timeout=timeout)
  File "/home/ec2-user/test_role/lib64/python3.7/site-packages/google/api_core/future/polling.py", line 130, in result
    raise self._exception
google.api_core.exceptions.Forbidden: 403 Access Denied: File gs://test-mikami/data_sample.csv: Access Denied

BigQuery 管理者ロールでも、GCS からのデータロードでエラーになりました。

load_table_from_uri では GCS のオブジェクトを参照する必要があり、また extract_table は GCS にエクスポートデータの GCS オブジェクトを作成する必要があるため、GCS 関連のロールも付与する必要がありそうです。

Cloud Strage ストレージのオブジェクト作成者とストレージ オブジェクト閲覧者のロールを追加しました。

再度実行してみると、無事全てのジョブが実行できるようになりました。

(test_role) [ec2-user@ip-10-0-43-239 test_role]$ python job.py keys/bq-admin.json
（省略）
Selected data
        Starting job 00e94131-1d43-42e9-a51a-4e004723ef04
table: data_sample Loaded from uri.
        Starting job 144ed64b-1d42-4a8e-bd1a-1b489e802f59
table: data_sample Loaded from file.
        Starting job 4a659cd9-6da6-493f-a021-a23c86d5ae23
table: data_sample_copy Copied.
        Starting job 2cf82257-8deb-40da-bed2-a5e8d622e929
extracted data to ['gs://test-mikami/extract_table_dogs']
Deleted table cm-da-mikami-yuki-258308.dataset_1.data_sample
Deleted table cm-da-mikami-yuki-258308.dataset_1.data_sample_copy

BigQuery データオーナーのロールでは、これまでは各メタデータの参照や編集、データの編集など全ての操作が可能でしたが、ジョブ関連の操作は全て実行できませんでした。

(test_role) [ec2-user@ip-10-0-43-239 test_role]$ python job.py keys/bq-data-owner.json
Traceback (most recent call last):
  File "job.py", line 24, in <module>
    for obj in jobs:
  File "/home/ec2-user/test_role/lib64/python3.7/site-packages/google/api_core/page_iterator.py", line 212, in _items_iter
    for page in self._page_iter(increment=False):
  File "/home/ec2-user/test_role/lib64/python3.7/site-packages/google/api_core/page_iterator.py", line 243, in _page_iter
    page = self._next_page()
  File "/home/ec2-user/test_role/lib64/python3.7/site-packages/google/api_core/page_iterator.py", line 369, in _next_page
    response = self._get_next_page_response()
  File "/home/ec2-user/test_role/lib64/python3.7/site-packages/google/api_core/page_iterator.py", line 419, in _get_next_page_response
    method=self._HTTP_METHOD, path=self.path, query_params=params
  File "/home/ec2-user/test_role/lib64/python3.7/site-packages/google/cloud/bigquery/client.py", line 556, in _call_api
    return call()
  File "/home/ec2-user/test_role/lib64/python3.7/site-packages/google/api_core/retry.py", line 286, in retry_wrapped_func
    on_error=on_error,
  File "/home/ec2-user/test_role/lib64/python3.7/site-packages/google/api_core/retry.py", line 184, in retry_target
    return target()
  File "/home/ec2-user/test_role/lib64/python3.7/site-packages/google/cloud/_http.py", line 423, in api_request
    raise exceptions.from_http_response(response)
google.api_core.exceptions.Forbidden: 403 GET https://bigquery.googleapis.com/bigquery/v2/projects/cm-da-mikami-yuki-258308/jobs?maxResults=5&projection=full: Access Denied: Project cm-da-mikami-yuki-258308: User does not have bigquery.jobs.list permission in project cm-da-mikami-yuki-258308.

また、BigQuery ジョブユーザーロールでも、単体では全てのジョブ操作でエラーになってしまいました。BigQuery ジョブユーザーは、他の BigQuery ロールと組み合わせて使用するためのロールのようです。

BigQuery データオーナーのサービスアカウントに BigQuery ジョブユーザーと、Cloud Strage ストレージのオブジェクト作成者とストレージ オブジェクト閲覧者のロールを追加して再度実行してみると、list_jobs と extract_table 以外のジョブは実行できるようになりました。

(test_role) [ec2-user@ip-10-0-43-239 test_role]$ python job.py keys/bq-data-owner.json
秋田犬
Selected data
        Starting job 3d9069c5-b9ee-4ce8-8471-11a1b3ae60e9
table: data_sample Loaded from uri.
        Starting job f377ce1e-bcec-4f05-88d0-ccaa72ab8aa9
table: data_sample Loaded from file.
        Starting job fa10bbca-92c5-4c94-bbac-c0bf87798acd
table: data_sample_copy Copied.
        Starting job 249ffe5c-8e76-47ef-ba35-0f7a92c24313
Traceback (most recent call last):
  File "job.py", line 87, in <module>
    job.result()
  File "/home/ec2-user/test_role/lib64/python3.7/site-packages/google/cloud/bigquery/job.py", line 818, in result
    return super(_AsyncJob, self).result(timeout=timeout)
  File "/home/ec2-user/test_role/lib64/python3.7/site-packages/google/api_core/future/polling.py", line 130, in result
    raise self._exception
google.api_core.exceptions.Forbidden: 403 Access Denied: BigQuery BigQuery: Permission denied while writing data.

BigQuery ジョブユーザーのロールに付与された権限を確認してみると、bigquery.jobs.list や bigquery.jobs.get 権限が付与されていなかったため、ジョブの参照はできないようです。

また、BigQuery データオーナーと BigQuery 管理者のロールに付与されている権限を比較すると大分差分があるため、やはり BigQuery データオーナーロールでは可能な操作に制限があるようです。

許可されていない操作を実行した場合の挙動

権限がない操作を実行した場合、大抵は以下のようにパーミッションエラーメッセージが表示され、エラーメッセージの内容から不足している権限が推測できます。

(test_role) [ec2-user@ip-10-0-43-239 test_role]$ python dataset.py keys/bq-data-viewer.json
（省略）
Traceback (most recent call last):
  File "dataset.py", line 40, in <module>
    dataset = client.create_dataset(dataset)
  File "/home/ec2-user/test_role/lib64/python3.7/site-packages/google/cloud/bigquery/client.py", line 461, in create_dataset
    retry, method="POST", path=path, data=data, timeout=timeout
  File "/home/ec2-user/test_role/lib64/python3.7/site-packages/google/cloud/bigquery/client.py", line 556, in _call_api
    return call()
  File "/home/ec2-user/test_role/lib64/python3.7/site-packages/google/api_core/retry.py", line 286, in retry_wrapped_func
    on_error=on_error,
  File "/home/ec2-user/test_role/lib64/python3.7/site-packages/google/api_core/retry.py", line 184, in retry_target
    return target()
  File "/home/ec2-user/test_role/lib64/python3.7/site-packages/google/cloud/_http.py", line 423, in api_request
    raise exceptions.from_http_response(response)
google.api_core.exceptions.Forbidden: 403 POST https://bigquery.googleapis.com/bigquery/v2/projects/cm-da-mikami-yuki-258308/datasets: Access Denied: Project cm-da-mikami-yuki-258308: User does not have bigquery.datasets.create permission in project cm-da-mikami-yuki-258308.

ですが、例えばBigQuery ジョブユーザーロールでデータセット一覧を参照した場合など、一部の操作ではエラーにはならず、空データが返却される挙動が確認できました。

特にカスタムロールを利用する場合など、どのような挙動になるか事前に動作確認してみるとよいかと思います。

ロールごとに実行可能な操作一覧

各定義済みロールを単体でサービスアカウントに付与した場合、BigQuery Python クライアントライブラリ経由で可能な操作は以下の通りでした。

BigQuery
管理者		 BigQuery
データオーナー		 BigQuery
データ編集者		 BigQuery
データ閲覧者		 BigQuery
ユーザー		 BigQuery
ジョブユーザー		 BigQuery
メタデータ閲覧者		 BigQuery
読み取りセッション
ユーザー
プロジェクト一覧参照
list_projects		 〇 〇 〇 〇 〇 〇 〇 〇
データセット一覧参照
list_datasets		 〇 〇 〇 〇 〇 ×（※1） 〇 ×（※1）
データセット情報参照
get_dataset		 〇 〇 〇 〇 〇 × 〇 ×
データセット作成
create_dataset		 〇 〇 〇 × 〇 × × ×
データセット更新
update_dataset		 〇 〇 〇 × 〇 N/A（※2） × N/A（※2）
データセット削除
delete_dataset		 〇 〇 〇 × 〇 N/A（※2） × N/A（※2）
テーブル一覧参照
list_tables		 〇 〇 〇 〇 〇 N/A（※2） 〇 N/A（※2）
テーブル情報参照
get_table		 〇 〇 〇 〇 × × 〇 ×
テーブル作成
create_table		 〇 〇 〇 × × × × ×
テーブル更新
update_table		 〇 〇 〇 × N/A（※3） N/A（※3） × N/A（※3）
テーブル削除
delete_table		 〇 〇 〇 × N/A（※3） N/A（※3） × N/A（※3）
パーティション参照
list_partitions		 〇 〇 〇 〇 N/A（※3） N/A（※3） × N/A（※3）
テーブルデータ参照
list_rows		 〇 〇 〇 〇 N/A（※3） N/A（※3） × N/A（※3）
テーブルデータ追加
insert_rows		 〇 〇 〇 × N/A（※3） N/A（※3） × N/A（※3）
ルーチン一覧参照
list_routines		 〇 〇 〇 〇 〇 N/A（※2） 〇 N/A（※2）
ルーチン情報参照
get_routine		 〇 〇 〇 〇 × × 〇 ×
ルーチン作成
create_routine		 〇 〇 〇 × × × × ×
ルーチン更新
update_routine		 〇 〇 〇 × N/A（※4） N/A（※4） × N/A（※4）
ルーチン削除
delete_routine		 〇 〇 〇 × N/A（※4） N/A（※4） × N/A（※4）
モデル一覧参照
list_models		 〇 〇 〇 〇 〇 〇 N/A（※2）
モデル情報参照
get_model		 〇 〇 〇 〇 × × 〇 ×
モデル更新
update_model		 〇 〇 〇 × N/A（※5） N/A（※5） × N/A（※5）
モデル削除
delete_model		 〇 〇 〇 × N/A（※5） N/A（※5） × N/A（※5）
ジョブ一覧参照
list_jobs		 〇 × × × ×（※1） × × ×
SQLクエリ実行
query		 〇 × × × × × × ×
ファイルデータロード
load_table_from_file		 〇 × × × × × × ×
GCSデータロード
load_table_from_uri		 × × × × × × × ×
テーブルコピー
copy_table		 〇 × × × × × × ×
データエクスポート
extract_table		 × × × × N/A（※3） N/A（※3） × N/A（※3）

※1：パーミッションエラーにはならないがデータ取得できず

※2：get_dataset不可のため

※3：get_table不可のため

※4：get_routine不可のため

※5：get_model不可のため

ドキュメントにベータ版と記載のある、BigQuery リソース管理者、BigQuery Connection 管理者、BigQuery Connection ユーザーロールの確認結果は一覧には記載していませんが、2020/05/15 時点では、プロジェクト参照以外の全操作（Connection ユーザーではプロジェクト参照も）が実行できませんでした。

まとめ（所感）

ジョブ実行を含めた全ての操作が必要な場合には BigQuery 管理者ロールの付与が必要ですが、データロードなどで GCS を使用する場合は、合わせて Cloud Strage ロールの付与も必要です。

また、他のデータべースサービスで GRANT SELECT に相当する参照権には BigQuery データ閲覧者のロールが相当しますが、BigQuery ではさらに、データセットやテーブルなどのメタ情報は参照できるがデータの中身は参照できない、BigQuery メタデータ閲覧者のロールも準備されています。

BigQuery データ編集者ロールでは、データセットやテーブルの作成・編集・削除、テーブルへのデータ追加処理など実行できますが、SQL クエリやデータロードなどのジョブ実行をするには、BigQuery ジョブユーザーロールも合わせて付与する必要があります。

なお、動作確認時、BigQuery データ編集者と BigQuery ユーザーでデータセットの削除（ delete_dataset ）も実行できたのですが、GCP 管理コンソールからロールの権限を確認したところ、bigquery.datasets.delete 権限は付与されていないようでした。 bigquery.datasets.create 権限は付与されているので、delete 権限は明示しなくても実行できるのでしょうか？

カスタムロールを作成して、可能な操作に関して権限レベルでより詳細に調査してみるのもおもしろそうです。

参考

Share this article

facebook logohatena logotwitter logo

関連記事

PythonでBigQueryにスケジュールされたクエリを一覧・削除してみた

PythonでBigQueryにスケジュールされたクエリを一覧・削除してみた

User avatar
nayu.t.s
2022.05.24
BigQuery テーブルチューニングに効果的なクラスタリングが、既存テーブルにも追加できるようになりました！

BigQuery テーブルチューニングに効果的なクラスタリングが、既存テーブルにも追加できるようになりました！

User avatar
みかみ
2021.04.23
BigQuery でテーブル名やカラム名などの更新できない項目を変更するにはどうすればいいか確認してみた

BigQuery でテーブル名やカラム名などの更新できない項目を変更するにはどうすればいいか確認してみた

User avatar
みかみ
2020.05.29
Python クライアントライブラリで Google Cloud Storage の参照・作成・更新・削除操作をするにはどのメソッドを使えばよいのか確認してみた

Python クライアントライブラリで Google Cloud Storage の参照・作成・更新・削除操作をするにはどのメソッドを使えばよいのか確認してみた

User avatar
みかみ
2020.05.21
Classmethod's white logo
Facebook's logo
X's logo
YouTube's logo

© Classmethod, Inc. All rights reserved.