Tracking Failed Login Attempts

Understanding the Fetch Phase in Oracle SQL

Tracking Failed Login Attempts in Oracle

Quite often, we encounter problems with users getting locked, and neither we nor the application team knows where the failed login attempts originate from. It can be due to a user password change or a refresh of Dev/QA/UAT environments where not all applications have the correct password configured.

There are two common ways to track this:

Using a database trigger
Enabling a database trace

Using a trace can be tricky because if you don't configure it correctly, all logons can fail to connect to the database due to a misconfiguration. Always test it in a development/test database first.

Configuring the Trigger

Create a Table to Log Failed Login Attempts


  CREATE TABLE logon_failure_log (
        logon_id NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
        username VARCHAR2(30),
        os_user VARCHAR2(30),
        error_code NUMBER,
        error_message VARCHAR2(4000),
        logon_time TIMESTAMP,
        terminal VARCHAR2(255),
        ip_address VARCHAR2(50),
        client_application VARCHAR2(100),
        hostname VARCHAR2(100)
    )
    TABLESPACE users;

Create a synonym for the table so all users can access it and share the data with involved teams:


    CREATE OR REPLACE PUBLIC SYNONYM DBA_LOGON_FAILURE_LOG FOR SYS.LOGON_FAILURE_LOG;

Create the SQL Trigger


    CREATE OR REPLACE TRIGGER logon_failure_trigger
        AFTER SERVERERROR ON DATABASE
        WHEN (ora_server_error(1) = 1017)
    DECLARE
        v_username VARCHAR2(30);
        v_osuser VARCHAR2(30);
        v_ip VARCHAR2(50);
        v_program VARCHAR2(100);
        v_hostname VARCHAR2(100);
        v_terminal VARCHAR2(255);
    BEGIN
        SELECT USER,
               SYS_CONTEXT('USERENV', 'OS_USER'),
               SYS_CONTEXT('USERENV', 'IP_ADDRESS'),
               SYS_CONTEXT('USERENV', 'CLIENT_PROGRAM_NAME'),
               SYS_CONTEXT('USERENV', 'HOST'),
               SYS_CONTEXT('USERENV', 'TERMINAL')
          INTO v_username, v_osuser, v_ip, v_program, v_hostname, v_terminal
          FROM dual;

        INSERT INTO logon_failure_log (username, os_user, logon_time, terminal, ip_address, client_application, hostname)
        VALUES (v_username, v_osuser, SYSTIMESTAMP, v_terminal, v_ip, v_program, v_hostname);

        COMMIT;
    END logon_failure_trigger;
    /

With the trigger enabled, every time a user fails to connect to the database, a new row will be added to the table logon_failure_log.

Using a SQL Trace

Another method is to use a SQL trace, as described in Doc 352389.1:


    SQL> ALTER SYSTEM SET EVENTS '1017 trace name errorstack level 10';

This will start logging failed login attempts to the instance alert log. In the alert log, you will see a trace file mentioned:

2024-12-20T17:07:42.099169-03:00
    LAB(3): Errors in file /u01/app/oracle/diag/rdbms/cdb/cdb/trace/cdb_ora_17804.trc:
    ORA-01017: invalid username/password; logon denied

Trace File Details

Opening the trace file will provide detailed information about the session:

Trace file /u01/app/oracle/diag/rdbms/cdb/cdb/trace/cdb_ora_17804.trc
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.22.0.0.0
Build label:    RDBMS_19.22.0.0.0DBRU_LINUX.X64_231229
ORACLE_HOME:    /u01/app/oracle/product/19.0.0/dbhome_1
System name:    Linux
Node name:      orcl-perf-lab
Release:        5.4.17-2136.322.6.2.el7uek.x86_64
Version:        #2 SMP Sat Aug 19 11:55:11 PDT 2023
Machine:        x86_64
CLID:   P
Instance name: cdb
Redo thread mounted by this instance: 1
Oracle process number: 69
Unix process pid: 17804, image: oracle@orcl-perf-lab


*** 2024-12-20T17:07:42.098969-03:00 (LAB(3))
*** SESSION ID:(1111.57603) 2024-12-20T17:07:42.099015-03:00
*** CLIENT ID:() 2024-12-20T17:07:42.099019-03:00
*** SERVICE NAME:(lab) 2024-12-20T17:07:42.099022-03:00
*** MODULE NAME:(SQL Developer) 2024-12-20T17:07:42.099025-03:00
*** ACTION NAME:() 2024-12-20T17:07:42.099028-03:00
*** CLIENT DRIVER:(SERVER) 2024-12-20T17:07:42.099030-03:00
*** CONTAINER ID:(3) 2024-12-20T17:07:42.099033-03:00

dbkedDefDump(): Starting a non-incident diagnostic dump (flags=0x0, level=10, mask=0x0)
----- Error Stack Dump -----
 at 0x7ffd0a996bb0 placed dbkda.c@298
ORA-01017: invalid username/password; logon denied
----- SQL Statement (None) -----

In the trace file, search for the "program" word to identify the source application:

*** 2024-12-20T17:07:43.647822-03:00 (LAB(3))
    ----------------------------------------
    SO: 0x8ef2dda0, type: session (4), map: 0x8d80e210
        state: LIVE (0x4532), flags: 0x1
        owner: 0x8eeef400, proc: 0x8eeef400
        link: 0x8ef2ddc0[0x8ae8cd20, 0x8eeef470]
        child list count: 2, link: 0x8ef2de10[0x729568e8, 0x86fc8058]
        conid: 3, conuid: 4139995895, SGA version=(1,0), pg: 0
    SOC: 0x8d80e210, type: session (4), map: 0x8ef2dda0
         state: LIVE (0x99fc), flags: INIT (0x1)
    (session) sid: 1111 ser: 57603 trans: (nil), creator: 0x8e61d848
              flags: (0x41) USR/- flags2: (0x40009) -/-/INC
              flags_idl: (0x1) status: BSY/-/-/- kill: -/-/-/-
              DID: 0000-0000-000000000000-0000-00000000, short-term DID:
              txn branch: (nil)
          con_id/con_uid/con_name: 3/4139995895/LAB
          con_logonuid: 4139995895 con_logonid: 3
          con_scuid: 4139995895 con_scid: 3
              edition#: 0              user#/name: 0/SYS
              oct: 0, prv: 0, sql: (nil), psql: (nil)
              stats: 0x73c0c710, PX stats: 0x135f26c4
    service name: lab
    client details:
      O/S info: user: mailb, term: unknown, ospid: 19232
      machine: DESKTOP-HULK program: SQL Developer
      application name: SQL Developer, hash value=1012150930

With this information, you can share details with the DevOps/Application team to identify the root cause of failed login attempts.

Here is how to disable the trace:


    SQL> ALTER SYSTEM SET EVENTS '1017 trace name context off';

Search This Blog

BHThoughts