Substitute FGA functionality in Oracle 9iR2

Hi!

We wanted to make a whole sql statement capture solution with FGA in a datawarehouse system to audit user actions on an Oracle9iR2 RDBMS on HP UNIX. We heard, that there is serious problem with FGA in this version and the patch which will fix it will come out only in 1 or 2 months, but our customer wants to use this patch only after 3 months more testing period. For this 5 months period we need a solution which lets us to capture the whole sql statement of users who selects predefined tables (predefined columns). We need the time, dbuser, osuser, client and sql statement information at least (table name is also preferred).

Thank you for your help
DobronteAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

schwertnerCommented:
Look at this:

I want to audit 'select' of all my users, I've already see the table dba_fga_audit_trail, but in fact i need more informations and i need to see all the clause 'sql', not only the first 4000 characthers, so i think i have to use v_$sqltext_with_new_lines.
I m using FGA with the procedure FGA_CAPTURE

exec DBMS_FGA.ADD_POLICY(OBJECT_SCHEMA => 'DEMO',OBJECT_NAME => 'REF_UR',POLICY_NAME => 'POL_REF_UR',HANDLER_SCHEMA => 'DEMO',HANDLER_MODULE=> 'CAPTURE',ENABLE => TRUE)

CREATE OR REPLACE procedure CAPTURE_FGA(V_SCHEMA VARCHAR2,V_TABLE VARCHAR2,V_POLICY VARCHAR2)
AS
x number;
scenario number;
testsq varchar2(4000);
begin
for x in
( select username||'('||sid||','||serial#||
') ospid = ' || process ||
' program = ' || program, username,
to_char(LOGON_TIME,' Day YYYY-MM-DD HH24:MI:SS') logon_time,
to_char(sysdate,' Day YYYY-MM-DD HH24:MI:SS') current_time,
sql_address,prev_sql_addr,LAST_CALL_ET,MACHINE,TERMINAL,process,program,
audsid,sid,serial#,osuser,a.module,b.CPU_TIME,b.EXECUTIONS,b.FETCHES,b.INVALIDATIONS,
b.LOADS, b.OPTIMIZER_COST,b.OPTIMIZER_MODE,b.sorts,b.OPEN_VERSIONS,b.ROWS_PROCESSED,
b.BUFFER_GETS,b.DISK_READS,b.PARSE_CALLS,b.ELAPSED_TIME,b.USERS_EXECUTING,b.USERS_OPENING,
b.LOADED_VERSIONS,b.FIRST_LOAD_TIME,b.LAST_LOAD_TIME
from v$session a,v$sql b
where status = 'ACTIVE'
and rawtohex(prev_sql_addr) <> '00'
and b.address = prev_sql_addr
and username is not null order by last_call_et)
loop
for y in ( select max(decode(piece,0,sql_text,null)) ||max(decode(piece,1,sql_text,null)) ||max(decode(piece,2,sql_text,null)) ||max(decode(piece,3,sql_text,null))
sql_text
from v$sqltext_with_newlines
where
piece < 4
and address in
(select prev_sql_addr from v$session
where sid = x.sid
and serial# = x.serial#) )
loop
if ( y.sql_text not like '%listener.get_cmd%' ) then
insert into rt_suivi_consult_aas
(trc_schema,trc_table,trc_policy,dt_trace,sql_text_line,terminal,
machine,username,process,program,sid,serial#,osuser,module,logon_time,
CPU_TIME,EXECUTIONS,FETCHES,INVALIDATIONS,LOADS,OPTIMIZER_COST,OPTIMIZER_MODE,
sorts,OPEN_VERSIONS,ROWS_PROCESSED,BUFFER_GETS,DISK_READS,PARSE_CALLS,ELAPSED_TIME,
USERS_EXECUTING,USERS_OPENING,LOADED_VERSIONS,
FIRST_LOAD_TIME,LAST_LOAD_TIME)
values (v_schema,v_table,v_policy,x.current_time,y.sql_text,x.terminal,
x.machine,x.username,x.process,x.program,x.sid,x.serial#,x.osuser,
x.module,x.logon_time,x.cpu_time,x.EXECUTIONS,x.FETCHES,x.INVALIDATIONS,
x.LOADS,x.OPTIMIZER_COST,x.OPTIMIZER_MODE,
x.sorts,x.OPEN_VERSIONS,x.ROWS_PROCESSED,
x.BUFFER_GETS,x.DISK_READS,x.PARSE_CALLS,
x.ELAPSED_TIME,x.USERS_EXECUTING,x.USERS_OPENING,
x.LOADED_VERSIONS,x.FIRST_LOAD_TIME,x.LAST_LOAD_TIME);
end if;
end loop;
end loop;
end;
/

After I make a select on my table ref_ur
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.