Database Reference
In-Depth Information
CLOSE CV;
end;
/
EXEC SHA1DBVIEWSTATECHECKER('SYS');
SELECT * FROM SHA1VIEWSTATES;
By combining the above concepts we can create a package state checker that uses base tables. This is useful to
do before and after a patch to see what has been changed in the DB as a result of the application of the patch. It can
also be done after a suspected incident to see if sensitive objects have been changed in an unauthorized fashion.
PACKAGESTATEPRO
will state-check a given owner's packages as follows:
create table PACKAGESTATESNEW(OWNERIN VARCHAR2(30),USER$NAME VARCHAR2(30),OBJ$OWNER VARCHAR2(30),
NAMEIN VARCHAR2(30),
SOURCE$OBJID NUMBER,
OBJ$TYPE VARCHAR2(30),
COUNTOUT NUMBER,
CTIMEOUT TIMESTAMP,
STIMEOUT TIMESTAMP,
LASTDDLOUT TIMESTAMP,
HASH NUMBER);
CREATE OR REPLACE PROCEDURE PACKAGESTATEPRO (OWNERIN VARCHAR2) AS TYPE C_TYPE IS REF CURSOR;
CV C_TYPE;
USER$NAME VARCHAR2(30);
OBJ$OWNER VARCHAR2(30);
NAMEIN VARCHAR2(30);
SOURCE$OBJID NUMBER;
OBJ$TYPE VARCHAR2(30);
COUNTOUT NUMBER;
CTIMEOUT TIMESTAMP;
STIMEOUT TIMESTAMP;
LASTDDLOUT TIMESTAMP;
HASH NUMBER;
BEGIN
OPEN CV FOR 'SELECT sys.user$.NAME , sys.obj$.owner#, sys.obj$.NAME, sys.source$.obj#, sys.OBJ$.
TYPE#, Count(sys.source$.line), ctime, stime, mtime from (sys.source$ join sys.obj$
ON sys.source$.obj#=sys.obj$.obj#)
inner join sys.user$ ON sys.obj$.owner# = sys.user$.user#
where sys.obj$.TYPE#=11
And sys.user$.NAME = :x GROUP BY sys.user$.NAME, sys.obj$.owner#, sys.obj$.NAME, sys.source$.obj#,
sys.OBJ$.TYPE#, ctime, stime, mtime' using OWNERIN;
LOOP
FETCH CV INTO USER$NAME, OBJ$OWNER, NAMEIN, SOURCE$OBJID, OBJ$TYPE, COUNTOUT, CTIMEOUT, STIMEOUT,
LASTDDLOUT;
DBMS_OUTPUT.ENABLE(200000);
SELECT SUM(dbms_utility.get_hash_value(text,1000000000,power(2,30))) INTO HASH from dba_source
where name = NAMEIN and owner = OWNERIN;
DBMS_OUTPUT.PUT_LINE(OWNERIN||','||USER$NAME||','||OBJ$OWNER||','||NAMEIN||','||SOURCE$OBJID||','
||OBJ$TYPE||','||COUNTOUT||','||CTIMEOUT||','||STIMEOUT||','||LASTDDLOUT||','||HASH);
