Agent-Generated SQL: A Test Pack for Permissions and Wrong Answers

An agent-generated query can be syntactically valid and still expose another customer’s rows, truncate a result without warning, or multiply a metric through a faulty join. A useful test pack therefore checks more than whether SQL runs. It checks who can see which records, whether the result fits the intended limit, and whether the returned numbers mean what the user asked.

This tutorial builds those checks around a small hypothetical orders dataset. The examples use SQL and Python-like test code, but the test design can be adapted to another warehouse, database driver or analytics application. The code is illustrative: connect the placeholder functions to your own test environment before use, and do not run generated queries against production while developing the pack.

1. Prerequisites and test boundary

You need a non-production database or isolated schema with deterministic fixture data, a way to execute a query under a specific tenant identity, and a test runner that can compare returned rows with expected results. If your application applies row-level security (RLS), the test identity must pass through the same enforcement path used by the application. A developer’s unrestricted database account is not an adequate substitute.

Superset’s RLS filters apply to generated queries for configured datasets and subjects; SQL Lab and API access need separate validation, and embedding is not authorization. Apache Superset security documentation. If Superset is part of your deployment, use this as a reminder to test each relevant access path, not as a substitute for verifying your own configuration. For a deeper deployment treatment, see production patterns for row-level security in Apache Superset.

Prepare two fictional tenants, north and south, and give each a test principal. Each principal should be able to read its own rows through the application’s normal query route. Your fixture should also contain at least one other tenant’s row, so an isolation test has something real to detect. If the fixture contains only the active tenant, a broken access rule may appear to work simply because there is nothing else to leak.

Keep the test environment small enough to understand by inspection. Use fixed dates and known amounts rather than random records. Record how tenant identity is established—such as a trusted request context or a database role—and ensure the agent cannot choose or overwrite that identity by adding a SQL predicate of its own. The test pack validates behavior; it does not make an unsafe execution architecture safe.

Before proceeding, identify the complete route that a generated query follows: agent output, query validation, application identity binding, database execution, result shaping and response. Include any analytics interface through which users can submit or view queries. A test that stops at SQL text inspection cannot prove that the deployed execution route enforces the intended boundary.

2. Set up a deliberately small fixture

Use a schema with one order row per order and one or more line-item rows per order. Keeping order facts and item facts separate makes the join test meaningful. The example below assumes an orders table with order_id, tenant_id, order_date and status, plus an order_items table with order_id, product_id, quantity and unit_price.

The fixture should make expected totals easy to calculate. For tenant north, create two completed orders in the date range: order 101 worth $30 and order 102 worth $20. Give order 101 two item rows worth $10 and $20; give order 102 one item row worth $20. Add a cancelled order worth $90 to north, and at least one completed order for south worth $500. These deliberately contrasting values expose both status-filter omissions and cross-tenant leaks.

A concise seed sketch follows. Adapt types and syntax to your database. The values are illustrative and are not a claim about any particular schema or connector.

INSERT INTO orders (order_id, tenant_id, order_date, status) VALUES
  (101, 'north', DATE '2026-08-03', 'completed'),
  (102, 'north', DATE '2026-08-04', 'completed'),
  (103, 'north', DATE '2026-08-05', 'cancelled'),
  (201, 'south', DATE '2026-08-03', 'completed');

INSERT INTO order_items (order_id, product_id, quantity, unit_price) VALUES
  (101, 'A', 1, 10.00),
  (101, 'B', 1, 20.00),
  (102, 'A', 2, 10.00),
  (103, 'C', 1, 90.00),
  (201, 'D', 5, 100.00);

For this fixture, completed revenue for north during the specified dates is $50: $30 from order 101 and $20 from order 102. There are two qualifying orders. The cancelled order and the south order are purposeful negative controls. This arithmetic is an illustrative expected result, not an external benchmark.

Make fixture setup repeatable. Each test run should begin from a known state, and cleanup should not depend on a test having passed. If your database cannot cheaply reset a schema, use a unique test namespace or a transaction strategy that your execution route supports. Confirm that the test principal sees the fixture through the real authorization mechanism before adding agent-generated SQL to the loop.

3. Define the request and expected answer before testing SQL

Write down the business question in precise terms before asking an agent to generate a query. For the first scenario, use: “For tenant north, show completed revenue and the number of completed orders from August 1 through August 31, 2026.” The tenant should come from trusted application context, not from a user-controlled SQL fragment. The dates, status definition, revenue formula and aggregation grain should be explicit in the test case.

Translate the request into an expected-result record. For example: tenant north; inclusive start date August 1; exclusive end date September 1; status completed; order count 2; revenue 50.00; currency represented by the fixture’s numeric unit. An exclusive end date avoids ambiguity about timestamps late on the final day. If the production metric uses a different time-zone or currency convention, encode that convention in the fixture and expected result instead of silently assuming it.

A small test-case object might look like this:

case = {
    "tenant_context": "north",  # injected by the trusted test harness
    "start_date": "2026-08-01",
    "end_date_exclusive": "2026-09-01",
    "status": "completed",
    "expected_order_count": 2,
    "expected_revenue": 50.00,
}

Treat this object as the oracle for the test, not as instructions the agent can rewrite. The generated SQL can be inspected and executed, but it cannot update the expected amount, switch the tenant context, or redefine “completed” to make its own answer pass. That separation is essential: comparing an answer with expectations derived from the same generated query only confirms that the query agrees with itself.

A semantic layer can help keep business metric definitions consistent across agent-generated questions, but its design is a separate concern from the negative tests here. See how a semantic layer can keep business metrics consistent for agents. Regardless of where definitions live, this test pack still needs independent expected values and access assertions.

4. Step one: capture and inspect the generated SQL

Send the test request through the same query-generation path used by the product, then save the returned SQL alongside the test case. Keep the prompt, tenant context, date range, generated text, execution identity, result rows and assertion outcomes together as one test record. This makes failures diagnosable without treating the model’s explanation as proof that its query is correct.

First apply mechanical checks before execution. Reject statements that are not a single permitted read query, references outside the test schema, unexpected table names, or attempts to change session state. The exact parser and policy depend on your database and application; a regular expression alone is not a reliable SQL security boundary. Parameterize values where the execution path supports it, and do not treat a tenant predicate written by the agent as the source of tenant authorization.

For the example question, a plausible query shape aggregates orders at the order grain and limits tenant, dates and status. The following is an illustrative target, not a required canonical query:

SELECT
  COUNT(*) AS order_count,
  SUM(order_total) AS revenue
FROM (
  SELECT
    o.order_id,
    SUM(i.quantity * i.unit_price) AS order_total
  FROM orders AS o
  JOIN order_items AS i ON i.order_id = o.order_id
  WHERE o.tenant_id = :tenant_id
    AND o.order_date >= :start_date
    AND o.order_date < :end_date_exclusive
    AND o.status = :status
  GROUP BY o.order_id
) AS per_order;

The parameters in this sketch are placeholders. Bind them through your application’s supported parameter mechanism; do not interpolate arbitrary user text into SQL. Some database systems require different syntax or query structure. The important test conditions are the intended grain, filters, join relationship and independently verified result—not copying this exact statement.

Expected output after the checks in this tutorial is one row with order_count = 2 and revenue = 50.00. If the query fails inspection, stop before execution and record the reason. Do not “fix” a failing test by loosening the checks until a risky query happens to pass; change the query policy or test only when the underlying requirement has genuinely changed.

5. Step two: test tenant isolation with a negative case

Run a cross-tenant test in which the active principal is north, while the fixture includes south data. A successful test does not merely show that the SQL contains tenant_id = 'north'. It shows that the actual execution route cannot return south rows when the query omits the predicate, alters it, or tries a broader condition.

Use at least two related cases. In the positive case, a north principal requests its own completed revenue and receives the expected $50. In the negative case, attempt a query shape that would expose the south order if the authorization boundary were absent. Run it only in the isolated test environment, under the north principal and through the normal application path. The expected outcome is either a denial or a result containing no south records, according to the enforcement design. Record which outcome is intended; avoid accepting a vague “no error” as a pass.

A useful assertion is about observable data, not merely the generated text. If the negative query returns rows, assert that every returned row belongs to the tenant bound to the test principal. Also include a case that asks directly for another tenant’s identifier: the request must not change the principal’s scope. Avoid returning sensitive values in failure logs; a leaked row count and the test fixture’s synthetic identifiers are generally enough to locate the defect.

Keep the tenant identity outside the model-controlled query. For example, the harness may establish north as the execution identity and bind only the permitted request parameters. Whether your architecture enforces this through database roles, trusted query rewriting, an authorization-aware service, or another mechanism is a design decision for your system. Test the mechanism actually deployed rather than assuming a WHERE clause generated by an agent will always be present.

The following is intentionally framework-neutral pseudocode. Replace execute_as and assert_tenant_scope with functions in your own test harness. It illustrates assertions, not a ready-made security library.

# Illustrative pseudocode; wire to an isolated test database and real app route.
result = execute_as(
    principal="north",
    sql=generated_sql,
    parameters=trusted_parameters,
)

assert result.denied or all(row.tenant_id == "north" for row in result.rows)

Expected result: the north request returns only north-scoped data, or a deliberately disallowed query is rejected before any result is exposed. If an unfiltered query returns the south fixture row, mark the test failed even if the answer’s prose claims that it used the north tenant. The result and authorization behavior take precedence over the model’s explanation.

6. Step three: test row limits without confusing truncation with correctness

A row limit is a boundary on output size; it is not proof that the answer includes every record needed for the requested calculation. An agent may add LIMIT 10, receive ten rows, and still omit the eleventh qualifying row. Conversely, an aggregate query may return one row while representing thousands of source records. Test the limit policy separately from the semantic correctness of the answer.

Create a fixture where the expected qualifying row count is known. For a list request, add three qualifying records and configure the test case with a maximum of two returned rows. Define what the product should do when more than two rows qualify: reject the request as too broad, return a clearly marked truncated preview, or provide a separately verified aggregate. Do not let the model choose silently among those behaviors. The right policy depends on the user task, but the test needs one explicit expected outcome.

For an aggregate such as revenue, calculate the expected total over the complete qualifying set before applying any presentation limit. A query that first selects an arbitrary subset and then sums it should fail the expected-value assertion. If the database returns a capped result, include a truncation indicator or another explicit status in the application response; a plain number that looks complete is misleading when the input was incomplete.

Use boundary cases: zero qualifying rows, exactly the maximum, and one more than the maximum. For example, with a display cap of two, test 0, 2 and 3 matching rows. Assert both the returned row count and the declared result status. If ordering affects which two rows appear, specify a stable sort key in the expected behavior; otherwise two executions could return different previews while both appear to pass.

Expected outputs should distinguish completeness from size. A complete two-row response can be labeled complete. A three-row match capped to two should either be blocked or explicitly labeled partial according to the chosen design. A one-row aggregate is not “complete” merely because it is under a row cap: compare its value with the independently calculated fixture total.

7. Step four: catch incorrect joins and duplicated metrics

The join test should contain a counterexample that changes the answer, not just a query that is syntactically suspicious. In this fixture, order 101 has two item rows. Joining orders to items produces two joined rows for that order. If the query counts joined rows as orders, it counts order 101 twice. If it sums a stored order-level amount after the join, that amount can also be multiplied by the number of matching items.

For the requested metrics, the correct order count is two and revenue is $50. A flawed query such as COUNT(*) over the joined rows yields three item rows for the two qualifying orders. A different flawed query that sums order-level totals after joining can inflate order 101’s $30 total to $60, producing $80 rather than $50. These outputs are intentionally derived from the stated fixture; they illustrate two distinct fan-out errors.

Use separate assertions for each metric. COUNT(DISTINCT order_id) can address one counting error, but it does not automatically repair a duplicated sum. Summing item-level extended amounts can be correct when item rows are the authoritative revenue grain, while summing an order-level amount after a one-to-many join may not be. The test should encode the actual metric definition and grain, not reward a particular SQL idiom.

Add a control query for each expected value. For example, compute order count from orders with the tenant, date and status predicates, and compute revenue from item amounts grouped at the intended order grain. Compare the generated answer with those independent fixture-derived expectations. The control query is test machinery; do not use the generated query itself to decide what the correct result should be.

Check for missing join conditions as well as fan-out. A join only on a non-unique product identifier, for example, can associate rows from unrelated orders. The fixture can include the same product in more than one order so an incorrect join key visibly changes the result. Keep the primary relationship explicit in the fixture and expected behavior. If the production schema uses composite keys, include the relevant key components in both test data and assertions.

A compact assertion sketch makes the distinction clear:

# Illustrative pseudocode: values come from fixed fixture expectations.
assert result.order_count == 2
assert result.revenue == 50.00
assert result.complete is True

Expected output is not merely “query returned one row.” The test should identify the wrong count of three, the inflated revenue of $80, or any other mismatch as a failure with enough context to locate the metric and fixture case. Avoid rounding away meaningful differences during assertion; use the numeric precision and tolerance appropriate to the metric definition.

8. Step five: run the pack across execution paths

A test pack is only useful if it exercises the routes users can actually reach. Build a small matrix of entry points: the application’s agent response route, any direct query endpoint, and any analytics interface used for ad hoc access. For each route, record the principal, tenant context, query submitted, enforcement result and returned rows. Do not assume that a check in one interface automatically governs another.

Run the positive and negative tenant cases through each relevant route. Run the row-limit and join-result checks through every route that can execute generated SQL or shape its output. If a particular path cannot execute SQL directly, test its own authorization and result-handling boundary instead of marking it covered by a test from a different path.

Where Superset is part of the system, keep SQL Lab, API and embedded access as distinct test targets when they are in scope. The product’s security configuration and the application’s data boundary may differ by route. For Helm-based deployments, a production values file is deployment configuration, not evidence that the runtime policy behaves as intended; use the Superset Helm deployment reference for deployment context, then verify the resulting routes with your own tests.

A useful run record contains a stable test-case identifier, fixture version, route, principal, generated SQL fingerprint, parameter names, returned row count, expected and actual values, and pass/fail reason. Avoid retaining credentials or full real customer data in test output. In a synthetic fixture, include enough row identifiers to reproduce the failure while keeping the record focused on the assertion that failed.

Expected result: the same test case has an explicit outcome for every applicable route. “Passed in the UI” is not enough if an API path can return rows under a broader identity. If test outcomes differ by route, preserve that difference as a failure to investigate rather than averaging it into a single green status.

9. Step six: separate query approval from result verification

If a workflow requires human approval before a generated query runs, bind approval to the exact query payload and its execution context. A changed SQL statement, tenant, date range or parameter value should require a new decision. Approval should expire rather than remaining valid indefinitely. These controls reduce the chance that a reviewer approves one action while the system executes another; they do not prove that the approved SQL is accurate.

After execution, verify the business result independently. Compare the output with expected fixture values, check completeness status, confirm tenant scope and ensure that the result shape matches the requested metric. Do not infer correctness from a successful database response or a confident natural-language summary. Database success means the database accepted a statement; it does not mean the statement answered the intended question.

A practical test flow keeps access checks before result release and meaning checks after execution. The diagram shows the test harness, not a claim that a particular product implements these stages.

flowchart TD
    A["Test case"] --> B["Generate SQL"]
    B --> C["Execute as tenant"]
    C --> D["Check access boundary"]
    D -->|"Fail"| F["Reject and investigate"]
    D -->|"Pass"| E["Check rows and meaning"]
    E -->|"Fail"| F
    E -->|"Pass"| G["Return verified result"]

    accTitle: Agent SQL test decision flow
    accDescr: A test case produces SQL that runs as a tenant identity. Access failures are rejected; access passes proceed to row and meaning checks. Only results passing both checks may be returned.

In the flow, “execute as tenant” means the test environment uses the same trusted identity-binding design as the application, not that the agent selects its own scope. “Check rows and meaning” combines independent assertions for completeness, row limits and metric correctness. A failed access check should not proceed to result release; a result that passes access but fails meaning checks should also be withheld or clearly handled as an error according to the product’s response design.

For an implementation that needs help translating this test boundary into an analytics product, consider embedded analytics. The next step should be to map your actual routes and authorization context to the test cases above, not to assume that embedding itself provides access control.

10. Troubleshooting and failure analysis

The cross-tenant test passes, but only because no foreign rows are visible in the fixture. Confirm that the test identity can encounter the synthetic other-tenant record if enforcement is removed. Keep one known foreign record and verify that the test would detect it. A negative test with no counterexample is not evidence of isolation.

A query contains a tenant predicate, but the access test still fails. Check whether the execution route binds the tenant independently, whether the database principal is the one expected, and whether the query has an alternate path that bypasses the predicate. Compare the result with the trusted principal’s tenant. Do not simply add another model instruction and call the boundary fixed; the test needs to demonstrate that the execution path enforces the required scope.

The row-limit case returns the expected number of rows but the wrong subset. Specify deterministic ordering and test the over-limit state, not just the returned count. Then decide whether the user should receive a partial result, a refusal or an aggregate calculated across all matches. If the response gives no indication of truncation, the test should fail even when the displayed rows are individually valid.

Revenue fails only after adding a join. Inspect the grain of every selected measure. Identify whether a value is defined per order or per item, then compare the generated join cardinality with the fixture’s known rows. Test each metric independently; distinct-count logic may repair an order count while leaving a duplicated sum untouched.

The same test passes in one route and fails in another. Treat the route as part of the test case. Record which identity, filter and execution component each route uses, then repair the boundary that differs. Do not mark the whole product safe based on its most restrictive path.

The result is correct in the tiny fixture but suspicious in production-shaped data. Add a minimal counterexample for the suspected error: multiple items per order, repeated product identifiers, a boundary date, or more qualifying rows than the cap. Keep examples small enough that a reviewer can calculate the expected result manually. Larger synthetic volumes can test operational limits later, but they do not replace clear semantic counterexamples.

The model’s explanation says the query is correct, but an assertion fails. Preserve the generated SQL and actual output, and trust the independent assertion for test status. The explanation can help a developer understand intent, but it must not change the expected value or override an access failure.

11. Printable test worksheet and acceptance criteria

Use this worksheet for each question type you plan to support. Complete it before enabling the corresponding result path. “Not applicable” should include a reason; it should not become a way to leave a high-risk route untested.

• Question definition: Record tenant context, date boundaries, status rules, metric formulas, aggregation grain and expected response shape. Resolve ambiguous terms before SQL generation.
• Fixture controls: Include at least one valid in-scope row, one out-of-scope tenant row, and any status or join counterexamples that can change the requested answer. Calculate expected values independently.
• Identity path: Name the principal used for each route and identify where trusted tenant context is established. Confirm that generated SQL cannot select a broader identity.
• Cross-tenant negative case: Attempt the relevant unsafe query shape in isolation. Assert denial or zero out-of-scope rows, according to the defined policy. Fail on any foreign record.
• Row-limit boundaries: Test zero, exactly-at-cap and over-cap matches. Specify stable ordering, completeness signaling and the expected behavior when a request exceeds the cap.
• Join counterexample: Include a one-to-many relationship that exposes duplicated counts or sums. Assert each metric against fixed expected values at its intended grain.
• Independent result check: Compare actual output with fixture-derived expectations. Do not derive the oracle from the generated SQL or its explanation.
• Route coverage: Run each relevant case through every path that can execute queries or return results. Keep route-specific failures visible.
• Failure record: Capture the test identifier, route, principal, query reference, outcome and assertion reason without retaining unnecessary sensitive data.
• Release decision: Require all in-scope access and result assertions to pass before returning the answer as verified. Document any intentionally partial result and its user-visible status.

A completed worksheet is useful only if it is tied to a reproducible fixture and a real execution route. Keep the test case, expected values and route matrix under version control alongside the code that binds tenant identity and shapes results. When the schema, metric definition, access path or limit policy changes, review the affected tests rather than assuming old assertions still represent the product’s behavior.

For an initial rollout, prioritize the tests that can expose another tenant’s data, silently label partial output as complete, or materially alter a metric through a join. Add cases for other query patterns as they become supported. This prioritization does not replace broad testing; it gives engineering teams a defensible first boundary and makes each newly supported query shape carry an explicit correctness contract.

The central acceptance rule is straightforward: generated SQL is not ready to answer a user merely because it executes. The test pack should prove that the active tenant remains in scope, that output limits are handled honestly, and that the result matches an independent expectation for the requested metric. When any of those checks fails, withhold the result and investigate the specific boundary or query behavior that failed.