Building a BI Dashboard With Role-Based Access Control: Real Costs, Real Failure Modes

What actually happens the first time two roles look at the same chart

A regional sales manager opens the revenue dashboard. She should see her three territories. The KPI tile shows a company total of $4.2M, because someone built it from a pre-aggregated summary table written before the filter was applied. The filter does work on the detail grid below it, so the grid shows $890K. She screenshots both, sends it to the CFO, and nobody trusts the dashboard again.

That is the defining failure of this capability. Permissions were applied at the presentation layer, not at the query layer. Every naive build does this, because the naive build treats access control as a UI concern: hide the tab, hide the column, hide the chart. The data still travels to the browser. Open the network tab and the full result set is sitting in the JSON payload. We have inherited builds where an intern-level analyst could read the executive compensation table by inspecting an API response for a page they were not even allowed to open.

The export path is worse, because it is a different code path written three sprints later and it does not apply the territory filter at all. Exports and scheduled email reports are where role-based access control quietly dies in almost every build we audit.

Row-level security is a database problem, and where you put it decides your architecture

You have three real places to enforce who sees which rows, and picking wrong costs you a rewrite.

In the database. PostgreSQL has native row-level security via CREATE POLICY, driven by a session variable you set per request. Supabase built its entire model on this. It is the strongest option because a bug in your API cannot leak rows, the database refuses. The cost is that RLS policies with subqueries can wreck the query plan. We have seen a policy with an IN clause against a user-territory mapping table take a 40ms query to 3.5 seconds because the planner could not push the predicate down. The fix is usually a materialized entitlement table with a covering index, plus checking that your policy expression is marked LEAKPROOF where it matters. Also know that RLS is bypassed by the table owner and by any role with BYPASSRLS, so your migration user and your ORM connection user must not be the same role. That single detail catches most teams.

In a semantic layer. Tools like Cube, dbt with access grants, or Looker's LookML with access_filters put entitlements in a metrics layer above the warehouse. This is the right answer when the same metric must be consistent across a dashboard, an export and an API. It also gives you one definition of "revenue" instead of eleven.

In application code. A WHERE clause appended by your service layer. Fastest to build, most fragile. It only survives if every read path goes through a single repository function and you have a test that fails when someone writes a raw query. Write that test on day one.

Whatever you pick, the entitlement itself is rarely "role equals manager." It is almost always a graph: user belongs to a team, team owns territories, territory rolls up to a region, and the VP sees everything under her node. That is hierarchical, and hierarchies change. Someone gets promoted on Tuesday and the historical dashboard has to answer whether she can now see last quarter's numbers for her new region. That is a product decision, not a technical one, and nobody asks it during the quote.

RBAC, ABAC and the moment roles stop being enough

Every project starts with four roles. Admin, manager, analyst, viewer. By month four you have "analyst but not salary data," "viewer but only the EMEA client," and "external auditor, read-only, expires in 90 days." That is not RBAC anymore, that is attribute-based access control, and the difference matters because RBAC is a lookup and ABAC is an evaluation.

The competent move is to model permissions as resource plus action plus condition from the start, even if you only ship four roles. Look at how Google Zanzibar does it, which is the design behind OpenFGA and SpiceDB, both open source and both worth using if your permission graph has relationships like "editor of a folder inherits editor on every dashboard inside it." Casbin is the lighter option if you just need policy expressions in-process. Oso, Permit.io and Cerbos are the commercial versions of the same idea.

The specific trap: permission checks inside a loop. You render a list of 200 dashboards, each row calls the authorization service, you make 200 network calls and the page takes 9 seconds. Every serious authorization system has a batch check or a "list objects user can access" call. Use it. If you build in-house, you need the same primitive, and you need to decide your caching TTL knowing that a revoked permission that stays cached for 5 minutes is a 5 minute security hole you will have to explain in a SOC 2 audit.

Also decide early whether permissions are additive only. If a user has two roles and one grants and one denies, what wins? AWS IAM says explicit deny always wins. Pick a rule, write it in the docs, and make the UI show the effective permission, not the assigned roles. Support tickets that say "why can't Priya see this" are unanswerable without an effective-permissions view.

User lifecycle, SCIM, and the ex-employee who still has a dashboard

If your buyer is an enterprise, they will ask for SAML or OIDC single sign-on, and then they will ask for SCIM 2.0. These are not the same thing and the second one is where the work is. SSO controls who can log in. SCIM controls who exists.

Without SCIM, deprovisioning is a promise. Someone leaves the company, IT disables them in Okta or Entra ID, and your app never hears about it. Their session token stays valid until it expires. Their scheduled Monday report keeps landing in their personal inbox because the schedule is owned by a user record you never deactivated. We have found live scheduled exports mailing revenue data to people who left 14 months earlier.

SCIM 2.0 means implementing /Users and /Groups endpoints per RFC 7644, handling PATCH with the operations array (which is genuinely awkward, and Okta and Azure send subtly different payloads for the same intent), and deciding what deactivation means for you. Do you delete the user's dashboards? Transfer them? Orphan them? Group membership sync is the part most teams underestimate: the identity provider pushes a group, you have to map that group to a role, and the mapping UI is a real feature. Budget 3 to 5 weeks for a correct SCIM implementation and group-to-role mapping. It is almost never in the original quote.

Add short-lived access tokens with a refresh mechanism so a revocation actually takes effect within minutes, not hours. If you use JWTs with permissions baked in, understand that you have made revocation impossible until expiry. Either keep tokens under 15 minutes or check permissions server-side per request against live state.

Performance, caching, and why the cache is the leak

Dashboards get slow, so someone adds caching. That is the exact moment role-based access control breaks, because a cache key that does not include the user's entitlement scope will serve the VP's numbers to the intern. It has happened, it will happen, and it is silent.

The rule is that the cache key must include a hash of the effective entitlement set, not the user ID. Key on user ID and you get a per-user cache with a 3% hit rate, which is no cache at all. Key on entitlement hash and everyone in the same scope shares a warm cache. That hash has to change the instant a permission changes, which means your permission writes must bump a version counter that participates in the key.

Other numbers worth knowing. A dashboard with 12 tiles fires 12 queries, and if each one is a fresh warehouse scan you are paying per query on BigQuery or Snowflake and waiting on cold compute. Pre-aggregation via materialized views or a Cube pre-agg layer takes a 6 second dashboard to 300ms, but every pre-aggregate must itself be entitlement-aware or you are back to the first problem. The usual solution is to pre-aggregate at the finest entitlement grain, for example per territory, and sum at read time.

Concurrency matters more than raw speed. Ten users refreshing a dashboard at 9am Monday is 120 concurrent warehouse queries. Snowflake will queue them and you get a dashboard that is fast in demo and unusable on Monday. Put a query queue with per-user limits in front, and set a hard timeout so a runaway query does not hold a connection for four minutes.

Audit trails, and what regulated buyers will actually ask for

If you sell to finance, healthcare or anyone doing SOC 2 Type II, "who saw what, when" is a deliverable, not a nice-to-have. That means an append-only log of every data access with the user, the resource, the filters applied and the row count returned. It also means logging permission changes with the actor and the before and after state, because auditors ask "who granted this and when."

The nuance is that access logs at dashboard scale are high volume and expensive to keep hot. Ship them to something append-only and cheap, keep 90 days queryable, archive the rest. Do not put them in your primary Postgres and do not let them be deletable by your application role.

The other regulated ask is masking rather than hiding. A support agent may need to know a customer record exists without seeing the email. Column-level masking is different code from row filtering, and if you built only row filtering you will be adding a second system.

What this costs and what moves the number

Adding a role-aware dashboard to an existing product where the data model is already sane and auth already exists: $15,000 to $60,000. The spread inside that band is almost entirely about how many roles multiply against how many distinct data sources. Four roles against one Postgres database is the low end. Nine roles against Postgres plus Stripe plus a warehouse plus a legacy SQL Server is the high end, because each source needs its own entitlement mapping and its own consistency story.

As part of a first release, where you are also building the identity model, the org hierarchy and the ingestion: $50,000 to $130,000 across 10 to 16 weeks.

A full platform with multi-tenancy, a semantic layer, SCIM, audit trails and customer-configurable roles: $150,000 to $350,000 over 6 to 12 months.

What specifically drives this capability up, in order of impact. First, hierarchical entitlements with inheritance, which roughly doubles the authorization work versus flat roles. Second, customer-configurable roles, meaning your buyer's customers define their own roles, which turns a config file into a product with a UI, a validator and a migration path. Third, SCIM and enterprise SSO, which adds 3 to 5 weeks. Fourth, real-time data, because caching plus entitlements plus freshness is the hardest triangle here. Fifth, exports and scheduled reports, which double every access-control surface. Sixth, historical permission accuracy, meaning the dashboard must answer as of a past date under past entitlements, which requires temporal permission storage and is the single most expensive requirement in this list.

When you should not build this

If your dashboard is internal-only, under about 50 users, and the roles map cleanly to teams, do not build it. Use Metabase, and use a paid tier, because sandboxing and row-level permissions are not in the open source version. A year of that licence is a rounding error against $40,000 of engineering, and you get scheduled reports, a query builder and SSO without writing any of it.

If you need to embed analytics inside your own SaaS product for your customers, and the charts are not your differentiator, price Omni, Preset, Explo and Luzmo before you write code. Embedded BI vendors have already solved the multi-tenant entitlement problem, and their signed-token embedding model is a genuinely good design.

If you are on Salesforce, Odoo, HubSpot or NetSuite and the data lives there, their native reporting already inherits the platform's permission model. Rebuilding it outside means rebuilding the permission model too, and you will get it wrong.

Build custom when the dashboard is the product, when the entitlement logic is genuinely unusual (per-record ownership, time-boxed access, consent-driven visibility), when you need it inside your own UI with your own interactions, or when per-seat BI pricing at your user count exceeds the build cost within 18 months. Run that last one as a real calculation before anyone writes code: seats times list price times 24 months, against the build quote.

How to brief and vet a developer for this

Brief them with the permission matrix, not the chart list. Write out every role against every resource against every action, including export, share and schedule. If you cannot fill that grid, you are not ready to quote, and any vendor who quotes anyway is guessing. Include your org hierarchy and say explicitly whether managers see their reports' data.

Ask where they enforce row-level filtering, and why there. If the answer is "in the frontend" or "we hide the components," stop. If they say Postgres RLS, ask what happens to the query plan and how they handle the connection pooler resetting session variables, because PgBouncer in transaction mode will silently break SET LOCAL patterns if you get it wrong.

Ask how they cache a dashboard tile for a user with restricted access. If the cache key does not include entitlement scope in their answer, they have not shipped this.

Ask what happens when a user's role changes mid-session. The good answer involves token lifetime, a permission version counter, and cache invalidation. The bad answer is "they log out and back in."

Ask how CSV export enforces the same permissions as the dashboard. If exports are an afterthought in their answer, they will be an afterthought in the build.

Ask whether they have implemented SCIM, and if so, what PATCH operation from Azure AD gave them trouble. Someone who has done it has a story. Someone who has not will describe SCIM as "just a REST API."

Ask for their position on RBAC versus ABAC, and whether they have used OpenFGA, SpiceDB, Casbin or Cerbos. Not using one is fine. Not knowing they exist is not.

Finally, ask them to tell you about a time permissions leaked in something they built. Everyone who has shipped this has that story. The ones who claim it never happened have either not shipped it or have not looked.