How to Configure Copilot Studio Bigquery Read Only Access

Published
Length
3 min read

Luis Felipe Dias

All posts

You set up copilot studio bigquery read only access with a cloud service account. You give that account roles that let the bot run jobs and read tables. Many teams start by linking their AI bot with broad owner or editor roles. That quick shortcut lets the bot change tables, delete rows, or read secret data. A safer setup separates the right to run queries from the right to change data. How copilot studio bigquery read only access works Cloud data warehouses split permissions into two distinc

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 read only access with a cloud service account. You give that account roles that let the bot run jobs and read tables. Many teams start by linking their AI bot with broad owner or editor roles. That quick shortcut lets the bot change tables, delete rows, or read secret data. A safer setup separates the right to run queries from the right to change data.

How copilot studio bigquery read only access works

Cloud data warehouses split permissions into two distinct actions for every query. First, an identity needs permission to start a query job inside a project. Second, that same identity needs permission to read the target datasets and tables. If you grant only the table read role, the bot cannot run any queries. If you grant a broad project role, the bot can read every dataset there.

To lock this down, you create a dedicated cloud service account for the bot. You grant job execution rights at the project level so queries can run. Then you grant data viewer rights only on the specific datasets the bot needs. You do not grant any roles that allow table creation, updates, or drops. Next, you link the bot to the warehouse using those scoped credentials. When a user asks the bot a question, the bot sends a query. The cloud warehouse checks the service account roles before it returns any rows.

What native roles solve and what they leave open

Native cloud roles do a good job of stopping write actions at the database. The bot cannot drop a table or alter rows when it lacks write permissions. Native dataset roles also keep the bot out of unrelated tables in other projects. This stops accidental changes from bad prompts or broken bot logic.

Still, static cloud roles only decide who can log in and which tables open. They do not inspect individual queries while those queries run in flight. Once a table is open for reading, the bot can read every column inside. If that table holds emails, phone numbers, card numbers, or national IDs, queries pull them. Data masking rewrites matched fields in results so raw values do not reach the bot. Native read roles alone do not rewrite sensitive fields per query on the fly.

Auditor Read-Only Access + Customer Support Access to Production: architecture patterns and best practices

Continue reading? Get the full guide.

Free. No spam. Unsubscribe anytime.

Shared bot credentials also make auditing hard for security teams. Every user who chats with the bot runs queries under the same service account. Cloud logs show the bot account ran a query, not which person asked. Also, static service account keys sit inside bot settings until someone rotates them by hand. If a key leaks, anyone with that key can read your warehouse tables.

Why query-level controls matter for AI bots

AI bots write and run queries based on plain text questions from users. A user might ask a broad question that scans huge tables all at once. Native read roles let that huge scan run if the table is allowed. Teams need a way to inspect each statement and block unsafe queries before execution. Tying each query back to a real user identity also fixes the audit trail.

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

Why does my read-only service account fail to run queries?

Reading data and running a query job use two separate permissions in the cloud. Your service account needs permission to create query jobs as well as read tables.

Can a read-only role stop the bot from reading sensitive columns?

A basic table read role gives the bot access to every column in that table by default. To hide emails or card numbers, you need column security or dynamic data masking.

Should several bots share the same database service account?

No, sharing one service account across bots mixes their permissions and cloud audit logs. Create a separate account for each bot and scope it only to needed datasets.

The query runs. The emails stay hidden.