 |
|
Automated Checksum Collection
for PLSQL
Oracle Forensics tips by Paul Wright
|
Automated checksum collection for
PLSQL packages using SHA1:
SHA1DBPACKAGESTATECHECKER.sql
--this
query will run from the victim server
set wrap off
set linesize 400
set serveroutput on
CREATE OR
REPLACE PROCEDURE SHA1DBPACKAGESTATECHECKER(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) :='PACKAGE';
begin
OPEN CV FOR 'SELECT DISTINCT OBJECT_NAME FROM SYS.ALL_OBJECTS WHERE
OBJECT_TYPE=''PACKAGE'' 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;
/
SQL> spool
\mnt\usbdatadrive\sha1packagestate.sql
SQL> EXEC SHA1DBPACKAGESTATECHECKER('SYS');
SQL> spool off
--then
afterwards reinstate the checksums in a table on the collection
server.
CREATE
TABLE SHA1PACKAGESTATES(SHA1SCHEMA VARCHAR2(40), SHA1NAME
VARCHAR2(40), SHA1CHECKSUM VARCHAR2(40));
SQL>
@sha1packagestate.sql
1 row
created.
1 row
created.
The query should be ran dumping the
results to SQL*PLUS which can be spooled off on the victim server to
the evidence data drive.
This is an implementation of SHA1
automated checksum collection for VIEWS on the collection server
after the source has been copied over.
SHA1DBVIEWSTATECHECKER.sql
--this is
the collection server side query
set wrap off
set linesize 400
set serveroutput on
DROP TABLE
SHA1VIEWSTATES
CREATE
TABLE SHA1VIEWSTATES(SHA1SCHEMA VARCHAR2(40), SHA1NAME VARCHAR2(40),
SHA1CHECKSUM VARCHAR2(40));
CREATE OR
REPLACE PROCEDURE SHA1DBVIEWSTATECHECKER(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) :='VIEW';
begin
OPEN CV FOR 'SELECT DISTINCT OBJECT_NAME FROM SYS.DBA_OBJECTS WHERE
OBJECT_TYPE=''VIEW'' 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 SHA1VIEWSTATES values(lvschema, lvname, l_hash);
EXIT WHEN CV%NOTFOUND;
END LOOP;
CLOSE CV;
end;
/
EXEC
SHA1DBVIEWSTATECHECKER('SYS');
SELECT * FROM SHA1VIEWSTATES;
Later on, when the source tables have
been copied over using the SQL*PLUS COPY command, the checksums can
be calculated on the collection server and compared to those on the
victim server using this type of query. This will find the
combination of differences. If both tables are identical there
should be no resultset but need to check as always.
(((select *
from SHA1PACKAGESTATEVIEWS)minus
(select * from SHA1PACKAGESTATEVIEWSNEW))UNION
((select * from SHA1PACKAGESTATEVIEWSNEW)minus
(select * from SHA1PACKAGESTATEVIEWS)))
This is an excerpt from the book "Oracle
Forensics: Oracle Security Best Practices", by Paul M. Wright,
the father of Oracle Forensics.
