How
to resolve the issue of a user account being continuously locked in Oracle
Database?
To resolve the issue of a user account being continuously locked in Oracle Database, follow these steps:
1. Identify the cause: Check the database
logs or audit trails to determine what is causing the account to be locked.
Look for any failed login attempts or suspicious activity.
2. Reset the account: If there are no
security concerns, reset the locked user account password. This can be done
using the ALTER USER statement in Oracle SQL.
3. Check for application or script issues:
Review any applications or scripts that interact with the user account. Ensure
that they are using the correct credentials and not causing repeated login
failures.
4. Verify password policies: Make sure that
the account's password meets the password complexity requirements defined in
the Oracle Database. Adjust the password policies if necessary. '
5. Monitor and investigate: Continuously monitor
the account for any further lockouts. If the issue persists, investigate
further by checking for any potential security breaches or incorrect
configuration settings.
6. Seek Oracle support: If the issue remains
unresolved, it is recommended to seek assistance from Oracle support or consult
with a database administrator who specializes in Oracle Database. By following
these steps, you should be able to address the issue of a user account being
continuously locked in Oracle Database.
Here’s A Practical Case
(Example):
Sometimes, when changing the username and
password in both the application and the database, we face the issue of the
account being frequently locked (timed out). Our account is locked frequently
with error ORA-28000: the account is locked.
Cause: Our application is tried to connect with wrong password then its locked.
The user profile reached to maximum number of times specified by the user’s
profile parameter FAILED_LOGIN_ATTEMPTS.
Note:
1.
The PASSWORD_LOCK_TIME
= value (example 1)
The table DBA_USERS.ACCOUNT_STATUS
show value “LOCKED(TIMED)” whenever the number of failed login attempts is >
FAILED_LOGIN_ATTEMPTS.
2.
The PASSWORD_LOCK_TIME
= unlimited:
The table DBA_USERS.ACCOUNT_STATUS
show value “LOCKED” whenever the number of failed login attempts is greater
than > FAILED_LOGIN_ATTEMPTS.
Solution:
1. Check the User default profile
col username for a10
col account_status for a16
col profile for a20
SELECT username,
account_status,
lock_date,
profile
FROM dba_users
WHERE username LIKE
'%APEX%';
USERNAME ACCOUNT_STATUS LOCK_DATE PROFILE
---------- ---------------- ---------
--------------------
APEX_LISTENER OPEN DEFAULT
APEX_PUBLIC_USER OPEN DEFAULT
APEX_REST_PUBLIC_USER OPEN DEFAULT
APEX_220100 OPEN DEFAULT
Privacy Settings
2. Check the setting of PASSWORD_LOCK_TIME
and FAILED_LOGIN_ATTEMPTS in profile.
col resource_name for a23
col limit for a20
SELECT resource_name, limit
FROM dba_profiles WHERE profile = 'DEFAULT' and resource_name in ('PASSWORD_LOCK_TIME','FAILED_LOGIN_ATTEMPTS');
RESOURCE_NAME LIMIT
----------------------- --------------------
FAILED_LOGIN_ATTEMPTS 10
PASSWORD_LOCK_TIME 1
Note: By default this values are set.
3. you have two options first create new
profile or change the DEFAULT Porfile.
-- I am going with changing the same profile.
alter profile default limit PASSWORD_LOCK_TIME unlimited;
alter profile default limit FAILED_LOGIN_ATTEMPTS unlimited;
4. Unlock the user account (one by one).
alter user APEX_LISTENER
account unlock;
alter user APEX_PUBLIC_USER
account unlock;
alter user
APEX_REST_PUBLIC_USER account unlock;
alter user APEX_220100
account unlock;
User altered.
User altered.
User altered.
User altered.
-- unlock locked users (bulk option)
DECLARE
BEGIN
FOR lr_lok
IN (SELECT username
FROM dba_users
WHERE username LIKE
'%APEX%' AND account_status = 'LOCKED')
LOOP
EXECUTE IMMEDIATE 'ALTER
USER '
|| lr_lok.username
|| ' ACCOUNT UNLOCK';
END LOOP;
END;
5. Hopefully, this will fix your problem but
if you want alter profile for only your user then you create new profile.
ALTERNATIVELY ..... YOU CAN CREATE A NEW PROFILE AND ASSIGN IT TO THE IMPACTED
ACCOUNT(S):
1. Let see other method of creating new profile
with all unlimited limits
CREATE PROFILE
USER_UNLIMITED LIMIT
COMPOSITE_LIMIT UNLIMITED
PASSWORD_LIFE_TIME
UNLIMITED
PASSWORD_REUSE_TIME
UNLIMITED
PASSWORD_REUSE_MAX
UNLIMITED
PASSWORD_VERIFY_FUNCTION
NULL
PASSWORD_LOCK_TIME
UNLIMITED
PASSWORD_GRACE_TIME
UNLIMITED
FAILED_LOGIN_ATTEMPTS
UNLIMITED;
2. Assign that profile to user.
Alter user APEX_LISTENER
profile USER_UNLIMITED;
3. Unlock the user account;
Alter user APEX_LISTENER
account unlock;
User altered.
——————————
In this post, we have successfully resolved
the user account locking issue in Oracle. Our team of experts swiftly addressed
the problem, ensuring that users no longer experience any inconvenience due to
their accounts being locked. Your organization can now enjoy uninterrupted
access and enhanced security measures provided by our effective resolution.
——————————
Moysekou,
11/12/2023 21h09mn - Waterloo, Belgium
#database #technologies #tech #digital
#oracle # intech #plsql
#innovation
