Troubleshooting a locked system user

[patrickbachmann]

Hi folks,

We recently had an issue where a system user in production queries data from HANA and the ID got locked for 3 invalid attempts.  The question we are asking is how this could have happened because this is production system and nobody should be manually connecting with this user ID.  I'm trying to find a system view that might show invalid connection attempts and if there could be a way to find out what IP address the user was trying to logon from.  ie: I'm wondering if somebody was trying to use this ID that should not be.

Thanks,

-Patrick

[lbreddemann · Accepted solution]

Hi Patrick,

to see the single failed attempts you will need to setup auditing.

- Lars

[patrickbachmann · Accepted solution]

Thanks Lars,

That's actually what we did AFTER this happened so we are monitoring it.  The strange thing to me is that when it happened I immediately ran;

select * from "SYS"."INVALID_CONNECT_ATTEMPTS" where USER_NAME = 'XYZ'

And it didn't show any invalid attempts for that user this day even though their account seemed to be suddenly locked for invalid attempts.  It showed 3 in the past but all were well prior to this day.

-Patrick

[patrickbachmann · Accepted solution]

PS:  The following NEXT day (June 3) this same query indeed showed 4 invalid attempts for this user.  But it was a day later.  So wondering if somehow this table is not immediately updated?

[patrickbachmann · Accepted solution]

Also curious on this above query.  If the SUCCESSFUL_CONNECT_TIME is Jun 3 and the next column says INVALID_CONNECT_ATTEMPTS = 4 can I assume all the invalid connections were also on Jun 3?  It's not clear to me in this table.

[lbreddemann · Accepted solution]

As much as I understand this view, it gives you the total number of failed logon attempts that happened during the time between the current line's successful connection and the last successful connection before that.

So, in your case I'd say, you reactivated the user, it connected successfully again and only then you got the number of invalid connect attempts.

There's however no data on when - during the period from the last successful logon to the currrent one - the failed logons occured.

Makes sense?

- Lars

[patrickbachmann · Accepted solution]

Ahh ok, that makes sense.  Thanks for the explanation.

-Patrick