Three Tier Oracle Security in London ~ Paul M. Wright

(nix, oracle, java, www, cloud ) intersect (safety, security, reliability, integrity)

Three Tier Oracle Security in London ~ Paul M. Wright RSS Feed
 

DB Data Forensics

There has been a lot of good work carried out recently on Database forensics approached from the OS, particularly using reverse engineering techniques to understand the format of metadata in the datafiles such as timestamps . However it is commonly found in practice that OS level access is denied to the DB team in order to enforce segregation of duty. Additionally the Terrabyte datafile size of many commercial enterprise DBs makes Datafile analysis impractical. The impact of this is that DB level methods of verifying the state of an object or dataset are often more useful.
For instance Timestamps within the DB are easily accessible via ALL_OBJECTS and provide some level of DB based integrity checking using the created and last compile time. Problem is that a user can set the timestamp of an object back in time quite easily. For instance:

SQL> set serveroutput on
SQL> create or replace procedure time_test as
2 timevar varchar2(20);
3 begin
4 select sysdate into timevar from dual;
5 dbms_output.put_line(timevar);
6 end;
7 /
Procedure created.
SQL> exec time_test;
18-JAN-09
PL/SQL procedure successfully completed.
SQL> alter procedure time_test compile timestamp '1066-11-11:12:0:59';
Procedure altered.
SQL> select timestamp from User_objects where object_name='TIME_TEST';
TIMESTAMP
-------------------
1066-11-11:12:00:59

The above is a good reason for using a checksum method of verifying the integrity object source code e.g. this code adapted from newly reprinted Oracle Forensics Book, creates checksums of oracle triggers.

set wrap off
set linesize 400
set serveroutput on
CREATE OR REPLACE PROCEDURE SHA1DBTRIGGERSTATECHECKER(lvschema in varchar2) AS TYPE C_TYPE IS REF CURSOR;
CV C_TYPE;
string varchar2(32767);
l_hash raw(2000);
lvname VARCHAR2(30);
lvtype varchar2(30) :='TRIGGER';
begin
OPEN CV FOR 'SELECT DISTINCT OBJECT_NAME FROM SYS.ALL_OBJECTS WHERE OBJECT_TYPE=''TRIGGER'' AND OWNER = :x' using lvschema;
LOOP
FETCH CV INTO lvname;
DBMS_OUTPUT.ENABLE(200000);
l_hash:=dbms_crypto.hash(dbms_metadata.get_ddl(lvtype, lvname, lvschema), 3);
dbms_output.put_line(l_hash||' ~ '||lvname);
EXIT WHEN CV%NOTFOUND;
END LOOP;
CLOSE CV;
end;
/

Which when ran gives a report as follows:

SQL> exec SHA1DBTRIGGERSTATECHECKER('SYS');
B312355402E68C3774A5AA9924DDFAA34DBFEB39 ~ AURORA$SERVER$SHUTDOWN
98A197D536C0E980E69BE7F4AACF6BA8AF16C185 ~ AURORA$SERVER$STARTUP
1A754A605EAFF286019E63523341552ECD566D23 ~ AW_DROP_TRG
4A745424A0F74535FBB8071492E08716FD472B34 ~ CDC_ALTER_CTABLE_BEFORE
04B324FB25F554912E00C900601FC927983D61BB ~ CDC_CREATE_CTABLE_AFTER
9713B54BB1C32460187701B943118741D659B2BD ~ CDC_CREATE_CTABLE_BEFORE
2EEB4B0E86F503127850EA09ABB9F5EA6A2D8C6D ~ CDC_DROP_CTABLE_BEFORE
9EA99FDBF89486184E14CC5B8A3522C8BBB9C0A2 ~ NO_VM_ALTER
D7290D62A24C97BC9B54213F0A8E026D883CF8EC ~ NO_VM_CREATE
D87B235B866C59D011B3BB8155A3BC60A372144E ~ NO_VM_DROP
01C69F6F073D542B53A96D9A40971D3FDCF5C64F ~ OLAPISHUTDOWNTRIGGER
C59C0EE44E255744DDF757CC4A8576AD6E8AF556 ~ OLAPISTARTUPTRIGGER
C59C0EE44E255744DDF757CC4A8576AD6E8AF556 ~ OLAPISTARTUPTRIGGER

The logical extension of the above concept would be a Tripwire-like monitoring product for DB objects…but how can the same ideas be applied to the data contained within the DB i.e the actual data that the DB is designed to contain. The data is the crown jewels that will be the target of an attacker. A forensic examiner should be able to confirm the integrity of evidential data at the DB level. Additionally DBAs would like to be able to statecheck static read-only tables over time. This could be attempted by converting the SCN recorded for the last update of each row in a table to a timestamp as follows:

SELECT SCN_TO_TIMESTAMP( ORA_ROWSCN ) FROM table_name;

However the SCN to timestamp relationship is not direct and can be changed, for instance when a client DB connects to a server DB via a dblink the SCN of the DB with the lowest SCN will become the same as the SCN on the DB with the higher SCN. (Incidentally this fact could cause a denial of service if the client DB had an SCN close the maximum)..

So timestamp/SCNs are not perfect for verifying integrity ~ what is needed is statechecking for evidential data itself? This is the realm of DB Data Forensics.

Ok ~ so the aim in this example is to check that the evidential data in a table has not changed from a previously known state (I have used dual here for convenience so replace dual with the table to be statechecked).

–1.Select the checksum for the dataset returned by the required query.

select DBMS_SQLHASH.gethash('select 1 from dual', 2) from dual;
SQL> select DBMS_SQLHASH.gethash('select 1 from dual', 2) from dual;
DBMS_SQLHASH.GETHASH('SELECT1FROMDUAL',2)
--------------------------------------------------------------------------------
9D9DFF9320E27082B15B4ED7A086BA83

–2.Compare that known checksum to the current checksum for the same dataset

(select utl_raw.cast_to_raw(DBMS_SQLHASH.gethash('select 1 from dual',2)) from dual)intersect
(select utl_raw.cast_to_raw('9D9DFF9320E27082B15B4ED7A086BA83') from dual);
SQL> (select utl_raw.cast_to_raw(DBMS_SQLHASH.gethash('select 1 from
dual', 2)) from dual)intersect
2 (select utl_raw.cast_to_raw('9D9DFF9320E27082B15B4ED7A086BA83') from dual);
UTL_RAW.CAST_TO_RAW(DBMS_SQLHASH.GETHASH('SELECT1FROMDUAL',2))
--------------------------------------------------------------------------------
3944394446463933323045323730383242313542344544374130383642413833

-If a result set is returned (as above) then the state of the table data is the same as the previous known checksum.
-If “no rows” are selected then the state of the table’s data has changed from that represented by the previous known checksum (9D9DFF9320E27082B15B4ED7A086BA83).

This is a great feature of Oracle, though remember that this query has a high performance hit but the performance hit can be decreased by changing the last argument of DBMS_SQLHASH.GETHASH to 1 (md4) and security increased by raising to 3 (sha1).
Only problem is by default DBMS_SQLHASH is only accessible to the SYSDBA. Another SYSDBA-only feature to add to the others. Personally I think the ability to statecheck data in the DB should be open to more users as the ability to verify the integrity of data has wide appeal. Of additional relevance to DB Data Forensics is the tracking of that data once it has left the DB and is “mobile”. Solutions such as Fidelis are aimed at this growing DLP market. Of course Fidelis cannot read SSH and does not understand TNS so Hedgehog has an advantage. This is especially true given that Oracle Audit has been shown to be bypassable by this interesting example. http://blog.red-database-security.com/2009/01/16/proof-of-concept-how-to-bypass-oracle-auditing-using-dbms_ijob/
….Perhaps we will soon be celebrating the Chinese Year of the Hedgehog? Though the problem of securing sensitive data once it has left the DB is still in process..

Seperate point – I noticed an interesting post on collecting evidence regarding the activity of a Toad user at the excellent SANS Forensic blog.
http://sansforensics.wordpress.com/2009/01/13/oracle-forensics-toad-from-quest-software/

Also a tool named Fuzzor, for Fuzzing Oracle PL/SQL input, has been released by Slavik at his blog which often has new material of interest.

There are quite a few exploits since the January 2009 CPU. I still have bugs outstanding so there is still work to be done..especially with regards to DB Data Forensics..

Cheers,
Paul

2 Responses to “DB Data Forensics”

  1. 1
    Paul Wright:

    Thanks for the interesting update Danny. I have made a note to revisit Fidelis’ feature set in future. It would be interesting to know what versions of TNS FIdelis supports as I have had experience of IDS/IPS not implementing TNSv9 (10g) properly as it had not updated from TNSv8 (9i)?

  2. 2
    khair:

    can we use also the same logic for package bodies, function or procedures. We are using tripwire but we are not satisfied from the operation point of view

    Thank you
    khair

Leave a Reply

You must be logged in to post a comment.