Oracle DBMS_REDACT
Version 26ai

General Information
Library Note Morgan's Library Page Header
The best Oracle News for FY2026

Oracle Database 26ai will be available on generic Linux platforms in January and soon on AIX and Windows
Purpose Redact, mask, data returned by query
AUTHID CURRENT_USER
Constants
Name Data Type Value
Add Policy
NONE BINARY_INTEGER 0
FULL BINARY_INTEGER 1
PARTIAL BINARY_INTEGER 2
FORMAT_PRESERVING BINARY_INTEGER 3
RANDOM BINARY_INTEGER 4
REGEXP BINARY_INTEGER 5
NULLIFY BINARY_INTEGER 6
REGEXP_WIDTH BINARY_INTEGER 7
Alter Policy
ADD_COLUMN BINARY_INTEGER 1
DROP_COLUMN BINARY_INTEGER 2
MODIFY_EXPRESSION BINARY_INTEGER 3
MODIFY_COLUMN BINARY_INTEGER 4
SET_POLICY_DESCRIPTION BINARY_INTEGER 5
SET_COLUMN_DESCRIPTION BINARY_INTEGER 6
Presets
REDACT_US_SSN_F5 VARCHAR2(29) 'VVVFVVFVVVV,VVV-VV-VVVV,X,1,5'
REDACT_US_SSN_L4 VARCHAR2(29) 'VVVFVVFVVVV,VVV-VV-VVVV,X,6,9'
REDACT_US_SSN_ENTIRE VARCHAR2(29) 'VVVFVVFVVVV,VVV-VV-VVVV,X,1,9'
REDACT_NUM_US_SSN_F5 VARCHAR2(5) '9,1,5'
REDACT_NUM_US_SSN_L4 VARCHAR2(5) '9,6,9'
REDACT_NUM_US_SSN_ENTIRE VARCHAR2(5) '9,1,9'
REDACT_ZIP_CODE VARCHAR2(17) 'VVVVV,VVVVV,X,1,5'
REDACT_NUM_ZIP_CODE VARCHAR2(5) '9,1,5'
REDACT_CCN16_F12 VARCHAR2(46) 'VVVVFVVVVFVVVVFVVVV,VVVV-VVVV-VVVV-VVVV,*,1,12'
REDACT_DATE_MILLENNIUM VARCHAR2(9) 'm1d1y2000'
REDACT_DATE_EPOCH VARCHAR2(9) 'm1d1y1970'
Redaction Formats from EM
REDACT_AMEX_CCN_FORMATTED VARCHAR2(42) 'VVVVFVVVVVVFVVVVV,VVVV-VVVVVV-VVVVV,*,1,10';
REDACT_AMEX_CCN_NUMBER VARCHAR2(6) '0,1,10'
REDACT_SIN_FORMATTED VARCHAR2(29) 'VVVFVVVFVVV,VVV-VVV-VVV,*,1,6'
REDACT_SIN_NUMBER VARCHAR2(5) '9,1,6'
REDACT_SIN_UNFORMATTED VARCHAR2(25) 'VVVVVVVVV,VVVVVVVVV,*,1,6'
REDACT_CCN_FORMATTED VARCHAR2(46) 'VVVVFVVVVFVVVVFVVVV,VVVV-VVVV-VVVV-VVVV,*,1,12'
REDACT_CCN_NUMBER VARCHAR2(6) '9,1,12'
REDACT_NA_PHONE_FORMATTED VARCHAR2(31) 'VVVFVVVFVVVV,VVV-VVV-VVVV,X,1,6'
REDACT_NA_PHONE_NUMBER VARCHAR2(6) '0,4,10'
REDACT_NA_PHONE_UNFORMATTED VARCHAR2(28) 'VVVVVVVVVV,VVVVVVVVVV,X,4,10'
REDACT_UK_NIN_FORMATTED VARCHAR2(33) 'VVFVVFVVFVVFV,VV VV VV VV V,X,3,8'
REDACT_UK_NIN_UNFORMATTED VARCHAR2(25) 'VVVVVVVVV,VVVVVVVVV,X,3,8'
Preset RegExp Patterns
RE_PATTERN_US_SSN VARCHAR2(26) '(\d\d\d)-(\d\d)-(\d\d\d\d)'
RE_PATTERN_CC_L6_T4 VARCHAR2(33) '(\d\d\d\d\d\d)(\d\d\d*)(\d\d\d\d)'
RE_PATTERN_ANY_DIGIT VARCHAR2(2) '\d'
RE_PATTERN_US_PHONE VARCHAR2(39) '(\(\d\d\d\)|\d\d\d)-(\d\d\d)-(\d\d\d\d)'
RE_PATTERN_EMAIL_ADDRESS VARCHAR2(51) '([A-Za-z0-9._%+-]+)@([A-Za-z0-9.-]+\.[A-Za-z]{2,4})'
RE_PATTERN_IP_ADDRESS VARCHAR2(36) '(\d{1,3}\.\d{1,3}\.\d{1,3})\.\d{1,3}'
RE_PATTERN_AMEX_CCN VARCHAR2(17) '.*(\d\d\d\d\d)$'
RE_PATTERN_CCN VARCHAR2(15) '.*(\d\d\d\d)$'
Preset RegExp Replace String Patterns
RE_REDACT_CC_MIDDLE_DIGITS VARCHAR2(10) '\1XXXXXX\3'
RE_REDACT_WITH_SINGLE_X VARCHAR2(1) 'X'
RE_REDACT_WITH_SINGLE_1 VARCHAR2(1) '1'
RE_REDACT_US_PHONE_L7 VARCHAR2(11) '\1-XXX-XXXX'
RE_REDACT_EMAIL_NAME VARCHAR2(7) 'xxxx@\2'
RE_REDACT_EMAIL_DOMAIN VARCHAR2(12) '\1@xxxxx.com'
RE_REDACT_EMAIL_ENTIRE VARCHAR2(14) 'xxxx@xxxxx.com'
RE_REDACT_IP_L3 VARCHAR2(6) '\1.999'
RE_REDACT_AMEX_CCN VARCHAR2(12) '**********\1'
RE_REDACT_CCN VARCHAR2(14) '************\1'
Preset RegExp Position Value
RE_BEGINNING BINARY_INTEGER 1
Preset RegExp Occurrence Value
RE_ALL BINARY_INTEGER 0
RE_FIRST BINARY_INTEGER 1
Preset RegExp Match Parameters
RE_CASE_SENSITIVE VARCHAR2(1) 'c'
RE_CASE_INSENSITIVE VARCHAR2(1) 'i'
RE_MULTIPLE_LINES VARCHAR2(1) 'm'
RE_NEWLINE_WILDCARD VARCHAR2(1) 'n'
RE_IGNORE_WHITESPACE VARCHAR2(1) 'x'
Dependencies
DBMS_ASSERT DBMS_REDACT_INT DBMS_REDACT_LIB
Documented Yes: Packages and Types Reference
Exceptions
Error Code Reason
ORA-28075 The policy expression has unsupported functions
First Available 12.1
Pragma SUPPLEMENTAL_LOG_DATA(default, AUTO_WITH_COMMIT);
Security Model Owned by SYS with EXECUTE granted to the EXECUTE_CATALOG_ROLE and IMP_FULL_DATABASE roles.
Source {ORACLE_HOME}/rdbms/admin/dbmsredacta.sql
{ORACLE_HOME}/rdbms/admin/prvtredacta.plb
Subprograms
ADD_POLICY DROP_POLICY FPM_UNMASK
ALTER_POLICY DROP_POLICY_EXPRESSION UPDATE_FULL_REDACTION_VALUES
APPLY_POLICY_EXPR_TO_COL ENABLE_POLICY UPDATE_POLICY_EXPRESSION
CREATE_POLICY_EXPRESSION FPM_MASK Demo
DISABLE_POLICY    
 
ADD_POLICY
Define a redaction policy on a table or view dbms_redact.add_policy(
object_schema          IN VARCHAR2       := NULL,
object_name            IN VARCHAR2,
policy_name            IN VARCHAR2,
policy_description     IN VARCHAR2       := NULL,
column_name            IN VARCHAR2       := NULL,
column_description     IN VARCHAR2       := NULL,
function_type          IN BINARY_INTEGER := dbms_redact.full,
function_parameters    IN VARCHAR2       := NULL,
expression             IN VARCHAR2,
enable                 IN BOOLEAN        := TRUE,
regexp_pattern         IN VARCHAR2       := NULL,
regexp_replace_string  IN VARCHAR2       := NULL,
regexp_position        IN BINARY_INTEGER := 1,
regexp_occurrence      IN BINARY_INTEGER := 0,
regexp_match_parameter IN VARCHAR2       := NULL);
CREATE OR REPLACE FUNCTION myredact RETURN BINARY_INTEGER AUTHID DEFINER IS
BEGIN
  RETURN 1;
END myredact;
/

DECLARE
 lSchema      redaction_policies.object_owner%TYPE := USER;
 lObject      redaction_policies.object_name%TYPE := 'PERSON';
 lPolicy      redaction_policies.policy_name%TYPE := 'PERSON_SSN_REDACT';
 lDescript    redaction_policies.policy_description%TYPE := 'SSN Obfuscation';
 lColumn      redaction_columns.column_name%TYPE := 'SSN';
 lColDes      redaction_columns.column_description%TYPE := 'SSN Masking Test';
 lFuncType    BINARY_INTEGER := dbms_redact.full;
 lFuncParam   redaction_columns.function_parameters%TYPE := '';
 lExpression  VARCHAR2(60) := 'SYS_CONTEXT(''SYS_SESSION_ROLES'', ''SUPERVISOR'') = ''FALSE''';
 lEnable      BOOLEAN := FALSE;
 lREPattern   redaction_columns.regexp_pattern%TYPE := NULL;
 lReplString  redaction_columns.regexp_replace_string%TYPE := NULL;
 lREPosition  BINARY_INTEGER := 1;
 lREOccur     BINARY_INTEGER := 0;
 lREMatchParm redaction_columns.regexp_match_parameter%TYPE := NULL;
BEGIN
  dbms_redact.add_policy(lSchema, lObject, lPolicy, lDescript, lColumn, lColDes,
                         lFuncType, lFuncParam, lExpression, lEnable, lREPattern,
                         lReplString, lREPosition, lREOccur, lREMatchParm);
END;
/

col pname format a20
col pexpr format a60
col pdesc format a30
col object_owner format a12
col object_name format a12
col column_name format a12
col column_description format a20
col function_type format a12
col policy_name format a18
col expression format a30
col policy_description format a20

SELECT * FROM sys.radm$;

SELECT * FROM sys.radm_td$;

SELECT object_owner, object_name, column_name, function_type, regexp_position, regexp_occurrence, column_description
FROM redaction_columns;

SELECT * FROM redaction_policies;

-- Maria Colgan's demo
BEGIN
  dbms_redact.add_policy(object_schema => 'coffeeshop',
                         object_name   => 'customers',
                         column_name   => 'c_custid',
                         policy_name   => 'redact_Loyal_card',
                         function_type => DBMS_REDACT.FULL,
                         expression    => '1=1');
END;
/
 
ALTER_POLICY
Alter a data redaction policy dbms_redact.alter_policy(
object_schema          IN VARCHAR2       := NULL,
object_name            IN VARCHAR2,
policy_name            IN VARCHAR2,
action                 IN BINARY_INTEGER := dbms_redact.ADD_COLUMN,
column_name            IN VARCHAR2       := NULL,
function_type          IN BINARY_INTEGER := dbms_redact.FULL,
function_parameters    IN VARCHAR2       := NULL,
expression             IN VARCHAR2       := NULL,
regexp_pattern         IN VARCHAR2       := NULL,
regexp_replace_string  IN VARCHAR2       := NULL,
regexp_position        IN BINARY_INTEGER := 1,
regexp_occurrence      IN BINARY_INTEGER := 0,
regexp_match_parameter IN VARCHAR2       := NULL,
policy_description     IN VARCHAR2       := NULL,
column_description     IN VARCHAR2       := NULL);
exec dbms_redact.alter_policy(USER, 'PERSON', 'PERSON_SSN_REDACT', column_name => 'USER_PIN');

PL/SQL procedure successfully completed.
 
APPLY_POLICY_EXPR_TO_COL
Apply a column-specific Policy Expression to a column dbms_redact.apply_policy_expr_to_col(
object_schema          IN VARCHAR2 := NULL,
object_name            IN VARCHAR2,
column_name            IN VARCHAR2,
policy_expression_name IN VARCHAR2 := NULL);
TBD
 
CREATE_POLICY_EXPRESSION
Create a Data Redaction Policy Expression dbms_redact.create_policy_expression(
policy_expression_name        IN VARCHAR2,
expression                    IN VARCHAR2,
policy_expression_description IN VARCHAR2 := NULL);
TBD
 
DISABLE_POLICY
Disable an existing data redaction policy dbms_redact.disable_policy(
object_schema IN VARCHAR2 := NULL,
object_name   IN VARCHAR2,
policy_name   IN VARCHAR2);
exec dbms_redact.display_policy(USER, 'PERSON', 'PERSON_SSN_REDACT');

PL/SQL procedure successfully completed.
 
DROP_POLICY
Drop a data redaction policy dbms_redact.drop_policy(
object_schema IN VARCHAR2 := NULL,
object_name   IN VARCHAR2,
policy_name   IN VARCHAR2);
exec dbms_redact.drop_policy(USER, 'PERSON', 'PERSON_SSN_REDACT');

PL/SQL procedure successfully completed.
 
DROP_POLICY_EXPRESSION
Drop a Data Redaction Policy Expression dbms_redact.drop_policy_expression(policy_expression_name IN VARCHAR2);
TBD
 
ENABLE_POLICY
Enable a disable data redaction policy dbms_redact.enable_policy(
object_schema IN VARCHAR2 := NULL,
object_name   IN VARCHAR2,
policy_name   IN VARCHAR2);
exec dbms_redact.enable_policy(USER, 'PERSON', 'PERSON_SSN_REDACT');

PL/SQL procedure successfully completed.
 
FPM_MASK
Apply format-preserving Data Redaction to the input dbms_redact.fpm_mask(
input_format  IN VARCHAR2,
output_format IN VARCHAR2,
input_value   IN VARCHAR2,
masking_key   IN VARCHAR2);
TBD
 
FPM_UNMASK
Remove a format-preserving Data Redaction from the input dbms_redact.fpm_unmask(
input_format  IN VARCHAR2,
output_format IN VARCHAR2,
input_value   IN VARCHAR2,
masking_key   IN VARCHAR2);
TBD
 
UPDATE_FULL_REDACTION_VALUES
Update replacements for full redaction dbms_redact.update_full_redaction_values(
number_val    IN NUMBER        := NULL,
binfloat_val  IN BINARY_FLOAT  := NULL,
bindouble_val IN BINARY_DOUBLE := NULL,
char_val      IN CHAR          := NULL,
varchar_val   IN VARCHAR2      := NULL,
nchar_val     IN NCHAR         := NULL,
nvarchar_val  IN NVARCHAR2     := NULL,
datecol_val   IN DATE          := NULL,
ts_val        IN TIMESTAMP     := NULL,
tswtz_val     IN TIMESTAMP WITH TIME ZONE := NULL,
blob_val      IN BLOB          := NULL,
clob_val      IN CLOB          := NULL,
nclob_val     IN NCLOB         := NULL);
TBD
 
UPDATE_POLICY_EXPRESSION
update a Data Redaction Policy Expression dbms_redact.update_policy_expression(
policy_expression_name        IN VARCHAR2,
expression                    IN VARCHAR2,
policy_expression_description IN VARCHAR2 := NULL);
TBD
 
Demo
Demonstration of DBMS_REDACT functionality conn uwclass/uwclass@pdbdev

CREATE TABLE person (
per_id        NUMBER,
first_name    VARCHAR2(25),
last_name     VARCHAR2(25) NOT NULL,
mobile_phone  VARCHAR2(12),
ssn           VARCHAR2(11),
user_pin      NUMBER(4),
primary_email VARCHAR2(30));

ALTER TABLE person
ADD CONSTRAINT pk_person
PRIMARY KEY (per_id);

INSERT INTO person
(per_id, first_name, last_name, mobile_phone, ssn, primary_email)
VALUES
(1, 'Dan', 'Morgan', '206-555-1212', '555-11-2222', 'damorgan12c@gmail.com');

COMMIT;

-- incomplete

Related Topics
Built-in Functions
Built-in Packages
Database Security
Data Masking
DBMS_REDACT_INT
DBMS_REDACTOR
What's New In 21c
What's New In 26ai

Morgan's Library Page Footer
This site is maintained by Daniel Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2026 Daniel A. Morgan All Rights Reserved