Oracle Dynamic Data Masking

Sensitive columns — SSNs, credit cards, emails, addresses — must stay queryable for support, analytics, and development. Broad cleartext access is not the answer. Data masking is. For some workloads (GDPR, HIPAA, PCI), masking is also a legal requirement.

Oracle Database ships Data Redaction as part of the Advanced Security option. (Oracle also sells a separate Data Masking and Subsetting Pack — static masking for non-prod clones, out of scope here.) Bytebase Dynamic Data Masking sits in front. One policy model across every Oracle deployment and every other engine in the fleet. Request. Review. Approve. This post compares them.

Oracle Data Redaction#

Data Redaction is GA on Oracle Database Enterprise Edition with the Advanced Security option (ASO), an extra-cost license. Shipped in 11.2.0.4 and supported through 12c, 18c, 19c, 21c, and 23ai. Standard Edition and Express Edition do not include it. Oracle Autonomous Database includes ASO; Database@AWS, @Azure, and @Google Cloud follow the same on-prem licensing.

Policies are defined per column with DBMS_REDACT.ADD_POLICY. The server rewrites query results for sessions whose policy expression evaluates to TRUE. The data on disk is unchanged.

Five function types#

-- Full redaction on a salary column for everyone except the payroll role.
BEGIN
  DBMS_REDACT.ADD_POLICY(
    object_schema => 'HR',
    object_name   => 'EMPLOYEES',
    column_name   => 'SALARY',
    policy_name   => 'salary_redact',
    function_type => DBMS_REDACT.FULL,
    expression    => 'SYS_CONTEXT(''USERENV'',''SESSION_USER'') != ''PAYROLL_USER''');
END;
/

-- Partial redaction on SSN — show last 4 digits, mask first 5.
BEGIN
  DBMS_REDACT.ADD_POLICY(
    object_schema       => 'HR',
    object_name         => 'EMPLOYEES',
    column_name         => 'SSN',
    policy_name         => 'ssn_redact',
    function_type       => DBMS_REDACT.PARTIAL,
    function_parameters => DBMS_REDACT.REDACT_US_SSN_F5,
    expression          => '1=1');
END;
/

-- Add a column to an existing policy (12.2+).
BEGIN
  DBMS_REDACT.ALTER_POLICY(
    object_schema => 'HR',
    object_name   => 'EMPLOYEES',
    policy_name   => 'salary_redact',
    action        => DBMS_REDACT.ADD_COLUMN,
    column_name   => 'COMMISSION_PCT',
    function_type => DBMS_REDACT.FULL);
END;
/

Pre-12.2, each table was limited to one redaction policy. From 12.2 onward, a single policy covers multiple columns via ALTER_POLICY ... ADD_COLUMN. The per-table policy object stays singular, but it can carry many columns, each with its own function type.

Permissions and the policy expression#

Two privileges govern the model:

• EXECUTE ON DBMS_REDACT — required to create or modify policies. Grant to the security officer.
• EXEMPT REDACTION POLICY — bypasses every redaction policy in the database. The session sees real values. Grant sparingly and audit changes.

The expression parameter controls whether redaction applies for a session. It evaluates against runtime context, typically SYS_CONTEXT:

-- Mask everyone except the application schema.
expression => 'SYS_CONTEXT(''USERENV'',''SESSION_USER'') != ''APP_USER'''

-- Mask everyone outside an application context set by the app on connect.
expression => 'SYS_CONTEXT(''APP_CTX'',''role'') != ''ANALYST'''

SYS and users with SYSDBA see real data — redaction does not apply to them. Same for any user granted EXEMPT REDACTION POLICY. Limit who holds these and record any grant change to Unified Audit.

What Data Redaction does not do#

• Stop SYS, SYSDBA, or EXEMPT REDACTION POLICY holders. They see real values. Limit who holds these.
• Defend against ad-hoc inference. A user with SELECT but no exemption can still probe with predicates — WHERE salary BETWEEN 99999 AND 100001 returns the redacted 0 but reveals which rows match. Pair with Database Vault and audit.
• Apply to DML. INSERT, UPDATE, and MERGE operate on the underlying values. A session with redacted SELECT and UPDATE can still overwrite the column.
• Protect Data Pump exports. expdp reads the table directly; redaction does not apply. Anyone with DATAPUMP_EXP_FULL_DATABASE exports cleartext. Use the Oracle Data Masking and Subsetting Pack for non-prod clones.
• Protect RMAN backups. Backups are physical block copies; redaction is a query-time transform.
• Apply to internal SQL. Recursive SQL issued by the Oracle internals (parsing, the optimizer) sees real values.
• Cover every column type. Virtual columns (pre-12.2), columns referenced by virtual columns, certain XMLType configurations, columns indexed by Oracle Text, and columns under Editioning Views have restrictions. Check the version-specific Restrictions on Oracle Data Redaction page.
• Compose automatically with Database Vault realms. The two overlap; sequencing matters. Database Vault gates who can access the table; redaction transforms what they see. Configure both, in that order.
• Filter rows. Data Redaction operates at the column level. For row-level control, use Virtual Private Database (VPD) or Real Application Security.

Bytebase Dynamic Data Masking#

Native Data Redaction has two documented gaps. EXEMPT REDACTION POLICY holders and SYSDBA see cleartext. SELECT-only users infer underlying values with ranged predicates. Both follow from the same design: redaction rewrites results inside the engine, but privileges and predicates run upstream of the rewrite. Closing the gaps requires governing the query itself — not just the result.

Bytebase Dynamic Data Masking governs the query. Queries route through Bytebase's SQL Editor. Bytebase masks results before they leave the editor. SYSDBA and EXEMPT REDACTION POLICY on the backing instance do not bypass the policy. Ad-hoc inference becomes an access decision: granting Query rights runs through a built-in workflow — Request. Review. Approve. — every step audited.

Policies compose from three layers, evaluated in fixed precedence: Masking Exemption > Global Masking Rule > Column Masking.

1. Global Masking Rule. Workspace-level. Rules evaluate top-down. First match wins. Match conditions span environment, project, database, and data classification. Each match applies a Semantic Type, which selects a masking algorithm — full, partial, MD5, range, or custom.

2. Column Masking. Project-level override on a specific column when the global rule does not apply.

3. Masking Exemption. Named users receive time-bound Query or Export exemptions to specific databases or tables. Service accounts are not eligible. Every grant logged. Every access logged.

Masking is infectious. When a column is masked, the policy propagates to every view and derived structure that depends on it. Expressions over masked columns stay masked.

Policies can also be codified via GitOps.

Masking decisions are recorded in the audit log. Every SQL execution entry carries per-column masking metadata — masked columns, Semantic Type, matching rule — alongside user, source IP, statement, and row count. Granted exemptions, used exemptions, and policy edits are first-class audit events. The access decision and the proof of enforcement share the same record.

Enforcement boundary: Bytebase masks queries routed through the SQL Editor. Traffic that hits Oracle directly bypasses it — covered there by native Data Redaction. The pattern is symmetric: native redaction at the database; Bytebase on the human query path, where approval and audit matter. One policy applies across Oracle Database EE, SE, Autonomous Database, Database@AWS / @Azure / @Google Cloud, and AWS RDS for Oracle.

Comparison#

Picking one#

• Single Oracle deployment with ASO already licensed. Masking must enforce regardless of client. Use Data Redaction. In-engine, every read path. The only option that masks JDBC, OCI, and SQL*Plus sessions equally for non-exempt users. Pair with Database Vault to keep DBAs out of the redacted columns and Unified Audit to record privilege grants.
• Standard Edition, or no ASO budget. Native Data Redaction is not available. Use Bytebase on the human query path, and a separate process for direct connections (limit SELECT grants on sensitive tables, audit those grants).
• Mixed fleet — Oracle alongside Postgres, MySQL, SQL Server, Snowflake. Use Bytebase. One policy model. Every engine. Audited grants for every unmask, recorded in the same place as your access logs.
• Both. Data Redaction at the database — direct connections, JDBC, exports for users without EXEMPT. Bytebase governs the human query path through the SQL Editor with approval and audit. They compose.

Try Bytebase Dynamic Data Masking with this tutorial.