Re: audit trails
- Date: Wed, 7 Dec 2016 17:13:26 +0100 (CET)
- From: Johan De Meersman <vegivamp@xxxxxxxxx>
- Subject: Re: audit trails
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, ...).
----- Original Message -----
> From: "Sándor Halász" <hsv@xxxxxxxx>
> To: "MySql" <mysql@xxxxxxxxxxxxxxx>
> Sent: Wednesday, 7 December, 2016 14:56:55
> Subject: Re: audit trails
> 2016/12/07 01:26 ... machiel@xxxxxxxxxxxx:
>> 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
> ; 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