How to Monitor and Identify Failed Login Attempts in Oracle

Ensuring the security of an Oracle database is a critical responsibility for any Database Administrator (DBA). Monitoring failed login attempts and identifying locked accounts are essential steps to prevent unauthorized access and maintain system integrity. This guide will walk you through auditing failed login attempts and pinpointing the source of locked accounts in Oracle.

Real-World Scenario

Imagine you are an Oracle DBA at a large organization. Recently, users have reported difficulties logging into their accounts, with some accounts being locked after multiple failed login attempts. As the DBA, it’s your responsibility to investigate, identify the root cause, and resolve the issue promptly.

Understanding Oracle Return Codes

Oracle provides specific return codes to help diagnose login-related issues:

• Return Code 1017: Indicates an invalid login attempt, typically caused by incorrect credentials such as a wrong username or password.
• Return Code 28000: Signifies that an account has been locked, often due to exceeding the allowed number of failed login attempts as defined by your database security policy.

SQL Query to Audit Failed Login Attempts

The following SQL query can help you track failed login attempts and identify locked accounts. This query retrieves details such as the username, host, terminal, timestamp, and return codes for failed login events.

SET PAGESIZE 1299
SET LINESIZE 299
COLUMN username FORMAT A15
COLUMN userhost FORMAT A13
COLUMN timestamp FORMAT A39
COLUMN terminal FORMAT A23
SELECT 
    username,
    userhost,
    terminal,
    timestamp,
    returncode
FROM 
    dba_audit_session
WHERE 
    username = '&USER_NAME' 
    AND returncode IN (1017, 28000);

Explanation of the Query

• username: The user attempting to log in.
• userhost: The host from which the login attempt originated.
• terminal: The terminal or device used during the login attempt.
• timestamp: The date and time of the login attempt.
• returncode: The return code indicating the outcome of the login attempt (e.g., 1017 or 28000).

Steps to Use the Query

1. Replace &USER_NAME with the username you want to audit. For example, to check attempts for the user HR, enter HR when prompted.
2. Look for returncode = 1017 for invalid login attempts and returncode = 28000 for locked accounts.

Conclusion

Regularly auditing failed login attempts and identifying locked accounts is an essential practice for securing your Oracle database. By leveraging the provided SQL query, you can quickly address unauthorized access attempts and assist users in unlocking their accounts. This proactive approach not only protects sensitive data but also enhances user experience by ensuring timely resolution of account issues.