Use the DBMS_REDACT package to mask data - PolarDB

The DBMS_REDACT package masks sensitive data in query results to protect data security without altering the underlying stored values. Masking is applied at query time — privileged users and applications see redacted values while the original data remains unchanged.

Two Grand Unified Configuration (GUC) parameters control the feature's behavior:

Parameter	Description
`polar_enable_masking_policy`	Specifies whether to enable data masking globally.
`polar_enable_subquery_masking`	Specifies whether to apply masking inside subqueries when the feature is enabled.

Subprograms

Subprogram	Description
ADD_POLICY	Adds a data masking policy to a table.
ALTER_POLICY	Modifies an existing data masking policy.
DISABLE_POLICY	Disables a data masking policy.
ENABLE_POLICY	Enables a data masking policy.
DROP_POLICY	Removes a data masking policy from a table.
UPDATE_FULL_REDACTION_VALUES	Updates the default masked values used for each data type under full masking.

Function types

The function_type parameter appears in ADD_POLICY and ALTER_POLICY. All valid values are listed here for reference.

Constant	Description
`DBMS_REDACT.NONE`	No masking is applied.
`DBMS_REDACT.FULL`	Replaces the column value with a type-specific default. This is the default function type.
`DBMS_REDACT.NULLIFY`	Returns NULL as the masked value.
`DBMS_REDACT.PARTIAL`	Masks a portion of the column value. Requires `function_parameters`.
`DBMS_REDACT.RANDOM`	Returns a different random value on each query.
`DBMS_REDACT.REGEXP`	Masks values matching a regular expression pattern.
`DBMS_REDACT.REGEXP_WIDTH`	Provided for Oracle compatibility. Behaves the same as `REGEXP`.

Subquery masking behavior

When a masked column inside a subquery is referenced by an outer query, the outer query operates on the already-masked values by default.

For example, given a table mask_tbl(a int, b int) with a masking policy on column a, the following query evaluates the JOIN condition against masked values:

SELECT t1.a, t2.a
FROM (SELECT a FROM mask_tbl) t1
LEFT JOIN (SELECT a FROM mask_tbl) t2 ON t1.a = t2.a;

To evaluate the JOIN condition against the original pre-masking values, disable subquery masking for the session:

SET polar_enable_subquery_masking = off;

With polar_enable_subquery_masking set to off, the LEFT JOIN uses pre-masking values. The values returned by the statement are not masked.

ADD_POLICY

Adds a data masking policy to a table.

Syntax

DBMS_REDACT.ADD_POLICY(
    object_schema          VARCHAR2 DEFAULT NULL,
    object_name            VARCHAR2,
    policy_name            VARCHAR2,
    policy_description     VARCHAR2 DEFAULT NULL,
    column_name            VARCHAR2 DEFAULT NULL,
    column_description     VARCHAR2 DEFAULT NULL,
    function_type          INTEGER DEFAULT 1,
    function_parameters    VARCHAR2 DEFAULT NULL,
    expression             VARCHAR2,
    enable                 BOOLEAN DEFAULT TRUE,
    regexp_pattern         VARCHAR2 DEFAULT NULL,
    regexp_replace_string  VARCHAR2 DEFAULT NULL,
    regexp_position        INTEGER DEFAULT 1,
    regexp_occurrence      INTEGER DEFAULT 0,
    regexp_match_parameter VARCHAR2 DEFAULT NULL);

Parameters

Parameter	Required	Description
`object_schema`	No	The schema of the target table. Defaults to the current user's schema.
`object_name`	Yes	The name of the table to apply the policy to.
`policy_name`	Yes	The name of the data masking policy.
`policy_description`	No	The description of the policy.
`column_name`	No	The name of the column to mask.
`column_description`	No	The description of the masked column.
`function_type`	No	The masking function type. Default: `DBMS_REDACT.FULL`. See Function types for all valid values.
`function_parameters`	No	The masking format string. Required when `function_type` is `DBMS_REDACT.PARTIAL`. See PARTIAL masking format for syntax.
`expression`	Yes	A BOOLEAN expression that controls when masking is applied. Masking is active when this expression evaluates to TRUE.
`enable`	No	Specifies whether to enable the policy when it is created. Default: `TRUE`.
`regexp_pattern`	No	The regular expression pattern to match. Used with `REGEXP` and `REGEXP_WIDTH`.
`regexp_replace_string`	No	The replacement string for matched patterns.
`regexp_position`	No	The character position at which the regular expression search starts. Default: `1`.
`regexp_occurrence`	No	The number of pattern occurrences to replace. `0` replaces all occurrences. A positive integer `n` replaces the first `n` occurrences.
`regexp_match_parameter`	No	The match behavior modifier. Valid values: `i`, `c`, `n`, `m`, `x`.

PARTIAL masking format

The function_parameters string format depends on the column's data type.

String columns

Format: <input_format>, <output_format>, <mask_char>, <mask_from>, <mask_to>

Field	Description
`input_format`	Defines the structure of the input. `V` marks characters that can be masked; `F` marks characters that are skipped.
`output_format`	Defines the structure of the output. Must have the same number of `V` characters as the input format.
`mask_char`	The character displayed in masked positions.
`mask_from`	The start position for masking, counted by the index of `V` characters in the input format.
`mask_to`	The end position for masking, counted the same way.

Example: VVVFFVVV, VV-VV-VV, X, 1, 3 masks abcdefg as XX-Xe-fg.

Numeric columns

Format: <mask_digit>, <mask_from>, <mask_to>

Field	Description
`mask_digit`	The digit (0–9) that replaces masked positions.
`mask_from`	The start position for masking, counted from the first digit of the column value.
`mask_to`	The end position for masking.

Example: 5, 2, 5 masks 123456789 as 155556789.

Date columns

Format: <field><value>[...] — a sequence of field codes alternating between a field identifier and a replacement value.

Field code	Meaning	Example
Uppercase letter	The field is not masked.	`D` — day is not masked
Lowercase letter + digit	The field is replaced with the given value.	`d15` — day becomes 15

Field identifiers: m (month), d (day), y (year), h (hour), m (minute), s (second).

Example: m12Dy2001Hm1s1 masks 2023-01-01 12:00:00 as 2001-12-01 12:01:01.

Example

The following example creates a partial masking policy on the num column, then verifies the output.

CREATE TABLE masktbl(name varchar2(40), num integer);

INSERT INTO masktbl VALUES('Alice', 123456789);
INSERT INTO masktbl VALUES('Bob', 234567890);

-- Create a data masking policy.
BEGIN
    DBMS_REDACT.ADD_POLICY(object_name => 'masktbl',
                            policy_name => 'p1',
                            expression => '1=1',
                            column_name => 'num',
                            function_type => DBMS_REDACT.PARTIAL,
                            function_parameters => '5,1,4');
END;

-- Query the table. The num column shows masked values.
SELECT name, num FROM masktbl ORDER BY 1, 2;
    name  |    num
-------+-----------
    Alice | 555556789
    Bob   | 555567890
(2 rows)

ALTER_POLICY

Modifies an existing data masking policy.

Syntax

DBMS_REDACT.ALTER_POLICY (
    object_schema              VARCHAR2 DEFAULT NULL,
    object_name                VARCHAR2,
    policy_name                VARCHAR2,
    action                     INTEGER  DEFAULT 1,
    column_name                VARCHAR2 DEFAULT NULL,
    function_type              INTEGER  DEFAULT 1,
    function_parameters        VARCHAR2 DEFAULT NULL,
    expression                 VARCHAR2 DEFAULT NULL,
    regexp_pattern             VARCHAR2 DEFAULT NULL,
    regexp_replace_string      VARCHAR2 DEFAULT NULL,
    regexp_position            INTEGER  DEFAULT 1,
    regexp_occurrence          INTEGER  DEFAULT 0,
    regexp_match_parameter     VARCHAR2 DEFAULT NULL,
    policy_description         VARCHAR2 DEFAULT NULL,
    column_description         VARCHAR2 DEFAULT NULL);

Parameters

Parameter	Required	Description
`object_schema`	No	The schema of the target table. Defaults to the current user's schema.
`object_name`	Yes	The name of the table to which the policy applies.
`policy_name`	Yes	The name of the data masking policy to modify.
`action`	No	The operation to perform. Valid values: `DBMS_REDACT.ADD_COLUMN`, `DBMS_REDACT.DROP_COLUMN`, `DBMS_REDACT.MODIFY_EXPRESSION`, `DBMS_REDACT.MODIFY_COLUMN`, `DBMS_REDACT.SET_POLICY_DESCRIPTION`, `DBMS_REDACT.SET_COLUMN_DESCRIPTION`.
`column_name`	No	The name of the column to modify.
`function_type`	No	The masking function type. See Function types.
`function_parameters`	No	The masking format string. See PARTIAL masking format.
`expression`	No	The updated BOOLEAN expression for the policy.
`regexp_pattern`	No	The regular expression pattern to match.
`regexp_replace_string`	No	The replacement string for matched patterns.
`regexp_position`	No	The start position for the regular expression search. Default: `1`.
`regexp_occurrence`	No	The number of occurrences to replace. `0` replaces all. A positive integer `n` replaces the first `n`.
`regexp_match_parameter`	No	The match behavior modifier. Valid values: `i`, `c`, `n`, `m`, `x`.
`policy_description`	No	The updated description for the policy.
`column_description`	No	The updated description for the masked column.

Example

The following example creates a policy with no masked columns, then uses ALTER_POLICY to add a column and later modify the masking parameters.

CREATE TABLE masktbl(name varchar2(40), num integer);

INSERT INTO masktbl VALUES('Alice', 123456789);
INSERT INTO masktbl VALUES('Bob', 234567890);

-- Create a policy with no columns. Queries return unmasked data.
BEGIN
    DBMS_REDACT.ADD_POLICY(object_name => 'masktbl',
                            policy_name => 'p1',
                            expression => '1=1');
END;

SELECT name, num FROM masktbl ORDER BY 1, 2;
    name  |    num
-------+-----------
    Alice | 123456789
    Bob   | 234567890
(2 rows)

-- Add the num column with partial masking (digit 9, positions 1-4).
BEGIN
    DBMS_REDACT.ALTER_POLICY(object_name => 'masktbl',
                            policy_name => 'p1',
                            action => DBMS_REDACT.ADD_COLUMN,
                            column_name => 'num',
                            function_type => DBMS_REDACT.PARTIAL,
                            function_parameters => '9, 1, 4');
END;

SELECT name, num FROM masktbl ORDER BY 1, 2;
    name  |    num
-------+-----------
    Alice | 999956789
    Bob   | 999967890
(2 rows)

-- Modify the masking parameters (digit 5, positions 1-4).
BEGIN
    DBMS_REDACT.ALTER_POLICY(object_name => 'masktbl',
                            policy_name => 'p1',
                            action => DBMS_REDACT.MODIFY_COLUMN,
                            column_name => 'num',
                            function_type => DBMS_REDACT.PARTIAL,
                            function_parameters => '5, 1, 4');
END;

SELECT name, num FROM masktbl ORDER BY 1, 2;
    name  |    num
-------+-----------
    Alice | 555556789
    Bob   | 555567890
(2 rows)

DISABLE_POLICY

Disables a data masking policy without removing it.

Syntax

DBMS_REDACT.DISABLE_POLICY (
    object_schema           VARCHAR2 DEFAULT NULL,
    object_name             VARCHAR2,
    policy_name             VARCHAR2);

Parameters

Parameter	Required	Description
`object_schema`	No	The schema of the target table. Defaults to the current user's schema.
`object_name`	Yes	The name of the table for which to disable the policy.
`policy_name`	Yes	The name of the data masking policy to disable.

Example

CREATE TABLE masktbl(name varchar2(40), num integer);

INSERT INTO masktbl VALUES('Alice', 123456789);
INSERT INTO masktbl VALUES('Bob', 234567890);

-- Create a policy (enabled by default).
BEGIN
    DBMS_REDACT.ADD_POLICY(object_name => 'masktbl',
                            policy_name => 'p1',
                            expression => '1=1',
                            column_name => 'num',
                            function_type => DBMS_REDACT.PARTIAL,
                            function_parameters => '5,1,4');
END;

-- The num column shows masked values.
SELECT name, num FROM masktbl ORDER BY 1, 2;
    name  |    num
-------+-----------
    Alice | 555556789
    Bob   | 555567890
(2 rows)

-- Disable the policy.
CALL DBMS_REDACT.DISABLE_POLICY(object_name => 'masktbl', policy_name => 'p1');

-- The num column shows original values.
SELECT name, num FROM masktbl ORDER BY 1, 2;
    name  |    num
-------+-----------
    Alice | 123456789
    Bob   | 234567890
(2 rows)

ENABLE_POLICY

Enables a data masking policy that was created with enable => false or previously disabled.

Syntax

DBMS_REDACT.ENABLE_POLICY (
    object_schema    VARCHAR2 DEFAULT NULL,
    object_name      VARCHAR2,
    policy_name      VARCHAR2);

Parameters

Parameter	Required	Description
`object_schema`	No	The schema of the target table. Defaults to the current user's schema.
`object_name`	Yes	The name of the table for which to enable the policy.
`policy_name`	Yes	The name of the data masking policy to enable.

Example

CREATE TABLE masktbl(name varchar2(40), num integer);

INSERT INTO masktbl VALUES('Alice', 123456789);
INSERT INTO masktbl VALUES('Bob', 234567890);

-- Create a policy in a disabled state.
BEGIN
    DBMS_REDACT.ADD_POLICY(object_name => 'masktbl',
                            policy_name => 'p1',
                            expression => '1=1',
                            column_name => 'num',
                            function_type => DBMS_REDACT.PARTIAL,
                            function_parameters => '5,1,4',
                            enable => false);
END;

-- The policy is disabled. The num column shows original values.
SELECT name, num FROM masktbl ORDER BY 1, 2;
    name  |    num
-------+-----------
    Alice | 123456789
    Bob   | 234567890
(2 rows)

-- Enable the policy.
CALL DBMS_REDACT.ENABLE_POLICY(object_name => 'masktbl', policy_name => 'p1');

-- The num column now shows masked values.
SELECT name, num FROM masktbl ORDER BY 1, 2;
    name  |    num
-------+-----------
    Alice | 555556789
    Bob   | 555567890
(2 rows)

DROP_POLICY

Removes a data masking policy from a table.

Syntax

DBMS_REDACT.DROP_POLICY (
    object_schema      VARCHAR2 DEFAULT NULL,
    object_name        VARCHAR2,
    policy_name        VARCHAR2);

Parameters

Parameter	Required	Description
`object_schema`	No	The schema of the target table. Defaults to the current user's schema.
`object_name`	Yes	The name of the table from which to remove the policy.
`policy_name`	Yes	The name of the data masking policy to remove.

Example

CREATE TABLE masktbl(name varchar2(40), num integer);

INSERT INTO masktbl VALUES('Alice', 123456789);
INSERT INTO masktbl VALUES('Bob', 234567890);

-- Create a policy.
BEGIN
    DBMS_REDACT.ADD_POLICY(object_name => 'masktbl',
                            policy_name => 'p1',
                            expression => '1=1',
                            column_name => 'num',
                            function_type => DBMS_REDACT.PARTIAL,
                            function_parameters => '5,1,4');
END;

-- The num column shows masked values.
SELECT name, num FROM masktbl ORDER BY 1, 2;
    name  |    num
-------+-----------
    Alice | 555556789
    Bob   | 555567890
(2 rows)

-- Remove the policy.
CALL DBMS_REDACT.DROP_POLICY(object_name => 'masktbl', policy_name => 'p1');

-- The policy no longer exists. The num column shows original values.
SELECT name, num FROM masktbl ORDER BY 1, 2;
    name  |    num
-------+-----------
    Alice | 123456789
    Bob   | 234567890
(2 rows)

UPDATE_FULL_REDACTION_VALUES

Updates the default values shown for each data type when DBMS_REDACT.FULL masking is applied.

Syntax

DBMS_REDACT.UPDATE_FULL_REDACTION_VALUES (
    number_val       NUMBER        DEFAULT  NULL,
    binfloat_val     FLOAT4        DEFAULT  NULL,
    bindouble_val    FLOAT8        DEFAULT  NULL,
    char_val         CHAR          DEFAULT  NULL,
    varchar_val      VARCHAR2      DEFAULT  NULL,
    nchar_val        NCHAR         DEFAULT  NULL,
    nvarchar_val     NVARCHAR2     DEFAULT  NULL,
    date_val         DATE          DEFAULT  NULL,
    ts_val           TIMESTAMP     DEFAULT  NULL,
    tswtz_val        TIMESTAMPTZ   DEFAULT  NULL,
    blob_val         BLOB          DEFAULT  NULL,
    clob_val         CLOB          DEFAULT  NULL,
    nclob_val        NCLOB         DEFAULT  NULL);

Parameters

Parameter	Description
`number_val`	The default masked value for NUMBER columns.
`binfloat_val`	The default masked value for FLOAT columns.
`bindouble_val`	The default masked value for DOUBLE columns.
`char_val`	The default masked value for CHAR or NCHAR columns.
`varchar_val`	The default masked value for VARCHAR2, NVARCHAR2, CLOB, or NCLOB columns.
`nchar_val`	Oracle compatibility only. No configuration needed.
`nvarchar_val`	Oracle compatibility only. No configuration needed.
`date_val`	The default masked value for DATE columns.
`ts_val`	The default masked value for TIMESTAMP columns.
`tswtz_val`	The default masked value for TIMESTAMPTZ columns.
`blob_val`	The default masked value for BLOB columns.
`clob_val`	Oracle compatibility only. No configuration needed.
`nclob_val`	Oracle compatibility only. No configuration needed.

Example

The following example applies full masking to the num column, then changes the default masked value from 0 to 12345.

CREATE TABLE masktbl(name varchar2(40), num integer);

INSERT INTO masktbl VALUES('Alice', 123456789);
INSERT INTO masktbl VALUES('Bob', 234567890);

-- Create a full masking policy.
BEGIN
    DBMS_REDACT.ADD_POLICY(object_name => 'masktbl',
                            policy_name => 'p1',
                            expression => '1=1',
                            column_name => 'num',
                            function_type => DBMS_REDACT.FULL);
END;

-- The num column shows 0 (the default full-masking value for NUMBER).
SELECT name, num FROM masktbl ORDER BY 1, 2;
    name  | num
-------+-----
    Alice |   0
    Bob   |   0
(2 rows)

-- Update the default masked value for NUMBER columns.
CALL DBMS_REDACT.UPDATE_FULL_REDACTION_VALUES(number_val => 12345);

-- The num column now shows 12345.
SELECT name, num FROM masktbl ORDER BY 1, 2;
    name  |  num
-------+-------
    Alice | 12345
    Bob   | 12345
(2 rows)