How to Configure Copilot Studio ClickHouse Prevent Delete

Published
Length
3 min read

Luis Felipe Dias

All posts

To set up copilot studio clickhouse prevent delete controls, create a read-only database role. Then link your agent to the database using that restricted user account. Many teams start by reusing a shared service account with broad write rights. That shortcut lets an automated agent drop rows or wipe tables by mistake. A misread user prompt can trigger a bad query in seconds. Locking down the database user stops destructive commands before they alter your data. How native copilot studio clickh

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 copilot studio clickhouse prevent delete controls, create a read-only database role. Then link your agent to the database using that restricted user account. Many teams start by reusing a shared service account with broad write rights. That shortcut lets an automated agent drop rows or wipe tables by mistake. A misread user prompt can trigger a bad query in seconds. Locking down the database user stops destructive commands before they alter your data.

How native copilot studio clickhouse prevent delete roles work

ClickHouse manages users, roles, row rules, settings profiles, and quotas as access items. Database admins create and change these items with SQL statements. Turning on SQL access control requires a local storage directory on the server. It also needs at least one admin account with user management rights turned on. An admin can only hand out rights that their own account already holds.

Creating a new user requires the right to create users in the database. The user creation statement sets the login method and allowed source hosts. It also assigns default roles and user settings for that specific account. To block delete actions, you grant only read rights to the agent role. You never grant delete, drop, or alter rights to that user.

You can also limit read grants to specific columns on a table. If an agent only has rights to two columns, it can read only those. A query asking for all columns fails unless the table has only those two. Recent database versions also let admins grant read rights by table name prefix. Once you build this read-only user, you place its login details in your agent tool. The agent then logs in with those restricted rights on every run.

What native roles solve and what they leave open

Native database roles stop an agent from running delete or drop statements. If the agent sends a delete command, the database blocks the query right away. Column grants also keep the agent from reading unapproved columns in a table. This setup solves the basic problem of an agent wiping out production data.

Still, static database roles only decide who can log in and which tables open. They do not inspect what individual queries do in flight during a session. A shared agent account gives every bot user the exact same data view. You cannot tie a single database query back to the human who asked the bot. Also, column grants are all or nothing for the columns you allow.

ClickHouse Access Management + Copilot Security Implications: architecture patterns and best practices

Continue reading? Get the full guide.

Free. No spam. Unsubscribe anytime.

If a permitted column holds emails, phone numbers, or card numbers, queries return them raw. Native roles do not rewrite matched fields in query results on the fly. They also cannot pause a risky query to ask a human for approval first. Teams still have to store static database keys inside the agent platform settings. Managing and rotating those static keys across many bots creates extra work for admins.

How to tighten agent database controls

Start by auditing every database account linked to your automated agents today. Remove any shared admin accounts from your bot tools right away. Create a dedicated role for each agent workflow with only the needed table grants. Limit host rules so the user account only logs in from expected network ranges.

Next, review the columns your agent actually needs to answer user questions. Drop read grants on any column that holds national IDs or card numbers. Set query quotas on the role so a bad loop cannot overload the server. Still, database grants alone cannot link queries to user identities or mask fields dynamically.

Add runtime guardrails and masking beside ClickHouse

Database grants and network rules control who can reach ClickHouse. Even so, an allowed client or agent can send an alter mutation that deletes rows or read raw personal data. In practice, hoop.dev runs beside the server as a sidecar for your ClickHouse and checks native TCP queries with no change to your drivers.

With hoop.dev ClickHouse support, guardrails refuse banned queries before they run, and data masking rewrites matched string columns in results. The audit trail logs which entity types were masked and how many without storing raw values. Furthermore, hoop.dev holds SOC 2 Type II and is open source on GitHub.

Frequently asked questions

Can ClickHouse block delete commands for a specific agent user?

Yes, you can create a user and grant only read rights on specific tables. Without delete or alter rights, the database rejects any delete query from that user.

What happens when an agent runs select star with column grants?

ClickHouse refuses a select star query unless the user has grants on every column. The agent must name only the permitted columns in its query to get data back.

Why are read-only database roles not enough for AI agents?

Read-only roles still return raw sensitive fields like emails or phone numbers inside permitted columns. They also hide which human user triggered a specific query through the shared agent account.

The query runs. The emails stay hidden.