Triggers are not the best way to go about this. Consider: * What is to stop a malicious user from truncating the audit table? * Triggers may fail (corrupt target table, for instance) and a trigger failure may cancel the source statement * Triggers have a performance impact - you're basically doubling every DML action. * Triggers get executed sequentially for multi-inserts, slowing the whole operation down
I suggest having a look at one of the available audit plugins. * Percona has one that can iirc also be compiled against the standard (oracle) community edition * Oracle have one too in newer versions (I think from 5.7?) but it's enterprise licensed * MariaDB has one in the community version, but only works against MariaDB server * McAfee also had one, but I'm unsure about it's current status The benefit of a plugin is that the code runs out of the "userspace", has lower performance impact (parallelism) and cannot cause originating statements to fail. Additionally, I would assume that some of these, if not all, can also log towards an external target (file, network, ...). /Johan ----- Original Message ----- > From: "Sándor Halász" <h...@tbbs.net> > To: "MySql" <mysql@lists.mysql.com> > Sent: Wednesday, 7 December, 2016 14:56:55 > Subject: Re: audit trails > 2016/12/07 01:26 ... mach...@seworx.co.za: >> well in essence the following is required. >> >> we need to know who made what changes to tables. > > There is a machination that you can try in every trigger that will add > the user-name to the binary log: > > set @asdfasdfasd = CURRENT_USER(); > INSERT INTO T VALUE ( ... @asdfasdfasd, UNIX_TIMESTAMP() ... ); > > The value assigned the variable @asdfasdfasd, since it is used to change > a table, will show up in the binary log. The function "UNIX_TIMESTAMP" > yields a number that matches TIMESTAMP in the binary log. > >> we recently had a case of important data being deleted, however >> finding it i binary logs proved 2 things : >> >> 1. it takes very long to find as we did not know in which file the >> details were. > > You did not know where the binary log was saved? That is set by you in > the global variables "log_bin_basename" and "log_bin_index". > >> I have managed to figure that part out almost fully and have one or two >> more kinks to work out. We will be adding an error in the trigger for >> deletes, however it should still log the delete attempts to audit table >> and this is where I am stuck now. >> >> I hit the error, however the attempt to delete is not being logged to >> the audit table. > > Only if the DELETE looks valid is the BEFORE DELETE trigger triggered. > If the deletion would yield inconsistency according to the constraints > that you set up and MySQL supports then the deletion is aborted and > rolled back ere AFTER DELETE trigger is triggered. > >> The problen however is now that they would like to know what query was >> run. i.e. was it a straight query or was it run by calling some >> procedure. I am however not sure if this will even be something that can >> be logged. > > I use > binlog_format=STATEMENT > ; then the transaction is logged--but MySQL Cluster does not support this. > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/mysql -- Unhappiness is discouraged and will be corrected with kitten pictures. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql
