ORA-28000: The account is locked

Introduction

Encountering the ORA-28000: The account is locked error in Oracle databases can be frustrating. This error indicates that a user account has been locked, typically due to too many failed login attempts or security policies enforced by the DBA. In this article, we will explore the causes of this error and provide comprehensive solutions to unlock an Oracle account efficiently.

Understanding ORA-28000 Error

The ORA-28000 error occurs when an Oracle user account is locked. This often happens due to:

• Exceeding the allowed number of failed login attempts (based on FAILED_LOGIN_ATTEMPTS policy).
• Manual locking of the account by a Database Administrator (DBA).
• Expired passwords requiring immediate changes.
• Security settings enforced through Oracle Profile configurations.

Before proceeding with unlocking the account, it is important to determine the cause of the lock.

Cause: The user has entered wrong password consequently for maximum number of times specified by the user’s profile parameter FAILED_LOGIN_ATTEMPTS, or the database administrator has locked the account.

Checking the Locked Accounts in Oracle

To identify which accounts are locked, use the following SQL query:

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

This query lists all accounts with a LOCKED status. If the account is locked due to password attempts, it will appear here.

Unlocking an Oracle Account

There are multiple ways to unlock an Oracle account depending on the underlying cause.

1. Unlocking a User Account via SQL*Plus

If you have DBA privileges, unlocking an account is straightforward. Execute the following command:

ALTER USER username ACCOUNT UNLOCK;

Replace username with the actual locked user’s name. This will instantly unlock the account.

2. Resetting the User Password and Unlocking

If the account is locked due to too many failed login attempts, you may need to reset the password before unlocking it:

ALTER USER username IDENTIFIED BY newpassword ACCOUNT UNLOCK;

Make sure to replace newpassword with a strong, secure password.

3. Modifying the Failed Login Attempts Policy

To prevent frequent account locks, you can modify the FAILED_LOGIN_ATTEMPTS parameter in the profile associated with the user:

1- Check the current profile settings:

SELECT profile, resource_name, limit FROM dba_profiles WHERE resource_name = 'FAILED_LOGIN_ATTEMPTS';

2- Modify the limit (e.g., setting it to 10 attempts):

ALTER PROFILE default LIMIT FAILED_LOGIN_ATTEMPTS 10;

Setting a higher threshold reduces the likelihood of an account getting locked due to incorrect login attempts.

4. Unlocking Using Enterprise Manager

If you prefer a GUI method, use Oracle Enterprise Manager (OEM):

1. Log in to Oracle Enterprise Manager.
2. Navigate to Security > Users.
3. Select the locked user and click Unlock Account.
4. Optionally, reset the password if required.

5. Checking If the Password Has Expired

Sometimes, a locked account could be due to an expired password. You can check this with:

SELECT username, expiry_date FROM dba_users WHERE username = 'username';

If the password is expired, reset it:

ALTER USER username IDENTIFIED BY newpassword;

Preventing Future Account Locks

To avoid repeated occurrences of the ORA-28000 error, consider the following best practices:

1. Adjusting Password Policies

Modify the password policy settings to reduce the chance of frequent locks:

ALTER PROFILE default LIMIT PASSWORD_LIFE_TIME UNLIMITED;

This prevents passwords from expiring unexpectedly.

2. Monitoring Failed Login Attempts

Regularly monitor failed login attempts using the Oracle Audit Trail:

SELECT username, timestamp FROM dba_audit_session WHERE returncode = 1017;

This helps in identifying unauthorized access attempts.

3. Implementing Account Lockout Alerts

Set up alerts to notify DBAs when accounts get locked, enabling faster resolutions.

4. Educating Users on Strong Password Practices

Encourage users to:

• Use complex passwords.
• Store credentials securely.
• Avoid multiple incorrect login attempts.

Conclusion for ORA-28000

The ORA-28000: The account is locked error can be easily resolved using the methods outlined above. Whether unlocking an account via SQL, resetting passwords, or modifying security policies, a proactive approach ensures a secure and efficient Oracle environment.

How to Resolve ORA-011033: ORACLE initialization or shutdown in progress

FAQs

1. Why is my Oracle account locked?

Your Oracle account may be locked due to too many failed login attempts, an expired password, or a DBA manually locking it.

2. How do I check if my Oracle account is locked?

Run the following SQL query:

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

3. How can I prevent my Oracle account from getting locked?

You can increase the failed login attempt limit, disable password expiration, and monitor failed logins using Oracle audit logs.

4. Can a non-DBA user unlock their account?

No, only a DBA or a user with appropriate privileges can unlock an Oracle account.