[Last Call] Learn how to a build a cloud-first strategyRegister Now

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

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
0
Dobronte
Asked:
Dobronte
1 Solution
 
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

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now