Oracle | DBMS

Oracle DBMS_REDACT Version 23c

General Information

Library Note

Morgan's Library Page Header

ACE Director Alum Daniel Morgan, founder of Morgan's Library, is scheduling complimentary technical Workshops on Database Security for the first 30 Oracle Database customers located anywhere in North America, EMEA, LATAM, or APAC that send an email to asra_us@oracle.com. Request a Workshop for your organization today.

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');

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');

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);

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');

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