Oracle Data Redaction: Step-by-Step Guide to Protect Sensitive Data

In this step-by-step guide, we’ll walk you through everything you need to know about Oracle Data Redaction. You’ll learn what data redaction is, how redaction policies work, the different types of data redaction, and how to implement it practically using SQL examples. By the end of this guide, you’ll understand how Oracle Data Redaction helps protect sensitive data in real time, ensures compliance with privacy laws, and strengthens your database security all without affecting your applications or data integrity.

What is Oracle Data Redaction?

Oracle Data Redaction is a built-in security feature in Oracle Database that helps protect sensitive or confidential information from unauthorized access. It works by masking or hiding data in real time when users query the database without changing the actual data stored in the tables.

For example, an HR manager might see a full employee salary, while a normal user only sees a masked value like “XXXXXX”. This ensures that critical data such as salary, PAN, credit card numbers, or personal details are never exposed to users who shouldn’t see them.

Oracle Data Redaction policies can be defined using the DBMS_REDACT package, allowing administrators to set rules based on conditions such as user roles, application contexts, or environments.

By using Data Redaction, organizations can strengthen database security, meet compliance standards (like GDPR or PCI DSS), and protect sensitive data all without modifying existing applications or database structures.

What is a Redaction Policy?

A Redaction Policy is the main rule or instruction that tells Oracle which data to hide, how to hide it, and when to hide it.

It acts as the control point for applying data masking to specific tables and columns.

Each policy defines:

Table and Column: The data you want to protect (e.g., EMPLOYEE.SALARY).
Redaction Type: How the data should be masked (Full, Partial, Random, etc.).
Condition (Expression): When or for whom the masking should apply.

You create and manage redaction policies using the DBMS_REDACT package.

Oracle Data Redaction Types Explained (With Simple Examples)

1. FULL Redaction

Definition: Full redaction completely hides the original data and replaces it with a default masking value based on the data type.

Sample: 123-456-789 → XXXX

Best Use Case: When no part of the sensitive data should be visible.

2. PARTIAL Redaction

Definition: Partial redaction masks only a specific portion of the data and reveals the rest. You can customize which characters to expose.

Sample: 123456789 → 1234XXXX9

Best Use Case: Commonly used for masking credit card numbers, phone numbers, or showing only last 4 digits.

3. RANDOM Redaction

Definition: Random redaction replaces actual data with a random value each time. The output looks unpredictable and does not reveal real details.

Sample: 123456789 → 594837201

Best Use Case: When you want data to appear "changed" or obfuscated without showing the real value.

4. NULLIFY Redaction

Definition: NULLIFY replaces the actual data with NULL, making it appear blank to unauthorized users.

Sample: 123-456-789 → NULL

Best Use Case: When you want to completely hide data without showing any placeholder or masked text.

5. NONE

Definition: No redaction is applied. The data appears exactly as stored in the table.

Sample: 123456789 → 123456789

Best Use Case: For privileged users or when policy conditions evaluate to "no masking required."

6. REGEXP (Regular Expression) Redaction

Definition: REGEXP redaction masks data based on a regular expression pattern. Very useful for structured fields like email, PAN, Aadhaar, phone numbers, etc.

Sample: abc@gmail.com → ***@gmail.com

Best Use Case: Masking a specific format or pattern in a field.

Now we will explore how to create a data redaction policy and how to alter an existing policy for implementation on a database table.

Test Table:

SQL> select * from HR.INFO;

     SR_NO NAME            CARD_INFO       MAIL
---------- --------------- --------------- ---------------
         1 Rahul Sharma    123-654-876     rahul@gmail.com
         2 Amit Verma      987-321-654     amit@gmail.com
         3 Sneha Patel     543-789-210     sneha@gmail.com
         4 Karan Joshi     654-123-987     karan@gmail.com
         5 Neha Kapoor     321-567-894     neha@gmail.com

Policy creation and management for each redaction type.

Policy Creation:

SQL> show user
USER is "SYS"
SQL>
SQL> BEGIN
 DBMS_REDACT.ADD_POLICY  (
    object_schema => 'HR'
   ,object_name => 'INFO'
   ,policy_name => 'PROTECT_CARD'
   ,expression => '1=1');
END;
/  2    3    4    5    6    7    8

PL/SQL procedure successfully completed.