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.
SQL> desc employees;
Name Null? Type
----------------------------------------- -------- ----------------------------
EMPLOYEE_ID NOT NULL NUMBER(6)
FIRST_NAME VARCHAR2(20)
LAST_NAME NOT NULL VARCHAR2(25)
EMAIL NOT NULL VARCHAR2(25)
PHONE_NUMBER VARCHAR2(20)
HIRE_DATE NOT NULL DATE
JOB_ID NOT NULL VARCHAR2(10)
SALARY NUMBER(8,2)
COMMISSION_PCT NUMBER(2,2)
MANAGER_ID NUMBER(6)
DEPARTMENT_ID NUMBER(4)
SQL>
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).
begin dbms_redact.ADD_POLICY(OBJECT_SCHEMA=>'HR', OBJECT_NAME=>'EMPLOYEES', POLICY_NAME=>'SALARY_FULL_REDACT', FUNCTION_TYPE=>DBMS_REDACT.FULL, COLUMN_NAME=>'SALARY', EXPRESSION=>'SYS_CONTEXT(''USERENV'',''CURRENT_USER'') = ''IARSOV'''); end; / PL/SQL procedure successfully completed.
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.
SQL> show user; USER is "IARSOV" SQL> SQL> SQL> select first_name,last_name, salary from hr.employees where email = 'SKING'; FIRST_NAME LAST_NAME SALARY -------------------- ------------------------- ---------- Steven King 0 SQL>
So far so good, as expected data redaction took place.
Now, lets create normal index on SALARY and check if we have any difference.
SQL> create index ss_ix on employees(salary); Index created. SQL> SQL> SQL> conn iarsov@pdb1 Enter password: Connected. SQL> SQL> SQL> SQL> select first_name,last_name, salary from hr.employees where email = 'SKING'; FIRST_NAME LAST_NAME SALARY -------------------- ------------------------- ---------- Steven King 0 SQL>
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.
SQL> create function f(p_val number) 2 return number deterministic 3 is 4 begin 5 return p_val; 6 end f; 7 / Function created. SQL> SQL> conn iarsov@pdb1 Enter password: Connected. SQL> SQL> show user; USER is "IARSOV" SQL> SQL> select first_name,last_name, hr.f(salary) from hr.employees where email = 'SKING'; FIRST_NAME LAST_NAME HR.F(SALARY) -------------------- ------------------------- ------------ Steven King 0 SQL>
Great, everything works fine.
Now, lets create function based index on SALARY and try the query again.
SQL> create index ss_ix_f on employees(f(salary)); Index created. SQL> SQL> conn iarsov@pdb1 Enter password: Connected. SQL> SQL> show user; USER is "IARSOV" SQL> SQL> select first_name,last_name, hr.f(salary) from hr.employees e where email = 'SKING'; FIRST_NAME LAST_NAME HR.F(SALARY) -------------------- ------------------------- ------------ Steven King 24000 SQL>
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:
SQL> select first_name,last_name, hr.f(salary)
from hr.employees
where email = 'SKING'
group by first_name, last_name, hr.f(salary);
select first_name,last_name, hr.f(salary)
*
ERROR at line 1:
ORA-00979: not a GROUP BY expression
That is not the case if we have defined function-based index or index based on expression on the redacted column.
SQL> select first_name,last_name, hr.f(salary) from hr.employees where email = 'SKING' group by first_name, last_name, hr.f(salary); FIRST_NAME LAST_NAME HR.F(SALARY) -------------------- ------------------------- ------------ Steven King 24000 SQL>
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.
Update:
Data redaction also breaks with virtual columns (no indexes involved): Data Redaction thoughts
Hello Ivica,
Have you tried to check whether the same behavior appears when ANY
function-based index exists, or only if you use the SAME function in the FBI and in your SELECT statement ?
It looks to me that the optimizer may decide to use the FBI to simply retrieve
the calculated value (function result) from the index itself, instead of recalculating
it, and, in such a case, it uses the non-redacted values stored in the index.
In other words, the data retrieval “comes before” applying the redaction.
You can probably check this by displaying the execution plan.
Most probably, the new feature was not “completely implemented” into the optimizer’s behavior as well …
Thanks a lot & Best Regards,
Iudith Mentzel
Oracle Developer
Zim Integrated Shipping Services Ltd.
Haifa, Israel
Hi Iudith,
This behavior occurs only if the same function is used as the one for the index.
I didn’t see any change in the execution plan that would give me some information for this behavior. The index may and should use the original value but the data should be redacted just before the result is returned back to the user/application.
Yes, I would agree that this feature is not completely implemented or it’s some kind of bug …
Regards,
Ivica Arsov
Ivica,
You don’t even need to create the user-defined function:
create index emp_fbi on employees(salary+0);
select …. salary+0 … from employess …
It gives me the value of salaray on my test of 12.1.0.2
Regards
Jonathan Lewis
Jonathan,
If you try the same SQL as inline view it will be redacted.
From the documentation: Inline views are redacted outermost.
SQL> select salary+0 sal from hr.employees where email = ‘SKING’;
SAL
———-
24000
SQL>
SQL> select sal from (select salary+0 sal from hr.employees where email = ‘SKING’);
SAL
———-
0
SQL>
Both statements -> same execution plan.
Regards,
Ivica Arsov
Hi Jonathan,
Yes, that’s also true, that was my first case when I discovered this and later I tried with Function-based indexes.
Also, further tests for function-based indexes showed that only if the function (used for the index) and the index are created by the end user (in this case IARSOV), redaction will take place.
But, if the function or index is created by any other user (also the index it doesn’t have to be the table owner) redaction will break.
example 1 (function and index owner IARSOV2):
Step 1:
SQL> show user;
USER is “IARSOV”
SQL>
SQL> select first_name,last_name, iarsov2.f(salary) from hr.employees where email = ‘SKING’;
FIRST_NAME LAST_NAME IARSOV2.F(SALARY)
——————– ————————- —————–
Steven King 0
SQL>
SQL>
Step 2:
SQL> show user;
USER is “IARSOV2”
SQL>
SQL>
SQL> create index ss_ix on hr.employees(iarsov2.f(salary));
Index created.
SQL>
Step 3:
SQL> show user;
USER is “IARSOV”
SQL> select first_name,last_name, iarsov2.f(salary) from hr.employees where email = ‘SKING’;
FIRST_NAME LAST_NAME IARSOV2.F(SALARY)
——————– ————————- —————–
Steven King 2400
SQL>
example 2 (function owner IARSOV, index owner IARSOV2)
Step 1:
SQL> show user;
USER is “IARSOV”
SQL>
SQL> select first_name,last_name, iarsov.f(salary) from hr.employees where email = ‘SKING’;
FIRST_NAME LAST_NAME IARSOV.F(SALARY)
——————– ————————- —————-
Steven King 0
SQL>
Step 2:
SQL> show user;
USER is “IARSOV2”
SQL>
SQL> create index ss_ix on hr.employees(iarsov.f(salary));
Index created.
SQL>
Step 3:
SQL> show user;
USER is “IARSOV”
SQL> select first_name,last_name, iarsov2.f(salary) from hr.employees where email = ‘SKING’;
FIRST_NAME LAST_NAME IARSOV2.F(SALARY)
——————– ————————- —————–
Steven King 2400
SQL>
Regards,
Ivica Arsov
So we pay for a feature that does not work. Doh!