8
\$\begingroup\$

I wish to create audit trails for specific tables and columns in my database, and document who made the change, when it was made, and what the change was.

To do so, I will create the following tables:

  1. Audits: Create a record whenever a change is made to any table, and stores the table, the date, the user who made the change, and the task (insert, update, remove).
  2. Audits_1pk: 1-to-1 relationship to audits, and stores the primary key of any table which has a single primary key.
  3. Audits_2pk: Same as audits_1pk except used for tables which have a compound primary key made up of (2) keys.
  4. Audits_3pk: Same as audits_1pk except used for tables which have a compound primary key made up of (3) keys.
  5. Audit_int: Stores the effected affected column name if is a int type, and has a 1-to-many relationship to audits.
  6. Audit_text: Same as audit_int, but is for columns which are of text type.
  7. Audit_var_45: Same as audit_int, but is for columns which are of varchar(45) type.

I will then add triggers to the tables I wish to audit, and will write to the above tables.

Below is the fully working script. I include an example where the "students" table and the "courses_has_students" table is modified and audited.

Please comment on the appropriateness of my implementation, and whether you have any recommendations.

SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL,ALLOW_INVALID_DATES';

CREATE SCHEMA IF NOT EXISTS `auditTest` DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci ;
USE `auditTest` ;

-- -----------------------------------------------------
-- Table `auditTest`.`users`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `auditTest`.`users` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
  `name` VARCHAR(45) NOT NULL ,
  PRIMARY KEY (`id`) )
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `auditTest`.`auditTasks`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `auditTest`.`auditTasks` (
  `task` CHAR(1) NOT NULL ,
  `name` VARCHAR(45) NOT NULL ,
  PRIMARY KEY (`task`) )
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `auditTest`.`audits`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `auditTest`.`audits` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
  `users_id` INT UNSIGNED NULL ,
  `dateChanged` DATETIME NOT NULL ,
  `dbUser` VARCHAR(45) NOT NULL ,
  `requesting_ip` CHAR(15) NULL ,
  `tableName` VARCHAR(45) NOT NULL ,
  `task` CHAR(1) NOT NULL ,
  PRIMARY KEY (`id`) ,
  INDEX `fk_audits_users_idx` (`users_id` ASC) ,
  INDEX `fk_audits_tasks1_idx` (`task` ASC) ,
  CONSTRAINT `fk_audits_users`
    FOREIGN KEY (`users_id` )
    REFERENCES `auditTest`.`users` (`id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_audits_tasks1`
    FOREIGN KEY (`task` )
    REFERENCES `auditTest`.`auditTasks` (`task` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `auditTest`.`audits_1pk`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `auditTest`.`audits_1pk` (
  `audits_id` INT UNSIGNED NOT NULL ,
  `pk1` VARCHAR(45) NOT NULL ,
  PRIMARY KEY (`audits_id`) ,
  CONSTRAINT `fk_audits_1pk_audits1`
    FOREIGN KEY (`audits_id` )
    REFERENCES `auditTest`.`audits` (`id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `auditTest`.`audits_2pk`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `auditTest`.`audits_2pk` (
  `audits_id` INT UNSIGNED NOT NULL ,
  `pk1` VARCHAR(45) NOT NULL ,
  `pk2` VARCHAR(45) NOT NULL ,
  PRIMARY KEY (`audits_id`) ,
  CONSTRAINT `fk_audits_2pk_audits1`
    FOREIGN KEY (`audits_id` )
    REFERENCES `auditTest`.`audits` (`id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `auditTest`.`audits_3pk`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `auditTest`.`audits_3pk` (
  `audits_id` INT UNSIGNED NOT NULL ,
  `pk1` VARCHAR(45) NOT NULL ,
  `pk2` VARCHAR(45) NOT NULL ,
  `pk3` VARCHAR(45) NOT NULL ,
  PRIMARY KEY (`audits_id`) ,
  CONSTRAINT `fk_audits_3pk_audits1`
    FOREIGN KEY (`audits_id` )
    REFERENCES `auditTest`.`audits` (`id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `auditTest`.`audit_int`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `auditTest`.`audit_int` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
  `audits_id` INT UNSIGNED NOT NULL ,
  `columnName` VARCHAR(45) NOT NULL ,
  `oldValue` INT NULL ,
  `newValue` INT NULL ,
  PRIMARY KEY (`id`) ,
  INDEX `fk_audit_int_audits1_idx` (`audits_id` ASC) ,
  CONSTRAINT `fk_audit_int_audits1`
    FOREIGN KEY (`audits_id` )
    REFERENCES `auditTest`.`audits` (`id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `auditTest`.`audit_text`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `auditTest`.`audit_text` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
  `audits_id` INT UNSIGNED NOT NULL ,
  `columnName` VARCHAR(45) NOT NULL ,
  `oldValue` TEXT NULL ,
  `newValue` TEXT NULL ,
  PRIMARY KEY (`id`) ,
  INDEX `fk_audit_int_audits1_idx` (`audits_id` ASC) ,
  CONSTRAINT `fk_audit_int_audits10`
    FOREIGN KEY (`audits_id` )
    REFERENCES `auditTest`.`audits` (`id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `auditTest`.`audit_var_45`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `auditTest`.`audit_var_45` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
  `audits_id` INT UNSIGNED NOT NULL ,
  `columnName` VARCHAR(45) NOT NULL ,
  `oldValue` VARCHAR(45) NULL ,
  `newValue` VARCHAR(45) NULL ,
  PRIMARY KEY (`id`) ,
  INDEX `fk_audit_int_audits1_idx` (`audits_id` ASC) ,
  CONSTRAINT `fk_audit_int_audits100`
    FOREIGN KEY (`audits_id` )
    REFERENCES `auditTest`.`audits` (`id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `auditTest`.`students`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `auditTest`.`students` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
  `name` VARCHAR(45) NOT NULL ,
  `ssn` CHAR(10) NOT NULL ,
  `notes` TEXT NULL ,
  `nickname` VARCHAR(45) NULL ,
  PRIMARY KEY (`id`) )
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `auditTest`.`courses`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `auditTest`.`courses` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
  `name` VARCHAR(45) NOT NULL ,
  `course_number` VARCHAR(45) NOT NULL ,
  PRIMARY KEY (`id`) )
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `auditTest`.`courses_has_students`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `auditTest`.`courses_has_students` (
  `courses_id` INT UNSIGNED NOT NULL ,
  `students_id` INT UNSIGNED NOT NULL ,
  `other_int` INT NULL ,
  PRIMARY KEY (`courses_id`, `students_id`) ,
  INDEX `fk_courses_has_students_students1_idx` (`students_id` ASC) ,
  INDEX `fk_courses_has_students_courses1_idx` (`courses_id` ASC) ,
  CONSTRAINT `fk_courses_has_students_courses1`
    FOREIGN KEY (`courses_id` )
    REFERENCES `auditTest`.`courses` (`id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_courses_has_students_students1`
    FOREIGN KEY (`students_id` )
    REFERENCES `auditTest`.`students` (`id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

USE `auditTest`;

DELIMITER $$
USE `auditTest`$$


CREATE TRIGGER tg_students_ins AFTER INSERT ON students
FOR EACH ROW 
BEGIN
    INSERT INTO audits(tableName,task,dateChanged,users_id,dbUser,requesting_ip) VALUES ('students', 'i', NOW(), @users_id, USER(), @requesting_ip );
    SET @AID=LAST_INSERT_ID();
    INSERT INTO audits_1pk(audits_id,pk1) VALUES (@AID,NEW.id );
    IF NEW.name IS NOT NULL THEN
        INSERT INTO audit_var_45(audits_id,columnName,oldValue,newValue) VALUES (@AID,'name',NULL,NEW.name);
    END IF;
    IF NEW.ssn IS NOT NULL THEN
        INSERT INTO audit_var_45(audits_id,columnName,oldValue,newValue) VALUES (@AID,'ssn',NULL,NEW.ssn);
    END IF;
    IF NEW.notes IS NOT NULL THEN
        INSERT INTO audit_text(audits_id,columnName,oldValue,newValue) VALUES (@AID,'notes',NULL,NEW.notes);
    END IF;
END$$

USE `auditTest`$$


CREATE TRIGGER tg_students_upd AFTER UPDATE ON students
FOR EACH ROW 
BEGIN
    IF NOT NEW.name <=> OLD.name OR NOT NEW.ssn <=> OLD.ssn OR NOT NEW.notes <=> OLD.notes THEN
        INSERT INTO audits(tableName,task,dateChanged,users_id,dbUser,requesting_ip) VALUES ('students', 'u', NOW(), @users_id, USER(), @requesting_ip );
        SET @AID=LAST_INSERT_ID();
        INSERT INTO audits_1pk(audits_id,pk1) VALUES (@AID,NEW.id );
        IF NOT NEW.name <=> OLD.name THEN
            INSERT INTO audit_var_45(audits_id,columnName,oldValue,newValue) VALUES (@AID,'name',OLD.name,NEW.name);
        END IF;
        IF NOT NEW.ssn <=> OLD.ssn THEN
            INSERT INTO audit_var_45(audits_id,columnName,oldValue,newValue) VALUES (@AID,'ssn',OLD.ssn,NEW.ssn);
        END IF;
        IF NOT NEW.notes <=> OLD.notes THEN
            INSERT INTO audit_text(audits_id,columnName,oldValue,newValue) VALUES (@AID,'notes',OLD.notes,NEW.notes);
        END IF;
    END IF;
END$$

USE `auditTest`$$


CREATE TRIGGER tg_students_del AFTER DELETE ON students
FOR EACH ROW 
BEGIN
    INSERT INTO audits(tableName,task,dateChanged,users_id,dbUser,requesting_ip) VALUES ('students', 'd', NOW(), @users_id, USER(), @requesting_ip );
    SET @AID=LAST_INSERT_ID();
    INSERT INTO audits_1pk(audits_id,pk1) VALUES (@AID,OLD.id );
END$$


DELIMITER ;

DELIMITER $$
USE `auditTest`$$


CREATE TRIGGER tg_courses_has_students_ins AFTER INSERT ON courses_has_students
FOR EACH ROW 
BEGIN
    INSERT INTO audits(tableName,task,dateChanged,users_id,dbUser,requesting_ip) VALUES ('courses_has_students', 'i', NOW(), @users_id, USER(), @requesting_ip );
    SET @AID=LAST_INSERT_ID();
    INSERT INTO audits_2pk(audits_id,pk1,pk2) VALUES (@AID,NEW.courses_id,NEW.students_id);
    IF NEW.other_int IS NOT NULL THEN
        INSERT INTO audit_int(audits_id,columnName,oldValue,newValue) VALUES (@AID,'other_int',NULL,NEW.other_int);
    END IF;
END$$

USE `auditTest`$$


CREATE TRIGGER tg_courses_has_students_upt AFTER UPDATE ON courses_has_students
FOR EACH ROW 
BEGIN
    IF NOT NEW.other_int <=> OLD.other_int THEN
        INSERT INTO audits(tableName,task,dateChanged,users_id,dbUser,requesting_ip) VALUES ('courses_has_students', 'u', NOW(), @users_id, USER(), @requesting_ip );
        SET @AID=LAST_INSERT_ID();
        INSERT INTO audits_2pk(audits_id,pk1,pk2) VALUES (@AID,NEW.courses_id,NEW.students_id);
        IF NOT NEW.other_int <=> OLD.other_int THEN
            INSERT INTO audit_int(audits_id,columnName,oldValue,newValue) VALUES (@AID,'other_int',OLD.other_int,NEW.other_int);
        END IF;
    END IF;
END$$

USE `auditTest`$$


CREATE TRIGGER tg_courses_has_students_del AFTER DELETE ON courses_has_students
FOR EACH ROW 
BEGIN
    INSERT INTO audits(tableName,task,dateChanged,users_id,dbUser,requesting_ip) VALUES ('courses_has_students', 'd', NOW(), @users_id, USER(), @requesting_ip );
    SET @AID=LAST_INSERT_ID();
    INSERT INTO audits_2pk(audits_id,pk1,pk2) VALUES (@AID,OLD.courses_id,OLD.students_id);
END$$


DELIMITER ;


SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;



-- Required by audit tables

INSERT INTO auditTasks (task,name) VALUES ('i','insert');
INSERT INTO auditTasks (task,name) VALUES ('u','update');
INSERT INTO auditTasks (task,name) VALUES ('d','delete');

-- Get some business records

INSERT INTO users (id,name) VALUES (0,'John Doe');
INSERT INTO users (id,name) VALUES (0,'Jane Doe');

INSERT INTO courses (id,name,course_number) VALUES (0,'Math','123abc');
INSERT INTO courses (id,name,course_number) VALUES (0,'English','123abc');

-- Set by PHP application
SET @requesting_ip='555.555.555.555';
SET @users_id=1;

-- Start normal routines
INSERT INTO students (id,name,ssn,notes,nickname) VALUES (0,'Billy Bob','555-55-5555',NULL,'Bebop');
UPDATE students SET name='Bill Bob',notes='Some notes' WHERE id=1;
INSERT INTO courses_has_students (courses_id,students_id) VALUES (1,1);
INSERT INTO courses_has_students (courses_id,students_id) VALUES (2,1);
DELETE FROM courses_has_students WHERE courses_id=1 AND students_id=1;
DELETE FROM courses_has_students WHERE courses_id=2 AND students_id=1;
DELETE FROM students WHERE id=1;

-- Audit database

SELECT u.name AS user_name, at.name AS task, a.dateChanged, a.tableName, af.columnName, af.oldValue AS oldValue_text, af.newValue AS newValue_text, null AS oldValue_val_45, null AS newValue_val_45
FROM audits AS a
INNER JOIN users AS u ON u.id=a.users_id
INNER JOIN auditTasks AS at ON at.task=a.task
INNER JOIN audits_1pk AS apk ON apk.audits_id=a.id
LEFT OUTER JOIN audit_text AS af ON af.audits_id=a.id
WHERE a.tableName='students' AND apk.pk1=1
UNION
SELECT u.name AS user_name, at.name AS task, a.dateChanged, a.tableName, af.columnName, null AS oldValue_text, null AS newValue_text, af.oldValue AS oldValue_val_45, af.newValue AS newValue_val_45
FROM audits AS a
INNER JOIN users AS u ON u.id=a.users_id
INNER JOIN auditTasks AS at ON at.task=a.task
INNER JOIN audits_1pk AS apk ON apk.audits_id=a.id
LEFT OUTER JOIN audit_var_45 AS af ON af.audits_id=a.id
WHERE a.tableName='students' AND apk.pk1=1
ORDER BY dateChanged ASC;

SELECT u.name AS user_name, at.name AS task, a.dateChanged, a.tableName, af.columnName, af.oldValue, af.newValue
FROM audits AS a
INNER JOIN users AS u ON u.id=a.users_id
INNER JOIN auditTasks AS at ON at.task=a.task
INNER JOIN audits_2pk AS apk ON apk.audits_id=a.id
LEFT OUTER JOIN audit_int AS af ON af.audits_id=a.id
WHERE a.tableName='courses_has_students' AND apk.pk1=1 AND apk.pk2=1
ORDER BY dateChanged ASC;
\$\endgroup\$
3
+50
\$\begingroup\$

An alternative way I have seen is to create separate audit tables for each table you want to audit and simply have a trigger that copies the entire row into the table plus the action (insert, update, delete), the user that made the change and timestamp of when it happened.

It does involve more tables but it means you only add audit tables & triggers for the tables you actually want audited so is potentially a simplier solution since having several audit tables based on the keys and having to work out what goes where has more potential areas for bugs to creep in.

One other advantage is that you can easily query the tables to see not only the type of change and affected fields but all fields allowing a snapshot of the data at time of change.

Couple of disadvantages: you have to create audit tables and triggers for every table you want audited and if you need to create a timeline of changes across multiple tables then it takes a little more work but the userId and timestamp should help there.

\$\endgroup\$
  • \$\begingroup\$ Thanks Nathan, Yes, I have seen the method you describe, and I understand your stated pros and cons to it. I have not, however, seen the method I am proposing, and that makes me think there must be something wrong with it. Have you ever seen it? Do you see any major drawbacks to it? \$\endgroup\$ – user1032531 Apr 18 '13 at 11:27
  • \$\begingroup\$ a few things spring to mind. 1 - the intention seems to be to try and centralise the auditing but by having to have multiple tables you have a lot more moving parts so increase possibility that something could go wrong 2 - if a user changes 10 fields on a record then you have to store 10 records in the audit table, so if you need to report on it it is harder to pull together a picture of the data & if you're having to report on it this is definately someting to take into account. 3 - maintainability by other people, will they easily be able understand how it works to extend in the future \$\endgroup\$ – Nathan Apr 18 '13 at 13:02
  • \$\begingroup\$ 1 - The intention is to provide a framework where only given columns of given tables can be easily added to the "audit list". 2 - if a user changes 10 fields on a record then you have to store 10 records in the audit table, but only if you want to audit those 10 fields. 3 - maintainability by other people is not an immediate concern, but probably can be handled by a view. Not trying to necessarily defend my approach. Think the cons exceed the pros? \$\endgroup\$ – user1032531 Apr 19 '13 at 2:14
  • \$\begingroup\$ I don't know your context but from an auditing point of view does it make sense to only audit certain fields? what value do you gain from only a few fields? I guess it will depend on how the audit information will be used, who is consuming it - a person or another system? \$\endgroup\$ – Nathan Apr 19 '13 at 8:36
  • \$\begingroup\$ Thanks Nathan, People will enter and use the data. Certain fields are more sensitive than others, and only the sensitive ones need be audited.d \$\endgroup\$ – user1032531 Apr 20 '13 at 10:37
1
\$\begingroup\$

I agree with Nathan. The only change I would make is that I would add previous fields also, since you want to track the contents. So, to summarize, for each table there will be an audit table with twice as many columns as the original table (for before and after the change), plus an action, userid and timestamp. Once you have these info, you will be able to process it in application level to perform analytics.

Your approach is logically alright, but I think it introduces unnecessary complexity for implemetation. The choice depends on your project. For example, if one of your table is a Session table that get updated for every user login (say every second) then duplicating it is probably not a good idea, your solution will be a better fit for that (you will save memory). But, if your tables are very important (like account information, or deals with money), then I think Nathan's solution is the way to go.

Hope I can help :)

\$\endgroup\$
  • \$\begingroup\$ Thanks Faisal, I do have an oldValue to track all previous values. In one sense, I wonder if it is really even needed since oldValue is the same as the previous newValue, but it does I suppose make it more intuitive. \$\endgroup\$ – user1032531 Apr 20 '13 at 10:40
  • \$\begingroup\$ Again, you are absolutely right. But, it does make one row depend on other row rather (which make the system more complex to handle). For me, I try to find a balance. \$\endgroup\$ – faisal Apr 21 '13 at 6:05
  • \$\begingroup\$ @faisal if you snapshot the entire row on every change then you don't need to stored old value & new value, you can just look at the previous record and see what the value was. Admittedly can be more difficult working out what's changed unless you write a report to highlight the change made in each row which I'd think is what you actually want from an audit. \$\endgroup\$ – Nathan Apr 22 '13 at 9:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.