Database Reference
In-Depth Information
3 begin
4 select sysdate into timevar from dual;
5 dbms_output.put_line(timevar);
6 end;
7 /
Procedure created.
SQL> exec time_test;
18-JAN-09
PL/SQL procedure successfully completed.
SQL> alter procedure time_test compile timestamp '1066-11-11:12:0:59';
Procedure altered.
SQL> select timestamp from User_objects where object_name='TIME_TEST';
TIMESTAMP
-------------------
1066-11-11:12:00:59
One saving grace for schema accounts is that SYS will be able to identify this tampering by checksumming the
packages in that schema and comparing them to a known good. Here is code for checksumming all the triggers owned
by a given account:
set wrap off
set linesize 400
set serveroutput on
CREATE OR REPLACE PROCEDURE SHA1DBTRIGGERSTATECHECKER(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) :='TRIGGER';
begin
OPEN CV FOR 'SELECT DISTINCT OBJECT_NAME FROM SYS.ALL_OBJECTS WHERE OBJECT_TYPE=''TRIGGER'' 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), 3);
dbms_output.put_line(l_hash||' ~ '||lvname);
EXIT WHEN CV%NOTFOUND;
END LOOP;
CLOSE CV;
end;
/
SQL> exec SHA1DBTRIGGERSTATECHECKER('SYS');
B312355402E68C3774A5AA9924DDFAA34DBFEB39 ~ AURORA$SERVER$SHUTDOWN
98A197D536C0E980E69BE7F4AACF6BA8AF16C185 ~ AURORA$SERVER$STARTUP
1A754A605EAFF286019E63523341552ECD566D23 ~ AW_DROP_TRG
4A745424A0F74535FBB8071492E08716FD472B34 ~ CDC_ALTER_CTABLE_BEFORE
04B324FB25F554912E00C900601FC927983D61BB ~ CDC_CREATE_CTABLE_AFTER
9713B54BB1C32460187701B943118741D659B2BD ~ CDC_CREATE_CTABLE_BEFORE
Search WWH ::




Custom Search