18.3 Mask Unauthorized Cell Values in Query Results

When an end user queries a table protected by Oracle Deep Data Security (Deep Sec), unauthorized column values are masked and returned as NULL rather than the entire row being filtered out.

Because the row remains available, Oracle SQL applies its standard NULL-handling rules to these masked values. For example, an analytic SUM function ignores NULL values and returns NULL if its evaluation window contains no non-NULL values.

Distinguish masked values from actual NULLs

Standard NULL-handling functions like NVL(amount, 0) cannot differentiate between an unauthorized value masked as NULL and a genuine NULL explicitly stored in the database.

When your query logic requires you to replace only unauthorized values (for example, substituting them with zero) while preserving genuine NULLs, you must use the ORA_IS_COLUMN_AUTHORIZED function.

For complete function syntax and usage details, see Use the ORA_IS_COLUMN_AUTHORIZED Function.

Example: Replace unauthorized amounts with zero in a running total

This example demonstrates how to substitute unauthorized values with zero inside a running total calculation.

1. Create the data grant
   The following grant keeps six tenant-10 rows available to the sales_analyst_role, but explicitly excludes the amount and secret_note columns. Consequently, the value of amount is returned as NULL for every tenant-10 row.

   Note:

   This assumes no other data grant authorizes the amount column.

   CREATE OR REPLACE DATA GRANT sales_app.tenant_sales_analytics AS
     SELECT (ALL COLUMNS EXCEPT amount, secret_note)
     ON sales_app.sales
     WHERE tenant_id = 10
     TO sales_analyst_role;

2. Create the query
   The following query compares a standard running total (which processes the masked NULLs) against a running total that leverages ORA_IS_COLUMN_AUTHORIZED to dynamically replace unauthorized amounts with zero.

   SELECT 
       category,
       month_no,
       amount,
       -- Standard calculation: Evaluates masked values as NULL
       SUM(amount) OVER (
           PARTITION BY category
           ORDER BY month_no
           ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
       ) AS running_total,
       
       -- Adjusted calculation: Replaces unauthorized values with 0
       SUM(
           DECODE(
               ORA_IS_COLUMN_AUTHORIZED(amount),
               FALSE, 0,
               TRUE, amount
           )
       ) OVER (
           PARTITION BY category
           ORDER BY month_no
           ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
       ) AS running_total_with_zero
   FROM sales_app.sales
   ORDER BY category, month_no;

3. Verify the result
   The query returns the following result.

   CATEGORY  MONTH_NO  AMOUNT  RUNNING_TOTAL  RUNNING_TOTAL_WITH_ZERO
   --------  --------  ------  -------------  -----------------------
   A                1    NULL           NULL                        0
   A                2    NULL           NULL                        0
   A                3    NULL           NULL                        0
   A                4    NULL           NULL                        0
   B                1    NULL           NULL                        0
   B                3    NULL           NULL                        0

   • RUNNING_TOTAL: Returns NULL because the analytic window contains no authorized, non-NULL amount values.
   • RUNNING_TOTAL_WITH_ZERO: Returns 0 because ORA_IS_COLUMN_AUTHORIZED identifies the values as unauthorized (FALSE), prompting the DECODE function to feed 0 into the SUM function.

Tip:

When handling unauthorized values in analytic functions, you must choose between input-level and output-level substitution based on your business logic.

• Input-level substitution (use ORA_IS_COLUMN_AUTHORIZED inside the function): Use this method if the business calculation requires the unauthorized value to be mathematically treated as zero. Note that substituting inputs fundamentally alters the results of functions like AVG, COUNT(amount), and regression functions.
• Output-level substitution (use NVL outside the function): If the underlying math should remain untouched (preserving standard NULL-handling behavior) and you only want the final displayed result to show as zero, do not modify the inputs. Instead, wrap the completed analytic expression in an NVL function.

For additional details on how column-level security impacts mathematical outcomes, see Understand the Effect of Column Security on Analytical Results.