How to Resolve ORA-28000: The account is locked

ORA-28000

ORA-28000 means that the account that you tried to use is locked because some privileged user disabled the ability of connection, or attempt connections with wrong passwords exceeded the limit.

SQL> conn hr/hr
ERROR:
ORA-28000: The account is locked.

Please note that, schema objects and data of the user can still be used by others, even though the account is locked. In some cases, it could be a good tactic for sharing accounts which do not need logging.

Solution

You can revert the status by unlocking the account. First of all, logging as a privileged user.

SQL> conn / as sysdba
Connected.

And then we can check the status of the account.

SQL> column account_status forma a20;
SQL> select account_status, lock_date from dba_users where username = 'HR';

ACCOUNT_STATUS       LOCK_DATE
-------------------- ---------
LOCKED               23-DEC-19

There're some ways to solve ORA-28000.

1. ALTER USER ACCOUNT UNLOCK
2. PASSWORD_LOCK_TIME
3. FAILED_LOGIN_ATTEMPTS

ALTER USER ACCOUNT UNLOCK

A simple unlocking can solve ORA-28000 by privileged users. If you can't do it, please ask DBA to do that.

SQL> alter user hr account unlock;

User altered.

In reality, DBA may lock some inactive accounts for a period of time before actually dropping them. Therefore, if your account is locked, please inform your DBA as soon as possible.

PASSWORD_LOCK_TIME

PASSWORD_LOCK_TIME means that it is allowable to unlock the account automatically after a period of time, if the account lock was triggered by FAILED_LOGIN_ATTEMPTS.

In such situation, you may set a shorter PASSWORD_LOCK_TIME to release the account lock automatically, say 5 minutes.

SQL> alter profile default limit password_lock_time 5/1440;

Profile altered.

In this case, we set the lock time to a shorter period, 5 minutes.

By the way, a locked account which is waiting for automatic unlock has ASTATUS 4.

FAILED_LOGIN_ATTEMPTS

FAILED_LOGIN_ATTEMPTS means that how many times of failed login attempts are allowed before locking the account. Yes, the account will be locked if the number of consecutive failed attempts meets the value.

When ORA-28000 becomes too often, you may also consider to remove the restriction of FAILED_LOGIN_ATTEMPTS by setting it to UNLIMITED.

SQL> alter profile default limit failed_login_attempts unlimited;

Profile altered.

Please note that, some native accounts are initially locked by the database to protect them from unauthorized access. This is designed scheme.

For expired password of accounts, you shall see ORA-28001: the password has expired.