Solving a Data Redaction Mystery — When Data Was Masked Only in the Application!

Recently, I encountered an interesting challenge while working with Oracle Data Redaction.

A database user had been granted the following privilege:

GRANT EXEMPT REDACTION POLICY TO APP_SCHEMA;

Naturally, I expected that the redacted data would now appear unmasked everywhere.
But to my surprise — the data was clearly visible in SQL Developer, yet still masked inside the application. 🤔

---

🔍 Investigating the Behavior

At first glance, this seemed like a strange inconsistency.
If the user APP_SCHEMA already had the EXEMPT REDACTION POLICY privilege, why was the data still masked in the application?

After checking with the client, they mentioned an important detail:

“Our application does not connect directly as APP_SCHEMA — it connects through a connection pool using another database user.”

💡 That one line changed everything.

---

🧠 Understanding What Was Really Happening

Oracle Data Redaction works per database session, not by the logical application user.

Here’s what was actually happening behind the scenes:

• 🧑‍💻 SQL Developer connection:
  The session was created directly as APP_SCHEMA.
  → The EXEMPT REDACTION POLICY privilege applied, so the data appeared unmasked.

• 🌐 Application connection:
  Sessions were created through a connection pool user (e.g., POOL_USER, WEB_USER, or APP_CONN_USER).
  → This user didn’t have the privilege — so the redaction still applied.

Even though the application logically acted as APP_SCHEMA, the real session user in the database was different.

---

🧾 Verifying the Real Session User

To confirm my suspicion, I executed the following SQL from within the application session:

SELECT SYS_CONTEXT('USERENV','SESSION_USER') AS session_user,
       SYS_CONTEXT('USERENV','CURRENT_USER')  AS current_user
FROM dual;

The result revealed the truth — the session was connected using the connection pool user, not APP_SCHEMA.

Mystery solved! 🔍

---

🔧 The Fix

Once the actual connection user was identified, the solution was simple:

GRANT EXEMPT REDACTION POLICY TO APP_CONN_USER;

After restarting the application (to refresh the pool connections), the issue was resolved.
Now the data appeared unmasked both in SQL Developer and in the application. ✅

---

⚙️ Bonus Tip — Identify Connection Pool Users

To check which users your application sessions are really using, run:

SELECT USERNAME, STATUS, COUNT(*) AS session_count
FROM v$session
WHERE USERNAME IS NOT NULL
GROUP BY USERNAME, STATUS
ORDER BY USERNAME, STATUS;

This quick query helps identify all active sessions and their respective users — perfect for troubleshooting connection pool issues.

---

✨ Final Thoughts

This experience reminded me of an important Oracle principle:

Privileges in Oracle depend on the actual session user — not the logical application user.

So whenever you notice data redaction behaving differently between tools and applications,
always check who’s actually connecting behind the scenes.

Sometimes, the solution is just one missing grant away. 😉