How to Trace Oracle Failed Logon Attempts with a Logon Trigger

Monitoring failed logon attempts in Oracle databases is crucial for security and auditing purposes. While Oracle’s auditing feature captures this information, it can be time-consuming and complex to configure and analyze. In this blog post, we will explore an alternative approach using an Oracle Logon trigger to track failed logon attempts and store the relevant information in a log table. This method provides a streamlined way to capture and analyze failed logon attempts without the need for extensive auditing configurations.

Creating the Log Table: To begin, we need to create a log table that will store the information about failed logon attempts. Here is an example of a log table

CREATE TABLE FAILED_LOGONS_TABLE2 (
   ip_address          VARCHAR2(255),
   machine             VARCHAR2(100),
   terminal            VARCHAR2(255),
   os_username         VARCHAR2(255),
   username            VARCHAR2(60),
   module              VARCHAR2(255),
   extended_timestamp  DATE
);

This table includes columns to capture the IP address, machine name, terminal, OS username, attempted username, module, and the timestamp of the failed logon attempt.

Creating the Logon Trigger: Next, we will create an Oracle Logon trigger that will be fired whenever a failed logon attempt occurs. The trigger will extract the relevant information from the user environment context and insert it into the log table. Here is

CREATE OR REPLACE TRIGGER LOGON_DENIED_TO_ALERT2
AFTER SERVERERROR ON DATABASE
WHEN (ora_server_error(1) = 1017)
BEGIN
   INSERT INTO FAILED_LOGONS_TABLE2 (
      ip_address,
      machine,
      terminal,
      os_username,
      username,
      module,
      extended_timestamp
   )
   VALUES (
      SYS_CONTEXT('USERENV', 'IP_ADDRESS'),
      SYS_CONTEXT('USERENV', 'HOST'),
      SYS_CONTEXT('USERENV', 'TERMINAL'),
      SYS_CONTEXT('USERENV', 'OS_USER'),
      USER,
      SYS_CONTEXT('USERENV', 'MODULE'),
      SYSDATE
   );
   COMMIT;
END failed_logon_trg;

The trigger is set to fire after a server error occurs, specifically when an ORA-01017 error (invalid username/password; logon denied) is raised. It retrieves the necessary information from the user environment context using the SYS_CONTEXT function and inserts it into the log table.

Testing the Logon Trigger: SQL> sqlplus dba/welcome1@dbadeeds.com

You will receive an ORA-01017 error indicating an invalid username/password. Behind the scenes, the logon trigger will be triggered, and the relevant information will be inserted into the log table.

Analyzing the Failed Logon Attempts: Once failed logon attempts have been captured in the log table, you can query the table to analyze the data. For example:

SELECT DISTINCT *
FROM FAILED_LOGONS_TABLE2;

This query retrieves all the distinct records from the log table, providing information about the failed logon attempts, including the IP address, machine, terminal, OS username, attempted username, module, and timestamp.

Conclusion: By utilizing an Oracle Logon trigger, we have demonstrated a simplified approach to track failed logon attempts and store the relevant information in a dedicated log table. This method offers an alternative to Oracle’s auditing feature, providing a streamlined solution for capturing and analyzing failed logon attempts. Remember to adjust the log table and trigger code to meet your specific requirements and security policies.

DBADEEDS