Re: How to log attempts to connect as a locked user account

From: chet justice <chet.justice_at_gmail.com>
Date: Sat, 1 Aug 2009 23:17:17 -0400
Message-ID: <8311a5b60908012017s62a548e3l1c11afa649108d03_at_mail.gmail.com>

Since you are just de-commissioning these databases, why go through the auditing process at all. Just set up a trigger and have it send you an email everytime someone tries to connect. Might be easier than going in randomly to query the audit trail.

I tried to do something similar here:
http://www.oraclenerd.com/2009/02/audit-failed-logon-attempts.html

CREATE OR REPLACE
TRIGGER failed_logon_notifications
  AFTER SERVERERROR ON DATABASE
DECLARE
  b VARCHAR2(3) := UTL_TCP.CRLF;

  l_username VARCHAR2(30);
  l_failed_attempts INTEGER;
  l_subject VARCHAR2(40) := 'Alert - Failed Login';
  l_message VARCHAR2(500);

BEGIN
  IF ora_is_servererror( 01017 ) 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 || '_at_revolutionmoney.com',
          recipients => 'dbas_at_email.com',
          subject => l_subject,
          message => l_message );
    EXCEPTION
      WHEN others THEN
        RAISE;

    END;
  END IF;
END failed_logon_notifications;
/

chet

-- 
chet justice
www.oraclenerd.com

On Sat, Aug 1, 2009 at 9:03 PM, Yong Huang <yong321_at_yahoo.com> wrote:



>

> > Make sure AUDIT_TRAIL is set to

> > something other than NONE (DB is good).

> > This requires an instance restart.

>

> Why not set it to something other than none when the database

> was created, or last time it was bounced? I don't think

> there's any overhead until you start to audit anything. So

> you get the convenience whenever you audit something when

> need arises and noaudit something when you're done.

>

> Yong Huang

>

>

>

> --

> http://www.freelists.org/webpage/oracle-l

>

>

>


--
http://www.freelists.org/webpage/oracle-l

Received on Sat Aug 01 2009 - 22:17:17 CDT