Logging integrity

Since 2009 system administrators working in Italy have had something new and compulsory to deal with: logging. We have to keep track of any login and logout from any system that contains personal or sensitive data. These logs have to be complete and indicating the name of the operator, the date and time and their integrity must be validated. You can read the full document from the Italian data protection authority.
Different solutions may come to your mind and the easier one is to store these logs in read-only devices such as CDs or DVDs.
Anyway the best solution is to validate your logs as soon as they are recorded and in this case only a database backend can easily do the job.
I want to share a typical MySQL solution with triggers.
First of all we start modifying the SystemEvents table of your logging database adding a new column:

ALTER TABLE `SystemEvents` ADD `ChkSum` VARCHAR(40) NOT NULL;

Than we define a trigger for calculating and inserting the SHA1 hash:

DELIMITER //
CREATE TRIGGER insert_hash BEFORE INSERT ON SystemEvents
FOR EACH ROW BEGIN
SET @lastid = (SELECT ID FROM `SystemEvents` ORDER BY ID DESC LIMIT 1);
SET @chain1 = (SELECT ChkSum FROM `SystemEvents` WHERE ID = @lastid);
SET @chain2 = (SELECT CONCAT(NEW.ReceivedAt,NEW.DeviceReportedTime,
NEW.Facility,NEW.Priority,NEW.FromHost,NEW.Message,NEW.SysLogTag) );
SET @hashres = (SELECT CONCAT(@chain1,@chain2));
SET NEW.ChkSum = SHA1(@hashres);
END;//
DELIMITER ;

As you can see the query is divided into steps to be more readable and to permit some debug. The interesting part is that the SHA1 hash for the new row is calculated starting from the SHA1 hash of the previous row concatenated with some columns (ReceivedAtDeviceReportedTimeFacilityPriorityFromHostMessage and SysLogTag) of the new log row.
With this concatenation every record of the database is linked with the previous one: so not only the row is validated but the entire database can be checked for any kind of modification, included deletion.
Anyway it’s always possible to add other triggers for other events.
Let’s see a second trigger that deals with updated records:

DELIMITER //
CREATE TRIGGER updated_record BEFORE UPDATE ON SystemEvents
FOR EACH ROW BEGIN
SET NEW.ChkSum = 'ATTENTION: UPDATED RECORD!';
END;//
DELIMITER ;

In this case any updated record will not have the SHA1 hash but a string reporting that it has been modified.
This is only a simple example. You can do even more, such as reporting the date and time of the modification and even saving the old record into another one (remember that MySQL uses NEW.column for the new value and OLD.column for the old one when using the BEFORE statement).
You can also add a trigger for any deleted record.
When deploying this kind of solution you should properly think of privilege separation. Use a dedicated user for your logging database and remove GRANT and TRIGGER privileges so it will be impossible to delete or modify the triggers defined (unless you are a superuser).
Happy logging!


This entry was posted on Tuesday, April 26th, 2011 at 11:08 PM and is filed under privacy, security, software.

You can follow any responses to this entry through the RSS 2.0 feed. Both comments and pings are currently closed.

Comments are closed.