Using Oracle Triggers to Populate a Table: A Deep Dive into Troubleshooting Failed User Logons

Using Oracle Triggers to Populate a Table: A Deep Dive into Troubleshooting

Understanding the Problem

As an Oracle database administrator, you want to track failed user logins for monitoring purposes. You’ve created a table to store this information and associated it with a trigger that fires on logon events. However, when checking the table values, you’re not seeing any records.

In this article, we’ll delve into the world of Oracle triggers and explore the reasons behind this unexpected behavior.

Table Creation and Trigger Definition

Let’s examine the code snippets provided in the original question:

-- Create table
CREATE TABLE myschema.errors(
    user_name VARCHAR2(30) NOT NULL ENABLE,
    error_code VARCHAR2(50) NOT NULL ENABLE,
    error_message VARCHAR2(50) NOT NULL ENABLE,
    error_time timestamp)
    TABLESPACE "USERS";

-- Create application context
CREATE CONTEXT myschema6_ctx USING myschema.myschema6_pkg; 

-- Create package body
CREATE OR REPLACE PACKAGE BODY myschema.myschema6_pkg 
    IS 
        c_context CONSTANT VARCHAR2(30) := 'myschema6_ctx';
    username  VARCHAR2(30);
    My_USER  VARCHAR2(30);

            PROCEDURE set_contexts IS 
            BEGIN 
            SELECT SYS_CONTEXT ('USERENV', 'SESSION_USER') into username FROM DUAL;
            SELECT SYS_CONTEXT ('USERENV', 'OS_USER') into My_USER FROM DUAL;
            DBMS_SESSION.SET_CONTEXT ( c_context, username, My_USER); 
    END; 
    END myschema6_pkg;

-- Create trigger
CREATE OR REPLACE TRIGGER myschema6_ctx_trig AFTER LOGON ON DATABASE
    DECLARE 
    My_USER VARCHAR2(30);
        v_code VARCHAR2(30);
        v_errm VARCHAR2(30);
    BEGIN
        myschema.myschema6_pkg.set_contexts;
        SELECT SYS_CONTEXT ('USERENV', 'OS_USER') into My_USER FROM DUAL;
            EXCEPTION
                WHEN OTHERS THEN
                   v_code := SQLCODE;
                   v_errm := SUBSTR(SQLERRM, 1 , 64);

                   -- to insert information about errors.
               INSERT INTO myschema.errors(user_name,error_code,error_message,error_time)
              VALUES(My_USER,v_code,v_errm, SYSTIMESTAMP);
    END;
/

The Issue: Using `AFTER LOGON ON DATABASE` Trigger

The problem lies in the trigger definition:

CREATE OR REPLACE TRIGGER myschema6_ctx_trig AFTER LOGON ON DATABASE

As mentioned in the original answer, you cannot use an AFTER LOGON ON DATABASE trigger to capture logon failures. Since a logon failure never successfully logs on, this trigger will never be executed.

Alternative: Using `AFTER SERVERERROR ON DATABASE` Trigger

To capture logon failures, you should use an AFTER SERVERERROR ON DATABASE trigger:

CREATE OR REPLACE TRIGGER myschema6_ctx_trig AFTER SERVERERROR ON DATABASE
    DECLARE 
    My_USER VARCHAR2(30);
        v_code VARCHAR2(30);
        v_errm VARCHAR2(30);
    BEGIN
        -- ...

In this modified trigger, you’ll check for specific error codes (e.g., 28000, 1017, and 1005) to identify logon failures. Note that the USERNAME in v$session will be NULL for logon failures; instead, use SYS_CONTEXT('USERENV','AUTHENTICATED_IDENTITY') to get the intended username.

Best Practices

Here are some additional best practices to keep in mind:

Use packages and set contexts only when necessary, as you can access required information using standard Oracle calls.
Access V$ views like v$session with your SID for more detailed information about the session.
Make sure any trigger writing to a log uses an autonomous transaction and includes a COMMIT.

Standard Logon Auditing

Before diving into custom system error triggers, explore Oracle’s standard logon auditing:

-- For DBA_AUDIT_TRAIL
SELECT *
  FROM dba_audit_trail
 WHERE action_name = 'LOGON'
   AND returncode <> 0;

-- For Unified auditing
SELECT *
  FROM unified_audit_trail
 WHERE action_name = 'LOGON'
   AND return_code <> 0;

These queries provide a wealth of information about logon events, including return codes and error messages. Only proceed with system error triggers if you need enriched information beyond what standard auditing provides.

Conclusion

In this article, we explored the world of Oracle triggers and addressed the issue at hand: using a trigger to populate a table for failed user logins. By understanding the limitations of the AFTER LOGON ON DATABASE trigger and leveraging best practices, as well as standard logon auditing, you can effectively track and monitor logon events in your Oracle database.

Additional Resources

Example Use Cases

Standard Logon Auditing: Use the provided queries to monitor logon events and identify potential security issues.
Custom System Error Triggers: Create triggers using AFTER SERVERERROR ON DATABASE to capture specific error codes related to logon failures.

By following this article’s guidance and best practices, you’ll be well-equipped to tackle even the most complex Oracle trigger-related challenges.

Last modified on 2024-07-31