How to Configure Copilot Studio Bigquery Approval
You set up copilot studio bigquery approval by linking your bot to a flow that pauses before running queries. This setup routes data requests to a human reviewer before the cloud role executes the command. Many teams start by giving their AI bot a shared service account with broad read and write roles. That wide role lets the bot run any query without a check. A single bad prompt can then scan huge tables or change live records. How a copilot studio bigquery approval workflow runs A safe setu
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 copilot studio bigquery approval by linking your bot to a flow that pauses before running queries. This setup routes data requests to a human reviewer before the cloud role executes the command. Many teams start by giving their AI bot a shared service account with broad read and write roles. That wide role lets the bot run any query without a check. A single bad prompt can then scan huge tables or change live records.
How a copilot studio bigquery approval workflow runs
A safe setup separates the chat bot from the database role. First, the user asks the bot a question in the chat window. The bot builds a query or picks a set action based on that prompt. Instead of hitting the data warehouse right away, the bot triggers an action flow.
This flow sends a request to an approver in chat or email. The message shows who asked for the data and what action the bot wants to take. The flow then waits for a person on your team to click approve or reject.
If the reviewer rejects the request, the flow stops and tells the user. If the reviewer approves it, the flow uses its cloud role to run the job in BigQuery. The data warehouse processes the query and sends the rows back to the flow. Last, the bot formats those results and shows them to the user.
What native roles solve and where they fall short
Cloud roles and flow checks solve the first step of access control. They decide which service account can log in and which datasets it can see. They also let a manager pause a bot workflow before the cloud action starts. This stops unauthorized bots from reaching your tables at all.
Still, this native setup leaves a big gap during the actual query run. Cloud roles check who logs in, not what each SQL statement does in flight. Once the flow gets approval, the service account runs with its full set of table permissions. If the bot writes a broad query, the database will return every column in that table.
Approval Chains & Escalation + BigQuery IAM: architecture patterns and best practices
Continue reading? Get the full guide.
Free. No spam. Unsubscribe anytime.
The native approval card also lacks deep context on the data itself. A reviewer might approve a safe-looking request that still pulls sensitive rows. For example, the returned rows might hold emails, phone numbers, card numbers, or national IDs. Without query-level checks and data masking, those raw fields go straight into the bot chat history.
Why query-level checks matter for AI bots
AI bots do not act like standard apps with fixed queries. They build new requests based on what a user types in the prompt. Even if you tell the bot to only read safe columns, prompts can trick it. A user might ask the bot to join another table or dump raw records.
To keep data safe, teams need controls that inspect the exact statement before it runs. You also need masking that rewrites matched fields in results, such as card numbers or emails. Separating identity checks from query checks gives your team real control over every bot action.
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
Can I use a read-only role instead of an approval step?
A read-only role stops the bot from deleting or changing rows in your tables. Still, it does not stop the bot from reading private tables or running huge scans. You still need approval checks and data masking to protect sensitive columns from leaking into chat.
What should a reviewer check before approving a bot query?
The reviewer should check the user identity, the target table, and the exact SQL text. They must make sure the query limits row counts and avoids scanning full partitions. They should also check that the query does not pull unmasked emails or national IDs.
Why is a shared service account risky for AI studio bots?
A shared service account makes every bot query look the same in your cloud logs. When twenty users ask the bot for data, the database only sees one account name. Tying each request to the actual user identity makes audits and approvals much clearer.
Related guides
In this post