Pete Finnigan's Oracle Security Forum


    
      
        Pete Finnigan's Oracle Security Forum
        (http://www.petefinnigan.com/forum/yabb/YaBB.cgi)
      

        Oracle Security >> Oracle Auditing >> Grabbing SQL used for update (9i)
        
(Message started by: Pete Finnigan on Oct 31^st, 2006, 10:07am)

Title: Grabbing SQL used for update (9i)
Post by Pete Finnigan on Oct 31^st, 2006, 10:07am

Hi

I have a requirement to log update statements being run by certain userid's.

I know FGA in 10G can do this easily, but has anyone got any links, code, etc.. to accomplish this in 9i.

thanks in advance
Darren

Title: Re: Grabbing SQL used for update (9i)
Post by Pete Finnigan on Nov 1^st, 2006, 10:01am

Hi Darren,

I dont have any example code but it should be possible with a trigger to look via v$sqlarea, v$sql etc (beware of trigger performance and also access to these views is expensive) and get the SQL. I seem to remember in the darkest depths of my mind seeing a similar question a few years ago. You could try searching c.d.o.s and oracle-l, lazydba and dba-village as these are the likely places I saw it. I beleive that someone did this and got it working.

cheers

Pete

Title: Re: Grabbing SQL used for update (9i)
Post by Pete Finnigan on Nov 6^th, 2006, 1:10am

This works in 9iR2. Not sure about 9iR1.

Code:

create or replace TRIGGER log_upd before update on fred
DECLARE
v_temp VARCHAR2(2000);
v_num NUMBER;
v_sql_text ora_name_list_t;
begin
v_num := ora_sql_txt(v_sql_text);
BEGIN
FOR i IN 1..v_num LOOP
v_temp := v_temp || v_sql_text(i);
END LOOP;
EXCEPTION
WHEN VALUE_ERROR THEN NULL;
END;
v_temp := translate(v_temp,''''||chr(0)||chr(10),'"');
dbms_output.put_line(v_temp);
end;

Title: Re: Grabbing SQL used for update (9i)
Post by Pete Finnigan on Nov 6^th, 2006, 10:22am

Thanks gary, thats very useful.

cheers

Pete