Finally I’ve found some time to finish this part 2 which covers date and number data types. If you are interested for redaction of character data type you can check part 1.
For this post I’m using TIME_ID and AMOUNT_SOLD columns from sales table – SH schema.
Policy definition is simple, the only “special” parameter that needs to be set is function_type parameter (to DBMS_REDACT.FULL). We don’t need to set other specific parameters for this type of redaction. Oracle will perform full redaction with predefined default value.
I’ve tested this policy with another user who has select privilege over sh.sales table. Note that I’ve enabled this policy only for specific user (in this case IARSOV user) by setting expression to SYS_CONTEXT(‘USERENV’,’CURRENT_USER’) = ‘IARSOV’
As in previous example there aren’t any special settings. Just specify DBMS_REDACT.FULL for function_type.
TIME_ID was replaced with default value (01.01.2001) for all 918843 rows.
For this redaction type I chose policy definition that replaces first two numbers with the number 9. Format defined for function_parameters: ‘9,1,2’
9 – what number to use as replacement
1 – start position
2 – end position
For dates partial redact, function_parameters requires specific format. Fields used for formatting are M (m#), D (d#), Y(y#), H(h#), M(m#), S(s#) where:
M – month (skip redaction)
m# – replace month with the value defined for #. (# can be between 1 and 12)
D – day (skip redaction)
d# – replace day with the value defined for #. (# can be between 1 and 31)
Y – year (skip redaction)
y# – replace year with the value defined for #. (# can be between 1 and 9999)
H – hour (skip redaction)
h# – replace hours with the value defined for #. (# can be between 0 and 23)
M – minute (skip redaction)
m# – replace minutes with the value defined for #. (# can be between 0 and 59)
S – second (skip redaction)
s# – replace seconds with the value defined for #. (# can be between 0 and 59)
If we want to redact the year we have to specify y# where # specifies the year we want our policy to use as replacement. For example, in order to redact the following date 21.12.2014 to 21.12.2000 we would set function_parameters parameter as ‘MDy2000’.
The year was redacted to 2000 for all 918843 rows.
Random redaction is simple to set (no need for specific format), something like FULL redaction just with different value for function_type.
We are getting different values for every execution.
Same is for dates.
As you can see the redaction kicked in and we received strange dates (specially for the year).