Using a Collection Database to Collect Database Evidence Oracle Forensics tips by Paul Wright

 

The point of this query is to make sure that the evidence we have collected has not changed in transfer. It is better to collect all DB evidence to a collection DB as it can be sorted and analyzed more easily and in a manner consistent with its nature i.e. it is Oracle DB data therefore it should be collected into an Oracle DB for analysis. One could use a dblink to do cross database checksumming like the one below: 

--CREATE A DBLINK NAME VICTIMDBLINK POINTING FROM THE COLLECTION DB TO THE VICTIM DB eg..

create database link VICTIMDBLINK connect to system identified by manager using '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.1.1.167)(PORT=1521))(CONNECT_DATA=
(SERVICE_NAME=ORCL)))';

However in the interests of keeping it simple we shall proceed as we are with Triggers. The first query below is on the victim using SQL*PLUS spooling and second on the collection server to a table ready for comparison of the two checksums there.

SHA1DBTRIGGERSTATECHECKER.sql

--victim server
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),
dbms_crypto.hash_sh1);
dbms_output.put_line('insert into SHA1PACKAGESTATES
values('''||lvschema||''','''||lvname||''','''||l_hash||''');'); 
 EXIT WHEN CV%NOTFOUND;
 END LOOP;
 CLOSE CV;
end;
/

spool /mnt/usbdatadrive/sha1sysviews.txt
SQL> exec sha1dbtriggerstatechecker('SYS');
insert into SHA1PACKAGESTATES
values('SYS','AURORA$SERVER$SHUTDOWN','B312355402E68C3774A5AA9924DDFAA34DBFEB39');
insert into SHA1PACKAGESTATES
values('SYS','OLAPISTARTUPTRIGGER','6DCE3FC93CCB7E250DD385033AFDC9F79DDDE31B');
insert into SHA1PACKAGESTATES
values('SYS','AURORA$SERVER$STARTUP','98A197D536C0E980E69BE7F4AACF6BA8AF16C185');
insert into SHA1PACKAGESTATES
values('SYS','NO_VM_DROP_A','3CC74015384089057665A4A4112DEEE947F6FD1A');
spool off
--then the same source copied over to the collection server is checksummed there
directly --into a table for comparison with the above output.

CREATE TABLE SHA1PACKAGESTATETRIGGERSNEW(SHA1SCHEMA VARCHAR2(40), SHA1NAME
VARCHAR2(40), SHA1CHECKSUM VARCHAR2(40));
create or replace procedure sha1dbtriggerstatecheckernew(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@VICTIMDBLINK
 WHERE OBJECT_TYPE=''TRIGGER'' AND OWNER = :x' using lvschema;
   LOOP
   FETCH CV INTO lvname;
   DBMS_OUTPUT.ENABLE(200000);
   string:=dbms_metadata.get_ddl(lvtype, lvname, lvschema);
   l_hash:=dbms_crypto.hash(UTL_I18N.STRING_TO_RAW(string,'AL32UTF8'),
dbms_crypto.hash_sh1);
   dbms_output.put_line('HashSHA1='||l_hash||' Name='||lvschema||'.'||lvname);
   insert into SHA1PACKAGESTATETRIGGERSNEW values(lvschema, lvname, l_hash);
   EXIT WHEN CV%NOTFOUND;
 END LOOP;
 CLOSE CV;
end;
/

select * from SHA1PACKAGESTATETRIGGERS;
….

--to compare two checksum profiles can use a minus query like one below to find
combination of differences. 

(((select * from SHA1PACKAGESTATETRIGGERS)minus
(select * from SHA1PACKAGESTATETRIGGERSNEW))UNION
((select * from SHA1PACKAGESTATETRIGGERSNEW)minus
(select * from SHA1PACKAGESTATETRIGGERS))) 

--If both are identical there should be no resultset but need to check as always..

Then the same type of check for Java Source integrity:

SHA1DBJAVASTATECHECKER.sql

DROP TABLE SHA1JAVASTATES
CREATE TABLE SHA1JAVASTATES(SHA1SCHEMA VARCHAR2(40), SHA1NAME VARCHAR2(40),
SHA1CHECKSUM VARCHAR2(40));
CREATE OR REPLACE PROCEDURE SHA1DBJAVASTATECHECKER(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) :='JAVA_SOURCE';
begin
   OPEN CV FOR 'SELECT DISTINCT OBJECT_NAME FROM SYS.DBA_OBJECTS WHERE
OBJECT_TYPE=''JAVA SOURCE'' 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),
dbms_crypto.hash_sh1);
   dbms_output.put_line('HashSHA1='||l_hash||' Name='||lvschema||'.'||lvname);
   insert into SHA1JAVASTATES values(lvschema, lvname, l_hash);
   EXIT WHEN CV%NOTFOUND;
 END LOOP;
 CLOSE CV;
end;
/
EXEC SHA1DBJAVASTATECHECKER('SYSTEM');
SELECT * FROM SHA1JAVASTATES; 

SQL> CREATE OR REPLACE PROCEDURE SHA1DBJAVASTATECHECKER(lvschema in varchar2) AS
TYPE C_TYPE IS REF CURSOR;
  2  CV C_TYPE;
  3     string varchar2(32767);
  4     l_hash raw(2000);
  5     lvname VARCHAR2(30);
  6     lvtype varchar2(30) :='JAVA_SOURCE';
  7  begin
  8     OPEN CV FOR 'SELECT DISTINCT OBJECT_NAME FROM SYS.DBA_OBJECTS WHERE
OBJECT_TYPE=''JAVA SOURCE'' AND OWNER = :x' using lvschema;
  9     LOOP
 10     FETCH CV INTO lvname;
 11     DBMS_OUTPUT.ENABLE(200000);
 12     l_hash:=dbms_crypto.hash(dbms_metadata.get_ddl(lvtype, lvname, lvschema),
dbms_crypto.hash_sh1);
 13     dbms_output.put_line('HashSHA1='||l_hash||'
Name='||lvschema||'.'||lvname);
 14     insert into SHA1JAVASTATES values(lvschema, lvname, l_hash);
 15     EXIT WHEN CV%NOTFOUND;
 16   END LOOP;
 17   CLOSE CV;
 18  end;
 19  /
Procedure created.
SQL> EXEC SHA1DBJAVASTATECHECKER('SYSTEM');
HashSHA1=FD4415AEC630B46F19909E09D5258CB1B71E4D1D Name=SYSTEM.JAVAREADBINFILE
HashSHA1=FD4415AEC630B46F19909E09D5258CB1B71E4D1D Name=SYSTEM.JAVAREADBINFILE

 

This is an excerpt from the book "Oracle Forensics: Oracle Security Best Practices", by Paul M. Wright, the father of Oracle Forensics.