dbms_sqlhash tips

You can use the dbms_sqlhash procedure to see if the result set for a SQL statements has changed and dbms_sqlhash can also be used to check data integrity.

The dbms_sqlhash procedure is created by running dbmsobtk.sql at $ORACLE_HOME/rdbms/admin/dbmsobtk.sql.

dbms_sqlhash.gethash(
   sqltext     in varchar2,                
   digest_type in binary_integer,          
   chunk_size  in number default 134217728)
return raw;

The Oracle 10g security guide notes the arguments to dbms_sqlhash:

The docs also note the general usage for dbms_sqlhash:

'the DBMS_SQLHASH package can check data integrity by making use of hash algorithms. It provides an interface to generate the hash value of the result set returned by a SQL query.

Hash values are like data fingerprints and are used to ensure data integrity. DBMS_SQLHASH provides support for several industry standard hashing algorithms, including MD4, MD5, and SHA-1 cryptographic hashes . .

DBMS_SQLHASH includes the GETHASH function that is used to retrieve the hash value of a query result set. The GETHASH function runs one of the supported cryptographic hash algorithms against the result set of the SQL statement to arrive at a hash value.

You can compare hash values to check whether data has been altered. For example, before storing data, Laura runs the DBMS_SQLHASH.GETHASH function against the SQL statement to create a hash value of the SQL result set.

When she retrieves the stored data at a later date, she reruns the hash function against the SQL statement using the same algorithm. If the second hash value is identical to the first one, then data has not been altered. Any modification to the result set data would cause the hash value to be different.?

Pete Finnigan notes that dbms_sqlhash can also check data integrity, to "test the integrity of dictionary objects in a similar fashion to some of the commercial database scanners":

?[The dbms_sqlhash package] is an interesting package that allows the use of cryptographic hashes such as HASH_MD4, HASH_MD5, or HASH_SH1 (From DBMS_CRYPTO) to hash the result set of a SQL statement to allow the checking of data integrity.

This allows data to be checked to see if it has been changed.

The package with the function GETHASH can also be used to test the integrity of dictionary objects in a similar fashion to some of the commercial database scanners that are available.

The package can of course be used to select the source of packages, triggers, views and more and hashes can be calculated and stored for later comparison.?

If you like Oracle tuning, you might enjoy my book "Oracle Tuning: The Definitive Reference", with 950 pages of tuning tips and scripts.  You can buy it direct from the publisher for 30%-off and get instant access to the code depot of Oracle tuning scripts.