Oracle 12c Data Redaction

Arup Nanda's OraMag introduction to Oracle's data redaction was the starting point for my investigations, and can be found here.

My observations in playing with the 12c redaction functionality are

DBMS_REDACT is owned by SYS, and so either needs to be run from the SYS account, or execute on DBMS_REDACT needs to be granted to the schema owner applying the policy.
Note that any account granted execute access on DBMS_REDACT will be able to change or disable any redaction policy established on any object in any schema.
Only one redaction policy can be applied to a given schema object. Attempts to apply a second policy will result in ORA-28069: A data redaction policy already exists on this object.
Function based columns can not be redacted, attempts to redact a function based column will result in ORA-28073: The column "FIELD_NAME" has an unsupported data type.
Redaction is effective only for application code only, anyone with SQL access to the database will be able to derive the redacted data with suitably taylored queries. See unredacted data example query below.
Some application code will break. The example I tried was a TO_NUMBER( ) on a VARCHAR2 field (containing only numeric digits). A query using an account seeing the unredacted data worked "as expected", but with an account seeing the redacted data failed ORA-01722: invalid number. As ever, it is important to test any applications which references data you wish to redact.
Note the TO_NUMBER( ) referred to here was in the SELECT list of the query, if the TO_NUMBER( ) is in one of the WHERE clauses, then it works fine. The ORA-01722 is also encountered if the SELECT list is in a WITH block, whether or not it is ultimatey returned.
Implicite conversions work. Given the last point, I was initially a little surprised when an implicite TO_NUMBER worked for the account seeing the redacted data. The query
SELECT r.* FROM rik.redac_test r WHERE r.account_number = 12345678912;
successfully returned the record with account_number '012345678912' (with the account number redacted in the query output).
On closer inspection, I realised that the TO_NUMBER( ) above was in the SELECT list, rather than being part of a clause, as in this example.

The core content of the script used for my games is pasted below. The script below does not include the creation of the app1 account, or the deterministic function on which the function based column age was based.

CREATE TABLE redac_test (
  id                    NUMBER NOT NULL,
  name                  VARCHAR2(10) NOT NULL,
  date_of_birth         DATE NOT NULL,
  account_number        VARCHAR2(12) NOT NULL,
  phone_number          VARCHAR2(11) NOT NULL,
  age                   AS (fn_compute_age(date_of_birth)),
  CONSTRAINT redac_test_pk PRIMARY KEY (id)
);

GRANT SELECT, INSERT, DELETE, UPDATE ON redac_test TO app1, app2;

BEGIN
  DBMS_REDACT.add_policy(object_schema       => 'RIK'
                        ,object_name         => 'REDAC_TEST'
                        ,policy_name         => 'Redaction Test 1'
                        ,expression          => 'USER != ''RIK'''
                        ,column_name         => 'ACCOUNT_NUMBER'
                        ,function_type       => DBMS_REDACT.partial
                        ,function_parameters => 'VVVVVVVVVVVV,VVVVVVVVVVVV,*,1,8'
                        );
  DBMS_REDACT.alter_policy(object_schema       => 'RIK'
                          ,object_name         => 'REDAC_TEST'
                          ,policy_name         => 'Redaction Test 1'
                          ,action              => DBMS_REDACT.add_column
                          ,column_name         => 'PHONE_NUMBER'
                          ,function_type       => DBMS_REDACT.partial
                          ,function_parameters => 'VVVVVVVVVVVV,VVVVVVVVVVVV,*,4,8'
                          );
  DBMS_REDACT.alter_policy(object_schema       => 'RIK'
                          ,object_name         => 'REDAC_TEST'
                          ,policy_name         => 'Redaction Test 1'
                          ,action              => DBMS_REDACT.add_column
                          ,column_name         => 'DATE_OF_BIRTH'
                          ,function_type       => DBMS_REDACT.partial
                          ,function_parameters => 'MDy1970'
                          );
  DBMS_REDACT.alter_policy(object_schema       => 'RIK'
                          ,object_name         => 'REDAC_TEST'
                          ,policy_name         => 'Redaction Test 1'
                          ,action              => DBMS_REDACT.add_column
                          ,column_name         => 'ID'
                          ,function_type       => DBMS_REDACT.random
                          );
END;
/

The above has the following effect on SELECT * FROM rik.redac_test; from one of the APP1 or APP2 accounts:

        ID NAME       DATE_OF_B ACCOUNT_NUMB PHONE_NUMBE        AGE
---------- ---------- --------- ------------ ----------- ----------
         7 Joe        12-JAN-70 ********8912 079*****643         21
         1 Harry      29-FEB-70 ********9123 079*****642         28

Unredacting data

The example query below shows how easy it is to work around data redaction if one is able to use SQL, rather than an existing application code base, to access the tables with the redacted data in.

WITH digit AS (
  SELECT TO_CHAR(rownum - 1) AS num
  FROM   all_objects
  WHERE  rownum <= 10
)
SELECT id, name, account_number,
       a.num || b.num || c.num || d.num || e.num || f.num || g.num || h.num ||
       SUBSTR(account_number, 9, 4) AS generated_account
FROM   rik.redac_test
JOIN   digit a ON a.num = SUBSTR(account_number, 1, 1)
JOIN   digit b ON b.num = SUBSTR(account_number, 2, 1)
JOIN   digit c ON c.num = SUBSTR(account_number, 3, 1)
JOIN   digit d ON d.num = SUBSTR(account_number, 4, 1)
JOIN   digit e ON e.num = SUBSTR(account_number, 5, 1)
JOIN   digit f ON f.num = SUBSTR(account_number, 6, 1)
JOIN   digit g ON g.num = SUBSTR(account_number, 7, 1)
JOIN   digit h ON h.num = SUBSTR(account_number, 8, 1)
ORDER BY id
/

        ID NAME       ACCOUNT_NUMB GENERATED_ACCOUNT
---------- ---------- ------------ -----------------
         7 Joe        ********8912 012345678912
         5 Harry      ********9123 123456789123