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!




You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.

3 Responses to “Oracle: Audit a failed logon attempt without auditing”

  1. Dave Says:

    I once got an email from my boss saying, “I’m concerned that there may be security implications from having UTL_SMTP accessible on the Dev databases. Also, I’m sleeping with your wife.”

    Looked up to see the developers grinning like loons. We changed to a SYSTEM.MAIL_PKG package that had the only access to UTL_SMTP and audited all mails sent to a table including OSUSER and TERMINAL. Which stopped that.

  2. usn Says:

    Cool ideas, both of them – how to tell the truth, and how to “fix” the issue. :)

  3. chet Says:

    @usn

    Glad I could help!

    I went through something similar with my previous DBA. We were looking for people trying to hack the system. Of course we had auditing turned on along with policies set up on logins (3 times and your locked), but these seemed an easier solution for our needs.

    chet

Leave a Reply