关于BigQuery用户操作记录查询与表权限核查的技术问询

使用BigQuery的INFORMATION_SCHEMA.JOBS_BY_PROJECT视图，结合作业的引用表信息提取所需数据：

DECLARE target_dataset STRING DEFAULT `your-project.your-dataset`; -- 替换为你的项目+数据集
DECLARE lookback_days INT64 DEFAULT 180; -- 过去6个月约180天

SELECT
  job.user_email AS 用户名,
  CONCAT(ref_table.project_id, '.', ref_table.dataset_id, '.', ref_table.table_id) AS 涉及表名,
  TIMESTAMP_TRUNC(job.start_time, SECOND) AS 操作时间,
  job.query_type AS 操作类型
FROM
  `your-project`.`region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT job, -- 替换为你的项目+区域
  UNNEST(job.referenced_tables) ref_table
WHERE
  job.start_time >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL lookback_days DAY)
  AND ref_table.dataset_id = SPLIT(target_dataset, '.')[OFFSET(1)]
  AND ref_table.project_id = SPLIT(target_dataset, '.')[OFFSET(0)]
  AND job.query_type IN ('SELECT', 'INSERT', 'UPDATE', 'DELETE', 'MERGE') -- 覆盖SELECT和所有DML类型
  AND job.state = 'DONE' -- 仅统计已完成的作业
ORDER BY
  操作时间 DESC;

关键提示：

• 替换代码中的项目ID、数据集ID和BigQuery区域为你的实际信息
• referenced_tables会返回作业涉及的所有表，通过UNNEST展开数组获取明细
• 过滤条件包含了所有DML操作类型，确保不遗漏INSERT/UPDATE/DELETE/MERGE类操作
• 只统计已完成作业，避免混入未完成或失败的任务记录

通过INFORMATION_SCHEMA.OBJECT_PRIVILEGES视图直接获取表级权限信息：

DECLARE target_dataset STRING DEFAULT `your-project.your-dataset`; -- 替换为你的项目+数据集

SELECT
  grantee AS 用户名,
  CONCAT(table_catalog, '.', table_schema, '.', table_name) AS 表名,
  privilege_type AS 权限类型,
  grantor AS 授权人,
  granted_time AS 授权时间
FROM
  `your-project`.`region-us`.INFORMATION_SCHEMA.OBJECT_PRIVILEGES -- 替换为你的项目+区域
WHERE
  object_type = 'TABLE'
  AND table_schema = SPLIT(target_dataset, '.')[OFFSET(1)]
  AND table_catalog = SPLIT(target_dataset, '.')[OFFSET(0)]
ORDER BY
  用户名, 表名, 权限类型;

关键提示：

• 通过object_type = 'TABLE'过滤仅表权限，排除其他对象（如视图、函数）的权限记录
• 可以清晰看到每个用户对指定数据集中各表的具体权限（如SELECT、INSERT、UPDATE等）
• 包含授权人和授权时间，方便追溯权限变更历史

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