Oracle 10.2 Migrations – Account LOCKED(TIMED) and FAILED_LOGIN_ATTEMPTS
Recently we’ve had quite a few migrations to 10g Release 2 and several times been hit by one issue – some users consistently get locked with status LOCKED(TIMED). One good example is with the DBSNMP and SYSMAN users, but more important are locked production accounts.
It turned out that the FAILED_LOGIN_ATTEMPTS attribute for the DEFAULT profile has been changed in 10.2.0.2 (actually 10.2.0.1 and above) from UNLIMITED to the value of 10. Well, that’s good from security point of view. On the other hand, this is really dangerous, especially during or after migrations while chances are high that some process will try to connect with wrong credentials. This can easily end up with a service outage because an application can’t connect.
One way to resolve it is to change the DEFAULT profile. However, I would recommend leaving it 10 by default and, instead, create a new profile and assign the critical production users to this profile:
CREATE PROFILE DEFAULT_10GR1
LIMIT FAILED_LOGIN_ATTEMPTS UNLIMITED;
ALTER USER [USERNAME] PROFILE DEFAULT_10GR1;
The next step should be to review this policy with your security officer. By the way, this must be a substantive discussion – a production DBA should be keen to avoid service outages by any means, while a security officer’s priority is preventing unauthorized access.
PS: Interesting that the issue with the DBSNMP account is supposed to be resolved by creating a special profile – MONITORING_PROFILE. There are few notes on Metalink about it like 336629.1.
PPS: Just now I found out that Laurent has already mentioned that a while ago (and thanks to him) the change was finally documented in the Readme note for Oracle 10.2.
November 7th, 2006 at 12:30 pm
this is useful information - since I am on my way to a 10g Migration
November 8th, 2006 at 4:12 am
the fact that the OEM 10g agent is trying to log with DBSNMP/DBSNMP until the account is lock is a pity. After one invalid password, Oracle should figure out we are NOT using the password DBSNMP for the account DBSNMP.
November 8th, 2006 at 6:07 am
Hi Alex,
I would not recommend setting it back to unlimited. In fact I disagree with Oracle’s very conservative value of 10. I would recommend creating different profiles for differnet groups of users, ie PROD accounts, DBA’s, power users, users (if they connect directly), default accounts….
Each should have their own profile and differnt values. For instance failed_login_attempts should be much lower for accounts that are rarely directly accessed. Also the lock time for these should be much higher for the same reason. Even though Oracle have strengthened their default security position no one should rely on it. The security design should suit the application/ regulatory issues / internal policies always.
cheers
Pete
November 8th, 2006 at 12:51 pm
Laurent,
Good idea, Merci. But it should retry sooner or later anyway. Otherwise, it’s a manual action.
Pete,
Thanks for your input. Fully agree with you and that why I mentioned reviewing security policies (or at least introduce some as there is often nothing to review). However, I believe that migration projects should not include any additional tasks but bare minimum. Otherwise, it’s too much troubles to troubleshoot what’s going wrong during migration. What has more priority for the company - migration or security is another question. Often I see it’s not in favor of security. :-(
Cheers guys.
November 9th, 2006 at 2:38 am
Alex, I could even say, it is a miserable strategy from Oracle to suppose the dbsnmp password could be dbsnmp, Oracle should not even allow this! as you install the agent, you should be forced to set the password for each target, the current approach well, it is easier to set it to dbsnmp for auto-discovery is simply a huge security hole.
YMMV
November 9th, 2006 at 7:36 am
Well, I don’t recall that in 10g you have a default password, at least, in the installer. In fact, if I remember correctly, OUI does not allow setting it to DBSNMP manually (if I don’t mistake it for something else).
November 10th, 2006 at 3:16 am
yes, that’s why I wrote that it is a bad practice to suppose the dbsnmp password is dbsnmp (well, the 10g agent works with 9i databases where it was default). So I guess the OEM team should update their security practices to be conform with the newest db releases !