Function Based Indexes and Data Redaction

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

 

 

Regards,
Ivica Arsov

6 thoughts on “Function Based Indexes and Data Redaction

  1. 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

  2. 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

  3. 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

  4. 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

Leave a Reply