Regular Expression-Based Redaction in Oracle Redaction

Regular Expression-Based Redaction Policy in Oracle Data Redaction

Regular expression-based redaction is used to search for patterns of data and redact that data according to your need.
For example: email addresses as an example you can search and redact it.

Syntax of Regular Expression:

DBMS_REDACT.ADD_POLICY (
object_schema IN VARCHAR2 := NULL,
object_name IN VARCHAR2,
column_name IN VARCHAR2 := NULL,
policy_name IN VARCHAR2,
function_type IN BINARY_INTEGER := 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);

Parameters:
function_type: Value for regular expression redaction is DBMS_REDACT.REGEXP.
regexp_pattern: describe the search pattern for data if it match then replaces the data by the regexp_replace_string.
regexp_replace_string: Specifies how you want to replace the data to be redacted.
regexp_position: Specifies the starting position for the string search. Default is 1 position
regexp_occurrence: Specifies how to perform the search and replace operation. If 0 or RE_ALL then all occurrence pattern is replace. If RE_FIRST then first occurrence if n integer then n occurrence replace
regexp_match_parameter: Specifies a text literal that lets you change the default matching behaviour of the function.

Shortcuts for both the regexp_pattern and regexp_replace_string parameters

DBMS_REDACT.RE_PATTERN_ANY_DIGIT : Match any digit and redact with X or 1 as follows:
regexp_replace_string => DBMS_REDACT.RE_REDACT_WITH_SINGLE_X
regexp_replace_string => DBMS_REDACT.RE_REDACT_WITH_SINGLE_1

DBMS_REDACT.RE_PATTERN_CC_L6_T4 : Number leading with 6 digit and trail with 4 digit credit card, redact middle numbers as follows:
regexp_replace_string => DBMS_REDACT.RE_REDACT_CC_MIDDLE_DIGITS

DBMS_REDACT.RE_PATTERN_US_PHONE : U.S. telephone number with the characters specified. Redact last 7 digit as follows:
regexp_replace_string => DBMS_REDACT.RE_REDACT_US_PHONE_L7

DBMS_REDACT.RE_PATTERN_EMAIL_ADDRESS: Searches for any email address, redact as name understood.
regexp_replace_string => RE_REDACT_EMAIL_NAME (redact username)
regexp_replace_string => RE_REDACT_EMAIL_DOMAIN(redact domain name)
regexp_replace_string => RE_REDACT_EMAIL_ENTIRE ( redact both)

DBMS_REDACT.RE_PATTERN_IP_ADDRESS: Searches for IP address. Redact last section with 999
regexp_replace_string => DBMS_REDACT.RE_REDACT_IP_L3

Example

BEGIN
DBMS_REDACT.ADD_POLICY(
object_schema => 'HR',
object_name => 'Employees',
column_name => 'creditcardno',
policy_name => 'redact_emp_cc',
function_type => DBMS_REDACT.REGEXP,
function_parameters => NULL,
expression => '1=1',
regexp_pattern => DBMS_REDACT.RE_PATTERN_CC_L6_T4,
regexp_replace_string => DBMS_REDACT.RE_REDACT_CC_MIDDLE_DIGITS,
regexp_position => DBMS_REDACT.RE_BEGINNING,
regexp_occurrence => DBMS_REDACT.RE_FIRST,
regexp_match_parameter => DBMS_REDACT.RE_MATCH_CASE_INSENSITIVE,
policy_description => 'Regular expressions to redact credit card numbers',
column_description => 'Employee credit card numbers');
END;
/

SELECT creditcardno FROM HR.Employees;
creditcardno
----------------
401288XXXXXX2222
411111XXXXXX3333
555555XXXXXX4444
511111XXXXXX5555

Custom or manual define the regular expression
Choose the matching pattern in the regexp pattern and replace it with help of regexp_replace_string.

BEGIN
DBMS_REDACT.ADD_POLICY(
object_schema => 'hr',
object_name => 'employees',
column_name => 'creditcardno',
policy_name => 'redact_emp_ids',
function_type => DBMS_REDACT.REGEXP,
expression => '1=1',
regexp_pattern => '(\d\d\d)(\d\d)(\d\d\d\d)',
regexp_replace_string => 'XXXXX\3',
regexp_position => 1,
regexp_occurrence => 0,
regexp_match_parameter => 'i',
policy_description => 'Redacts employee IDs using regular expression',
column_description => 'creditcard redact');
END;
/

Note: data divided into 3 group and redact first five character and left the 3 group

Output:
SELECT creditcardno FROM hr.employees;
creditcardno
------------
XXXXX1234
XXXXX5678

Advertisements

One thought on “Regular Expression-Based Redaction in Oracle Redaction

  1. Pingback: Oracle Redaction | Smart way of Technology

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s