Create a Read-Only Database User for AI Agents

A read-only database user for an AI agent is a dedicated login with permission to connect and to run SELECT on specific tables, and nothing else. Create it with your database’s CREATE USER (or CREATE ROLE) statement, grant SELECT on only the schemas or tables the agent needs, and set up default privileges if new tables should be included. Add limits on connections and query time. Never use a superuser, the application’s own login, or a role such as db_owner. The exact SQL for PostgreSQL, MySQL and MariaDB, SQL Server, Oracle, ClickHouse and Snowflake is below.

Rules that apply on every engine

• One login per purpose. A separate user for the AI makes its queries easy to find and its access easy to revoke.
• Grant tables, not the database. Start with the tables the agent needs. Widen later.
• Keep sensitive columns out at the source with column-level grants or views.
• Set limits on the login itself: connections, statement time, and where the engine allows it, CPU or reads.
• Prefer a read replica. A read-only user on a replica cannot slow down your application’s writes.
• Test it. Log in as the user and try a write. It should fail.

PostgreSQL

CREATE ROLE ai_reader LOGIN PASSWORD 'choose-a-strong-password'
  CONNECTION LIMIT 5;
ALTER ROLE ai_reader SET statement_timeout = '30s';
ALTER ROLE ai_reader SET default_transaction_read_only = on;

GRANT CONNECT ON DATABASE shop TO ai_reader;
GRANT USAGE ON SCHEMA sales TO ai_reader;

-- Specific tables
GRANT SELECT ON sales.orders, sales.products TO ai_reader;
-- Or the whole schema:
-- GRANT SELECT ON ALL TABLES IN SCHEMA sales TO ai_reader;

-- Tables that app_owner creates later in this schema
ALTER DEFAULT PRIVILEGES FOR ROLE app_owner IN SCHEMA sales
  GRANT SELECT ON TABLES TO ai_reader;

-- A table without its sensitive columns
GRANT SELECT (id, city, created_at) ON sales.customers TO ai_reader;

Three PostgreSQL details people miss:

1. ALTER DEFAULT PRIVILEGES applies only to objects created by the role named in FOR ROLE (or by you, if you leave it out). Name the role your migrations run as.
2. Before PostgreSQL 15, every role could create tables in the public schema. On older versions, run REVOKE CREATE ON SCHEMA public FROM PUBLIC;. This affects all roles, so check your application does not rely on it.
3. Avoid pg_read_all_data (PostgreSQL 14 and later). It grants SELECT on every table in every schema, which is the opposite of scoped.

Functions are executable by PUBLIC by default. If an exposed schema has SECURITY DEFINER functions owned by a privileged role, review them, because they run with their owner’s rights.

MySQL and MariaDB

CREATE USER 'ai_reader'@'10.0.%' IDENTIFIED BY 'choose-a-strong-password'
  WITH MAX_USER_CONNECTIONS 5;

-- Specific tables
GRANT SELECT ON shop.orders TO 'ai_reader'@'10.0.%';
GRANT SELECT ON shop.products TO 'ai_reader'@'10.0.%';
-- Or a whole database, including tables created later:
-- GRANT SELECT, SHOW VIEW ON shop.* TO 'ai_reader'@'10.0.%';

-- A table without its sensitive columns
GRANT SELECT (id, city, created_at) ON shop.customers TO 'ai_reader'@'10.0.%';

The host part ('10.0.%' here) limits where the user can connect from. Use '%' only if the client’s address is unknown. Never grant FILE, PROCESS, SUPER or anything ON *.*.

MySQL has no per-user statement timeout, so have the client run SET SESSION max_execution_time = 30000 (milliseconds). It can also run SET SESSION TRANSACTION READ ONLY. On MariaDB, put the limit on the user instead:

CREATE USER 'ai_reader'@'10.0.%' IDENTIFIED BY 'choose-a-strong-password'
  WITH MAX_USER_CONNECTIONS 5 MAX_STATEMENT_TIME 30;

SQL Server and Azure SQL

CREATE LOGIN ai_reader WITH PASSWORD = 'Choose-a-strong-password1';

USE SalesDb;
CREATE USER ai_reader FOR LOGIN ai_reader;

-- One schema, including tables added to it later
GRANT SELECT ON SCHEMA::sales TO ai_reader;
-- Or every table and view in the database:
-- ALTER ROLE db_datareader ADD MEMBER ai_reader;

-- Keep columns out
DENY SELECT ON sales.Customers (Email, Phone) TO ai_reader;

On Azure SQL Database you can create a contained user in the database instead of a server login: CREATE USER ai_reader WITH PASSWORD = '...';. Do not add the user to db_owner or db_datawriter, or grant EXECUTE.

SQL Server has no per-session read-only switch. If you run an Always On availability group with a readable secondary, connect with ApplicationIntent=ReadOnly so the AI’s queries go there. Resource Governor (Enterprise edition) can cap the login’s CPU and memory.

Oracle

CREATE USER ai_reader IDENTIFIED BY "Choose-a-strong-password1";
GRANT CREATE SESSION TO ai_reader;

-- READ, unlike SELECT, does not allow SELECT ... FOR UPDATE or LOCK TABLE
GRANT READ ON app_owner.orders TO ai_reader;
GRANT READ ON app_owner.products TO ai_reader;

-- Oracle 23ai can grant a whole schema at once:
-- GRANT SELECT ANY TABLE ON SCHEMA app_owner TO ai_reader;

-- Limits (CPU and read limits need RESOURCE_LIMIT = TRUE)
CREATE PROFILE ai_limits LIMIT
  SESSIONS_PER_USER 5
  CPU_PER_CALL 6000
  LOGICAL_READS_PER_CALL 50000000;
ALTER USER ai_reader PROFILE ai_limits;

Never grant SELECT ANY TABLE without ON SCHEMA: that covers every table in every schema of the database. To hide a column, create a view in the owner’s schema without it, and grant the view.

ClickHouse

CREATE USER ai_reader IDENTIFIED BY 'choose-a-strong-password';
GRANT SELECT ON shop.orders TO ai_reader;
GRANT SELECT(id, city, created_at) ON shop.customers TO ai_reader;

CREATE SETTINGS PROFILE ai_limits SETTINGS
  max_execution_time = 30,
  max_concurrent_queries_for_user = 5
TO ai_reader;

A readonly = 1 setting in the profile makes the user read-only at the server, but it also blocks the client from changing any setting. readonly = 2 still refuses writes while allowing settings, such as per-query time limits, to be changed.

Snowflake

CREATE ROLE AI_READER;
GRANT USAGE ON WAREHOUSE AI_WH TO ROLE AI_READER;
GRANT USAGE ON DATABASE SHOP TO ROLE AI_READER;
GRANT USAGE ON SCHEMA SHOP.SALES TO ROLE AI_READER;
GRANT SELECT ON ALL TABLES IN SCHEMA SHOP.SALES TO ROLE AI_READER;
GRANT SELECT ON FUTURE TABLES IN SCHEMA SHOP.SALES TO ROLE AI_READER;

CREATE USER AI_SERVICE TYPE = SERVICE
  DEFAULT_ROLE = AI_READER DEFAULT_WAREHOUSE = AI_WH
  RSA_PUBLIC_KEY = 'paste-your-public-key';
GRANT ROLE AI_READER TO USER AI_SERVICE;

Give the AI its own small warehouse with STATEMENT_TIMEOUT_IN_SECONDS and a resource monitor, so its queries have a cost ceiling and never compete with yours.

Check that it really is read-only

Log in as the new user and try something harmless that writes, such as CREATE TABLE ai_test (id int). It should fail with a permission error. Then list what the user holds:

Engine	Check
PostgreSQL	SELECT table_schema, table_name, privilege_type FROM information_schema.role_table_grants WHERE grantee = 'ai_reader';
MySQL / MariaDB	SHOW GRANTS FOR 'ai_reader'@'10.0.%';
SQL Server	EXECUTE AS USER = 'ai_reader'; SELECT * FROM fn_my_permissions(NULL, 'DATABASE'); REVERT;
Oracle	SELECT * FROM dba_tab_privs WHERE grantee = 'AI_READER'; and the same on dba_sys_privs

What a read-only user does not cover

A read-only user is the strongest single control you hold, because it applies whatever software connects with it. It still leaves gaps:

• It reads everything you granted, and an AI agent passes what it reads to its model provider.
• Grants drift. Someone adds a role to fix an urgent issue, and the user quietly gains write access.
• Reads can be expensive, and a single agent can open many of them.
• There is no record of which person asked the agent for which query, unless something in between keeps one.

Using it with Datablare

Datablare connects AI agents such as Claude, ChatGPT, Cursor and VS Code to your database over MCP. It adds the layers a login cannot. When you add a source, it checks whether the login can write and warns you if it can. The setup screen gives you the read-only SQL for your engine, including limits. Every query then passes a guard that refuses anything but one read. It runs in a read-only session, or in an always-rolled-back transaction on SQL Server and Snowflake. And it is recorded in Audit with the person who asked. You choose tables and hide columns per project, on top of the login’s own grants.

The flow: sign up, add your database with the user you created above (or try the e-commerce sample first), choose tables and hide columns, copy the project’s MCP link from the Connect page, add it to your AI tool, ask, and see the query in Audit. Engine-specific notes are on the PostgreSQL, MySQL, SQL Server and Oracle pages. You can create a free account and see security for what is enforced and what is stored.