What Causes ORA-28000: The Account Is Locked Error and How to Fix It?

 

Introduction

The ORA-28000: The Account Is Locked error in Oracle Database occurs when a user account is locked, preventing login attempts. This error can disrupt critical operations, such as application access or administrative tasks, and is often caused by security policies or user errors. In this detailed tutorial, we’ll explore the causes of ORA-28000, provide actionable steps to fix it, and share preventive measures to avoid future lockouts. With real-life scenarios, practical scripts, and best practices, this guide is designed for beginners and experienced DBAs alike to ensure uninterrupted access to your Oracle Database.

---

What Causes ORA-28000: The Account Is Locked?

The ORA-28000 error is triggered when a user account is locked, typically due to the following reasons:

1. Excessive Failed Login Attempts: Too many incorrect password entries exceed the limit defined in the user’s profile.

2. Profile Settings: The user’s profile has a FAILED_LOGIN_ATTEMPTS limit, locking the account after reaching it.

3. Manual Account Locking: An administrator explicitly locks the account for security or maintenance purposes.

4. Password Expiry: The account is locked due to an expired password if PASSWORD_LIFE_TIME is set.

5. Inactivity Timeout: Some systems lock accounts after prolonged inactivity, depending on profile settings.

6. Application Misconfiguration: Applications sending incorrect credentials repeatedly, triggering lockouts.

Example Error Message:

ORA-28000: The account is locked

Real-Life Scenario: An e-commerce application fails to connect to the database, throwing ORA-28000 for the APP_USER account. Users cannot access the product catalog, and the DBA suspects repeated incorrect login attempts from a misconfigured application.

---

Step 1: Diagnosing ORA-28000

To resolve ORA-28000, first identify why the account is locked and gather relevant details.

1.1 Check Account Status

Verify the account’s status using the DBA_USERS view.

Query:

SELECT 
    username,
    account_status,
    lock_date,
    expiry_date
FROM 
    dba_users
WHERE 
    username = 'APP_USER';

Sample Output:

USERNAME  ACCOUNT_STATUS  LOCK_DATE           EXPIRY_DATE
--------  --------------  ------------------- -------------------
APP_USER  LOCKED          01-SEP-2025 10:00   NULL

Observation: The ACCOUNT_STATUS is LOCKED, indicating the account is inaccessible.

1.2 Check Profile Settings

Review the user’s profile to identify lockout policies.

Query:

SELECT 
    p.profile,
    p.resource_name,
    p.limit
FROM 
    dba_profiles p
WHERE 
    p.resource_name IN ('FAILED_LOGIN_ATTEMPTS', 'PASSWORD_LOCK_TIME')
    AND p.profile = (SELECT profile FROM dba_users WHERE username = 'APP_USER');

Sample Output:

PROFILE  RESOURCE_NAME           LIMIT
-------  ----------------------  ------
DEFAULT  FAILED_LOGIN_ATTEMPTS  3
DEFAULT  PASSWORD_LOCK_TIME     1

Explanation:

• FAILED_LOGIN_ATTEMPTS = 3: Locks the account after 3 failed login attempts.

• PASSWORD_LOCK_TIME = 1: Locks the account for 1 day (in days).

1.3 Check Failed Login Attempts

Investigate recent failed login attempts in the audit trail (if auditing is enabled).

Query:

SELECT 
    username,
    action_name,
    returncode,
    timestamp
FROM 
    dba_audit_trail
WHERE 
    username = 'APP_USER'
    AND action_name = 'LOGON'
    AND returncode = 1017 -- ORA-01017: invalid username/password
ORDER BY 
    timestamp DESC;

Real-Life Finding: The audit trail shows multiple failed login attempts for APP_USER due to an incorrect password in the application configuration.

---

Step 2: Resolving ORA-28000

Once the cause is identified, apply these solutions to unlock the account and restore access.

2.1 Unlock the Account

Unlock the account using the ALTER USER command.

Command:

ALTER USER APP_USER ACCOUNT UNLOCK;

Verification:

SELECT username, account_status FROM dba_users WHERE username = 'APP_USER';

Sample Output:

USERNAME  ACCOUNT_STATUS
--------  --------------
APP_USER  OPEN

Pros:

• Quick and simple solution.

• Restores access immediately.

Cons:

• Does not address the root cause (e.g., incorrect passwords).

• Requires DBA privileges.

Real-Life Fix: In the e-commerce system, unlocking APP_USER restored application access within minutes.

2.2 Reset the Password

If the account was locked due to failed login attempts, resetting the password may be necessary.

Command:

ALTER USER APP_USER IDENTIFIED BY new_password;

Action:

• Update the application configuration with the new password.

• Test the connection:

sqlplus APP_USER/new_password@ORCL

Pros:

• Resolves issues caused by incorrect credentials.

• Enhances security with a new password.

Cons:

• Requires coordination with application teams.

• May disrupt connections if not updated everywhere.

2.3 Modify Profile Settings

Adjust the profile to relax lockout policies if they are too restrictive.

Example: Increase FAILED_LOGIN_ATTEMPTS and reduce PASSWORD_LOCK_TIME.

ALTER PROFILE DEFAULT LIMIT
    FAILED_LOGIN_ATTEMPTS 10
    PASSWORD_LOCK_TIME 0.0417; -- 1 hour (1/24 days)

Pros:

• Reduces frequent lockouts.

• Balances security and usability.

Cons:

• Weakening security policies may not be suitable for all environments.

• Requires careful testing.

Best Practice: Set FAILED_LOGIN_ATTEMPTS to a reasonable value (e.g., 5–10) and PASSWORD_LOCK_TIME to a short duration (e.g., 1 hour).

2.4 Fix Application Configuration

If the lockout is due to application errors, correct the credentials or connection logic.

Example:

• Check the application’s connection string (e.g., in a configuration file like app.config):

<connectionStrings>
    <add name="OracleDB" connectionString="User Id=APP_USER;Password=wrong_password;Data Source=ORCL"/>
</connectionStrings>

• Update to the correct password:

<connectionStrings>
    <add name="OracleDB" connectionString="User Id=APP_USER;Password=new_password;Data Source=ORCL"/>
</connectionStrings>

Real-Life Fix: The e-commerce application had an outdated password in its configuration. Updating it prevented further ORA-28000 errors.

---

Step 3: Preventing ORA-28000

Prevent future lockouts with these strategies.

3.1 Implement Strong Password Management

Enforce secure but manageable password policies.

Example: Set password life and reuse limits.

ALTER PROFILE DEFAULT LIMIT
    PASSWORD_LIFE_TIME 180 -- 180 days
    PASSWORD_REUSE_MAX UNLIMITED;

Pros:

• Reduces lockouts from expired passwords.

• Maintains security standards.

Cons:

• Users may need to update passwords periodically.

• Requires user communication.

3.2 Enable Account Unlock Automation

Automatically unlock accounts after a short period by setting a low PASSWORD_LOCK_TIME.

Example:

ALTER PROFILE DEFAULT LIMIT PASSWORD_LOCK_TIME 0.0139; -- 20 minutes

Pros:

• Minimizes DBA intervention.

• Improves user experience.

Cons:

• May reduce security if set too low.

3.3 Monitor and Audit Logins

Enable auditing to track failed login attempts and identify patterns.

Enable Auditing:

AUDIT SESSION BY APP_USER;

Check Audit Trail:

SELECT 
    username,
    action_name,
    returncode,
    timestamp
FROM 
    dba_audit_trail
WHERE 
    username = 'APP_USER'
    AND action_name = 'LOGON';

Pros:

• Identifies sources of failed logins (e.g., misconfigured applications).

• Enhances security monitoring.

Cons:

• Increases storage for audit data.

• Requires auditing setup.

3.4 Educate Users and Application Teams

Train users and developers to:

• Use correct credentials.

• Update application configurations after password changes.

• Avoid sharing accounts that could lead to lockouts.

---

Step 4: Advanced Troubleshooting

For complex environments, use these advanced techniques:

4.1 Check for Brute-Force Attacks

If lockouts occur repeatedly, investigate potential security threats.

Query:

SELECT 
    username,
    client_identifier,
    COUNT(*) AS failed_attempts
FROM 
    dba_audit_trail
WHERE 
    returncode = 1017
    AND timestamp > SYSDATE - 1
GROUP BY 
    username, client_identifier
ORDER BY 
    failed_attempts DESC;

Action: Block suspicious IP addresses or implement stronger network security.

4.2 Use Oracle Vault

For high-security environments, use Oracle Database Vault to control account access and prevent unauthorized lockouts.

Pros:

• Enhances security with fine-grained access control.

• Prevents accidental or malicious lockouts.

Cons:

• Requires additional licensing.

• Complex to configure.

4.3 Temporary Unlock for Testing

For troubleshooting, temporarily disable lockout policies.

Command:

ALTER PROFILE DEFAULT LIMIT FAILED_LOGIN_ATTEMPTS UNLIMITED;

Action: Restore secure settings after testing:

ALTER PROFILE DEFAULT LIMIT FAILED_LOGIN_ATTEMPTS 5;

Best Practice: Use this only in non-production environments.

---

Pros and Cons of Resolution Techniques

Technique	Pros	Cons
Unlock Account	Quick fix, restores access	Doesn’t address root cause
Reset Password	Resolves credential issues	Requires config updates
Modify Profile	Prevents frequent lockouts	May weaken security
Fix Application Config	Eliminates recurring errors	Needs coordination with teams
Enable Auditing	Identifies lockout causes	Increases storage overhead

---

Best Practices and Standards

1. Set Reasonable Lockout Policies: Balance security and usability with FAILED_LOGIN_ATTEMPTS (5–10) and PASSWORD_LOCK_TIME (20 minutes–1 hour).

2. Monitor Audit Trails: Regularly check DBA_AUDIT_TRAIL for failed logins.

3. Secure Application Credentials: Store passwords securely (e.g., in Oracle Wallet) to prevent misconfiguration.

4. Automate Unlocks: Use short lockout durations for non-critical accounts.

5. Document Changes: Log all account unlocks and password resets for accountability.

6. Test Changes: Validate configuration updates in a test environment.

---

Real-Life Case Study

Scenario: An e-commerce platform’s APP_USER account is locked, causing the product catalog to be inaccessible. Users report ORA-28000 errors.

Actions:

1. Confirmed the account status:

SELECT username, account_status FROM dba_users WHERE username = 'APP_USER';
-- Output: LOCKED

2. Unlocked the account:

ALTER USER APP_USER ACCOUNT UNLOCK;

3. Found repeated failed logins in the audit trail:

SELECT username, returncode, timestamp FROM dba_audit_trail WHERE username = 'APP_USER';

4. Updated the application’s configuration file with the correct password.

5. Adjusted the profile to allow more login attempts:

ALTER PROFILE DEFAULT LIMIT FAILED_LOGIN_ATTEMPTS 10 PASSWORD_LOCK_TIME 0.0139; -- 20 minutes

Result: The application reconnected, and lockouts stopped occurring after fixing the configuration.

---

Conclusion

The ORA-28000: The Account Is Locked error can disrupt database access, but it’s easily resolved by unlocking the account, resetting passwords, and fixing application configurations. Prevent future lockouts by setting reasonable profile policies, enabling auditing, and educating users. Use the provided scripts to diagnose and resolve ORA-28000 efficiently, ensuring your Oracle Database remains accessible and secure.

📘Free Job Preparation Zone 🎯 Visit Free Learning Zone