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?
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.Sign up to join this community
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'
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...
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
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.
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 :
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()