Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 5423
  • Last Modified:

Trigger to capture SQL statement and user info

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
csreeni
Asked:
csreeni
  • 5
  • 4
  • 2
  • +3
1 Solution
 
MikeOM_DBACommented:

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

0
 
JankovskyCommented:
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
 
slightwv (䄆 Netminder) Commented:
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
csreeniAuthor Commented:
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
 
slightwv (䄆 Netminder) Commented:
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
 
slightwv (䄆 Netminder) Commented:
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
 
helpneedCommented:
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
 
jayaprakashbindiCommented:
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
 
jayaprakashbindiCommented:
Hi

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

0
 
csreeniAuthor Commented:
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
 
slightwv (䄆 Netminder) Commented:
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
 
csreeniAuthor Commented:
I am calling a standard package/procedure in trigger. I may have trigger on multiple tables, calling this same package.
0
 
slightwv (䄆 Netminder) Commented:
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
 
csreeniAuthor Commented:
Thank you very much...
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 5
  • 4
  • 2
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now