Audit Failed Logon Attempts in Oracle Database.

Failed logon attempts can cause account locks, resulting in disruptions for users and increased workload for DBAs. Identifying the source of failed logon attempts is crucial for troubleshooting and preventing further issues. Oracle’s audit functionality provides an efficient way to track and log these attempts.

Background

Failed logon attempts cannot be tracked using logon triggers since they do not execute for failed logons. Instead, Oracle audit trails are used to capture this information. This article demonstrates how to configure auditing specifically for failed logon attempts, while minimizing overhead by disabling unnecessary audit options.

Important Note

Enabling auditing may increase database load. Therefore, it is recommended to evaluate the impact in a non-production environment before deploying it in production systems.

Steps to Audit Failed Logon Attempts

1. Enable Oracle Audit

To enable auditing, set the audit_trail parameter. This parameter determines where audit records are stored:

SQL> SHOW PARAMETER audit_trail;

NAME          TYPE        VALUE
------------- ----------- -------
audit_trail   string      NONE

Enable auditing based on the database initialization file:

• If using an SPFILE:

  SQL> ALTER SYSTEM SET audit_trail=DB SCOPE=SPFILE;

• If using a PFILE, modify the init.ora file directly.

Restart the database to apply changes:

SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP;

2. Disable Default Privilege Auditing

Oracle 11g and later enable some default audit options when audit_trail is set. To optimize resource usage, generate and execute a script to disable these unnecessary audits:

SQL> SELECT 'noaudit ' || privilege || ';' 
FROM dba_priv_audit_opts 
WHERE user_name IS NULL;

-- Example output:
noaudit ALTER SYSTEM;
noaudit CREATE SESSION;
noaudit CREATE USER;
-- Run these commands to disable default audits

3. Enable Auditing for Failed Logon Attempts

Enable auditing specifically for failed logon attempts with the following command:

SQL> AUDIT CONNECT WHENEVER NOT SUCCESSFUL;

Audit succeeded.

Verify the settings:

SQL> SELECT PRIVILEGE, SUCCESS, FAILURE FROM dba_priv_audit_opts;

PRIVILEGE      SUCCESS    FAILURE
-------------  ---------  ----------
CREATE SESSION NOT SET    BY ACCESS

4. Retrieve Audit Information

Audit information is stored in sys.aud$. Use the dba_audit_session view to query failed logon attempts:

SQL> SELECT os_username, username, userhost, 
TO_CHAR(timestamp, 'MM/DD/YYYY HH24:MI:SS') AS logon_time,
action_name, returncode 
FROM dba_audit_session;

-- Example output:
OS_USERNAME     USERNAME     USERHOST     LOGON_TIME           ACTION_NAME   RETURNCODE
Arvind          oracle       machine1     12/06/2022 13:40:12 LOGON         1017

Note:

• RETURNCODE represents the ORA error code. Common codes include:
  • 1017: Incorrect password
  • 28000: Account locked
  • 1045: Missing connect privilege

5. Disabling Auditing

If auditing is no longer required, disable it with the following command:

SQL> NOAUDIT CONNECT;

Noaudit succeeded.

6. Relocating sys.aud$

To prevent sys.aud$ from consuming space in the system tablespace, move it to a different tablespace using the DBMS_AUDIT_MGMT package:

SQL> EXEC DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_LOCATION(
  audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
  audit_trail_location_value => 'USER_DATA1'
);

SQL> SELECT table_name, tablespace_name FROM dba_tables WHERE table_name = 'AUD$';

TABLE_NAME  TABLESPACE_NAME
----------  ---------------
AUD$        USER_DATA1

7. Cleaning Up sys.aud$

Use the following commands to delete or truncate old audit records:

DELETE FROM sys.AUD$;
TRUNCATE TABLE sys.AUD$;

Conclusion

Auditing failed logon attempts in Oracle provides critical insights for identifying unauthorized access attempts and troubleshooting account lock issues. By enabling focused auditing, managing audit storage, and periodically cleaning up logs, you can ensure optimal performance and security in your database environment.