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
ciphersolAsked:
Who is Participating?
 
slightwv (䄆 Netminder)Connect With a Mentor Commented:
Are you wanting to capture the attempted username that failed?

Not sure if this will work for you but try: AUTHENTICATED_IDENTITY for SYS_CONTEXT.

http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions165.htm
0
 
ciphersolAuthor Commented:
That appears to have worked.  Thank you.
0
 
slightwv (䄆 Netminder) Commented:
No problem.  Glad to help.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.