Oracle Data Redaction: Mask at Read Time, Prove It Isn't Access Control

The support rep sees ****-****-****-0001 on their screen and everyone relaxes. The card number is “masked,” the demo looks compliant, the box gets ticked. Then someone runs an export, or a report built on the same table, or reads the datafile off disk — and the full number is right there, exactly where it always was, unchanged.

That gap between what redaction looks like and what it does is where teams get burned. Data Redaction (the DBMS_REDACT package, part of Advanced Security) is a genuinely useful, near-zero-friction control: it masks sensitive columns as a query returns them, with no application change. But it is constantly mistaken for two things it is emphatically not — encryption at rest, and access control. Get that wrong and you’ve put a sticker over the problem. This post is what redaction actually is, the four ways to mask, who gets masked and who doesn’t, and — the part that ends the argument — a lab that proves the boundary by reading the real data straight off the disk while the screen still says ****.

What redaction is — and the two things it isn’t

Redaction is a read-time transform. When a query returns a protected column, Oracle rewrites the value on the way out — to a mask, a partial value, a pattern, or a random stand-in — based on a policy you attach to the table. The application doesn’t change, the SQL doesn’t change, and, crucially, the stored data doesn’t change at all. Nothing is written. The bytes in the block are the same before and after you add the policy; only the result the client receives is different.

That single fact defines both edges of what redaction is good for:

• It is not encryption at rest. The real value is still sitting in the datafile, in every RMAN backup, in redo, and in a Data Pump export taken by a privileged user. Redaction never touches the bytes on disk. If the threat is a stolen datafile or a lost backup, redaction does nothing — that’s what Transparent Data Encryption is for, and the two are complementary, not alternatives.
• It is not access control. The WHERE clause is evaluated against the real value, not the masked one, so a user who only ever sees ****-****-****-0001 can still confirm a full card number by guessing it in a predicate. Exports, expressions, and inference all route around the mask. Redaction reduces casual exposure — the over-the-shoulder look, the screenshot, the ad-hoc report — and shrinks who sees full values by default. It does not stop a determined, authorized user.

The right mental model: redaction is the last-mile display filter. It sits on top of privileges, TDE, and auditing — not instead of them.

The four ways to mask

A redaction policy attaches to a table and names one or more columns, each with a function type that decides how the value is masked:

• FULL — replace the whole value with a fixed default: 0 for numbers, a single space for character data. The blunt instrument; use it when no part of the value should show.
• PARTIAL — keep some of the value and mask the rest. This is the one you’ll use most: show the last four digits of a card, the last four of an SSN, the area code of a phone number. Oracle ships built-in format constants for the common cases (REDACT_CCN16_F12 for a 16-digit card, REDACT_US_SSN_F5 for a US SSN) so you don’t hand-roll the position math.
• REGEXP — pattern-based masking, for values without a fixed layout. The classic is an email address: mask the name part, keep the domain, so user0001@example.com becomes xxxx@example.com.
• RANDOM — replace the value with a random one of the same type. Useful when a real-looking value keeps a screen or a downstream process happy but the actual data must not leak.

(There’s also NULLIFY, which returns NULL, and NONE, a pass-through you can use to stage a policy before turning it on.) Building the policy is two calls — ADD_POLICY creates it on the first column, ALTER_POLICY adds the rest:

BEGIN
  -- card: PARTIAL, using the built-in 16-digit credit-card format -> shows only the last 4 digits
  DBMS_REDACT.ADD_POLICY(
    object_schema       => 'REDOWN',
    object_name         => 'CUSTOMERS',
    policy_name         => 'RED_CUSTOMERS',
    column_name         => 'CARD',
    function_type       => DBMS_REDACT.PARTIAL,
    function_parameters => DBMS_REDACT.REDACT_CCN16_F12,
    expression          => '1=1');                 -- who to redact; see below

  -- ssn: PARTIAL, built-in US-SSN format -> XXX-XX-6789
  DBMS_REDACT.ALTER_POLICY(
    object_schema       => 'REDOWN', object_name => 'CUSTOMERS', policy_name => 'RED_CUSTOMERS',
    action              => DBMS_REDACT.ADD_COLUMN, column_name => 'SSN',
    function_type       => DBMS_REDACT.PARTIAL,
    function_parameters => DBMS_REDACT.REDACT_US_SSN_F5);

  -- email: REGEXP -> mask the name, keep the domain
  DBMS_REDACT.ALTER_POLICY(
    object_schema         => 'REDOWN', object_name => 'CUSTOMERS', policy_name => 'RED_CUSTOMERS',
    action                => DBMS_REDACT.ADD_COLUMN, column_name => 'EMAIL',
    function_type         => DBMS_REDACT.REGEXP,
    regexp_pattern        => DBMS_REDACT.RE_PATTERN_EMAIL_ADDRESS,
    regexp_replace_string => DBMS_REDACT.RE_REDACT_EMAIL_NAME);

  -- salary: FULL -> a NUMBER redacts to 0
  DBMS_REDACT.ALTER_POLICY(
    object_schema => 'REDOWN', object_name => 'CUSTOMERS', policy_name => 'RED_CUSTOMERS',
    action        => DBMS_REDACT.ADD_COLUMN, column_name => 'SALARY',
    function_type => DBMS_REDACT.FULL);
END;
/

One policy per table, as many columns as you like. Note there’s no GRANT, no view, no application change — the table is the same table it was a moment ago.

Who gets redacted, and who doesn’t

The expression parameter is the whole game for targeting. It’s a SQL predicate evaluated per session: when it’s true, the session is redacted; when it’s false, that session sees the real value. 1=1 above redacts everyone who is subject to the policy — fine for a demo, wrong for production. A real policy targets by identity or context:

-- redact for everyone EXCEPT sessions that hold a role trusted to see full PII
expression => 'SYS_CONTEXT(''SYS_SESSION_ROLES'', ''PII_FULL'') IS NULL'

You can key the expression off the session user, an enabled role, an application context, the client identifier, or the connection’s IP — anything you can express in SQL. The one rule: base it on something the user can’t spoof. Redacting on a CLIENT_IDENTIFIER that the application sets for its own convenience is a mask anyone can lift by setting the identifier themselves.

Above the expression sits a hard override: the EXEMPT REDACTION POLICY system privilege. A user who holds it bypasses every redaction policy in the database, regardless of any expression. SYS is always exempt. Grant the privilege deliberately, to as few accounts as possible, and audit who has it — it is the master key to every mask you’ve set.

flowchart TD
Q["Query from CLERK<br/>SELECT card ... WHERE card = '4111-2222-3333-0001'"] --> F["WHERE runs on the REAL value<br/>(masking hasn't happened yet)"]
F --> R["Matching row read from the datafile<br/>plaintext: 4111-2222-3333-0001"]
R --> P{"Redaction policy:<br/>is the session EXEMPT?"}
P -->|"no — subject (CLERK)"| M["Projected column MASKED<br/>→ ****-****-****-0001"]
P -->|"yes — exempt / SYS / Data Pump"| C["Projected column AS-IS<br/>→ 4111-2222-3333-0001"]
subgraph REST["Redaction never touches these"]
  D["Datafile on disk"]
  B["RMAN backup"]
  E["Data Pump export"]
end
R -.reads from.- REST

Now prove it

Put the same rows behind the policy above, then read them back as two different users — an ordinary CLERK, who is subject to the policy, and an AUDITOR, who holds EXEMPT REDACTION POLICY:

-- as CLERK (subject to the policy)          -- as AUDITOR (EXEMPT REDACTION POLICY)
SELECT card, ssn, email, salary              SELECT card, ssn, email, salary
FROM   redown.customers WHERE id = 1;        FROM   redown.customers WHERE id = 1;

Same row, same instant, one policy. The clerk sees masks; the auditor sees plaintext. That’s the feature working exactly as intended — masking decided per session, at read time, with the stored data untouched underneath. The proof that it’s untouched is right there in the auditor’s column: nothing was destroyed to produce the mask.

What it doesn’t do — the same lab, two more reads

Here’s where the myths die. First, redaction is not encryption at rest. Read the datafile off disk the way a thief with a copy of your storage would — not through the database, which redacts, but straight off the filesystem:

# the real card prefix, written to the ordinary datafile in the clear:
grep -a -c '4111-2222-3333' /opt/oracle/oradata/FREE/FREEPDB1/red_data.dbf   # -> 162   (still there)

162 hits. The real card numbers are sitting in the datafile in plaintext, exactly where redaction left them — because redaction never touched the disk. Contrast that with the TDE lab, where the same kind of grep finds zero hits in the encrypted datafile. That’s the whole distinction in one command: encryption removes the data from the file; redaction leaves it and masks the query.

Second, redaction is not access control. Two ways to see it. Try to peel the mask off with an expression and Oracle stops you — a redacted column used inside most functions returns NULL, a deliberate anti-tamper:

-- as CLERK:
SELECT 'the card is ' || card FROM redown.customers WHERE id = 1;   -- returns NULL, not the real value

But that same protection doesn’t extend to the predicate, and that’s the hole. The WHERE clause runs against the real stored value, so a user who has only ever seen **** can still confirm — or enumerate — the true value by guessing it in a filter:

-- as CLERK, who "can't see" full card numbers:
SELECT COUNT(*) FROM redown.customers WHERE card = '4111-2222-3333-0001';   -- 1  -> confirmed

The count comes back 1. The mask on the screen never mattered; the filter saw the real data. Redaction masks the output, not the query. A user you don’t trust with the data shouldn’t be able to query the table at all — that’s a GRANT, not a mask.

Don’t take my word for it — run it. The Data Redaction lab stands up an Oracle Database Free container, builds one table behind a four-column DBMS_REDACT policy, and reads it back as a subject CLERK and an EXEMPT AUDITOR. It asserts the clerk sees masks and the auditor sees plaintext, then greps the datafile and asserts the real card numbers are still on disk (redaction ≠ encryption), and asserts a WHERE clause on the true value still matches (redaction ≠ access control). If the clerk ever sees a real value, or the data isn’t on disk, the run fails. It’s proven on every CI push.

What teams get wrong

• Treating redaction as encryption. “The PII is redacted, so we don’t need TDE.” The datafile, the backup, and the export all still hold the real values. Redaction is a screen filter; the disk is plaintext. Pair it with TDE — one protects the file, the other narrows the view.
• Treating redaction as access control. Letting users who shouldn’t see PII query the table because “it’s redacted anyway.” Inference through predicates, full Data Pump exports, and any exempt account route straight around the mask. If someone shouldn’t have the data, revoke the access — don’t mask it.
• Redacting on a spoofable expression. Basing the policy on a CLIENT_IDENTIFIER or context value the application sets for convenience. Anyone who can set the same value lifts the mask. Key the expression off roles or session facts the user can’t forge.
• Forgetting the bypass paths. EXEMPT REDACTION POLICY holders and SYS see everything; a full Data Pump export carries the real data; a CREATE TABLE AS SELECT run by an exempt user materializes plaintext into a new, unprotected table. Audit who is exempt, and treat exports as sensitive.
• Masking a column the application computes on. Because a redacted column returns NULL inside expressions, redacting a value the app concatenates, hashes, or does arithmetic on can silently turn results into NULL. Redact what’s displayed, not what’s processed — and test the app against the policy.
• Confusing it with the Data Masking pack. Redaction is dynamic — it happens live, on production, at read time, and the data stays real. Static data masking (subsetting) permanently rewrites values, and is for building non-production copies. Different tools for different jobs; don’t reach for redaction to sanitize a test clone.

Redaction is the third leg of the same data-security discipline as the rest: TDE makes the files unreadable when they’re stolen, unified auditing records who looked, and the hardening checklist and least-privilege grants decide who can query at all. Redaction sits on top of those, narrowing what full values reach a screen or a report by default. Use it for exactly that — and never as a stand-in for the layer beneath it. Then prove the boundary the way that ends the argument: with the Data Redaction lab, where the screen says **** and the datafile, read off disk, says otherwise.