Auditing + System Triggers

[Chuck]

Hi,

I am using the following logon trigger to store the session information into a table "client_info"
When ever the user logon , his session information stores in the client_info table.
And when the session logoff its information will be deleted from the table client_inf via Logoff trigger.

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 “client_info”, 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.



Regards

CLee




CREATE TABLE CLIENT_INFO(session_id number,client_name VARCHAR2(30),sid number,
serial# number,workstation varchar2(30),
host_name varchar2(30),logon_time date, Description varchar2(4000));





CREATE OR REPLACE TRIGGER trg_client_info
AFTER LOGON ON DATABASE

BEGIN

execute immediate

'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'')';

commit;

END;
/




CREATE OR REPLACE TRIGGER logoff_session
BEFORE LOGOFF ON DATABASE

BEGIN

DELETE from client_info where session_id = userenv('sessionid');

commit;

END;
/

[davey23uk]

triggers wont fire after an abnormal log off

[julian]

Don't delete the rows. Or just make a job that runs every hour and remove the SIDs from your client_info table that do not exist in v$session.

[jmodic]

Just out of curriosity: why do you keep insisting in using dynamic sql (EXECUTE IMMEDIATE) in your triggers when normal plain sql would do just fine?

Another question: why do you need that info from V$SESSION to be stored in a permanent table during the duration of the session at all? What's the reason for that?

[mrchrispy]

Looks like a view would do exactly what you want, I'd look into that tbh.

[Chuck]

Hi,

Thanks for ideas about views.

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

Chuck

[Chuck]

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 somebody please give suggestion about it.

Regards


Chuck

[pando]

a dba uses forms to see sessions....?! you must be kidding right

[jmodic]

Originally posted by Chuck
Could somebody please give suggestion about it.

Not exactly a suggestion, more like an observation: I have a feeling that in your shop both dba's (which you obviously are not) and developers (it seems that you fall into that category) are pretty much confused about how oracle works and what it offers....

[jlakhani]

There are lots of DBAs out there(myself not included) who like to see their views thru a gui interface.