Oracle DBMS_REDACT
Version 12.1.0.2

General Information
Library Note Morgan's Library Page Footer
The Library is currently in the process of being upgraded from Oracle Database Version 12.1.0.1 to 12.1.0.2. Demos are being upgraded to reflect the new Container paradigm as well as EBR (Edition Based Redefinition) and may contain references to CDBs, PDBs, and other objects you may not be familiar with such as CDB_OBJECTS_AE: Welcome to 12c.

Interested in Oracle GoldenGate? Check out the IGGOUG,
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
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'
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}'
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'
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_REDACT 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;
 
ALTER_POLICY
Alter a data redcaction 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');
 
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');
 
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);
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;

Related Topics
Data Masking
Packages
Security

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-2014 Daniel A. Morgan All Rights Reserved