Oracle Data Redaction (Dynamic Data Masking) Step-by-Step Implementation






1. Introduction

In modern database environments, securing sensitive data is a critical requirement for compliance, privacy, and regulatory standards. Oracle provides a powerful feature called Oracle Data Redaction (Dynamic Data Masking), which enables real-time masking of sensitive data at query execution time without modifying the actual stored data.

This feature is implemented using the DBMS_REDACT package, allowing database administrators to 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 (Dynamic Data Masking) ensures that:

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

In this implementation, data 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 includes:

  • Tables:

o CUSTOMER_ID_DETAILS
o CUSTOMER_PROFILE
o ACCOUNT_ID_DETAILS
o ACCOUNT_PROFILE

·        Columns:

           o ID_NUMBER
           o DATE_OF_BIRTH
           o Customer name fields

3. Environment Details

• Oracle Database Version: 19c (19.20) 

• Architecture: Multitenant (CDB/PDB) 

• PDB Name: FIN_PDB 

• Feature Used: Data Redaction 

• Schema: BANK_APP 

• Test User: APP_READONLY

4. Prerequisites

Before implementing data redaction, ensure the following:

  • Data Redaction feature is enabled:
  • Required package is valid:
  • Connect to correct PDB:

 

5. Implementation Steps

1. Apply Redaction Policy on Identification Tables

CUSTOMER_ID_DETAILS

2. Apply Redaction on Customer Tables

CUSTOMER_PROFILE

  • Create base policy on DATE_OF_BIRTH
  • Extend policy to other columns using ALTER_POLICY

Columns masked:

     • DATE_OF_BIRTH
     • FIRST_NAME
     • MIDDLE_NAME
     • THIRD_NAME
     • LAST_NAME
     • FULL_NAME

 

 

3. Apply Redaction on CIF Tables

ACCOUNT_ID_DETAILS

 

ACCOUNT_PROFILE

  • Similar approach using ADD + ALTER POLICY

 

The script uses DBMS_REDACT to mask sensitive customer and account data at query time in the FIN_PDB container. It applies FULL redaction, meaning the actual data is replaced (e.g., with blank values or fixed redacted output) when accessed by unauthorized users.

 Key behavior

  • Creates redaction policies using DBMS_REDACT.ADD_POLICY
  • Extends existing policies using DBMS_REDACT.ALTER_POLICY

6. Validation / Testing

As Application User (Authorized)

✔ Actual Data Visible

 

As TEST User (Unauthorized)

✔ Output Masked (NULL / Redacted values)

7. Key Observations

  • Redaction works dynamically at query runtime
  • No changes to actual stored data
  • Policies are schema-level and column-specific
  • Same policy can be extended to multiple columns

8. Benefits

·        Enhances data security

·        Supports compliance requirements (GDPR, etc.)

·        No application changes required

·        Centralized control via DB policies

·        Works seamlessly in PDB environments

9. Risks and Mitigation

⚠ Risks

  • Incorrect policy expression may expose data
  • Performance overhead in high-volume queries
  • Misconfiguration may impact application behavior

 

✅ Mitigation

  • Always test in UAT before production
  • Use precise conditions in policy expression
  • Monitor performance after implementation
  • Maintain proper documentation

10. 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.

11. Future Enhancements

While the current implementation of Oracle Data Redaction (Dynamic Data Masking) effectively secures sensitive data, there are several areas where the solution 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 will provide more meaningful data visibility (e.g., showing last 4 digits of 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
  • Application module (via MODULE / ACTION)

This allows more granular control instead of relying only on SESSION_USER.

 

3. Integration with Oracle Security FeaturesIntegrate data redaction with:

  • Oracle Database Vault for access control
  • Oracle Advanced Security for encryption
  • Oracle Unified Auditing to track who accessed redacted data

This creates 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
  • Maintain consistency across multiple environments (DEV, UAT, PROD)

 

5. Monitoring and Performance Optimization

Implement monitoring to:

  • Track redaction policy usage
  • Measure performance impact
  • Optimize 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 Real Application Clusters setups
  • Oracle Data Guard environments for consistency across primary and standby databases

 

7. Dynamic Masking Based on Data Sensitivity Classification

Introduce a classification mechanism where data is tagged (e.g., Confidential, Restricted), and redaction policies are applied dynamically based on classification level.