We often come across the error ORA-28000 the account is locked in day-to-day activities. This can happen with developers and DBA.
Reasons
This can happen due to multiple reasons
a. Oracle DBA has purposely locked the account
alter user <username> account lock; select status from dba_users where username='&1';
b. The wrong password has been attempted many times resulting in locking. The number of FAILED_LOGIN_ATTEMPTS can be found using the below query
select username , profile from dba_users where username='&1'; ----- ------ SCOTT TECH_PW select * from dba_profiles where profile='&1' and resource_name='FAILED_LOGIN_ATTEMPTS'; SQL> select RESOURCE_NAME,resource_type,LIMIT from dba_profiles where PROFILE='TECH_PW' ; RESOURCE_NAME RESOURCE LIMIT ----------- ------- ----- COMPOSITE_LIMIT KERNEL DEFAULT SESSIONS_PER_USER KERNEL DEFAULT CPU_PER_SESSION KERNEL DEFAULT CPU_PER_CALL KERNEL DEFAULT LOGICAL_READS_PER_SESSION KERNEL DEFAULT LOGICAL_READS_PER_CALL KERNEL DEFAULT IDLE_TIME KERNEL DEFAULT CONNECT_TIME KERNEL DEFAULT PRIVATE_SGA KERNEL DEFAULT FAILED_LOGIN_ATTEMPTS PASSWORD 5 PASSWORD_LIFE_TIME PASSWORD 90 PASSWORD_REUSE_TIME PASSWORD UNLIMITED PASSWORD_REUSE_MAX PASSWORD 10 PASSWORD_VERIFY_FUNCTION PASSWORD DEFAULT PASSWORD_LOCK_TIME PASSWORD DEFAULT PASSWORD_GRACE_TIME PASSWORD DEFAULT Default values can be found by querying the default profile SQL> select RESOURCE_NAME,resource_type,LIMIT from dba_profiles where PROFILE='DEFAULT'; RESOURCE_NAME RESOURCE LIMIT ------------- ------- ------ COMPOSITE_LIMIT KERNEL UNLIMITED SESSIONS_PER_USER KERNEL UNLIMITED CPU_PER_SESSION KERNEL UNLIMITED CPU_PER_CALL KERNEL UNLIMITED LOGICAL_READS_PER_SESSION KERNEL UNLIMITED LOGICAL_READS_PER_CALL KERNEL UNLIMITED IDLE_TIME KERNEL UNLIMITED CONNECT_TIME KERNEL UNLIMITED PRIVATE_SGA KERNEL UNLIMITED FAILED_LOGIN_ATTEMPTS PASSWORD UNLIMITED PASSWORD_LIFE_TIME PASSWORD UNLIMITED PASSWORD_REUSE_TIME PASSWORD UNLIMITED PASSWORD_REUSE_MAX PASSWORD UNLIMITED PASSWORD_VERIFY_FUNCTION PASSWORD NULL PASSWORD_LOCK_TIME PASSWORD UNLIMITED PASSWORD_GRACE_TIME PASSWORD UNLIMITED
We can also find where all failed login attempts happened by enabling auditing
audit session whenever not successful; select OS_USERNAME,USERNAME,USERHOST,to_char(timestamp,'MM-DD-YYYY HH24:MI:SS'), returncode from dba_audit_trail where returncode > 0
Solution
In case this does not happen purposely, we can solve the problem using the below approach accordingly
(1) Unlock the account using the below command
alter user <username> account unlock;
Before executing the above query make sure the reason for the error is sorted out. Otherwise, again the error will happen.
(2) If the reason for the error is unknown and Till the issue is resolved, we can create another profile with unlimited FAILED_LOGIN_ATTEMPTS
SQL> CREATE PROFILE TECH_TMP LIMIT FAILED_LOGIN_ATTEMPTS UNLIMITED ;
And Assign it to the user who is experiencing the issue
alter user scott profile TECH_TMP; alter user scott account unlock;
Once the cause is established and we know the source from where the invalid password is coming, we can change the user profile to the previous one and drop the newly created profile
alter user scott profile TECH_PW; drop profile TECH_TMP;
Hope you like this post on ORA-28000 the account is locked and it will help in your day-to-day activities. Please do provide feedback on it
Related Articles
ORA-01017: invalid username/password; logon denied
How to login as user without changing the password in Oracle database
Create User in Oracle
How to create Users and Roles in Oracle database 12c
ORA-00904
ORA-28002
Password Version in oracle
https://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_4003.htm
Leave a Reply