Field-Level Permission Checks for Text-to-SQL Systems

Length: About 3,500 words · Reading time: about 16–18 minutes

Field-level permission checks for Text-to-SQL systems determine whether a generated SQL query is allowed to access each column it references, not only whether the user can access the table. A safe Text-to-SQL workflow should detect sensitive fields in projections, filters, joins, aggregations, derived expressions, and lineage before the query reaches the database.

This matters because generated SQL often looks harmless at the table level. A user may be allowed to query a customers table for basic analytics, but not allowed to select email, filter by ssn_last4, join on device_id, or derive a segment from a restricted health, salary, or financial field. Table permissions alone cannot express these cases clearly enough for production AI data access.

Short Answer

Text-to-SQL systems need field-level permission checks because the LLM generates the exact SQL shape at runtime. The system must inspect the generated query before execution and ask:

Which fields does this query read, expose, filter on, join with, aggregate, derive, or pass into downstream outputs — and is this user allowed to use those fields for this purpose?

A practical field-level permission check should:

1. parse the SQL;
2. bind tables, aliases, CTEs, and columns to catalog metadata;
3. identify all field usages, not only selected output columns;
4. classify sensitive fields such as PII, financial data, HR data, credentials, or regulated attributes;
5. evaluate policies using user, role, purpose, environment, and query shape;
6. return a structured decision: allow, warn, deny, or approval_required;
7. write an audit record explaining which fields and policies affected the decision.

Key Takeaways

• Table-level access is not enough for production Text-to-SQL because sensitive data risk often lives at the column level.
• A generated query can expose restricted fields directly in SELECT, indirectly through filters, joins, aggregations, CASE expressions, or derived outputs.
• Field-level permission checks require catalog-aware SQL semantic analysis, not string matching.
• A policy engine should evaluate the query against user role, purpose, field labels, environment, and usage context.
• Useful decisions are explicit: allow, warn, deny, or approval_required.
• Field-level permissions are a bridge between SQL semantic validation, column-level lineage, LLM SQL Guard architecture, and SQL governance readiness assessments.

Why Table-Level Permissions Are Not Enough

Many database permission models begin with table access:

analyst can SELECT from analytics.customers
analyst can SELECT from analytics.orders
analyst cannot SELECT from raw.payment_cards

That is useful, but Text-to-SQL creates a more precise problem. A generated query may access an allowed table in an unsafe way.

For example:

SELECT
  customer_id,
  email,
  phone,
  lifetime_value
FROM analytics.customers
WHERE country = 'US';

A business analyst may be allowed to analyze customers by country, segment, or lifetime value. But the same user may not be allowed to retrieve raw email addresses or phone numbers. If the system only checks table access, the query may appear acceptable because analytics.customers is allowed.

Field-level permission checking asks a more specific question:

Can this user access analytics.customers.customer_id?  yes
Can this user access analytics.customers.email?        no
Can this user access analytics.customers.phone?        no
Can this user access analytics.customers.lifetime_value? maybe, depending on role and purpose

For Text-to-SQL, this distinction matters because the user did not hand-write the SQL. The model may choose fields that the user did not explicitly request, use SELECT *, or include sensitive fields because they seem useful for answering the prompt. The guard layer must check the actual generated SQL, not only the user’s natural-language intent.

Where Sensitive Fields Hide in SQL

A common mistake is to check only the final SELECT list. That misses many real permission risks.

Sensitive fields can appear in several places.

A Text-to-SQL system should treat these as different usage roles. Selecting email is not the same as filtering on email, and aggregating salary is not the same as returning every salary value. But all of them are field usage and should be visible to the policy engine.

Example 1: Direct Sensitive Field Exposure

A user asks:

Show the top customers in California.

The LLM generates:

SELECT
  customer_id,
  full_name,
  email,
  phone,
  total_spend
FROM analytics.customers
WHERE state = 'CA'
ORDER BY total_spend DESC
LIMIT 50;

The query is syntactically valid. The table may be allowed. But the model added contact fields that the user did not need.

A field-level permission result should identify the specific fields and the decision:

{
  "decision": "deny",
  "reason": "Query exposes PII fields not allowed for analyst role.",
  "field_access": [
    {
      "field": "analytics.customers.email",
      "usage": "projection",
      "labels": ["PII", "contact"],
      "policy": "deny_pii_projection_for_analyst",
      "effect": "deny"
    },
    {
      "field": "analytics.customers.phone",
      "usage": "projection",
      "labels": ["PII", "contact"],
      "policy": "deny_pii_projection_for_analyst",
      "effect": "deny"
    }
  ],
  "recommended_action": "Remove email and phone, or request approval under a permitted role."
}

This is more useful than a generic “permission denied” error. It tells the application, reviewer, or repair loop exactly which fields caused the decision.

Example 2: Sensitive Field Used in a Filter

Not all sensitive access appears in the output.

SELECT
  customer_id,
  total_spend
FROM analytics.customers
WHERE ssn_last4 = '1234';

The result does not display ssn_last4, but the query uses it to select rows. For many organizations, filtering by a highly sensitive identifier is still restricted. It can reveal whether a person exists in a dataset or allow targeted lookup.

A practical policy may distinguish projection from filtering:

policies:
  - id: deny_ssn_filter_for_analyst
    type: field_access
    effect: deny
    when:
      role: analyst
      field_labels_any: [government_identifier]
      usage_any: [filter]

The decision might be:

{
  "decision": "deny",
  "matched_policies": ["deny_ssn_filter_for_analyst"],
  "reason": "Analyst role cannot filter customers by government identifiers.",
  "field_access": [
    {
      "field": "analytics.customers.ssn_last4",
      "usage": "filter",
      "labels": ["PII", "government_identifier"],
      "effect": "deny"
    }
  ]
}

This is why a field-level permission engine needs semantic analysis. A string search for ssn is not enough. The system must resolve the referenced column to the catalog, know its labels, and understand where it appears in the query.

Example 3: Aggregation May Be Allowed When Raw Values Are Not

Some policies allow aggregate analysis while denying raw field exposure.

For example, a compensation analyst may not be allowed to list individual salaries:

SELECT employee_id, salary
FROM hr.employees;

But the same user may be allowed to query an aggregate:

SELECT department, AVG(salary) AS avg_salary
FROM hr.employees
GROUP BY department;

Even then, the query may require safeguards: minimum group size, approved purpose, row-level filters, masking, or human approval.

A field-level permission check should therefore preserve usage context:

{
  "field": "hr.employees.salary",
  "usage": "aggregation_input",
  "aggregation": "AVG",
  "output_column": "avg_salary",
  "labels": ["HR", "compensation"],
  "policy_result": "approval_required",
  "reason": "Compensation fields may be used in aggregate analysis only with approved purpose and minimum group size checks."
}

The goal is not always to block. The goal is to make the decision explicit.

Example 4: Derived Columns Can Still Reveal Restricted Fields

A generated query may avoid selecting the raw sensitive field but derive a new output from it:

SELECT
  customer_id,
  CASE
    WHEN credit_score >= 720 THEN 'prime'
    WHEN credit_score >= 660 THEN 'near_prime'
    ELSE 'subprime'
  END AS credit_segment
FROM finance.customer_risk;

The output column credit_segment is derived from credit_score. If credit_score is restricted, the derived output may also need a policy decision.

This is where field-level permissions and column-level lineage meet. The system should understand:

query_result.credit_segment <- finance.customer_risk.credit_score

The policy decision might be:

{
  "decision": "approval_required",
  "reason": "Output credit_segment is derived from restricted credit_score.",
  "lineage": [
    {
      "target": "query_result.credit_segment",
      "source": "finance.customer_risk.credit_score",
      "usage": "derived_expression",
      "labels": ["financial_risk", "regulated"]
    }
  ]
}

Without lineage, the system may treat credit_segment as a harmless new field. With lineage, it can carry the sensitivity of the source column into the derived output.

What the Policy Engine Needs as Input

A useful field-level permission check does not start from SQL alone. It needs a request envelope and governance metadata.

A minimal request might include:

{
  "request_id": "req_2026_05_field_001",
  "user": {
    "id": "u_12345",
    "roles": ["sales_analyst"],
    "department": "sales_operations"
  },
  "purpose": "interactive_chatbi",
  "environment": "production_readonly",
  "dialect": "postgresql",
  "generated_sql": "SELECT customer_id, email, total_spend FROM analytics.customers ORDER BY total_spend DESC LIMIT 50"
}

The catalog should describe tables and columns:

schemas:
  analytics:
    tables:
      customers:
        columns:
          customer_id:
            type: string
          email:
            type: string
          phone:
            type: string
          total_spend:
            type: decimal
          state:
            type: string

Classification metadata should describe sensitivity:

classifications:
  analytics.customers.email:
    labels: [PII, contact]
    sensitivity: high

  analytics.customers.phone:
    labels: [PII, contact]
    sensitivity: high

  analytics.customers.total_spend:
    labels: [financial_behavior]
    sensitivity: medium

Policy rules should express decisions:

policies:
  - id: deny_pii_projection_for_sales_analyst
    type: field_access
    effect: deny
    when:
      role: sales_analyst
      field_labels_any: [PII]
      usage_any: [projection]

  - id: warn_financial_behavior_for_interactive_chatbi
    type: field_access
    effect: warn
    when:
      purpose: interactive_chatbi
      field_labels_any: [financial_behavior]

  - id: approval_for_sensitive_export
    type: query_shape
    effect: approval_required
    when:
      result_limit_greater_than: 1000
      field_labels_any: [PII, financial_behavior]

These examples are intentionally simple. In a production environment, policies may come from IAM, data catalog labels, privacy systems, security review workflows, and business rules. But the core pattern is the same: SQL facts plus user context plus field classifications produce a policy decision.

Why String Matching Fails

Some teams try to block fields with simple text patterns:

if SQL contains "email", block it
if SQL contains "ssn", block it
if SQL contains "salary", require approval

This approach breaks quickly.

First, aliases can hide field names:

SELECT c.email AS contact
FROM customers c;

Second, unqualified names need binding:

SELECT email
FROM customers;

The system must know which email column this means.

Third, CTEs and subqueries can rename fields:

WITH contacts AS (
  SELECT customer_id, email AS contact_key
  FROM customers
)
SELECT contact_key
FROM contacts;

Fourth, expressions can derive sensitive outputs:

SELECT SHA256(email) AS email_hash
FROM customers;

Hashing may reduce exposure in some contexts, but it does not automatically remove governance risk. The output still depends on a PII source field, and policy should decide whether the transformation is acceptable.

Fifth, different dialects have different quoting, struct access, JSON operators, and function behavior. A reliable checker needs a dialect-aware parser and a semantic binding layer.

A Practical Evaluation Flow

A production Text-to-SQL system should place field-level permission checks after SQL generation and before execution:

User question
  ↓
LLM generates SQL
  ↓
SQL parser
  ↓
Catalog binding
  ↓
Field usage extraction
  ↓
Sensitive-field classification
  ↓
Policy evaluation
  ↓
allow / warn / deny / approval_required
  ↓
Execute, repair, approve, or reject
  ↓
Audit log

The important point is that the field check is not a separate static checklist. It depends on the generated query. Two prompts from the same user can produce different SQL shapes and therefore different policy outcomes.

Example SQL Facts JSON

For a generated query:

SELECT
  customer_id,
  email,
  total_spend
FROM analytics.customers
WHERE state = 'CA'
ORDER BY total_spend DESC
LIMIT 50;

A useful SQL facts output might look like this:

{
  "sql_id": "chatbi_042",
  "dialect": "postgresql",
  "parse_status": "success",
  "statement_type": "select",
  "tables": [
    {
      "name": "customers",
      "schema": "analytics",
      "alias": null
    }
  ],
  "field_usage": [
    {
      "field": "analytics.customers.customer_id",
      "usage": "projection",
      "labels": [],
      "sensitivity": "low"
    },
    {
      "field": "analytics.customers.email",
      "usage": "projection",
      "labels": ["PII", "contact"],
      "sensitivity": "high"
    },
    {
      "field": "analytics.customers.total_spend",
      "usage": "projection",
      "labels": ["financial_behavior"],
      "sensitivity": "medium"
    },
    {
      "field": "analytics.customers.total_spend",
      "usage": "ordering",
      "labels": ["financial_behavior"],
      "sensitivity": "medium"
    },
    {
      "field": "analytics.customers.state",
      "usage": "filter",
      "labels": ["location"],
      "sensitivity": "low"
    }
  ],
  "policy_matches": [
    {
      "policy_id": "deny_pii_projection_for_sales_analyst",
      "effect": "deny",
      "field": "analytics.customers.email",
      "reason": "Sales analyst cannot project PII contact fields."
    },
    {
      "policy_id": "warn_financial_behavior_for_interactive_chatbi",
      "effect": "warn",
      "field": "analytics.customers.total_spend",
      "reason": "Financial behavior field used in interactive ChatBI query."
    }
  ],
  "decision": "deny",
  "recommended_action": "Remove email from the projection, or request approval under a role allowed to access contact PII."
}

This structure is useful because it can serve multiple audiences:

• the application can block or repair the query;
• the user can receive a clear explanation;
• the security team can review policy matches;
• the governance team can inspect sensitive-field usage patterns;
• the engineering team can build stable APIs around SQL facts.

Decision Model: Allow, Warn, Deny, Approval Required

A binary allow/deny model is often too rigid for enterprise Text-to-SQL. Some queries are safe. Some are clearly prohibited. Others should be allowed with a warning, masked output, row limit, or approval workflow.

For generated SQL, these decisions should be returned before execution. The database should not be the first place where permission problems appear.

How This Connects to SQL Semantic Validation

Field-level permission checks depend on SQL semantic validation.

A policy engine cannot reliably evaluate customers.email unless the system can first answer:

• Does customers refer to the expected table?
• Does email exist in that table?
• Is email an output column, filter dependency, join key, aggregation input, or derived source?
• Did a CTE rename it?
• Did SELECT * expand to include it?
• Is there an alias hiding the original field?

This is why field-level permission checking should not be implemented as a string filter around an LLM. It needs a catalog-aware SQL semantic layer.

How This Connects to Column-Level Lineage

Column-level lineage explains how output columns depend on source columns. That is essential when sensitive data moves through transformations.

For example:

SELECT
  customer_id,
  SHA256(email) AS email_hash
FROM analytics.customers;

A lineage-aware system should know:

query_result.email_hash <- analytics.customers.email

Then policy can decide whether hashing is sufficient for the current role and purpose. In some environments, hashed email may be allowed for matching workflows. In others, it may still be restricted because it can be joined back to identity data.

This is why field-level permission, sensitive-field detection, and lineage should not be treated as separate silos. They are different views of the same SQL facts.

What to Log for Audit

A field-level decision should be reviewable later. A useful audit event should include:

• request ID;
• user or role context;
• purpose and environment;
• generated SQL hash or stored SQL, depending on policy;
• dialect;
• tables and fields referenced;
• sensitive labels;
• field usage roles;
• matched policies;
• final decision;
• recommended action;
• whether the query was executed, repaired, approved, or rejected.

A simplified audit record might look like this:

{
  "event_type": "text_to_sql_policy_decision",
  "request_id": "req_2026_05_field_001",
  "user_role": "sales_analyst",
  "purpose": "interactive_chatbi",
  "decision": "deny",
  "matched_policies": ["deny_pii_projection_for_sales_analyst"],
  "sensitive_fields": [
    {
      "field": "analytics.customers.email",
      "usage": "projection",
      "labels": ["PII", "contact"]
    }
  ],
  "action": "query_rejected_before_execution"
}

This audit trail matters because enterprise AI systems need more than a final answer. They need evidence of control.

Practical Checklist for Teams

Use this checklist when evaluating a Text-to-SQL or ChatBI system:

• Can the system parse generated SQL for your actual dialects?
• Can it bind unqualified columns, aliases, CTEs, subqueries, and SELECT * to catalog metadata?
• Can it identify sensitive fields in projections, filters, joins, aggregations, grouping, ordering, and derived outputs?
• Can it distinguish direct exposure from aggregate use?
• Can it carry sensitivity through derived columns using lineage?
• Can policies use user role, purpose, environment, field labels, and usage role?
• Can it return allow, warn, deny, and approval_required?
• Can it explain which field and policy caused the decision?
• Can it fail safely when SQL cannot be parsed or bound?
• Can it write an audit record before execution?
• Can reviewers test the system with 50–100 representative SQL samples?

If the answer is no to several of these questions, the Text-to-SQL workflow may still be useful for demos, but it is not yet ready for governed production use.

Common Questions

Are database permissions enough for Text-to-SQL?

Database permissions are necessary, but they are usually not enough by themselves. A database can enforce grants at execution time, but a Text-to-SQL governance layer should inspect the generated SQL before execution, with user intent, application role, purpose, field labels, policy rules, and audit requirements.

What is the difference between table-level and field-level permission?

Table-level permission decides whether a user can access a table. Field-level permission decides whether a user can access or use specific columns within that table. A user may be allowed to query customers for aggregate analytics but not allowed to select customers.email or filter by customers.ssn_last4.

Should field-level checks inspect only the SELECT list?

No. Field usage can appear in SELECT, WHERE, JOIN, GROUP BY, HAVING, ORDER BY, window functions, CTEs, subqueries, and derived expressions. Sensitive access can affect results even when the sensitive field is not displayed.

Can an LLM judge whether a field is sensitive?

An LLM can help explain policy messages, but the core permission decision should not depend on the model guessing. Sensitive-field detection should use approved metadata such as catalog labels, glossary terms, classification rules, and policy configuration.

How does field-level permission relate to SQL lineage?

Lineage shows which source fields contribute to output fields. That matters when sensitive fields are transformed or renamed. If email_hash is derived from customers.email, policy may still need to treat the output as sensitive or restricted.

What should happen when the policy engine is uncertain?

A governed system should fail safely. Depending on the risk, it can return deny, approval_required, or repair with a clear reason. It should not silently allow SQL it cannot parse, bind, or classify.

Summary Table

Conclusion

Field-level permission checks are one of the most important controls for production Text-to-SQL. They close the gap between “this user can query a table” and “this generated SQL is allowed to use these specific fields in this specific way.”

A practical implementation needs more than prompts, string matching, or table grants. It needs SQL parsing, catalog binding, sensitive-field metadata, usage-aware policy rules, lineage for derived outputs, explicit decisions, and audit logs.

For teams building ChatBI, Text-to-SQL, or AI data agents, this capability is not a nice-to-have. It is part of the control layer that determines whether generated SQL can be safely executed, repaired, approved, or rejected before it reaches the database.

If you want to evaluate a single generated query first, you can try SQL Guard-style validation with your SQL.

For a broader review, collect 50–100 representative generated SQL queries, include role and field-classification context, and use the results to assess whether your Text-to-SQL workflow is ready for governed production use.