Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: Audit Trail challenge

Re: Audit Trail challenge

From: John Doe <john.doe_at_company.com>
Date: Tue, 6 Jul 1999 12:05:02 +0800
Message-ID: <7ls12r$fub$1@amdint.amd.com>


Hi Thomas
Thanks for your expertise, I have modified your code a bit and set the 'trap'.
Now I am waiting to see if I catch anything... Thanks again!
Terence

Thomas Kyte wrote in message <377e0949.2106118_at_newshost.us.oracle.com>...
>A copy of this was sent to "John Doe" <john.doe_at_company.com>
>(if that email address didn't require changing)
>On Fri, 2 Jul 1999 09:33:40 +0800, you wrote:
>
>>Hi
>>I am a fairly new DBA and is currently facing a challenge.
>>Recently a superuser of our HR database has complained that some of the
>>employee training records are disappearing from our Oracle 7.3.2 database.
>>(The user interface for the HR application is built using Power Builder
5.0)
>>.I have setup the audit trail on that table but it doesn't help much, only
>>confusing the issue further, as the trail shows that certain users has
been
>>deleting data but the users were denying it saying they were just doing
>>queries. Furthermore the trail shows multiple rows of deletion at the
very
>>same time, which is not possible thru the user interface (they can only
>>delete one record at a time), and the users do not have any access to the
>>database server or SQLplus tools at all.
>>What we would need now is an alogorithm to show who is doing what SQL
>>statement whenever a delete is attempted on that particular table.
>>Whould greatly appreciate if anyone could help shed a light on this.
>>Pls reply to wk.teoh_at_amd.com
>>Thanks a lot
>>Terence
>>
>
>
>don't know if you want to goto this level of detail but the following might
give
>you some ideas.
>
>by using a trigger, you can capture alot about the current session --
including
>the sessions open cursors and database call stack which might help you
pinpoint
>what part of the application is doing the 'damage'. Here is an example
against
>the EMP table from scott/tiger. Note that SYS must grant select on the
v_$*
>tables to the owner of the trigger.
>
>SQL> connect sys/manager
>Connected.
>SQL> grant select on v_$open_cursor to tkyte;
>SQL> grant select on v_$session to tkyte;
>SQL> grant select on v_$sqltext to tkyte;
>
>SQL> connect tkyte/tkyte
>Connected.
>
>SQL> create table log_table
> 2 ( ts date, seq int, username varchar2(30),
> 3 what varchar2(30), addr raw(4), piece int, sql_text
varchar2(2000) );
>Table created.
>
>SQL> create sequence log_seq;
>
>SQL> create or replace trigger emp_log
> 2 before delete on emp for each row
> 3 declare
> 4 l_stack varchar2(2000) := dbms_utility.format_call_stack;
> 5 begin
> 6 insert into log_table values ( sysdate, log_seq.nextval, user,
> 7 'call stack', null, 0, l_stack );
> 8
> 8 insert into log_table
> 9 select sysdate, log_seq.currval, user, 'open cursors',
> 10 address, piece, sql_text
> 11 from v$sqltext
> 12 where address in (select address
> 13 from v$open_cursor
> 14 where sid = ( select sid
> 15 from v$session
> 16 where audsid =
userenv('sessionid') )
> 17 );
> 18 end;
> 19 /
>Trigger created.
>
>SQL> create or replace procedure foo
> 2 as
> 3 begin
> 4 delete from emp where rownum = 1;
> 5 end;
> 6 /
>Procedure created.
>
>SQL> exec foo
>PL/SQL procedure successfully completed.
>
>SQL> exec foo
>PL/SQL procedure successfully completed.
>
>
>So, we have a trigger to capture the current sessions state. We can now
report
>on the log_Table. the following shows what is in it after doing the above.
>
>The call stack is very useful if you use stored procedures. It will show
you
>exactly how you got to this trigger. The following shows us that we got to
the
>trigger TKYTE.EMP_LOG from line 4 of the procedure TKYTE.FOO -- the delete
>statement. This in itself might be more then enough information. The
remaining
>lines show all of the open cursors my session has -- note: these cursors
might
>be OPEN but not necessarily ACTIVE in this transaction. plsql and other
things
>cache cursors so you might see sql in there that isn't relevant.
>
>
>SQL> column sql_text format a40
>SQL> column what format a13
>SQL> break on addr skip 1
>SQL> select seq, what, addr, sql_text from log_table
> 2 order by seq, what, addr, piece;
>
> SEQ WHAT ADDR SQL_TEXT
>---------- ------------- -------- ----------------------------------------
> 1 call stack ----- PL/SQL Call Stack -----
> object line object
> handle number name
> 80624bb8 2 TKYTE.EMP_LOG
> 8065ce84 4 procedure TKYTE.FOO
> 8054df14 1 anonymous block
>
>
> 1 open cursors 804E55C4 begin foo; end;
>
> 1 open cursors 8054C850 INSERT INTO LOG_TABLE SELECT SYSDATE,LOG
> _SEQ.CURRVAL,USER,'open
>
> 1 open cursors cursors',ADDRESS,PIECE,SQL_TEXT FROM V
> $SQLTEXT WHERE ADDRESS
>
> 1 open cursors IN (SELECT ADDRESS FROM V$OPEN_CURSOR
> WHERE SID = (SELECT SI
>
> 1 open cursors D FROM V$SESSION WHERE AUDSID = USERE
> NV('sessionid') ) )
>
>
> 1 open cursors 80551884 INSERT INTO LOG_TABLE VALUES ( SYSDATE,L
> OG_SEQ.NEXTVAL,USER,'cal
>
> 1 open cursors l stack', NULL ,0,:b1 )
>
> 1 open cursors 805B5674 DELETE FROM EMP WHERE ROWNUM = 1
>
> 2 call stack ----- PL/SQL Call Stack -----
> object line object
> handle number name
> 80624bb8 2 TKYTE.EMP_LOG
> 8065ce84 4 procedure TKYTE.FOO
> 805597fc 1 anonymous block
>
>
> 2 open cursors 804C11A0 begin foo; end;
>
> 2 open cursors 8054C850 INSERT INTO LOG_TABLE SELECT SYSDATE,LOG
> _SEQ.CURRVAL,USER,'open
>
> 2 open cursors cursors',ADDRESS,PIECE,SQL_TEXT FROM V
> $SQLTEXT WHERE ADDRESS
>
> 2 open cursors IN (SELECT ADDRESS FROM V$OPEN_CURSOR
> WHERE SID = (SELECT SI
>
> 2 open cursors D FROM V$SESSION WHERE AUDSID = USERE
> NV('sessionid') ) )
>
>
> 2 open cursors 80551884 INSERT INTO LOG_TABLE VALUES ( SYSDATE,L
> OG_SEQ.NEXTVAL,USER,'cal
>
> 2 open cursors l stack', NULL ,0,:b1 )
>
> 2 open cursors 805B5674 DELETE FROM EMP WHERE ROWNUM = 1
>
>
>18 rows selected.
>
>
>Hope this helps
>
>--
>See http://govt.us.oracle.com/~tkyte/ for my columns 'Digging-in to
Oracle8i'...
>Current article is "Part I of V, Autonomous Transactions" updated June
21'st
>
>Thomas Kyte tkyte_at_us.oracle.com
>Oracle Service Industries Reston, VA USA
>
>Opinions are mine and do not necessarily reflect those of Oracle
Corporation Received on Mon Jul 05 1999 - 23:05:02 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US