Link to home
Start Free TrialLog in
Avatar of ciphersol
ciphersol

asked on

Oracle Failed Login Trigger - Capture Oracle User

How can the Oracle username be captured using a trigger and table for a failed log on attempt.  I'm aware Oracle auditing captures this information. See example below:

create table stats$user_log
(
   terminal            varchar2(255),
   os_username         varchar2(255),
   username            varchar2(30),
   extended_timestamp  date)
tablespace users
;

CREATE OR REPLACE TRIGGER failed_logon_trg
  AFTER SERVERERROR ON DATABASE
  when (ora_server_error(1)=1017)
BEGIN
   insert into stats$user_log
      (terminal, os_username, username, extended_timestamp)
   values
      (SYS_CONTEXT('USERENV','TERMINAL'),
      SYS_CONTEXT('USERENV','OS_USER'),
      USER,
      sysdate);
      COMMIT;
END failed_logon_trg;
/

I've tried both 1) USER result NULL and 2) SYS_CONTEXT('USERENV','CURRENT_USER') result SYS
ASKER CERTIFIED SOLUTION
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of ciphersol
ciphersol

ASKER

That appears to have worked.  Thank you.
No problem.  Glad to help.