unable to collect username during fail logon to the database

[Unna]

Dear all,

I have a trigger to notify failed user logon; however, I am unable to collect the username. My trigger looks something like this:

CREATE OR REPLACE TRIGGER failed_logon_notifications
AFTER SERVERERROR ON DATABASE
DECLARE
v_username varchar2(30);
...
BEGIN
IF ora_is_servererror( 01017 ) THEN
v_username := SYS_CONTEXT( 'USERENV', 'SESSION_USER');
...
BEGIN
utl_mail.send
EXCEPTION
WHEN others THEN
RAISE;
END IF;
END;
/

I notice that both “SYS_CONTEXT( 'USERENV', 'SESSION_USER')” and “select user from dual” return null for failed logon; however, the userid in aud$ is able to collect the username even for failed logon. Does any know how Oracle does that? Or is there a way to collect username for failed logon?


Thanks,
Unna

[gandolf989]

If someone fails to log in to the database, then they don't have a username, since they did not successfully authenticate.
However, auditing is recording what they were trying to log in as. Hence you should rely on auditing for the information
that you want, not a database level trigger. It seems to me that this behavior is what we should expect.

[Unna]

Thanks for your reply. Initially I would agree with you; however, looking at aud$ table the userid column records failed logon user name as well. I believe there must be a way to collect that information.