18.6 Design Guidelines for Analytics with Oracle Deep Data Security

Analytical queries process only the data authorized by your data grants. To ensure your grouping, ordering, and aggregations yield accurate results, follow these guidelines.

• Separate access rules from analytical logic: Define all access boundaries within your data grants. Always place analytical operations (such as window functions, aggregates, PIVOT, and QUALIFY) in the query that reads the object, never in the data-grant predicate itself.
• Secure all referenced objects: Configure data grants for every object that an analytical query references. This includes tables and views used in joins and subqueries.
• Authorize critical operational columns: Ensure the end user has authorization to access any columns required for grouping, joining, partitioning, ordering, or mathematical calculations. If these structural columns are masked as NULL, they can unintentionally skew the analytical results.
• Preserve standard NULL behavior during calculations: Allow Oracle SQL to apply its normal NULL semantics to unauthorized values during query execution. If a report requires a display substitute, such as 'REDACTED' or 0 for NULL, apply it only in the final, outermost SELECT list.
• Clarify statistical and approximate results: Masked NULL values are often ignored by statistical functions. Whenever a calculation's actual input size might differ from the overall row count, display the number of contributing non-NULL values—for example, by pairing COUNT(column) or REGR_COUNT alongside COUNT(*)—so users understand exactly how much data factored into the result.