0

I belive Oracle audit cannot track it since the user actually did not connect to the database. Or can audit help? I mean, can Oracle audit track without the user login in the database?

Someone or some application is trying to connect many times in a account and is causing LOCKED(TIMED) (too many tries to connect with the wrong password, definied in user profile). How can I find out which machine or IP and OS user those tries comes?

1
  • Auditing can most definitely capture bad login attempts, including details of the client system and remote user. That's largely the point of it. You may also be able to get additional details from the listener logs.
    – pmdba
    Mar 21, 2023 at 10:58

1 Answer 1

2

Yes, Oracle's auditing does track failed logins. If logon auditing is enabled, you can query it:

SELECT *
  FROM dba_audit_trail
 WHERE returncode != 0
   AND action_name = 'LOGON'

A returncode of != 0 will mean logon failure, which includes bad password attempts.

You can also create your own "after servererror on database" trigger and record ORA- errors thrown to clients. Bad password attempts would throw an error that would be picked up by such a trigger. The username you'd have to get from SYS_CONTEXT('USERENV','AUTHENTICATED_IDENTITY'). But regular auditing is probably easier for you.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.