Row-Level Security Leaks Through an Aggregate
A company has a single SALES subject area. A session variable INITIALIZATION block sets USER_REGION from a lookup table. A logical table filter on the SALES fact restricts rows to REGION = VALUEOF(USER_REGION). A newly added aggregate table, SALES_AGG_REGION, contains pre-summed totals by region and is mapped as an additional logical table source with a higher priority. Testing shows that a user in the East region can see national totals when a report selects only the revenue measure with no region column. The aggregate source was created without a region filter, and the filter is defined on the detail source only.
1. Explain the leak and where the security predicate must be enforced.
Read the worked answer
The filter is attached to the detail logical table source, so when the BI Server chooses the aggregate source for a measure-only query, the predicate is not applied. The aggregate returns national totals, and the user sees them.
Security predicates must be enforced at the logical level, not only on one physical source. Define the region restriction so it applies to every logical table source of the fact, including the aggregate. One approach is a logical table filter on the logical fact table that references the session variable, ensuring the predicate is added regardless of which source is selected. Alternatively, add the region column to the aggregate and apply the filter there too, so the aggregate can be constrained.
After the fix, test a measure-only query, a query grouped by region, and a query that could use either source. Inspect the physical SQL to confirm the predicate appears in every plan.
2. What else should be checked to ensure the aggregate cannot bypass security in other ways?
Read the worked answer
Confirm the aggregate grain includes region, otherwise it cannot be filtered at all and must be rebuilt or excluded from the mapping for restricted users. Check that no other logical table source, such as a summary or a materialized view, omits the predicate. Review any caching: a cache entry created under one user's credentials must not be served to another user if the result depends on the session variable. The cache key should reflect the security context, or caching should be scoped appropriately.
Also verify the initialization block runs for every connection and that USER_REGION cannot be spoofed. Test with a user whose region value is null or missing to ensure the default denies rather than allows access.