REDACT function causes overly masked result

There can be several reasons why the REDACT function causes an overly masked result. Some possible ways to resolve your issue are provided here.

Symptoms

The redact function causes an overly masked result or an ORA-03106 error in Oracle traffic.

Causes

The redact function in the Guardium policy rule is doing a pattern match with the result set. It has a feature to replace the matched string with the user specified character.

Environment

Guardium collectors are affected.

Resolving the problem

Use the regular expression [\x0c]{1}[0-9]{8}([0-9]{4}). This regular expression ensures that it starts with the length of the column followed by 12 digits and replaces the last 4 digits.

For Windows databases:

For Microsoft SQL with VARCHAR only, you must specify the exact number (from 01 to 99) of characters to redact. Specify the number in angle brackets. For example, to redact the first 10 characters of your string:
```
<10>([0-9][0-9][0-9][0-9][0-9][0-9])[0-9][0-9][0-9][0-9]
```
For Microsoft SQL, you can also use the following expression to redact the first 6 digits of a 10-digit number:
```
[\x0A\x14]([0-9][0-9][0-9][0-9][0-9][0-9])[0-9][0-9][0-9][0-9] 
```
Where \x0A represents 10 bytes and \x14 represents 20 bytes.

Note: For more information about using regex with Windows, see REDACT - Working with regex on Windows DB servers.