Quirky Oracle DOS attack option

As of 11g, Oracle has implemented a number of built-in password protections. One such protection is retarding login attempts when an incorrect password is used. After three failed attempts, Oracle will retard login for several seconds up to approximately 10 seconds on successive attempts.

The documentation states that if the correct password is entered, log in occurs without delay. However, this is not quite true. Take the following situation: A new application is trying to connect to the database but is configured with the wrong password. Typically configured with some connection pooling, this will very quickly reach the 10 second delay. Now, whilst this new application is trying to incorrectly connect to the database, ALL attempts (correct and incorrect) to connect to that database user are delayed. This means existing applications will but delayed even though they have the correct credentials.

I experienced this on 11.1.0.7.0. Let's hope Oracle come up with a fix!

Quirky Oracle LOBS

Recently a client of mine took to storing volitile session related data in the database in the form of a BLOB. Whilst I argued the virtues of storing session related information at the app tier and the reduced scalability of such a design, for the short term it looked like the only solution.

Naturally I was inclined to test and tune a variety of configurations for the LOB such as chunk size, storage in and out of row (see the "SecureFiles and Large Objects Developer's Guide" in www.oracle.com/pls/db111/portal.all_books).

As I explained to the developers, when dealing with LOBS, you can chuck it and suffer the consequences (as they were seeing in their own tests) or do it properly. I was fairly confident that I would be able to improve upon the performance that comes by default.

Initially the lob was 8k, so I wouldn't have the luxury of choosing between in-row and out-of-row design (anything over 4k forces you to the latter). My choice was then reduced to lobs that were cached, but generated logging (with Oracle LOBS you can't have one without the other for out-of-row LOBS) or non-cached without logging. Load testing revealed the load profile in the database and here's a snipped from AWR (1 hr snapshot with LOB configured of non-caching):

Reads CPU Elapsed
Physical Reads Executions per Exec %Total Time (s)
-------------- ----------- ------------- ------ -------- ---------
289,226 70,151 4.1 53.7
update mybooking set data = :1 where id = :2

data is the the LOB (a BLOB in my case). This revealed that the app updates the LOB 70k times per hour.

The average size was 22k, so I set chunksize to 24K (rounded up to multiple of db block size = 8k). You can work out the average size with:

AVG(DBMS_LOB.GETLENGTH(DATA))

I then tried the caching/logging option and found that physical disk reads reduced by 70-80% but of course redo generation rose dramatically to 500 MB/hr. Therein lies the dilemma. Admittedly the application response times were similar with both configuration, but at the time of the tests the application was being throttled with a 100ms delay.

I didn't have to scratch my head for long however (so I still have hair!)... The application was changed such that the average LOB size reduced to 2k. I jumped at the chance of storing the LOB in-row, knowing that I could create the table with the NOLOGGING option. I would benefit from caching and nologging hooray! So what happened...?

To my dissappointment, Oracle kept logging away, much like before. It turns out that there is a bug in Oracle (Bug 4205241) that effects 9.2.0.4 all the way up to 11g (groan).

Quirky 11g Default Profile

So you're used to setting up and configuring your Oracle database in a 3-tier architecture (typically web app). As has been the case for many past releases, Oracle assigns the default profile to users unless otherwise specified. However, to help you along (...not!), the default profile in 11g is now restrictive as follows:

PASSWORD_LIFE_TIME 180     (days)
PASSWORD_GRACE_TIME 7      (days)
FAILED_LOGIN_ATTEMPTS 10
PASSWORD_LOCK_TIME 1       (days)

Oracle probably thought that this was smart security, but I wager to bet that most Oracle databases are connected directly application servers and password management is not dictated by this way. The upshot is that many an unsuspecting DBA, having setup a database that is happily supporting a website or a.n.other 3-tier configuration, gets a rude awakening when the application can no longer connect after 6 months! Frankly it would have been better for Oracle to make available a secure profile with the above properties that needs to be explicitly assigned to users.

To resolve this issue you can run the following:

ALTER PROFILE DEFAULT LIMIT
FAILED_LOGIN_ATTEMPTS UNLIMITED
PASSWORD_LIFE_TIME UNLIMITED;