?
Solved

Trigger to capture SQL statement and user info

Posted on 2005-03-24
14
Medium Priority
?
4,995 Views
Last Modified: 2007-12-19
Gurus,

I have this urgent req.

I need to come up with a wrapper to audit DML activities on specific tables ( I may be calling package/procedure on each of those table's triger) by some users after checking their profile.

Its been very long time I have written pl/sql code, Can you guys help me come up with some thing in short time.

I need database trigger , which calls the wrapper for DML activity of specific table.
It should be an autonomous transaction.

I need to log the username, timestamp, terminal, machine, type of DML operation , and SQL statement that user issued.

Appreciate your help sincererly,
Sri
0
Comment
Question by:csreeni
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
  • 2
  • +3
14 Comments
 
LVL 29

Expert Comment

by:MikeOM_DBA
ID: 13629972

What you are asking for is a TRACE!
Set trace parameter's to on (true) and use tkprof to analyze the trace file.

0
 
LVL 6

Expert Comment

by:Jankovsky
ID: 13630483
In a sepatrate case it's possible based on statement based trigger using V$session, V$sql identifying session_id.

To solve it systematically I'd recommend you to set on auditing.

 
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 13630643
Check out auditing:
http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96521/audit.htm#1108

If you don't wish to go to that level, you should be able to come up with your own using triggers and PL/SQL.  Just beware that your own can always be bypassed by any privledged/knowledgable user (then again, so can auditing).

This doesn't capture the SQL but this can be added with a little more work (just don't have time right now to get this part).
------------------------------------------------
drop table myAudit;
create table myAudit(
      username varchar2(100),
      whichtable varchar2(100),
      DMLtimestamp varchar2(100),
      terminal varchar2(100),
      ipaddress varchar2(100),
      osuser varchar2(100),
      DMLoperation  varchar2(100)
)
/


drop table tab1;
create table tab1 (
      col1 varchar2(100)
)
/

create or replace trigger tab1_trig
after insert or update or delete on tab1
for each row
declare
      PRAGMA AUTONOMOUS_TRANSACTION;
      DMLop varchar2(6);
begin
      if inserting then DMLop := 'Insert';
      elsif deleting then DMLop := 'Delete';
      elsif updating then DMLop := 'Update';
      else DMLop := '???';
      end if;

      insert into myAudit values(
            sys_context('userenv', 'session_user'),
            'TAB1',
            systimestamp,
            sys_context('userenv', 'terminal'),
            sys_context('userenv', 'ip_address'),
            sys_context('userenv', 'os_user'),
            DMLop
      );
      commit;

end;
/

show errors

insert into tab1 values('a');
insert into tab1 values('b');
update tab1 set col1 ='Z' where col1='b';
delete from tab1 where col1='Z';
commit;

select * from myAudit;

0
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 

Author Comment

by:csreeni
ID: 13630841
we set up auditing for DDL activities, but DML on some critical tables need to be captured. We had to opt for triggers, as auditing can't be done at object and user level and can't capture the actual statement. Capturing the statement part looks to be tricky.

I can start with what slight suggested. Try to get session id as jan suggested.

If any could help me with that, its great. I am also short of time on this -:)

0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 13631060
Not sure a simple trigger will work for you.

To get the entire SQL you can use:
select sql_text from v$sqltext
           where address = (select sql_address from v$session where AuDsid = SYS_CONTEXT('USERENV','SESSIONID'))
           order by piece ;

Where I think it will break down is whoever fires the trigger will need select on the V$ views.  Also, BIND variables don't show up in v$sqltext.

0
 
LVL 77

Accepted Solution

by:
slightwv (䄆 Netminder) earned 2000 total points
ID: 13631647
This approach is better (I keep forgetting about the system events).

It's mainly borrowed from:
http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:33373327079924

Note:  It is untested since I'm on 10g and as mentioned in the above link:  ora_sql_txt retruns null in 10g.......
also, you might need to change things to a CLOB for large SQL
-------------------------------------------------------------
drop table myAudit;
create table myAudit(
      username varchar2(100),
      whichtable varchar2(100),
      DMLtimestamp varchar2(100),
      terminal varchar2(100),
      ipaddress varchar2(100),
      osuser varchar2(100),
      DMLoperation  varchar2(100),
      DMLcode  varchar2(4000)
)
/


drop table tab1;
create table tab1 (
      col1 varchar2(100)
)
/

create or replace trigger tab1_trig
after insert or update or delete on tab1
declare
      DMLop varchar2(6);
      sql_text ora_name_list_t;
      stmt varchar2(4000);
      n number;
      i number;
begin

      n := ora_sql_txt(sql_text);
      dbms_output.put_line('Hi: ' || n);

      FOR i IN 1..nvl(n,0) LOOP
       stmt := stmt || sql_text(i);
      END LOOP;

      if inserting then DMLop := 'Insert';
      elsif deleting then DMLop := 'Delete';
      elsif updating then DMLop := 'Update';
      else DMLop := '???';
      end if;


      insert into myAudit values(
            sys_context('userenv', 'session_user'),
            'TAB1',
            systimestamp,
            sys_context('userenv', 'terminal'),
            sys_context('userenv', 'ip_address'),
            sys_context('userenv', 'os_user'),
            DMLop,
            stmt
      );

end;
/

show errors


insert into tab1 values('a');
insert into tab1 values('b');
update tab1 set col1 ='Z' where col1='b';
delete from tab1 where col1='Z';
commit;

select * from myAudit;

0
 
LVL 5

Expert Comment

by:helpneed
ID: 13642396
hi

the best ever way to capture these is auditing but as per u require the wrapper for auditing i think
create a table with all details and with dml trigger u can insert the values into the table with PRAGMA AUTONOMOUS_TRANSACTION ...

the slightwv has given the perfect answer ..... just make it as PRAGMA AUTONOMOUS_TRANSACTION thats all

regards
0
 

Expert Comment

by:jayaprakashbindi
ID: 13642871
create table myAudit(
     username varchar2(100),
     whichtable varchar2(100),
     DMLtimestamp varchar2(100),
     terminal varchar2(100),
     ipaddress varchar2(100),
     osuser varchar2(100),
     DMLoperation  varchar2(100)
)
/


drop table tab1;
create table tab1 (
     col1 varchar2(100)
)
/

create or replace trigger tab1_trig
after insert or update or delete on tab1
for each row
declare
     PRAGMA AUTONOMOUS_TRANSACTION;
     DMLop varchar2(6);
begin
     if inserting then DMLop := 'Insert';
     elsif deleting then DMLop := 'Delete';
     elsif updating then DMLop := 'Update';
     else DMLop := '???';
     end if;

     insert into myAudit values(
          sys_context('userenv', 'session_user'),
          'TAB1',
          systimestamp,
          sys_context('userenv', 'terminal'),
          sys_context('userenv', 'ip_address'),
          sys_context('userenv', 'os_user'),
          DMLop
     );
     commit;

end;
/

show errors

insert into tab1 values('a');
insert into tab1 values('b');
update tab1 set col1 ='Z' where col1='b';
delete from tab1 where col1='Z';
commit;

select * from myAudit;

0
 

Expert Comment

by:jayaprakashbindi
ID: 13642896
Hi

1. enable the oracle auditing option to view the sql and session information

0
 

Author Comment

by:csreeni
ID: 13648361
Is there any function that gives me the name of triggering table.... in our case tab1
I tried dbms_stanadard.dictionary_obj_name
-- It works fine when I do the operation as owner, but if I issue command on user.tab1
then its not logging any thing.
tried ora_dict_obj_name too.....

Any ideas!
Thx

 
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 13651965
I believe you will have to hard code the table name in the trigger itself.  Can you explain whay you would need this since the trigger is table specific?  I'm not aware of any system level trigger that will work for you.
0
 

Author Comment

by:csreeni
ID: 13654244
I am calling a standard package/procedure in trigger. I may have trigger on multiple tables, calling this same package.
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 13654607
All you need to do is hardcode the parameter to the package in the trigger.


for example:

create or replace procedure bubba(table_name in varchar2) ...


create or replace trigger tab1_trigger
...
bubba('TAB1');
...

create or replace trigger tab2_trigger
...
bubba('TAB2');
...
0
 

Author Comment

by:csreeni
ID: 13657155
Thank you very much...
0

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
Suggested Courses

764 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question