ORA-28000: the account is locked error getting frequently

Question

I am facing this error given below :

 ORA-28000: the account is locked 

Is this a DB Issue ? Whenever I unlock the user account using the alter SQL query, that is ALTER USER username ACCOUNT UNLOCK, it will be temporarily OK.

Then after sometime the same account gets locked again. The database is using oracle XE version. Does anybody else have the same issue?

Answer 1

One of the reasons of your problem could be the password policy you are using.

And if there is no such policy of yours then check your settings for the password properties in the DEFAULT profile with the following query:

SELECT resource_name, limit
FROM dba_profiles 
WHERE profile = 'DEFAULT'
AND resource_type = 'PASSWORD';

And If required, you just need to change the PASSWORD_LIFE_TIME to unlimited with the following query:

ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED;

And this Link might be helpful for your problem.

Answer 2

Here other solution to only unlock the blocked user. From your command prompt log as SYSDBA:

sqlplus "/ as sysdba"

Then type the following command:

alter user <your_username> account unlock;

Answer 3

Unlock the particular user account with username:

ALTER USER [USER_NAME] ACCOUNT UNLOCK;

Unlock all the user accounts

SELECT 'alter user ' || username || ' account unlock;' FROM dba_users;

Answer 4

Login to SQL Plus client on the oracle database server machine.

enter user-name: system

enter password: password [Only if, if you have not changed your default password while DB installation]

press enter. after which, you will be seeing the connection status.

Now,

SQL> ALTER USER [USER_NAME] ACCOUNT UNLOCK;

press enter.

you will be seeing message: user altered.

Now try login with the user name on db client[sqldeveloper].

Answer 5

Way to unlock the user :

$ sqlplus  /nolog
SQL > conn sys as sysdba
SQL > ALTER USER USER_NAME ACCOUNT UNLOCK;

and open new terminal

SQL > sqlplus / as sysdba
connected
SQL > conn username/password  //which username u gave before unlock

• it will ask new password:password
• it will ask re-type password:password
• press enter you will get logged in

Answer 6

This issue produced due to exceeded the FAILED_LOGIN_ATTEMPTS, so we can get that from

SELECT resource_name, limit FROM dba_profiles
WHERE profile = 'DEFAULT' AND resource_type = 'PASSWORD';

Then we can get users states which means account is locked or open

SELECT * FROM DBA_USERS;

If the particular user account locked, you can altered that to UNLOCK by

ALTER USER {Account} ACCOUNT UNLOCK;

And if you want to increase the number of FAILED_LOGIN_ATTEMPTS, you can use

ALTER PROFILE DEFAULT LIMIT FAILED_LOGIN_ATTEMPTS UNLIMITED;

Answer 7

In my case (i'm using container) it was because of APEX_PUBLIC_USER was locked. Strange, because i'm starting container with -e IGNORE_APEX=TRUE.

Answer 8

Check the PASSWORD_LOCK_TIME parameter. If it is set to 1 then you won't be able to unlock the password for 1 day even after you issue the alter user unlock command.

Answer 9

I have faced this similar issue and resolved it by using following steps :

1. Open windows command prompt.
2. Login using the command sqlplus "/ as sysdba"
3. Then executed the command alter user HR identified by password account unlock
   
   Please note, the password is the password that I have used.
   
   By using above steps you can connect to Oracle Database as user HR with the password password.

Answer 10

Solution 01

Account Unlock by using below query :

SQL> select USERNAME,ACCOUNT_STATUS from dba_users where username='ABCD_DEV';    
USERNAME             ACCOUNT_STATUS
-------------------- --------------------------------
ABCD_DEV       LOCKED

SQL> alter user ABCD_DEV account unlock;    
User altered.

SQL> select USERNAME,ACCOUNT_STATUS from dba_users where username='ABCD_DEV';    
USERNAME             ACCOUNT_STATUS
-------------------- --------------------------------
ABCD_DEV       OPEN

Solution 02

Check PASSWORD_LIFE_TIME parameter by using below query :

SELECT resource_name, limit FROM dba_profiles WHERE profile = 'DEFAULT' AND resource_type = 'PASSWORD';

RESOURCE_NAME                    LIMIT
-------------------------------- ------------------------------
FAILED_LOGIN_ATTEMPTS            10
PASSWORD_LIFE_TIME               10
PASSWORD_REUSE_TIME              10
PASSWORD_REUSE_MAX               UNLIMITED
PASSWORD_VERIFY_FUNCTION         NULL
PASSWORD_LOCK_TIME               1
PASSWORD_GRACE_TIME              7
INACTIVE_ACCOUNT_TIME            UNLIMITED

Change the parameter using below query

ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED;