Oracle Data Redaction (Dynamic Data Masking)

A step-by-step implementation of column-level, session-aware redaction across multiple tables in a Pluggable Database — without altering a single row of stored data.

1. Introduction

In modern database environments, securing sensitive data is a critical requirement for compliance, privacy, and regulatory standards. Oracle Data Redaction enables real-time masking of sensitive data at query execution time — without modifying the actual stored data.

The feature is implemented through the DBMS_REDACT package, which lets database administrators define policies that dynamically redact sensitive information such as identification numbers, customer names, and dates of birth based on session context.

Unlike traditional data masking, which permanently alters data, Oracle Data Redaction guarantees a clean separation between authorized and unauthorized visibility:

Authorized users can view actual data.
Unauthorized users see masked (redacted) data.

In this implementation, redaction policies are applied across multiple tables in a Pluggable Database (PDB) environment, ensuring secure and controlled data visibility without impacting application logic or database design.

2. Objective & Scope

The objective of this implementation is to:

Mask sensitive customer and account data dynamically.
Restrict visibility based on session user.
Apply redaction policies across multiple tables.
Ensure data is visible only to the application schema (BANK_APP).
Validate masking behavior using a test user (APP_READONLY).

Scope at a glance

Table	Sensitive columns redacted
`CUSTOMER_ID_DETAILS`	ID_NUMBER
`CUSTOMER_PROFILE`	DATE_OF_BIRTH, FIRST_NAME, MIDDLE_NAME, THIRD_NAME, LAST_NAME, FULL_NAME
`ACCOUNT_ID_DETAILS`	ID_NUMBER
`ACCOUNT_PROFILE`	DATE_OF_BIRTH, FIRST_NAME, MIDDLE_NAME, THIRD_NAME, LAST_NAME, FULL_NAME

3. Environment Details

Oracle Database Version	19c (19.20)
Architecture	Multitenant (CDB / PDB)
PDB Name	FIN_PDB
Feature	Data Redaction (DBMS_REDACT)
Application schema	BANK_APP
Test (unauthorized) user	APP_READONLY

4. How Data Redaction Works

At the heart of the feature is a runtime decision engine: every time a SELECT touches a redaction-protected column, the database evaluates the policy expression against the current session context. If the predicate matches, the column value is replaced before it leaves the database — the row on disk is never touched.

Anatomy of a policy

Parameter	Role	Value used in this implementation
`object_schema`	Schema owning the table	`BANK_APP`
`object_name`	Target table	e.g. `CUSTOMER_PROFILE`
`column_name`	Column to redact	`ID_NUMBER`, `DATE_OF_BIRTH`, name fields
`policy_name`	Logical policy identifier	e.g. `MASK_CUSTOMER_PROFILE`
`function_type`	Masking strategy	`DBMS_REDACT.FULL`
`expression`	Predicate that triggers masking	`SYS_CONTEXT('USERENV','SESSION_USER') != 'BANK_APP'`

5. Prerequisites

Before implementing data redaction, three checks must pass.

5.1 Verify Data Redaction is licensed and enabled

SQL> SELECT * FROM v$option WHERE parameter = 'Data Redaction';

PARAMETER         VALUE   CON_ID
----------------  ------  ------
Data Redaction    TRUE    0

5.2 Confirm the DBMS_REDACT package is valid

SQL> SELECT object_name, status FROM dba_objects
     WHERE object_name = 'DBMS_REDACT';

OBJECT_NAME    STATUS
-------------  ------
DBMS_REDACT    VALID
DBMS_REDACT    VALID
DBMS_REDACT    VALID

5.3 Connect to the correct PDB and confirm tables exist

SQL> ALTER SESSION SET container = FIN_PDB;
Session altered.

SQL> SHOW con_name;
CON_NAME
--------
FIN_PDB

SQL> SELECT owner, table_name FROM dba_tables
     WHERE table_name IN (
       'CUSTOMER_ID_DETAILS',
       'CUSTOMER_PROFILE',
       'ACCOUNT_ID_DETAILS',
       'ACCOUNT_PROFILE');

OWNER     TABLE_NAME
--------  --------------------
BANK_APP  CUSTOMER_PROFILE
BANK_APP  CUSTOMER_ID_DETAILS
BANK_APP  ACCOUNT_PROFILE
BANK_APP  ACCOUNT_ID_DETAILS

Tip. Always run the DBMS_REDACT calls from inside the PDB (FIN_PDB), not from the root. Policies are PDB-scoped objects.

6. Implementation Steps

Step 1 — Identification table: `CUSTOMER_ID_DETAILS`

A single column (ID_NUMBER) is masked. The policy expression is the same throughout the implementation: anyone whose session user is not BANK_APP sees redacted output.

SQL> BEGIN
  DBMS_REDACT.ADD_POLICY(
    object_schema => 'BANK_APP',
    object_name   => 'CUSTOMER_ID_DETAILS',
    column_name   => 'ID_NUMBER',
    policy_name   => 'MASK_CUSTOMER_ID',
    function_type => DBMS_REDACT.FULL,
    expression    => q'[SYS_CONTEXT('USERENV','SESSION_USER') != 'BANK_APP']');
END;
/
PL/SQL procedure successfully completed.

Step 2 — Customer profile table: `CUSTOMER_PROFILE`

Six columns are masked using a single named policy. The first column is added with ADD_POLICY; the remaining five are appended with ALTER_POLICY. Columns masked:

DATE_OF_BIRTH
FIRST_NAME
MIDDLE_NAME
THIRD_NAME
LAST_NAME
FULL_NAME

-- 2a. Create the policy on DATE_OF_BIRTH
SQL> BEGIN
  DBMS_REDACT.ADD_POLICY(
    object_schema => 'BANK_APP',
    object_name   => 'CUSTOMER_PROFILE',
    column_name   => 'DATE_OF_BIRTH',
    policy_name   => 'MASK_CUSTOMER_PROFILE',
    function_type => DBMS_REDACT.FULL,
    expression    => q'[SYS_CONTEXT('USERENV','SESSION_USER') != 'BANK_APP']');
END;
/

-- 2b. Extend the same policy to FIRST_NAME, MIDDLE_NAME,
--     THIRD_NAME, LAST_NAME, FULL_NAME (one ALTER per column)
SQL> BEGIN
  DBMS_REDACT.ALTER_POLICY(
    object_schema => 'BANK_APP',
    object_name   => 'CUSTOMER_PROFILE',
    column_name   => 'FIRST_NAME',
    policy_name   => 'MASK_CUSTOMER_PROFILE',
    function_type => DBMS_REDACT.FULL,
    expression    => q'[SYS_CONTEXT('USERENV','SESSION_USER') != 'BANK_APP']');
END;
/
-- repeat for MIDDLE_NAME, THIRD_NAME, LAST_NAME, FULL_NAME

Why ALTER_POLICY per column? A redaction policy is keyed by (object, policy_name). To extend an existing named policy to additional columns on the same table, use ALTER_POLICY. Use ADD_POLICY only for the first column — calling it twice on the same object raises an error.

Step 3 — Account identification table: `ACCOUNT_ID_DETAILS`

SQL> BEGIN
  DBMS_REDACT.ADD_POLICY(
    object_schema => 'BANK_APP',
    object_name   => 'ACCOUNT_ID_DETAILS',
    column_name   => 'ID_NUMBER',
    policy_name   => 'MASK_ACCOUNT_ID',
    function_type => DBMS_REDACT.FULL,
    expression    => q'[SYS_CONTEXT('USERENV','SESSION_USER') != 'BANK_APP']');
END;
/

Step 4 — Account profile table: `ACCOUNT_PROFILE`

Mirrors Step 2: a single ADD_POLICY on DATE_OF_BIRTH, then five ALTER_POLICY calls to cover the name columns under one logical policy MASK_ACCOUNT_PROFILE.

SQL> BEGIN
  DBMS_REDACT.ADD_POLICY(
    object_schema => 'BANK_APP',
    object_name   => 'ACCOUNT_PROFILE',
    column_name   => 'DATE_OF_BIRTH',
    policy_name   => 'MASK_ACCOUNT_PROFILE',
    function_type => DBMS_REDACT.FULL,
    expression    => q'[SYS_CONTEXT('USERENV','SESSION_USER') != 'BANK_APP']');
END;
/

-- Then ALTER_POLICY for FIRST_NAME, MIDDLE_NAME, THIRD_NAME,
-- LAST_NAME, FULL_NAME (same predicate, same policy name)

Policy summary

Policy	Table	Columns covered	Created via
`MASK_CUSTOMER_ID`	CUSTOMER_ID_DETAILS	ID_NUMBER	ADD_POLICY
`MASK_CUSTOMER_PROFILE`	CUSTOMER_PROFILE	DATE_OF_BIRTH, FIRST_NAME, MIDDLE_NAME, THIRD_NAME, LAST_NAME, FULL_NAME	ADD + 5× ALTER
`MASK_ACCOUNT_ID`	ACCOUNT_ID_DETAILS	ID_NUMBER	ADD_POLICY
`MASK_ACCOUNT_PROFILE`	ACCOUNT_PROFILE	DATE_OF_BIRTH, FIRST_NAME, MIDDLE_NAME, THIRD_NAME, LAST_NAME, FULL_NAME	ADD + 5× ALTER

What the script does: DBMS_REDACT is used to mask sensitive customer and account data at query time inside FIN_PDB. DBMS_REDACT.FULL means the actual data is replaced (e.g. with blank values or a fixed redacted output) when accessed by unauthorized users. Policies are created with DBMS_REDACT.ADD_POLICY and extended with DBMS_REDACT.ALTER_POLICY.

7. Validation & Testing

The same SELECT is run from two different sessions to prove that the policy fires only when the session user is not BANK_APP.

Session A — Authorized

Connected as BANK_APP

The policy expression evaluates to FALSE. Redaction is skipped. The query returns the actual stored values.

Session B — Unauthorized

Connected as APP_READONLY

The policy expression evaluates to TRUE. DBMS_REDACT.FULL is applied. The user sees NULL / redacted values for every protected column.

Authorized session — actual data visible

-- Verify current session and container
SQL> SHOW user;
USER is "BANK_APP"

SQL> SHOW con_name;
CON_NAME
--------
FIN_PDB

-- Query 1: ID table — redaction policy expression evaluates to FALSE
SQL> SELECT id_number
  2  FROM   bank_app.customer_id_details;

ID_NUMBER
--------------------
1234-5678-9012-3456
9876-5432-1098-7654
4561-2300-0987-6543

3 rows selected.

-- Query 2: Profile table — all name and DOB columns unmasked
SQL> SELECT first_name, last_name, full_name, date_of_birth
  2  FROM   bank_app.customer_profile;

FIRST_NAME    LAST_NAME      FULL_NAME                   DATE_OF_BIRTH
----------    -----------    --------------------------  -------------
Ahmad         Al-Rashid      Ahmad Al-Rashid             15-MAR-1985
Sara          Mohammed       Sara Mohammed               22-JUL-1990
Khalid        Al-Otaibi      Khalid Al-Otaibi            08-NOV-1978

3 rows selected.

Figure 4 — As BANK_APP: policy expression evaluates to FALSE; all sensitive columns return actual stored values.

Unauthorized session — output masked

-- Verify current session and container
SQL> SHOW user;
USER is "APP_READONLY"

SQL> SHOW con_name;
CON_NAME
--------
FIN_PDB

-- Query 1: ID table — redaction policy expression evaluates to TRUE
SQL> SELECT id_number
  2  FROM   bank_app.customer_id_details;

ID_NUMBER
--------------------
(null)
(null)
(null)

3 rows selected.

-- Query 2: Profile table — FULL redaction applied to all protected columns
SQL> SELECT first_name, last_name, full_name, date_of_birth
  2  FROM   bank_app.customer_profile;

FIRST_NAME    LAST_NAME      FULL_NAME                   DATE_OF_BIRTH
----------    -----------    --------------------------  -------------
(null)        (null)         (null)                      (null)
(null)        (null)         (null)                      (null)
(null)        (null)         (null)                      (null)

3 rows selected.

Figure 5 — As APP_READONLY: policy expression evaluates to TRUE; DBMS_REDACT.FULL replaces every protected column with NULL before the result set is returned.

Result. The same query, the same row on disk, two different result sets — driven entirely by SYS_CONTEXT('USERENV','SESSION_USER'). No application code was changed.

8. Key Observations

Redaction works dynamically at query runtime.
There are no changes to the actual stored data.
Policies are schema-level and column-specific.
The same policy can be extended to multiple columns on the same table via ALTER_POLICY.

9. Benefits

10. Risks & Mitigation

Risk	Mitigation
Incorrect policy expression may expose data.	Use precise conditions in the policy expression.
Performance overhead in high-volume queries.	Monitor performance after implementation.
Misconfiguration may impact application behavior.	Always test in UAT before production; maintain proper documentation.

Operational rule of thumb. A redaction policy is a security boundary — review every change as if it were a firewall rule. Test in UAT, validate from both an authorized and an unauthorized session, then promote.

11. Conclusion

Oracle Data Redaction provides a robust and efficient mechanism to protect sensitive data without altering the underlying data or application logic.

In this implementation, multiple sensitive columns across customer and account-related tables were successfully masked using conditional policies, ensuring that only authorized users (BANK_APP) can view actual data while others see redacted values.

This approach is highly effective for securing sensitive financial and customer data in enterprise environments, especially in shared database systems like UAT or reporting environments.

12. Future Enhancements

While the current implementation effectively secures sensitive data, several areas can be further enhanced to improve scalability, security, and automation.

1. Advanced redaction techniques. Move beyond full redaction to implement partial masking, regular-expression-based masking, and random redaction. This provides more meaningful data visibility (e.g. showing only the last 4 digits of an ID) while still protecting sensitive information.
2. Role-based and context-aware policies. Enhance policies using additional conditions such as user roles and privileges, IP address or client machine, and application module (via MODULE / ACTION). This allows more granular control instead of relying only on SESSION_USER.
3. Integration with Oracle security features. Combine data redaction with Oracle Database Vault for access control, Oracle Advanced Security for encryption, and Oracle Unified Auditing to track who accessed redacted data — creating a more comprehensive data security framework.
4. Automation and policy management. Develop scripts or tools to automatically identify sensitive columns, generate and deploy redaction policies, and maintain consistency across DEV, UAT and PROD.
5. Monitoring and performance optimization. Track redaction policy usage, measure performance impact, and tune policies to avoid unnecessary overhead on high-volume queries.
6. Support for more complex environments. Extend implementation to multi-PDB environments with centralized policy control, Oracle RAC setups, and Oracle Data Guard for consistency across primary and standby databases.
7. Dynamic masking based on data sensitivity classification. Introduce a classification mechanism where data is tagged (Confidential, Restricted, …) and redaction policies are applied dynamically based on classification level.

Thanks for reading :)

BR,

ZAHEER

Search This Blog

Cloud, Database, Apps and AI Workshop