?
Solved

Oracle Failed Login Trigger - Capture Oracle User

Posted on 2012-03-27
3
Medium Priority
?
1,491 Views
Last Modified: 2012-03-27
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
0
Comment
Question by:ciphersol
  • 2
3 Comments
 
LVL 78

Accepted Solution

by:
slightwv (䄆 Netminder) earned 2000 total points
ID: 37771625
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
 

Author Comment

by:ciphersol
ID: 37771689
That appears to have worked.  Thank you.
0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 37771818
No problem.  Glad to help.
0

Featured Post

Granular recovery for Microsoft Exchange

With Veeam Explorer for Microsoft Exchange you can choose the Exchange Servers and restore points you’re interested in, and Veeam Explorer will present the contents of those mailbox stores for browsing, searching and exporting.

Question has a verified solution.

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

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…
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
Via a live example, show how to take different types of Oracle backups using RMAN.
Suggested Courses

839 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