Solved

Oracle Logon to database trigger

Posted on 2013-11-29
7
934 Views
Last Modified: 2013-12-03
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.
0
Comment
Question by:itfocus
  • 4
  • 2
7 Comments
 
LVL 20

Expert Comment

by:flow01
ID: 39686552
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
/
0
 
LVL 19

Expert Comment

by:Thommy
ID: 39689385
Make sure that your database logon trigger is created by user SYS?

What is your Oracle database version?
0
 

Author Comment

by:itfocus
ID: 39689568
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.
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:itfocus
ID: 39689923
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.
0
 

Author Comment

by:itfocus
ID: 39689929
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,
0
 
LVL 20

Accepted Solution

by:
flow01 earned 500 total points
ID: 39690418
First try an other possibility

AFTER LOGON ON DATABASE
BEGIN
-- maybe the sessionid is know within the trigger but not at the time the WHEN is evaluated
IF sys_context('USERENV','SESSIONID') > 0  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;


Else
create the view to mimic your audit table

create view stats$user_log_vw as select * from stats$user_log;

create the trigger on the view
CREATE OR REPLACE TRIGGER conditional_insert
INSTEAD OF INSERT
ON stats$user_log_vw
FOR EACH ROW
BEGIN
  if :new.session_id = 0 then null; else
    insert into stats$user_log (session_id) values (:new.session_id);  
    -- change the insert to include all the columns of your audit table
  end if;
END conditional_insert;
/

recreate your logon-trigger but instead of inserting into your table , insert into the view
and be carefull !!!:
 i don't know what happens if the trigger fails : maybe no sessions  can be started at all.
so
1. be sure to keep a 'sys'-session active that can drop or or disable a not-working trigger
2.add an exception handler to your trigger until your are sure everything works fine
...
EXCEPTION WHEN OTHERS THEN
    NULL;  --  make sure an error does not make every logon impossible
END;
3. think of an escape in the production-version (what happens if your audit-table is out of table-space ?) :  for example exclude  user sys.
0
 

Author Closing Comment

by:itfocus
ID: 39693144
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;

/
0

Featured Post

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…

733 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question