Pranav Bakare

Posted on Jul 19

DBMS_REDACT in Oracle Database

#database #data #datascience #oracle

🔐 Understanding DBMS_REDACT in Oracle: A Complete Guide to Dynamic Data Redaction

This includes what it is, why it's used, when to use it, and a step-by-step implementation example with context management using SYS_CONTEXT.

📌 What is DBMS_REDACT?

DBMS_REDACT is an Oracle Database package that provides dynamic data redaction—which means masking sensitive data at runtime based on user roles, session context, or policies, without modifying the data stored in the database.

This ensures sensitive fields such as SSNs, passwords, card numbers, etc., are hidden or masked for unauthorized users, while visible to authorized users—all transparently, during query execution.

💡 Why Use DBMS_REDACT?

In enterprise-grade applications like Cargo Management Systems, HR systems, or Banking applications, there are sensitive fields (passwords, salary, card info, etc.).
Not all users should see this data. For example:

User Role Can View Password?

Admin ✅ Yes
Auditor ❌ No (Masked)
Guest ❌ No (Masked)

DBMS_REDACT helps avoid the need to change queries or application logic—instead, data redaction is handled at the DB level, ensuring centralized and secure access.

🧠 Types of Redaction Methods in DBMS_REDACT

Oracle provides various redaction types:

FULL: Entire value is redacted (e.g., shown as XXXX)
PARTIAL: Only part is visible (e.g., show last 4 digits of card)
REGEXP: Redact values using regex
RANDOM: Replace with random values
NONE: No redaction

📈 When to Use DBMS_REDACT?

Use DBMS_REDACT when:

You need real-time dynamic masking of data.
You want to separate data visibility from data storage.
You want to avoid maintaining separate "sanitized" tables.
You need auditable and policy-driven redaction.

🛠 Step-by-Step Implementation of DBMS_REDACT with SYS_CONTEXT

Let’s implement a sample scenario:

"Only users with the role AUTHORIZED_USER should be able to see employee salaries. Everyone else will see masked values."

1️⃣ Step 1: Create a test table

CREATE TABLE employees (
    emp_id      NUMBER,
    emp_name    VARCHAR2(50),
    salary      NUMBER
);


INSERT INTO employees VALUES (1, 'Alice', 90000);
INSERT INTO employees VALUES (2, 'Bob', 80000);
COMMIT;

2️⃣ Step 2: Define an application context

Application context will hold session info like role.

CREATE CONTEXT app_ctx USING set_context_proc;

Now create a procedure to set this context:

CREATE OR REPLACE PROCEDURE set_context_proc(role IN VARCHAR2) IS
BEGIN
    DBMS_SESSION.set_context('app_ctx', 'user_role', role);
END;

3️⃣ Step 3: Simulate role setting in a session

BEGIN
    set_context_proc('AUTHORIZED_USER');  -- or 'UNAUTHORIZED_USER'
END;

4️⃣ Step 4: Create the redaction policy

BEGIN
  DBMS_REDACT.add_policy(
    object_schema      => 'YOUR_SCHEMA',
    object_name        => 'EMPLOYEES',
    column_name        => 'SALARY',
    policy_name        => 'salary_redact_policy',
    function_type      => DBMS_REDACT.FULL,
    expression         => 'SYS_CONTEXT(''APP_CTX'', ''USER_ROLE'') != ''AUTHORIZED_USER'''
  );
END;

The expression controls when to redact the data. If the role is not AUTHORIZED_USER, the salary column will be redacted (masked as 0 or XXXX depending on datatype).

5️⃣ Step 5: Test it out

Let’s simulate as authorized:

BEGIN
    set_context_proc('AUTHORIZED_USER');
END;

SELECT emp_name, salary FROM employees;
-- ✅ Shows actual salary

Now simulate as unauthorized:

BEGIN
    set_context_proc('UNAUTHORIZED_USER');
END;

SELECT emp_name, salary FROM employees;
-- ❌ Salary is redacted

🧩 Additional Notes

DBMS_REDACT does not encrypt data; it only masks it during retrieval.
Works seamlessly with existing applications—no need to change queries.
You can use DBMS_REDACT.alter_policy and drop_policy to modify or remove policies.

✅ Summary

DBMS_REDACT is an enterprise-grade data masking solution within Oracle Database. By combining it with session-based context management, you can achieve fine-grained control over who sees what—without altering your application or data models.

💬 "Show real data only when necessary. Hide it when it’s a liability." — This is the philosophy behind DBMS_REDACT.

DEV Community