How to Configure Copilot Studio Bigquery Audit Log
You set up a copilot studio bigquery audit log workflow by linking a custom agent action to Google Cloud logging sinks. Then you assign a cloud service role that lets the agent query your dataset and record each job. Many teams start by giving the agent a broad project role to speed up testing. When roles are too broad, the agent can read private tables and run large queries. How to set up a copilot studio bigquery audit log First, you create a dedicated cloud service account for your agent w
Your coding agents run with your access.
Claude Code reaches prod with a developer's credentials, and approval prompts get clicked through. hoop.dev checks every query, masks sensitive data, and holds risky changes for a human.
You set up a copilot studio bigquery audit log workflow by linking a custom agent action to Google Cloud logging sinks. Then you assign a cloud service role that lets the agent query your dataset and record each job. Many teams start by giving the agent a broad project role to speed up testing. When roles are too broad, the agent can read private tables and run large queries.
How to set up a copilot studio bigquery audit log
First, you create a dedicated cloud service account for your agent workflow. You grant this account a narrow role that only reads specific dataset tables. Never give the agent an admin role or full project edit rights. Next, you turn on data access logs inside your cloud logging console. Cloud logging captures every query job and writes an entry to an audit sink.
After the cloud side is ready, you build the custom action inside your agent workspace. You store the service account key inside your workspace secret store. When a user asks the agent a question, the action sends a query to the dataset. The cloud engine runs the query and returns the rows to the agent. At the same time, the cloud platform writes the event to your audit log.
Your security team can then read these audit logs to track agent activity. Each log entry shows the service account name, the query text, and the job time. It also records how many bytes the query scanned and which tables it touched. This basic setup gives your team a timeline of every database call the agent makes.
What native logging solves and where it falls short
Native cloud logs do a great job of recording past database events. They prove that a query ran and show which service account sent it. They also help teams track cloud costs by logging the size of each scan. If an incident happens, auditors can review the log sink to see what tables were read.
Still, native roles and cloud logs leave a major operational gap open. The cloud setup only checks who logs in at the start of the call. Every user who talks to the agent shares the exact same service account identity. In your audit log, every query looks like it came from one generic bot user. You cannot easily tell which human prompt triggered a risky database query.
Audit Log Integrity + BigQuery IAM: architecture patterns and best practices
Continue reading? Get the full guide.
Free. No spam. Unsubscribe anytime.
Also, an audit log only tells you about a bad query after it finishes. It does not inspect or block individual statements while they are in flight. If a user tricks the agent into asking for sensitive rows, the query still runs. Native roles cannot rewrite matched fields like emails, phone numbers, card numbers, or national IDs in query results. To protect live data, teams need controls that work on each query before results return.
Best practices for safer agent database queries
Start by splitting your datasets so the agent only sees safe tables. Keep tables with card numbers, phone numbers, and national IDs out of the agent role. Set strict byte limits on the database project to stop runaway agent queries. Review your audit log tables every week to spot strange query patterns early.
Next, map each agent request back to the person who started the chat. When you tie user identity to each database statement, your logs become much more useful. You should also add pre-execution checks for any query that changes data or reads sensitive columns. Finally, use field masking that rewrites matched fields in results before the agent reads them.
Govern statements and responses across databases and APIs
Good config and scoped roles are needed setup. They do not check single queries in flight or mask sensitive fields in returned rows. Across Postgres, MySQL, SQL Server, MongoDB, ClickHouse, Kubernetes APIs, SSH, and internal HTTP endpoints, hoop.dev runs beside each supported service as an open-source proxy, a sidecar for your database or API.
First, hoop.dev guardrails block banned queries before they run. Then hoop.dev data masking rewrites matched sensitive values in responses in memory. The audit trail records every statement and verdict per resource. Furthermore, hoop.dev holds SOC 2 Type II and is open source on GitHub.
Frequently asked questions
What does a BigQuery audit log record for Copilot Studio queries?
The log records the service account identity, the full SQL text, and the timestamp. It also logs the referenced tables, the job status, and the total bytes billed.
Why do all agent queries show the same user in cloud logs?
The agent uses one shared service account to talk to the cloud API. Because of this, the database sees the bot identity instead of the human user.
Can native cloud audit logs block sensitive data from reaching the agent?
No, audit logs only record events after the database processes the query. To redact emails or card numbers in results, you need inline data masking controls.
Related guides
In this post