如何获取BigQuery中有权访问指定表的所有用户、组及服务账号

要获取指定表的所有可访问主体（用户、组、服务账号），需要结合项目级IAM、数据集级权限、表级权限三层权限关系——BigQuery的权限是自上而下继承的，单一维度的查询无法覆盖所有有权限的主体。以下是具体实现方法：

方法一：SQL查询数据集+表级权限 #

通过INFORMATION_SCHEMA同时拉取数据集和表的直接授权，合并后得到基础的授权列表：

-- 数据集级授权主体
SELECT
  grantee,
  privilege_type,
  'dataset_level' AS permission_level
FROM mycompany.`region-us`.INFORMATION_SCHEMA.DATASET_PRIVILEGES
WHERE dataset_name = 'mydataset'

UNION ALL

-- 表级授权主体
SELECT
  grantee,
  privilege_type,
  'table_level' AS permission_level
FROM mycompany.`region-us`.INFORMATION_SCHEMA.OBJECT_PRIVILEGES
WHERE object_schema = 'mydataset' AND object_name = 'testdata'

ORDER BY permission_level, grantee;

方法二：补充项目级IAM权限 #

项目级的BigQuery角色（如roles/bigquery.dataViewer、roles/bigquery.admin）会授予所有数据集和表的访问权，需要单独筛选这类角色的绑定主体：

Python代码实现 #

from pprint import pprint
from google.oauth2 import service_account
import googleapiclient.discovery

credentials = service_account.Credentials.from_service_account_file(
    filename='keys/key.json',
    scopes=['https://www.googleapis.com/auth/cloud-platform'])

# 获取项目IAM策略
crm_service = googleapiclient.discovery.build('cloudresourcemanager', 'v1', credentials=credentials)
project_iam = crm_service.projects().getIamPolicy(resource='project-answer-379305', body={}).execute()

# 筛选BigQuery数据访问相关角色
bq_data_roles = [
    'roles/bigquery.admin',
    'roles/bigquery.dataViewer',
    'roles/bigquery.dataEditor',
    'roles/bigquery.dataOwner'
]

project_level_grantees = []
for binding in project_iam.get('bindings', []):
    if binding['role'] in bq_data_roles:
        for member in binding['members']:
            project_level_grantees.append({
                'grantee': member,
                'role': binding['role'],
                'permission_level': 'project_level'
            })

pprint(project_level_grantees)

方法三：合并所有权限来源 #

将项目级、数据集级、表级的授权主体合并去重，得到该表的完整可访问主体列表：

示例Python脚本（统一处理） #

from google.cloud import bigquery
from google.oauth2 import service_account
import googleapiclient.discovery

# 初始化客户端
credentials = service_account.Credentials.from_service_account_file(
    'keys/key.json',
    scopes=['https://www.googleapis.com/auth/cloud-platform'])

bq_client = bigquery.Client(credentials=credentials, project='project-answer-379305')
crm_service = googleapiclient.discovery.build('cloudresourcemanager', 'v1', credentials=credentials)

# 1. 获取表级权限
table_perms = bq_client.get_table('mydataset.testdata').iam_policy
table_grantees = [{'grantee': m, 'role': r, 'level': 'table'} for r, ms in table_perms.items() for m in ms]

# 2. 获取数据集级权限
dataset_perms = bq_client.get_dataset('mydataset').iam_policy
dataset_grantees = [{'grantee': m, 'role': r, 'level': 'dataset'} for r, ms in dataset_perms.items() for m in ms]

# 3. 获取项目级BigQuery数据角色
project_iam = crm_service.projects().getIamPolicy(resource='project-answer-379305', body={}).execute()
bq_roles = ['roles/bigquery.admin', 'roles/bigquery.dataViewer', 'roles/bigquery.dataEditor', 'roles/bigquery.dataOwner']
project_grantees = []
for binding in project_iam.get('bindings', []):
    if binding['role'] in bq_roles:
        for m in binding['members']:
            project_grantees.append({'grantee': m, 'role': binding['role'], 'level': 'project'})

# 合并去重
all_grantees = table_grantees + dataset_grantees + project_grantees
unique_grantees = {g['grantee']: g for g in all_grantees}.values()

# 输出结果
for g in unique_grantees:
    print(f"主体: {g['grantee']}, 角色: {g['role']}, 权限层级: {g['level']}")

注意事项 #

• 服务账号以serviceAccount:xxx@xxx.iam.gserviceaccount.com格式存在，需确保查询和API调用包含这类主体
• 权限优先级：表级 > 数据集级 > 项目级，同一主体的多层权限以最具体的层级为准
• 若存在自定义角色，需额外筛选包含BigQuery数据访问权限的自定义角色绑定

内容的提问来源于stack exchange，提问作者develop