S 4.73 Specifying upper limits

Initiation responsibility: Head of IT Section, IT Security Management

Implementation responsibility: Administrators, application developers

To allow better control of access to a database system and improve performance, it is advisable to specify upper limits for certain parameters. Particular note must be made of the following items here:

Specifying upper limits for selectable data records

Particularly for databases holding large amounts of data, it is recommended to specify a maximum number of data records which can be selected during access to the database.

If such upper limits do not exist, users can intentionally or unintentionally execute SELECTs of any scope. This not only obstructs the activities of the individual user, but also results in long waiting periods for all other users of the database. Data records which have been selected for modification remain unavailable to all other users until the transaction is complete.

The upper limits must be defined within the framework of the applications which access the database. Here, suitable controls and locks must be implemented to monitor adherence to the upper limits. In the case of applications which offer search functions, unrestricted searching should generally be disabled, and the entry of search criteria should be made mandatory.

Imposing restrictions on resources

Another option offered by certain manufacturers is the restriction of resources as regards the usage of a database. Here, it is possible to define a large number of attributes, including the number of logins per user ID, maximum permissible CPU utilisation time per login, total duration of a database session and the maximum permissible inactive period while an ID remains logged in.

Examples:

The following instruction limits the temporary tablespace "Temp" to 100 MB for database ID "Smith" in an Oracle database:

ALTER USER Smith TEMPORARY TABLESPACE Temp QUOTA 100M ON Temp;

The next instruction is used to create a profile tester which limits the number of sessions, maximum CPU utilisation time per session, maximum duration of a database link and maximum idle time (IDLE). Such profiles can be allocated to individual users.

CREATE PROFILE Tester LIMIT

SESSIONS PER USER 2,

CPU_PER_SESSION 6000,

IDLE_TIME 30,

CONNECT_TIME 500;

For example, Ingres databases allow the imposition of limits on the maximum input and output, as well as the maximum number of records for queries issued by users and user groups.

It is also possible to limit the number of users who can access the database simultaneously. The restriction of this number via parameter settings in the database management system ensures that the maximum number of licenses available for the database software is not exceeded. Simultaneous access by a large number of users might also result in an excessively high operational load on the database server, thus increasing the average transaction times. If, for some reason, an extension of the resources of the database system is not possible or desirable, limiting the number of simultaneous access attempts also helps alleviate the situation.

The related requirements should already be clarified during selection of the standard database software, to allow the preparation of a concept for imposing limitations on resources, should this become necessary (refer to S 2.124 Selection of suitable database software).

Additional controls:

• Have upper limits been specified for applications, and is adherence to them being monitored?

• Have unrestricted searches in the applications been disabled in principle?

• Have requirements for restricting database resources been formulated and documented?