18.1 About Data Authorization for Analytics

Oracle analytical SQL performs operations such as window calculations, cross-tabulations, advanced aggregations, statistical analysis, and pattern matching directly within the database. By running these operations on data governed by Oracle Deep Data Security (Deep Sec), you can build secure reports, dashboards, and analytical applications.

When an analytical query executes, Deep Sec dynamically evaluates the current end user's data grants to determine exactly which rows and column values that user is permitted to see. This architecture allows a single analytical query to serve many users with different security entitlements, completely removing the need to hard-code authorization predicates into your application logic. However, the application SQL must still supply any standard business filters, such as a chosen date range, product category, or sales status, that are required for the specific analysis.

The data exposed to an analytical query is strictly governed by the following rules:

• A cell is available to the query only if the end user is authorized to select it.
• A cell value that the end user is not authorized to select is NULL.
• Data grants are additive. All applicable data grants contribute to the cell values available to the end user.

Analytical SQL functions operate exclusively on the effective data set produced by these rules.

Representative analytical SQL capabilities

You can run Oracle analytical SQL directly against objects protected by data grants to build windowed metrics, cross-tabulation reports, statistical analyses, pattern detection, and what-if models. There is no need to define a separate security implementation for each query; the database automatically constrains the rows and column values available to the statement based on the current end user's applicable data grants. See About Data Grants.

The following table lists the analytical capability families discussed in this chapter:

Capability Family	Representative SQL Capabilities
Window and ordered calculations	LAG, LEAD, FIRST_VALUE, LAST_VALUE, ranking functions, KEEP (DENSE_RANK FIRST or LAST), running or moving aggregates, and ROWS or RANGE windows. Oracle AI Database 26ai also supports the named WINDOW clause and QUALIFY.
Cross-tabulation and result shaping	PIVOT, UNPIVOT, partitioned outer joins for data densification, and top-N queries with FETCH FIRST. Oracle AI Database 26ai also supports partitioned row limiting.
Advanced and approximate aggregation	ROLLUP, CUBE, GROUPING SETS, GROUPING, GROUPING_ID, and APPROX_COUNT_DISTINCT. Oracle AI Database 26ai also supports GROUP BY ALL.
Statistical and numerical analysis	REGR_* linear-regression functions, t-tests, DBMS_STAT_FUNCS, and UTL_NLA.
Pattern and model analysis	MATCH_RECOGNIZE (for row-pattern analysis) and MODEL (for spreadsheet-style calculations and what-if analysis).

When a query relies on sensitive columns for grouping, joining, partitioning, ordering, or mathematical calculations, always consider how the NULL values returned for unauthorized data might alter the final analytical result. For detailed behavior, see About Analytical Function-Specific Effects.