DBSheriff for BigQuery — creating the service accounts and granting access

DBSheriff connects to your project with four service accounts, each with only the roles its job needs. Run the commands with the gcloud CLI as a project owner (gcloud auth login, then gcloud config set project <project-id>).

0. What must be enabled in the project

The Without it column says what DBSheriff loses when an item is missing, so you can decide what to enable. Nothing optional or recommended stops the product: detection, response and the evidence pack run on the required items alone.

1. Enable the APIs

P=<project-id>
gcloud services enable bigquery.googleapis.com cloudresourcemanager.googleapis.com \
  iam.googleapis.com logging.googleapis.com datacatalog.googleapis.com --project=$P

2. The four service accounts

Why privateLogViewer: BigQuery's data-access audit entries are "private logs". With logging.viewer alone the API answers 200 with no entries and no error. DBSheriff notices that: when the log has delivered nothing for five minutes while the jobs list shows finished jobs of other accounts, it raises the Availability finding A10, audit log unreadable and reads finished jobs from the jobs list until the log delivers again. roles/iam.securityReviewer already contains the permission this grant adds (logging.privateLogEntries.list), so an account that holds logging.viewer and securityReviewer reads the data-access entries without privateLogViewer. After a missing role is granted, Google can take a minute or two to apply it, and the finding closes when the log delivers its next new entry (on a project nobody is using, with the next job).

gcloud iam service-accounts create dbsheriff-bq-monitor  --project=$P --display-name="DBSheriff monitor"
gcloud iam service-accounts create dbsheriff-bq-response --project=$P --display-name="DBSheriff response"
gcloud iam service-accounts create dbsheriff-bq-posture  --project=$P --display-name="DBSheriff posture"
gcloud iam service-accounts create dbsheriff-classify    --project=$P --display-name="DBSheriff classification"

M=serviceAccount:dbsheriff-bq-monitor@$P.iam.gserviceaccount.com
for r in roles/bigquery.resourceViewer roles/logging.viewer roles/logging.privateLogViewer \
         roles/bigquery.jobUser roles/bigquery.dataViewer; do
  gcloud projects add-iam-policy-binding $P --member=$M --role=$r; done
# jobUser + dataViewer: the Query Health scanner runs a small read-only
# INFORMATION_SCHEMA.PARTITIONS lookup per partitioned table it sees
# referenced in a job, to compute what fraction of the table's partitions
# a query actually touched. Running any query needs bigquery.jobs.create
# (jobUser); reading INFORMATION_SCHEMA.PARTITIONS needs dataViewer.
# Without both, the partition-pruning check (P17) never fires.

R=serviceAccount:dbsheriff-bq-response@$P.iam.gserviceaccount.com
for r in roles/bigquery.admin roles/resourcemanager.projectIamAdmin roles/iam.serviceAccountKeyAdmin \
         roles/iam.serviceAccountAdmin roles/datacatalog.categoryAdmin; do
  gcloud projects add-iam-policy-binding $P --member=$R --role=$r; done
# deny-policy rung of the lock ladder (folder or organisation level):
# gcloud resource-manager folders add-iam-policy-binding <folder-id> --member=$R --role=roles/iam.denyAdmin

S=serviceAccount:dbsheriff-bq-posture@$P.iam.gserviceaccount.com
for r in roles/bigquery.metadataViewer roles/bigquery.jobUser roles/bigquery.resourceViewer \
         roles/iam.securityReviewer roles/logging.viewer roles/logging.privateLogViewer; do
  gcloud projects add-iam-policy-binding $P --member=$S --role=$r; done

C=serviceAccount:dbsheriff-classify@$P.iam.gserviceaccount.com
for r in roles/bigquery.jobUser roles/datacatalog.viewer; do
  gcloud projects add-iam-policy-binding $P --member=$C --role=$r; done
# read access only on the datasets you want scanned (project-wide dataViewer if all of them):
bq add-iam-policy-binding --member=$C --role=roles/bigquery.dataViewer $P:<dataset>
# Dataset-level IAM is allowlist-gated on some project tiers (notably the
# no-billing "sandbox" tier) — if the command above fails with "This
# feature requires allowlisting", grant roles/bigquery.dataViewer at the
# project level instead (the loop above, with dataViewer added to it).

3. Keys

for sa in dbsheriff-bq-monitor dbsheriff-bq-response dbsheriff-bq-posture dbsheriff-classify; do
  gcloud iam service-accounts keys create ./$sa-key.json --iam-account=$sa@$P.iam.gserviceaccount.com
done

The JSON content of each key file (not the path) is pasted into the DBSheriff Admin Console: monitor → Key; posture → the Posture private key PEM (Snowflake) / service account key JSON (BigQuery, Spanner) / … box (under Advanced: Posture credential); classify → the Classification private key PEM (Snowflake) / service account key JSON (BigQuery, Spanner) box (under Advanced: Classification credential). The response account's key goes into Advanced: Kill & Lock credentials, in the Kill private key PEM (Snowflake) / service-account JSON (Spanner, BigQuery) box (and the matching Lock box, which falls back to the kill key when blank). Until it is set, cancel, lock and the policy-tag write-back run with the monitor key, which does not have the response account's roles and cannot do them. Rotate keys at least every 90 days (DBSheriff flags older ones), or run DBSheriff on Google Cloud with Workload Identity and leave the key fields blank (a host deployed from deploy/gcp allows that out of the box; on any other host, a Compute Engine VM you built yourself included, set ALLOW_ADC_SERVERS=true in .env first, or the Console refuses a new server with no key).

4. Register the project in DBSheriff

Admin Console → Monitored Servers → Add: engine BigQuery, Project ID in the database field, Location (for example US) in the schema field, and the keys from §3. The Poll mode box defaults to engine default, which polls at the BigQuery value under Poll Intervals in Config Parameters (2 seconds by default). balanced polls every 60 seconds while quiet and every 10 seconds after activity, economy every 5 minutes, realtime every 2 seconds; custom lets you set the quiet and active intervals. The job and audit-log reads are free API calls, so the mode only protects API quota. Switches: collect audit logs, jobs, IAM and keys (on); write sensitivity policy tag back on approval (off by default). DBSheriff excludes its own four accounts from monitoring automatically.

5. Optional but recommended

• Audit-log retention and export: keep the _Default bucket at 30 days or more and add a sink to a locked bucket or dataset; DBSheriff reports a short retention and a missing sink.
• VPC Service Controls: when your project is in an organisation with a perimeter, grant the posture account Access Context Manager Reader at the organisation so DBSheriff can mirror the perimeter's address ranges into its trusted networks.
• Expected admins: set POSTURE_BIGQUERY_ALLOWED_ADMINS to the principals that are supposed to hold owner / editor / BigQuery admin (DBSheriff's own response account included), so the posture check lists only the unexpected ones.

6. Verify

gcloud projects get-iam-policy $P --format="table(bindings.role,bindings.members)"
gcloud iam service-accounts keys list --iam-account=dbsheriff-bq-monitor@$P.iam.gserviceaccount.com
gcloud logging read 'logName:"data_access" AND protoPayload.serviceName="bigquery.googleapis.com"' \
  --project=$P --limit=3 --freshness=1d      # data-access entries exist (they always do on BigQuery)

The Admin Console has no Test button for a server. DBSheriff connects with the monitor key as soon as it starts monitoring the server (within the registry reload interval, five minutes by default); a key BigQuery refuses shows in the producer's log (Error fetching running jobs). The other keys are used when their job runs: the response key for a cancel or lock, the posture key in the hourly import and the posture scans, the classification key in the classification scans. The first hourly import shows identities, authentication events and coverage within the hour.