Row-Level Security, Roles & Groups

Row- and column-level security, roles and groupsWho may open what, who sees which rows, and how each column is shown to them — decided once from your Entra ID groups and enforced on the server for every query, sheet and export. A bank's trade blotter is one table, but the sales desk in Frankfurt may see only EMEA trades, risk sees every trade with counterparties pseudonymised, internal audit sees traders masked and no commission at all, and HR sees salaries the managers may not. ATX AnalyticsTable answers that with three layers that build on each other. Roles — Viewer, Analyst and Admin — come from your identity provider and decide what kind of work a person may do. Object-level access decides which projects, dashboards and connections they may open. A data-access policy, kept with the project, decides which rows of each table and sheet they see and how every column is shown: in full, masked, as a salted pseudonym, empty or not at all. The server applies the policy to every query on the project's DuckDB database, to the project file before it reaches the window, and to imports, exports and copies, so totals, charts, pivots, the AI assistant and every exported file cover only what that person may see. Nothing in the window can switch it off, and administrators are not exempt. Every screen in this chapter comes from the DataAccess-RLS-Test project: two DuckDB tables, a chain of views, a Salaries sheet and nine personas you can preview one by one.

What it does

Product roles — Viewer, Analyst and Admin — taken from Microsoft Entra ID app roles (AT.Viewer, AT.Analyst, AT.Admin) or mapped from any Entra, OIDC or SAML groups, enforced by the server on every request
Object-level access: one project folder, dashboard, database or cloud-storage connection or mail profile shared with named people and groups at View, Edit or Manage, or denied outright
Row-level security on DuckDB tables, views and sheets: each rule names who it is for and which rows they see — all, none, or a condition such as region in the regions this person holds
Column-level security with five treatments: clear, hash (a salted SHA-256 pseudonym that still groups and joins), mask (keeping the last few characters), null (present but empty) and hide (not there at all)
User attributes in the policy — region, department, desk — assigned to groups or to single people, so one rule covers every desk instead of one rule per desk
Closed by default: a secured table or sheet shows nothing to someone no rule names, and a missing attribute matches nothing rather than everything
Rules combine cell by cell: a manager can see every colleague's name from one rule and salaries only for their own department from another
Personas — named test identities in the policy — with Validate showing what each would see before anything is saved, and Preview as reopening the project exactly as one of them sees it
Show what runs: the SQL the server actually executes for a query under the current identity, without running it
One policy for the whole installation, pushed by the administrator, that always wins over a project's own and can switch project policies off
Audit records for every query decision, every filtered project load, every protected save, every preview, every policy change and every refusal

How it works

Roles first. In the Entra admin centre, the app registration the product signs in with gets three app roles with the values AT.Admin, AT.Analyst and AT.Viewer; under Enterprise applications → Users and groups, people or groups are assigned to them. Nothing else is needed for those three values. To use groups you already have instead, the administrator maps them in the role map — ANALYTICS_RBAC_ROLE_MAP, the [rbac] section of config.ini or rbac.policy.json — by app-role value or group object id, and adds the groups claim to the token ("Groups assigned to the application", which stays under Entra's 200-group limit). The highest matching role wins; a signed-in person nobody matches gets the default role — Viewer once a role map is configured. Roles are read from the token, so a new assignment applies after the person signs in again.
Per-person security needs a sign-in. On a server installation with Entra ID, OIDC or SAML every request carries the person's identity, groups and roles. On a desktop installation nobody signs in: the person at the keyboard is the single principal desktop, who owns the projects they build — the desktop is where a policy is written and previewed, and a server is where it separates people.
Open the project and go to Settings → Settings → Set / Restore → Data access. The panel says whether the project is secured, who the server takes you to be, and — for every secured table and sheet — whether you have full, filtered or no access, which rows you see, how each column is shown and by which rules. Administrators and the owners the policy names also get the policy editor.
A policy is one JSON file, SECURITY/data-access.json in the project folder, versioned with the project. Start from a template fills in a working example. Targets name a DuckDB table or view, or a sheet with the row its headers are on. Each target has rules, and each rule is a grant: principals, rows, columns.
Principals say who a rule is for: group:<group object id> for a directory group, user:<sign-in name, e-mail or object id> for one person, role:<app role>, rbac:viewer, rbac:analyst or rbac:admin for a product role, service:<token> for automation, desktop, system for unattended work, and * for everyone. Every comparison ignores case. Entra puts group object ids, not names, into the token, so a rule for an Entra group names its id; a group name matches only where the sign-in or the SCIM directory supplies names.
Attributes carry the values a row filter compares with. "region": { "group:<EMEA sales>": ["EMEA"], "user:anna@bank.com": ["EMEA", "APAC"] } gives the EMEA group one region and Anna two; a rule with rows { "column": "region", "op": "in", "attribute": "region" } then shows each person the rows of the regions they hold. Values are compared as data and never pasted into SQL.
Personas mirror real people for testing. A persona has an id, a label, and the user name, groups and roles of the person it stands for — the same group ids the real person's token will carry. Validate checks the draft strictly — an unknown field, operator or treatment is an error — and lists, for every persona, the rows and columns they would get. Save policy writes the file; it applies from the next query.
Preview as reopens the project through one persona's access, instead of yours, never combined with it. A strip under the ribbon names the persona, saving is turned off, and the preview is recorded in the audit trail. Stop preview returns to your own view.
When a restricted person queries the project, the server parses the SQL with DuckDB's own parser, replaces each secured table with a subquery that returns only their rows and treated columns, parses the result again and checks it before it runs. Views over a secured table are rewritten the same way. SQL that cannot be secured — SQL held in a string, direct file reads, table macros, ATTACH, COPY, SET — is refused with a sentence that says why.
When a restricted person opens the project, a secured sheet arrives with withheld rows blank in place, so every formula reference still points where it did; cached formula results are dropped and recalculated over what they may see. Saving cannot overwrite a secured sheet with a filtered copy — the stored sheet is kept.

Practices that keep it reliable

Grant to groups, not people — joiners, movers and leavers are then handled in Entra ID and the policy never changes; keep user: principals for the genuine exceptions
Name Entra groups by object id and put the group's display name in the rule's description, so the policy stays readable and still matches the token
Add a persona for every kind of reader before going live — including one with no grant at all — and Validate after every change; the outsider persona is the quickest proof that the target is closed
Use hash rather than mask where analysts still need to count, group or join on a column, and hide where the column must never leave a team, such as national identifiers
Set hashSalt to a long random secret and never reuse the public test salt; a pseudonym is only as strong as its salt
Name at least two owners — a group, not a single person — so the policy can still be changed when somebody is away
Push the policies that matter to the whole organisation from the installation, where a project cannot loosen them, and leave project policies for project-specific detail
Read Show what runs with your auditor once: the rewritten SQL is the plainest evidence of how the filter is applied

In a regulated environment

Need-to-know is enforced where the data is served, not where it is displayed: totals, charts, pivots, Find, copy, every export and the AI assistant only ever receive rows the person may see, so there is no client-side filter to work around.
Closed by default and fail closed: a person no rule names sees nothing, a missing attribute matches nothing, and a policy with an error is not used — its data is unavailable to everyone except administrators, owners and break-glass principals, who see it unfiltered so they can repair it.
Administrators are not exempt. Being an administrator lets you edit the policy, not read its data; break-glass principals exist only in the installation policy, and every query they run is audited as a bypass.
Segregation of duties is expressible directly: risk sees exposure by pseudonymised counterparty, audit sees every trade with traders masked and no commission, HR alone sees national identifiers — from one table, with one policy.
Evidence for reviews: Validate lists each persona's access, Show what runs prints the executed SQL, and the tamper-evident audit trail records each decision, each filtered load and the hash of the policy before and after every change.
Stated plainly: external databases are not rewritten — their own row-level security applies, for example PostgreSQL under Entra on-behalf-of; on a desktop installation the project files are on the user's own disk, so the boundary for confidential data is a server installation; and a restricted person can see that a table or column exists, but not its values.

One project, nine people — what each one sees

The data owner — everything, in full (Settings → Settings → Set / Restore → Data access). The desktop user who built the project is named an owner, so the Salaries sheet shows all twelve staff with their department, region, salary and e-mail. Owning the policy is what lets you edit it; it is the owners rule that shows the data — an owner without a rule would see nothing.
Your access, and the policy behind it (Settings → Settings → Set / Restore → Data access). The top of Settings → Data access says whether the project is secured and which file the policy comes from, who the server takes you to be — here the desktop user, holding the principals desktop, user:admin, rbac:admin and * — and, for every secured table and sheet, the access you have, the rows you see, how each column is shown and which rules decided it.
Validate — every persona, before you save (Settings → Data access → Policy → Validate). Validate checks the draft strictly and then evaluates it for every persona in the policy. EMEA sales gets main.trades filtered to region is one of ["EMEA"] with commission empty, trader as a pseudonym and counterparty_email masked, by the rule sales-by-region, and no access at all to employees or the Salaries sheet. Bob, a salesperson with no region assigned yet, gets nothing — a missing attribute matches no rows.
Ops manager — every name, only Ops salaries (Settings → Data access → Preview as → Ops manager). Two rules name the managers group: a directory rule that shows every employee with salary empty, and an own-department rule that shows salary for rows whose department is the manager's. They combine cell by cell, so the Ops manager sees all twelve names but salaries and (masked) e-mails only for the three Ops rows. The Totals sheet, whose formulas read the Salaries sheet, is recalculated on open: 183,000 over 3 rows instead of the owner's 919,000 over 12.
EMEA sales — the same query, filtered and masked (SQL → Queries → 01-try-as-each-persona → 02-columns-you-get → Run). SELECT id, region, trader, counterparty_email, commission FROM trades, run in the SQL editor as EMEA sales, returns EMEA rows only. Commission is empty, the trader is a 16-character salted pseudonym — the same trader always gets the same pseudonym, so counting and grouping by trader still work — and the e-mail keeps only its last four characters.
Risk — every trade, counterparties pseudonymised (SQL → Queries → 02-columns-you-get → Run). The risk rule shows every row and every column in full except the counterparty, which becomes a pseudonym. Exposure can still be summed and grouped by counterparty — equal counterparties stay equal — without anyone in risk reading a client's e-mail address.
Internal audit — a hidden column is not there at all (SQL → Queries → 02-columns-you-get → Run). Audit sees every trade with traders masked, but commission and counterparty_email are hidden: for this person the columns do not exist, so the same query stops with "Referenced column counterparty_email not found". Hide is the treatment for data that must never leave a team; null keeps the column and empties it.
Saved results are re-read through the policy (Pinned trades). A pinned block keeps a copy of its result in the project file. For a restricted person that copy is blanked on load and the pin is re-read through the policy, so the region-by-desk summary shows EMEA sales four EMEA desks and 16 trades, and Internal audit all twelve desk rows across three regions.
Outsider — closed by default (Settings → Data access → Preview as → Outsider). A person no rule names sees no rows of any secured table or sheet. The Salaries sheet keeps its title and header row — rows above the header are not filtered — and every row below arrives blank in place, so formulas elsewhere still point where they did.
Show what runs — the evidence for an auditor (Settings → Data access → Show what runs). Type a query and Show prints the SQL the server would execute for the current identity, without running it. Each column of the secured table is wrapped in a barrier — CASE WHEN region IN ('EMEA') THEN … END — the trader goes through sha256 with the salt, the e-mail through the mask, and the table is read under a per-query random alias. The salt itself is printed as <hashSalt>, so the screen can be shown or pasted into a report.
What a restricted person cannot do (Settings → Data access → Show what runs). SQL that cannot be secured is refused with a sentence that says why. SELECT * FROM query_table('trades') would read the table by a name held in a string, which the rewrite cannot see, so it is refused — as are direct file reads, table macros, COPY, ATTACH, SET and writes into a secured table. Every refusal is a dataPolicy.denied record in the audit trail.

Screens

Preview as the Ops manager: every colleague's name, but salary and e-mail only for the Ops department — two rules combined cell by cell. The strips under the ribbon say whose view this is and that cells are withheld.
Your access while previewing: employees and Salaries filtered by manager-own-dept and manager-directory; no access to trades.
Pinned trades as Internal audit: every region and desk — audit's rule shows all rows, and masks or hides columns instead.

Glossary terms on this page

KPI dashboard: definition

ATX Institute on LinkedIn

This site needs JavaScript for the full experience. Every page above is reachable without it.