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, ...).


/Johan

----- Original Message -----
> From: "Sándor Halász" <[email protected]>
> To: "MySql" <[email protected]>
> Sent: Wednesday, 7 December, 2016 14:56:55
> Subject: Re: audit trails

> 2016/12/07 01:26 ... [email protected]:
>>   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