NEED audit script ...
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 2 of 2

Thread: NEED audit script ...

  1. #1
    Join Date
    Nov 2000
    Posts
    89
    Oracle people:

    Could someone give me a hand and show me a script that shows who has made changes(new table entries or other changes) to the DB without having to startup the whole "audit" system ?

    I've tried a script from my not so handy oracle book- doesn't work and I've failed to hack it into shape :-(

    Here it is:

    -----
    col Owner format a20
    col Object_Name format a30
    col Timestamp format a20
    select Owner, Object_Name, Object_Type, Status, Timestamp
    from DBA_OBJECTS
    where SUBSTR(Timestamp,1,10) = TO_CHAR(sysdate-1,'YYYY-MM-DD')
    order by Owner, Object_Name
    /

    ------

    Thanks !

    Roger

  2. #2
    Join Date
    Nov 2000
    Posts
    89
    Oracle People:

    Sometimes it's good not to get an answer :-) as I've been forced to dig a bit deeper into Oracle.

    I can't use logminer and I can't use auditing so I've created the following trigger as a sort of low budget auditing:

    TRIGGER STYLE AUDITS FOR ORACLE


    First create a table that will contain the audit information:

    SQL> create table amy.aud (

    ID varchar2(20),
    TIME varchar2(20),
    TERM varchar2(20),
    TYPE varchar2(20));

    Now create a trigger script:

    ----------------------------------------
    create or replace trigger trig1
    after insert or update on AMY.artist
    for each row
    declare
    time_now DATE;
    terminal CHAR(10);
    begin
    time_now := SYSDATE;

    terminal := USERENV('TERMINAL');

    IF INSERTING THEN

    INSERT INTO amy.aud

    VALUES (user, time_now,

    terminal, 'INSERT');



    ELSIF DELETING THEN

    INSERT INTO amy.aud

    VALUES (user, time_now,

    terminal, 'DELETE');



    ELSE

    INSERT INTO amy.aud

    VALUES (user, time_now,

    terminal, 'UPDATE');


    END IF;

    END;

    /
    ---------------------------------------------------




Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width