Trying to tie Audit Information to a UserId. The problem is that we use
Business Objects and the AUD$ contains a generic Id (let's say BO_USER
). So, we don't know who the actual user is that accessed a particular
table. It says BO_USER access a table XYZ.
The workaround we have used is to have BO application pass the UserId as a
comment in the SQL statement itself, and that's working fine. SQL looks like
"SELECT /* USER('deepak') */ col_A, col_B etc.
As mentioned in an earlier post, I have already found a workaround. What
I am now doing is, as part of a logoff trigger, I capture the "SID, SERIAL#,
AUDSID, USER_ID" in another table for that session (joining v$session to
v$open_cursor and/or v$sql), where USER_ID is just a bunch of substr, instr
to extract the UserId from the SQL.
Once I have the above info, I can always join it to AUD$ and get the
actual user name that accessed a table.
-Deepak
----- Original Message ----
From: Niall Litchfield <niall.litchfield@xxxxxxxxx>
To: darrah.john@xxxxxxxxx; sharmakdeep_oracle@xxxxxxxxx;
oracle-l@xxxxxxxxxxxxx
Sent: Thursday, April 26, 2007 11:17:25 AM
Subject: Re: Capturing all SQL_IDs for a SessionId
dbms_monitor.session_trace_enable might work or fill the filesystem .
What problem are you trying to solve?
On 4/26/07, John Darrah <darrah.john@xxxxxxxxx> wrote:
> v$active_session_history is as close as you will get without putting a
> sql_trace on the session. It samples every second.
>
> On 4/25/07, Deepak Sharma <sharmakdeep_oracle@xxxxxxxxx> wrote:
> >
> > Is there a way to capture all the SQL_IDs that a session generated -
> > historically?
> >
> > I have tried a couple of options like v$open_cursor (it's volatile in
the
> > sense that entries exist as long as the session exist).
> >
> > v$active_session_history - Does only sampling and does not capture
every
> > sql.
> >
> > Pls correct me if my above observations are wrong.
> >
> > Thanks,
> > Deepak
> >
> > __________________________________________________
> > Do You Yahoo!?
> > Tired of spam? Yahoo! Mail has the best spam protection around
> > http://mail.yahoo.com
> > --
> > //www.freelists.org/webpage/oracle-l
> >
> >
> >
>
--
Niall Litchfield
Oracle DBA
http://www.orawin.info
--
//www.freelists.org/webpage/oracle-l
__________________________________________________
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com
