DBMS_REDACT API parameters

DBMS_REDACT API parameters:

Name
Datatype
Value Description
Add Policy
NONE
BINARY_INTEGER
0 No redaction, zero effect on the result of a query against table.
FULL
BINARY_INTEGER
1 Full redaction, redacts full values of the column data.
PARTIAL
BINARY_INTEGER
2 Partial redaction, redacts a portion of the column data.
FORMAT_PRESERVING BINARY_INTEGER 3 Format Preserving
RANDOM
BINARY_INTEGER
4 Random redaction, each query results in a different random value depending on the datatype of the column.
REGEXP
BINARY_INTEGER
5 Regular Expression based redaction, searches for the pattern of data to redact.
NULLIFY
BINARY_INTEGER
6  Nullify
REGEXP_WIDTH BINARY_INTEGER 7  Regular Expression width
Alter Policy
ADD_COLUMN BINARY_INTEGER 1 Adds a column to the redaction policy.
DROP_COLUMN BINARY_INTEGER 2 Drops a column from the redaction policy.
MODIFY_EXPRESSION BINARY_INTEGER 3 Modifies the expression of a redaction policy. The redaction is applied when the expression evaluates to the BOOLEAN value to TRUE.
MODIFY_COLUMN BINARY_INTEGER 4 Modifies a column in the redaction policy to change the redaction function type or function parameter.
SET_POLICY_DESCRIPTION BINARY_INTEGER 5 Sets the redaction policy description. 
SET_COLUMN_DESCRIPTION BINARY_INTEGER 6 Sets a description for the redaction performed on the column.
Presets
REDACT_US_SSN_F5
VARCHAR2(29)
‘VVVFVVFVVVV,VVV-VV-VVVV,X,1,5’ Redacts the first 5 numbers of SSN. Example: The number 123-45-6789 becomes XXX-XX-6789.
REDACT_US_SSN_L4
VARCHAR2(29)
‘VVVFVVFVVVV,VVV-VV-VVVV,X,6,9’ Redacts the last 4 numbers of SSN. Example: The number 123-45-6789 becomes 123-45-XXXX
REDACT_US_SSN_ENTIRE VARCHAR2(29) ‘VVVFVVFVVVV,VVV-VV-VVVV,X,1,9’ Redacts the entire SSN. Example: The number 123-45-6789 becomes XXX-XX-XXXX.
REDACT_NUM_US_SSN_F5 VARCHAR2(5) ‘9,1,5’ Redacts the first 5 numbers of SSN when the column is a number datatype. Example: The number 123456789 becomes 999996789.
REDACT_NUM_US_SSN_L4 VARCHAR2(5) ‘9,6,9’ Redacts the last four numbers of SSN when the column is a number datatype. Example: The number 123456789 becomes 123459999.
REDACT_NUM_US_SSN_ENTIRE VARCHAR2(5) ‘9,1,9’ Redacts the entire SSN when the column is a number datatype. Example: The number 123456789 becomes 999999999.
REDACT_ZIP_CODE
VARCHAR2(17)
‘VVVVV,VVVVV,X,1,5’ Redacts a 5 digit zip code. Example: 12345 becomes XXXXX.
REDACT_NUM_ZIP_CODE VARCHAR2(5) ‘9,1,5’ Redacts a 5 digit zip code when the column is a number datatype. Example: 12345 becomes 99999.
REDACT_CCN16_F12
VARCHAR2(46)
‘VVVVFVVVVFVVVVFVVVV,VVVV-VVVV-VVVV-VVVV,*,1,12’ Redacts a 16 digit credit card number and displays only 4 digits. Example: 1234 5678 9000 2358 becomes ****-****-****-2358.
REDACT_DATE_MILLENNIUM VARCHAR2(9) ‘m1d1y2000’ Redacts a date that is in the DD-MM-YY format. Example: 01-MAR-00 becomes Jan 1, 2000.
REDACT_DATE_EPOCH VARCHAR2(9) ‘m1d1y1970’ Redacts all dates to 01-JAN-1970.
Redaction Formats
REDACT_AMEX_CCN_FORMATTED VARCHAR2(42) ‘VVVVFVVVVVVFVVVVV,VVVV-VVVVVV-VVVVV,*,1,10’; Redacts the Amercian Express credit card number and replaces the digit with * except for the last 5 digits. Example: The credit card number 1234 567890 34500 becomes **** ****** 34500.
REDACT_AMEX_CCN_NUMBER VARCHAR2(6) ‘0,1,10’ Redacts the Amercian Express credit card number and replaces the digit with 0 except the last 5 digits. Example: The credit card number 1234 567890 34500 becomes 0000 000000 34500.
REDACT_SIN_FORMATTED VARCHAR2(29) ‘VVVFVVVFVVV,VVV-VVV-VVV,*,1,6’ Redacts the Social Insurance Number by replacing the first 6 digits by *. Example: 123-456-789 becomes ***-***-789.
REDACT_SIN_NUMBER VARCHAR2(5) ‘9,1,6’ Redacts the Social Insurance Number by replacing the first 6 digits by 9. Example: 123456789 becomes 999999789.
REDACT_SIN_UNFORMATTED VARCHAR2(25) ‘VVVVVVVVV,VVVVVVVVV,*,1,6’ Redacts the Social Insurance Number by replacing the first 6 digits by *. Example: 123456789 becomes ******789.
REDACT_CCN_FORMATTED VARCHAR2(46) ‘VVVVFVVVVFVVVVFVVVV,VVVV-VVVV-VVVV-VVVV,*,1,12’ Redacts a credit card number by * and displays only 4 digits. Example: The credit card number 1234-5678-9000-4671 becomes ****-****-****-4671.
REDACT_CCN_NUMBER VARCHAR2(6) ‘9,1,12’ Redacts a credit card number by 0 except the last 4 digits. Example: The credit card number 1234567890004671 becomes 0000000000004671.
REDACT_NA_PHONE_FORMATTED VARCHAR2(31) VVVFVVVFVVVV,VVV-VVV-VVVV,X,1,6′ Redacts the North American phone number by X leaving the area code. Example: 123-456-7890 becomes 123-XXX-XXXX.
REDACT_NA_PHONE_NUMBER VARCHAR2(6) ‘0,4,10’ Redacts the North American phone number by 0 leaving the area code. Example: 1234567890 becomes 1230000000.
REDACT_NA_PHONE_UNFORMATTED VARCHAR2(28) ‘VVVVVVVVVV,VVVVVVVVVV,X,4,10’ Redacts the North American phone number by X leaving the area code. Example: 1234567890 becomes 123XXXXXXX.
REDACT_UK_NIN_FORMATTED VARCHAR2(33) ‘VVFVVFVVFVVFV,VV VV VV VV V,X,3,8’ Redacts the UK National Insurance Number by X but leaving the alphabetic characters. Example: NY 22 01 34 D becomes NY XX XX XX D.
REDACT_UK_NIN_UNFORMATTED VARCHAR2(25) ‘VVVVVVVVV,VVVVVVVVV,X,3,8’ Redacts the UK National Insurance Number by X but leaving the alphabetic characters. Example: NY220134D becomes NYXXXXXXD.
RegExp Patterns
RE_PATTERN_US_SSN VARCHAR2(26) ‘(ddd)-(dd)-(dddd)’ Searches the SSN number and replaces the identified pattern with the characters specified by the regexp_replace_string parameter. ‘1-XXX-XXXX’ or ‘XXX-XXX-3’ will return 123-XXX-XXXX or XXX-XXX-6789 for the value ‘123-45-6789’ respectively.
RE_PATTERN_CC_L6_T4 VARCHAR2(33) ‘(dddddd)(ddd*)(dddd)’ Searches for the middle digits of a credit card number that includes 6 leading digits and 4 trailing digits. The regexp_replace_string setting to use with the format is RE_REDACT_CC_MIDDLE_DIGITS that replaces the identified pattern with the characters specified by the RE_REDACT_CC_MIDDLE_DIGITS parameter.
RE_PATTERN_ANY_DIGIT VARCHAR2(2) ‘d’ Searches for any digit and replaces the identified pattern with the characters specified by the following values of the regexp_replace_string parameter. regexp_replace_string=> RE_REDACT_WITH_SINGLE_X (replaces any matched digit with the X character). regexp_replace_string=> RE_REDACT_WITH_SINGLE_1 (replaces any matched digit with the 1 character).
RE_PATTERN_US_PHONE VARCHAR2(39) ‘((ddd)|ddd)-(ddd)-(dddd)’ Searches for the U.S phone number and replaces the identified pattern with the characters specified by the regexp_replace_string parameter. regexp_replace_string=> RE_REDACT_US_PHONE_L7 (searches the phone number and then replaces the last 7 digits).
RE_PATTERN_EMAIL_ADDRESS VARCHAR2(51) ‘([A-Za-z0-9._%+-]+)@([A-Za-z0-9.-]+.[A-Za-z]{2,4})’ Searches for the email address and replaces the identified pattern with the characters specified by the following values of the regexp_replace_string parameter. regexp_replace_string=> RE_REDACT_EMAIL_NAME (finds the email address and redacts the email username). regexp_replace_string=> RE_REDACT_EMAIL_DOMAIN (finds the email address and redacts the email domain). regexp_replace_string=> RE_REDACT_EMAIL_ENTIRE (finds the email address and redacts the entire email address).
RE_PATTERN_IP_ADDRESS VARCHAR2(36) ‘(d{1,3}.d{1,3}.d{1,3}).d{1,3}’ Searches for an IP address and replaces the identified pattern with the characters specified by the regexp_replace_string parameter. The regexp_replace_string parameter to be used is RE_REDACT_IP_L3 that replaces the last section of an IP address with 999 and indicates it is redacted.
RE_PATTERN_AMEX_CCN VARCHAR2(17) ‘.*(ddddd)$’ Searches for the American Express credit card number. The regexp_replace_string parameter to be used is RE_REDACT_AMEX_CCN that redacts all of the digits except the last 5.
RE_PATTERN_CCN VARCHAR2(15) ‘.*(dddd)$’ Searches for the credit card number other than American Express credit cards. The regexp_replace_string parameter to be used is RE_REDACT_CCN that redacts all of the digits except the last 4.
RegExp Replace String Patterns
RE_REDACT_CC_MIDDLE_DIGITS VARCHAR2(10) ‘1XXXXXX3’ Redacts the middle digits of a credit card number according to the regexp_pattern parameter with the RE_PATTERN_CC_L6_T4 format and replaces each redacted character with an X. Example: The credit card number 1234 5678 9000 2490 becomes 1234 56XX XXXX 2490.
RE_REDACT_WITH_SINGLE_X VARCHAR2(1) ‘X’ Replaces the data with a single X character for each of the characters. Example: The credit card number 1234 5678 9000 2490 becomes XXXX XXXX XXXX XXXX.
RE_REDACT_WITH_SINGLE_1 VARCHAR2(1) ‘1’ Replaces the data with a single 1 digit for each of the data digits. Example: The credit card number 1234 5678 9000 2490 becomes 1111 1111 1111 1111.
RE_REDACT_US_PHONE_L7 VARCHAR2(11) ‘1-XXX-XXXX’ Redacts the last 7 digits of U.S phone number according to the regexp_pattern parameter with the RE_PATTERN_US_PHONE format and replaces each redacted character with an X. Example: The phone number 123-444-5900 becomes 123-XXX-XXXX.
RE_REDACT_EMAIL_NAME VARCHAR2(7) ‘xxxx@2’ Redacts the email name according to the regexp_pattern parameter with the RE_PATTERN_EMAIL_ADDRESS format and replaces the email username with the four x characters. Example: The email address test1@example.com becomes xxxx@example.com.
RE_REDACT_EMAIL_DOMAIN VARCHAR2(12) ‘1@xxxxx.com’ Redacts the email domain name according to the regexp_pattern parameter with the RE_PATTERN_EMAIL_ADDRESS format and replaces the domain with the five x characters. Example: The email address test1@example.com becomes test1@xxxxx.com.
RE_REDACT_EMAIL_ENTIRE VARCHAR2(14) ‘xxxx@xxxxx.com’ Redacts the entire email address according to the regexp_pattern parameter with the RE_PATTERN_EMAIL_ADDRESS format and replaces the email address with the x characters. Example: The email address test1@example.com becomes xxxx@xxxxx.com.
RE_REDACT_IP_L3 VARCHAR2(6) ‘1.999’ Redacts the last 3 digits of an IP address according to the regexp_pattern parameter with the RE_PATTERN_IP_ADDRESS format. Example: The IP address 172.0.1.258 becomes 172.0.1.999, which is an invalid IP address.
RE_REDACT_AMEX_CCN VARCHAR2(12) ‘**********1’ Redacts the first 10 digits of an American Express credit card number. Example: 123456789062816 becomes **********62816.
RE_REDACT_CCN
VARCHAR2(14)
‘************1’ Redacts the first 12 digits of a credit card number. Example: 8749012678345671 becomes ************5671.
RegExp Position Value
RE_BEGINNING
BINARY_INTEGER
1 Specifies the position of a character where search must begin. By default, the value is 1 that indicates the search begins at the first character of source_char.
RegExp Occurrence Value
RE_ALL
BINARY_INTEGER
0 Specifies the replacement occurrence of a substring. If the value is 0, then the replacement of each matching substring occurs.
RE_FIRST
BINARY_INTEGER
1 Specifies the replacement occurrence of a substring. If the value is 1, then the replacement of the first matching substring occurs.
RegExp Match Parameters
RE_CASE_SENSITIVE
VARCHAR2(1)
‘c’ Specifies the case-sensitive matching.
RE_CASE_INSENSITIVE VARCHAR2(1) ‘i’ Specifies the case-insensitive matching.
RE_MULTIPLE_LINES
VARCHAR2(1)
‘m’ Treats the source string as multiple lines but if you omit this parameter, then it indicates as a single line.
RE_NEWLINE_WILDCARD VARCHAR2(1) ‘n’ Specifies the period (.), but if you omit this parameter, then the period does not match the newline character.
RE_IGNORE_WHITESPACE VARCHAR2(1) ‘x’ Ignores the whitespace characters.

Leave a Comment

Your email address will not be published. Required fields are marked *

Twitter
YouTube
Pinterest
LinkedIn