Database Triggers

Triggers are a method that can be used to add some logic to the database system. When a set action occurs (such as the arrival of a certain type of data), a process can be automatically started. Some of the common database triggers include the following.

Check Triggers
Database triggers are procedural code that is automatically executed in reaction to selected events on a particular table, row or field in a database. The auditor should check that these are used and where. Triggers need to be set to fire when events that are defined in policy occur.

System triggers
System triggers allow the activation of controls that start when system events take place. These events can include:

• The start- up and shutdown of the database,
• Logon and logoff from users,
• Privileged access, and
• The creation, altering and dropping of schema objects.

Using autonomous transactions also allows a log to be written for the above system events. The audit should check what (if any) systems triggers exist and ensure that these are aligned with the policy of the organization.

Update, delete, and insert triggers
Defense in depth requires an understanding of the users' actions at multiple levels. This is not just access to the database, but access at the detailed row level for selected events and where there is sensitive data. Database triggers need to be written to capture changes at the column and row level.

Where data is extremely sensitive and any and all changes must be recorded, the database can be configured to write entire rows of data detailing a change to the data (who, what, where and why). This can be done both ahead of and subsequent to the modification of data being made with a write of information to a log table in the database and to an alternate location. This class of logging is extremely resource intensive. It requires that at least as many extra records are written and stored as the planned change (and at times more).

The one flaw in this technique is an inability to capture read access to a file using normal database triggers.

Oracle (as an example) breaks audit into three areas that can be used for logging and in creating triggers:

1. Statement auditing (CREATE TABLE or CREATE SESSION),
2. Privilege auditing (ALTER USER), and
3. Object level auditing (SELECT TABLE).

These inbuilt levels of auditing can provide the auditor with a rich source of evidence in the form of logs.

Triggers are also commonly used as a form of database control. They can be used to trigger the execution of other procedures. For instance, integrity controls may be used to place an entry into a log to record access to tables. In this way user access may be recorded. It is possible to record information across different tables.

Database triggers are also effective in adding security controls to a database. A trigger can include an event, condition and action. Triggers may be more complex than an assertion but will allow the database to automatically prohibit inappropriate actions, automatically start handling procedures using stored procedures or other processes or write a row to a log file. This may be used to reflect information about the user and transaction that has been created. This log may then be displayed in a format that can be read by humans or using automated procedures and tools. Like any stored procedure domains and triggers can be used to enforce controls for all users and all database activities.