Data Redaction and Virtual Columns

Prior to 12.2, data redaction was unsafe if used in conjunction with virtual columns. In fact, it was possible to select virtual column data even for the virtual columns based on a redacted column. Fortunately, this security breach was fixed in 12.2. Now, the error “ORA-28081: Insufficient privileges – the command references a redacted object” gets raised when selecting from a redacted table.

Ivica Arsov described this behaviour in his blog post.

However, the current implementation seems to impose some unexpected restrictions. I mean the error is being generated even if we select a column which is not redacted, on condition that a virtual column which is derived from a redacted column is defined in the table. Let me demonstrate this point with a simple test case which I ran on a 12.2.0.1.180116 database.

Firstly, I’m going to create the table O.T containing the virtual column N3 which depends on N1. It’s worth noting that the column N1 is redacted for everyone.

create user o identified by Password$111 ;
grant create session,resource, unlimited tablespace to o ;
create table o.t (
  n1 number, n2 number, n3 number generated always as (n1+1) virtual 
) ;

BEGIN
DBMS_REDACT.add_policy(
  object_schema => 'O',
  object_name => 'T',
  column_name => 'N1',
  policy_name => 'redact_pol',
  function_type => DBMS_REDACT.full,
  expression => '1=1'
);
END;
/

create user u identified by Password$111 ;
grant create session to u ;
grant select on o.t to u ;

Then, I’m going to select the column N2 which is neither redacted nor virtual nor referenced by any virtual column.

connect u/Password$111
select n2 from o.t ;

ERROR at line 1:
ORA-28081: Insufficient privileges - the command references a redacted object.

As you can see, the error was raised to protect the data, although we didn’t try to query any redacted information. In other words, a defined virtual column derived from a redacted column will unnecessarily cause the error, regardless of whether we reference it in the select command.

Consequently, the select statement above will also keep failing as long as there are some extended statistics on a redacted column.