0

I have sensitive information in multiple databases on the MySQL Server. I need to record which user have inserted or updated the record and incase of update we should be able to see the previous and the new values.

Is it possible in MySQL?

1

Usually for this purpose, I add a column in the table saying end user who updated it, and each client populates it when it it inserted/updated.

But if want to track each changes, usually I also add 2 datetime(6) columns in the table called StartDate and EndDate (use DateTime for mysql version before 5.6 not supporting microseconds)

EndDate is added to primary index (preferably at the end).

When row I insert a row into it, I put Sysdate(6) in StartDate and '9999-12-31' in EndDate (year 9999, yes it is hardcoded but if your application is still active at this time, it won't be your problem)

When I want to update an existing row, I close it first with a query like update MyTable set EndDate=sysdate(6), UpdatedBy='currentuser' where key=somekeyvalue and EndDate='9999-12-31' then I insert row with new values as explained earlier

When I look to current values, I always add " and EndDate='9999-12-31') in query

When I look to values applicable on a certain date, ie 2017-02-01 I perform query on a range of dates like this select * from MyTable where key=somekeyvalue and StartDate<='2017-02-01' and EndDate>'2017-02-01'

  • The problem with this approach is that you are relying on the application to perform the audit function, and it would be too easy for a developer to create code which did not follow the audit requirements. This type of auditing should be kept separate from the application so that the developers cannot affect the audit trail. – Dave Rix Feb 1 '17 at 11:47
  • It depends of the case, it might be useful to display in the application who last updated the data. Also it could provide an additional feature to see the state of a table (or the whole application, if you put that on all tables !) at a previous date – Laurent PELE Feb 1 '17 at 11:50
  • I agree with you on that, but it still stands that it would be too easy for someone to get around the auditing by not performing the required update within the application code - either by intention, or by just forgetting to add the audit code to any insert / update. If you need to track the content changes as part of an audit requirement, you cannot leave it to the application developers to make sure this is always coded this way – Dave Rix Feb 1 '17 at 11:54
  • I put another solution based on CSV log file populated by a trigger – Laurent PELE Feb 1 '17 at 12:02
0
  • General query log will log every user activity - Can grow huge.
  • You can use binary logs to review changes but again you won't be able to see user info / previous version of the record.

So here I'd suggest to implement triggers on the tables you require the logging. It'd create additional load to the system but if that's the requirement...

  • Agree with this - for MySQL, triggers for this requirement are the way to go. Activating the general log will slow your server down bigtime. – Vérace Nov 6 '15 at 13:08
  • @Vérace - Seems like this kind of trigger (which will be writing to another table) will slow down the server about as much. – Rick James Dec 4 '15 at 6:30
  • That's right @RickJames but do we have any alternative to the requirement? May be user can choose to do this on slave! – mysql_user Dec 4 '15 at 6:36
  • Maybe the server is not too busy to handle the load. Maybe there is enough disk space to last a long time. Maybe a Slave dedicated to this would solve the CPU load issue. Maybe that Slave could have bigger, cheaper, disks. No free lunch. – Rick James Dec 4 '15 at 15:58
0

Having the application record the before and after details would be a monumental pain - it would have to know the existing content before doing the update, which would add an extra read query, and you'd have to ensure that this was done in all areas of the application, and it would be easy to missing an area / bypass the audit requirement.

A trigger is probably the best way to go with this, but there are some caveats around doing it this way. I agree with @rick-james comments on the other answer regarding triggers, but you could mitigate the drawbacks partially.

  • You should only enable the triggers on the tables you want to audit, and not across all tables, as that can cause a lot of additional overhead.

  • If your application uses a common user account to connect to the db, then the trigger will only be able to record against that user, and not the specific user of the application - for that you would need to also write the actual user information to the table you are auditing at the same time as the data update, and pull that information out into your audit table within the trigger

  • If you write the audit information to a separate database, into a single table which holds information about the update such as timestamp, user, table, field, before_content, after_content - this would then record a row for each field that was updated, rather than the entire row

  • You could define the audit database / table using the blackhole storage engine, and then replicate that database to a dedicated slave (excluded from all other slaves) so that you can more easily restrict access to the information - the slave would need to use a physical storage engine such as innodb or archive. This would also not use much more storage on the master other than the extra binary log content

  • all audit analysis could then be done against the dedicated slave and not the master, as this table could grow rapidly depending on the application.

0

You can create a specific text log file using mysql CSV engine like:

CREATE TABLE sensitivechanges 
(mytable VARCHAR(30) NOT NULL, mykey text NOT NULL, 
oldvalue text NOT NULL, newvalue text NOT NULL, 
mydate datetime(6) NOT NULL, updatedByUser varchar(30)NOT NULL) 
ENGINE=CSV;

and populate it by a trigger that is activated on a change :

something like :

INSERT INTO sensitivechanges (mytable, mykey, oldvalue, newvalue, mydate, updatedByUser) 
VALUES ('table', 'key', 'oldvalue', 'newvalue', sysdate(6), 'user');

csv files generated by mysql can be recycled (for example every day) by an administrator :

  1. use query flush table sensitivechanges so that recent changes are stored in the file
  2. the file is called sensitivechanges.CSV when you rotate it, leave an empty file
  3. query again : flush table sensitivechanges;
  4. don't change sensitivechanges.CSM file
  5. if you have slave, keep in mind that if you empty the file with a system command, it will grow on your slave, so don't replicate it on track changes only the slave

you can also

perform rotation with some queries :

CREATE TABLE sensitivechanges_bkp20170201 ENGINE=CSV as select * from sensitivechanges ;
flush table sensitivechanges_bkp20170201;

truncate sensitivechanges;

zip sensitivechanges_bkp20170201.csv

drop TABLE sensitivechanges_bkp20170201;

orginal question was about mysql-5.5 which doesn't support datetime(6) (in microsecond), so replace datetime(6) by datetime and sysdate(6) by sysdate()

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.