使用服务账号验证多数据集下BigQuery表的访问权限

操作流程 #

1. 读取包含多组Project ID、数据集及表名的Excel数据导入请求文件
2. 使用服务账号凭证完成BigQuery身份验证
3. 校验服务账号对目标BigQuery表的查询权限
4. 更新请求文件，标记各表的权限状态并输出到Excel文件

实现代码 #

import pandas as pd
from google.cloud import bigquery
from google.oauth2 import service_account

# 服务账号密钥文件路径
service_account_key_file = 'path_to_your_service_account_key_file.json'
# 导入请求Excel文件路径
excel_file_path = 'path_to_your_ingestion_request_file.xlsx'
# 输出Excel文件路径
output_file_path = 'path_to_your_output_file.xlsx'

# 身份验证（显式指定权限范围）
credentials = service_account.Credentials.from_service_account_file(
    service_account_key_file,
    scopes=["https://www.googleapis.com/auth/bigquery"]
)
client = bigquery.Client(credentials=credentials)

# 读取导入请求文件
ingestion_requests = pd.read_excel(excel_file_path)

# 权限校验函数（改用实际查询验证逻辑）
def check_authority(client, project, dataset, table):
    try:
        # 构造极简查询验证访问权限
        query = f"SELECT 1 FROM `{project}.{dataset}.{table}` LIMIT 1"
        query_job = client.query(query)
        # 尝试获取查询结果，能执行则说明有权限
        query_job.result()
        return "Access Granted"
    except bigquery.exceptions.AccessDenied:
        return "Access Denied"
    except Exception as e:
        # 捕获其他错误（如表不存在、网络异常等）
        return str(e)

# 生成权限校验报告
report = []
for index, row in ingestion_requests.iterrows():
    result = check_authority(client, row["Project ID"], row["Dataset"], row["Table"])
    report.append({
        "Project ID": row["Project ID"],
        "Dataset": row["Dataset"],
        "Table": row["Table"],
        "Result": result
    })

# 保存报告到Excel
report_df = pd.DataFrame(report)
report_df.to_excel(output_file_path, index=False)

问题描述 #

当BigQuery中确实存在目标表时，脚本仍返回Access Denied，或抛出错误：('invalid_grant: Invalid JWT Signature.', {'error': 'invalid_grant', 'error_description': 'Invalid JWT Signature.'})

排查与解决方案 #

针对「Invalid JWT Signature」错误 #

• 校验服务账号密钥文件：确认密钥文件路径正确，文件是从GCP控制台完整下载的有效JSON文件，未被修改、过期或损坏。
• 同步系统时间：JWT签名对时间精度要求高，本地系统时间与标准时间偏差超过5分钟会触发该错误，将系统时间同步到NTP服务器即可解决。
• 显式指定权限范围：在创建Credentials时添加BigQuery专属权限范围（如代码示例中补充的scopes参数），避免默认权限范围不足导致的验证失败。

针对「存在表但返回Access Denied」问题 #

• 替换权限校验逻辑：原代码通过读取表的IAM Policy判断权限，但该操作需要服务账号拥有roles/bigquery.dataOwner或roles/iam.securityReviewer权限，多数场景下服务账号无此权限，导致误判。改用尝试执行简单查询的方式验证权限，更贴合实际访问场景。
• 补充角色判断（若坚持用IAM Policy方式）：原代码仅检查roles/bigquery.dataViewer和roles/bigquery.user，需补充roles/bigquery.dataOwner、roles/bigquery.admin等具备访问权限的角色。
• 核对资源名称准确性：确认Excel中的Project ID、数据集名称、表名与BigQuery中的完全一致，注意GCP资源名的大小写敏感性。

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