18.5 Understand the Effect of Column Security on Analytical Results

When an end user lacks authorization to view a specific column value, column-level security in Oracle Deep Data Security (Deep Sec) replaces that value with NULL instead of filtering out the entire row.

Because Oracle SQL applies standard NULL-handling rules to these column values, the outputs of counts, groupings, sort orders, window functions, and statistical calculations can be significantly altered, even though the total row count remains unchanged.

The following sections explain how specific Oracle SQL functions process these NULL values.

Topics:

• About Core SQL Behavior for Masked Data
• About Analytical Function-Specific Effects
• Interpret PIVOT Results
• Interpret Regression Results
• Interpret APPROX_COUNT_DISTINCT Results

18.5.1 About Core SQL Behavior for Masked Data

When an unauthorized value is returned as NULL, standard SQL conditions (such as IS NULL or equality checks) cannot distinguish it from a genuine NULL stored in the database. If your query must differentiate between the two, use the ORA_IS_COLUMN_AUTHORIZED function on the base column.

The following behavior applies:

• A comparison such as sensitive_column = 'X' evaluates to UNKNOWN when sensitive_column is NULL; therefore, the row is not selected.
• The sensitive_column IS NULL predicate evaluates to TRUE for both stored NULL values and unauthorized values that have been masked to NULL.
• Most aggregate functions ignore NULL values. However, COUNT(*) counts every row, regardless of whether a column value is NULL.
• If no non-NULL values remain, COUNT(expression) returns 0, whereas SUM, AVG, MIN, MAX, and other aggregates that ignore NULL values return NULL.
• Scalar expressions can preserve or replace NULL values. For example, NVL(sensitive_column, 0) replaces NULL with 0 for use in subsequent calculations.

18.5.2 About Analytical Function-Specific Effects

Each SQL feature applies its normal NULL behavior to the rows and columns visible to the query. The following tables describe how specific analytical operations react to masked values and provide design guidance for handling them.

Core SQL and NULL behavior

SQL Feature	Effect on Masked (NULL) Values	Design Guidance
Projection and scalar expressions	The expression receives NULL. Arithmetic normally returns NULL; however, NVL, COALESCE, CASE, or DECODE can replace it.	Substitute a display value only in the final SELECT list. Do not supply a replacement value before an aggregate unless that change is intended.
WHERE, HAVING, QUALIFY	Equality, inequality, and range comparisons with NULL evaluate to UNKNOWN. IS NULL matches both stored and unauthorized NULLs.	Use ORA_IS_COLUMN_AUTHORIZED if the query logic must distinguish between stored and unauthorized NULLs.
COUNT(*)	Counts a returned row even if one or more of its column values are unauthorized.	If a row should not be counted at all, restrict access with the data grant's row predicate.
COUNT(column), SUM, AVG, MIN, MAX	These functions ignore unauthorized values returned as NULL. If no non-NULL values remain, COUNT(column) returns 0; the other listed functions return NULL.	Display COUNT(*) alongside COUNT(column) so users know how many returned rows actually supplied a valid value.

Windowing, ordering, and ranking

SQL Feature	Effect on Masked (NULL) Values	Design Guidance
LAG, LEAD	Default NULL handling returns NULL. IGNORE NULLS skips the masked value, but the row itself remains in the partition.	Do not assume that a skipped value means the row was entirely removed from the data set.
FIRST_VALUE, LAST_VALUE	RESPECT NULLS can return NULL; IGNORE NULLS searches for a non-NULL value. Stored NULL values and unauthorized values returned as NULL are treated the same.	Explicitly specify RESPECT NULLS or IGNORE NULLS. When an analytic ORDER BY clause is present and the window frame is omitted, LAST_VALUE uses the default frame, which ends at the current row. Specify RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING when the query needs the last value in the complete partition.
PARTITION BY and grouping keys	Rows whose partitioning or grouping value is NULL are placed in the same partition or group. This includes stored NULL values and unauthorized values returned as NULL.	Ensure that a partitioning or grouping column is available when the query must keep its values separate.
ROWS windows	A row remains in a ROWS frame even when a column value in that row is unauthorized. An aggregate that ignores NULL does not use that value.	Distinguish between the number of rows in the frame versus the number of non-NULL values actually used by the calculation.
RANGE windows, ranking, and analytic ORDER BY	Rows whose ordering value is NULL have the same sort value. RANGE frames treat them as tied. ROWS frames and functions that depend on row order can vary when the order is not unique. By default, ascending order uses NULLS LAST and descending order uses NULLS FIRST.	Use ordering columns that the end user is authorized to access. When the query requires a stable order, add an authorized column that makes the ordering unique.
KEEP (DENSE_RANK FIRST or LAST)	An unauthorized ranking value is NULL and follows the selected NULL ordering, which can change the first or last dense-rank set. An unauthorized aggregate value is ignored.	Ensure both the ranking column and the aggregate column are authorized when the result depends on them.

Cross-tabulation, joins, grouping, and result shaping

SQL Feature	Effect on Masked (NULL) Values	Design Guidance
PIVOT	A NULL pivot key does not match a listed non-NULL pivot value. A NULL-ignoring aggregate does not use a NULL measure. COUNT(*) can still count a row when its pivot key matches. Source columns not referenced by the pivot clause become implicit grouping columns, and their stored or unauthorized NULL values can combine groups.	Restrict rows with the data grant's row predicate when necessary, and ensure that the pivot key, measure, and required grouping columns are available.
UNPIVOT	For a single-measure UNPIVOT, the default EXCLUDE NULLS omits a generated row whose value is NULL. For a multi-measure UNPIVOT, it omits the generated row when all values in the tuple are NULL. INCLUDE NULLS retains the generated row.	If the result must show why a value is NULL, calculate the authorization indicator from the base column before applying UNPIVOT.
Joins	An unauthorized join value is NULL and does not match a non-NULL value in an equality join. An outer join can still retain the row and add NULL values for the other object.	Ensure that columns used for analytical joins are available, or use data grant row predicates to limit the rows that can take part in the join.
Partitioned outer joins	Data densification can create rows and NULL values in addition to stored or unauthorized NULL values.	Keep the densification key in the query so that a NULL is not automatically interpreted as missing source data.
ROLLUP, CUBE, GROUPING SETS, GROUPING, GROUPING_ID	Rows with an unauthorized grouping value are grouped under NULL. GROUPING distinguishes a NULL generated by ROLLUP or CUBE from an ordinary NULL, but it cannot distinguish a stored NULL from an unauthorized value returned as NULL.	Use ORA_IS_COLUMN_AUTHORIZED in the base query if the report must distinguish between the two cases.
Top-N and partitioned row limiting	Unauthorized ordering values are NULL and therefore have the same sort value. FETCH ... ONLY can select different rows among tied values unless the order is unique; WITH TIES returns all rows tied at the boundary. WITH TIES is not available with partitioned row limiting.	Use partitioning and ordering columns that the end user is authorized to access. When the result requires a stable order, add an authorized column that makes the ordering unique.

Statistical, approximate, and numerical analysis

SQL Feature	Effect on Masked (NULL) Values	Design Guidance
APPROX_COUNT_DISTINCT	The function ignores unauthorized values returned as NULL, so it estimates the number of distinct, available, non-NULL values.	Do not interpret this estimate as the total number of rows returned by the query.
REGR_*	A regression pair is not used when either the dependent or independent expression is NULL. REGR_COUNT counts only non-NULL pairs and returns 0 when none remain; other regression functions can return NULL when their input is insufficient.	Compare REGR_COUNT with COUNT(*) to understand how many rows actually supplied a complete pair.
T-tests and DBMS_STAT_FUNCS	NULL handling and minimum input requirements depend on the selected routine. Unauthorized values returned as NULL can reduce the input or leave too few values.	Check the NULL and minimum-input rules for the routine used by the query.
UTL_NLA	An unauthorized matrix or vector element is NULL and can make the numerical input incomplete.	Do not call the routine with incomplete input, or ensure that every required element is available.

Pattern and model analysis

SQL Feature	Effect on Masked (NULL) Values	Design Guidance
MATCH_RECOGNIZE	A comparison in DEFINE evaluates to UNKNOWN when an operand is NULL, which can prevent or change a match. A NULL expression in MEASURES is evaluated after recognition and does not itself change which rows matched.	Ensure that columns used by DEFINE, PARTITION BY, and ORDER BY are available. Use a data grant row predicate when a row must not take part in pattern matching.
MODEL	An unauthorized measure is a NULL or absent measure cell during rule evaluation, according to KEEP NAV or IGNORE NAV. An unauthorized dimension value is a NULL dimension value and follows the model's cell-reference and uniqueness rules.	Ensure that required model dimensions and measures are available, and write rules for the resulting NULL behavior.

18.5.3 Interpret PIVOT Results

The PIVOT operation processes only the pivot keys and aggregate inputs that are visible to the query. A NULL pivot key does not match a listed non-NULL value, and a NULL-ignoring aggregate does not use a NULL measure.

In the sales_app.tenant_sales_analytics data grant example, the secret_note column is not included in the data grant’s column list, so its values are returned as NULL.

The following query uses secret_note as both the aggregate input and the pivot key while attempting to count a specific note value:

SELECT NVL(a1, 0) AS note_count
FROM (
  SELECT secret_note
  FROM sales_app.sales
)
PIVOT (
  COUNT(secret_note)
  FOR secret_note IN ('TEN10-A-1' AS a1)
);

NOTE_COUNT 
---------  
        0

The query returns 0 because secret_note is masked as NULL and therefore does not match 'TEN10-A-1'. Consequently, COUNT(secret_note) has no non-NULL values to count.

The six tenant-10 rows remain in the authorized row set, but secret_note is returned as NULL. A query that uses COUNT(*) can still count those rows because COUNT(*) does not evaluate the contents of the secret_note column.

Tip:

PIVOT implicitly groups by any source columns not referenced in the pivot clause. Select only the source columns strictly required by the operation, and ensure the end user is authorized to access every pivot or grouping column the query uses.

18.5.4 Interpret Regression Results

Regression aggregate functions, such as REGR_COUNT and REGR_SLOPE, form a pair from the dependent and independent expressions in each row. If either expression evaluates to NULL, the pair is discarded and not used in the calculation.

The REGR_COUNT function is useful for showing how many pairs contributed to the result.

The following query counts pairs formed from the length of secret_note and month_no:

SELECT REGR_COUNT(LENGTH(secret_note), month_no) AS sample_count
FROM sales_app.sales;

SAMPLE_COUNT 
---------  
        0

If secret_note is restricted by a data grant, its value is returned as NULL in every row. Consequently, no single row supplies both of the values required to form a valid regression pair.

18.5.5 Interpret APPROX_COUNT_DISTINCT Results

The APPROX_COUNT_DISTINCT function estimates the number of distinct non-NULL values in the rows returned by the query. Rows excluded by data grants do not contribute at all, and unauthorized column values returned as NULL are not counted.

The following query compares exact and approximate distinct customer counts for each category using a restricted data set:

SELECT category,
       COUNT(DISTINCT customer_id) AS exact_customers,
       APPROX_COUNT_DISTINCT(customer_id) AS approximate_customers
FROM sales_app.sales
GROUP BY category
ORDER BY category;

CATEGORY   EXACT_CUSTOMERS  APPROXIMATE_CUSTOMERS
--------   ---------------  ---------------------
A                        3                      3
B                        1                      1

For this small data set, both calculations correctly return 3 for category A and 1 for category B. The approximate result remains an estimate. If there are other rows in the table belonging to restricted tenants, their customer identifiers do not contribute to these totals because those rows are entirely filtered out by the data grant before the aggregation occurs.