Using "Packagestate" Queries

Oracle Forensics tips by Paul Wright

Checksums of object source code using "packagestate"queries

When transferring the source code tables content from victim server to collection server it is imperative to checksum the source of the objects before and after.
This procedure below will use the MD5 algorithm to create a checksum for the source code of a PLSQL object such as a view.

dbms_obfuscation_toolkit.md5.sql

set wrap off
set linesize 400
set serveroutput on

create or replace function md5checksum(lvtype in varchar2,lvname in varchar2,lvschema in varchar2) return varchar2

string varchar2(32767);
checksum varchar2(16);

begin

   string:=dbms_metadata.get_ddl(lvtype, lvname, lvschema);
   dbms_obfuscation_toolkit.md5(input_string => string, checksum_string=>
checksum);
   return checksum;
end;
/

md5checksum.sql Create a hash for a particular view

col objectname for a20
col md5sum for a40

select object_name name, utl_raw.cast_to_raw(md5checksum(object_type,object_name,owner)) md5sum from dba_objects

where owner='SYS'
and object_type ='VIEW'
and object_name='DBA_USERS';

Or run it on all the views of a schema.

col objectname for a20
col md5sum for a40

select object_name name, utl_raw.cast_to_raw(md5checksum(object_type,object_name,owner)) md5sum from dba_objects

where owner='SYS'
and object_type ='VIEW';

You could run the MD5SUM routine on all the objects in a schema or DB. The problem with doing this is that it will take a long time due to the complexity of the algorithm and also DBMS_OBFUSCATION will only take code upto 32k so may error on large input.

If you wish to use code that is quicker for all objects in a schema then use the DBMS_UTILITY queries listed below and in Chapters 11-13. DBMS_UTILITY is quicker than MD5 as it uses a weaker checksumming algorithm.

For example:

dbms_utility.get_hash_value.sql

set wrap off
set linesize 400
set serveroutput on

DECLARE

long_var LONG;

BEGIN

select sys.view$.text into long_var from sys.view$ left outer join

sys.obj$ on sys.view$.obj# = sys.obj$.obj# where sys.obj$.name='DBA_USERS';

if dbms_utility.get_hash_value(long_var,1000000000,power(2,30)) = 1958803667

then DBMS_OUTPUT.PUT_LINE('The checksum for dba_users is correct');
else
DBMS_OUTPUT.PUT_LINE('The checksum for dba_users is not correct');
end if;
end;
/

For automated collection of many PLSQL package checksums use AutoforenpackagestateImproved.sql in Chapter 11 titled ?Ascertaining Vulnerability status in the DB independent of reported patch level?.

To run this query remotely from the collection server using a trusted DBMS_UTILITY use the dblink version in the same Chapter autoforenpackDBlink.sql . For trigger checksums use triggerforensicstate.sql again in the same chapter further on. For view checksumming use Automatedforensicviewstatecheck.sql in Chapter 13.

Working up to date dbstatechecker code is always available from

http://www.oracleforensics.com/dbstatechecker.sql

However the problem with DBMS_UTILITY.GET_HASH_VALUE is that it uses a weak proprietory checksumming algorithm and so should not be used for forensics work where deliberate malicious activity is suspected. Additionally DBMS_UTILITY will provide differing results between Oracle 7 and Oracle 8,9,10. DBMS_OBFUSCATION.MD5 has the minimum requirements for a verifiable checksum and always returns the same checksum for the same input on all versions of Oracle upon which it is installed. Mike Hordila of DBActions Inc., www.dbactions.com found that DBMS_OBFUSCATION_TOOLKIT.MD5 is capable of returning a hash of value 2^128, whilst DBMS_UTILITY.GET_HASH_VALUE can only return a hash of (2^31)-1

This is an implementation of MD5 using DBMS_OBFUSCATION_TOOLKIT

dbms_obfuscation_toolkit.md5.auto.sql

set wrap off
set linesize 400
set serveroutput on

create or replace function md5checksum(lvtype in varchar2,lvname in varchar2,lvschema in varchar2) return varchar2

string varchar2(32767);
checksum varchar2(16);

begin

   string:=dbms_metadata.get_ddl(lvtype, lvname, lvschema);
   dbms_obfuscation_toolkit.md5(input_string => string, checksum_string=>
checksum);

   return checksum;

end;

SQL> create or replace function md5checksum(lvtype in varchar2,lvname in varchar2,lvschema in varchar2) return varchar2

2 is
3     string varchar2(32767);
4     checksum varchar2(16);
5 begin
6     string:=dbms_metadata.get_ddl(lvtype, lvname, lvschema);
7     dbms_obfuscation_toolkit.md5(input_string => string,
checksum_string=> checksum);
8     return checksum;
9 end;
10 /

Function created.

SQL> col objectname for a20
SQL> col md5sum for a40

SQL> select object_name name, utl_raw.cast_to_raw(md5checksum(object_type,object_name,owner)) md5sum from dba_objects

2 where owner='SYS'
3 and object_type ='VIEW'
4 and object_name='DBA_USERS';

NAME
---------------------------------------------
MD5SUM
----------------------------------------
DBA_USERS
BFFD01780BC3504B6091A89D5BEBC6FB

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