Saturday, May 24, 2008

ORA-28000: the account is locked

Error Description:
-------------------------

Whenever you try connect to connect a user it failed with error message.
SQL> CONN INDIA/USA
ERROR:
ORA-28000: the account is locked
Warning: You are no longer connected to ORACLE.


And immediately the current user is disconnected from oracle.

Cause of The Problem:
-------------------------------

The user account is locked. This may be explicitly issued by dba user Like,
SQL> ALTER USER INDIA ACCOUNT UNLOCK; or it may be locked internally based on the profile resource limit. In order to know more about profile and resource limit have a look at,
User Resource Limit
Profile in Oracle

For example if I set FAILED_LOGON_ATTEMPTS of the assigned profile to a user set as 10 (which is default in 10.2)then after failed logon attempt 10 times the user account will be automatically locked.

Solution of The problem:
-------------------------------

Try to unlock the user account as a dba user by ALTER USER username ACCOUNT UNLOCK;
SQL> CONN INDIA/USA
ERROR:
ORA-28000: the account is locked
Warning: You are no longer connected to ORACLE.

SQL> CONN ARJU/A
Connected.

SQL> ALTER USER INDIA ACCOUNT UNLOCK;

User altered.

SQL> CONN INDIA/T

Connected.

There may be the reason that user is locked based on imposed resource limit. For example from application wrong password is set and for that the account is locked. You then have to know the assigned profile of the user by,
SQL> SELECT PROFILE FROM DBA_USERS WHERE USERNAME=
2 'INDIA';


PROFILE
----------
DEFAULT

SQL> SELECT RESOURCE_NAME,LIMIT FROM DBA_PROFILES WHERE PROFILE='DEFAULT';


RESOURCE_NAME LIMIT
-------------------------------- ----------------------------------------
COMPOSITE_LIMIT UNLIMITED
SESSIONS_PER_USER UNLIMITED
CPU_PER_SESSION UNLIMITED
CPU_PER_CALL UNLIMITED
LOGICAL_READS_PER_SESSION UNLIMITED
LOGICAL_READS_PER_CALL UNLIMITED
IDLE_TIME UNLIMITED
CONNECT_TIME UNLIMITED
PRIVATE_SGA UNLIMITED
FAILED_LOGIN_ATTEMPTS 10
PASSWORD_LIFE_TIME UNLIMITED
PASSWORD_REUSE_TIME UNLIMITED
PASSWORD_REUSE_MAX UNLIMITED
PASSWORD_VERIFY_FUNCTION NULL
PASSWORD_LOCK_TIME UNLIMITED
PASSWORD_GRACE_TIME UNLIMITED

16 rows selected.


Here FAILED_LOGIN_ATTEMPTS resource is set to 10. You can make it unlimited by

SQL> ALTER PROFILE DEFAULT LIMIT FAILED_LOGIN_ATTEMPTS UNLIMITED;
Profile altered.

And then unlock the account.
SQL> ALTER USER INDIA ACCOUNT UNLOCK;

Related Documents
---------------------------

User Resource Limit in Oracle
Profile in Oracle

No comments: