CODEWITHSUNDEEP
sqloracle-database
gregor
gregor

Reputation: 301

The user is locked. How to find out from whom?

Recently, the user has become very often locked. I suspect this is due to the erroneous input of the username and password, but I don’t know how to find out from which computer this happens. Is it possible to get any information about this (for example, IP or username) due to which the user was blocked?

Oracle Database 10g Release 10.2.0.3.0 - Production

Upvotes: 1

Views: 6674

Answers (1)

MT0
MT0

Reputation: 167972

It may not be that the user has blocked themselves. This question gives several reasons including:

  • An appropriately privileged user could issue the command:

    ALTER USER user_name ACCOUNT LOCK;

  • An appropriately privileged user could issue the command:

    REVOKE CONNECT FROM user_name;

  • If could be that the PASSWORD_LIFE_TIME is exceeded, or both that and PASSWORD_GRACE_TIME is exceeded.

  • User exceeds FAILED_LOGIN_ATTEMPTS

Only 1 of those 4 reasons would have been initiated by the (now) locked user.

The accepted answer to that question states:

Provided audit trail is turned on, then I prefer to use the following to help track down login failures (which is usually the cause of locked accounts):

select * from dba_audit_trail where returncode in (1017, 28000) order by timestamp desc;

returncode is the ORA- error that would be returned from the database: 1017 is "invalid usercode or password" and 28000 is "account is locked".

And then goes on to give more details of how to activate the audit trail if it is not already turned on.

Upvotes: 2

Related Questions