Organizations today handle massive amounts of sensitive information, ranging from Personally Identifiable Information (PII) and Protected Health Information (PHI) to financial records that should be protected from exposure to users who don’t require access to them. Data Redaction selectively redacts sensitive data at query runtime, preventing unauthorized exposure, while keeping the underlying data unchanged. In our previous blog, we discussed how we significantly enhanced the feature’s capabilities and expanded support for additional business use cases with Oracle Database 23ai.
In this blog, we’ll use query examples to demonstrate key capabilities and recent advancements in Data Redaction introduced with Oracle Database 23ai.
What’s new at a glance
Until now, Data Redaction primarily supported redacting sensitive columns in queries when these columns were not part of SQL constructs. As you expand, the need for redaction expands in business applications and complex analytics, and the need for broader SQL compatibility is essential.
Oracle Database 23ai addresses this with key enhancements that expand Data Redaction’s coverage across more complex SQL scenarios:
- Mathematical and set functions support: Data Redaction now supports aggregate functions (SUM, COUNT, MIN, MAX) and set operations (UNION, INTERSECT, MINUS). It also extends to advanced SQL constructs such as WITH and OUTER JOINs, so even complex queries can run without revealing actual data.
- Sorting and grouping: You can now use columns with redaction policies in DISTINCT, GROUP BY, and ORDER BY clauses without errors.
- Views with expressions: Columns with redaction policies can now be included in expressions (CONCAT, MIN, MAX, etc.) within regular or inline views.
- Support for redacting virtual columns in function-based indexes: Virtual columns used by function-based indexes are automatically redacted whenever their base columns are redacted, keeping sensitive data protected even when it is accessed through virtual columns.
Exploring Data Redaction enhancements with examples
We’ll now walk through query examples that reflect real-world use cases and key capabilities. To follow along, install the Human Resources (HR) sample schema (latest version here). These examples require Oracle Database 23ai version 23.6 or later.
Let’s say that the business wants to enforce that any user other than the HR schema owner sees redacted values. First, we would use Oracle’s DBMS_REDACT package to create redaction policies on the HR.EMPLOYEES table.
BEGIN
DBMS_REDACT.ADD_POLICY(
object_schema => 'HR',
object_name => 'EMPLOYEES',
policy_name => 'REDACT_DATA',
expression => 'SYS_CONTEXT(''USERENV'',''SESSION_USER'') != ''HR''');
END;
/
We will use this policy in the examples below, connecting as HR (the owner) and as a non-HR user to demonstrate both outcomes.
Mathematical and set functions support
In organizations, HR teams need to generate reports on employee statistics without exposing sensitive details such as salary to every user directly or even indirectly through functions like SUM, AVG, COUNT, etc. This helps organizations maintain strict data governance and compliance with privacy regulations.
You can now simply define a Data Redaction policy for the SALARY column, and it automatically redacts those values in the report without any application changes.
Apply a redaction policy to the SALARY column
BEGIN
DBMS_REDACT.ALTER_POLICY (
object_schema => 'HR',
object_name => 'EMPLOYEES',
policy_name => 'REDACT_DATA',
column_name => 'SALARY',
action => DBMS_REDACT.ADD_COLUMN,
function_type => DBMS_REDACT.FULL);
END;
/
In this example, we use DBMS_REDACT.FULL, which completely redacts the column values. Data Redaction also supports other forms of redaction, such as partial, random, and regular expression-based redaction, providing flexibility depending on the use case.
Query the table
SELECT department_id AS dept_id,
COUNT(employee_id) AS emp_count,
AVG(salary) AS avg_salary
FROM hr.employees
GROUP BY department_id
FETCH FIRST 5 ROWS ONLY;
This would compute employee statistics, such as the number of employees and average salary, grouped by department.
Result
The HR manager will see the unredacted average salary data when running the report.
dept_id emp_count avg_salary
—————- —————- ————-
10 1 4400
20 2 9500
30 6 4150
40 1 6500
50 45 3475.56
5 rows selected.
However, when the HR analyst (restricted access) runs the query, the average salary values are redacted, with the default redacted value for numeric columns being 0.
dept_id emp_count avg_salary
—————- —————- ————-
10 1 0
20 2 0
30 6 0
40 1 0
50 45 0
5 rows selected.
This example shows how Data Redaction can automatically redact sensitive numeric aggregates such as avg_salary, without changing a line of application code or impacting query performance.
Sorting and grouping
Oracle Database 23ai now lets you include redacted columns directly in DISTINCT, GROUP BY, and ORDER BY clauses. This means you can perform complex aggregations on redacted data without rewriting queries or risking sensitive information exposure.
This example shows how you can redact sensitive data in analytic-style queries. The following query groups employees by manager id, counts direct reports, and sums team salary, while honoring the redaction policy applied to the salary column in the previous example.
Query over redacted column
SELECT
manager_id,
COUNT(DISTINCT employee_id) AS direct_reports,
SUM(salary) AS total_team_salary
FROM hr.employees
GROUP BY manager_id
ORDER BY total_team_salary DESC
FETCH FIRST 5 ROWS ONLY;
With Oracle Database 23ai, you can now view data sorted by the aggregated total team salary for each manager, along with the distinct direct reports per manager.
Result
The HR manager will see the total team salary data.
manager_id direct_reports total_team_salary
—————- ————– ——————
100 14 155400
123 8 25900
121 8 25400
122 8 23600
120 8 22100
5 rows selected.
The HR analyst user (restricted access) will not see the total team salary data in the query result. Since all redacted values appear as 0, the rows may not be ordered by total team salary, and the database may return them in any order.
manager_id direct_reports total_team_salary
—————- ————– ——————
100 14 0
123 8 0
121 8 0
122 8 0
120 8 0
5 rows selected.
Support for redacting virtual columns in function-based indexes
Function-based indexes are created on the result of a function or expression, allowing efficient querying when those functions or expressions are used in SQL statements. Function-based indexes create virtual columns internally, where the value of a virtual column is computed on the fly. With Oracle Database 23ai, virtual columns are automatically redacted when their base columns are redacted.
In this example, let’s create a function-based index on the employees’ phone numbers with periods, hyphens, or any other characters replaced with an empty string for consistency. This automatically creates a virtual column behind the scenes. We will then explicitly add a virtual column to store the rounded values of employees’ salaries. Redaction can be applied to base columns, and in Oracle Database 23ai, any associated virtual columns, implicit or explicit, are automatically redacted as well.
Create a function-based index on the phone number column of the EMPLOYEES table:
CREATE INDEX hr.phone_number_idx
ON hr.employees(
REPLACE(REPLACE(phone_number, '.', ''), '-', '')
);
Add a virtual column for salary:
ALTER TABLE hr.employees ADD (
rounded_salary AS (ROUND(salary, -3)));
Apply redaction policy to the phone number column:
BEGIN
DBMS_REDACT.ALTER_POLICY(
object_schema => 'HR',
object_name => 'EMPLOYEES',
column_name => 'phone_number',
policy_name => 'REDACT_DATA',
action => DBMS_REDACT.ADD_COLUMN,
function_type => DBMS_REDACT.FULL);
END;
/
As shown earlier, a redaction policy has already been added for the SALARY column using DBMS_REDACT.ALTER_POLICY.
Also, if you use DBMS_REDACT.UPDATE_FULL_REDACTION_VALUES to set the default redacted value to ‘X’, and the redaction function is set to ‘FULL’, the redacted value displayed will be ‘X’ instead of a blank space.
BEGIN
DBMS_REDACT.UPDATE_FULL_REDACTION_VALUES(
number_value => 0,
char_value => 'X');
END;
/
Query the Table
SELECT employee_id, phone_number, rounded_salary
FROM hr.employees
WHERE employee_id IN (101, 103, 176, 201);
Result
EMPLOYEE_ID PHONE_NUMBER ROUNDED_SALARY
———– ———- ————
176 X 0
103 X 0
101 X 0
201 X 0
5 rows selected.
Prior to these enhancements, these queries would have failed if they involved virtual columns that referenced redacted base columns. With Oracle Database 23ai, redaction is now automatically applied to such virtual columns without errors.
Note: Redaction policies cannot be applied to a virtual column directly. The virtual column will be redacted from the policy applied to the base column. Also, if a virtual column references one or more redacted base columns, the entire virtual column is redacted automatically to prevent data leakage.
Views with expressions
Views abstract data for reporting and access control. In Oracle Database 23ai, expressions over redacted columns remain redacted. You can also define independent redaction policies directly on views for more granular control.
Note: Materialized views do not inherit base table redaction; they must have policies explicitly defined on them.
Let’s say an organization needs to restrict access to employees’ names and email addresses for privacy compliance. A redaction policy is applied on the FIRST_NAME, LAST_NAME, and EMAIL columns in the HR.EMPLOYEES table. These columns are also exposed through a reporting view (EMPLOYEE_VIEW) used by analysts. With Oracle Database 23ai, the redaction automatically applies within the view as well.
Define the view
CREATE OR REPLACE VIEW hr.employee_view AS
SELECT
employee_id AS EMP_ID,
ROUND(MONTHS_BETWEEN(SYSDATE, hire_date)/12) AS YEARS_OF_SERVICE,
CONCAT(UPPER(first_name), ' ', UPPER(last_name)) AS FULL_NAME,
EMAIL
FROM hr.employees;
This view displays EMP_ID and YEARS_OF_SERVICE, combines an employee’s first and last names into a single FULL_NAME column using the CONCAT function, and includes the EMAIL column.
Apply redaction policy to the FIRST_NAME column
BEGIN
DBMS_REDACT.ALTER_POLICY (
object_schema => 'HR',
object_name => 'EMPLOYEES',
policy_name => 'REDACT_DATA',
column_name => 'FIRST_NAME',
action => DBMS_REDACT.ADD_COLUMN,
function_type => DBMS_REDACT.FULL);
END;
/
Similarly, apply a redaction policy to the LAST_NAME column. This following command updates the REDACT_DATA policy on the EMPLOYEES table to add redaction for the EMAIL column, using a regular expression to replace the entire email value with xxxx@company.com.
Apply redaction policy to the EMAIL column
BEGIN
DBMS_REDACT.ALTER_POLICY (
object_schema => 'HR',
object_name => 'EMPLOYEES',
policy_name => 'REDACT_DATA',
column_name => 'EMAIL',
action => DBMS_REDACT.ADD_COLUMN,
function_type => DBMS_REDACT.REGEXP,
regexp_pattern => '^.*$',
regexp_replace_string => 'xxxx@company.com',
regexp_position => 1,
regexp_occurrence => 1,
regexp_match_parameter => 'i');
END;
/
Query the View
SELECT EMP_ID, YEARS_OF_SERVICE,FULL_NAME, EMAIL
FROM hr.employee_view
WHERE emp_id IN (101, 103, 176, 201);
Result
EMP_ID YEARS_OF_SERVICE FULL_NAME EMAIL
——- —————- ———— ————
101 20 X xxxx@company.com
103 20 X xxxx@company.com
176 19 X xxxx@company.com
201 22 X xxxx@company.com
4 rows selected.
In EMPLOYEE_VIEW, the EMAIL column is redacted to a fixed placeholder value, while the FULL_NAME column is fully redacted because its underlying FIRST_NAME and LAST_NAME columns are included in the redaction policy. Since the full redaction value was updated in the previous example, the output now shows ‘X’. In this scenario, the analyst can see the EMP_ID but not the personal details for FULL_NAME or EMAIL. This confirms that redaction policies on base table columns are still enforced in views.
Try Oracle Data Redaction Today
To learn more about Data Redaction, visit the Advanced Security product page on the Oracle website. For hands-on experience, try our free, interactive Oracle Data Redaction LiveLab.
To learn more, refer to the following resources:
- GitHub Data Redaction sample demo script
- Oracle Database Security Primer— Chapter 10: Data Redaction