Oracle Server - ON-LOGON Trigger ??

Author

ON-LOGON Trigger ??

Robert Bralic

2005-07-27, 7:23 am

Hallo,

I would likre to write in PL/SQL small tool for
dba-s,that will note from "V$SESSION" for any,
login username and other datas into a table,
something like "last" on UNIX, but I don't know
how to write and compile this function with that
trigger(ON-LOGIN, if this exysts), so I need
a little help.

Thanks in advance, Robert !
robert.bralic@si.t-com.hr

fitzjarrell@cox.net

2005-07-27, 7:23 am

Robert Bralic wrote:
> Hallo,
>
> I would likre to write in PL/SQL small tool for
> dba-s,that will note from "V$SESSION" for any,
> login username and other datas into a table,
> something like "last" on UNIX, but I don't know
> how to write and compile this function with that
> trigger(ON-LOGIN, if this exysts), so I need
> a little help.
>
>
> Thanks in advance, Robert !
> robert.bralic@si.t-com.hr

It has been done before, and such code is available in "Oracle9i:
PL/SQL Programming", by Scott Urman. You can download the code he's
written here:

http://shop.osborne.com/cgi-bin/ora.../downloads.html

LogPkg1.sql and LogPkg2.sql are what you need to use. Stop trying to
re-invent the wheel.

David Fitzjarrell

fitzjarrell@cox.net

2005-07-27, 7:23 am

fitzjarr...@cox.net wrote:
> Robert Bralic wrote:
>
>
> It has been done before, and such code is available in "Oracle9i:
> PL/SQL Programming", by Scott Urman. You can download the code he's
> written here:
>
> http://shop.osborne.com/cgi-bin/ora.../downloads.html
>
> LogPkg1.sql and LogPkg2.sql are what you need to use. Stop trying to
> re-invent the wheel.
>
>
> David Fitzjarrell

My mistake, it's LogGonnects.sql and LogPkg1.sql you need.

David Fitzjarrell

IANAL_VISTA

2005-07-27, 9:23 am

"Robert Bralic" <robo_bralic@yahoo.co.uk> wrote in
news:dc7tsm$ng4$2@ss
405.t-com.hr:

> Hallo,
>
> I would likre to write in PL/SQL small tool for
> dba-s,that will note from "V$SESSION" for any,
> login username and other datas into a table,
> something like "last" on UNIX, but I don't know
> how to write and compile this function with that
> trigger(ON-LOGIN, if this exysts), so I need
> a little help.
>
>
> Thanks in advance, Robert !
> robert.bralic@si.t-com.hr
>
>

IMO, it would be MUCH easier to simply AUDIT new sessions.

Robert Bralic

2005-07-28, 3:23 am

<fitzjarrell@cox.net> wrote in message
news:1122468689.416115.169070@z14g2000cwz.googlegroups.com...
>
>
> Robert Bralic wrote:
>
>
> It has been done before, and such code is available in "Oracle9i:
> PL/SQL Programming", by Scott Urman. You can download the code he's
> written here:
>
> http://shop.osborne.com/cgi-bin/ora.../downloads.html
>
> LogPkg1.sql and LogPkg2.sql are what you need to use. Stop trying to
> re-invent the wheel.
>
>
> David Fitzjarrell
>

Dear,

I'am sory for my question but internet is sometimes to big,
and in my company they don't by me books, and I 'am not
well payed.Somethimes when I have a free time is to boring,
and I make of some small, and usable software just for kill a boringes...

Thanks, Robert..!!

fitzjarrell@cox.net

2005-07-29, 3:23 am

Robert Bralic wrote:
> <fitzjarrell@cox.net> wrote in message
> news:1122468689.416115.169070@z14g2000cwz.googlegroups.com...
>
> Dear,
>
> I'am sory for my question but internet is sometimes to big,
> and in my company they don't by me books, and I 'am not
> well payed.Somethimes when I have a free time is to boring,
> and I make of some small, and usable software just for kill a boringes...
>
> Thanks, Robert..!!

Here is the script I use:

create table
stats$user_log
(
user_id varchar2(30),
session_id number(8),
host varchar2(30),
last_program varchar2(48),
last_action varchar2(32),
last_module varchar2(32),
logon_day date,
logon_time varchar2(10),
logoff_day date,
logoff_time varchar2(10),
elapsed_minutes number(8)
)
;

create or replace trigger
logon_audit_trigger
AFTER LOGON ON DATABASE
BEGIN
insert into stats$user_log values(
user,
sys_context('USERENV
','SESSIONID'),
sys_context('USERENV
','HOST'),
null,
null,
null,
sysdate,
to_char(sysdate, 'hh24:mi:ss'),
null,
null,
null
);
END;
/

create or replace trigger
logoff_audit_trigger

BEFORE LOGOFF ON DATABASE
BEGIN
-- ********************
********************
***********
-- Update the last action accessed
-- ********************
********************
***********
update
stats$user_log
set
last_action = (select action from v$session where
sys_context('USERENV
','SESSIONID') = audsid)
where
sys_context('USERENV
','SESSIONID') = session_id;
-- ********************
********************
***********
-- Update the last program accessed
-- ********************
********************
***********
update
stats$user_log
set
last_program = (select program from v$session where
sys_context('USERENV
','SESSIONID') = audsid)
where
sys_context('USERENV
','SESSIONID') = session_id;
-- ********************
********************
***********
-- Update the last module accessed
-- ********************
********************
***********
update
stats$user_log
set
last_module = (select module from v$session where
sys_context('USERENV
','SESSIONID') = audsid)
where
sys_context('USERENV
','SESSIONID') = session_id;
-- ********************
********************
***********
-- Update the logoff day
-- ********************
********************
***********
update
stats$user_log
set
logoff_day = sysdate
where
sys_context('USERENV
','SESSIONID') = session_id;
-- ********************
********************
***********
-- Update the logoff time
-- ********************
********************
***********
update
stats$user_log
set
logoff_time = to_char(sysdate, 'hh24:mi:ss')
where
sys_context('USERENV
','SESSIONID') = session_id;
-- ********************
********************
***********
-- Compute the elapsed minutes
-- ********************
********************
***********
update
stats$user_log
set
elapsed_minutes =
round((logoff_day - logon_day)*1440)
where
sys_context('USERENV
','SESSIONID') = session_id;
END;
/

David Fitzjarrell

Sponsored Links