unable to collect username during fail logon to the database
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
IF ora_is_servererror( 01017 ) THEN
v_username := SYS_CONTEXT( 'USERENV', 'SESSION_USER');
WHEN others THEN
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?
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.
this space intentionally left blank
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.
Click Here to Expand Forum to Full Width