I am wary of three security defaults introduced in Oracle 11g in relation to generic user accounts (those not exclusively used by individuals):
- PASSWORD_LIFE_TIME – a time bomb with a six month count down
- FAILED_LOGIN_ATTEMPTS – 10 failed login attempts are all that are needed to break most applications
- Intentional delays after three consecutive failures
Point 1 should be addressed by creating different profiles for generic accounts and allowing individual users to change their accounts via the application when they have expired. The generic application and administration accounts can still be changed regularly if necessary, but in most cases a sudden unexpected expiration would not be welcome.
I have seen real life outages as a result of point 2. Why break the entire application just because a few users forgot the new password after the weekend, or because one module’s connection pool had a mistake in its configuration? Increasing this limit or setting it to unlimited would have been enough to prevent accidental or malicious denial of service incidents with Oracle 10g. However, in 11g, new security measures have again threatened a new and easy denial of service attack / incident.
As a result of point 3, users with the right password and administrators trying to alter the account may find their sessions hanging because of other sessions attempting to log in with the incorrect password.
- sessions waiting on “library cache lock”
- most of the waiting sessions have a null username in v$session (because they haven’t logged in successfully)
- DBAs’ sessions trying to alter the user will also wait on the “library cache lock” but will eventually succeed
- attempts to connect with the correct password will eventually complete successfully
- delays range from 3 seconds to 10 seconds multiplied by the number of concurrent correct or incorrect log in attempts
- the library cache grid lock disappears when the account is locked
If it is not obvious which database account is trying to be accessed incorrectly, this can be determined by temporarily setting FAILED_LOGIN_ATTEMPTS=1 and then checking DBA_USERS for accounts that are locked(timed).
alter profile DEFAULT limit FAILED_LOGIN_ATTEMPTS 1; -- Wait for a connection to fail, then check for locked accounts clear col bre comp col user_id format 99999 heading UID col profile format a16 col ACCOUNT_STATUS format a16 heading STATUS select USERNAME, USER_ID, ACCOUNT_STATUS, PROFILE, to_char(LOCK_DATE,'DD-MON-RR') LOCK_DATE , to_char(EXPIRY_DATE,'DD-MON-RR') EXPIRY_DATE from dba_users where ACCOUNT_STATUS = 'LOCKED(TIMED)' order by username /
This is less than ideal in a live environment because connections with the right password will be rejected once the account is locked and a subsequent unlock command might take a while to take effect because of the library cache grid lock.
In 11.2 there is a solution – we can now dynamically disable the login failure delay by setting an event:
alter system set events '28401 TRACE NAME CONTEXT FOREVER, LEVEL 1';
After doing this, administrators are free to test passwords, reset passwords and un/lock accounts, while connections with the correct password are not delayed.
The EVENT initialisation parameter can also be set to make this change permanent if required.