Solved

Oracle Logon to database trigger

Posted on 2013-11-29
7
949 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

 

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

Industry Leaders: 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!

Question has a verified solution.

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

Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

726 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