Information Technology Reference
In-Depth Information
Tip 9
: Use database triggers to record changes to sensitive tables
The user activity log table is an important one from an audit perspective.
Other tables, e.g., those that control access to applications, are also
sensitive, and auditors also want IAM to record all changes to sensitive data
(users, roles, application-to-role access rights, etc.) Each such table should
have columns to record the user ID of the user who requested the change,
the user ID of the user who authorised the change, as well as timestamps for
each of those actions. Since the columns holding these request/authorise
fields only pertain to the latest change to a record, we need a way to store
the entire history of changes to a table in a reliable way.
Here's a simple mechanism: For every table that needs its change history
recorded, create 3 database triggers, one each on the INSERT, UPDATE and
DELETE actions. For each table, also create a history table that has all the
same fields as the original, but whose primary key is a meaning-free
sequence number. This table also needs another special field that says what
action resulted in a record being logged. The values of this column would be
INS (for inserted record), UPO (for updated old record), UPN (for updated
new record) and DEL (for deleted record).
The advantage of database triggers is that changes to tables are logged even
if a user bypasses the IAM application and directly updates a table.
The following diagram illustrates how a table's change history can be
automatically maintained.
