BigQuery audit logs explained

BigQuery writes Admin Activity and Data Access audit logs to Cloud Logging, with Data Access logs on by default. Each job writes entries in two formats at once, a sink routes them into BigQuery tables, and INFORMATION_SCHEMA.JOBS answers the cost questions.

BigQuery audit logs record who did what to your projects: who created a table, granted access, ran a query or read a column. They are Cloud Audit Logs, so they live in Cloud Logging, not in BigQuery, until you route them into a dataset with a sink. For BigQuery, Data Access audit logs are on by default, which is unusual: for other Google Cloud services they are off until you enable them. (The BigQuery Reservation API is an exception and needs ADMIN_READ logs turned on.) Audit logs say who and what. For how much a query cost or how many slots it used, you need INFORMATION_SCHEMA.JOBS.

The log types

Cloud Audit Logs has four types. For BigQuery you mainly deal with the first two.

We checked the default on a project that has no audit configuration in its IAM policy: it still holds BigQuery Data Access entries for every query, with nothing enabled.

A single job can write to both logs. In our project, a CREATE TABLE AS SELECT wrote an entry in Admin Activity (a tableCreation event, permission bigquery.tables.create, type ADMIN_WRITE) and entries in Data Access (the job itself, and a tableDataRead for each table it read). A GRANT on a dataset wrote a datasetChange entry in Admin Activity with the added member and role in bindingDeltas.

Two formats at the same time

BigQuery writes two generations of audit entries. Google calls them the older and newer format:

Google recommends the newer format. The older one is not switched off. We read the latest 400 BigQuery entries from one project and found both side by side: 115 new-format InsertJob entries, 64 old-format jobservice.insert and 79 old-format jobservice.jobcompleted. A typical query job wrote four entries in Data Access: two new (a jobInsertion when it starts, a jobChange with after: DONE when it ends) and two old (jobservice.insert or jobservice.query at the start, jobservice.jobcompleted at the end). Reading the results added a jobservice.getqueryresults entry. If you route “all BigQuery logs” you pay to store each job twice. Filter on the new format:

protoPayload.metadata."@type"="type.googleapis.com/google.cloud.audit.BigQueryAuditMetadata"

That is the filter Google gives in its migration guide. The jobChange entry has the data most people want: the SQL, the statement type, start and end times, the reservation, referenced tables, and queryStats.totalBilledBytes.

Which events answer which question

A tableDataRead entry is written in the project that owns the table, and names the columns the job read. That makes it the way to answer “who has read the salary column”, which INFORMATION_SCHEMA.JOBS can’t tell you directly (see column-level security).

Route the logs into BigQuery

In Logging you can search recent entries, but SQL over months is easier in BigQuery. A sink in Cloud Logging routes entries that match a filter to a BigQuery dataset. This is what we ran, in a throwaway dataset:

gcloud logging sinks create bq-audit \
  bigquery.googleapis.com/projects/PROJECT/datasets/audit \
  --use-partitioned-tables \
  --log-filter='protoPayload.metadata."@type"="type.googleapis.com/google.cloud.audit.BigQueryAuditMetadata"'

Then grant the sink’s writer identity (the command prints it) the BigQuery Data Editor role on the dataset. For example, with SQL: GRANT `roles/bigquery.dataEditor` ON SCHEMA audit TO "serviceAccount:...". Things we saw and Google documents:

• Two tables appeared, named from the log names: cloudaudit_googleapis_com_activity and cloudaudit_googleapis_com_data_access, each partitioned by day on timestamp. Without --use-partitioned-tables Google says you get date-sharded tables (..._YYYYMMDD), the default. Choose partitioned: it prunes by date and costs less to query. See partitioning.
• Entries arrive within about a minute. Logging uses the legacy streaming API; a new table can take several minutes.
• The payload is mostly JSON. The columns are protopayload_auditlog (a record with methodName, resourceName, authenticationInfo, requestMetadata, and so on) and metadataJson, a single STRING column holding the whole BigQueryAuditMetadata as JSON. You read it with JSON_VALUE, not dotted paths. See JSON.
• The first entry decides the schema. If a later entry changes a field’s type, or adds fields past BigQuery’s column limit, the sink writes the failures to an export_errors table.
• Routing is free, storage isn’t. Logging doesn’t charge for routing, but you pay BigQuery for the table storage and the streamed rows. Entries that also stay in the _Default bucket cost $0.50 per GiB ingested beyond a free 50 GiB per project per month.

Here is the query most teams want first, estimated spend per user. The rows are made-up, but follow the shape we saw in real entries, including the entry a job writes when it starts, which the DONE filter drops:

WITH log AS (
  SELECT STRUCT(STRUCT('[email protected]' AS principalEmail) AS authenticationInfo,
    '{"jobChange":{"after":"DONE","job":{"jobConfig":{"type":"QUERY"},"jobStats":{"queryStats":{"totalBilledBytes":"10485760","totalProcessedBytes":"1332943"}}}}}' AS metadataJson) AS protopayload_auditlog
  UNION ALL SELECT STRUCT(STRUCT('[email protected]'),
    '{"jobChange":{"after":"DONE","job":{"jobConfig":{"type":"QUERY"},"jobStats":{"queryStats":{"totalBilledBytes":"1099511627776"}}}}}')
  UNION ALL SELECT STRUCT(STRUCT('[email protected]'),
    '{"jobChange":{"after":"DONE","job":{"jobConfig":{"type":"QUERY"},"jobStats":{"queryStats":{"totalBilledBytes":"5497558138880"}}}}}')
  UNION ALL SELECT STRUCT(STRUCT('[email protected]'),
    '{"jobInsertion":{"job":{"jobConfig":{"type":"QUERY"}}}}')
)
SELECT
  protopayload_auditlog.authenticationInfo.principalEmail AS user_email,
  COUNT(*) AS finished_queries,
  ROUND(SUM(CAST(JSON_VALUE(protopayload_auditlog.metadataJson,
    '$.jobChange.job.jobStats.queryStats.totalBilledBytes') AS INT64)) / POW(1024, 4) * 6.25, 2) AS approx_usd
FROM log
WHERE JSON_VALUE(protopayload_auditlog.metadataJson, '$.jobChange.after') = 'DONE'
GROUP BY user_email
ORDER BY approx_usd DESC;

Two details in real data. First, totalBilledBytes is left out of the JSON when it is zero. A query we ran over a tiny table billed 0 bytes and its entry had no such field, so the value is NULL, not 0; SUM ignores it. Second, Google’s migration guide still prices the hourly cost query at $5.00 per TiB, the old on-demand rate. Use the current rate for your edition and location. See how queries are billed.

The tableDataRead events give you column-level access reports:

WITH log AS (
  SELECT STRUCT('projects/p/datasets/hr/tables/salaries' AS resourceName,
    STRUCT('[email protected]' AS principalEmail) AS authenticationInfo,
    '{"tableDataRead":{"fields":["employee_id","salary"],"jobName":"projects/p/jobs/j1","reason":"JOB"}}' AS metadataJson) AS protopayload_auditlog
  UNION ALL SELECT STRUCT('projects/p/datasets/hr/tables/salaries', STRUCT('[email protected]'),
    '{"tableDataRead":{"fields":["employee_id"],"jobName":"projects/p/jobs/j2","reason":"JOB"}}')
  UNION ALL SELECT STRUCT('projects/p/datasets/hr/tables/salaries', STRUCT('[email protected]'),
    '{"tableDataRead":{"fields":["employee_id","salary"],"jobName":"projects/p/jobs/j3","reason":"JOB"}}')
)
SELECT
  protopayload_auditlog.authenticationInfo.principalEmail AS user_email,
  column_name,
  COUNT(*) AS reads
FROM log, UNNEST(JSON_VALUE_ARRAY(protopayload_auditlog.metadataJson, '$.tableDataRead.fields')) AS column_name
WHERE protopayload_auditlog.resourceName = 'projects/p/datasets/hr/tables/salaries'
GROUP BY user_email, column_name
ORDER BY column_name DESC, user_email;

One trap: when the sink dataset is in a project the sink covers, your queries over the audit tables are themselves BigQuery jobs, and they are logged and routed back into the same tables. We found our own SELECT statements in the routed metadataJson. A dashboard that polls the audit table adds rows to it. Exclude the audit dataset from the sink filter, or route to a different project, and delete test sinks.

Audit logs or INFORMATION_SCHEMA.JOBS?

Google’s own guide says audit logs are the definitive record of who did what and where, and INFORMATION_SCHEMA is where you analyze workloads. They overlap on jobs and differ on almost everything else:

Use INFORMATION_SCHEMA for bills and slow queries, and audit logs for security questions, access reviews and “who deleted this table”. Querylab.io’s query history reads INFORMATION_SCHEMA.JOBS for you and lists every job in the project, whichever tool ran it, with its cost. See monitoring jobs and costs and INFORMATION_SCHEMA.

Viewing and permissions

Logs Viewer sees Admin Activity, Policy Denied and System Event logs. Data Access entries in the _Default bucket need Private Logs Viewer. Audit entries can contain SQL text, schema definitions and resource names, so treat access to the sink dataset as sensitive. Each log message is limited to 100,000 bytes, so very long queries are truncated.

Google’s list of audited methods (google.cloud.bigquery.v2.* and the older names) is long, and its summary table is misleading. It lists JobService.InsertJob and Query, and the old jobservice.insert, jobservice.query and jobservice.getqueryresults, under ADMIN_WRITE, which means Admin Activity. Each method’s own entry says Data Access. In our logs every job entry, old and new format, was in Data Access. InsertJob showed up in Admin Activity only for the tableCreation and tableChange events of jobs that created or changed a table.