Limiting Database Resources

Whether intentional or malicious, a computer’s resources can be monopolized without much effort. Generally, when this is done maliciously, it is known as a denial of service (DoS) attack.

DoS attacks are easy to implement and hard to defend against. The defense challenge arises from the fact that there are numerous ways to trigger such attacks. The result is simple: exhaust computing resources to the point that the database can no longer provide adequate service. Fortunately, there are some actions you can take in the database to mitigate the risk of DoS attacks.

Resource Limits

In addition to the password profile capabilities, Oracle supports the use of resource profiles to limit the use of precious database resources. Resource limits help ensure that the application or user doesn’t intentionally, or inadvertently, take over the database and system’s resources. You can view the various resources that can be managed as well as their values by querying the DBA_PROFILES view:

sec_mgr@KNOX10g> SELECT resource_name, LIMIT     2     FROM dba_profiles    3    WHERE PROFILE = 'DEFAULT'    4     AND resource_type = 'KERNEL';    RESOURCE_NAME                  LIMIT  --------------------------    --------  COMPOSITE_LIMIT                UNLIMITED  SESSIONS_PER_USER              UNLIMITED  CPU_PER_SESSION                UNLIMITED  CPU_PER_CALL                   UNLIMITED  LOGICAL_READS_PER_SESSION      UNLIMITED  LOGICAL_READS_PER_CALL         UNLIMITED  IDLE_TIME                      UNLIMITED  CONNECT_TIME                   UNLIMITED  PRIVATE_SGA                    UNLIMITED    9 rows selected.   

Notice that the default values are all set to unlimited. A best practice is to actually define as many of these values as possible. Some general guidelines on the parameters are as follows:

• Set the SESSIONS_PER_USER to the size of your application server connection pool. If you aren’t using a connection pool (or have no idea what that means), then set the value to something reasonable. You should consider that an application may lock or a computer may freeze with the connection open, so a value of one may be too restrictive.

  Tip 

  It’s possible to create a denial-of-service attack by utilizing the CREATE SESSION privilege and connecting to the database over and over until the database server exhausts all memory. Setting this parameter helps to ensure this will not happen.

• IDLE_TIME can be set to help ensure that users don’t leave a connected terminal in the database while they step out for a lunch break. If their machine is left unlocked, then someone can simply walk up and start accessing the user’s data without having to worry about breaking passwords or subverting privileges. This value is more applicable to client-server applications than to web applications; if the latter is using a connection pool, the server shouldn’t disconnect the pooled connections.

• CPU_PER_CALL is a hard parameter to guess, but it helps to ensure the availability of the database. Often CPU monopolization occurs not by a malicious user, but by a bad programmer who inadvertently sends the database into a recursive loop (I’m speaking from experience here)!

Refer to the “Create Profile” section in the Oracle Database SQL Reference 10g document for specific definitions on all the settings. Setting the profile parameters to the logical and correct values may take a few tries before the best values are selected. Start with a least privilege mentality by setting the values very conservatively. If you find that you need to relax a few privileges for legitimate reasons, do so only after you have determined the values need to be relaxed.

A best practice is to create a profile for each application or class of users in the database. This includes administrators at all levels.