Hack 96. Create an Audit Trail

The most secure systems include an audit trail. It should be possible to find out who did what and when they did it.

Having a complex permissions system that determines who is allowed to write to different tables can actually make security worse. If the system is too complex for users to manage, or too inflexible to cope with the absence of authorized account holders, users will respond by sharing passwords. Once your users start sharing passwords, all hope of accountability goes out the window.

As an alternative, you can relax the permissions system but make sure that you can track down the people who are abusing the system. With a more open, accountable policy, workflows and security are improved.

For each of the vital tables, you create a "history" table. Suppose that you have a petty-cash table, shown in Table 11-6, which lists all items purchased. The who column shows the name of the person making the purchase; this would normally match the username of the person performing the change. When you look at this table you see that Ryka appears to have been abusing the system and has purchased some unauthorized radio-controlled cars.

However, if you have a table modification history for pettyCash, called pettyCashHistory, you can see exactly what really happened, as shown in Table 11-7.

Ryka's original entry for seq 1 was $0.50 for pencils, which was later edited by Guiti to make it seem that Ryka had ordered radio-controlled cars.

To make this work you need to copy the structure of the original table and add the extra columns, changedWhn and changedBy. The default values for these columns should be the current system time and the current user, respectively. Every INSERT and UPDATE on the main table should be accompanied by an INSERT into the history table. By using triggers [Hack #95], you can manage the audit trail automatically.

Of course, it is still possible for the owner of the pettyCashHistory table to make up a false record of events.

11.7.1. SQL Server

For SQL Server, the history table can have defaults on both of the additional fields:

CREATE TABLE pettyCashHistory (seq INTEGER ,whn DATETIME ,who VARCHAR(10) ,description VARCHAR(100) ,amount DECIMAL(8,2) ,changedWhn DATETIME DEFAULT GETDATE( ) ,changedBy VARCHAR(10) DEFAULT SYSTEM_USER );

The trigger code allows access to a pseudotable called inserted; this holds the new values:

CREATE TRIGGER pch ON pettyCash FOR INSERT, UPDATE AS INSERT INTO pettyCashHistory(seq,whn,who,description,amount) SELECT seq,GetDate( ),SYSTEM_USER,description,amount FROM inserted;

11.7.2. MySQL

You can create the pettyCashHistory table as follows:

CREATE TABLE pettyCashHistory (seq INTEGER ,whn DATE ,who VARCHAR(10) ,description VARCHAR(100) ,amount DECIMAL(8,2) ,changedWhn TIMESTAMP DEFAULT CURRENT_TIMESTAMP ,changedBy VARCHAR(50) );

In order to maintain the audit trail you can set up a trigger that will kick in every time someone performs an INSERT into the pettycash table:

CREATE TRIGGER pchIns AFTER INSERT ON pettyCash FOR EACH ROW INSERT INTO pettyCashHistory (seq,whn,who,description,amount,changedBy) VALUES (new.seq,new.whn,new.who,new.description, new.amount,current_user);

To catch attempts to change existing records you can set up another trigger to fire following an UPDATE on the same table:

CREATE TRIGGER pchUpd AFTER UPDATE ON pettyCash FOR EACH ROW INSERT INTO pettyCashHistory (seq,whn,who,description,amount,changedBy) VALUES (new.seq,new.whn,new.who,new.description, new.amount,current_user);

11.7.3. Oracle

Oracle has sophisticated auditing features built in. Oracle's built-in mechanism is more robust and comprehensive than the approach shown here. The Oracle audit system includes mechanisms for auditing all activity, including the actions of the database administrator. You can find an introduction to Oracle auditing at http://www.securityfocus.com/infocus/1689.

For completeness, the hack shown here utilizes Oracle's triggers. First, create the history table:

CREATE TABLE pettyCashHistory (seq INTEGER ,whn DATE ,who VARCHAR(10) ,description VARCHAR(100) ,amount DECIMAL(8,2) ,changedWhn TIMESTAMP DEFAULT CURRENT_TIMESTAMP ,changedBy VARCHAR(10) DEFAULT USER );

You can loop over the changed rows and refer to the :new pseudotable:

CREATE TRIGGER pch AFTER INSERT OR UPDATE ON pettyCash FOR EACH ROW BEGIN INSERT INTO pettyCashHistory (seq,whn,who,description,amount) VALUES (:new.seq,:new.whn,:new.who,:new.description, :new.amount); END pch; /

11.7.4. PostgreSQL

PostgreSQL is similar to MySQL, except that it uses plpgsql [Hack #92]. PostgreSQL can use CURRENT_TIMESTAMP and CURRENT_USER as default conditions in the CREATE TABLE definition:

CREATE TABLE pettyCashHistory (seq INTEGER ,whn TIMESTAMP ,who VARCHAR(10) ,description VARCHAR(100) ,amount DECIMAL(8,2) ,changedWhn TIMESTAMP DEFAULT CURRENT_TIMESTAMP ,changedBy VARCHAR(10) DEFAULT CURRENT_USER );

You must define the trigger query in a function first, and then associate it with the trigger:

CREATE OR REPLACE FUNCTION cash_update( ) RETURNS trigger AS ' BEGIN INSERT INTO pettyCashHistory (seq,whn,who,description,amount) VALUES (new.seq,new.whn,new.who,new.description, new.amount); return new; END 'LANGUAGE plpgsql; CREATE TRIGGER pch AFTER INSERT OR UPDATE ON pettyCash FOR EACH ROW EXECUTE PROCEDURE cash_update( );

11.7.5. Locking Down the Underlying Tables

Having set up the history table and the triggers you can now lock down the tables. You must revoke INSERT, UPDATE, and DELETE permissions from the pettyCashHistory table; otherwise, the would-be fraudster can easily cover his tracks. The triggers will execute with the permission of the user who created them, so the INSERT into pettyCashHistory will still work, but only from the trigger. You can now afford to be more generous with the permissions on the pettyCash table. You can allow INSERT from anyone as usual, and you can allow UPDATEthis will permit users to fix errors and will help to keep the data accurate. You should revoke DELETE permission on pettyCash. If there is a requirement for DELETE you can include a Boolean column to flag deleted rows instead of actually removing them.

11.7.6. Processing the History Table

The history table just grows and grows forever. Because it is an ordinary SQL table, you can search it just like any other table from within SQL. Under normal circumstances, no one ever needs to look at the history table; but it's there in case an investigation is one day required. It also serves as a deterrent: in itself, it can't prevent dishonest alteration of the data, but when users know that their every move is being recorded, they will be less tempted to cheat.