+1 (415) 943-1448
BQBrains

Locking Down BigQuery: IAM Design, VPC Service Controls, CMEK, and Audit Logs

Most BigQuery security writing stops at row-level security and column masking. That is the inside of the warehouse. The questions an auditor, a security reviewer, or a prospective enterprise customer actually asks are about the outside: who can read a whole dataset, who can export it to a bucket in another organization, which key encrypts it, and how you would prove, six months later, who ran a particular query.

This is a hands-on guide to that outer perimeter — IAM design, VPC Service Controls, customer-managed encryption keys, and Cloud Audit Logs — with the SQL, gcloud, and Terraform you need to put it in place. It assumes you have already read our guide to row-level security, column masking, and policy tags; the two layers are complementary.

1. Get IAM right before anything else

Perimeter controls do not compensate for over-broad grants. Three rules cover most of the damage we find in assessments.

Stop granting roles/bigquery.dataEditor at the project level. Project-level data roles apply to every dataset created afterwards, including ones you have not thought about yet. Grant at the dataset level, or at the table level where the dataset is mixed-sensitivity:

-- Dataset-level grant, expressed as DDL so it lives in Git
GRANT `roles/bigquery.dataViewer`
ON SCHEMA `acme-analytics.marts`
TO 'group:analysts@example.com';

-- Table-level grant for a single sensitive table
GRANT `roles/bigquery.dataViewer`
ON TABLE `acme-analytics.marts.customer_ltv`
TO 'group:revops@example.com';

Separate the right to run jobs from the right to read data. roles/bigquery.jobUser on the project plus a dataset-scoped dataViewer is the correct shape for an analyst. roles/bigquery.user quietly includes dataset creation; roles/bigquery.admin includes the ability to delete everything and to change reservations.

Use authorized views, authorized datasets, and authorized routines instead of raw-table grants. An authorized view can read a source dataset that its own readers cannot:

CREATE OR REPLACE VIEW `acme-analytics.marts_public.orders_safe` AS
SELECT order_id, order_date, region, net_amount
FROM `acme-analytics.raw.orders`;

-- Authorize the whole marts_public dataset against raw
ALTER SCHEMA `acme-analytics.raw`
SET OPTIONS (
  -- in Terraform this is google_bigquery_dataset_access with a dataset block
  description = 'see authorized dataset grant for marts_public'
);

In Terraform:

resource "google_bigquery_dataset_access" "marts_public_reads_raw" {
  dataset_id = google_bigquery_dataset.raw.dataset_id
  dataset {
    dataset {
      project_id = var.project_id
      dataset_id = google_bigquery_dataset.marts_public.dataset_id
    }
    target_types = ["VIEWS"]
  }
}

Finally, audit what you have. Every principal with data access to a dataset is visible in INFORMATION_SCHEMA:

SELECT
  object_name AS dataset_or_table,
  grantee,
  privilege_type
FROM `region-us`.INFORMATION_SCHEMA.OBJECT_PRIVILEGES
WHERE object_schema = 'raw'
ORDER BY grantee;

Run that on a schedule, write the output to a table, and diff it week over week. Unexplained new grantees are the single highest-signal security alert a data platform can emit.

2. VPC Service Controls: the exfiltration boundary

IAM answers "who may read this". VPC Service Controls answers "and to where may it leave". A service perimeter around your BigQuery projects blocks copy jobs, exports, and reads that cross the boundary, even for a principal whose IAM allows it — which is exactly the control that stops a compromised service account from running EXPORT DATA into an attacker's bucket.

# 1. Create a dry-run perimeter first. Never start enforcing.
gcloud access-context-manager perimeters create acme_data \
  --title="ACME data perimeter" \
  --resources=projects/111111111111,projects/222222222222 \
  --restricted-services=bigquery.googleapis.com,storage.googleapis.com \
  --policy=POLICY_ID \
  --perimeter-type=regular \
  --dry-run

# 2. Let it run for two weeks, then read the violations that *would* have occurred
gcloud logging read \
  'protoPayload.metadata."@type"="type.googleapis.com/google.cloud.audit.VpcServiceControlAuditMetadata"
   AND protoPayload.metadata.dryRun="true"' \
  --limit=200 --format=json

Expect the dry-run log to surface things you forgot: a Looker Studio connector, a partner's Analytics Hub subscription, a CI runner outside the perimeter, an on-prem IP range. Each one becomes either an ingress/egress rule or an access level, not an exception you carve out by widening IAM. Only enforce after the dry-run log is quiet for a full business cycle, including month-end.

Two BigQuery-specific gotchas:

  • Analytics Hub and data clean rooms cross perimeters. If you publish or subscribe to a listing with an organization outside the perimeter, you need an explicit egress or ingress rule for bigquery.googleapis.com naming that project. Plan this before you promise a partner a feed.
  • BigQuery Omni and external connections reach AWS or Azure from Google-managed infrastructure; perimeter rules behave differently from a normal query. Test them in dry-run specifically.

3. CMEK: encryption you can revoke

BigQuery encrypts everything at rest by default. Customer-managed encryption keys matter when your obligation is to be able to revoke access cryptographically, or to prove key residency in a region.

-- Dataset default key: every table created afterwards inherits it
ALTER SCHEMA `acme-analytics.regulated`
SET OPTIONS (
  default_kms_key_name =
    'projects/acme-kms/locations/us/keyRings/bq/cryptoKeys/regulated'
);

-- Confirm what a table is actually using
SELECT table_name, ddl
FROM `acme-analytics.regulated`.INFORMATION_SCHEMA.TABLES
WHERE table_name = 'claims';

Practical notes from deployments:

  • The key must live in the same region as the dataset; a multi-region dataset needs a multi-region key ring.
  • Grant the BigQuery service agent (bq-PROJECT_NUMBER@bigquery-encryption.iam.gserviceaccount.com) roles/cloudkms.cryptoKeyEncrypterDecrypter on the key, or every job fails with a key-access error.
  • Setting a dataset default does not re-encrypt existing tables. Rewrite them (CREATE OR REPLACE TABLE ... AS SELECT *, or a copy job with the destination key) and verify with the DDL query above.
  • Rotation is transparent for new data; existing data is re-encrypted lazily. If your policy requires full re-encryption on rotation, schedule table rewrites.
  • Destroying a key version makes the data permanently unreadable. That is the point — and it is also why key admin and data admin should never be the same person.

4. Audit logs you can actually query

Admin Activity logs are always on and free. Data Access logs for BigQuery are the ones that answer "who read this table", and DATA_READ must be enabled deliberately. Turn them on in the organization IAM policy, then route them to BigQuery so they are queryable:

gcloud logging sinks create bq_audit_sink \
  bigquery.googleapis.com/projects/acme-security/datasets/audit \
  --log-filter='protoPayload.serviceName="bigquery.googleapis.com"' \
  --organization=ORG_ID --include-children

Then the queries that matter at review time:

-- Every principal that read a sensitive table in the last 30 days
SELECT
  protopayload_auditlog.authenticationInfo.principalEmail AS principal,
  COUNT(*) AS reads,
  MAX(timestamp) AS last_read
FROM `acme-security.audit.cloudaudit_googleapis_com_data_access`
WHERE DATE(timestamp) >= CURRENT_DATE() - 30
  AND protopayload_auditlog.metadataJson LIKE '%regulated.claims%'
  AND JSON_VALUE(protopayload_auditlog.metadataJson,
                 '$.jobChange.job.jobStatus.jobState') = 'DONE'
GROUP BY principal
ORDER BY reads DESC;

-- Data leaving the warehouse: export and extract jobs
SELECT
  protopayload_auditlog.authenticationInfo.principalEmail AS principal,
  JSON_VALUE(protopayload_auditlog.metadataJson,
             '$.jobChange.job.jobConfig.extractConfig.destinationUris[0]') AS destination,
  timestamp
FROM `acme-security.audit.cloudaudit_googleapis_com_data_access`
WHERE JSON_VALUE(protopayload_auditlog.metadataJson,
                 '$.jobChange.job.jobConfig.type') = 'EXTRACT'
  AND DATE(timestamp) >= CURRENT_DATE() - 7
ORDER BY timestamp DESC;

Set a retention policy on the audit dataset (most regimes want 12 months or more), partition it by DATE(timestamp), and require a partition filter so a careless investigation query does not scan a year of logs. Wire a Cloud Monitoring log-based alert on two patterns to start with: any EXTRACT job targeting a bucket outside your organization, and any IAM grant on a regulated dataset.

5. A review-ready checklist

  1. No project-level dataEditor/dataOwner for humans; dataset-scoped grants only, expressed in Terraform or DDL.
  2. Weekly OBJECT_PRIVILEGES snapshot, diffed and alerted on.
  3. Authorized views or authorized datasets between raw and consumer layers.
  4. VPC Service Controls perimeter around all warehouse projects, enforced, with documented ingress/egress rules for every partner and tool.
  5. CMEK on regulated datasets, keys in a separate project with a separate admin group, rotation scheduled.
  6. DATA_READ audit logs enabled org-wide, sunk to a partitioned BigQuery dataset with ≥12-month retention.
  7. Alerts on external exports and on IAM changes to regulated datasets.
  8. An annual restore-and-access drill: prove you can answer "who read this row, when" within an hour.

Items 1–3 are a week of work for most teams. Item 4 is where it gets political, because the dry-run log always finds a tool somebody depends on.

If you want help designing the perimeter, or an independent review of what your BigQuery estate currently exposes, that is the core of our BigQuery data governance and compliance practice — get in touch and we will start with the access audit.