Most large organizations have a security policy, which is a good thing. Blind adherence to the policy is not. Typically, security policies are written with the end user in mind, and more often than not they are written for servers, and then applied everywhere. Policies that work well for end users logging into a server could be problematic for accounts used for automated authentication to a database.
The first problem that comes to mind is password expiration. Having a 90 day password expiration policy for end user accounts is a very good idea. It is even a good idea for end user accounts within your database. It may not be a good idea for accounts that are used to authenticate your web applications. These values are often cached, and an outage is often required to change the password and clear the old one from cache. Can your application sustain an outage every 90 days? More importantly, if you have set PASSWORD_LIFE_TIME to 90 days for the account profile, what will happen to your application when day 90 arrives? If you aren’t proactive about changing the password before the expiration data, chances are the application will fail, and you will be limited to logging in with SQLPLUS. If you are going to use PASSWORD_LIFE_TIME for these types of accounts (which I don’t necessarily recommend), set it for a high value like 366, and make changing the database password part of every software release.
The next problem that comes to mind is account locking after failed attempts. The default value of this setting used to be UNLIMITED, but as of 10gR2 the default value is 10. As I said in the profile tutorial, this is a brilliant strategy for end user accounts, but a terrible idea for application accounts, as it sets you up for a denial of service attack. The idea that you need to lock an account when it is being attacked is hard for some people to let go of. When I explained the concept to my security team, they kept suggesting that we raise the number of attempts. How about 50 or 100? The problem is that it is very easy to set up an automated attack to launch that many failed logins. It can happen in a matter of seconds. Even if you only lock the account for a minute or two, the attacker can lock up the account faster than you can type alter user account unlock;
So what do you do? To start with, be sure to use long, strong passwords. I mean really ugly passwords with 12-16 characters, and a good variety of alpha, numbers with a special character or two. If you are passing it from an application, does it really matter if the password is ys6Pklm-3uq0vm?
Next, (and this is just as important) be sure you enable auditing of failed login attempts. If you don’t know how, check out the Audit tutorial. Once you have enabled auditing, actively monitor the DBA_AUDIT_TRAIL. You can do this with an Enterprise Manager or Grid Control alert, but if you do, be sure that you have a strong password (and no account locking) for DBSNMP, or your attacker may be able to disable your effort. If you use Enterprise Manager for most of your monitoring/management activities, by all means set up the alert, but don’t rely on it as your only defense. Schedule a job to run every few minutes and alert you if any account has experienced an excessive number of failed login attempts.
declare
vSender VARCHAR2(30) := ‘oracle@dbserver’;
vRecip varchar(40) := ‘thediva@database-diva.com’;
vSub varchar(40) := ‘Excessive failed login attempts’;
vMType VARCHAR2(30) := ‘text/plain; charset=us-ascii’;
vMsg varchar2(80);
cursor fl_cursor is
select username, count(username) fail_count from dba_audit_trail
where returncode=1017 and timestamp > sysdate - 6/1440
group by username order by count(username);
userinfo fl_cursor%ROWTYPE;
begin
open fl_cursor;
loop
fetch fl_cursor into userinfo;
exit when fl_cursor%NOTFOUND;
if userinfo.fail_count > 19 then
vMsg := userinfo.fail_count||’ failed login attempts by user ‘||userinfo.username;
sys.utl_mail.send(vSender,vRecip,null,null,vSub,vMsg,vMtype,null);
end if;
end loop;
close fl_cursor;
end;
To create the user-defined metric in Enterprise Manager, choose a query with metric type number with SQL output 2 columns. Use the following query:
select username, count(username) from dba_audit_trail
where returncode=1017 and timestamp > sysdate - 1/240
group by username order by count(username) ;
Choose a value for your warning and critical thresholds, and set a schedule.
