Auditing BigQuery Access Logs

Auditing BigQuery Access Logs

Learn how to capture, query, and interpret Google BigQuery access logs to secure data, control spend, and maintain compliance.

What Are BigQuery Access Logs?

BigQuery access logs are records automatically written to Cloud Logging whenever a user or service account interacts with BigQuery. Each log entry is a JSON object that includes who performed the action (principalEmail), what was done (methodName), which resource was touched (resourceName), when it happened (timestamp), and several context-rich sub-objects. Combined, these logs offer a forensic trail for every jobs.query, tabledata.list, or tables.get call—even failed attempts.

Why Audit BigQuery Access Logs?

Regularly reviewing access logs enables you to:

• Detect unauthorized access – Spot compromised credentials or malicious insiders accessing restricted datasets.
• Control cost – Identify long-running or highly frequent queries that generate avoidable charges.
• Prove compliance – Generate evidence for GDPR, HIPAA, SOC 2, or internal governance reviews.
• Troubleshoot performance – Trace slow dashboards to the exact SQL and user.

Anatomy of a Log Entry

At a minimum, every entry has:

• protoPayload.authenticationInfo – User, service account, or workload identity.
• protoPayload.serviceName = "bigquery.googleapis.com"
• protoPayload.methodName – e.g. jobservice.jobcompleted, tableservice.gettable.
• resource.labels – Project number, dataset ID, table ID.
• protoPayload.metadata.jobChange.job.jobConfig.queryConfig.query – The actual SQL for queries.

Understanding the schema allows precise filters that reduce noise and speed up investigation.

Setting Up Logging in Google Cloud

BigQuery audit logs are enabled by default for all projects, but only retained for 30 days in Cloud Logging. For long-term analytics you should:

1. Create a log sink that routes resource.type = "bigquery_resource" to a dedicated BigQuery dataset (often called audit_logs).
2. Partition the destination table on the _PARTITIONTIME pseudo-column (or timestamp field) to avoid costly full scans.
3. Set IAM so that only security and data teams can query or export these logs.

Querying Access Logs in BigQuery

Once ingested into BigQuery, logs become first-class citizens ready for SQL. A common pattern is to wrap the raw table in LOGICAL views that surface only the fields you need. Below is a starter query that returns every successful query job in the past 24 hours:

SELECT
TIMESTAMP_TRUNC(protoPayload.timestamp, HOUR) AS hour_bucket,
protoPayload.authenticationInfo.principalEmail AS actor,
job.jobStatistics.totalProcessedBytes / POW(2, 30) AS processed_GB,
job.jobConfig.queryConfig.query AS sql_text
FROM `my_project.audit_logs.cloudaudit_googleapis_com_data_access` AS l,
UNNEST(protoPayload.metadata.jobChange) AS jc,
UNNEST([jc.job]) AS job
WHERE
protoPayload.serviceName = 'bigquery.googleapis.com'
AND protoPayload.methodName = 'jobservice.jobcompleted'
AND protoPayload.status.code = 0 -- success
AND TIMESTAMP(protoPayload.timestamp) > TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 24 HOUR)
ORDER BY processed_GB DESC;


Practical Use Cases and Examples

Detecting Unauthorized Data Exfiltration

Look for SELECTs on sensitive tables by unexpected principals or unusually large totalProcessedBytes. Combine with Cloud Identity information to trigger alerts when a user in the contractor group scans a PII dataset.

Identifying Costly Queries

Join jobStatistics.totalBilledBytes with pricing data to compute spend per user or dashboard. Historical trending makes it easy to see which Looker Studio report exploded in cost after a schema change.

Monitoring Service Account Usage

Service accounts should operate predictably. When a data-loading account suddenly performs tableservice.list calls, you may have leaked credentials. Schedule a daily query that flags new methodName values per service account.

Best Practices for Access Log Auditing

• Centralize logs in one BigQuery dataset across all projects to simplify cross-project investigations.
• Use materialized views for costly aggregations such as “bytes processed per user per day.”
• Partition & cluster on timestamp and principalEmail to minimize read costs.
• Apply row-level security so that sensitive SQL texts are visible only to auditors.
• Automate alerts with Cloud Monitoring or Pub/Sub pushes when thresholds are crossed.

Common Mistakes and How to Avoid Them

Even experienced teams fall into traps that weaken their audit posture.

• Ignoring non-query actions – Access logs include table metadata reads and export jobs that can leak data without running SQL. Always monitor tableservice.gettable and storage.Write events.
• Not retaining logs long enough – The default 30-day window is rarely sufficient for compliance or incident response. Export to BigQuery or Cloud Storage with at least six months of retention.
• Scanning the raw table – Forgetting to filter by _PARTITIONTIME or date range can create runaway bills. Use WHERE _PARTITIONTIME >= ... in every query or enforce dataset-wide partition filters.

Automating Audits with Scheduled Queries

Leverage BigQuery scheduled queries to run security checks hourly or daily. Store findings in a security_findings table and trigger Cloud Functions or Slack notifications on new rows. This forms the backbone of continuous monitoring without extra infrastructure.

Integrating Insights into Alerting & Dashboards

Publish summarized data (e.g., bytes processed by user per day) to Looker or Data Studio. Overlay cost thresholds and SLA markers. When an anomaly appears, drill back into the raw logs for root cause analysis.

How Galaxy Fits In

Because Galaxy is a modern SQL editor, you can point it at the audit_logs dataset and leverage its AI copilot to draft the complex JSON extraction queries shown above. Store vetted queries in Galaxy Collections so your security team shares one source of truth instead of pasting snippets into Slack.

• Context-aware autocomplete surfaces nested log fields without memorization.
• Endorsement lets a senior engineer mark an audit query as approved for incident response playbooks.
• Parameterization enables reusable templates where you pass a principal_email at runtime.

Conclusion

Auditing BigQuery access logs transforms raw metadata into actionable intelligence. By exporting logs to BigQuery, applying sound schema design, and automating analysis, teams can tighten security, control cost, and breeze through compliance audits. Pair these practices with Galaxy’s developer-friendly SQL workspace to move from ad-hoc queries to a living, shareable audit framework.