AI Analytics Row-Level Security: Let Users Ask Questions Without Leaking Data

The database or BI layer knows who is asking. The app can apply tenant filters, role checks, and column restrictions.

AI analytics often breaks that chain:

Human user → app session → AI service → service account → database query 

Now the warehouse sees one identity: the AI service account.

That account usually needs broad read access so it can answer many kinds of questions. If you do nothing else, a basic natural language question can become a cross-tenant data leak.

The model is not necessarily malicious. It may simply do what models do: search for the data that seems relevant. If the path is open, it will use it.

A safe design treats the AI analyst as an untrusted planner, not as the enforcement layer.

What row-level security means for AI analytics

Row-level security means users only see the records they are allowed to see, even when they ask broad questions.

For example:

• A customer admin sees only their company’s accounts.
• A regional manager sees only accounts in their region.
• A support agent sees limited account health but not billing details.
• A trial user sees sample data, not real customer revenue.
• A contractor sees assigned projects, not the full workspace.

With AI analytics, this must hold across every path:

• generated SQL
• semantic metric calls
• chart queries
• cached answers
• exported CSVs
• follow-up questions
• tool retries
• background agent jobs

If the user asks, “Show all churn risks,” the system should not trust the model to remember where tenant_id = .... The platform should enforce that scope below or beside the model.

A safer architecture for customer-facing AI analytics

Use a layered design:

User question ↓ Auth context ↓ AI planner ↓ Analytics gateway ↓ Policy engine ↓ Semantic layer ↓ Query compiler ↓ Database / warehouse ↓ Scoped result + evidence ↓ Answer or chart 

The important point: the AI planner proposes intent. It does not get raw database freedom.

The analytics gateway should own five jobs:

1. Verify the user and tenant.
2. Decide which datasets and metrics are visible.
3. Compile safe queries from approved semantic definitions.
4. Apply row-level and column-level filters.
5. log the question, query, result shape, and policy decision.

This creates a boundary the model cannot prompt its way around.

Do not let the model write final SQL unchecked

Natural language to SQL is tempting. It demos well. It also fails in ways that are easy to miss.

A model may:

• forget the tenant filter
• join on the wrong key
• select sensitive columns
• infer hidden tables from schema names
• use expensive queries
• change metric definitions
• include deleted or test records
• bypass a business rule that the UI always applies

A better pattern is intent to semantic query.

The model extracts the user’s intent, then your system maps it to approved metrics and dimensions.

{ "metric": "active_accounts", "dimensions": ["plan", "region"], "filters": [ { "field": "period", "operator": "last_30_days" } ], "visualization": "bar_chart" } 

Then your query compiler creates SQL using server-side policy.

select plan, region, count(*) as active_accounts from account_metrics where tenant_id = :tenant_id and deleted_at is null and activity_date >= current_date - interval '30 days' and region = any(:allowed_regions) group by plan, region order by active_accounts desc; 

The model can ask for “active accounts by region.” It cannot remove tenant_id, invent a metric, or select payroll data.

Start with an access context object

Every AI analytics request should begin with a server-created access context. Do not let the browser or the model provide this object.

type AnalyticsAccessContext = { userId: string; tenantId: string; role: "owner" | "admin" | "analyst" | "support" | "viewer"; allowedDatasets: string[]; allowedMetrics: string[]; rowFilters: { tenant_id: string; regions?: string[] }; deniedColumns: string[]; requestId: string; }; 

This object becomes the policy input for every later step.

The user may ask a broad question. The model may suggest a query. But the access context decides what data exists for this session.

Build a small metric catalog

A metric catalog keeps the model from redefining your business.

Without a catalog, “revenue” may mean booked revenue in one answer, paid invoices in another, and forecasted expansion in a third. That destroys trust.

A simple metric definition can start like this:

const metrics = { churn_risk_accounts: { id: "churn_risk_accounts", label: "Churn-risk accounts", sqlExpression: "count(distinct account_id)", allowedRoles: ["owner", "admin", "analyst"], defaultFilters: { is_test_account: false, deleted: false }, safeDimensions: ["plan", "region", "owner_team"], freshnessTargetMinutes: 60 } }; 

The model can explain this metric to the user, but it should not create the definition on the fly.

Enforce row-level security in more than one place

There are three common enforcement patterns.

1. Database-native RLS

This uses the database’s row-level security features. The query runs with a user or tenant context, and the database enforces the rule.

Example in Postgres:

alter table account_metrics enable row level security; create policy tenant_isolation_policy on account_metrics using (tenant_id = current_setting('app.tenant_id')::uuid); 

Then set the tenant before the query:

select set_config('app.tenant_id', :tenant_id, true); 

This is strong because the rule lives near the data. It is harder for app code to forget.

2. Analytics gateway filtering

The gateway injects tenant and role filters into every compiled query.

This is practical when you query warehouses or multiple data stores where native RLS is uneven.

The key rule: filters must be added by trusted code, not by the model.

3. Scoped semantic datasets

The user only sees a subset of datasets and dimensions. If a support user cannot access revenue, the model never receives revenue tables, revenue metrics, or revenue examples in its prompt.

This reduces both security risk and model confusion.

Most small teams should combine patterns 2 and 3 first. Add database-native RLS where your stack supports it cleanly.

Protect columns, not just rows

Row-level security answers, “Which records can this user see?”

AI analytics also needs column-level controls for emails, invoice notes, API keys, compensation, internal scores, support transcripts, and other sensitive fields. The model does not need most of this to answer common analytics questions.

Create a column policy:

const columnPolicy = { viewer: ["account_name", "plan", "usage_count", "created_month"], analyst: ["account_name", "plan", "usage_count", "mrr_band", "region"], support: ["account_name", "plan", "health_status", "open_ticket_count"], owner: ["*"] }; 

Prefer bands and aggregates over raw sensitive values. “MRR band: $1k-$5k” is often enough for AI analytics. You do not need to expose exact invoices for every question.

Add query budgets before users discover expensive questions

Natural language makes expensive analytics easier to trigger.

A user can ask:

Compare every customer cohort by feature usage, ticket sentiment, renewal risk, onboarding source, and plan since launch.

That may sound reasonable. It may also scan half your warehouse.

Add budgets for rows returned, query runtime, joins, date range, follow-up depth, chart series, returned context, and repeated-question caching.

Your query guard should reject plans with too many rows, too many joins, blocked columns, or a date range the user’s plan does not allow.

Return a helpful refusal:

I can answer that if we narrow the date range or choose fewer breakdowns. Try “last 90 days by plan and region.”

This protects cost and improves UX.

Make follow-up questions inherit scope

Follow-up questions are a hidden leak path.

User: “Show churn risk for my region.”

AI: “Here are the Midwest accounts at risk.”

User: “Now compare that with everyone else.”

The phrase “everyone else” is dangerous. It could mean every region in the tenant, every tenant, or all customers in the warehouse.

Follow-ups must inherit the original access context and policy, not raw result rows unless needed.

Store conversation state like this:

{ "conversation_id": "conv_123", "tenant_id": "tenant_456", "user_id": "user_789", "active_scope": { "regions": ["midwest"], "datasets": ["account_metrics"], "metrics": ["churn_risk_accounts"] }, "last_query_id": "qry_abc" } 

When the user asks a follow-up, the gateway resolves ambiguous words against policy, not against the model’s imagination.

Log evidence for every answer

AI analytics needs an answer receipt.

At minimum, log the user, tenant, original question, normalized intent, selected metrics, policy decision, query hash, row count, returned columns, freshness, model, and refusal reason if any.

Example:

{ "request_id": "req_01", "tenant_id": "tenant_456", "user_id": "user_789", "question": "Which accounts are slipping this month?", "intent": "churn_risk_accounts_by_owner", "policy": "allow", "filters_applied": ["tenant_id", "allowed_regions", "not_deleted"], "columns_returned": ["account_name", "owner_team", "risk_band"], "row_count": 42, "freshness_minutes": 18, "query_hash": "sha256:..." } 

Common implementation mistakes

Mistake 1: Putting the tenant filter in the prompt

Bad: “Always remember to filter by tenant_id.”

Good: the query compiler always injects tenant scope from server-side auth context.

Prompts are guidance. Policy is enforcement.

Mistake 2: Giving schema access to everyone

Do not show the model every table name and column. Hidden tables can leak meaning even if rows are blocked. A table name can be sensitive by itself.

Mistake 3: Returning raw rows when aggregates would work

If the user asks for a trend, return a trend. Do not send 10,000 rows to the model so it can summarize them.

Mistake 4: Caching answers without scope keys

Cache keys must include tenant, role, metric version, and policy version.

tenant_456:role_analyst:metric_v4:policy_v12:churn-risk-last-30-days 

Mistake 5: Treating charts as safe by default

Charts can leak. A chart with one bar can reveal a single customer’s revenue. Add minimum group sizes and suppression rules.

A simple rollout plan

You do not need a huge platform on day one.

Start here:

1. Pick three safe metrics customers already understand.
2. Define allowed dimensions for each metric.
3. Create an access context from server-side auth.
4. Build a small analytics gateway.
5. Compile semantic queries instead of trusting raw SQL.
6. Inject tenant and role filters in code.
7. Block denied columns.
8. Limit rows, joins, and date ranges.
9. Log an answer receipt.
10. Test cross-tenant and role-based prompts before launch.

For example, ship usage trends, active accounts, and support ticket volume first.

Avoid high-risk areas like billing disputes, payroll, medical records, security logs, raw messages, and unrestricted SQL exports until your boundaries are proven.

Test cases you should run

Add these to CI or a staging eval suite:

The goal is not to prove the model is obedient. It is to prove the boundary holds when the model is creative.

Final checklist

Before giving customers an AI analyst, make sure you can answer yes to these:

• Does every request start with server-side identity?
• Are tenant filters enforced outside the prompt?
• Can the model only use approved metrics and dimensions?
• Are sensitive columns blocked by role?
• Are queries budgeted by rows, time, joins, and date range?
• Do follow-up questions inherit the same scope?
• Are chart outputs protected against small-group leaks?
• Are answer receipts logged?
• Can you replay why an answer was generated?
• Do tests prove one tenant cannot reach another tenant’s data?

If not, your AI analytics feature may be a data leak with a chat box.

FAQ

What is AI analytics row-level security?

AI analytics row-level security is the practice of enforcing user, tenant, and role-based row filters when an AI system answers questions about data. The model may interpret the question, but trusted application or database code decides which records the user can access.

Is prompting enough to protect tenant data?

No. A prompt can remind the model to use tenant filters, but it is not a security boundary. Tenant scope should be enforced by the database, analytics gateway, semantic layer, or query compiler.

Should an AI analyst generate SQL directly?

It can generate draft SQL for internal review, but customer-facing systems should avoid executing unchecked SQL from a model. A safer pattern is to convert user intent into approved metrics, dimensions, and filters, then compile SQL with server-side policy.

How do I handle natural language follow-up questions safely?

Store conversation scope separately from model text. Follow-ups should inherit the same tenant, role, metric, and dataset limits. Ambiguous phrases like “everyone else” should be resolved by policy, not by model guesswork.

What should I log for AI analytics audits?

Log the user, tenant, original question, normalized intent, selected metrics, applied filters, returned columns, row count, freshness, query hash, model version, and policy decision. This creates evidence for debugging and customer trust.

How can small teams start without building a full BI platform?

Start with a few approved metrics, a small semantic catalog, strict tenant filters, column blocks, query limits, and answer receipts. Avoid raw SQL execution and sensitive datasets until your policy and tests are reliable.