Text to SQL and Data Agent Security: Safe DB Access

A text-to-SQL assistant or data agent turns a sentence typed by anyone, or text planted in a document the agent reads, into a query that runs with whatever database rights the agent holds. Analytics copilots, "chat with your data" features and autonomous data agents are spreading fast across warehouses and operational databases, and the model itself cannot be made reliably obedient. The controls that work sit outside the model: the database identity the agent uses, row-level security tied to the real user, validation of every generated statement before it runs, a semantic layer that narrows what can be asked, and an audit trail that names both the user and the agent. This guide is for data platform engineers, security architects and identity teams building or approving these systems. Output handling in general is covered in the OWASP LLM Top 10 LLM05 Improper Output Handling Guide; this page goes deeper on databases.

Key takeaways

The database is the enforcement point. Prompts that say "only run SELECT" are instructions, not permissions; grants, read-only transactions and row policies are.
Give the agent its own narrow identity. A dedicated, read-only, schema-limited role per agent and environment, never a shared application or administrator account.
Carry the user into the query. Row-level security only protects data when the end user's identity reaches the database, through per-user credentials or a tamper-proof session context.
Validate generated SQL before execution. Parse it, allow one statement of an approved type, check tables and columns against an allowlist, and add limits and timeouts.
Log the question, the SQL and the identity together. Without the prompt and the agent identity beside each query, an investigation cannot tell a bad question from a hijacked agent.

How text-to-SQL and data agents fail

OWASP's LLM Top 10 for 2025 names the core problem directly: in LLM05 Improper Output Handling, LLM-generated SQL executed without proper parameterisation leads to SQL injection, and one example scenario is a chat feature asked to delete all database tables. LLM06 Excessive Agency adds the identity half: an extension meant only to read data connects with an identity that also holds UPDATE, INSERT and DELETE. Data agents combine both, and add indirect prompt injection, because they read table contents, documents and tickets that an attacker may have written.

Failure mode	How it happens	Primary control	Reference
Destructive or write statements	User asks for, or injected text induces, DELETE, UPDATE, DROP or TRUNCATE	Read-only role and read-only transactions; statement type allowlist	OWASP LLM05, LLM06; ATT&CK T1485
Cross-tenant or cross-user data access	Generated query omits a tenant filter or joins another customer's rows	Row-level security bound to the end user, not to the agent	NIST SP 800-53 AC-3
Sensitive column exposure	Query selects salary, health or credential columns the user should not see	Column grants, views or masking; column allowlist in validation	OWASP LLM02; CSF 2.0 PR.DS-10
Indirect prompt injection	Instructions stored in a row, comment or document steer the agent's next query or tool call	Least privilege, no write or egress tools beside query tools, human approval	OWASP LLM01; ATLAS AML.T0051
Bulk extraction	Agent returns millions of rows or is used as an exfiltration path	Row limits, result size caps, egress review	ATT&CK T1213.006; ATLAS AML.T0086
Resource exhaustion	Unbounded joins or cross products consume warehouse credits or lock tables	Statement timeouts, cost limits, dedicated small compute	OWASP LLM10; SP 800-53 SC-5
Schema and credential leakage	Connection strings or role design placed in the system prompt are disclosed	Keep credentials and permission structure out of prompts	OWASP LLM07

See the OWASP LLM Top 10 LLM06 Excessive Agency Guide and the Prompt Injection Guide for the general patterns.

Choosing an architecture

How much freedom the model has to write SQL is the single biggest design decision. Less freedom means fewer answerable questions and much less risk.

Pattern	How it works	Best for	Main trade-off
Parameterised templates or tools	The model chooses from named queries (for example `get_orders_by_customer`) and supplies parameter values only	Customer-facing assistants, operational databases, regulated data	Only anticipated questions can be answered
Semantic layer	The model asks for governed metrics and dimensions; the layer generates the SQL	Business analytics on a warehouse with agreed metric definitions	Needs a maintained model of metrics and relationships
Constrained free SQL	The model writes SQL against an allowlisted set of views; a validator checks it before execution	Internal analysts exploring curated data	Validator quality decides safety; more review effort
Unconstrained free SQL	The model writes any SQL against the schema with a broad role	Personal sandboxes on synthetic or copied data only	Unsuitable for production or shared data

Decision guidance: start customer-facing and operational use cases with templates; use a semantic layer for business intelligence; allow constrained free SQL only for trained internal users on read replicas or curated views. Small organisations with one database can get most of the benefit from a read-only replica, a dedicated role and a short view allowlist. Large organisations with many data products should standardise on a semantic layer and a shared query gateway so the same validation and logging apply to every agent.

A dedicated, read-only database identity

The agent's database credential is a non-human identity and should be managed as one: owned, scoped, rotated and inventoried. See the Non-Human Identity (NHI) Guide.

One role per agent and environment: separate roles for development, staging and production, and for each agent or use case. The OWASP SQL Injection Prevention Cheat Sheet advises separate database users per application function and never assigning DBA or admin access to application accounts.
SELECT only, on views: grant SELECT on curated views or a reporting schema, not on base tables, and grant nothing on schemas the agent does not need. Views limit both columns and rows.
Read-only transactions as a backstop: in PostgreSQL, set default_transaction_read_only = on for the agent role (ALTER ROLE ... SET); a read-only transaction cannot alter non-temporary tables, so a write that slips past the validator still fails. Point the role at a read replica where one exists.
Timeouts per role: set statement_timeout and idle_in_transaction_session_timeout on the agent role. PostgreSQL documents both as disabled (0) by default and advises against setting statement_timeout globally in postgresql.conf, so set it on the role instead.
Fixed search path: set an explicit search_path for the role (for example the reporting schema and pg_catalog). The PostgreSQL documentation notes the default path is suitable only for a single user or a few mutually trusting users.
Warehouse roles: in Snowflake, give the agent a dedicated role with USAGE on one small warehouse and SELECT on specific objects. Cortex Analyst requests need the SNOWFLAKE.CORTEX_USER database role, which is granted to PUBLIC by default, or the narrower SNOWFLAKE.CORTEX_ANALYST_USER; review whether PUBLIC should keep the former. See the Service Accounts in Snowflake Guide.
Credentials outside the prompt and code: store the connection secret in a secrets manager, prefer short-lived or key-pair authentication, and never place connection strings or role names in the system prompt, as OWASP LLM07 warns. See the Secrets Management Guide.

Row-level security and user context

A read-only role stops writes, not over-reading. If an agent serves many users through one connection, every user sees whatever the agent role sees unless the database knows who is asking. Database engines below are listed alphabetically; each enforces row filtering in the engine, so it applies whatever SQL the model writes.

Engine	Mechanism	Passing the end user	Points to watch
PostgreSQL	`ALTER TABLE ... ENABLE ROW LEVEL SECURITY` and `CREATE POLICY` with `USING` (read) and `WITH CHECK` (write); restrictive policies combine with AND	Per-user roles (`current_user` in the policy), or a session setting read by the policy	Superusers, `BYPASSRLS` roles and table owners bypass policies unless `FORCE ROW LEVEL SECURITY` is set; no policy means default deny
Snowflake	`CREATE ROW ACCESS POLICY` returning BOOLEAN, attached with `ALTER TABLE ... ADD ROW ACCESS POLICY`; masking policies for columns	`CURRENT_ROLE()` or `IS_ROLE_IN_SESSION`, often with a mapping table of entitlements	The policy is evaluated with the policy owner's role; row access policies are evaluated before masking policies
SQL Server and Azure SQL	`CREATE SECURITY POLICY` with filter predicates (hide rows) and block predicates (reject writes) from an inline table-valued function	`sp_set_session_context` with `@read_only = 1` so the user ID cannot be changed on a pooled connection	Policies apply to dbo and sysadmin unless the predicate allows them; Microsoft warns of error-based side channels

Availability: Snowflake documents row access policies as requiring Enterprise Edition or higher. PostgreSQL row security is part of the core server. Microsoft documents row-level security from SQL Server 2016 (13.x), with filter predicates only in Azure Synapse Analytics and Microsoft Fabric.

The user identity must come from the authenticated session of your application, never from text the model produced. If the agent can call a tool that sets the session user, injected instructions can change it. The engine guides go further: see the PostgreSQL Security Guide, the Snowflake Security Guide and the SQL Server Security Guide.

Validating generated SQL before it runs

Parameterisation, the first defence in the OWASP cheat sheet, protects values but cannot cover table names, column names or keywords, which is exactly what a model generates. The cheat sheet's answer for structural choices is to map them to allowed literals and reject anything else. For text-to-SQL that becomes a validation gate between the model and the database:

Parse, do not pattern-match: parse the statement with a parser for your SQL dialect and work on the syntax tree. Keyword blocklists miss comments, casing tricks and dialect features.
One statement, approved type: reject multiple statements and anything other than SELECT (or the specific read statements you allow), including DDL, DML, GRANT, COPY, CALL and session commands.
Allowlist objects: every table, view and column referenced must be on the agent's allowlist; reject system catalogues unless needed.
Restrict functions: allow a known set of functions and reject the rest, especially functions with side effects or that read files or reach the network; also revoke EXECUTE on such functions from the role.
Bound the result: inject or enforce a row limit and a result size cap, and reject queries without a filter on very large tables.
Bind values: where the model supplies literal values, move them into bind parameters before execution.
Explain before run (optional): for expensive warehouses, run the engine's explain or cost estimate and refuse queries above a threshold.

Validation is a second line, not the first. A validator bug should still meet a read-only role and row policies underneath it.

Semantic layers and governed metrics

A semantic layer replaces "write SQL against these 400 tables" with "ask for these 40 metrics and dimensions". It narrows the attack surface and improves answer quality because definitions such as revenue or active customer are agreed once.

Snowflake Cortex Analyst: Snowflake recommends semantic views that define logical tables, dimensions, facts, metrics, relationships and verified queries. The documentation says generated SQL runs in the customer's warehouse and must adhere to established RBAC access controls. It also notes that anyone with access to the stage holding a legacy semantic model YAML file can use that model, so restrict stage access as well as table access. See the Snowflake Cortex AI Security Guide.
dbt Semantic Layer: metrics are defined once in the dbt project and served by MetricFlow; dbt states AI tools can connect through the dbt MCP server to use these governed metrics rather than raw tables.
Verified queries as tests: pairs of questions and approved SQL guide generation and double as a regression suite. Run them after every model, prompt or schema change.

A semantic layer does not replace database permissions: the identity that executes the generated SQL still needs least privilege and row policies.

Audit, detection and measurement

Log one record per agent query that joins what the model was asked with what the database did:

Application side: user ID, agent ID and version, the natural language question, retrieved context sources, generated SQL, validator decision and reason, rows returned and latency.
Database side: the engine's own query history or audit log for the agent role, so records exist even if the application logging fails. Correlate with a query tag or comment carrying the request ID.
Alerts: validator rejections of write or DDL statements, queries touching tables outside the allowlist, row counts far above the user's baseline, repeated permission errors (probing), and any change to row policies or the agent role's grants, which Microsoft's RLS guidance specifically says to monitor.

Useful metrics: percentage of agent queries executed under a per-user or session-bound identity; validator rejection rate by reason; number of agent roles with any write privilege (target zero); median and 99th percentile rows returned per query; and pass rate of the verified query suite after each change. Feed agent telemetry into the wider programme described in the Agent Controls and Monitoring Guide.

Implementation approach

Inventory: list every text-to-SQL feature, data agent and MCP database tool, the database each reaches and the credential it uses.
Re-credential first: replace shared or admin credentials with dedicated read-only roles, timeouts and fixed search paths; point them at replicas or curated views.
Bind users: enable row-level security on multi-user data and pass the end user through per-user credentials or read-only session context. Test with two users who should see different rows.
Add the validator in log-only mode: record what it would block for two to four weeks, tune the allowlists, then switch to enforce.
Move to templates or a semantic layer: for high-volume and customer-facing use cases, replace free SQL as definitions mature.
Test adversarially: plant instructions in a row the agent will read, ask for other users' data, ask for deletes, and request unbounded queries; every one should fail at the database or validator.
Rollback plan: keep the feature flag to disable query execution while leaving retrieval and explanation working, and keep the previous role grants in version control.

Framework mapping

Control	NIST SP 800-53 Rev 5	NIST CSF 2.0	Threat references
Dedicated read-only agent role	AC-6 Least Privilege	PR.AA-05	OWASP LLM06; ATT&CK T1485
Row-level security bound to the user	AC-3 Access Enforcement	PR.AA-05, PR.DS-10	ATT&CK T1213.006
Generated SQL validation	SI-10 Information Input Validation	PR.DS-10	OWASP LLM05; ATLAS AML.T0053
Timeouts and cost limits	SC-5 Denial-of-service Protection	PR.DS-10	OWASP LLM10
Query audit and review	AU-12, AU-6	PR.PS-04	ATLAS AML.T0086

Common failure patterns

Prompt rules instead of permissions: the Replit AI agent database deletion happened during a declared code freeze because the agent still held write access to production; the instruction not to change anything was not a control.
Over-scoped tokens within reach: in the PocketOS database deletion, an agent found an account-scoped platform token in an unrelated file and deleted a production database and its backups. Agents should only ever see credentials scoped to one environment and purpose.
One shared connection for all users: the agent queries as a service account with access to every tenant, so any user can ask for any customer's data.
Owner or superuser connections: the agent connects as the table owner or a bypass role, so PostgreSQL row policies silently do not apply.
Keyword blocklists as validation: regex checks for "DROP" or "DELETE" give false confidence and miss dialect features and stacked statements.
Logging only the answer: the chat transcript is kept but not the SQL, the role or the rows returned, so exposure cannot be scoped after an incident.

Practitioner checklist

Inventory every text-to-SQL feature, data agent and database tool with its credential and owner.
Give each agent and environment a dedicated SELECT-only role on curated views, with no admin, owner or bypass rights.
Set read-only transactions, statement and idle timeouts and a fixed search path on agent roles.
Enable row-level security on multi-user data and pass the authenticated user, never a model-supplied value.
Parse and validate every generated statement: one statement, approved type, allowlisted objects and functions, row limits.
Prefer templates or a semantic layer for customer-facing and high-volume use cases.
Keep credentials and permission structure out of prompts and store secrets in a secrets manager.
Log question, SQL, validator decision, identity and rows returned, correlated with database audit logs.
Run adversarial and verified query tests after every model, prompt or schema change.

Text to SQL and Data Agent Security: Safe AI Access to Databases