Oracle: Audit a failed logon attempt without auditing

Oracle has a disadvantage: It allows no trigger BEFORE LOGON ON DTATBASE! 🙂 For obvious reasons, this would be nonsense, but there’s a need for it! Auditing failed logon attempts, for example. Of course, there’s Oracle Auditing. But IMO, setting up an audit trail for one Email in one case looked like the overkill to me and my simple DB decommissioning needs (finding autistic developers).

The concept with a trigger was too nice to drop it after examining the AFTER LOGON trigger, which is useless for my problem. But the oracle-l mailing list helped AGAIN: There’s another oracle trigger: AFTER SERVERERROR ON DATABASE – catch ORA-28000, and you are perfectly auditing logon attempts on locked user accounts.

This is a code snippet for 10g and above, that sends an email on each attempt. Might be a spam bomb if abused, but as I said: My simple needs …

CREATE OR REPLACE TRIGGER failed_logon_notifications
  AFTER SERVERERROR ON DATABASE

DECLARE
  b VARCHAR2(3) := UTL_TCP.CRLF;
  l_subject VARCHAR2(40) := 'Alert - Failed Login';
  l_message VARCHAR2(500);

BEGIN
  IF ora_is_servererror( 28000 ) THEN
    l_message :=
      'Date/Time:  ' || TO_CHAR( SYSDATE, 'mm/dd/yyyy hh24:mi:ssss' ) || b;
    l_message :=
      l_message || 'OS User:  ' || SYS_CONTEXT( 'USERENV', 'OS_USER' ) || b;

    l_message :=
      l_message || 'Host:  ' || SYS_CONTEXT( 'USERENV', 'HOST' ) || b;
    l_message :=
      l_message || 'Terminal:  ' || SYS_CONTEXT( 'USERENV', 'TERMINAL' ) || b;

    l_message :=
      l_message || 'IP Address:  ' || SYS_CONTEXT( 'USERENV', 'IP_ADDRESS' ) || b;
    l_message :=
      l_message || 'Protocol:  ' || SYS_CONTEXT( 'USERENV', 'NETWORK_PROTOCOL' ) || b;

    l_message :=
      l_message || 'Database Instance:  ' || ora_instance_num || b;
    l_message :=
      l_message || 'Database Name:      ' || ora_database_name || b;

    BEGIN
      utl_mail.send
        ( sender => ora_database_name || '@example.com',
          recipients => 'martin.klier@example.com',
          subject => l_subject,
          message => l_message );

    EXCEPTION
      WHEN others THEN
        RAISE;

    END;
  END IF;
END failed_logon_notifications;

This piece of code does the same, but for pre-10g-dtabases without utl_mail (using utl_smtp), it’s a bit more elaborate, but works like a charm.

CREATE OR REPLACE TRIGGER failed_logon_notifications
  AFTER SERVERERROR ON DATABASE

DECLARE
  b VARCHAR2(3) := UTL_TCP.CRLF;
  l_subject VARCHAR2(40) := 'Alert - Failed Login';

  l_mailhost    VARCHAR2(64) := 'smtp.example.com';
  l_from        VARCHAR2(64) := 'server-'||ora_database_name||'@example.com';
  l_to          VARCHAR2(64) := 'martin.klier@example.com';
  l_mail_conn   UTL_SMTP.connection;

BEGIN
  IF ora_is_servererror( 28000 ) THEN
  BEGIN
      l_mail_conn := UTL_SMTP.open_connection(l_mailhost, 25);
      UTL_SMTP.helo(l_mail_conn, l_mailhost);
      UTL_SMTP.mail(l_mail_conn, l_from);
      UTL_SMTP.rcpt(l_mail_conn, l_to);

      UTL_SMTP.open_data(l_mail_conn);

      UTL_SMTP.write_data
         (l_mail_conn, 'Date: ' || TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI:SS') || b);
      UTL_SMTP.write_data
         (l_mail_conn, 'From: ' || l_from || b);
      UTL_SMTP.write_data
         (l_mail_conn, 'Subject: ' || l_subject || b);
      UTL_SMTP.write_data
         (l_mail_conn, 'To: ' || l_to || b);
      UTL_SMTP.write_data
         (l_mail_conn, '' || b);

      UTL_SMTP.write_data
         (l_mail_conn, 'Date/Time:  ' || TO_CHAR( SYSDATE, 'mm/dd/yyyy hh24:mi:ssss' ) || b);
      UTL_SMTP.write_data
         (l_mail_conn, 'User:  ' || SYS_CONTEXT( 'USERENV', 'USER' ) || b);
      UTL_SMTP.write_data
         (l_mail_conn, 'OS User:  ' || SYS_CONTEXT( 'USERENV', 'OS_USER' ) || b);

      UTL_SMTP.write_data
         (l_mail_conn, 'Host:  ' || SYS_CONTEXT( 'USERENV', 'HOST' ) || b);
      UTL_SMTP.write_data
         (l_mail_conn, 'Terminal:  ' || SYS_CONTEXT( 'USERENV', 'TERMINAL' ) || b);

      UTL_SMTP.write_data
         (l_mail_conn, 'IP Address:  ' || SYS_CONTEXT( 'USERENV', 'IP_ADDRESS' ) || b);
      UTL_SMTP.write_data
         (l_mail_conn, 'Protocol:  ' || SYS_CONTEXT( 'USERENV', 'NETWORK_PROTOCOL' ) || b);

      UTL_SMTP.write_data
         (l_mail_conn, 'Database Instance:  ' || ora_instance_num || b);
      UTL_SMTP.write_data
         (l_mail_conn, 'Database Name:      ' || ora_database_name || b);
      UTL_SMTP.close_data
         (l_mail_conn);

      UTL_SMTP.quit
         (l_mail_conn);

    EXCEPTION
      WHEN others THEN
        RAISE;
    END;
  END IF;
END failed_logon_notifications;

Thanks a lot to the oracle-l mailing list and the folks in this thread, especially to Chet for his blog entry!