S 2.130 Ensuring the integrity of a database

Initiation responsibility: Head of IT Section, IT Security Management

Implementation responsibility: Administrator; staff responsible for the individual IT applications

The integrity of a database needs to be monitored and secured in order to ensure the correctness of the related data and the consistency of the database state. The following techniques must be employed to avoid the occurrence of incorrect data and inconsistent states in a database:

• Access control

Access control implies the protection of the database against unauthorised access by assigning corresponding access rights as described in S 2.129 Controlling access to database information. This prevents manipulations of the data and database objects (such as tables).

The database administrator is responsible for implementing access control.

A detailed description of access control has been omitted here, as it is provided in S 2.129 Controlling access to database information.

• Synchronisation control

Synchronisation control is intended to prevent inconsistencies which could arise through parallel access to the same data. Several techniques are available for this purpose, including the locking of database objects and the allocation of timestamps.

The persons in charge of individual IT applications are responsible for implementing synchronisation control, insofar as a mechanism exceeding the scope of the database management system needs to be provided additionally.

A detailed description has been left out here, as synchronisation control is performed by most database management systems. We strongly advise against the use of a database management system which does not offer this feature.

• Integrity control

This involves the avoidance of semantic errors and semantically inconsistent database states through the observance and monitoring of database integrity constraints. These can pertain to individual relations or to groups of several mutual relations (referential integrity). Examples here are the specification of a primary key for a relation, definition of value ranges for individual attributes, and formulation of special constraints by means of an assertion clause.

Integrity control can be carried out by the database management system automatically by means of a monitor created using triggers or stored procedures. In principle, this allows any type of transaction to be performed; however, the database management system rejects those transactions which would impair the consistency of the database.

Responsibility for implementation lies with the persons in charge of individual IT applications and the application-specific administrator, insofar as the integrity constraints need to be realised in the form of relations, primary keys or general database objects.

The following items must be prepared as part of planning an IT application:

• A data model which maps the database objects as well as their mutual relationships

• A technical concept which includes a description of the conditions under which data can be manipulated.

The following points must be observed during the realisation of an IT application:

• The actual implementation of the data model specified during the conceptual phase must be described. This includes the definition and creation of tables, indices, value ranges etc.

• Triggers and stored procedures are defined during the realisation of the technical concept. Triggers and stored procedures can be used within an application (in the programs) and in the database (for tables). Triggers used on the database level act independently of the overlying applications, and must thus be managed centrally.

Example: 'Update' trigger for a table:

Whenever a data record in the table is modified, the statements defined for the trigger need to be executed. One of these statements can comprise the invocation of a stored procedure.

Where applications are concerned, integrity can be ensured through the suitable use of commit and rollback for transactions.

Additional controls:

• Are all the integrity control techniques described above implemented?

• Have all the integrity constraints been agreed with the administrators of the individual IT applications?