Re: SESSION INFO VIA AUDITING + LOGON TRIGGERS

Hi,

As I mentioned what should we do if I use the following for getting all the session's information on forms 6i screen:

create view client_info(session_id,DB_USER,Client_Name,Client_IP,Database,DB_Server_Name, DB_Server_IP,logon_time)
as
select sys_context ('USERENV', 'SESSIONID'),user, SYS_CONTEXT ('USERENV',
'Terminal'),sys_context('USERENV','IP_ADDRESS'), SYS_CONTEXT ('USERENV', 'DB_NAME'),utl_inaddr.get_host_name ,utl_inaddr.get_host_address,
sysdate from dual;

In that case, you see, we get the information using SYS_CONTEXT and dual table.
So how should a DBA see all the session information if he query the above view, because this view contains only the current session information.
I wanted to use the SYS_context function. With regards to this could someone please suggect what should we do.

Kinds Regards

Amit

kumar_929_at_yahoo.co.uk (AMIT) wrote in message news:<6f94b59f.0403170957.24e3854d_at_posting.google.com>...
> Hi,
>
> I also like to say that we are using forms 6i , that's why we wanted
> all the sessions information in a table .
>
> So when a dba go to form screen , he will see all the connected
> sessions with their different information like their Ip addresses,user
> name etc.
>
> But I like to say that if we use the view as you said instead of using
> table,the dba will see only his row on screen , which contains his
> session information, not other sessions's information.
>
> So this is the issue with creating views instead of table.
> Could someone please give your suggestion about it.
>
> Thanks
>
> Amit
>
>
>
> "Burt Peltier" <burttemp1ReMoVeThIs_at_bellsouth.net> wrote in message news:<ckS5c.45161$xL3.42732_at_bignews1.bellsouth.net>...
> > 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.
Received on Wed Mar 17 2004 - 22:17:14 CST