Oracle | DBMS

Oracle DBMS_REDACT Version 21c

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

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