Auditing an Oracle database for security issues is very important. PeteFinnigan.com provides all of the information and tools that you will need Click here for details of PeteFinnigan.com Limited's detailed Oracle database security audit service Click here for details of PeteFinnigan.com Limited's Oracle Security Training Courses
Cookie Policy:We only use essential cookies on small sections of this website. For details see here.

Welcome, Guest. Please Login.
Nov 23rd, 2017, 7:13am
News: Welcome to Pete Finnigan's Oracle security forum
Home | Help | Search | Members | Login
   Pete Finnigan's Oracle Security Forum
   Oracle Security
   Oracle Auditing
(Moderator: Pete Finnigan)
   Grabbing SQL used for update (9i)
« Previous topic | Next topic »
Pages: 1  Reply | Notify of replies | Send Topic | Print
   Author  Topic: Grabbing SQL used for update (9i)  (Read 1891 times)
darren turland
PeteFinnigan.com Newbie
*





   
View Profile |

Posts: 9
Grabbing SQL used for update (9i)
« on: Oct 31st, 2006, 10:07am »
Quote | Modify

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
« Last Edit: Oct 31st, 2006, 10:10am by darren turland » IP Logged
Pete Finnigan
PeteFinnigan.com Administrator
*****




Oracle Security is easier if you design for it

   
View Profile | WWW | Email

Gender: male
Posts: 309
Re: Grabbing SQL used for update (9i)
« Reply #1 on: Nov 1st, 2006, 10:01am »
Quote | Modify

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
IP Logged

Pete Finnigan (email:pete@petefinnigan.com)
Oracle Security Web site: http://www.petefinnigan.com
Forum: http://www.petefinnigan.com/forum/yabb/YaBB.cgi
Oracle security blog: http://www.petefinnigan.com/weblog/entries/index.html
gamyers
PeteFinnigan.com Junior Member
**



I love YaBB 1G - SP1!

   
View Profile |

Posts: 80
Re: Grabbing SQL used for update (9i)
« Reply #2 on: Nov 6th, 2006, 1:10am »
Quote | Modify

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;
IP Logged
Pete Finnigan
PeteFinnigan.com Administrator
*****




Oracle Security is easier if you design for it

   
View Profile | WWW | Email

Gender: male
Posts: 309
Re: Grabbing SQL used for update (9i)
« Reply #3 on: Nov 6th, 2006, 10:22am »
Quote | Modify

Thanks gary, thats very useful.
 
cheers
 
Pete
IP Logged

Pete Finnigan (email:pete@petefinnigan.com)
Oracle Security Web site: http://www.petefinnigan.com
Forum: http://www.petefinnigan.com/forum/yabb/YaBB.cgi
Oracle security blog: http://www.petefinnigan.com/weblog/entries/index.html
Pages: 1  Reply | Notify of replies | Send Topic | Print

« Previous topic | Next topic »

Powered by YaBB 1 Gold - SP 1.4!
Forum software copyright 2000-2004 Yet another Bulletin Board