Re: SESSION INFO VIA AUDITING + LOGON TRIGGERS

Seems like a waste of time to maintain in your own table what Oracle already maintains for you ...

Anyway, without the error messages from Oracle you refer to, this is just a guess at best or at least some changes you should make anyway.

Remove the "execute immediate" . Why would you need to do dynamic SQL ?

Prefix the table name in the insert statement with the owner/schema name of the table client_info (is it system.client_info?).

-- 

"AMIT" <kumar_929_at_yahoo.co.uk> wrote in message
news:6f94b59f.0403162035.69f16dbd_at_posting.google.com...


> Hi,

> Thanks for your ideas.

>

> With regards to getting client's session information in a table, I

> like to say that when I create a view on the basis of fixed tables

> like v_$session, the view is created, but when the user who has not

> enough priviliges logon to database, he did not connect to database

> with the message that he may not see the fixed tables etc  or

> recursive level error comes.

>

> So how should we overcome this issue, if we talk about to create a

> view which based on fixed tables etc,and how I used this view in Logon

> Trigger for getting the same info as I got via tables.

>  Could you please give some idea?

>

> Thanks

>

> Amit

>

>

> pagesflames_at_usa.net (Dusan Bolek) wrote in message

news:<1e8276d6.0403160759.267de48e_at_posting.google.com>...


> > kumar_929_at_yahoo.co.uk (AMIT) wrote in message

news:<6f94b59f.0403160327.38b3bf87_at_posting.google.com>...


> > > But the problem is that, the information will not be deleted from the

> > > client info table , if the user logoff abnormally from the database.

> > > How to overcome this issue?

> > > I heard that if we use views instead of the table

> > > &#8220;client_info&#8221;, which I am using, we could manage this

> > > issue.

> > > Could someone explain how should we do that?

> > >

> > > I am using auditing + logon and logoff trigger in order manage the

> > > session info.

> >

> > > 'INSERT INTO client_info(session_id,client_name,sid,

> > > serial#,workstation,host_name,logon_time , description)

> > > select a.sessionid,a.username , b.sid, b.serial#, a.os_username,

> > > b.machine,b.logon_time,a.comment_text from user_Audit_trail a ,

> > > V$session b

> > > where  a.sessionid = b.audsid

> > > and a.username = b.username

> > > and a.sessionid=userenv(''sessionid'')';

> >

> > Is true that your solution is probably not the best imaginable. I

> > think that the suggestion with a view means, that you're basically

> > doing a join between two tables and inserting the result into the

> > other table.

> > So a better way is just to create a view based on the same query and

> > when you will have to look at currently logged users, just select from

> > this view, where only actual data are going to be stored.

> > This is much more sensible than your trigger stuff.