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

  • From: chet justice <chet.justice@xxxxxxxxx>
  • To: yong321@xxxxxxxxx
  • Date: Sat, 1 Aug 2009 23:17:17 -0400

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 || '@revolutionmoney.com',
          recipients => 'dbas@xxxxxxxxx',
          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@xxxxxxxxx> 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
>
>
>
> --
> //www.freelists.org/webpage/oracle-l
>
>
>

Other related posts: