How to Configure Copilot Studio Bigquery Prevent Delete

Published
Length
3 min read

Luis Felipe Dias

All posts

To set up a copilot studio bigquery prevent delete rule, assign a read-only role to your bot account. You also remove table drop and row removal rights from that cloud identity. Many teams link their AI bots to data warehouses with broad admin keys during early testing. Those wide permissions let a bad prompt wipe out whole tables or remove key rows by mistake. How to set up copilot studio bigquery prevent delete rules First, look at how the bot reaches your data warehouse. The bot sends quer

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.

To set up a copilot studio bigquery prevent delete rule, assign a read-only role to your bot account. You also remove table drop and row removal rights from that cloud identity. Many teams link their AI bots to data warehouses with broad admin keys during early testing. Those wide permissions let a bad prompt wipe out whole tables or remove key rows by mistake.

How to set up copilot studio bigquery prevent delete rules

First, look at how the bot reaches your data warehouse. The bot sends queries through a connector or custom action that uses a cloud service account. If that service account holds editor or owner rights, the bot can run any statement it builds.

To stop data loss, build a custom role in your cloud console. Give this role permission to run jobs and read table rows only. Leave out permissions that update rows, remove rows, or drop tables. Next, bind this narrow role to the service account that your bot uses to log in.

You can also limit the role to specific datasets rather than the whole project. When the bot tries to run a delete statement, the warehouse checks the account role and blocks the job. This native setup gives you a strong baseline defense against bad bot actions.

Why role checks alone leave gaps for AI bots

Cloud roles work well when a bot only needs to read data. Yet many teams build bots that must write new rows or update status fields in a table. In BigQuery, data changes often share permission checks or require broader table write access. Once you grant write rights for inserts, stopping delete commands through basic roles gets hard.

Native roles decide who can log in and touch a table. They do not inspect the exact SQL text inside each request while it runs. A bot with write access could still run a bad delete statement if a prompt tricks it. Also, static service account keys sit inside bot settings for months.

BigQuery IAM + Copilot Security Implications: architecture patterns and best practices

Continue reading? Get the full guide.

Free. No spam. Unsubscribe anytime.

Also, read-only roles do not stop a bot from pulling raw sensitive rows. If a table holds emails, phone numbers, card numbers, or national IDs, the bot reads them all. Data masking that rewrites matched fields in results is needed to keep those values safe.

How to separate read actions from write actions

One way to reduce risk is to split your bot tasks across two distinct service accounts. Use a read-only account for all general search and reporting actions inside the bot. Create a second account for write tasks, and point it only to a safe staging table.

Your data team can then review rows in the staging table before merging them into main tables. You can also use stored procedures to wrap safe insert logic so the bot never writes raw SQL. This design keeps the bot away from direct delete power on your core tables.

Check database and API queries on the wire

Identity tools, roles, and network rules decide who opens a session. Setup alone cannot check what each query does or mask sensitive values in a response once a session starts. When workflows touch Postgres, MySQL, SQL Server, MongoDB, ClickHouse, Kubernetes APIs, SSH, or internal HTTP APIs, hoop.dev runs beside those services as an open-source proxy, a sidecar for your Postgres or API. It inspects requests coming in and results going back on the wire.

Your login details and client tools stay as they are while hoop.dev guardrails refuse bad deletes or drops before they run. On responses, hoop.dev data masking rewrites matched sensitive values in memory. Furthermore, hoop.dev holds SOC 2 Type II, is MIT licensed, and writes every query and decision to an audit trail that generates evidence for HIPAA, GDPR, and PCI DSS. See the source on GitHub.

Frequently asked questions

Can a read-only service account stop all BigQuery delete statements?

Yes, a read-only role blocks all table drops and row delete commands at the warehouse level. The bot can still run select queries and read data without any risk of removing rows.

What happens if my bot needs to insert rows but not delete them?

Granting write access on a table often allows both inserts and deletes in standard role setups. To fix this, let the bot write only to an append-only staging table instead of core tables.

How do I protect card numbers and emails from showing up in bot chats?

IAM roles only allow or block access to a full table or column. You need a rule that rewrites matched fields like emails and card numbers in query results.

The query runs. The emails stay hidden.