asked on

Oracle Logon to database trigger

Hi I have created a trigger to trap user logons to Oracle database:
create or replace trigger
logon_audit_trigger
AFTER LOGON ON DATABASE
BEGIN
insert into stats$user_log values(
user,
sys_context('USERENV', 'OS_USER'),
sys_context('USERENV','TERMINAL'),
sys_context('USERENV','SESSIONID'),
sys_context('USERENV','HOST'),
null,
null,
null,
sysdate,
to_char(sysdate, 'hh24:mi:ss'),
null,
null,
null
);
END;

This works fine - however there are a lot of background jobs which I want to ignore.
These have a session_ID of 0.
I have tried
~
~
AFTER LOGON ON DATABASE
when (sys_context('USERENV','SESSIONID') > 0)
BEGIN
~
~

But this causes no sesions to be captured,. I suspect the SESSION_ID is not set before the trigger fires.
I would welcome any ideas as to how to achieve what I am trying to do.

flow01

And if you omit the when clause, what value is inserted in stats$user_log ?
If it's available there (=0) you can use a view with an instead of trigger to filter the insert

Here is the concept
create table stats$user_log2 (session_id varchar2(50));
create view stats$user_logv2 as select * from stats$user_log2;

CREATE OR REPLACE TRIGGER conditional_insert
INSTEAD OF INSERT
ON stats$user_logv2
FOR EACH ROW
BEGIN
if :new.session_id = 0 then null; else
insert into stats$user_log2 (session_id) values (:new.session_id);
end if;
END conditional_insert;
/
insert into stats$user_logv2 select 0 from dual;
insert into stats$user_logv2 select 1 from dual;
select * from stats$user_log2
/

Thommy

Make sure that your database logon trigger is created by user SYS?

What is your Oracle database version?

Des Kelly

ASKER

Yes,
Without the where 0 is inserted in the session_ID.
problem is that these are executing constantly and generating large amounts of data in audit table.
I will test your suggestion which looks good.

I will update.
Appreciate assist.

Des Kelly

ASKER

Timmy,
It's oracle 9i. I have created a user specifically for this as audit people need access.
It's working ok except as I explained when I exclude session_id =0 ann sessions are excluded.

Des Kelly

ASKER

Hi Flow,
I have run what you offered and that;'s ok - except I don't fully understand what you are doing and how to incorporate it in my trigger.

Thanks,

ASKER CERTIFIED SOLUTION

flow01

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Des Kelly

ASKER

Hi Flow,
Hats off to you.
Replacing the When with If did the trick (I also took on board your suggestion to exclude SYS user):
create or replace trigger
logon_audit_trigger
AFTER LOGON ON DATABASE
BEGIN
IF sys_context('USERENV','SESSIONID') > 0 and user <> 'SYS' THEN
insert into stats$user_log values(
user,
sys_context('USERENV', 'OS_USER'),
sys_context('USERENV','TERMINAL'),
sys_context('USERENV','SESSIONID'),
sys_context('USERENV','HOST'),
null,
null,
null,
sysdate,
to_char(sysdate, 'hh24:mi:ss'),
null,
null,
null
);
END IF;
END;

/