Re: How to log attempts to connect as a locked user account
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:
CREATE OR REPLACE
AFTER SERVERERROR ON DATABASE
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);
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;
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;
-- chet justice www.oraclenerd.com On Sat, Aug 1, 2009 at 9:03 PM, Yong Huang <yong321_at_yahoo.com> wrote:Received on Sat Aug 01 2009 - 22:17:17 CDT
> > 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