Database Reference
In-Depth Information
Integrity State-Checking of Database Objects
Many objects in the database do not need to change their content, especially code objects like packages. If they
have changed state it may be because they have been tampered with to provide unauthorized backdoor access. It is
therefore a good idea to check the state of important objects. Here is a simple example of a dictionary state check:
SQL> SELECT AVG(dbms_utility.get_hash_value(text,1000000000,power(2,30))) FROM DBA_SOURCE WHERE
OWNER='SYS';
AVG(DBMS_UTILITY.GET_HASH_VALUE(TEXT,1000000000,POWER(2,30)))
————————————————————-
1564889684
We can carry out a forensic query without having to rely on views by using the base tables. For example:
SELECT sys.obj$.owner#, sys.obj$.NAME, sys.source$.obj#, ctime, mtime, stime,
AVG(dbms_utility.get_hash_value(source,1000000000,power(2,30)))
from sys.source$ inner join sys.obj$
ON sys.source$.obj#=sys.obj$.obj#
where sys.source$.obj# = 887
GROUP BY sys.obj$.owner#, sys.source$.obj#,ctime, mtime, stime,sys.obj$.NAME;
Building on this approach, we can checksum a given user's views using a more advanced SHA-1 checksum
algorithm from the
dbms_crypto
package. Just change the following
lvtype
parameter to whichever object type you
need to state-check for the given owner. Obviously, the more objects there are, the longer it will take.
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;
