With Data Redaction we can define policy for specific table/view columns where its data is redacted when it’s queried by end/application users. I won’t go into details about how policies are created, the available types for redaction etc… For that topic you can check Data Redaction part 1 and part 2
What I want to point is that Data Redaction doesn’t work properly when function based indexes or indexes based on expression are used for the redacted column. I couldn’t find any logic explanation why it is like that, if you have any opinions feel free to comment.
As example I took table EMPLOYEES from HR schema.
I’ve defined Data Redaction policy SALARY_FULL_REDACT (with FULL redaction type) which redacts SALARY column to 0 (zero).
If you didn’t know, default value for DBMS_REDACT.FULL is 0 (zero).
As you can see this policy is in effect only for iarsov user. If i log in as iarsov and query SALARY column from HR.EMPLOYEES I should get 0 as result.
So far so good, as expected data redaction took place.
Now, lets create normal index on SALARY and check if we have any difference.
Again, data redaction took place. It seems perfect.
Next I would like to show whether Data Redaction will be used if we use user-defined function to access SALARY values. For that purpose I’ve created function f as simple (dummy) function which returns the same value passed as parameter. The function is defined as deterministic because later it’s used for the function-based index definition.
Great, everything works fine.
Now, lets create function based index on SALARY and try the query again.
We’ve just enabled access to every employee salary (sensitive data).
So, is this a bug or normal behavior ?
Me personally, I think its a bug because data redaction works fine if we don’t have the index, but with the index somehow data redaction is disabled or something is messing up …
GROUP BY clause
According database documentation SQL expression are not allowed on redacted column if used in GROUP BY clause, ORA-00979 will be raised.
Just to prove, as expected:
Be careful when implementing Data Redaction because you might not intentionally reveal sensitive data.
I haven’t tested if this behavior is same for all data redaction types.