Logon Trigger for SQL TRACING

[BSena]

Dear Pete ,

Thanks.

I looked at your paper about SQL tracing. It contains great
information about SQL TRACING.
The Logon trigger that you use for enable sql tracing is a good
approach.
With regards to this, should we further enhance this Logon trigger for
enabling SQL TRACE for a particular session?
Because I wanted to pass a name or sid, serial# on run time to this
Logon trigger/procedure for enabling SQL TRACING for a specific user
not for many users.
Is that possible? If so could you give advice about how to do that?

Thanks

Bill

[Pete Finnigan]

Hi Bill

What you need to do is select the SID and SERIAL# when the logon trigger
executes. You can use the following select statement in your trigger and
then use the SID and SERIAL# for setting the event:

1 select s.sid,s.serial#
2 from v$session s
3* where sys_context('userenv','SESSIONID')=s.audsid
SQL> /

SID SERIAL#
---------- ----------
10 393

SQL>

You could also consider using an on schema trigger instead of the
database logon trigger. You could combine the above with a simple table
that contains a trace flag for each user that you can set remotely to
control whether trace is on or not. Update the value for a user before
they login and your trigger would get the username with the sid and
serial#,

hth

kind regards

Pete
--
Pete Finnigan
email:pe...@petefinnigan.com
Web site: http://www.petefinnigan.com - Oracle security audit specialists
Book:Oracle security step-by-step Guide - see http://store.sans.org for details.

[BSena]

Hi Pete,

I like to ask you, should we use the Logon trigger for SQL TRACING,
for the third party tool.
How should we manage SQL tracing and then read this trace file using
TKPROF for that specific session that is using the third party tool?
Any ideas will be great.

Thanks

Bill

Pete Finnigan <pl...@petefinnigan.com> wrote in message news:<F7rB2yA5...@peterfinnigan.demon.co.uk>...

[Pete Finnigan]

Hi Bill,

let me get this straight. you want to trace the use of a third party
tool in your organisation? - do the users of this tool logon with
different database ID's - can you identify each session? - i guess
probably. If all users used the same database id then you could check
for IP address of the client or if possible use dbms_application_info to
set the client_info field visible in v$session. So you could use your
logon trigger and in that trigger select sid,serial#,username, os PID,
IP address if needed. You could have a table with every users name in it
and their IP or whatever and a flag to say whether you want to trace
them or not. Then you just update that flag as needed. You can then
check the flag for the current user and set trace with alter session set
events....

To read the trace take a look at my paper http://www.petefinnigan.com/ra
mblings/how_to_set_trace.htm - it describes how to find the directory
your trace files will be in. In your trigger you could save the ospid to
a logging table along with the date/time, username sid serial# etc and
then use this information to find the correct trace file.

[BSena]

Thanks Pete,

As you thought, I want to trace the use of a third party
Tool.
The users of this tool logon the same single database with different
service names.
I have visited your papers about SQL TRACING.
It contains lots of information about SQL TRACE.
But at that stage I am little bit confused.
If possible, Could you give some sample with respect to my query (SQL
tracing for third party tool via logon trigger),in order to the trace
that specific
session which is rinnung this third party tool.

Thanks

Bill

Pete Finnigan <pl...@petefinnigan.com> wrote in message news:<E1+hl3Ay...@peterfinnigan.demon.co.uk>...

[Niall Litchfield]

"BSena" <bill...@hotmail.com> wrote in message
news:2ceca914.04031...@posting.google.com...

> Thanks Pete,
>
>
> As you thought, I want to trace the use of a third party
> Tool.
> The users of this tool logon the same single database with different
> service names.
> I have visited your papers about SQL TRACING.
> It contains lots of information about SQL TRACE.
> But at that stage I am little bit confused.
> If possible, Could you give some sample with respect to my query (SQL
> tracing for third party tool via logon trigger),in order to the trace
> that specific
> session which is rinnung this third party tool.
>
> Thanks
>
> Bill

You might find the following helpful. There are a couple of oddities. I
massage the username because I am using a domain account for testing
purposes. You probably wouldn't need to do this on *nix. I have cut out some
info that might identify the app or the os environment we have so the
trigger might not compile without a bit of editing.

CREATE OR REPLACE TRIGGER TRG_<SCHEMA_NAME>_LOGON

AFTER LOGON ON <SCHEMA_NAME>.SCHEMA

DECLARE

v_username varchar2(255);

begin

/* get the osuser */

select sys_context('userenv','os_user') into v_username from dual;

v_username := substr(v_username,instr(v_username,'\')+1);

if v_username = '<oslogon_name>' then

execute immediate 'alter session set events ''10046 trace name context
forever,level 8''';

end if;

end;

/

cheers

--
Niall Litchfield
Oracle DBA
Audit Commission UK

[BSena]

Thanks Niell,

I also like to say it seems that, I will give the name of the user in
if condition in the logon trigger, in order to start sql tracing.

I further define that, I wanted that whenever this third party tool
connect to database with any user, the sql tracing will start for that
specific seesion and then read the sqltrace file implicitly for that
session using TKPROF.
Looking forward to your suggestion.

Thanks

Bill

"Niall Litchfield" <n-litc...@audit-commission.gov.uk> wrote in message news:<4051ab6b$0$3302$ed9e...@reading.news.pipex.net>...