Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1049
  • Last Modified:

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.
0
itfocus
Asked:
itfocus
  • 4
  • 2
1 Solution
 
flow01Commented:
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
 
ThommyCommented:
Make sure that your database logon trigger is created by user SYS?

What is your Oracle database version?
0
 
itfocusAuthor Commented:
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!

 
itfocusAuthor Commented:
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
 
itfocusAuthor Commented:
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
 
flow01Commented:
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
 
itfocusAuthor Commented:
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

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!

  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now