How to Configure Copilot Studio ClickHouse Read Only Access
You set up copilot studio clickhouse read only access by creating a limited database user. Then you link that user account to your agent workflow. Many teams start by sharing a broad admin login to test their bots fast. That shortcut creates big risks when an agent runs bad queries or reads private tables. A dedicated read-only user keeps your data safe from accidental changes or deletions. How to set up copilot studio clickhouse read only access ClickHouse manages users, roles, row policies,
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 clickhouse read only access by creating a limited database user. Then you link that user account to your agent workflow. Many teams start by sharing a broad admin login to test their bots fast. That shortcut creates big risks when an agent runs bad queries or reads private tables. A dedicated read-only user keeps your data safe from accidental changes or deletions.
How to set up copilot studio clickhouse read only access
ClickHouse manages users, roles, row policies, settings profiles, and quotas as access items. You can create and manage all of these items with SQL statements. First, you must turn on SQL-driven access control on your database server. This step needs a storage folder set on the server for access rules. It also needs at least one admin user with access management rights turned on.
Once that is ready, an admin with user creation rights can make the new account. Remember that a database user can only grant rights that they already hold themselves. When you create the user, you set the login method and allowed host addresses. You also assign default roles and specific user settings during this step.
Next, you grant read rights on the exact tables the agent needs to read. You can even grant read rights on specific columns in a table. If a user only has rights on two columns, they can query only those columns. A query for all columns with a star will fail and return no data. That query only works if the table holds just those granted columns. In newer versions, you can also grant rights across tables that share a name prefix. Finally, you put these read-only user credentials into your agent tool setup.
What native read-only roles solve and what they miss
Native database roles and column grants solve a major part of your security puzzle. They stop the AI agent from dropping tables, changing rows, or deleting your records. Column grants also stop the bot from querying columns that you did not approve. Host rules make sure login attempts only come from known server addresses.
Still, static database roles only check who logs in at the start of a session. They do not inspect what individual queries do while they run in flight. An agent with read rights on a customer table can still pull every row at once. If an approved column holds emails, phone numbers, or card numbers, the query returns them raw. Static credentials also sit stored inside the agent platform until someone rotates them by hand. Also, shared bot logins make it hard to see which person triggered a slow query.
Auditor Read-Only Access + ClickHouse Access Management: architecture patterns and best practices
Continue reading? Get the full guide.
Free. No spam. Unsubscribe anytime.
Why column grants require careful query design
AI agents often write queries that ask for every column in a table by default. When you use column-level grants, ClickHouse blocks full-table star queries right away. This rule protects unapproved columns, but it can break simple agent prompts. You must guide your agent to ask only for the exact column names it needs.
Teams often create dedicated views or clean tables to make agent queries work better. You can also apply settings profiles and quotas to the agent user account. Quotas help limit how much work the database user can do over a set time. Combining tight column grants with user quotas keeps your analytical cluster fast and stable. Still, database grants alone cannot rewrite sensitive fields like national IDs inside query results.
Check ClickHouse queries and result columns on the wire
ClickHouse users and row policies decide who connects. They do not check each statement on the wire or redact sensitive string columns in query results. With hoop.dev, you run an open-source proxy, a sidecar for your ClickHouse. It reads ClickHouse's native TCP protocol before queries run while ClickHouse stays in charge of login and grants.
Multi-statement queries and table mutations are split and checked by hoop.dev guardrails. Banned actions are refused with a native ClickHouse error. On returned rows, hoop.dev data masking rewrites String and FixedString columns that match your rules. The audit trail logs each query, table, and verdict. See the MIT-licensed source on GitHub.
Frequently asked questions
Can a ClickHouse user grant read access to another user for the agent?
Yes, if they hold the right privileges. In ClickHouse, a user can only grant privileges that they already have themselves, and creating a new user requires the user creation privilege.
What happens if the agent runs a SELECT star query on a column-restricted table?
ClickHouse refuses the query and returns no data at all. The query only works if the table contains solely the columns granted to that user.
How do I enable SQL-driven user and role management in ClickHouse?
You must configure a storage directory on the server for access control and enable access management rights for at least one initial database user.
Related guides
In this post