| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: Audit Trail challenge
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 );
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_table2 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 Sat Jul 03 1999 - 08:05:42 CDT
 |
 |