Row-Level Security Leak and a Flaky Nightly Refresh

Check in this order - cheapest and most likely first

1. Is the Regional Manager actually in more than one group? Add the user to Exec for a pilot and the union of entitlements becomes two regions. Evidence: the site's group membership list for that user. Settles it in two minutes and is surprisingly often the answer.

2. Does the calculated field return NULL, and what does the filter do with NULL? A data source filter [Mapped Region] = [User Region] excludes NULLs - but if the filter was built as "keep all values where mapped region is not X" or a quick filter with "Show All Values", a NULL-mapped user passes through. Evidence: publish a diagnostic sheet showing USERNAME() and the mapped region for the affected user.

3. Email string mismatch. Trailing spaces, mixed case, an alias vs the SSO identity, a distribution list instead of a person. A near-miss on the key means no match - which either hides everything or, if a wildcard or blank row exists, shows everything. Evidence: TRIM(LOWER()) comparison between USERNAME() and the security table's email column for the affected user.

4. A blank or wildcard row in the security table. One empty Region row joined broadly can grant access to all regions. Evidence: filter the security table for NULL, blank, or '%' region values and count rows.

5. Join type and grain. If sales join to the security table many-to-many (a rep with two region rows), rows fan out and a user may see a region they are not entitled to. Evidence: COUNT(*) vs COUNTD(Order ID) on the joined result.

6. Stale published version. The user may be on a workbook version published before the filter was added. Evidence: compare revision history against the fix date.

Immediate containment: revoke the Regional Manager group's access to the workbook until the cause is identified. Do not debug a live access-control defect with 450 users exposed. Then fix, re-test as each of the three personas, and re-publish.

Investigate

1. Read the actual failure message and the refresh history, not the summary. "Connection lost" is generic; the underlying error (timeout, pool exhaustion, VPN drop, credential expiry, a source table locked by another job) is usually one level down in the run log.
2. Correlate with the calendar. Twice a week, not nightly, points at an external event: a competing ETL job on the same source, a maintenance window, a database failover, or a payload spike on a particular data load. Pull the failure timestamps and line them up against the source system's job schedule.
3. Check credentials. Extract credentials that are tied to a person expire when that person changes roles or a password rotates - a classic twice-a-week pattern if the account is also used interactively.
4. Check the connection type and location. A live connection through a VPN or a desktop gateway will fail whenever that path is interrupted; an extract refreshed by the server needs the driver installed on the server, not just the author's machine.
5. Check volume. 8.5M rows plus a hand-maintained union is a large nightly pull. A row-count spike can trip a statement timeout that is invisible on normal nights.

Harden

• Move to a service account with non-expiring credentials, owned by a team rather than a person.
• Stagger the schedule away from the competing job and add a retry (a second attempt 30 minutes later covers transient network faults).
• Alert on failure to a monitored channel, not to the tab's owner. Two failures a week for the last quarter should have been an incident, not a rumour.
• Fail the publish, not just the refresh, when validation checks fail, so stale data is visible as stale.
• Avoid the union of a hand-edited Excel file inside a production extract at all - see the governance answer.

The root problem

Access control implemented in a spreadsheet owned by one person is not access control; it is a single point of human failure. The leak, the missed monthly update, and the refresh fragility all trace to the same design choice. Fix the design, not the spreadsheet.

Target model

1. Entitlements live in a governed database table, one row per (user, region), with an owner group, an audit column (who changed it, when), and a change process. Not a union of an Excel file inside the extract.
2. Map identity through SSO, not email strings. Use a user function (USERNAME() with a domain-qualified convention, or a user attribute function where the identity provider supplies region as an attribute). String-matching email addresses is what produced the mismatch class of bug in the first place.
3. Enforce at the data source, not the worksheet. A data source filter on the entitlements join applies to every sheet and every new sheet by default; a worksheet filter is one drag away from being removed.
4. Separate the personas. Exec and Store Analyst likely need different data sources. One security model trying to serve 20 executives and 400 store analysts with different grains is where most leaks come from.
5. Certify and document. The workbook's data source is certified with a named owner, a refresh SLA, and a stated row-level security rule that a new analyst can read in one paragraph.

How to test it

• Maintain test accounts for each persona and run the workbook as them after every publish. A single test per persona catches leaks before users do.
• Build a reconciliation sheet inside the workbook: total revenue visible to the current user, versus the full total. Any Regional Manager whose visible total equals the company total is a red flag regardless of what the filters say.
• Row-count assertion in the refresh: distinct regions in entitlements must equal the master region list; refresh fails loudly otherwise.
• Audit query on the entitlements table monthly: rows with blank region, duplicate (user, region) pairs, or users with no group membership.

Sequence: contain the leak, then migrate entitlements to the database, then retire the Excel union. Do not do the migration first - the exposure is live.