Oracle Data Redaction and Nested Functions, Inline Views, and the WHERE Clause

Oracle Data Redaction affects nested functions, inline views, and the WHERE clause.

Nested functions work on actual data except in the following where they work on redacted data:
- JSON functions
- XML functions
- SQL expressions in cursor expressions
- Constructor functions
- PL/SQL functions
- User defined operators
- User defined aggregates
For example:
```
SELECT SUM(AVG(TO_NUMBER ((X)))
FROM HR.EMPLOYEES
    WHERE .....
```
Although the functions work on the actual data in column X, with the execution order being innermost to outermost, the result of the SQL expression gets redacted using full redaction as long as any redacted column is involved in the SQL expression. This is to avoid confusion on which redaction type to use to redact the result of the expression if more than one redacted column is specified in the function and each redacted column has a different function type.
Inline views are redacted outermost. For example, in
```
SELECT XYZ ...
    AS SELECT A...
    AS SELECT B...
    AS SELECT C...
```
Only SELECT XYZ is redacted after the execution of SELECT C, SELECT B, and SELECT A.
The WHERE clause is never redacted. Oracle Data Redaction redacts data only for the columns in the SELECT list.

Related Topics