Bug #102422 Optionally allow user data in the binlogs
Submitted: 29 Jan 2021 16:27 Modified: 3 Feb 2021 10:39
Reporter: Simon Mudd (OCA) Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Replication Severity:S4 (Feature request)
Version:8.0.23 OS:Any
Assigned to: CPU Architecture:Any
Tags: auditing, binlog, catching DML changes, logging, user/host

[29 Jan 2021 16:27] Simon Mudd
Description:
Please fix category if needed.

It can be very useful to track the user who made a change to data. e.g. user/hostname or user/ip address.  Recording that in the database for every change may be complex and add additional load. Optionally writing it to the binlog would provide a better auditing trace of changes and who they were committed by.

How to repeat:
Someone comes and asks who made a recent database change. Often it's hard to answer if you do not catch the action, if not using something like an audit plugin but that potentially requires extra database writes, has to be custom made.  Simply adding as metadata the user who made the change allows the fact to be recorded and used if/as/when needed.

This information would also be useful for external systems ingesting binlogs for later processing.

Suggested fix:
Consider adding a feature flag which if enabled would record the user making the change (user + host/ip) to the binlog as extra metadata associated with the transaction.
[29 Jan 2021 16:34] Simon Mudd
This information would go to the binlog but should not affect replication behaviour except "pass down" the event/metadata to downstream replicas.
[29 Jan 2021 21:24] MySQL Verification Team
Thank you for the feature request. Should be noted this would only work for binlog enabled servers, also there are some different performance options for offseting audit plugin writes

https://dev.mysql.com/doc/refman/5.7/en/audit-log-logging-configuration.html#audit-log-str...

This request looks similiar to sql server which seems to record this in transaction logs

https://jackworthen.com/2018/04/16/querying-the-transaction-log-of-a-database-to-identify-...
[3 Feb 2021 10:36] Simon Mudd
I think there is some scope for how this might be implemented. The SQL Server implementation sounds good too. It would be nice to have a SQL interface to the binlog information as shown there.

There is clearly also some question about where the information should go: auditing could push it outside of the server (or anywhere else), whereas binlogging would keep it related to the DML transactions, and as indicated would only be available if you write binlogs as these are clearly not optional.

The "need" to record user information (specifically user/host) clearly depends on the organisation so really what I think would be useful are sufficiently flexible hooks to handle most cases and without having to write custom plugins to deal with this.