Oracle DBMS_REDACT
Version 19.3

General Information
Library Note Morgan's Library Page Header
Which has the higher priority in your organization: Deploying a new database or securing the ones you already have? Looking for a website, and resources, dedicated solely to securing Oracle databases? Check out DBSecWorx.
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 RADM_FPTM$ REDACTION_POLICIES
DBMS_REDACT_INT RADM_FPTM_LOB$ REDACTION_VALUES_FOR_TYPE_FULL
DBMS_REDACT_LIB REDACTION_COLUMNS Demo
Documented Yes
Exceptions
Error Code Reason
ORA-28075 The policy expression has unsupported functions
First Available 12.1.0
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
Subprograms
 
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');
 
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);
 
 
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);
 
 
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');
 
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');
 
DROP_POLICY_EXPRESSION
Drop a Data Redaction Policy Expression dbms_redact.drop_policy_expression(policy_expression_name IN VARCHAR2);
 
 
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');
 
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);
 
 
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;

Related Topics
Built-in Functions
Built-in Packages
Data Masking
Security
What's New In 18c
What's New In 19c

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