The Subscription Burst That Sent EMEA Rows to APAC
A MicroStrategy distribution service sends a monthly Revenue by Product subscription to 400 users: 220 EMEA and 180 APAC. Row-level access uses a security filter mapping user login to REGION_ID through a 400-row user-region table. The 06:00 subscription runs under one shared 'dist_user' account. The report prompt is Region=All. At 05:40, 30 APAC users are moved to a new ANZ region. The 06:00 run completes with 400 outputs. At 09:00, those 30 ANZ users report EMEA revenue in their PDFs. The audit log shows dist_user, report REV_BY_PROD, 400 rows, 400 deliveries, and a 92% cache hit ratio. Result cache is set to 'Use cache for all users' with 24-hour expiration. The security team asks whether the audit log can identify which recipients received which rows, and why the 05:40 change did not take effect.
1. Diagnose the leakage: why the 05:40 region change did not affect the 06:00 outputs, and what role the shared distribution account and cache played.
Read the worked answer
The subscription executed as dist_user, so MicroStrategy evaluated dist_user's security filter, not each recipient's filter. If dist_user has broad or All-Region access, the report query produced a broad result set. The Region=All prompt reinforced that breadth. Distribution then delivered that result to 400 recipients. The 05:40 change to 30 users' region mappings affected the recipient list and their expected security filters, but it did not change the identity under which the report ran. That is why the change did not take effect at 06:00.
The 92% cache hit ratio is a strong clue. A cached result created under dist_user, with Region=All, can be reused for later deliveries. The cache key may include the report and prompt but not the individual recipient, because the recipient is not the execution identity. The audit row '400 rows' likely describes the report result, not 400 distinct recipient-level row sets. High cache reuse plus a shared execution account is exactly the condition that turns a personalized subscription into a broadcast.
The 30 ANZ users saw EMEA rows because their PDFs came from a result that was not filtered to their new region. The defect is not the 05:40 timing alone; it is the combination of shared execution identity, an All prompt, and cache sharing.
2. Recommend a distribution and security-filter design that personalizes each recipient and prevents cross-region rows.
Read the worked answer
Run the subscription under each recipient's identity or under a controlled impersonation that applies the recipient's security filter. In MicroStrategy distribution, enable personalization and ensure the report uses the recipient's security filter rather than a single shared filter. Remove Region=All from the production subscription; use a governed region prompt that is bound to the user's region mapping, or burst on Region so each output is generated for one region. If a shared service account is unavoidable, prohibit it from having All-Region access and require per-recipient impersonation with an allow-list and audit trail.
Disable 'Use cache for all users' for this report, or ensure the cache key includes the recipient's security identity. Invalidate the cache when the user-region table changes, and run the subscription after the mapping load has committed. Add a pre-send validation that checks each output's row count and region values against the recipient's expected region, and send a test to at least one user per region before the full run.
The design should treat region mapping as governed security data, not as a report prompt. The recipient's entitlements must be resolved at execution time and must be visible in the delivery log.
3. What audit-log fields and queries would you use to prove which recipients received which rows, and what remediation evidence would you retain?
Read the worked answer
The current audit record is insufficient because it identifies dist_user and a single report row count, not the recipient-to-row relationship. Enable subscription and delivery logging with fields for subscription ID, run ID, recipient, recipient region, execution user, security filter ID, report, prompt answers, SQL fingerprint, row count, cache source, cache creation time, and delivery status. Log the actual row-level region values where possible, or a hash plus region summary per delivery.
Query for deliveries where the recipient's expected region differs from any region present in the output. Also look for identical row counts across all 400 recipients, a high cache hit ratio, and deliveries where the execution user is a shared account. Compare the 05:40 mapping change time with the subscription start and cache creation time.
Retain the run log, the recipient list used, the security-filter mapping snapshot, the cache-purge record, the corrected re-run log, and the notification list for the 30 affected users. Remediation should include re-sending personalized outputs, purging the shared cache, reviewing the dist_user entitlements, and adding a control that blocks a subscription run if the execution identity has broader access than the recipients.