Author: David Ray
We often find a number of queries about Oracle login problems whereby a user is immediately disconnected from Oracle even if the correct user id, password and host name is entered. The error encountered is as follows
SQL> CONN SCOTT/TIGER
ERROR:
ORA-28000: the account is locked
Warning: You are no longer connected to ORACLE.
Well here are some possible solutions to this problem and today we will discuss how to solve ORA-28000: The Account is Locked error problem.
ora-28000: the account is locked - Account Locked During Installation:
Cause:
One reason of getting ORA-28000 error is that when you install Oracle then all accounts except SYS and SYSTEM accounts are locked by default.
Solution:
You can prevent this problem if you uncheck the lock.
ora-28000: the account is locked - Account Locked by DBA:
Cause:
Another reason of ora-28000: the account is locked error is when the DBA explicitly lock your account.
Solution:
You will have to ask administrator to unlock your account by running below command under DBA privileges.
SQL> ALTER USER SCOTT IDENTIFIED BY TIGER ACCOUNT UNLOCK;
ora-28000: the account is locked – Failed Login Attempts:
Cause:
A common reason of ora-28000: the account is locked error is when it gets locked internally based on the profile resource limit. This is due to failed login attempts with a user for which a profile was defined. In such case the user's profile parameter FAILED_LOGON_ATTEMPTS is set to a specific value. Now if a user enters wrong password consequently for maximum number of times specified in this parameter then the account gets locked.
Solution:
Oracle PASSWORD_LOCK_TIME parameter specifies the number of days an account will be locked after the failed login attempts defined by FAILED_LOGIN_ATTEMPTS. One solution is to wait until you reach the value of PASSWORD_LOCK_TIME and the account gets unlocked automatically but the problem with this solution is that most database administrators set the value of PASSWORD_LOCK_TIME to UNLIMITES.
Another solution is to a DBA to unlock the account.
If your account is locked based on imposed resource limit then you will have to know the assigned profile of the user.
View Profile Assigned to a User:
SELECT PROFILE FROM DBA_USERS
WHERE USERNAME='SCOTT';
PROFILE
----------
DEFAULT View Limits Set for the Profile:
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.
Alter FAILED_LOGIN_ATTEMPTS Parameter:
From the user's profile we can see that the FAILED_LOGIN_ATTEMPTS parameter is set to a value of 10. Now we run below command to make it UNLIMITED.
SQL> ALTER PROFILE DEFAULT LIMIT FAILED_LOGIN_ATTEMPTS UNLIMITED;
Profile altered.
Unlock the Account:
Finally we will unlock the account by running below command.
SQL> ALTER USER SCOTT ACCOUNT UNLOCK; Read Again!!
More Oracle Articles, Database Articles and DBA Tips
Database Security: Step by step guideline
DBA Tips for Speeding Up Large Table Updates!!
The Secrets of Single-Set Aggregates for DML Statements!!
Tricky Oracle Access by IP Address!!
12 U’s for Good Database Design
|
