Data Authorization: Why Security Fails in the Semantic Layer

Authorization Happens at Three Layers. Only One Stops Breaches.

Authorization can sit in the database, the BI layer, or the semantic layer. Where it lives determines whether unauthorized queries are rejected before they touch data or after. The stakes are concrete. The 2024 Snowflake credential campaign exposed 165 organizations. Broken access control ranks #1 in OWASP Top 10:2021.

The Architectural Comparison

Layer	When Enforced	Multi-warehouse?	Agent-ready?
Database-native	At query execution	No	Partial
BI-layer	After read, before display	No	No (agents bypass)
Semantic-layer	At compile time (before read)	Yes (16+ engines)	Yes (governs all paths)

Database-native controls (Snowflake masking, BigQuery RLS, Postgres RLS) are fast and native but trapped inside one engine. They cannot enforce policy across Snowflake and Databricks simultaneously.

BI-layer controls (Looker row filters, Tableau RLS) execute after the warehouse has already read and returned the data. Any agent or SQL client that bypasses the BI tool sees all rows. This is the confused-deputy problem: the warehouse logs "query by service_account_123" — it has no idea who the actual user is.

Semantic-layer enforcement happens at compile time, before any warehouse query is generated. Authorization shapes which entities exist in the resolved graph. If a user asks for data they cannot access, compilation fails. The data is never read. Attackers in the 2024 Snowflake campaign authenticated directly and ran normal queries under stolen credentials. BI-layer RLS did not apply because the query went straight to the warehouse.

Section 1 — The Layered Stack and Where Authorization Has Lived

The traditional analytics stack is storage (databases/warehouses) → transformation (dbt, SQL) → BI/presentation (Looker, Tableau, dashboards). The modern, AI-era stack inserts a new tier: storage → semantic layer → agents / APIs / BI.

Historically authorization has lived in one of three spots:

• Database-native: Snowflake masking, BigQuery column-level security, Postgres RLS.
• BI-layer: Looker access filters / user attributes, Tableau RLS.
• Ad hoc SQL filters baked into transformation models.

Each has a fatal flaw on its own. Database-native controls cannot reach across the warehouse boundary or out to agents and APIs. BI-layer controls fire too late and are bypassed by anything not going through the BI tool. Ad hoc SQL filters are invisible to audit — if the filter has a bug or is omitted, nothing flags it. A semantic layer enforces authorization at compile time — before any data is touched — sitting "between users, AI agents, and your data systems," resolving meaning at compile time and running "dialect-perfect SQL inside a policy-shaped subgraph."

Section 2 — The Database-Layer Limitation

Database-native enforcement is excellent at what it does — and what it does is bounded by one engine.

• Snowflake Dynamic Data Masking rewrites column values at query time based on role. It is powerful within Snowflake, but it is a Snowflake-only construct requiring Enterprise Edition. It cannot mask data that lives in Databricks or BigQuery. Critically, masking did not protect customers in the 2024 credential attacks because attackers held credentials that saw unmasked production data.
• BigQuery column-level security uses Data Catalog policy tags and is scoped to BigQuery/GCP. It does not reach a Snowflake or Postgres table.
• PostgreSQL RLS injects row predicates within Postgres only, with limited ABAC expressiveness.

In a multi-source estate the consequences compound. Policies must be authored separately in each system. When a user is terminated, access must be revoked in Snowflake and Databricks and BigQuery — miss one and the user retains access. The audit question "who accessed customer data across the estate?" requires querying three different audit logs and reconciling them by hand.

Section 3 — The BI-Layer Trap: Why Authorization Is Structurally Too Late

Here is how BI-layer authorization actually works. A user opens a dashboard. The BI tool generates SQL and runs it against the warehouse — typically under a shared service credential, not the end user's identity. The warehouse executes the query, reads the data, and returns results. Then the BI tool applies row-level security: filtering rows, masking columns, applying access filters.

The architectural problems follow directly:

1. The data is already read. By the time the BI tool filters, the warehouse has scanned and returned the rows. The exposure has already happened on the wire and in the warehouse's memory and logs.
2. Identity is lost. The warehouse logs "query executed by looker_service_account," not "query executed to answer a question for User X." This is the confused-deputy problem: the warehouse can't enforce per-user policy because it never sees the user.
3. It is bypassable. Google's Looker documentation is explicit: "If users have access to raw SQL (through SQL Runner or the API), they can bypass Explore access filters." If your AI agent queries the database directly, every row-level security policy in your Looker instance is silently bypassed.
4. It is a performance tax. Because filtering happens after the read, the warehouse cannot push authorization predicates down to prune data it should never have touched.
5. It is hard to audit. If the BI-layer masking has a bug or is inconsistently applied, the warehouse audit log won't show it — the query just looks like a normal service-account read.

The canonical failure: an analyst sees correctly masked revenue-by-region in a dashboard, then opens a SQL client or API against the same warehouse and sees global revenue unmasked. The BI-layer control simply did not apply.

Section 4 — Why AI Agents Broke the BI-Layer Model

The BI layer assumed a human analyst with training, judgment, and a tool in front of them. AI agents violate every part of that assumption. They query the warehouse programmatically — increasingly over the Model Context Protocol (MCP) — with no BI layer in the path to apply masking. Agents reason directly over the raw schema, inferring relationships that are implicit and undocumented.

This is dangerous for two compounding reasons. First, agents are unreliable on raw enterprise schemas. The BEAVER benchmark found that GPT-4o and Llama3-70B-Instruct "achieved close to 0 end-to-end execution accuracy," concluding that "off-the-shelf LLMs trained on public datasets are unable to generalize." Spider 2.0 tells the same story: "even the advanced LLMs—o1-preview solve only 17.1% of Spider 2.0 tasks. For widely-used models like GPT-4o, the success rate is only 10.1%." Agents hallucinate non-existent tables, invent joins, and omit filters.

Second, and worse for security: if authorization lives only at the BI layer, the agent has no authorization constraint at all. It reaches the warehouse under a service identity and reads whatever that identity can see. Microsoft's Fabric Data Agent has documented exactly this — RLS configured only in the semantic model was not enforced when the agent queried under its service identity, "resulting in all data being returned."

Section 5 — The Autonomous Semantic Layer: Compile-Time Enforcement

An autonomous semantic layer is a typed model of enterprise meaning — entities, relationships, metrics, and access policies — sitting above all data sources. The breakthrough for authorization is when enforcement happens: during query planning, before any warehouse query is generated. Before execution. Before access.

The flow:

1. Agent (or BI tool, or user) submits intent in natural language or structured form.
2. The semantic compiler resolves that intent against the graph. Which entities? Which relationships? Which metrics?
3. Authorization is evaluated. Personas, scopes, and policies shape an allowed subgraph before SQL is planned. If a metric depends on data outside that subgraph, compilation fails immediately. The unauthorized read never happens.
4. If authorized, deterministic SQL is compiled and executed. If not, compilation fails with a clear audit reason.

This is structural enforcement, not procedural. The BI layer reads then filters; the autonomous semantic layer fails during planning. Every compiled query generates a point-in-time reproducible audit trace: graph version, identity context, resolved entities, proven join paths, compiled SQL. Regulators can replay any past query and see exactly what definitions were in force.

Section 7 — Multi-Source Enforcement and the Revocation Problem

Consider a user with access to revenue in Snowflake but not in Databricks. Under database-native enforcement you maintain two separate policies in two systems. When the user is terminated, you must revoke in both — and in BigQuery, and anywhere else. Miss one and the breach is latent. The audit question "show me everyone who accessed customer data last quarter" becomes a multi-day reconciliation across three audit logs with three schemas. The semantic-layer answer collapses this to one policy, one revocation, one audit trail, enforced identically before any engine is touched. Fine-grained data access control mechanisms (RBAC, ABAC, row/column predicates) are what the semantic layer compiles; the layer is where those mechanisms are evaluated.

Section 8 — The "Authorization at the API Layer" Trap

A common intermediate pattern is to build REST APIs and embed authorization checks in API code. It does not scale: every API is a separate authorization implementation, policies diverge across services, testing is hard, and enforcement is opaque to the user. Agents become "a confused-deputy problem at industrial scale" when a single service identity serves many users. The semantic-layer alternative makes authorization declarative and centralized — one policy model, evaluated structurally — rather than scattered across hand-written endpoint code.

Section 9 — Build vs Buy for Semantic-Layer Enforcement

You can build this. The canonical DIY stack is Open Policy Agent (OPA) + custom SQL templating + a query parser: OPA decides allow/deny and can emit row filters as WHERE-clause fragments, while your own code handles parsing, join graphs, and dialect generation.

The expensive part is everything around the policy decision. The hidden cost of building your own data access layer is a from-scratch data access layer pegged at "easily 6-12 months of work by senior engineers" just to hit baseline parity, with a representative in-house TCO of two backend engineers for 12 months at $300,000+, DevOps at $100,000+, and ~$50,000/year ongoing maintenance. The recurring tax is dialect handling (Snowflake SQL vs Postgres vs BigQuery), join-path correctness, and keeping policies valid as schemas drift.

Decision rule: build if you have genuinely unique policy semantics and the standing engineering capacity to own a security-critical system indefinitely. Buy if you want multi-dialect support, audit trails, and semantic maintenance delegated, and you want to ship in weeks. An autonomous semantic layer reports datasource connection and initial graph build in "hours, not weeks," and production rollouts in regulated environments "in weeks, not months."

Section 10 — Competitive Landscape

• Snowflake / BigQuery / Postgres: database-native masking and RLS — fast, native, single-engine; do not reach agents/APIs or other warehouses.
• Looker / Tableau: BI-layer RLS — works for human analysts in-tool; late and bypassable.
• dbt: a transformation/metrics layer. Its semantic layer standardizes metric definitions and respects the data-platform credential's policies, but its scope "covers metric calculation but not broader entity definitions, access governance, or AI-agent APIs."
• Atlan / Collibra / Alation: governance and metadata catalogs. They document ownership, lineage, and classification, but policy enforcement "depend[s] on manual stewardship."
• Colrows: semantic execution layer — multi-warehouse, compile-time enforcement, per-query audit trace.
• Custom OPA + SQL: maximum control, maximum maintenance.

Section 11 — CTO Decision Framework

• Single warehouse, human analysts only: database-native (plus BI-layer for convenience) is acceptable.
• Two or more warehouses: per-warehouse enforcement becomes untenable — drift and revocation risk grow with each engine. Evaluate semantic-layer enforcement.
• AI agents in production: BI-layer authorization is bypassed by design. Semantic-layer enforcement becomes effectively mandatory.
• Regulatory audit intensity (HIPAA, GDPR, financial services): compile-time, reproducible audit trails move from nice-to-have to required.

Thresholds that change the recommendation: the moment you cross to 2+ warehouses or put any agent in production against real data, move authorization to the semantic layer. If you remain single-engine and human-only, you can defer.

Fix the Context, Not the Model. Data breaches happen because access control fires too late. Retrofit authorization into models and BI tools and you are still enforcing after the warehouse has read forbidden data. The only structural defense is a semantic layer that fails authorization during planning, before execution.

Frequently Asked Questions

Why does AI-driven analytics create a data authorization crisis?

AI agents query databases directly, bypassing BI-layer controls entirely. They resolve relationships that are undocumented. If authorization lives only at the BI layer, the agent has no constraint. It reaches the warehouse under a service identity and reads whatever that identity can see. This is the confused-deputy problem at scale.

How does compile-time enforcement at the semantic layer prevent data leakage?

The semantic compiler resolves user intent against a policy-shaped subgraph. If the intent requires data outside the user's scope, compilation fails before SQL is generated. The warehouse query never runs. The data is never read. This is structural security, not procedural.

Can an autonomous semantic layer replace row-level security in a modern stack?

Yes. Row-level security, column masking, RBAC, and ABAC are what the semantic compiler enforces. The difference is when: at compile time in the semantic layer, not at query execution in the database. Compile-time enforcement prevents unauthorized reads entirely. Database-native RLS only flags them after the fact.

Section 12 — Implementation Roadmap

• Greenfield: design the semantic layer from day one; point all consumers (BI, APIs, agents) at it.
• Brownfield: add the semantic layer as an overlay. Repoint agents and APIs to it first (they are the highest-risk, lowest-friction path), keep BI tools connected to the warehouse for backward compatibility, then migrate dashboards onto the governed layer.
• Hybrid: keep performance-critical, low-sensitivity queries database-native; route governed/audited/cross-warehouse queries through the semantic layer.