Solved

Oracle Logon to database trigger

Posted on 2013-11-29
7
916 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Oracle Finace 3 46
Pl/SQL Query 31 62
Oracle -- identify blocking session 24 22
Oracle DATE Column Space 11 44
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ā€¦
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.

757 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now