7.1 Overview Frequently Asked Questions

Find the answers to common questions regarding Oracle Data Redaction

What is Oracle Data Redaction?

Redaction is the process of selectively obscuring sensitive or confidential information that would be returned from a column in a table or view. Data Redaction does not change the contents of the table in the database, it operates on the data displayed when a user performs a query, redacting it based on the policy. Data Redaction can also be referred to as dynamic data masking.

Which use cases is Data Redaction best suited for?

Data Redaction is ideal for protecting sensitive information shown in dashboards, reports, or applications, restricting data exposure in management tools, and preventing sensitive data disclosure during analytics or read-only queries.

Does Data Redaction modify stored data, or only redact at query runtime?

Oracle Data Redaction only redacts data when queries are executed; the underlying stored data remains unchanged.

What’s the high-level workflow to define, test, and execute a redaction policy?

  1. Identify sensitive columns/tables.
  2. Grant necessary privileges to trusted users.
  3. Create redaction policy using the DBMS_REDACT.ADD_POLICY procedure.
  4. Test the policy by executing queries and verifying redacted output.
  5. Enable the policy for deployment; modify or drop as required.

Which data types are supported (character, number, date/time, JSON, LOB, etc.)?

Oracle Data Redaction supports
  • Character:
    • CHAR
    • VARCHAR2
    • NCHAR
    • NVARCHAR2
  • Number:
    • NUMBER
    • FLOAT
    • BINARY_FLOAT
    • BINARY_DOUBLE
  • Date and Time types:
    • DATE
    • TIMESTAMP
  • LOB and Boolean:
    • CLOB - only support full redaction
    • NCLOB - only support full redaction
    • BLOB - only support full redaction
    • BOOLEAN - only support full redaction

    Partial, random, or regular expression redaction on LOBs and BOOLEAN is not supported due to the complex and variable nature of these data types.

User-defined types, JSON, XML, RAW, and most spatial types are not supported.

What redaction methods are available, and when should each be used?

Redaction methods and when to use them:

  • Full redaction: Replaces the entire value with a fixed substitute (for example, '', 0, or a fixed date). Use when nothing should be revealed (for example, full SSN, credit card for non-privileged users). Simple, consistent, safest for highly sensitive fields.
  • Partial redaction: Masks only part of the value; shows allowed portions (for example, XXXX-XXXX-XXXX-1234). Use when users need limited visibility for workflows like lookups, matching, or customer support.

    Typical fields: SSN last 4, phone last 2–4 digits. Balance between usability and secrecy.

  • Random redaction: Substitutes values with random characters/numbers of the same type/length. Use when you need realistic-looking but non-derivable values for analysis where format matters but truth does not.

    Typical fields: Numeric IDs, amounts in demo views, free-text that shouldn’t be linked back.

  • Regular expression redaction: Uses a regex to find and mask patterns inside strings. Use when sensitive data is embedded in free text or mixed content (for example, notes containing emails, SSNs, ticket references).

    Typical patterns: \d{3}-\d{2}-\d{4} for SSN, A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,} for email patterns. Powerful and precise; test patterns carefully to avoid misses or over-masking.

  • None (no redaction): Leaves the value unchanged. Use when a policy intentionally allows visibility for testing purposes (for example, non-sensitive columns, privileged roles). Make this an explicit choice in policies to avoid surprises.
  • Nullify: Returns NULL instead of the actual value. Use when downstream apps treat “not available” differently from “masked” (for example, triggers conditional logic on nulls, or analytics should exclude the record). Can affect aggregates/joins, confirm application behavior with nulls.

How do I partially redact common PII (keep last-4 digits of SSN or redact email username/domain, etc)?

  • Use partial redaction for fixed patterns (for example, last-4 digits of SSN).

    For example: Use regex \d{3}-\d{2}-(\d{4}) to turn 123-45-6789 into XXX-XX-6789.

  • Use regular expression redaction to mask email usernames or domains or to redact phone number segments with defined patterns.

    For example: Use regex [^@]+(?=@example\.com) to turn joesmith@example.com into ****@example.com.

Can I stack multiple policies on the same table/column? How does precedence work?

No, you can't apply multiple policies on the same table or the same column. Only a single policy can be defined per table/column, so precedence between multiple policies does not apply.

What is the behavior with SELECT … FOR UPDATE, RETURNING INTO, and DML triggers?

  • SELECT … FOR UPDATE: Redaction applies normally — users see masked values even when rows are locked. The underlying data is not changed.
  • RETURNING INTO: Redaction applies before the value is returned to the client; users never see unredacted data through this clause.
  • DML triggers: Triggers that execute within the database have access to real (unredacted) data, since redaction occurs only at query result delivery time.

Summary: Redaction protects query outputs, not internal PL/SQL or database-side logic.

Do redacted values affect ORDER BY, GROUP BY, DISTINCT, or function-based indexes?

No. Redaction occurs after SQL processing and before data is returned to the client. Sorting, grouping, and distinct operations all use actual (unredacted) values. Function-based indexes and constraints continue to operate on real data. This behavior is consistent and fully supported in Oracle AI Database 26ai.

Note:

Users may infer ordering patterns, so use redaction alongside other controls (like VPD) if inference is a concern as inference attacks are not in the scope of Data Redaction.

How does redaction interact with materialized views, views on top of redacted tables, and result cache?

  • Views: Redaction policies apply transparently to views that reference redacted columns on base tables in Oracle AI Database 26ai.
  • Materialized views: Redaction does not change the stored data- it applies when users query the materialized view.
  • Result cache: Cached results are stored in unredacted form (as produced by the SQL engine). Redaction is applied again when the results are delivered to the end user.

Does redaction apply to Data Pump, external tables, database links, or replication/CDC streams?

  • Data Pump: Redaction does not apply- export/import utilities read physical data directly.
  • External tables: Redaction applies only if queried through SQL within the database, not on the external source file itself.
  • Database links: Redaction applies on the source database before the data is sent to the remote client.
  • Replication or Change Data Capture (CDC): Redaction does not modify captured or replicated data- it affects only query output.

In short, redaction protects runtime query results, not data movement or extract utilities.

How do I audit when redaction was applied (who saw redacted vs. original)?

  • Unified Auditing: Enable auditing on the objects protected by Data Redaction. Review entries in UNIFIED_AUDIT_TRAIL to identify which users queried redacted tables or views, along with timestamps and connection details.
  • Privilege auditing: Audit the use or grant of EXEMPT REDACTION POLICY. Anyone with this privilege can see unredacted data; tracking its usage ensures visibility into when redaction was bypassed.
  • Fine-Grained Auditing (FGA): Add an FGA policy to the same objects to log query activity whenever your redaction condition would be true (for example, based on user, role, or application context via SYS_CONTEXT). This provides an explicit record of redaction-eligible access.

Tip:

Include key session attributes such as CLIENT_IDENTIFIER, application module, or IP address in audit records. These help differentiate redacted sessions (no EXEMPT REDACTION POLICY, predicate = true) from unredacted sessions (exempt privilege or predicate = false).

How do I prove least-privilege and separation of duties for policy creators vs. consumers?

Role separation:

  • Policy Admins: minimal rights to create/alter policies (grant EXECUTE on DBMS_REDACT and required object privileges), no EXEMPT REDACTION POLICY.
  • DBAs/Operators: operational privileges, no ability to change policies.
  • Auditors read-only access to audit views, no policy/EXEMPT privileges.
  • Database Vault (recommended): Use Oracle Database Vault realms to prevent even powerful users from altering redaction policies or protected objects.

How do policies align with privacy frameworks (PII/PHI, GDPR, PCI, etc.)?

Data-minimization (GDPR/CCPA/HIPAA): Redaction enforces need-to-know by returning only masked values to non-privileged users.

  • PCI DSS: Use full/partial redaction to render credit card numbers unreadable for most roles (e.g., show last 4 only), reserving full visibility to explicitly authorized users.
  • HIPAA (minimum necessary): Apply contextual policies (role/app/time/IP) so workforce members see only what’s necessary.

Note:

Redaction is data-in-use control. Pair it with data-at-rest controls (TDE), row-level controls (VPD/OLS), and non-production masking (Oracle Data Safe) for full coverage. Always validate controls with your compliance team.

What are the common misconfigurations that lead to data exposure despite redaction?

  • Over-privileged users: Granting EXEMPT REDACTION POLICY broadly (or via a role that many are granted either directly or indirectly).
  • Narrow/incorrect predicates: Policy conditions that miss certain paths (for example, missed MODULE/CLIENT_IDENTIFIER, IP ranges, or connection pool patterns).
  • Partial reveals more than necessary: Showing more digits/characters than necessary (enables inference).
  • Uncovered data paths: Forgetting materialized views, ad-hoc reporting schemas, function columns, or newly added columns.
  • Data movement gaps: Assuming redaction applies to Data Pump, GoldenGate/CDC, or DB links data at rest, even though it doesn't.
  • Regex gaps: Incomplete patterns in regex redaction that miss alternative formats.
  • Caching/Extract, Transform, Load (ETL) leaks: App/result caches or ETL jobs storing unredacted results.
  • Testing only in app UI: Not testing direct SQL tools (SQL*Plus/SQLcl/SQL Developer) or read-only reporting accounts.

What’s new or changed for Data Redaction in Oracle AI Database 26ai (APIs, defaults, supported types, behavior)?

  • Broader SQL coverage: Oracle AI Database 26ai removes earlier restrictions, so redacted columns can now safely participate in SQL expressions, set operations, and view queries (e.g., GROUP BY, DISTINCT, UNION).
  • Enhanced regex options: You can use DBMS_REDACT.REGEXP or DBMS_REDACT.REGEXP_WIDTH for regular-expression redaction. Choose REGEXP_WIDTH if client drivers depend on character-width semantics.
  • Improved performance: Internal optimizations reduce runtime overhead for redaction in large parallel queries and mixed workloads.

Does Data Redaction work seamlessly with JSON Relational Duality views and do I define policies on the relational table, the duality view, or both?

Yes. Redaction applies at query return time, so duality views behave the same as regular views.

  1. Define redaction policies on the underlying relational columns; those rules automatically propagate through the duality view.
  2. Add view-level policies only if the view introduces derived or computed columns that also require masking.

Any considerations for JSON columns (LONGBLOB/CLOB JSON, search indexes) or JSON-path–targeted redaction in Oracle AI Database 26ai?

  • Targeting JSON data: Use regex-based redaction to mask patterns within JSON text, such as email addresses or ID numbers.
  • Search and indexing: JSON search indexes operate on real data; redaction occurs only when the query results are returned.

Oracle Data Redaction and JSON

Tip:

If JSON is stored as a LOB or character column, regex redaction works well. For projected JSON attributes, apply redaction on the underlying column or create a view-level rule.

Are there guidelines for redaction with new/augmented data types introduced in Oracle AI Database 26ai?

  • Redaction applies at the column level and only at query return time.
  • For supported non-character data types (e.g., spatial), use FULL or NULLIFY redaction.
  • Regex and partial redaction apply only to character or textual data.
  • Vector data types are not currently supported for redaction.

In a Oracle AI Database 26ai multitenant-only world, how should I manage policies across CDB/PDBs (common vs. local users, deployment pipelines)?

  • Define Data Redaction policies within each PDB, since policies are scoped to the schema and objects in that container.
  • Use common users and roles only for centralized administration.
  • Store DBMS_REDACT scripts in your CI/CD pipeline and apply them per PDB or application container.
  • Seed PDBs can include baseline redaction policies for consistent deployment.

Do Oracle AI Database 26ai changes in optimizer, result cache, or query rewrite have any implications for redacted columns (plans, grouping, sorting)?

No. The optimizer, grouping, and sorting logic still operate on real (unredacted) data. Redaction is applied after SQL processing and before results are returned to the client. Oracle AI Database 26ai ensures that query rewrite, set operations, and expression evaluations behave consistently with redacted columns.

What’s the recommended migration checklist when upgrading existing redaction policies from earlier versions to Oracle AI Database 26ai?

  • Inventory existing policies using DBA_REDACT_POLICIES and DBA_REDACT_COLUMNS.
  • Revalidate regex policies: switch to REGEXP_WIDTH if needed for client compatibility.
  • Check views and duality views to confirm base-table coverage; add view-level rules if required.
  • Test performance of queries using grouping, distinct, or unions involving redacted columns.
  • Audit privileges: review EXEMPT REDACTION POLICY grants and confirm audit settings.
  • Deploy per PDB through scripted rollout pipelines aligned with your multitenant model.

How does Oracle Data Redaction improve security and improve compliance?

Oracle Data Redaction improves security by dynamically masking sensitive data in real-time, ensuring that unauthorized users cannot access critical information even if they gain access to the database. It allows for granular access control based on user roles and conditions, reducing data exposure and insider threats without altering the stored data. For compliance, it helps organizations adhere to regulations like GDPR, HIPAA, and PCI DSS by auditing the EXEMPT REDACTION POLICY privilege and providing you the flexibility to define redaction policies that meet your specific data protection requirements. This reduces the risk of non-compliance, aiding organizations in avoiding fines and maintaining legal standards.