Accurate AI-generated SQL still needs separate access checks

Correct SQL can still disclose data to the wrong person

A sales rep asks for “quarterly sales for my hospitals” and receives figures for every hospital in the region. In this hypothetical example, the SQL query, the database instruction used to retrieve the data, runs correctly and calculates the requested total, but exposes information beyond the rep’s assigned accounts. For an engineering team evaluating an analytics assistant, execution and calculation checks therefore leave a separate question: was this person entitled to receive those rows?

Drawing on a few years of data experience, a builder used text-to-SQL, which converts a natural-language question into a database query, to analyze healthcare engagement and field-performance data in plain English. The builder reported that recurring analytics turnaround fell from days to minutes. As the tool’s developer, the builder has an interest in the approach’s effectiveness.

The faster turnaround made the demonstration useful, but a working demonstration could still hide missing access controls. A typical development check submits a question, confirms that the generated SQL runs, and compares the output with an expected answer. All those checks can pass even when the requester lacks permission to receive the result. Territory, assigned-account, and business-unit restrictions therefore need their own place in the evaluation.

Authorization needs the requester’s context

To enforce those restrictions, the builder identifies authorization, deciding what a person may access, as the central challenge. The proposed approach combines information about the requester with access rules enforced in code outside the model. Those rules can change a generated query to fit the requester’s permitted scope. The system can then return useful information within those boundaries.

Requester information matters because a role can leave individual boundaries unresolved. Two sales representatives may share a role while covering different territories or assigned accounts. Role-based access control, which grants permissions through roles, can allow both representatives to use sales data; their assignments then determine which records each may receive. The builder’s approach uses those assignments alongside the broader role permissions.

A prompt instruction such as “only show my territory” leaves that decision dependent on the model’s interpretation. The builder warns that a difficult question can cause the model to ignore, forget, or misunderstand the restriction. Enforcing rules in code moves the access decision outside that interpretation process. The model proposes SQL, and a separate policy check determines whether the request may proceed.

A separate check also creates options beyond allowing or rejecting the entire request. The builder favors changing a query when doing so preserves an answer within the user’s permissions. This need extends to multi-tenant applications, where customers share a system, as well as human resources (HR) systems and finance tools. In each case, access can depend on the requester’s organization, employee group, or financial scope.

The benchmark measures policy changes, leakage, and processing time

To evaluate that approach, the builder reports a benchmark of 900 test queries with two numerical breakdowns: an intervention tally and a query-disposition breakdown. The intervention tally counts policy changes, most often the addition of a row filter to the WHERE clause. A WHERE clause sets conditions that records must satisfy before a query returns them. The tally also includes column masking, which conceals restricted values, and scope restriction.

The disposition breakdown describes how requests moved through the workflow. Its categories include execution without changes, query modification, referral to a human reviewer, and blocking; modifications include a join rewrite, which changes how tables are combined. For an engineering lead, these outcomes distinguish requests handled automatically from those requiring another decision. The builder reports the following shares:

Queries that proceeded unchanged still passed through an authorization decision. The policy check determined whether each query fit the requester’s permissions, so a compliant query needed no change. Counting rewrites alone would therefore miss successful decisions to permit the original SQL. What mattered was whether the resulting access stayed within policy.

The builder also compared plain text-to-SQL, static role-based controls, and policy-aware processing for unauthorized disclosure. Some plain text-to-SQL queries returned up to about 80 unauthorized rows. Static role-based controls also leaked information, which the builder attributes to their lack of requester-specific context. The policy-aware approach returned no unauthorized rows in the evaluation.

Policy processing added about 6.5 milliseconds per query, according to the builder, who describes the delay as practically unnoticeable. This measurement puts processing time alongside access outcomes when assessing the workflow. A production evaluation can measure the same two things: what information the system disclosed and how much time the policy decision added.

Establish access rules before tuning SQL accuracy

The implementation order follows from the access problem: the builder recommends establishing identity and access context, getting the rules right, and then tuning accuracy. The builder expects models to keep improving at SQL generation. As generation improves, those rules still define the information a particular person may receive. Establishing them early gives accuracy testing a clear target: a useful answer within the requester’s permitted scope.

To establish that scope, the application first identifies the requester and retrieves their role, business unit, territory, and assigned accounts. These attributes connect broad permissions to the individual’s responsibilities. For the sales rep in the opening example, the assigned accounts determine what “my hospitals” may include. The application can then use that decision to control both model input and query execution.

The first control limits the schema information shown to the model: the tables, columns, and relationships it can use to construct SQL. The builder recommends exposing only permitted tables and columns. Generation therefore starts with information selected through the requester’s identity and access rules. This also reduces the restricted database information included in the model’s context.

Field-level metadata makes that selection more precise. The builder recommends tagging sensitive fields such as customer IDs, contract terms, and salaries in a metadata catalog, an inventory describing database fields and their properties. The application can use those tags to keep sensitive fields out of the information supplied to the model. This selection happens before generation, as the application prepares the model’s view of the database.

Restricted input still leaves a separate enforcement task after generation. A model working from permitted tables can produce SQL that exceeds a user’s row scope or combines data in an unacceptable way. The application therefore parses the generated SQL, examines its structure, and applies access rules in code before execution. This check connects the proposed query to the requester’s context.

When a query needs a change, the policy processor can add a row condition to the WHERE clause, mask columns, or narrow the query’s scope. It can also rewrite a join to respect access rules. A compliant query proceeds unchanged, a request needing judgment goes to a human reviewer, and an impermissible request is blocked. These decisions control which SQL may reach the database.

Before execution, the builder also recommends validating every query against explicit rejection rules. These include rejecting SELECT *, which requests every column, along with queries that cross scope boundaries, return oversized result sets, or use risky joins. Execution depends on the request’s properties. The model’s confidence or explanation of its intent cannot substitute for passing those checks.

Once a query passes, execution goes through a gateway that connects with a narrowly privileged database account. The builder presents the account as another limit on what the execution path can do if faulty generation or prompt injection, input designed to redirect the model, produces an unsafe request. Its database permissions set the outer boundary for execution. Requester-specific policy then determines the records and fields available within that boundary.

Production reviews must test entitlement and retain access records

With those controls in place, production testing needs to examine entitlement separately from calculation accuracy. A query can return the expected number while exposing unauthorized records, so reviewers need to inspect both the answer and the access it required. The builder’s comparison of plain text-to-SQL, static roles, and contextual policy provides a structure for that assessment. Each approach can be tested for correctness, unauthorized disclosure, and policy-processing overhead.

To test those boundaries, reviewers can repeat the same analytics request while changing the requester’s territory or assigned accounts. They should then check whether the generated SQL and returned information fit each permitted scope, including cases where the query proceeds unchanged. Timing those checks under the team’s own execution conditions adds the operational measurement. Together, these tests show whether the implementation preserves permissions as requester context changes.

After execution, the access decision also needs a record that another person can examine. The builder recommends recording the original question, user context, policy applied, final SQL, and result. These records connect the request to the rule governing access and the information returned. Keeping the final SQL alone would leave out the identity and policy context needed to explain why execution was permitted.

That explanation supports a concrete compliance inquiry: which person received which information, and why was access allowed? The builder’s audit-readiness goal is to answer in “minutes, not weeks,” giving the compliance team a usable history of the decision. The record supports that investigation; preventing disclosure remains the job of the access controls applied before execution. Retaining the full decision history lets reviewers examine an individual access event without reconstructing it from incomplete application logs.

Key executive takeaways

• Test access alongside accuracy: Analytics owners need separate checks for calculation correctness and the requester’s right to receive the results. A query can run correctly while exposing records outside a user’s assigned accounts or territory.
• Enforce requester-specific permissions: Application developers need access rules enforced in code outside the model. Combine roles with business unit, territory, and account assignments to determine each user’s permitted scope.
• Measure disclosure and processing time: The builder’s 900-query evaluation reported no unauthorized rows under policy-aware processing, with about 6.5 milliseconds of added processing time per query. Engineering reviewers can benchmark disclosure, query changes, and latency under their own conditions.
• Establish access controls before tuning accuracy: Platform owners need a controlled path from schema exposure through query validation and execution. Limit visible fields, inspect generated SQL, apply policy changes, and execute through a gateway using a narrowly privileged database account.
• Retain the full access decision: Application owners need records linking the original question, requester context, applied policy, final SQL, and result. This history gives compliance reviewers the evidence to establish who received which information and why access was allowed.